Use of Oracle SQL Performance Analyzer

Use of Oracle SQL Performance Analyzer

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

In versions earlier than Oracle Database 11g, I had to capture all SQL statements, trace and run these statements, and 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.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian
 
--- 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
 
*********/

For more details, please continue to read the highlights on the next page:

1 2 Next Page