Home » RDBMS Server » Performance Tuning » SQL Plan Baseline Trouble!!! (Oracle 11.2.0.3.0 & Oracle 10.2.0.1.0)
SQL Plan Baseline Trouble!!! [message #573761] Thu, 03 January 2013 01:23 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
SQL Plan Baseline

According to what i understood from documentation is that, For a sql query S1 explain plan used in 10g can be transported to 11g and this would become a default explain plan for query S1 in 11g irrespective of a better explain plan proposed by 11g

TEST CASE

1. created a table spm with two columns as below having 1000000 rows in 10g

create table spm(id number, name varchar2(200));

id column having values from 1..1000000

2. Gather statistics and executed below sql

set autotrace traceonly
select name from spm where id=1789;
set autotrace off

Full table scan was used---- perfect

3.Now transferred the sql plan as per document to 11g

4.created same table with exact specifications
but with a index on id column
again gathered statistsics

5.again excuted the above sql

set autotrace traceonly
select name from spm where id=1789;
set autotrace off

Expected result= FULL TABLE SCAN
Actual Result= INDEX SCAN

Above result stumped me...please help!!!

In 10g


SQL> create table spm(id number, name varchar2(200));

Table created.



SQL> declare
  2  type lst_tab is table of spm%rowtype index by binary_integer;
  3  v_tab lst_tab;
  4  begin
  5  for i in 1..1000000 loop
  6  v_tab(i).id:=i;
  7  v_tab(i).name:='My name is anthony '||i;
  8  end loop;
  9  forall i in v_tab.first..v_tab.last
 10  insert into spm values v_tab(i);
 11  commit;
 12  end ;
 13  /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------                      
Plan hash value: 2579179811                                                     
                                                                                
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |     1 |    30 |  1128   (4)| 00:00:14 |      
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    30 |  1128   (4)| 00:00:14 |      
--------------------------------------------------------------------------      
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("ID"=1789)                                                        


Statistics
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
       4977  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        428  bytes sent via SQL*Net to client                                   
        381  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

SQL> set autotrace off
SQL> SELECT sql_id,sql_text
  2  FROM   v$sql
  3  WHERE  sql_text LIKE 'select name from spm%'
  4  AND    sql_text NOT LIKE '%dba_sql_plan_baselines%'
  5  AND    sql_text NOT LIKE '%EXPLAIN%';

SQL_ID                                                                          
-------------                                                                   
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
d9a1xs0zhruu2                                                                   
select name from spm where id=:"SYS_B_0"                                        
                                                                                


SQL> select * from spm_test;
select * from spm_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'SPM_TEST');

PL/SQL procedure successfully completed.


SQL> DECLARE
  2        l_cursor DBMS_SQLTUNE.sqlset_cursor;
  3      BEGIN
  4        OPEN l_cursor FOR
  5            SELECT VALUE(p)
  6              FROM TABLE (
  7                 DBMS_SQLTUNE.select_cursor_cache (
  8                   'sql_id = ''d9a1xs0zhruu2''',
  9                   NULL,
 10                  NULL,
 11                  NULL,
 12                  NULL,
 13                  NULL,
 14                  1,
 15        'ALL')
 16             ) p;
 17       DBMS_SQLTUNE.load_sqlset (
 18           sqlset_name => 'Plan_2_sql',
 19           populate_cursor => l_cursor);
 20     END;
 21     /

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name => 'SPM_STAGE');

PL/SQL procedure successfully completed.

SQL> execute DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name => 'Plan_2_sql',staging_table_name => 'SPM_STAGE');

PL/SQL procedure successfully completed.

SQL> select count(*) from spm_stage;

  COUNT(*)                                                                      
----------                                                                      
         1                                                                      



Used exp-imp moved stage table in 11g

In 11g


SQL> select count(*) from dba_sql_plan_baselines;

  COUNT(*)
----------
        1
SQL> select origin,version,enabled,accepted from dba_sql_plan_baselines where sq
l_text like 'select name from spm%';

ORIGIN         VERSION      ENA ACC
---             ---         --- ---
MANUAL-LOAD    11.2.0.3.0   YES YES

SQL> create table spm(id number, name varchar2(200));

Table created.

SQL>
SQL> declare
  2  type lst_tab is table of spm%rowtype index by binary_integer;
  3  v_tab lst_tab;
  4  begin
  5  for i in 1..1000000 loop
  6  v_tab(i).id:=i;
  7  v_tab(i).name:='My name is anthony '||i;
  8  end loop;
  9  forall i in v_tab.first..v_tab.last
 10  insert into spm values v_tab(i);
 11  commit;
 12  end ;
 13  /

PL/SQL procedure successfully completed.

SQL> create index spm_idx on spm(id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SPM', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------
Plan hash value: 67516759

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

-------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Ti

e     |

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

-------

|   0 | SELECT STATEMENT            |         |     1 |    31 |     1   (0)| 00

00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM     |     1 |    31 |     1   (0)| 00

00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_IDX |     1 |       |     1   (0)| 00

00:01 |

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

-------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1789)


Statistics
----------------------------------------------------------
         25  recursive calls
         14  db block gets
       5076  consistent gets
          1  physical reads
       2956  redo size
        352  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> drop index spm_idx;

Index dropped.

SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------
Plan hash value: 2579179811

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    31 |  1375   (1)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    31 |  1375   (1)| 00:00:17 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1789)


Statistics
----------------------------------------------------------
         10  recursive calls
          1  db block gets
       5075  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------
Plan hash value: 2579179811

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    31 |  1375   (1)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    31 |  1375   (1)| 00:00:17 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1789)


Statistics
----------------------------------------------------------
         15  recursive calls
         14  db block gets
       5070  consistent gets
          0  physical reads
       2928  redo size
        352  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------
Plan hash value: 67516759

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

-------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Ti

e     |

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

-------

|   0 | SELECT STATEMENT            |         |     1 |    31 |     1   (0)| 00

00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SPM     |     1 |    31 |     1   (0)| 00

00:01 |

|*  2 |   INDEX RANGE SCAN          | SPM_IDX |     1 |       |     1   (0)| 00

00:01 |

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

-------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1789)


Statistics
----------------------------------------------------------
          7  recursive calls
          5  db block gets
       5057  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select name from spm where id=1789;


Execution Plan
----------------------------------------------------------
Plan hash value: 2579179811

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    31 |  1375   (1)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    31 |  1375   (1)| 00:00:17 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1789)


Statistics
----------------------------------------------------------
         10  recursive calls
          1  db block gets
       5078  consistent gets
          0  physical reads
          0  redo size
        352  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed



Re: SQL Plan Baseline Trouble!!! [message #573769 is a reply to message #573761] Thu, 03 January 2013 01:55 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'm confused, it is early after a long break so forgive me if I'm being stupid but, why does the sql_text show a bind when the issued query passed a literal? Are you forcing cursor sharing or some such?
Re: SQL Plan Baseline Trouble!!! [message #573771 is a reply to message #573769] Thu, 03 January 2013 01:59 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Yes !


SQL> sho parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE

Re: SQL Plan Baseline Trouble!!! [message #573776 is a reply to message #573771] Thu, 03 January 2013 02:15 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Cool, just ruling out the obvious. I assume optimizer_use_sql_plan_baselines = true?

Is there anything obvious in v$sql_shared_cursor to explain why it is not being used? I suspect baselines and force sharing are fiddly, although having not used much in the way of cursor sharing in my placed I've never experienced it but we have used baselines a lot.
Re: SQL Plan Baseline Trouble!!! [message #573781 is a reply to message #573776] Thu, 03 January 2013 02:38 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hey Roachcoach

Thx for the reply

We upgraded our env to 11g and now sql's are performing bad so to counter this issue we thought of using baseline, is this a viable approach?

Or we should go for adaptive cursor sharing 11g new feature, but this would affect all our sql's or any other suggestions?

SQL> sho parameter baselines

NAME                                 TYPE
------------------------------------ --------------------------
VALUE
------------------------------
optimizer_capture_sql_plan_baselines boolean
FALSE
optimizer_use_sql_plan_baselines     boolean
TRUE


Cursor_sharing in 10g =Force
Cursor_sharing in 11g=Similar

But i don't think cursor sharing should affect in this case , logically we tell oracle to use specific execution plan it should just blindly go and execute it
How hard is that?


SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_7ty8
8yjyu6uy80a8fbd71'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


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

SQL handle: SQL_7cf908f47da36bc8
SQL text: select name from spm where id=:"SYS_B_0"
--------------------------------------------------------------------------------


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

Plan name: SQL_PLAN_7ty88yjyu6uy80a8fbd71         Plan id: 177192305
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2579179811

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    31 |  1380   (2)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    31 |  1380   (2)| 00:00:17 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


   1 - filter("ID"=TO_NUMBER(:SYS_B_0))

24 rows selected.

Re: SQL Plan Baseline Trouble!!! [message #573795 is a reply to message #573781] Thu, 03 January 2013 03:59 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Nothing wrong with baselines imo, though long term the code should be corrected.

I'm wondering about baselines and their interaction with forced sharing because of the way they match, it is just conjecture though, as I mentioned I'm not in a force sharing environment.

Does the baseline report as being used in the source environment after it is created?
Re: SQL Plan Baseline Trouble!!! [message #573804 is a reply to message #573795] Thu, 03 January 2013 05:01 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Basically this sql plan baseline takes data of below table i.e the optimal specs from desired env.

SELECT * FROM TABLE (
               DBMS_SQLTUNE.select_cursor_cache (
                 'sql_id = ''2guq4mxycqggw'''))


I'm skeptical about some columns data but can't blame any of them with utmost confidence

SQL plan baseline has it ever worked for you?
to me its looks like a hokum!!
Re: SQL Plan Baseline Trouble!!! [message #573828 is a reply to message #573804] Thu, 03 January 2013 07:16 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Sorry, far from my planned quiet day at the office.

Yes, they definitely work - as mentioned they report this at the bottom of the explain plan statement.

Apologies, this is all I can spare time for at the moment Sad
Re: SQL Plan Baseline Trouble!!! [message #573837 is a reply to message #573761] Thu, 03 January 2013 08:26 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Could you please upload the following output:

set long 1000
select SQL_TEXT from dba_sql_plan_baselines where sql_text like 'select name from spm%';
select name from spm where id=1789;
select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','LAST'));
Re: SQL Plan Baseline Trouble!!! [message #573895 is a reply to message #573837] Thu, 03 January 2013 22:33 Go to previous message
rishwinger
Messages: 132
Registered: November 2011
Senior Member

SQL> select SQL_TEXT from dba_sql_plan_baselines where sql_text like 'select nam
e from spm%';

SQL_TEXT
--------------------------------------------------------------------------------

select name from spm where id=:"SYS_B_0"




SQL> select name from spm where id=1789;

NAME
--------------------------------------------------------------------------------

My name is anthony 1789




SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','',
'LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  d9a1xs0zhruu2, child number 0
-------------------------------------
select name from spm where id=:"SYS_B_0"

Plan hash value: 2579179811

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1375 (100)|          |
|*  1 |  TABLE ACCESS FULL| SPM  |     1 |    31 |  1375   (1)| 00:00:17 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_7ty88yjyu6uy80a8fbd71 used for this statement


22 rows selected.



Thx ,henceforth i'll never use sql trace

[Updated on: Thu, 03 January 2013 22:40]

Report message to a moderator

Previous Topic: Memory_target and sga_target in oracle 11gR2
Next Topic: suggestion for increasing the buffer cache
Goto Forum:
  


Current Time: Thu Mar 28 04:26:57 CDT 2024