某个sql query 跑太慢了，做调试。思路就是找到好的执行计划，用baseline固定住。
select sql_id, sql_fulltext From v$sql WHERE sql_fulltext LIKE ‘’; #找到sql_id
Select inst_id, sid, serial#, program, sql_id, machine, status, TO_CHAR(sql_exec_start, ‘MM-DD HH24:MI:SS’) AS startdt, (SYSDATE - sql_exec_start)*24*60*60 during FROM gv$session WHERE surname IS NOT NULL AND sql_id=’123456’ ORDER BY during DESC; #找到运行时间最长的sql）
select sql_text, hash_value FROM v$sqltext WHERE sql_id=”123456” ORDER BY piece;
Select * FROM table(dbms_xplan.display_cursor(‘123456’)); #从memory中找到该sql的执行plan
1980047261
VS
SELECT distinct plan_hash_value FROM dba_hist_sqlstat WHERE sql_id=’123456’; #从磁盘（disk）中找到该sql的历史PHV
PLAN_HASH_VALUE
-------------------------
377475139
发现两个不一样。
SELECT snap_id, plan_hash_value, sql_id FROM dba_hist_sqlstat WHERE sql_id=’123456’ and plan_hash_value=’123456’ ORDER BY 1;#找到snap_id
SELECT max(snap_id), plan_hash_value FROM dba_hist_sqlstat WHERE sql_id=’123456’; #找到所有的snap_id。
SELECT * FROM table(dbms_xplan.display_awr(‘123456’,1980047261);
SELECT object_id, owner, object_name, object_type FROM dba_objects WHERE object_name=(‘rar’); # 查看rar表的一些信息
EXEC dbms_stats.gather_table_stats(owname=>’ESP’, tabname=>’rar’, degree=>16, cascade=>True);
-- if you want to find the different execution plans of one query, use the below query. Then you can compare the execution plan to find the one better than current one.
SELECT plan_hash_value, MAX(snap_id) AS snapid FROM dba_hist_sqlstat WHERE sql_id='ak5wqatb2a317' GROUP BY plan_hash_value ORDER BY snapid;
SELECT distinct plan_hash_value FROM dba_hist_sqlstat WHERE sql_id='ak5wqatb2a317';
-- if you find an execution plan is better than current one, you want to apply that better and old execution plan into current query, use the below code. You need to find the snap id, plan_hash_value before you run the below code.
EXEC dbms_sqltune.create_sqlset(sqlset_name=>'DEVINSENROLLMENT', description=>'DevInsEnrollment Job');
SET serveroutput on
DECLARE
baseline_ref DBMS_SQLTUNE.SQLSET_CURSOR;
ret PLS_INTEGER;
BEGIN
OPEN baseline_ref FOR select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap=>211860,
end_snap=>211861,
basic_filter=>'sql_id='||CHR(39)||'3xq60zrkwksq1'||CHR(39)||'
and plan_hash_value=2259226081',
attribute_list=>'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('DEVINSENROLLMENT',baseline_ref);
ret := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name=>'DEVINSENROLLMENT',
sqlset_owner=>'SYS',
fixed=>'YES',
enabled=>'YES');
DBMS_OUTPUT.PUT_LINE(' Return ret: ' || ret);
END;
/
EXEC dbms_sqltune.drop_sqlset(sqlset_name=>'DEVINSENROLLMENT');