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 約束