Analyze and optimize the SQL statement
Install and view the ORACLE execution plan
The set of steps that ORACLE uses when executing SQL statements is called an execution plan.
Previous conditions:
Execute utlxplan.sql in the directory: $ORACLE_HOME/RDBMS/ADMIN directory
View the execution plan:
EXPLAN PLAN FOR
CREDIT @ ORCL > explain plan for select * from creditcard
Explained.
See the information of SQL's execution plan.
CREDIT @ ORCL > select a. Operation.optionsdescription, name, name, journal, objectcategory, type, from plan_table an order by id, id, parentkeeper.
More intuitive:
CREDIT @ ORCL > select lpad (', 2* (level-1)) | | operation | |'| options | |'| | object_name | |''| | decode (id,0,'cost=' | | position) "Query Plan" from plan_table connect by prior id=parent_id
Query Plan
-
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
This can also be queried:
CREDIT @ ORCL > select * from table (dbms_xplan.display)
PLAN_TABLE_OUTPUT
-
Plan hash value: 2658862924
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 9 | 1332 | 3 (0) | 00:00:01 |
| | 1 | TABLE ACCESS FULL | CREDITCARD | 9 | 1332 | 3 (0) | 00:00:01 |
Note
-
-dynamic sampling used for this statement (level=2)
Turn on the automatic tracking feature:
Set autotrace on
Access the execution plan of the table through ROWID:
SYS AS SYSDBA@ORCL > explain plan for
2 select * from hr.departments where rowid='AAAR5QAAFAAAACvAAa'
Explained.
Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL > select * from table (dbms_xplan.display)
PLAN_TABLE_OUTPUT
-
Plan hash value: 313428322
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 21 | 1 (0) | 00:00:01 |
| | 1 | TABLE ACCESS BY USER ROWID | DEPARTMENTS | 1 | 21 | 1 (0) | 00:00:01 |
-
8 rows selected.
Execution plan of the join query:
Optimize the case study:
Improve the efficiency of GROUP BY statements:
Select cardno,sum (amount) from consume group by cardno having cardno='9555xxxx3' or cardno='9555xxxx8'
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 114K | 4475K | 175K (3) | 00:00:03 |
| | * 1 | FILTER | | |
| | 2 | HASH GROUP BY | | 114K | 4475K | 175K (3) | 00:00:03 |
| | 3 | TABLE ACCESS FULL | CONSUME | 114K | 4475K | 171K (1) | 00:00:03 |
1. Perform a full table scan TABLE ACCESS FULL
two。 Perform grouping statistics HASH GROUP BY
3. Perform filtering operation FILTER
Analysis: filtering operation after grouping statistics, the amount of data processed by all grouping statistics is relatively large
Optimized statement:
Select cardno,sum (amount) from consume where "CARDNO" = '9555xxx3' OR "CARDNO" =' 9555xxx8' group by cardno
Use EXISTS instead of the IN keyword
+
Method 1: run the following script to generate the plan_ table
SQL > @ / u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql
Table created.
SQL > explain plan for
2 select deptno from scott.dept group by deptno
Explained.
SQL > select id,operation,options,object_name,position from plan_table
ID OPERATION OPTIONS OBJECT_NAME POSITION
-
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
Method 2:oracle provides v$sql_plan to
SQL > select id,options,operation,object_name,cost
2 from v$sql_plan
3 where object_owner='SCOTT'
The reason why no rows selected-- has no data is that the explain plan for command just now only produces the execution plan, not the actual execution statement.
SQL > select deptno from scott.dept group by deptno
DEPTNO
-
ten
twenty
thirty
forty
SQL > select id,operation,options,object_name,position from plan_table
ID OPERATION OPTIONS OBJECT_NAME POSITION
-
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1
