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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : June-2018

**********************************************************************************


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]


I am certified Oracle Cloud Architect:
Oracle Cloud Infrastructure 2018 Certified Architect Associate
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate

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: