明天终于说到了SPM，一个ORACLE官网举荐应用的SQL执行打算固定办法。为什么最常应用的办法最初说呢？因为介绍SPM应用办法的公开材料最多，ORACLE提供的接口也最丰盛，用一篇几百字的小文进行阐明，总感觉会挂一漏万，以偏概全。
然而思考再三，作为ORACLE官网鼎力举荐和继续开发欠缺的固定执行打算的次要伎俩，还是不能不说的。
还是像前几个办法一样，咱们用一个小的测试例子来阐明SPM的应用办法。
1.做成测试用Table。
conn test/test@localhost:1521/pdbcreate table tab2(c1 number, c2 number, c3 varchar2(10));declare a number;begin a := 1; for i in 1 .. 50 loop for j in 1 .. 100 loop insert into tab2 values(a,j,'a'); commit; a := a+1; end loop; end loop;end;/create index ind2_2 on tab2(c2);exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB2',cascade=>TRUE);2.执行原始SQL文。
set autot onset lin 120 pages 999select count(*) from tab2 where c2=1; COUNT(*)---------- 50実行計画----------------------------------------------------------Plan hash value: 3563712581----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | ||* 2 | INDEX RANGE SCAN| IND2_2 | 50 | 150 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("C2"=1)統計---------------------------------------------------------- 4 recursive calls 25 db block gets 4 consistent gets 0 physical reads 832 redo size 573 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed3.查看对象SQL文的sql_id和plan_hash_value。
conn sys/*****@localhost:1521/pdbselect sql_id, child_number, plan_hash_value from v$sql where sql_text = 'select count(*) from tab2 where c2=1';SQL_ID CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------3uat1k9ssur9p 0 35637125814.把对象SQL文的执行打算Load进SPM。
SQL> var cnt number;SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id', plan_hash_value => '&plan_hash_value');sql_idに値を入力してください: 3uat1k9ssur9pplan_hash_valueに値を入力してください: 3563712581PL/SQLプロシージャが失常に完了しました。SQL> print :cnt CNT---------- 15.查看SQLPLAN的BaseLine。
col sql_handle for a30col SQL_TEXT for a50col PLAN_NAME for a30set lin 120 pages 999select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';SQL_HANDLE SQL_TEXT PLAN_NAME FIX------------------------------ -------------------------------------------------- ------------------------------ ---SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 NO6.固定执行打算。
SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES');sql_handleに値を入力してください: SQL_156e46a7ed64e33dplan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stx910d0b22PL/SQLプロシージャが失常に完了しました。SQL> select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';SQL_HANDLE SQL_TEXT PLAN_NAME FIX------------------------------ -------------------------------------------------- ------------------------------ ---SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 YES7.执行原始SQL文，查看SQLPLAN BASELINE是否被应用。
conn test/test@localhost:1521/pdbset autot onset lin 120 pages 999SQL> select count(*) from tab2 where c2=1; COUNT(*)---------- 50実行計画----------------------------------------------------------Plan hash value: 3563712581----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | ||* 2 | INDEX RANGE SCAN| IND2_2 | 50 | 150 | 1 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("C2"=1)Note----- - SQL plan baseline "SQL_PLAN_1avk6nzqq9stx910d0b22" used for this statement統計---------------------------------------------------------- 20 recursive calls 41 db block gets 11 consistent gets 0 physical reads 4472 redo size 573 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed8.执行加Hint的SQL文。
SQL> select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1; COUNT(*)---------- 50実行計画----------------------------------------------------------Plan hash value: 2781695375---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | ||* 2 | TABLE ACCESS FULL| TAB2 | 50 | 150 | 5 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("C2"=1)統計---------------------------------------------------------- 4 recursive calls 25 db block gets 16 consistent gets 0 physical reads 904 redo size 573 bytes sent via SQL*Net to client 417 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed9.查看加Hint的SQL文的sql_id和plan_hash_value。
conn sys/*****@localhost:1521/pdbselect sql_id, child_number, plan_hash_value from v$sql where sql_text like 'select /*+ FULL (TAB2) */ count(*) from tab2 where c2=1';SQL_ID CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------556psnns1a273 0 278169537510.把加了Hint的SQL文的执行打算加到原始SQL文的BaseLine上。
SQL> var cnt number;SQL> exec :cnt := dbms_spm.load_plans_from_cursor_cache( sql_id => '&hinted_SQL_ID', plan_hash_value => &hinted_plan_hash_value, sql_handle => '&sql_handle_for_original');hinted_sql_idに値を入力してください: 556psnns1a273hinted_plan_hash_valueに値を入力してください: 2781695375sql_handle_for_originalに値を入力してください: SQL_156e46a7ed64e33dPL/SQLプロシージャが失常に完了しました。SQL> print :cnt RES---------- 111.查看SQLPLAN的BaseLine。
col sql_handle for a30col SQL_TEXT for a50col PLAN_NAME for a30set lin 120 pages 999select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';SQL_HANDLE SQL_TEXT PLAN_NAME FIX------------------------------ -------------------------------------------------- ------------------------------ ---SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stx910d0b22 YESSQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stxfbe5cdec NO12.删除最后Load的BaseLine。
SQL> exec :cnt :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');original_sql_handleに値を入力してください: SQL_156e46a7ed64e33doriginal_plan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stx910d0b22PL/SQLプロシージャが失常に完了しました。SQL> print :cnt CNT---------- 113.固定起初Load进来的执行打算。
SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'FIXED', attribute_value => 'YES');sql_handleに値を入力してください: SQL_156e46a7ed64e33dplan_nameに値を入力してください: SQL_PLAN_1avk6nzqq9stxfbe5cdecPL/SQLプロシージャが失常に完了しました。SQL> print :cnt CNT---------- 1SQL> select sql_handle,sql_text, plan_name,fixed from dba_sql_plan_baselines where sql_text like 'select count(*) from tab2 where c2=1';SQL_HANDLE SQL_TEXT PLAN_NAME FIX------------------------------ -------------------------------------------------- ------------------------------ ---SQL_156e46a7ed64e33d select count(*) from tab2 where c2=1 SQL_PLAN_1avk6nzqq9stxfbe5cdec YES14.执行原始SQL文，再次查看SQLPLAN。
conn test/test@localhost:1521/pdbset autot onset lin 120 pages 999select count(*) from tab2 where c2=1; COUNT(*)---------- 50実行計画----------------------------------------------------------Plan hash value: 2781695375---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 3 | 5 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 3 | | ||* 2 | TABLE ACCESS FULL| TAB2 | 50 | 150 | 5 (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter("C2"=1)Note----- - SQL plan baseline "SQL_PLAN_1avk6nzqq9stxfbe5cdec" used for this statement統計---------------------------------------------------------- 118 recursive calls 41 db block gets 83 consistent gets 0 physical reads 4520 redo size 573 bytes sent via SQL*Net to client 398 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed