SQL执行计划绑定

------SQL_ID：1saa0sf9a4ajv
SELECT B.RES_SKU_ID,
B.RES_KIND_ID,
MIN(B.ACCESS_NUMBER) BEG_ID,
MAX(B.ACCESS_NUMBER) END_ID,
COUNT(*) AMOUNT,
B.PRECODE_TAG,
B.POOL_CODE,
B.MGMT_COUNTY,
'0' || B.MGMT_ORG_ID STORE_ID,
B.MGMT_ORG_ID,
B.MGMT_DISTRICT,
B.RES_STATE,
B.MGMT_STATE,
B.ORG_LEVEL,
B.HLR_SEG
FROM (SELECT A.*, TO_NUMBER(A.ACCESS_NUMBER - ROWNUM) CC
FROM (SELECT D.RES_SKU_ID,
D.RES_KIND_ID,
D.POOL_CODE,
D.MGMT_DISTRICT,
D.MGMT_COUNTY,
D.MGMT_ORG_ID,
D.RES_STATE,
D.MGMT_STATE,
D.ORG_LEVEL,
D.PRECODE_TAG,
D.ACCESS_NUMBER,
D.HLR_SEG
FROM RES_NUM_ORIGIN D
WHERE (POOL_CODE IN (:1, :2) AND MGMT_STATE = :3 AND
MGMT_COUNTY = :4 AND
substr(D.ACCESS_NUMBER, 1, 1) = '1' AND
ACCESS_NUMBER >= :5 AND ACCESS_NUMBER <= :6 AND
RES_STATE = :7)
ORDER BY D.RES_SKU_ID,
D.RES_KIND_ID,
D.POOL_CODE,
D.MGMT_ORG_ID,
D.MGMT_DISTRICT,
D.MGMT_COUNTY,
D.RES_STATE,
D.MGMT_STATE,
D.ORG_LEVEL,
D.ACCESS_NUMBER,
D.PRECODE_TAG,
D.HLR_SEG) A) B
GROUP BY B.RES_SKU_ID,
B.RES_KIND_ID,
B.POOL_CODE,
B.MGMT_DISTRICT,
B.MGMT_ORG_ID,
B.MGMT_COUNTY,
B.RES_STATE,
B.MGMT_STATE,
B.ORG_LEVEL,
B.PRECODE_TAG,
B.HLR_SEG,
B.CC
ORDER BY BEG_ID

select sql_id,name,datatype_string,value_string from v$sql_bind_capture where sql_id = '1saa0sf9a4ajv';

vi 1.sh
sqlplus -s '/as sysdba' <<EOF
set timing on
select distinct sid from v\$mystat;
set pages 1000 line 300
alter session set current_schema=uop_res;
variable n1 VARCHAR2(32);
exec :n1:='0';

variable n2 VARCHAR2(32);
exec :n2:='2';

variable n3 VARCHAR2(32);
exec :n3:='1';

variable n4 VARCHAR2(32);
exec :n4:='N45C';

variable n5 VARCHAR2(32);
exec :n5:='15115147423';

variable n6 VARCHAR2(32);
exec :n6:='15115147423';

variable n7 VARCHAR2(32);
exec :n7:='1';
SELECT B.RES_SKU_ID,
B.RES_KIND_ID,
MIN(B.ACCESS_NUMBER) BEG_ID,
MAX(B.ACCESS_NUMBER) END_ID,
COUNT(*) AMOUNT,
B.PRECODE_TAG,
B.POOL_CODE,
B.MGMT_COUNTY,
'0' || B.MGMT_ORG_ID STORE_ID,
B.MGMT_ORG_ID,
B.MGMT_DISTRICT,
B.RES_STATE,
B.MGMT_STATE,
B.ORG_LEVEL,
B.HLR_SEG
FROM (SELECT A.*, TO_NUMBER(A.ACCESS_NUMBER - ROWNUM) CC
FROM (SELECT /*+ INDEX(D IDX_NUM_ORIGIN_ACCESSNUMBER)*/D.RES_SKU_ID,
D.RES_KIND_ID,
D.POOL_CODE,
D.MGMT_DISTRICT,
D.MGMT_COUNTY,
D.MGMT_ORG_ID,
D.RES_STATE,
D.MGMT_STATE,
D.ORG_LEVEL,
D.PRECODE_TAG,
D.ACCESS_NUMBER,
D.HLR_SEG
FROM RES_NUM_ORIGIN D
WHERE (POOL_CODE IN (:n1, :n2) AND MGMT_STATE = :n3 AND
MGMT_COUNTY = :n4 AND
substr(D.ACCESS_NUMBER, 1, 1) = '1' AND
ACCESS_NUMBER >= :n5 AND ACCESS_NUMBER <= :n6 AND
RES_STATE = :n7)
ORDER BY D.RES_SKU_ID,
D.RES_KIND_ID,
D.POOL_CODE,
D.MGMT_ORG_ID,
D.MGMT_DISTRICT,
D.MGMT_COUNTY,
D.RES_STATE,
D.MGMT_STATE,
D.ORG_LEVEL,
D.ACCESS_NUMBER,
D.PRECODE_TAG,
D.HLR_SEG) A) B
GROUP BY B.RES_SKU_ID,
B.RES_KIND_ID,
B.POOL_CODE,
B.MGMT_DISTRICT,
B.MGMT_ORG_ID,
B.MGMT_COUNTY,
B.RES_STATE,
B.MGMT_STATE,
B.ORG_LEVEL,
B.PRECODE_TAG,
B.HLR_SEG,
B.CC
ORDER BY BEG_ID;
exit;
EOF

sh 1.sh > 1.log

select prev_sql_id from v$session where sid=xxx;

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/ */outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = 'g33nj4vjpxdu7'    --手动加hint执行的sql_id
and PLAN_HASH_VALUE = 4234089204  --手动加hint执行的sql_id hash value
and rownum=1
and other_xml is not null)) d;

select sql_fulltext
into cl_sql_text
from v$sql
where sql_id = '1saa0sf9a4ajv'     --原始SQL（效率差）的sql_id
and rownum=1
and PLAN_HASH_VALUE = 2369504654; --原始SQL（效率差）的hash value

dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,
profile => ar_profile_hints,
name => 'profile_1saa0sf9a4ajv_sql', ---自定义名称，不能和现有的冲突
force_match => true,
replace => true);
end;
/