Introduction and use of Oracle Profiling Tools

Introduction to Oracle Database-level optimization analysis tools

When we optimize the diagnosis of the database, we need to collect the appropriate information for reference, from the personal experience, the statistical data are divided into two major categories

One category is database-level statistics
The second class is OS-level statistics

Here's a breakdown of what tools are commonly used to gather information at different levels to help optimize diagnostics

The first is the Oracle database-level Optimization analysis tool Introduction

Directory:

1.statspack
2.ASH
3.AWR
4.ORACLE EXPLAIN Plan Summary (query SQL execution Plan)
A.autotrace
Use of B.explain

1.statspack

A. Installation

Sql> sqlplus "/As SYSDBA"

Sql> select file_name from Dba_data_files;

sql> Create tablespace perfstat datafile ' e:/oracle/oradata/skate/perfstat.dbf ' size 2000m;

Sql> @ORACLE_HOME/rdbms/admin/spcreate.sql

B. Use

Sql> Conn PERFSTAT/PASSWD

Collect statistical information
Sql> Execute Statspack.snap
Or
Sql> exec statspack. SNAP (I_snap_level =>5);

Generate reports
Sql> @ORACLE_HOME/rdbms/admin/spreport.sql

There are two ways to collect information regularly, one is Oracle job, one is OS crontab, I'm more accustomed to OS level crontab

Set a job that automatically collects samples once per hour

Declare
Variable job number;
Begin
Dbms_job.submit (: Job, "STATSPACK.SNAP;", trunc (sysdate + 1/24, ' hh24 '), "trunc (sysdate+1/24, ' hh24 ')");
commit;
End;
/

View Job Usage

Sql> Select Job,schema_user,next_date,interval,what from User_jobs

Automatic Stop sampling job

Declare
Variable job number;
Begin
Dbms_job.submit (: Job, "Dbms_job.broken (44,true);", Trunc (Sysdate + 1), "null");
commit;
End;
/

Clears data from all stats statistics tables

Sql> @ORACLE_HOME/rdbms/admin/sptrunc.sql

Snapshot level, which can be done by exec statspack. Modify_statspack_parameter (i_snap_level=n) to modify, N can be 0,5,6,7,10, default is 5.

0 provide general performance statistics only
5 added a general analysis of SQL statements
6 added SQL plan and use
7 added segment (segments) level statistics
10 increased analysis of latch-up (latches)

One of the documentation recommendations is to be cautious about 10 because of the high cost.

eg
Sql> exec statspack. SNAP (I_snap_level =>6);

Oracle not only provides script spreport.sql for generating database reports, but also provides another Statspack report script sprepsql.sql to generate SQL reports

Sql> @ORACLE_HOME/rdbms/admin/sprepsql.sql

Reference Documentation:
Use Statspack to get top SQL in the build environment and its execution plan
Http://www.hellodba.com/Doc/statspack_report_sql.htm

2.AWR

AWR is a built-in library that is automatically configured and enabled, and his collection of performance data is by default an hour, and Awr's analysis of historical data

Generate report scripts generate reports in the directory using $oracle_home/rdbms/admin/, as follows:

Awrrpt.sql: Generate a statistical report of the specified snapshot interval;
Awrrpti.sql: Generates the specified DB instance, and specifies a statistical report of the snapshot interval;
Awrsqlrpt.sql: Generates a statistical report of the specified snapshot interval, specifying the SQL statement (which is actually specified as the sqlid of the statement);
Awrsqrpi.sql: Generates a statistical report of the specified SQL statement for the specified DB instance, specifying the snapshot interval;
Awrddrpt.sql: Specify two different time periods, generate a statistical comparison report of the two periods;
Awrddrpi.sql: Specify a DB instance and specify two different time periods to generate a statistical comparison report for both periods;

Modify Snapshots Settings
With the modify_snapshot_settings process, DBAs can adjust settings that include three aspects of snapshot collection frequency, snapshot save time, and number of captured SQL. Three parameters corresponding to modify_snapshot_settings:

Retention: Sets the time, in minutes, for the snapshot to be saved. The value can be set to a minimum of 1 days and a maximum of 100 years. Setting the parameter value to 0 means that the collected snapshot information is persisted permanently.

Interval: Sets the frequency, in minutes, for snapshot collection. The value can be set to a minimum of 10 minutes and a maximum of 1 years. Setting this parameter value to 0 means that the AWR attribute is disabled.

Topnsql: Specifies the amount of SQL that is collected to compare resources that can be set to a minimum of 30 and a maximum of 100000000.

AWR related several views:

Dba_hist_wr_control: View relevant settings for the current snapshot collection
V$active_session_history: Automatically maintained in memory by Ash, collecting information about the active session in the current system at a frequency of once per second
Dba_hist_active_sess_history: Is the historical data of the view v$active_session_history, saved on the hard disk
Dba_hist_database_instance: Displays information about the database as an instance
Dba_hist_snapshot: Snapshot information collected by the current database

3.ASH

Ash and awr are not completely separate two functions, Ash collects information from v$session in seconds and stores it in memory, which can be reused, when the memory is full, ash data is given to AWR and finally written to the system view

Ash consists of two parts, part of the SGA, which is reflected in the data since the start of the system, and Ash retains as much as 1 hours of content, which is saved in the V$active_session_history another part of the System dictionary table Dba_hist_ Active_sess_history, it's permanent data.

Ash also has a script to generate reports in the directory $oracle_home/rdbms/admin/

Ashrpt.sql: Generate a database-level ASH Statistics Report
Ashrpti.sql: Generate Ash statistics reports at the DB instance level, commonly used for RAC Singleton instances

4.ORACLE EXPLAIN Plan Summary (query SQL execution Plan)

A.autotrace

Installation

Run the script with the SYS user Ultxplan.sql

The script to build this table is: (UNIX: $ORACLE _home/rdbms/admin, Windows:%oracle_home%/rdbms/admin) ultxplan.sql.

Sql> Connect sys/[email protected] as SYSDBA;

Sql> @c:/oracle/ora92/rdbms/admin/utlxplan.sql;

sql> create public synonym plan_table for plan_table;--synonyms

Sql> Grant all on plan_table to public;--authorized by all users

To establish a role plustrace in the database, run the script plustrce.sql with the SYS user to create the role, the script
(UNIX: $ORACLE _home/sqlplus/admin, windows:%oracle_home%/sqlplus/admin);

Sql> @c:/oracle/ora92/sqlplus/admin/plustrce.sql;

The role Plustrace is then granted to the user who needs to autotrace;

Sql>grant plustrace to public;

After the above steps of the setup, you can use Autotrace in Sql*plus, Autotrace function can only be used in Sql*plus

AUTOTRACE Statistics Common Column interpretation

DB block gets: The number of blocks read from the buffer cache
Consistent gets: The number of block of undo data read from buffer cache
Physical reads: Number of blocks read from disk
Redo Size:dml the size of the generated redo
Sorts (memory): The amount of sorting performed
Sorts: The amount of sort performed on the disk

eg

[email protected]>set autotrace
Usage:set Autot[race] {OFF | On | Trace[only]} [Exp[lain]] [stat[istics]

[email protected]>set timing on

[email protected]>set Autot trace Exp Stat

[email protected]>select * from Tab;

3809 rows selected.

elapsed:00:00:00.06

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
---------------------------------------------------------------------
| 0 |        SELECT STATEMENT |  | 1059 |   46596 | 216 (2) |
|  1 |        NESTED LOOPS OUTER |  | 1059 |   46596 | 216 (2) |
|   2 | TABLE ACCESS Full |  obj$ | 1059 |   39183 | 158 (2) |
|   3 | TABLE ACCESS cluster|     tab$ |     1 |     7 | 1 (0) |
|    4 | INDEX UNIQUE SCAN |     i_obj# |       1 |     | 0 (0) |
---------------------------------------------------------------------

Note
-----
-' plan_table ' is old version

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
9077 consistent gets
0 physical Reads
0 Redo Size
133502 Bytes sent via sql*net to client
3252 Bytes received via sql*net from client
255 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
3809 rows processed

B. Use of explain

Each SQL statement executed by an Oracle RDBMS must be evaluated by the Oracle Optimizer. So, understand how the optimizer chooses (searches) the path to
And how the index is used is a great help in optimizing SQL statements. The explain can be used to quickly and easily detect a query in a given SQL statement
The search path (which we commonly call access path) is how data is obtained. So that we choose the best query mode to achieve maximum optimization effect.

1.1. Installation

To use explain, first execute the corresponding script to create the Explain_plan table.

The specific script executes as follows:

$ORACLE _home/rdbms/admin/utlxplan.sql (UNIX) After the script generates a table this program creates a table named Plan_table.

1.2. Use

General usage Syntax:
Explain PLAN [SET statement_id [=] < string literal >] [into < table_name >]
for < sql_statement >

which

STATEMENT_ID: is a unique string that distinguishes the current execution plan from other execution plans stored in the same plan.

TABLE_NAME: is the plan table name, which is structured as shown earlier, and you can set this name arbitrarily.

Sql_statement: is a real SQL statement.

Like what:

Sql>explain plan set statement_id= ' t_test ' for select * from T_test;

Sql>

Explained

Execute the following statement to query the execution plan

Sql>select a.operation,options,object_name,object_type,id,parent_id
2 from Plan_table A
3 WHERE statement_id= ' t_test '
4 ORDER by Id;

You can also use this sentence select * from table (dbms_xplan.display); It is possible to list all the data in the plan_table.

----End-----

Reprint to: http://blog.csdn.net/wyzxg/article/details/5346974