SQL Baslines in 11g are the new Stored Outlines – and one of the nicest features of SQL Baselines is that you are allowed to fake them; or rather, it’s legal to generate an execution plan for one query and transfer its execution plan to another query using the packaged procedure dbms_spm.load_plans_from_cursor_cache(). This posting is a demonstration of the technique.

We start with a sample data set and a query that is going to do “the wrong thing”. As usual I’ve got a locally managed tablespace with 8KB blocks and 1MB uniform. extents, freelist management, and I’m running with CPU Costing disabled (and running 11.1.0.6 in this case):

set serveroutput off 

create table t1 

as 

with generator as ( 

    select  --+ materialize 

        rownum id 

    from dual 

    connect by 

        rownum <= 10000 

) 

select 

    rownum          id, 

    mod(rownum,1000)    n1000, 

    lpad(rownum,10,'0') small_vc, 

    rpad('x',100)       padding 

from 

    generator   v1, 

    generator   v2 

where 

    rownum <= 10000 

; 

create index t1_n1 on t1(n1000); 

begin 

    dbms_stats.gather_table_stats( 

        ownname      => user, 

        tabname      =>'T1', 

        method_opt   => 'for all columns size 1' 

    ); 

end; 

/ 

select 

    /*+ target_query */ 

    id 

from 

    t1 

where 

    n1000 = 500 

;

The final query is the one that I want to fake a baseline for. With my current set up it does an index range scan to pick up 10 rows, but I’m going to make it do a tablescan instead. I’m going to need to pull the exact text of the query from memory in a moment, so I’ll find its sql_id and child_number by searching for the “pseudo-hint” that I’ve included in the text, and I’ll report the execution plan to show that I’ve picked up the right thing (I’m assuming that there’s only one piece of SQL that’s going to include the text “target_query”, of course):

 
column  sql_id          new_value   m_sql_id_1 

column  plan_hash_value     new_value   m_plan_hash_value_1 

column  child_number        new_value   m_child_number_1 

select 

    sql_id, plan_hash_value, child_number 

from 

    v$sql 

where 

    sql_text like '%target_query%' 

and sql_text not like '%v$sql%' 

and rownum = 1 

; 

select * from table(dbms_xplan.display_cursor('&m_sql_id_1',&m_child_number_1)); 

SQL_ID  306m98cpu9yz7, child number 0 

------------------------------------- 

select  /*+ target_query */  id from  t1 where  n1000 = 500 

Plan hash value: 1420382924 

--------------------------------------------------------------------- 

| Id  | Operation                   | Name  | Rows  | Bytes | Cost  | 

--------------------------------------------------------------------- 

|   0 | SELECT STATEMENT            |       |       |       |    11 | 

|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |    80 |    11 | 

|*  2 |   INDEX RANGE SCAN          | T1_N1 |    10 |       |     1 | 

--------------------------------------------------------------------- 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

   2 - access("N1000"=500)

The next step is to create a new query (and in a production system that might simply mean running a heavily hinted version of the target query) that uses the execution plan I want to see; and I’ll use the same search technique to find it and report its plan:

 
select 

    /*+ full(t1) alternate_query */ 

    id 

from 

    t1 

where 

    n1000 = 500 

; 

column  sql_id          new_value   m_sql_id_2 

column  plan_hash_value     new_value   m_plan_hash_value_2 

column  child_number        new_value   m_child_number_2 

select 

    sql_id, plan_hash_value, child_number 

from 

    v$sql 

where 

    sql_text like '%alternate_query%' 

and sql_text not like '%v$sql%' 

and rownum = 1 

; 

select * from table(dbms_xplan.display_cursor('&m_sql_id_2',&m_child_number_2)); 

SQL_ID  bvpb73bb6c6uy, child number 0 

------------------------------------- 

select  /*+ full(t1) alternate_query */  id from  t1 where  n1000 = 500 

Plan hash value: 3617692013 

---------------------------------------------------------- 

| Id  | Operation         | Name | Rows  | Bytes | Cost  | 

---------------------------------------------------------- 

|   0 | SELECT STATEMENT  |      |       |       |    28 | 

|*  1 |  TABLE ACCESS FULL| T1   |    10 |    80 |    28 | 

---------------------------------------------------------- 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

   1 - filter("N1000"=500)

After been running and reporting these queries and their plans, I’ve captured the SQL_Id, child_number, and plan_hash_value for each query; and this is more than enough information to make it possible to create an SQL Baseline for one query using the execution plan for the other query.

 
declare 

    m_clob  clob; 

begin 

    select 

        sql_fulltext 

    into 

        m_clob 

    from 

        v$sql 

    where 

        sql_id = '&m_sql_id_1' 

    and child_number = &m_child_number_1 

    ; 

    dbms_output.put_line(m_clob); 

    dbms_output.put_line( 

        dbms_spm.load_plans_from_cursor_cache( 

            sql_id          => '&m_sql_id_2', 

            plan_hash_value     => &m_plan_hash_value_2, 

            sql_text        => m_clob, 

            fixed           => 'YES', 
---------------------禁止演化baseline

            enabled         => 'YES' 

        ) 

    ); 

end; 

/

I used the SQL_ID and child_number from the first query to get the full SQL text of the query into an in-memory CLOB, and then use the SQL_id and plan_hash_value from the second query to associate the second plan with the first query – storing the result as a SQL Baseline that is enabled and ready for use.

You’ll have to take my word that I haven’t faked the following text, but this is what I get when I re-run the original query (flushing the shared pool first to make sure that I don’t accidentally end up picking up the original child cursor):

 
alter system flush shared_pool; 

select 

    /*+ target_query */ 

    id 

from 

    t1 

where 

    n1000 = 500 

; 

select * from table(dbms_xplan.display_cursor('&m_sql_id_1',null)); 

SQL_ID  306m98cpu9yz7, child number 1 

------------------------------------- 

select  /*+ target_query */  id from  t1 where  n1000 = 500 

Plan hash value: 3617692013 

---------------------------------------------------------- 

| Id  | Operation         | Name | Rows  | Bytes | Cost  | 

---------------------------------------------------------- 

|   0 | SELECT STATEMENT  |      |       |       |    28 | 

|*  1 |  TABLE ACCESS FULL| T1   |    10 |    80 |    28 | 

---------------------------------------------------------- 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

   1 - filter("N1000"=500) 

Note 

----- 

   - cpu costing is off (consider enabling it) 

   - SQL plan baseline SYS_SQL_PLAN_3c0ea7f3dbd90e8e used for this statement

It’s so much easier than the fiddling one had to do for stored outlines which was quite easy in 8i, but got a little nasty in 9i because of the extra (undocumented) details that looked as if they might have been necessary when the third table appeared in the outln schema. However, in 10g, the dbms_outln package was enhanced to allow you to create outlines from the library cache – see this note from Randolf Geist for more details – but remember that stored outlines will be deprecated in Oracle 12.

Footnote: the dbms_spm package is created by script. $ORACLE_HOME/rdbms/admin/dbmsspm.sql, and there is a comment near the top saying:

 
--           Grant the execute privilege on SPM package to public.           -- 

--           But ADMINISTER SQL MANAGEMENT OBJECT privilege is checked       -- 

--           before a user is allowed to execute.                            --

ITPub博客

伪造baseline

最新文章