Shulou(Shulou.com)06/01 Report--

With SPA, you can play specific changes 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 comparison reports to help you assess their impact.

In versions prior to Oracle Database 11g, I had to capture all SQL statements and run them by tracking

Then get the execution plan-this is an extremely time-consuming and error-prone task. With the new version, I don't have to do that anymore.

I switched to a very simple and effective SQL Performance Analyzer.

-use scenarios

1. Database upgrade

two。 Implement optimization recommendations

3. Change the 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 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 (iMagnesia ocpyang`)

End loop

Dbms_output.put_line ('successfully enter data! ')

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 query

Select count (*) from T1 where sid '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

/

/ * there are two parameters worth specifying:

1) the first parameter of SELECT_CURSOR_CACHE is basic_filter, and the values it can take are:

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 parameter of SELECT_CURSOR_CACHE is attribute_list

BASIC (default)-all attributes (such as execution statistics and binds) are returned except 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 sql optimization set

Select sql_id,sql_text from dba_sqlset_statements

Where sqlset_name='OCPYANG_STS' and sql_text like'% from T1%'

-8. Create a new SPA

Var v_task varchar2 (64)

Begin

: v_task:=dbms_sqlpa.create_analysis_task (

Sqlset_name = > 'OCPYANG_STS'

Task_name = > 'SPA01'

);

End

/

/ * Grammar

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

/

/ * Grammar

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 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. Perform 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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.