Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.