72 lines
1.9 KiB
SQL
72 lines
1.9 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.
|
|
|
|
-- Purpose: Show a summary of ACTIVE SQL workareas grouped by operation type (SORT, HASH, etc)
|
|
-- Show both PGA workarea usage and TEMP usage by workareas (workareas don't include
|
|
-- other PGA/TEMP use like PL/SQL arrays and global temporary tables)
|
|
--
|
|
-- Usage: @wrkasum 1=1
|
|
-- @wrkasum username='APPUSER'
|
|
-- @wrkasum "program LIKE '%BatchRunner.exe%' AND machine='host123'"
|
|
|
|
PROMPT Total PGA allocation from V$PROCESS (excluding MGA)
|
|
|
|
SELECT
|
|
inst_id
|
|
, ROUND(SUM(pga_alloc_mem)/1048576,1) total_alloc_pga
|
|
, ROUND(SUM(pga_used_mem) /1048576,1) total_used_pga
|
|
FROM
|
|
gv$process
|
|
GROUP BY
|
|
inst_id
|
|
ORDER BY
|
|
inst_id
|
|
/
|
|
|
|
PROMPT Top allocation reason by PGA memory usage
|
|
|
|
COL wrkasum_operation_type FOR A30
|
|
|
|
SELECT
|
|
operation_type wrkasum_operation_type
|
|
, policy
|
|
, ROUND(SUM(actual_mem_used)/1048576) actual_pga_mb
|
|
, ROUND(SUM(work_area_size)/1048576) allowed_pga_mb
|
|
, ROUND(SUM(tempseg_size)/1048576) temp_mb
|
|
, MAX(number_passes) num_passes
|
|
, COUNT(DISTINCT qcinst_id||','||qcsid) num_qc
|
|
, COUNT(DISTINCT inst_id||','||sid) num_sessions
|
|
FROM
|
|
gv$sql_workarea_active
|
|
WHERE
|
|
&1
|
|
GROUP BY
|
|
operation_type
|
|
, policy
|
|
ORDER BY
|
|
actual_pga_mb DESC NULLS LAST
|
|
/
|
|
|
|
PROMPT Top SQL_ID by TEMP usage...
|
|
|
|
SELECT
|
|
sql_id
|
|
, policy
|
|
, ROUND(SUM(actual_mem_used)/1048576) actual_pga_mb
|
|
, ROUND(SUM(work_area_size)/1048576) allowed_pga_mb
|
|
, ROUND(SUM(tempseg_size)/1048576) temp_mb
|
|
, MAX(number_passes) num_passes
|
|
, COUNT(DISTINCT qcinst_id||','||qcsid) num_qc
|
|
, COUNT(DISTINCT inst_id||','||sid) num_sessions
|
|
FROM
|
|
gv$sql_workarea_active
|
|
WHERE
|
|
&1
|
|
GROUP BY
|
|
sql_id
|
|
, policy
|
|
ORDER BY
|
|
temp_mb DESC NULLS LAST
|
|
/
|
|
|