04 | April | 2017 | Catt Solutions

Oracle 12c provides the DBMS_SPM package for managing plan evolution. This article demonstrates the creation of SQL Plan using the DBMS_SPM package.

Logon to you database server as the oracle software owner and connect to SQLPLUS as sysdba.

[root@linux2 ~]# su – oracle

Last login: Tue Dec 27 07:53:43 EST 2016 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 28 11:06:31 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Create test table for SPM test and populate with values.

create table test_spm(val_1 number, val_2 char(1));

insert into test_spm

select level, ‘a’ from dual connect by level <=100;

SQL> create table test_spm(val_1 number, val_2 char(1));

Table created.

SQL> insert into test_spm

2 select level, ‘a’ from dual connect by level <=100;

100 rows created.

SQL> commit;

Commit complete.

Get the SQL PLAN name by setting the AUTOTRACE to TRACE and executing query against test table. SQL PLAN NAME in this example is SQL_PLAN_891rxd509mzp3eb1890ae.

SET AUTOTRACE TRACE

select * from test_spm;

SET AUTOTRACE TRACE

select * from test_spm;SQL> SQL>

100 rows selected.

Execution Plan

———————————————————-

Plan hash value: 1145642998

——————————————————————————

——————————————————————————

| 0 | SELECT STATEMENT | | 100 | 500 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| TEST_SPM | 100 | 500 | 2 (0)| 00:00:01 |

——————————————————————————

Note

—–

– dynamic statistics used: dynamic sampling (level=AUTO)

– SQL plan baseline “SQL_PLAN_891rxd509mzp3eb1890ae” used for this statement

Statistics

———————————————————-

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

2816 bytes sent via SQL*Net to client

617 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100 rows processed

SQL>

Set autotrace off.

set autotrace off;

SQL> set autotrace off;

Get the SQL_HANDLE from PLAN_NAME out of the dba_sql_plan_baselines table.

SELECT sql_handle, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE plan_name = ‘SQL_PLAN_891rxd509mzp3eb1890ae’;

SELECT sql_handle, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE plan_name = ‘SQL_PLAN_891rxd509mzp3eb1890ae’;SQL> SQL> 2 3

SQL_HANDLE PLAN_NAME ENA ACC

——————– —————————— — —

SQL_8486fd694099fea3 SQL_PLAN_891rxd509mzp3eb1890ae YES YES

SQL>

Create an evolve task using the function CREATE_EVOLVE_TASK function with SQL_HANDLE from above.

SET SERVEROUTPUT ON

DECLARE

l_return VARCHAR2(32767);

BEGIN

l_return := DBMS_SPM.create_evolve_task(sql_handle => ‘SQL_8486fd694099fea3’);

DBMS_OUTPUT.put_line(‘Task Name: ‘ || l_return);

END;

SQL> SET SERVEROUTPUT ON

DECLARE

l_return VARCHAR2(32767);

BEGIN

l_return := DBMS_SPM.create_evolve_task(sql_handle => ‘SQL_8486fd694099fea3’);

DBMS_OUTPUT.put_line(‘Task Name: ‘ || l_return);

END;

/SQL> 2 3 4 5 6 7

Task Name: TASK_1049

PL/SQL procedure successfully completed.

SQL>

Execute the evolve task using the DBMS_SPM.execute_evolve_task function and the TASK_NAME from above.

SET SERVEROUTPUT ON

DECLARE

l_return VARCHAR2(32767);

BEGIN

l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1049’);

DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);

END;

SQL> SET SERVEROUTPUT ON

DECLARE

l_return VARCHAR2(32767);

BEGIN

l_return := DBMS_SPM.execute_evolve_task(task_name => ‘TASK_1049’);

DBMS_OUTPUT.put_line(‘Execution Name: ‘ || l_return);

END;

/SQL> 2 3 4 5 6 7

Execution Name: EXEC_1082

PL/SQL procedure successfully completed.

SQL>

Report on the task outcome using the DBMS_SPM.report_evolve_task function and the TASK_NAME and EXECUTION_NAME from above.

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100

SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1049’, execution_name => ‘EXEC_1082’) AS output

FROM dual;

SQL>

SET LONG 1000000 PAGESIZE 1000 LONGCHUNKSIZE 100 LINESIZE 100

SELECT DBMS_SPM.report_evolve_task(task_name => ‘TASK_1049’, execution_name => ‘EXEC_1082’) AS output

FROM dual;SQL> SQL> SQL> 2

OUTPUT

—————————————————————————————————-

GENERAL INFORMATION SECTION

———————————————————————————————

Task Information:

———————————————

Task Name : TASK_1049

Task Owner : SYS

Execution Name : EXEC_1082

Execution Type : SPM EVOLVE

Scope : COMPREHENSIVE

Status : COMPLETED

Started : 12/28/2016 15:07:14

Finished : 12/28/2016 15:07:14

Last Updated : 12/28/2016 15:07:14

Global Time Limit : 2147483646

Per-Plan Time Limit : UNUSED

Number of Errors : 0

———————————————————————————————

SUMMARY SECTION

———————————————————————————————

Number of plans processed : 0

Number of findings : 0

Number of recommendations : 0

Number of errors : 0

———————————————————————————————

SQL>

Implement the recommendations in the task with the DBMS_SPM.implement_evolve_task function and the TASK_NAME from above.

SET SERVEROUTPUT ON

DECLARE

l_return NUMBER;

BEGIN

l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_1049’);

DBMS_OUTPUT.put_line(‘Plans Accepted: ‘ || l_return);

END;

SQL>

SET SERVEROUTPUT ON

DECLARE

l_return NUMBER;

BEGIN

l_return := DBMS_SPM.implement_evolve_task(task_name => ‘TASK_1049’);

DBMS_OUTPUT.put_line(‘Plans Accepted: ‘ || l_return);

END;

/SQL> SQL> 2 3 4 5 6 7

Plans Accepted: 0

PL/SQL procedure successfully completed.

SQL>

Verify the change by querying the DBA_SQL_PLAN_BASELINES table witht eh SQL_HANDLE from above.

SELECT sql_handle, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE sql_handle = ‘SQL_8486fd694099fea3’;

SQL> SELECT sql_handle, plan_name, enabled, accepted

FROM dba_sql_plan_baselines

WHERE sql_handle = ‘SQL_8486fd694099fea3’;

2 3

SQL_HANDLE PLAN_NAME ENA ACC

——————– —————————— — —

SQL_8486fd694099fea3 SQL_PLAN_891rxd509mzp3eb1890ae YES YES

SQL>

This complets use of DBMS_SPM for creating SQL plan.

Larry Catt

OCP

Oracle EM Express Opening Page provides the ability to view a snapshot of current database instance. This article covers the general layout of EM Express Opening Page and its functional usage.

Logon the EM Express as an administrative user. In this case we will use the URL: https://10.30.15.63:5500/em. This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default). NOTE: You may receive a certificate warning but select continue to site option. Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

The initial page will give you a summary of the current setup and state of the oracle database EM Express is attached to.

There are six drop down views which can be minimized: Status, Performance, Incidents, Resources, Running Jobs, and SQL Monitor

Status drop down give the general information about the database being monitored.

Performance drop down give the current activity on the database instance by Activity Class or Services.

Incidents drop down give all errors and warning that have occurred for administrative review.

Resources drop down give a snap shot of the resources currently being used by the database instance.

Running Jobs drop down give lists all jobs currently executing in the database.

SQL Monitor drop down give the currently executing SQL statements against the database instance.

This article summarized the feature and use of the Opening Page of Oracle 12c EM Express.

Larry Catt

OCP

Catt Solutions

Daily Archives: April 4, 2017

Oracle 12c – DBMS_SPM – Creating SQL Plan

EM Express 12c Opening Page

Oracle tips and tricks.