Searching and solving SQL statement execution efficiency in Oracle

This article describes in detail how to find and solve SQL statement execution efficiency problems in Oracle. I. methods for identifying statements that occupy a large amount of resources (4 methods)
1. Problems Related to slow response reported by the test group and end users.
2. Use the V _ $ sqlarea view to provide execution details. (Number of executions, disk reads, and buffer reads)
? Data Column
Executions: number of executions
Disk_reads: Number of disk reads
Command_type: Command type (3: Select, 2: insert; 6: update; 7 delete; 47: PL/SQL program Unit)
Optimizer_mode: Optimization Method
SQL _text: SQL statement
Sharable_mem: Memory occupied by the Shared Pool
Buffer_gets: Number of times the buffer is read
? Purpose
1. Help you find SQL statements with poor performance
2. Help you find the SQL statements with the highest frequency
3. Help you analyze whether indexes are required or improve connections
Monitor the Session of the current ORACLE: Family: 'times new Roman '; MSO-Hansi-font-family: 'times new Roman' ">. If a clock icon is displayed, indicates that the SQL running time in the process is long.
4. Trace tool:
A) Check the initial database service parameters: 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 convert the trace file
F) Parse. A large number of resolutions often indicate that the size of the database server's shared pool needs to be increased,
The large number of queries or current extracts indicates that if no index is available, the statement may run more effectively,
The number of disk extracts indicates that the index may improve the performance,
Missing more than once in the library cache indicates that a larger sharing pool is required.
Ii. How to manage statement processing and options
? Cost-based and rule-based optimizer (CBO and RBO)
? Value of the optimizer mode parameter:
Choose: If statistical data of any table accessed exists, the cost-based optimizer is used to obtain the optimal throughput. If some tables do not have statistical data, use an estimate. If no statistical data is available, rule-based optimizer is used.
All_rows: cost-based optimizer is always used, and the goal is to obtain the optimal throughput.
First_rows_n: always uses the cost-based optimizer to obtain the optimal response time for the first n rows ("N" can be 100, 1000, or) returned.
First_rows: used for backward compatibility. The combination of cost and test methods can be used to quickly transmit the first few rows.
Rule: Always use rule-based Optimizer
3. Use the database features to obtain Processing Statistics (Interpretation plan and autotrace) that help to view performance)
No1: explain Plan
A) To use the explain tool, you need to create the explain_plan table, which must first enter the account of the owner of the relevant application tables, views, and indexes. (@ D:/Oracle/ora92/rdbms/admin/utlxplan)
B) table structure:
Statement_id: Specifies the name of an execution plan for a specified SQL statement. If set statement_id is not used in the explain Plan statement, this value is set to null.
Operation: the name of the operation performed in a planned step, such as filters, index, table, Marge joins and table.
Option: Supplement operation operations. For example, operation for a table may be table access, but option may be by rowid or full.
Object_owner: schema name or Oracle account name that owns the database object.
Object_name: Database Object Name
Object_type: type, for example, table, view, index, etc.
ID: Specifies the position of a step in the execution plan.
Parent_id: indicates the previous operation that retrieves information from an operation. By using the connect by operation with ID and parent_id, We can query the entire execution plan tree.
C) Explain
? Full table scans (no available index, large data volume, small table, full table scan hints, hwm (high water mark), rowid scan)
? Index Scanning
Unique index scan (index unique scans)
Index range scans)
Index range scans descending)
Index skip scans)
Full index scan (full scans)
Fast full index scans)
Index joins)
Bitmap joins)
? How to select an access path: CBO first checks the conditions in the WHERE clause and the from clause to determine which access paths are available. Then, CBO uses this access path to generate a set of possible execution plans, then evaluates the cost of each plan through the index and table statistics, and finally selects the lowest cost optimizer.
? Table connection method:
Nested loops cyclically checks the External table (driver table) to check whether the connection with the internal table meets the conditions one by one. It is better when the driver table is small, the internal table is large, and the internal and external join columns have indexes. When sort_area space is insufficient, Oracle will also choose to use NL. The cost-based Oracle optimizer (CBO) automatically selects a small table as the External table. (Advantage: nested loop connections have advantages over other connection methods. They can quickly extract the first batch of records from the result set without waiting for the entire result set to be completely determined. Disadvantage: nested loop connection is inefficient if the connected columns of the internal row source table (the second table to be read (the internal table) do not contain indexes, or the index is not highly optional. If the driving row source table (Records extracted from the driving table) is very large, other connection methods may be more effective .)
Sort-merge join: sorts and merges the join columns of the two tables. It can only be used when the join columns are equal, suitable for situations where the two tables are of different sizes (when there is a lack of data selectivity or available indexes, or when both source tables are too large (more than 5% of the number of records, sort and merge connections are more efficient than nested loop connections. However, Permutation and merge connections can only be used for equivalent connections (where D. deptno = E. dejptno, rather than where D. deptno> = E. deptno ). Sort the memory blocks required for the merged connections to be sorted (if sort_area_size is set too small ). This will lead to more memory and disk I/O usage in the temporary tablespace .)
Hash join is used as a hash in the join columns of one table. Therefore, only one other table is used for sorting and merging. In theory, it is faster than sort join? /TD>
"Font-family:; MSO-ascii-font-family: 'times new Roman '; MSO-Hansi-font-family: 'times new Roman'"> or full.
Object_owner: schema name or Oracle account name that owns the database object.
Object_name: Database Object Name
Object_type: type, for example, table, view, index, etc.
ID: Specifies the position of a step in the execution plan.
Parent_id: indicates the previous operation that retrieves information from an operation. By using the connect by operation with ID and parent_id, We can query the entire execution plan tree.
C) Explain
? Full table scans (no available index, large data volume, small table, full table scan hints, hwm (high water mark), rowid scan)
? Index Scanning
Unique index scan (index unique scans)
Index range scans)
Index range scans descending)
Index skip scans)
Full index scan (full scans)
Fast full index scans)
Index joins)
Bitmap joins)
? How to select an access path: CBO first checks the conditions in the WHERE clause and the from clause to determine which access paths are available. Then, CBO uses this access path to generate a set of possible execution plans, then evaluates the cost of each plan through the index and table statistics, and finally selects the lowest cost optimizer.
? Table connection method:
Nested loops cyclically checks the External table (driver table) to check whether the connection with the internal table meets the conditions one by one. It is better when the driver table is small, the internal table is large, and the internal and external join columns have indexes. When sort_area space is insufficient, Oracle will also choose to use NL. The cost-based Oracle optimizer (CBO) automatically selects a small table as the External table. (Advantage: nested loop connections have advantages over other connection methods. They can quickly extract the first batch of records from the result set without waiting for the entire result set to be completely determined. Disadvantage: nested loop connection is inefficient if the connected columns of the internal row source table (the second table to be read (the internal table) do not contain indexes, or the index is not highly optional. If the driving row source table (Records extracted from the driving table) is very large, other connection methods may be more effective .)