\n\t–\u67e5\u627e\u4f4e\u6548sql\n<\/p>\n
\n\tSELECT EXECUTIONS,
\n\t DISK_READS,
\n\t BUFFER_GETS,
\n\t ROUND((BUFFER_GETS – DISK_READS) \/ BUFFER_GETS, 2) Hit_radio,
\n\t ROUND(DISK_READS \/ EXECUTIONS, 2) Reads_per_run,
\n\t SQL_TEXT
\n\t FROM V$SQLAREA
\n\t WHERE EXECUTIONS > 0
\n\t AND BUFFER_GETS > 0
\n\t AND (BUFFER_GETS – DISK_READS) \/ BUFFER_GETS < 0.8
\n\t ORDER BY 4 DESC;\n<\/p>\n
\n\t–\u67e5\u627ebad sql\u7684\u65b9\u6cd5\uff1a\n<\/p>\n
\n\tselect *
\n\t from (select buffer_gets, sql_text
\n\t from v$sqlarea
\n\t where buffer_gets > 500000
\n\t order by buffer_gets desc)
\n\t where rownum <= 30;\n<\/p>\n
\n\t—\u6267\u884c\u6b21\u6570\u591a\u7684SQL\uff1a\n<\/p>\n
\n\tselect sql_text, executions
\n\t from (select sql_text, executions from v$sqlarea order by executions desc)\n<\/p>\n
\n\t where rownum < 81;\n<\/p>\n
\n\t–\u8bfb\u786c\u76d8\u591a\u7684SQL\uff1a\n<\/p>\n
\n\tselect sql_text, disk_reads
\n\t from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)\n<\/p>\n
\n\t where rownum < 21;\n<\/p>\n
\n\t–\u6392\u5e8f\u591a\u7684SQL\uff1a\n<\/p>\n
\n\tselect sql_text, sorts
\n\t from (select sql_text, sorts from v$sqlarea order by sorts desc)\n<\/p>\n
\n\t where rownum < 21;\n<\/p>\n
\n\t–\u5206\u6790\u7684\u6b21\u6570\u592a\u591a\uff0c\u6267\u884c\u7684\u6b21\u6570\u592a\u5c11\uff0c\u8981\u7528\u7ed1\u53d8\u91cf\u7684\u65b9\u6cd5\u6765\u5199sql\uff1a\n<\/p>\n
\n\tset pagesize 600;\n<\/p>\n
\n\tset linesize 120;\n<\/p>\n
\n\tselect substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"\n<\/p>\n
\n\t from v$sqlarea\n<\/p>\n
\n\t where executions < 5\n<\/p>\n
\n\t group by substr(sql_text, 1, 80)\n<\/p>\n
\n\thaving count(*) > 30\n<\/p>\n
\n\t order by 2;\n<\/p>\n
\n\t–\u6e38\u6807\u7684\u89c2\u5bdf\uff1a\n<\/p>\n
\n\tset pages 300;\n<\/p>\n
\n\tselect sum(a.value), b.name\n<\/p>\n
\n\t from v$sesstat a, v$statname b\n<\/p>\n
\n\t where a.statistic# = b.statistiC#
\n\t
\n\t and b.name = 'opened cursors current'\n<\/p>\n
\n\t group by b.name;\n<\/p>\n
\n\tselect count(0) from v$open_cursor;\n<\/p>\n
\n\tselect user_name, sql_text, count(0)
\n\t from v$open_cursor\n<\/p>\n
\n\t group by user_name, sql_text
\n\thaving count(0) > 30;\n<\/p>\n
\n\t–\u67e5\u770b\u5f53\u524d\u7528\u6237&username\u6267\u884c\u7684SQL\uff1a\n<\/p>\n
\n\tselect sql_text
\n\t from v$sqltext_with_newlines
\n\t where (hash_value, address) in
\n\t (select sql_hash_value, sql_address
\n\t from v$session
\n\t where username = '&username')
\n\t order by address, piece;<\/p>\n","protected":false},"excerpt":{"rendered":"
–\u67e5\u627e\u4f4e\u6548sql SELECT EXECUTIONS, &nb … \u7ee7\u7eed\u9605\u8bfb\u67e5\u51fa\u4f4e\u6548\u7684SQL<\/span>