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

(1). Explain plan order (not allowed)

Explain plan for select statement

Select * from table (dbms_xplan.display)

(2) DBMS_XPLAN package

Select * from table (dbms_xplan.display);-- must be in cache. The result is not valid.

Select * from table (dbms_xplan.display_cursor (null,null,'advanced'));-- must be in cache, accurate

Select * from table (dbms_xplan.display_cursor ('sql_id/hash_value',child_cursor_number,'advanced'));-- sql_id must be found in the cache, accurate (select sql_text,sql_id,hash_value,child_cursor from v$sql where sql_text like' select * from abc')

Select * from table (dbms_xplan.display_awr ('sql_id'));-find sql_id in the AWR report, accurate

(3) .autotrace switch (used by sqlplus)

Set autotrace on

(4). 10046 event, the execution plan is the most accurate

SQL > oradebug event 10046 trace name context forever,level 12-- enable the 10046 event

SQL > select * from abc where a > 100;-- execute sql statement

SQL > oradebug tracefile_name-- View the generated file

SQL > oradebug event 10046 trace name context off-- close the 10046 event

[root@localhost ~] $tkprof 10000.trc 20000.trc-converts trc files into formats that are highly readable

Set the execution plan method:

Set autotrace off default, turn off the execution plan

Set autotrace on explain displays only the execution plan

Set autotrace on statistics displays only execution plan statistics

Set autotrace on displays execution plans and statistics

Set autotrace traceonly is similar to on in that it does not display the execution result of the statement

The method of accessing data in 1.oracle Database

(1)。 Full table scan (Full Table Scans,FTS)

(2)。 Scan by hiding the rowid field (Table Access by ROWID)

(3)。 Index range scan (Index range Scan)

(4)。 Index unique scan (Index unique scan)

(5)。 Index full scan (Index full scan)

(6)。 Index Quick scan (Index fast full scan)

1)。 Full table scan

SQL > set autotrace on

SQL > select * from emp where comm=1400

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

Execution Plan

Plan hash value: 3956160932

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("COMM" = 1400)

Access: indicates that the value of the condition will affect the access path to the data (tables and indexes)

Filter: indicates that the value of the condition does not affect the access path of the data, but only acts as a filter

Statistics

1 recursive calls

0 db block gets-how many blocks have been read

7 consistent gets-logical read (number of block read from buffer cache)

0 physical reads-physical read (number of block read from disk)

0 redo size-how many redo logs are generated

1028 bytes sent via SQL*Net to client-the number of bytes passed in by the client

523 bytes received via SQL*Net from client-the number of bytes passed from the server to the client

2 SQL*Net roundtrips to/from client

0 sorts (memory)-memory consumed by sorting

0 sorts (disk)-disk occupied by sort

1 rows processed-how many lines are affected

SQL >

2) .rowid field scan

SQL > select * from emp where rowid='AAAVREAAEAAAACXAAN'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Execution Plan

Plan hash value: 1116584662

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY USER ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 |

Statistics

1 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

1022 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

3)。 Index range scan

SQL > create index in_sal on emp (sal)

Index created.

SQL > select * from emp where sal

< 1000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 Execution Plan ---------------------------------------------------------- Plan hash value: 3065173639 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IN_SAL | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAL" (4).索引唯一扫描 SQL>

Select * from emp where empno=7566

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7566 JONES MANAGER 7839 02-APR-81 2975 20

Execution Plan

Plan hash value: 2949544139

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7566)

Statistics

1 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

892 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

(5)。 Index full scan

SQL > select * from emp where sal > 2000 order by empno

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7566 JONES MANAGER 7839 02-APR-81 2975 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

6 rows selected.

Execution Plan

Plan hash value: 4170700152

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 10 | 380 | 2 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS BY INDEX ROWID | EMP | 10 | 380 | 2 (0) | 00:00:01 |

| | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("SAL" > 2000)

Statistics

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

1263 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

6 rows processed

SQL >

6) Fast index scan

two。 Use explain plan for to view the execution plan

SQL > explain plan for select * from emp where sal > 3000

Explained.

SQL > seletc * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 3065173639

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 7 | 266 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 7 | 266 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IN_SAL | 7 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

-

2-access ("SAL" > 3000)

14 rows selected.

SQL >

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.