How to find and solve Oracle SQL statement execution efficiency problems

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 Columns
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
3. Monitor the current Oracle session. If the clock is displayed, 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 optimizers (CBO and RBO for short)
• Value of Optimizer Mode:
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 will be used
All_rows: Always use cost-based Optimizer, with the goal of obtaining the optimal throughput
First_rows_n: always use 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 indexes, large data volumes, small tables, Full Table Scans hints, HWM (High Water Mark), and Rowid Scans)
• 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 the other table is used for sorting and merging. In theory, it is faster than sort join and requires sufficient memory, the SORT_JOIN_ENABLE parameter is enabled. (When a useful index is missing, hash connections are more effective than nested loop connections. The hash connection may be faster than the sort merge connection, because only one source table needs to be sorted in this case. Hash connections may also be faster than nested loop connections, because processing hash tables in memory is faster than retrieving B _ tree indexes. Like sorting and merging connections and cluster connections, hash connections can only be used for equivalent connections. Like sorting and merging connections, hash connections use memory resources, and when the memory used for sorting is insufficient, I/O of the temporary tablespace will be added (this will make the connection method extremely slow ). Finally, only the cost-based optimizer can use Hash connections .)
Index connection:
No2: AUTOTRACE
• Set autotrace steps:
1. Log On With system
2. Create the plustrace role. <your_oracle_home> \ sqlplus \ admin \ plustrce. SQL
3. grant permissions to regular users: grant plustrace to <user id>
4. If no plan_table exists, create <your_oracle_home> \ rdbms \ admin \ utlxplan. SQL
• Set autotrace options

 

On	Display query results, execution plans, and statistical data
On statistics	Displays query results, statistics, and execution plans
On explain	Displays query results, execution plans, and no statistical data
Traceonly	Displays execution plans and statistical results, excluding query results
Traceonly statistics	Show statistics only
Recursive cballs	Number of recursive calls generated at the user and system levels. Oracle maintains some tables for internal processing. When oracle needs to change these tables, it will generate an SQL statement internally, and then the statement is generated into a recursive call.
Db block gets	Number of requests for a CURRENT Block
Consistent gets	The number of times that a consistent read is requested.
Physical reads	Obtain the total number of data blocks read from the disk. This number is equal to the value of "direct physical read" and all data blocks read into the buffer zone.
Redo size	Total number of retries generated (in bytes)
Bytes sent via SQL * Net to client	Total number of bytes sent from the previous process to the customer
Bytes encoded ed via SQL * Net from client	Total number of bytes received from the customer through Oracle Net
SQL * Net roundtrips to/from client	Total number of Oracle Net messages sent to and received from customers
Sorts (memory)	Number of sorting operations that are fully executed in the memory and do not require any disk writes
Sorts (disk)	The number of sorting operations that require at least one disk write
Rows processed	Number of rows processed during the operation

4. Use the timing feature to measure and compare the processing time.
Set timing on

Author: ERDP Technical Architecture"