SPM相关
---当前执行中的SPM
select distinct a.sql_id,
a.PLAN_HASH_VALUE,
b.PLAN_NAME,
b.sql_handle,
a.SQL_PLAN_BASELINE,
a.OBJECT_STATUS,
b.ENABLED,
b.ACCEPTED,
b.FIXED
from v$sql a, dba_sql_plan_baselines b
where a.sql_id = 'a14y1zs2t6uxz'
and a.EXACT_MATCHING_SIGNATURE = b.signature
and a.PLAN_HASH_VALUE = '2147322827';
---当前执行中的SPM
select *
from dba_sql_plan_baselines b
where b.signature = (select distinct EXACT_MATCHING_SIGNATURE
from v$sql
where sql_id = '81bddu5k7s2pc')
order by created desc;
select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED
from dba_sql_plan_baselines b
where b.signature = (select distinct EXACT_MATCHING_SIGNATURE
from v$sql
where sql_id = '81bddu5k7s2pc')
order by created desc;
---查看SPM的执行计划
select *
from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_424e8458321deb48',
plan_name => 'SQL_PLAN_44mn4b0t1vuu861430fdb'));
---当前内存中SQL效率情况；
select CHILD_NUMBER,
PLAN_HASH_VALUE,
EXECUTIONS,
CPU_TIME / A.EXECUTIONS,
A.BUFFER_GETS / A.EXECUTIONS,
a.SQL_FULLTEXT,
a.PARSING_SCHEMA_NAME,a.SQL_FULLTEXT,a.SQL_PLAN_BASELINE,a.OBJECT_STATUS
from v$sql A
where sql_id = '4rp0wh263x4m6';
---parse user
select a.PARSING_SCHEMA_NAME from v$sql a
where a.sql_id='afxvxz2mw2rxu';
----内存中执行计划
select * from table(dbms_xplan.display_cursor('c0wnumuc2nu4p',2,'ADVANCED'));
---AWR中获取执行计划
select * from table(dbms_xplan.display_awr
('9v3u2vxx3a8cr','1814554837','203185647','ADVANCED'));
---查看索引对应的列情况
select * from dba_ind_columns
where table_name='HS_ESSR_RATE_JOB';
---SQL历史执行情况
select snap_id,
date_time,
plan_hash,
executions,
trunc(avg_etime_s, 2),
trunc(avg_lio, 2),
trunc(avg_pio, 2),
trunc(avg_cputime_s, 2),
trunc(avg_row)
from (select distinct s.snap_id,
to_char(s.begin_interval_time, 'yyyy-mm-dd hh24:mi') ||
to_char(s.end_interval_time, '-----hh24:mi') date_time,
sql.plan_hash_value plan_hash,
sql.executions_delta executions,
(sql.elapsed_time_delta / 1000000) /
decode(sql.executions_delta,
null,
1,
0,
1,
sql.executions_delta) avg_etime_s,
sql.buffer_gets_delta /
decode(sql.executions_delta,
null,
1,
0,
1,
sql.executions_delta) avg_lio,
sql.disk_reads_delta /
decode(sql.executions_delta,
null,
1,
0,
1,
sql.executions_delta) avg_pio,
(sql.cpu_time_delta / 1000000) /
decode(sql.executions_delta,
null,
1,
0,
1,
sql.executions_delta) avg_cputime_s,
sql.rows_processed_total /
decode(sql.executions_delta,
null,
1,
0,
1,
sql.executions_delta) avg_row
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql.instance_number =
(select instance_number from v$instance)
and sql.dbid = (select dbid from v$database)
and s.snap_id = sql.snap_id
and sql_id = trim('c0wnumuc2nu4p')
order by s.snap_id desc)
where rownum '3u0hdmwqbfqnw',PLAN_HASH_VALUE => '1994681556',SQL_HANDLE=> 'SQL_ec04b01d965e5caa');
---drop spm
var x number;
exec 😡 := dbms_spm.drop_sql_plan_baseline(sql_handle =>'SQL_e336c21d41b8aec3',plan_name => 'SQL_PLAN_f6dq23p0vjbq38cda8725');
---evolve spm
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => null);
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_6683a21397ad6dd9',PLAN_NAME => 'SQL_PLAN_6d0x22fbuuvft48f9dbfa',verify=>'YES',commit=>'YES');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
--alter spm
var x number;
exec 😡 := dbms_spm.ALTER_SQL_PLAN_BASELINE(sql_handle =>'SQL_e336c21d41b8aec3',plan_name => 'SQL_PLAN_f6dq23p0vjbq38cda8725',attribute_name => 'ENABLED',attribute_value => 'NO');
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle =>'&handle', plan_name =>'&plan_name', attribute_name =>'ENABLED', attribute_value=>'NO');
dbms_output.put_line(my_plans);
END;
/
----FIX SPM
var x number;
exec 😡 := dbms_spm.ALTER_SQL_PLAN_BASELINE(sql_handle =>'SQL_be245db2b0081c92',plan_name => 'SQL_PLAN_bw92xqas0h74kb18a12b5',attribute_name => 'FIXED',attribute_value => 'YES');
---查询历史的spm信息
select s.sql_id,
b.sql_handle,
b.plan_name,
b.signature,
b.enabled,
b.accepted,
b.fixed,b.parsing_schema_name,dbmgr.pkg_manage_spm.spm_to_plan_hash_value(plan_name)
from (select distinct sql_id, t.FORCE_MATCHING_SIGNATURE
from dba_hist_sqlstat t where t.sql_id = 'bpdbzhdvg92ax') s
JOIN dba_sql_plan_baselines b on (s.FORCE_MATCHING_SIGNATURE =
b.signature)
and sql_id = 'bpdbzhdvg92ax';
---手动load spm
VAR B2 VARCHAR2(128);
VAR B1 VARCHAR2(128);
EXEC :B2 := 'GP02002048921959';
EXEC :B1 := 'PC02001111495846';
SELECT T.*
FROM (SELECT (SELECT /*+index(PB PK_POL_BEN) */ DISTINCT
PB.EFF_DATE,
PB.MATU_DATE,
PB.BEN_STS,
PM.APPNO,
PB.CERTNO,
SUBSTR (PB.PLAN_CODE, 0, 5) PLAN_CODE,
MIN (PB.GRADE_LEVEL)
OVER (PARTITION BY PB.CERTNO, SUBSTR (PB.PLAN_CODE, 0, 5))
GRADE_LEVEL,
ROW_NUMBER ()
OVER (PARTITION BY PB.CERTNO, SUBSTR (PB.PLAN_CODE, 0, 5)
ORDER BY PB.POLNO DESC)
TID
FROM POL_BEN PB, POL_MAIN PM, POL_CERT PC
WHERE PB.POLNO = PM.POLNO
AND PB.POLNO = :B2
AND PB.CERTNO = NVL (:B1, PB.CERTNO)
AND PC.CERTNO = PB.CERTNO
AND PC.NAMED_FLAG 'N'
AND PC.POLNO PC.CERTNO) T
WHERE T.TID = '1';
-----替换执行计划
var x number;
exec 😡 := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=> '3u0hdmwqbfqnw',PLAN_HASH_VALUE => '1994681556',SQL_HANDLE=> 'SQL_ec04b01d965e5caa');
SQL_HANDLE是原来坏的。
SQL_ID与plan_hash_value是优化后的。
---disable spm
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle =>'&handle', plan_name =>'&plan_name', attribute_name =>'ENABLED', attribute_value=>'NO');
dbms_output.put_line(my_plans);
END;
/
--从awr创建SQLSET
exec DBMS_SQLTUNE.CREATE_SQLSET('test');
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(263, 264,'sql_id='||CHR(39)||'5yv7yvjgjxugg'||CHR(39)||' and plan_hash_value=3429127057',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('test', baseline_ref_cursor);
end;
/
--从SQLSET加载到SPM
set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset(sqlset_name => 'test', sqlset_owner => 'SYS', fixed => 'NO', enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/
--查看是否导入成功
col sql_handle for a35
col plan_name for a35
col origin for a40
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%tb_spm_test_lhr%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE signature in(select dbms_sqltune.sqltext_to_signature(sql_text)
from dba_hist_sqltext
where sql_id='&sql_id')
order by created desc;
查看sql执行计划是否正确
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle =>'&sql_handle',plan_name =>'&plan_name',format =>'ALL'));
清空某个sql id的cursor
select PLAN_HASH_VALUE,q'[exec sys.dbms_shared_pool.purge(']'||address||','||hash_value||q'[','C');]' as flush_sql
from gv$sqlarea where sql_id='9c40p210jpx6g';
得到刷新语句后直接执行即可。
