02 Performance Kit

The previous introduction of some sql*plus knowledge, I already know, note: Autotrace can only run on Sql*plus, run on Toad, the actual bottom is also run on Sql*plus.

AUTOTRACE

Autotrace is easy to display, but it's easy to omit some important information, such as partition information, binding variables, and so on.

The direct use of the command explain can not omit the partition information, the operation method is:

Explain plan for Select ... (Note that this is only temporary display, if you want to save the plan, then you need to add an ID, generally do not need to save the program)

Explain plan and autotrace is a kiss relationship, the master use Autotrace as the first-class adjustment tool , so, the individual or recommend the use of Autotrace, give up explain, big deal use 10046.

The execution plan differs slightly from the result of running in Sql*plus with Toad, where Sql*plus will have the following content:

(cost=24 card=155 bytes=17205)

Cost: Costs for each step of the query

Card: The abbreviation for cardinality (cardinality), which is an estimate of the number of rows of records that will be output when a particular query is poorly planned.

BYTES:CBO predicts the number of data bytes that each planned step will return. Based on: Base (Card) and line width.

In addition: the content displayed in statistics:

Recursive calls: the number of SQL statements that are run for your SQL statement execution

AUTOTRACE exporting content of interest1. Recursive calls

Recursive call statistics are SQL that must be executed because you need to execute other SQL statements. For example, if you execute an INSERT statement that triggers a trigger that runs a query, it is recursive SQL.

Reason for recursive invocation:

1) Hard Parse, select * from Scott.emp; A lot of recursive calls are seen in the query plan, but there is no time for the 2nd execution, because the 1th time is hard parsing, note that although we are just a SQL statement on the statement, Oracle can query many systems, such as permissions, within the system.

2) the PL/SQL function call, if it is called with a single-pl function, and the PL-SQL function itself needs to run a lot of SQL, then it will be very high recursive call. If this is the case, use the bag to be nice.

Recursive invocation Summary: Remember that recursive SQL can be avoided whenever possible.

2. DB blocks gets and consistent gets

Data block retrieved = DB blocks gets (read from data block) + consistent gets (read from buffer cache)

In general, the primary way to reduce block reads and consistent reads is through query tuning, but to be successful, you must keep an open mind and have a good understanding of the possible access paths.

3. Physical reads

A measure of how much actual I/O or physical I/O is required, a physical read of the table or index data puts a block into the buffer cache, and then we perform a logical I/O to retrieve the block. As a result, most physical reads follow a logical I/O directly behind them.

4. Redo Size

Redo dimension statistics show how much data was generated during execution. It is the most useful when it comes to identifying high-volume operations.

TKPROF

P98

02 Performance Kit