Actual combat: ORACLE SQL Performance Analyzer

With the SPA, you can play specific types of changes depending on the type of change (such as initialization of change, optimizer statistics refresh, and database upgrade)
SQL or the entire SQL payload, and then generate a comparison report that helps you evaluate their impact.

In the version number prior to Oracle Database 11g, I had to capture all the SQL statements and execute them by tracing,
Then get the run plan-this is a very time-consuming and error-prone task. The new version number, we don't need to do that anymore,
I switched to a very easy and effective SQL performance Analyzer.

---usage scenarios

1. Database Upgrade
2. Implementing optimization Recommendations
3. Change the scenario
4. Collect statistical information
5. Changing database parameters
6. Changing the operating system and hardware

Create Tablespace Test
DataFile ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01. DBF '
Size 5000m
Autoextend on
Next 100m MaxSize Unlimited
Extent Management Local Autoallocate
Segment Space management auto;

CREATE TABLE T1
(
Sid int NOT NULL,
Sname VARCHAR2 (10)
)
Tablespace test;

-2.-Loop 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 (' Success in data entry! ‘);
Commit
End
/

Update T1 set sname= ' Suzhou ' where sid=500001;

Update T1 set sname= ' Nanjing ' where sid=600001;

---3. Collect statistical information

exec dbms_stats.gather_table_stats (USER, ' T1 ', cascade=>true)

alter system flush Shared_pool;

---4. Run 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 a new STS

BEGIN
Dbms_sqltune. Drop_sqlset (
Sqlset_name = ' ocpyang_sts '
);
END;
/

BEGIN
Dbms_sqltune. Create_sqlset (
Sqlset_name = ' ocpyang_sts ',
Sqlset_owner = ' SYS ',
Description = ' ocpyangtest ');
END;
/

---6. Load 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 '
)
A
Dbms_sqltune.load_sqlset (
Sqlset_name = ' ocpyang_sts ',
Populate_cursor = Cur01);
Close Cur01;
END;
/

The/********* has two parameters that deserve special clarification:

1) The first parameter of the Select_cursor_cache is Basic_filter, and the value it can take is:

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 (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 number of Select_cursor_cache is attribute_list

BASIC (default)-all attributes (such as execution statistics and binds) are returned except the plans the execution conte XT 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. Querying SQL Optimization Sets

Select Sql_id,sql_text from Dba_sqlset_statements
where Sqlset_name= ' ocpyang_sts ' and sql_text like '% from t1% ';

---8. New Spa

var v_task varchar2 (64);
Begin
: V_task:=dbms_sqlpa.create_analysis_task (
Sqlset_name = ' ocpyang_sts ',
Task_name = ' SPA01 '
);
End
/

/********** syntax

Syntax

SQL text Format. This form of the function was 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 was called to prepare the analysis of a single statement from the cursor cache given its identifi Er.

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 was called to prepare the analysis of a single statement from the workload repository given a ran GE 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 was 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. Run 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. Run after 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. Comparison of running tasks

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
Set Long 100000000
Set pagesize 0
Set Linesize 200
Set Longchunksize 200
Set Trimspool on
Spool E:\report.txt

Select Dbms_sqlpa. Report_analysis_task (' SPA01 ') from dual;

Spool off;