Searching and solving method of Oracle SQL statement execution efficiency problem

Methods of identifying statements that occupy more resources (4 methods)

1. Feedback from the test group and end users on issues related to slow response.

2. The implementation details are provided using the V_$sqlarea view. (Number of times to perform, read, and read buffers)

& #8226; Data columns

Executions: Number of executions

Disk_reads: Read the number of disk

Command_type: Command Type (3:select,2:insert;6:update;7delete;47:pl/sql program unit)

Optimizer_mode: How to optimize

Sql_text:sql statement

Sharable_mem: How much memory is occupied with shared pool

Buffer_gets: Number of Read buffers

& #8226; use

1, to help find poor performance of the SQL statement

2, to help find the highest frequency of SQL

3, to help analyze whether the need to index or improve the connection

3. Monitor the current Oracle session, such as a clock flag, indicating that the SQL in this process is running for a long time.

4.Trace Tools:

A) View the initial parameters of the database service: Timed_statistics, User_dump_dest, and Max_dump_file_size

b) Step 1:alter session Set Sql_trace=true

(c) Step 2:run SQL;

D) Step 3:alter session Set Sql_trace=false

(e) Step 4: Use "tkprof" to transform trace files

f) Parse, the large number of resolutions usually indicates the need to increase the database server's shared pool size

The large number of query or current fetches indicates that the statement might run more efficiently if there is no index.

The number of disk fetches indicates that the index is likely to improve performance,

More than one miss in the library cache indicates a larger shared pool size is required