2
top_5_sql_on_sqlstats
emcs
87次下载
1228次浏览
2019-07-24
4.5

脚本内容

==================================================================== -- Script: top_5_sql_on_sqlstats.sql -- Author: Quanwen Zhao -- Updated: May 17, 2019 -- Trying not to check v$sql, as you can see Connor's this blog post -- https://connor-mcdonald.com/2019/03/04/less-slamming-vsql/ ==================================================================== -- Top_5_buffer_gets -- Ranking Top 5 SQL for buffer_gets (High CPU) on v$sqlstats -- Number of buffer gets for all cursors with this SQL text and plan SET LINESIZE 32767 SET PAGESIZE 50000 COLUMN sql_id FORMAT a13 COLUMN sql_text FORMAT a60 COLUMN buffer_gets FORMAT 999,999,999,999,999 SELECT * FROM (SELECT sql_id , sql_text , buffer_gets , DENSE_RANK() OVER (ORDER BY buffer_gets DESC) AS buffer_gets_rank FROM v$sqlstats WHERE buffer_gets > 1000000 ) WHERE buffer_gets_rank <= 5 / SET LINESIZE 80 SET PAGESIZE 14 -- Top_5_disk_reads -- Ranking Top 5 SQL for disk_reads (High I/O) on v$sqlstats -- Number of disk reads for all cursors with this SQL text and plan SET LINESIZE 32767 SET PAGESIZE 50000 COLUMN sql_id FORMAT a13 COLUMN sql_text FORMAT a70 COLUMN disk_reads FORMAT 999,999,999,999,999 SELECT * FROM (SELECT sql_id , sql_text , disk_reads , DENSE_RANK() OVER (ORDER BY disk_reads DESC) AS disk_reads_rank FROM v$sql WHERE disk_reads > 100000 ) WHERE disk_reads_rank <= 5 / SET LINESIZE 80 SET PAGESIZE 14 -- Top_5_poor_parsing_applications -- Ranking Top 5 SQL for poor parsing applications (parse_calls/executions) on v$sqlstats -- parse_calls, Number of parse calls for all cursors with this SQL text and plan -- executions, Number of executions that took place on this object since it was brought into the library cache SET LINESIZE 32767 SET PAGESIZE 50000 COLUMN sql_id FORMAT a13 COLUMN sql_text FORMAT a70 COLUMN parse_calls/executions FORMAT 999,999,999,999,999 HEADING 'parse_calls|executions' COLUMN parse_app_rank FORMAT 999 HEADING 'parse_app|rank' SELECT * FROM (SELECT sql_id , sql_text , parse_calls/executions , DENSE_RANK() OVER (ORDER BY DECODE(executions, 0, 0, parse_calls / executions) DESC) AS parse_app_rank FROM v$sql WHERE DECODE(executions, 0, 0, parse_calls / executions) > 1 ) WHERE parse_app_rank <= 5 / SET LINESIZE 80 SET PAGESIZE 14 -- Top_5_shared_memory -- Ranking Top 5 SQL for shared memory (Memory hogs) on v$sqlstats -- Total shared memory (in bytes) currently occupied by all cursors with this SQL text and plan SET LINESIZE 32767 SET PAGESIZE 50000 COLUMN sql_id FORMAT a13 COLUMN sql_text FORMAT a60 COLUMN sharable_mem FORMAT 999,999,999,999,999 SELECT * FROM (SELECT sql_id , sql_text , sharable_mem , DENSE_RANK() OVER (ORDER BY sharable_mem DESC) AS sharable_mem_rank FROM v$sqlstats WHERE sharable_mem > 10000000 ) WHERE sharable_mem_rank <= 5 / SET LINESIZE 80 SET PAGESIZE 14

评论

贡献排行榜