Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, August 20, 2015

Oracle SPM Manual Loding.

Manually loading the SQL Plan Baselines 

SQL> show parameter baseline

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


SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1694116892

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |     6 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY            |       |     1 |     6 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SPM    |     1 |     6 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | SPM_IDX1 |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

  3 - access("COL1"=300)



SQL> set long 10000
SQL> declare    
 2  abc pls_integer;
 3  begin
 4  abc:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fydaq3sch82s7');
 5  end;
 6  /

PL/SQL procedure successfully completed.

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%'
 2  
SQL> ;
 1* select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%'
SQL> /

SIGNATURE SQL_HANDLE             PLAN_NAME            ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD    YES YES YES


SQL> drop index spm_idx1;

Index dropped.

SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 950742835

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    1 |    6 |   104   (2)| 00:00:02 |
|   1 |  SORT ORDER BY       |      |    1 |    6 |   104   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| SPM  |    1 |    6 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------


SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected

SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected


SQL> create index spm_idx on spm(col1);

Index created.

SQL> exec dbms_stats.gather_table_stats('ARCHIVE','SPM');

PL/SQL procedure successfully completed.

SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected

SQL> select /*SPM_TEST*/ * from spm where col1=300 order by col2;

no rows selected

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';

SIGNATURE SQL_HANDLE             PLAN_NAME            ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g360281aedb AUTO-CAPTURE    YES NO    YES
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD    YES YES YES

SQL> show parameter baseline

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_a845896a93f43c66') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_A845896A93F43C66')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
          Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
 SQL_HANDLE = SYS_SQL_a845896a93f43c66
 PLAN_NAME  =
 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
 VERIFY     = YES
 COMMIT     = YES

Plan: SQL_PLAN_ahjc9da9z8g360281aedb
------------------------------------
 Plan was verified: Time used .75 seconds.
 Plan passed performance criterion: 187.76 times better than baseline plan.
 Plan was changed to an accepted plan.

              Baseline Plan      Test Plan       Stats Ratio
              -------------      ---------       -----------
 Execution Status:        COMPLETE    COMPLETE
 Rows Processed:           0           0
 Elapsed Time(ms):           74.08        .013       5698.46
 CPU Time(ms):            15.111           0
 Buffer Gets:                 374           2           187
 Physical Read Requests:          370           0
 Physical Write Requests:       0           0
 Physical Read Bytes:         3031040           0
 Physical Write Bytes:        0           0
 Executions:               1           1

-------------------------------------------------------------------------------
               Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1


SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines where sql_text like 'select /*SPM_TEST*/%';

SIGNATURE SQL_HANDLE             PLAN_NAME            ORIGIN     ENA ACC AUT
---------- ------------------------------ ------------------------------ -------------- --- --- ---
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g360281aedb AUTO-CAPTURE    YES YES YES
1.2125E+19 SYS_SQL_a845896a93f43c66      SQL_PLAN_ahjc9da9z8g361a1720db MANUAL-LOAD    YES YES YES


SQL> select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle=>'SYS_SQL_a845896a93f43c66'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a845896a93f43c66
SQL text: select /*SPM_TEST*/ * from spm where col1=300 order by col2
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g360281aedb      Plan id: 42053339
Enabled: YES     Fixed: NO    Accepted: YES      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3250893592

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    28 |   168 |     3    (34)| 00:00:01 |
|   1 |  SORT ORDER BY            |           |    28 |   168 |     3    (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SPM     |    28 |   168 |     2     (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | SPM_IDX |    28 |       |     1     (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

  3 - access("COL1"=300)

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ahjc9da9z8g361a1720db      Plan id: 437723355
Enabled: YES     Fixed: NO    Accepted: YES      Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 950742835

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    28 |   168 |   104   (2)| 00:00:02 |
|   1 |  SORT ORDER BY       |      |    28 |   168 |   104   (2)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| SPM  |    28 |   168 |   103   (1)| 00:00:02 |
---------------------------------------------------------------------------

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

  2 - filter("COL1"=300)

46 rows selected.

SQL> SELECT parameter_name, parameter_value FROM   dba_sql_management_config; 

PARAMETER_NAME              PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT               10
PLAN_RETENTION_WEEKS               53

No comments: