Tips: 快速查询定位最慢的SQL

2019-11-08 20:56:04

字体：大中小

来源：转载

供稿：网友

最耗时SQL:

SELECT * FROM (SELECT C.SQL_ID, (C.ETIME - NVL(O.ELAPSED_TIME_TOTAL, 0)) / 1000000 ELAPSED_DELTA_TIME FROM (SELECT * FROM (SELECT SQL_ID, PLAN_HASH_VALUE, SUM(ELAPSED_TIME) ETIME FROM V$SQL S WHERE S.PLAN_HASH_VALUE <> 0 GROUP BY SQL_ID, PLAN_HASH_VALUE HAVING MAX(LAST_ACTIVE_TIME) > SYSDATE - 1 / 24 ORDER BY SUM(ELAPSED_TIME) DESC) WHERE ROWNUM < 100) C, (SELECT SQL_ID, ELAPSED_TIME_TOTAL, PLAN_HASH_VALUE FROM DBA_HIST_SQLSTAT HIS WHERE HIS.SNAP_ID = (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT) AND HIS.INSTANCE_NUMBER = USERENV('instance')) O WHERE C.SQL_ID = O.SQL_ID AND C.PLAN_HASH_VALUE = O.PLAN_HASH_VALUE ORDER BY ELAPSED_DELTA_TIME DESC) WHERE ROWNUM < 10;

会得到类似下面的结果：

SQL_ID ELAPSED_DELTA_TIME--------------------------------------- ------------------42bd8gf0xd720 2765.146325fr49k0t6qdz6c .91098661xywzr81rbm4 .4592341yd0xckwg8mrq .295755ag7apfq5716hc .270697f9rzyzsypmnkm .267293cr777xwby6a5d .1741715y6xxf087crn5 .168b6km56yym0gtv .152382

2 . 得到SQL_ID “42bd8gf0xd720”, 根据SQLID 拉取执行计划，即可根据执行计划分析:

select * from table(dbms_xplan.display_awr('42bd8gf0xd720'));

3 . 如果知道sql语句，也可以通过explan plan for+语句拉取执行计划如果要添加索引优化，记得加 online

CREATE INDEX "USERNAME"."IDX_索引名" ON "拥有者"."表名" ("列名") online;

4 .调优后如果添加了index或者做了改动，要重新收集下表的统计信息：

exec dbms_stats.gather_table_stats(ownname => '表拥有着',tabname => '表名' ,method_opt => 'for all indexed columns' ,cascade => true);

上一篇：pgsql操作数据库命令

下一篇：SQL 约束