66 lines
1.6 KiB
SQL
66 lines
1.6 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
prompt Show shared pool memory usage of SQL statement with SQL_ID &1
|
|
|
|
-- uncomment the chunk_ptr and/or subheap_desc if you want more detailed breakdown of individual chunk allocations
|
|
|
|
COL sqlmem_structure HEAD STRUCTURE FOR A20
|
|
COL sqlmem_function HEAD FUNCTION FOR A20
|
|
COL sqlmem_chunk_com HEAD CHUNK_COM FOR A20
|
|
COL sqlmem_heap_desc HEAD HEAP_ADDR FOR A16
|
|
|
|
|
|
SELECT
|
|
child_number
|
|
, sharable_mem
|
|
, persistent_mem
|
|
, runtime_mem
|
|
-- , typecheck_mem
|
|
FROM
|
|
v$sql
|
|
WHERE
|
|
sql_id = '&1'
|
|
/
|
|
|
|
-- the hints below are needed on oracle 12 as the CBO ended up
|
|
-- changing ignoring the join order specified in the GV$ view hints
|
|
|
|
SELECT /*+ NO_MERGE(@sel$2) NO_MERGE(@sel$3) LEADING(@sel$3 c) */
|
|
-- SELECT /*+ NO_MERGE(@sel$2) NO_MERGE(@sel$3) LEADING(@sel$4 c) xNO_MERGE(@"SEL$4") */
|
|
-- sql_text
|
|
-- , sql_fulltext
|
|
-- hash_value
|
|
-- , sql_id
|
|
sum(chunk_size) total_size
|
|
, trunc(avg(chunk_size)) avg_size
|
|
, count(*) chunks
|
|
, alloc_class
|
|
, chunk_type
|
|
, structure sqlmem_structure
|
|
, function sqlmem_function
|
|
, chunk_com sqlmem_chunk_com
|
|
, heap_desc sqlmem_heap_desc
|
|
-- , chunk_ptr
|
|
-- , subheap_desc
|
|
FROM
|
|
v$sql_shared_memory s
|
|
WHERE
|
|
sql_id = '&1'
|
|
-- hash_value = &1
|
|
GROUP BY
|
|
hash_value
|
|
, sql_id
|
|
, heap_desc
|
|
, structure
|
|
, function
|
|
, chunk_com
|
|
-- , chunk_ptr
|
|
, alloc_class
|
|
, chunk_type
|
|
-- , subheap_desc
|
|
ORDER BY
|
|
total_size DESC
|
|
/
|
|
|