[20221130]优化备库dg遇到的问题2.txt

来源：这里教程网时间：2026-03-03 18:12:29 作者：

[20221130]优化备库dg遇到的问题2.txt --//生产系统一些语句执行慢,开发或者同事移动到备库执行,我发现实际这些语句大部分都是由于选择错误的索引导致的情况. --//我想通过sql profile在主库来稳定执行计划,这样备库也可以使用sql profile选择合理的执行计划.但是我遇到一些问题,做一些记 --//录. 1.环境: SYS@192.168.100.237:1521/orcldg> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 19.0.0.0.0 BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.问题语句: SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID bu48z014njcg4, child number 0 ------------------------------------- select case a.hosp_id when 141 then 1 when 181 then 3 when 201 then 2 end hosp_code,a.id test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode a.pat_barcode pat_id,''INPATIENT_ID,'' CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age ,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO a.depart_name ,a.area_code ,a.area_name ,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS, a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,'' ''_STATUS_NAME,'' SAMPLING_POSITION, '' SAMPLE_CHARGE,'''' TEST_ORDER, _COUNT,''WORKLOAD, a.test_user_name, a.print_user_name, a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh Plan hash value: 86349049 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 13 (100)| | | | | | 1 | SORT ORDER BY | | 1 | 368 | | | 52224 | 52224 |47104 (0)| | 2 | HASH UNIQUE | | 1 | 368 | 13 (8)| 00:00:01 | 714K| 714K| 1390K (0)| |* 3 | FILTER | | | | | | | | | | 4 | NESTED LOOPS | | 1 | 368 | 12 (0)| 00:00:01 | | | | | 5 | NESTED LOOPS OUTER | | 1 | 362 | 9 (0)| 00:00:01 | | | | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 281 | 6 (0)| 00:00:01 | | | | |* 7 | INDEX RANGE SCAN | IX_LIS_TEST_ORDER_TIME | 3 | | 3 (0)| 00:00:01 | | | | | 8 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT | 1 | 81 | 3 (0)| 00:00:01 | | | | |* 9 | INDEX RANGE SCAN | IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)| 00:00:01 | | | | |* 10 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | 60 | 3 (0)| 00:00:01 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$4439918E 6 - SEL$4439918E / A@SEL$2 7 - SEL$4439918E / A@SEL$2 8 - SEL$4439918E / C@SEL$3 9 - SEL$4439918E / C@SEL$3 10 - SEL$4439918E / B@SEL$2 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (CHAR(30), CSID=852): '91237433' 2 - :2 (CHAR(30), CSID=852): '452127198107122110' 3 - (CHAR(30), CSID=852): '2022-11-18' 4 - (CHAR(30), CSID=852): '2022-11-24' Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd')) 6 - filter((("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR "A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) AND "A"."STATE">=60)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 7 - access("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd')) 9 - access("A"."ID"="C"."TEST_ID") 10 - access("A"."ID"="B"."ID") --//选择错误的索引IX_LIS_TEST_ORDER_TIME,应该选择IDENTITY_ID以及PAT_ID索引,首先这些索引是存在的. --//但是我首先遇到奇怪的问题. 3.生成执行脚本: SYS@192.168.100.237:1521/orcldg> @ b9 bu48z014njcg4 0 variable STR_MZHM VARCHAR2(32) variable STR_SFZH VARCHAR2(128) variable STR_DTFROM VARCHAR2(32) variable STR_DTTO VARCHAR2(32) begin :STR_MZHM := '91209282'; :STR_SFZH := '450802198909083618'; :STR_DTFROM := '2022-11-07'; :STR_DTTO := '2022-11-29'; null; end; / set termout off set sqlblanklines on alter session set current_schema=FINDREPORT; alter session set statistics_level=all; select distinct re.* from ( select case a.hosp_id when 141 then 1 when 181 then 3 when 201 then 2 end hosp_code,a.id test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode ,a.pat_type_name , a.pat_barcode pat_id,''INPATIENT_ID,'' CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age ,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLOODTYPE_RH,a.depart_code , a.depart_name ,a.area_code ,a.area_name ,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS, a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,'' SAMPLE_STATUS_NAME,'' SAMPLING_POSITION, '' SAMPLING_POSITION_NAME,'' TEST_ORDER, '' SAMPLE_CHARGE,'' ITEM_COUNT,''WORKLOAD, a.test_user_name, a.print_user_name, a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , a.sign_user_name , ''INSPECTION_PERSON, a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON, a.print_time ,''PRINT_COUNT,''EXPERT_RULE,''CHARGE_STATE,''INSPECTION_STATE,''RERUN_STATE,''READ_STATE, dbms_lob.substr(VERDICT) order_remark, ''REMARK_NAME,''ID_CARD,''CONFIRM_PERSON,''CONFIRM_TIME,''PATIENT_TYPE_OLD,''INSTRUMENT,''MOBILENO,''SEND_STATE,''SEND_DATE,''PATIENT_BIRTHDAY,a.report_type ,a.id RESID, case when a.state=90 then '已报告' when a.state=60 then '正在检验' when a.state=100 then '已打印' else '' end INSPECTIONSTATE,''YCBS,''runow from lis_test a inner join lis_result b on a.id=b.id left join lis_prompt c on a.id=c.test_id where ( a.pat_id= :str_mzhm or a.identity_id= :str_sfzh ) and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd') and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd') and a.state >=60 )re order by re.ORDER_TIME; set termout on set sqlblanklines off --@zws '' '' --@dpc '' '' @dpc '' outline rollback; alter session set current_schema=SYS ; --//而当我执行生成的脚本时,我发现里面记录的sql_id不是bu48z014njcg4,而是76wprdhujxj54,怎么可能不同呢? --//首先我不可能在备库执行spsw.sql脚本来交换执行计划来稳定提高性能,因为要在read write模式,我必须在主库执行. --//而sql_id=bu48z014njcg4的语句并不存在主库,主库执行后仅仅存在sql_id=76wprdhujxj54的语句. --//这样我在主库执行 --//@ spsw good_sql_id 0 76wprdhujxj54 0 '' true 是否到备库sql_id=bu48z014njcg4可以应用. --//注:最后我的测试可以.只要最后一个参数(第6个参数)为true. --//我执行的脚本b9.sql在这个语句中仅仅过滤掉里面\r字符,使用replace替换chr(13)=>''.看来以后给修改脚本代码改成替换为空格!! SYS@192.168.100.237:1521/orcldg> select lengthb(to_char(sql_fulltext)),length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54'); LENGTHB(TO_CHAR(SQL_FULLTEXT)) LENGTH(SQL_FULLTEXT) ORA_HASH(SQL_FULLTEXT) SQL_ID ------------------------------ -------------------- ---------------------- ------------- 1816 1806 3132263513 bu48z014njcg4 1777 1767 2094332972 76wprdhujxj54 --//注:应该使用lengthb函数查看长度,我开始在这里犯错.因为语句里面有汉字.10个汉字多了10个长度. --//1816-1777 = 39.两组存在39个字符的差距. --//在toad下执行: select length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id ,sql_fulltext from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54'); --//在toad下分别取出sql_fulltext输出,分别对应的保存为d1.bu48z014njcg4,d2.76wprdhujxj54文件. $ ls -l d1.bu48z014njcg4 d2.76wprdhujxj54 -rw-r--r-- 1 oracle oinstall 1790 2022-11-30 10:16:32 d1.bu48z014njcg4 -rw-r--r-- 1 oracle oinstall 1778 2022-11-30 10:16:52 d2.76wprdhujxj54 --//d1.bu48z014njcg4,d2.76wprdhujxj54有12个字符上的差异. --//1816-1790 = 26,这26个字符应该是\r字符. $ diff d1.bu48z014njcg4 d2.76wprdhujxj54 | cat -Ev 8,9c8,9$ < $ < '' SAMPLING_POSITION_NAME,'' TEST_ORDER, $ ---$ > $ > '' SAMPLING_POSITION_NAME,'' TEST_ORDER,$ 11,12c11,12$ < $ < a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , $ ---$ > $ > a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,$ 14,15c14,15$ < ''INSPECTION_PERSON, $ < a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON, $ ---$ > ''INSPECTION_PERSON,$ > a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON,$ 21,22c21,22$ < from lis_test a $ < inner join lis_result b on a.id=b.id $ ---$ > from lis_test a$ > inner join lis_result b on a.id=b.id$ 25,26c25,26$ < and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd') $ < and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd') $ ---$ > and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd')$ > and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd')$ --//看上去一样的,cat -E可以在结尾加入$,实际上有一些行结尾处有一些行包含空格.也就是差异的12个字符实际上空格.换一句话将 --//b9.sql脚本的输出时结尾的空格丢失了. --//我的缺省设置: --//set trimout on; -- 去除标准输出每行的行尾空格(缺省为off) --//set trimspool on; -- 去除spool输出结果中每行的结尾空格(缺省为off) SYS@192.168.100.237:1521/orcldg> show trimspool trimspool ON SYS@192.168.100.237:1521/orcldg> show trimout trimout ON --//我测试了set trimout off好像也不行呢?.另外写blog说明问题. --//如果加入-b参数,两者一致. --// -b Ignore changes in amount of white space --// -w Ignore white space when comparing lines. $ diff -b d1.bu48z014njcg4 d2.76wprdhujxj54 $ diff -w d1.bu48z014njcg4 d2.76wprdhujxj54 $ wc d1.bu48z014njcg4 d2.76wprdhujxj54 28 133 1790 d1.bu48z014njcg4 28 133 1778 d2.76wprdhujxj54 56 266 3568 total --//我仔细在toad下看了sql_fulltext的dump发现bu48z014njcg4每行结尾有\r字符.这样 --//1816-1790 = 26,应该多27个才对啊(最好1行不算).怎么差1个字符呢? --//仔细看76wprdhujxj54,lengthb输出的长度是1777,d2.76wprdhujxj54的文件大小1778,也是差1,这样就很好理解了,因为保存文件 --//时最后一行结尾存在\n字符,多1个是正常的. --//这样就完成对上了,我的b9脚本输出过滤chr(13)字符(\r),并且因为sqlplus的输出少了12个空格. --//27 chr(13) + 12 空格 = 39. --//1816-1777 = 39.这样正好能对上了. --//不过即使我修改生成的脚本加入\r字符在sqlplus结尾,在sqlplus执行的sql_id还是76wprdhujxj54. --//顺便做一个记录vim下在结尾加入^M的方法,打入:'a,'bs/$/\^M/ --//前面的\不能缺少,不然文本仅仅全部多一个空行. --//^M 输入 ctrl+q ctrl+m (for windwos) 或者 ctrl+v ctrl+m ( for linux) --//不过最后我发现我自己多虑了,实际上在主库执行如下: --//先运行bu48z014njcg4.sql9_0 => sql_id=76wprdhujxj54 --//加入提示: BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID"))) --//再运行bu48z014njcg4.sql9_0 -> sql_id=86uxv50rvtb3x SYS@192.168.100.235:1521/orcl> @ spsw 86uxv50rvtb3x 0 76wprdhujxj54 0 '' true PL/SQL procedure successfully completed. --//主要第6个参数等于true,就没有问题. SYS@192.168.100.235:1521/orcl> update (select * from sqlobj$auxdata where created<>to_char(created,'yyyy-mm-dd hh24:mi:ss')) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss'); 1 row updated. SYS@192.168.100.235:1521/orcl> commit ; Commit complete. --//为了在toad下维护sql profile. --//然后在备库执行或者查看sql_id=bu48z014njcg4的执行计划,会使用对应的sql profile. SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID bu48z014njcg4, child number 1 ------------------------------------- select case a.hosp_id when 141 then 1 when 181 then 3 when 201 then 2 end hosp_code,a.id test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode a.pat_barcode pat_id,''INPATIENT_ID,'' CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age ,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO a.depart_name ,a.area_code ,a.area_name ,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS, a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,'' ''_STATUS_NAME,'' SAMPLING_POSITION, '' SAMPLE_CHARGE,'''' TEST_ORDER, _COUNT,''WORKLOAD, a.test_user_name, a.print_user_name, a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh Plan hash value: 518087822 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 21 (100)| | | | | | 1 | SORT ORDER BY | | 1 | 368 | 21 (15)| 00:00:01 | 74752 | 74752 |65536 (0)| | 2 | HASH UNIQUE | | 1 | 368 | 20 (10)| 00:00:01 | 715K| 715K| 1388K (0)| |* 3 | FILTER | | | | | | | | | | 4 | NESTED LOOPS | | 1 | 368 | 19 (6)| 00:00:01 | | | | | 5 | NESTED LOOPS OUTER | | 1 | 362 | 16 (7)| 00:00:01 | | | | |* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 281 | 13 (8)| 00:00:01 | | | | | 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | 8 | BITMAP OR | | | | | | | | | | 9 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | | 10 | SORT ORDER BY | | | | | | 73728 | 73728 | | |* 11 | INDEX RANGE SCAN | IX_LIS_TEST_IDENTITY_ID | | | 4 (0)| 00:00:01 | | | | | 12 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | |* 13 | INDEX RANGE SCAN | IX_LIS_TEST_PAT_ID | | | 3 (0)| 00:00:01 | | | | | 14 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT | 1 | 81 | 3 (0)| 00:00:01 | | | | |* 15 | INDEX RANGE SCAN | IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)| 00:00:01 | | | | |* 16 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | 60 | 3 (0)| 00:00:01 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$4439918E 6 - SEL$4439918E / A@SEL$2 14 - SEL$4439918E / C@SEL$3 15 - SEL$4439918E / C@SEL$3 16 - SEL$4439918E / B@SEL$2 Peeked Binds (identified by position): -------------------------------------- 1 - :1 (CHAR(30), CSID=852): '91240481' 2 - :2 (CHAR(30), CSID=852): '452128198512081523' 3 - (CHAR(30), CSID=852): '2022-11-19' 4 - (CHAR(30), CSID=852): '2022-12-01' Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd')) 6 - filter(("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd') AND "A"."STATE">=60)) 11 - access("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH)) filter("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH)) 13 - access("A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) 15 - access("A"."ID"="C"."TEST_ID") 16 - access("A"."ID"="B"."ID") Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 17 (U - Unused (1)) --------------------------------------------------------------------------- 0 - STATEMENT - ALL_ROWS - DB_VERSION('19.1.0') - IGNORE_OPTIM_EMBEDDED_HINTS - OPTIMIZER_FEATURES_ENABLE('19.1.0') 0 - SEL$2 - MERGE(@"SEL$2" >"SEL$006708EA") 0 - SEL$98196233 - ANSI_REARCH(@"SEL$3") - MERGE(@"SEL$98196233" >"SEL$1") 0 - SEL$C97233AF - MERGE(@"SEL$C97233AF" >"SEL$E6E74641") 1 - SEL$4439918E - ANSI_REARCH(@"SEL$4") - LEADING(@"SEL$4439918E" "A"@"SEL$2" "C"@"SEL$3" "B"@"SEL$2") 6 - SEL$4439918E / A@SEL$2 U - BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID"))) - BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "A"@"SEL$2") 14 - SEL$4439918E / C@SEL$3 - BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "C"@"SEL$3") - INDEX_RS_ASC(@"SEL$4439918E" "C"@"SEL$3" ("LIS_PROMPT"."TEST_ID")) - USE_NL(@"SEL$4439918E" "C"@"SEL$3") 16 - SEL$4439918E / B@SEL$2 - INDEX(@"SEL$4439918E" "B"@"SEL$2" ("LIS_RESULT"."ID" "LIS_RESULT"."ITEM_ID")) - USE_NL(@"SEL$4439918E" "B"@"SEL$2") Note ----- - SQL profile switch tuning 76wprdhujxj54 used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 116 rows selected. 3.附上spsw.sql脚本: $ cat spsw.sql -- @create_profile_from_shared_pool c2trqja6wh561 0 TEST true -- @spsw good_sql_id 0 bad_sql_id 0 test true -- @spsw good_sql_id 0 bad_sql_id 0 '' true 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 = '&&1' AND child_number = &&2 AND other_xml IS NOT NULL)) d; SELECT SQL_FULLTEXT INTO cl_sql_text FROM -- replace with dba_hist_sqltext -- if required for AWR based -- execution v$sqlarea -- sys.dba_hist_sqltext WHERE sql_id = '&&3'and rownum=1; -- plan_hash_value = &&2; DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text, profile => ar_profile_hints, category => '&&5', DESCRIPTION => 'switch &&1 => &&3', name => 'switch tuning &&3' -- use force_match => true -- to use CURSOR_SHARING=SIMILAR -- behaviour, i.e. match even with -- differing literals , force_match => &&6); END; /

编辑推荐：

返回列表

[20221130]优化备库dg遇到的问题2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推