| SQL Plan Baseline Trouble!!! [message #573761] |
Thu, 03 January 2013 01:23  |
 |
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  |
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 #573776 is a reply to message #573771] |
Thu, 03 January 2013 02:15 |
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 #573795 is a reply to message #573781] |
Thu, 03 January 2013 03:59 |
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 #573895 is a reply to message #573837] |
Thu, 03 January 2013 22:33 |
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
