Monitor Oracle execution plans

In daily development activities, Oracle execution plans are sometimes monitored to optimize program and database performance. There are several common methods: 1.

In daily development activities, Oracle execution plans are sometimes monitored to optimize program and database performance. There are several common methods: 1.

In daily development activities, Oracle execution plans are sometimes monitored to optimize program and database performance.

There are several common methods:

1. Use the PL/SQL Dev Tool
1. directly File-> New-> Explain Plan Window. Execute SQL statements in the Window to view the Plan results. Among them, Cost indicates the cpu consumption, unit is n %, Cardinality indicates the number of lines executed, equivalent to Rows.
2. Execute the explain plan for select * from tableA where paraA = 1, and then select * from table (DBMS_XPLAN.DISPLAY) to view the oracle execution PLAN, the result is the same as that in 1. Therefore, we recommend that you use the 1 method when using the tool.
Note: The Command window of PL/SQL Dev does not support the set autotrance on Command. You can also use the tool to view the incomplete information of the plan. Sometimes we need sqlplus support.

Ii. Use sqlplus
1. Generally, the local machine is connected to a remote server, so the command is as follows:
Sqlplus user/pwd @ serviceName
The serviceName here is the namespace defined in tnsnames. ora.
2. Execute set autotrace on and then execute the SQL statement to list the following information:
... (Omitted information)
Statistics
1 recursive cballs (number of calls)
0 db block gets
2 consistent gets
0 physical reads (physical read-the number of data blocks read from the hard disk during SQL Execution)
0 redo size (number of retries-the size of the redo log generated during SQL Execution)
358 bytes sent via SQL * Net to client
366 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory) sorting in memory
0 sorts (disk) sorting in Hard disk
1 rows processed
The omitted information is the same as the information used to view the execution plan through the PL/SQL Dev tool. The following statistical information is more detailed.
Determining the SQL efficiency is not measured by time, but also by the number of logical reads in the SQL Execution status.
Logical READ = (db block gets + consistent gets)