Practice: oracle SQL Performance Analyzer

With SPA, you can play a specific stream based on various types of changes (such as initialization parameter changes, optimizer statistics refresh, and database upgrades)
SQL or the entire SQL load, and then generate a comparison report to help you evaluate their impact.

In versions earlier than Oracle Database 11g, I must capture all SQL statements and trace and run these statements,
Then get the execution plan-this is an extremely time-consuming and error-prone task. In the new version, we don't need to do that anymore,
I use a very simple and effective SQL Performance Analyzer.

--- Use Cases

1. Database Upgrade
2. Implementation of optimization suggestions
3. Change Plan
4. Collect statistics
5. Change Database Parameters
6. Change the operating system and hardware

 

Create tablespace test
Datafile 'e: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ test01.dbf'
Size 5000 m
Autoextend on
Next 100 m maxsize unlimited
Extent management local autoallocate
Segment space management auto;

 

Create table t1
(
Sid int not null,
Sname varchar2 (10)
)
Tablespace test;

 

 

-2.-cyclically import data
Declare
Maxrecords constant int: = 1000000;
I int: = 1;
Begin
For I in 1 .. maxrecords loop
Insert into t1 values (I, 'ocpyang ');
End loop;
Dbms_output.put_line ('data entered successfully! ');
Commit;
End;
/

Update t1 set sname = 'suzhou 'where sid = 500001;

Update t1 set sname = 'nanjing 'where sid = 600001;

--- 3. Collect statistics

Exec dbms_stats.gather_table_stats (USER, 't1', CASCADE => TRUE)

Alter system flush shared_pool;

--- 4. Execute the query

Select count (*) from t1 where sid <= 100;

Select count (*) from t1 where sid <= 500;

Select count (*) from t1 where sid> 50000;

--- 5. Create an STS

BEGIN
DBMS_SQLTUNE.DROP_SQLSET (
Sqlset_name => 'oss _ ss'
);
END;
/

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
Sqlset_name => 'oss _ ss ',
Sqlset_owner => 'sys ',
Description => 'oppyangtest ');
END;
/

--- 6. Load the SQL optimization set

Set serveroutput on
DECLARE
Cur01 dbms_sqltune.sqlset_cursor;
BEGIN
Open cur01 for select value (a) from table (dbms_sqltune.select_cursor_cache
(
Basic_filter => 'SQL _ text like ''' % T1 % ''' and parsing_schema_name = ''sys ''',
Attribute_list => 'all'
)
);
Dbms_sqltune.load_sqlset (
Sqlset_name => 'oss _ ss ',
Populate_cursor => cur01 );
Close cur01;
END;
/

/********* There are two parameters worth special explanation:

1) The first parameter of SELECT_CURSOR_CACHE is basic_filter, which can take the following values:

SQL _id VARCHAR (13 ),
Force_matching_signature NUMBER,
SQL _text CLOB,
Object_list SQL _objects,
Bind_data RAW (2000 ),
Parsing_schema_name VARCHAR2 (30 ),
Module VARCHAR2 (48 ),
Action VARCHAR2 (32 ),
Elapsed_time NUMBER,
Cpu_time NUMBER,
Buffer_gets NUMBER,
Disk_reads NUMBER,
Direct_writes NUMBER,
Rows_processed NUMBER,
Fetches NUMBER,
Executions NUMBER,
End_of_fetch_count NUMBER,
Optimizer_cost NUMBER,
Optimizer_env RAW (1, 1000 ),
Priority NUMBER,
Command_type NUMBER,
First_load_time VARCHAR2 (19 ),
Stat_period NUMBER,
Active_stat_period NUMBER,
Other CLOB,
Plan_hash_value NUMBER,
SQL _plan SQL _plan_table_type,
Bind_list SQL _binds

2) The last parameter of SELECT_CURSOR_CACHE is attribute_list.

BASIC (default)-all attributes (such as execution statistics and binds) are returned tables t the plans The execution context is always part of the result.

TYPICAL-BASIC + SQL plan (without row source statistics) and without object reference list

ALL-return all attributes

Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL _PLAN, SQL _PLAN_STATISTICS: similar to SQL _PLAN + row source statistics

*********/

--- 7. query the SQL optimization set

Select SQL _id, SQL _text from dba_sqlset_statements
Where sqlset_name = 'oss _ sts' and SQL _text like '% from t1 % ';

 

 

 

--- 8. Create a SPA

Var v_task varchar2 (64 );
Begin
: V_task: = dbms_sqlpa.create_analysis_task (
Sqlset_name => 'oss _ ss ',
Task_name => 'spa01'
);
End;
/

 

 

 

/********** Syntax

Syntax

SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.

DBMS_SQLPA.CREATE_ANALYSIS_TASK (
SQL _text IN CLOB,
Bind_list IN SQL _binds: = NULL,
Parsing_schema IN VARCHAR2: = NULL,
Task_name IN VARCHAR2: = NULL,
Description IN VARCHAR2: = NULL)
RETURN VARCHAR2;
SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.

DBMS_SQLPA.CREATE_ANALYSIS_TASK (
SQL _id IN VARCHAR2,
Plan_hash_value in number: = NULL,
Task_name IN VARCHAR2: = NULL,
Description IN VARCHAR2: = NULL)
RETURN VARCHAR2;
Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.

DBMS_SQLPA.CREATE_ANALYSIS_TASK (
Begin_snap in number,
End_snap in number,
SQL _id IN VARCHAR2,
Plan_hash_value in number: = NULL,
Task_name IN VARCHAR2: = NULL,
Description IN VARCHAR2: = NULL)
RETURN VARCHAR2;

SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.

DBMS_SQLPA.CREATE_ANALYSIS_TASK (
Sqlset_name IN VARCHAR2,
Basic_filter IN VARCHAR2: = NULL,
Order_by IN VARCHAR2: = NULL,
Top_ SQL IN VARCHAR2: = NULL,
Task_name IN VARCHAR2: = NULL,
Description IN VARCHAR2: = NULL
Sqlset_owner IN VARCHAR2: = NULL)
RETURN VARCHAR2;

**********/

 

--- 9. Execute SPA

Begin
Dbms_sqlpa.execute_analysis_task
(
Task_name => 'spa01 ',
Execution_type => 'test execute ',
Execution_name => 'before _ change'
);
End;
/

 

 

/********* Syntax

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name IN VARCHAR2,
Execution_type IN VARCHAR2: = 'test execute ',
Execution_name IN VARCHAR2: = NULL,
Execution_params IN dbms_advisor.argList: = NULL,
Execution_desc IN VARCHAR2: = NULL)
RETURN VARCHAR2;

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (
Task_name IN VARCHAR2,
Execution_type IN VARCHAR2: = 'test execute ',
Execution_name IN VARCHAR2: = NULL,
Execution_params IN dbms_advisor.argList: = NULL,
Execution_desc IN VARCHAR2: = NULL );

*********/

--- 10. Change

Create index index_01 on t1 (sid, sname)
Tablespace test;

Exec dbms_stats.gather_table_stats (USER, 't1', CASCADE => TRUE)

--- 11. Execute after the change

Begin
Dbms_sqlpa.execute_analysis_task
(
Task_name => 'spa01 ',
Execution_type => 'test execute ',
Execution_name => 'after _ change'
);
End;
/

 

Col TASK_NAME format a30
Col EXECUTION_NAME for a30
Select execution_name,
Status,
Execution_end
From DBA_ADVISOR_EXECUTIONS
Where task_name = 'spa01'
Order by execution_end
/

EXECUTION_NAME STATUS EXECUTION_END
------------------------------------------------------------
Before_change COMPLETED 2014-05-28 15:43:58
After_change COMPLETED 2014-05-28 15:44:58

 

--- 12. Execute task comparison

Begin
Dbms_sqlpa.EXECUTE_ANALYSIS_TASK (
Task_name => 'spa01 ',
Execution_type => 'compare performance ',
Execution_params => dbms_advisor.arglist (
'Execution _ name1 ',
'Before _ change ',
'Execution _ name2 ',
'After _ change '));
End;
/

 

--- 13. production report

Set serveroutput on size 999999
Sets long 100000000
Set pagesize 0
Set linesize 200
Set long chunksize 200
Set trimspool on
Spool e: \ report.txt

Select DBMS_SQLPA.REPORT_ANALYSIS_TASK ('spa01') from dual;

Spool off;