This discussion is archived
7 Replies Latest reply: Nov 14, 2012 6:00 AM by Chewy RSS

SQL Plan Management (11.2.0.1)

Chewy Newbie
Currently Being Moderated
Hi Guys,

I'm doing some testing as below.
I tried to keep the SQL plan in baseline. (when the table has no index, as such performing full scan)
However, after i create the index. It's not picking the original plan which i put it in baseline. It's using range scan.

Can advise what have i done wrong?

thanks!



SQL> show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE

SQL> CREATE TABLE spm_test_tab (
2 id NUMBER,
3 description VARCHAR2(50)
4 );

Table created.

SQL>
SQL> DECLARE
2 TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE;
3 l_tab t_tab := t_TAB();
4 BEGIN
5 FOR i IN 1 .. 10000 LOOP
6 l_tab.extend;
7 l_tab(l_tab.last).id := i;
8 l_tab(l_tab.last).description := 'Description for ' || i;
9 END LOOP;
10
11 FORALL i IN l_tab.first .. l_tab.last
12 INSERT INTO spm_test_tab VALUES l_tab(i);
13
14 COMMIT;
15 END;
16 /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> set linesize 2000
SQL> set pagesize 2000
SQL> SET AUTOTRACE TRACE
SQL>
SQL> SELECT description
2 FROM spm_test_tab
3 WHERE id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3128910679

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SPM_TEST_TAB | 1 | 25 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - filter("ID"=99)


Statistics
----------------------------------------------------------
26 recursive calls
5 db block gets
49 consistent gets
0 physical reads
944 redo size
228 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> set autotrace off;

SQL>
SQL> SELECT sql_id, sql_text
2 FROM v$sql
3 WHERE sql_text LIKE '%spm_test_tab%'
4 AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
5 AND sql_text NOT LIKE '%EXPLAIN%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------------
gat6z1bc6nc2d SELECT description FROM spm_test_tab WHERE id = 99
44b32kmh8ub2g DECLARE TYPE t_tab IS TABLE OF spm_test_tab%ROWTYPE; l_tab t_tab := t_TAB(); BEGIN


SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
5 sql_id => 'gat6z1bc6nc2d');
6
7 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
8 END;
9 /
Plans Loaded: 1

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, plan_name, enabled, accepted
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE '%spm_test_tab%'
4 AND sql_text NOT LIKE '%dba_sql_plan_baselines%';

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL>
SQL> CREATE INDEX spm_test_tab_idx ON spm_test_tab(id);

Index created.

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

PL/SQL procedure successfully completed.

SQL>
SQL> SET AUTOTRACE TRACE
SQL>
SQL> SELECT description
2 FROM spm_test_tab
3 WHERE id = 99;


Execution Plan
----------------------------------------------------------
Plan hash value: 2792204123

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("ID"=99)


Statistics
----------------------------------------------------------
1072 recursive calls
3 db block gets
192 consistent gets
0 physical reads
888 redo size
242 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
1 rows processed



SQL> set autotrace off;
SQL> SELECT sql_handle, plan_name, enabled, accepted
2 FROM dba_sql_plan_baselines
3 ;

SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9 SQL_PLAN_7qxjk7bch8h5tb65c37c8 YES YES

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points