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

Today, the editor will bring you an article about the introduction of sql quantitative analysis tools in Oracle. The editor thinks it's pretty good, so I'll share it for you as a reference. Let's follow the editor and have a look.

Oracle provides quantitative analysis tools such as explain,sql*trace,tkprof,auto*trace,awr,addm,sql*profiling,sql access advisor,sql tuning advisor,ash.

This time it mainly collates the use of explain,sql*trace,tkprof,auto*trace.

1.explain

2.sql*trace

3.tkprof

4.auto*trace

The use of 1.explain

1.1 create plan_table first

@? / rdbms/admin/utlxplan

1.2 analyze the sql statement execution plan

Explain plan for select * from dba_objects

1.3 View the sql statement execution plan

@? / rdbms/admin/utlxpls.sql; 9i only view serial execution plan, 10g view both serial and parallel

@? / rdbms/admin/utlxplp.sql; View parallel execution Plan

Explain does not actually execute the sql statement, so the actual process may not be the same as the result of the explain analysis. For example, after explain analysis, new indexes are established, indexes are deleted, new statistical information is collected, and so on.

New function dbms_xplan in 10g

Dbms_xplan.display

Displays the statement execution plan stored in the v$sql_plan view

Dbms_xplan.display_cursor

Displays the statement execution plan in any loaded cursor

Dbms_xplan.display_awr

Displays the statement execution plan saved in awr

Select plan_table_output from table (dbms_xplan.display ())

The use of 2.sql*trace and tkprof

The general process is as follows

Database-- (sql trace)-trace file-- (tkprof)-report file

Sql*trace differs from explain in that sql*trace does not analyze the execution plan for a single sql statement, but tracks the sql statement at the session level or even at the instance level, and generates the corresponding trace file in the operating system

Sql*trace tracks the actual execution of sql statements. Oracle provides tkprof programs to convert trace files into more readable files.

2.1 tracking at the session level

Alter session set sql_trace = true; execute dbms_session.set_sql_trace (true)

Tracking of other sessions

Execute dbms_system.set_trace_in_session (session_id,serial_id,true)

When analyzing, first check which sessions consume more resources, then query session_id,serial_id from the v$session view, and track and analyze the activities of these sessions through the above commands

2.2 tracking at the instance level

Just set sql_trace to true.

It is recommended not to take this measure, which will exert great pressure on the database and generate too many trace files.

Using the trace files generated by sql_trace, the original files are relatively difficult to read, and can be converted using tkprof programs.

For example:

Tkprof tracefile outputfile [options]

Tkprof tracefile outputfile sys=no explain=hr/hr sort=execcput print=3

Sys=no means that sql statements executed by sys users are not analyzed.

Explain=hr/hr means to connect to a hr user and analyze the execution plan

Sort=execcput indicates that the sql statements contained in the trace file are sorted according to the CPU consumption value.

Print=3 just analyzes the first three sql statements.

Enter tkprof directly in os to view all the help

The files generated by tkprof are mainly analyzed as follows

1. Statistics on the execution of each statement

two。 Execution path information of each statement

The use of 3.autotrace

3.1 Open autotrace

Set autotrace on

Set timing on

Execute sql statement

3.2 only look at the implementation plan and statistics

Set autotrace traceonly

3.3 look at the implementation plan only

Set autotrace traceonly explain

Autotrace is the execution plan after the statement is actually executed

3.4 look at statistics only

Set autotrace statistics

3.5 View help

Set autotrace

Usage: SET AUTOT [RACE] {OFF | ON | TRACE [ONLY]} [explain] [stats]]

3.6 how to view statistics

Focus on the consistent gets and physical reads metrics, which represent memory consumption and disk IO consumption, respectively, in block size (db_block_size).

After reading the content of the appeal, do you have a general understanding of the quantitative analysis tool of sql in Oracle? If you want to know more about the content of the article, welcome to follow the industry information channel, thank you for reading!

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.