1. SQL Plus
If you run SQL, just put in SQL Plus
SET AUTOTRACE ON before running sql statement.
SQL> set autotrace on
SQL> select * from test.test;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
2. Using EXPLAIN PLAN from SQL Plus and the query table plan_table where the explain plan is stored, or user DBMS_XPLAN.DISPLAY
SQL> delete plan_table;
2 rows deleted.
SQL> explain plan for select /* +rule */ * from test;
Explained.
SQL> select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id; 2 3 4 5 6 7 ^R
connect by prior id=parent_id;
SELECT STATEMENT ()
TABLE ACCESS (FULL)
TEST
SQL> select * from table(dbms_xplan.display);
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 5 | 65 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
12 rows selected.
SQL>
3. Using DBMS_XPLAN.DISPLAY_CURSOR to view explain plan for a specific sql_id
SQL> select /* TST */ * from test;
2
1
1
1
1
SQL> select sql_id, sql_text from v$sql where sql_text like '%TST%';
92ca0n47j90vn
select /* TST */ * from test
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('92ca0n47j90vn',0));
SQL_ID 92ca0n47j90vn, child number 0
-------------------------------------
select /* TST */ * from test
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| TEST | 5 | 65 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
SQL>
or use following SQL to view explain plans for sqls that contains some string
SQL> SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '% 2 TST%';
SQL_ID 92ca0n47j90vn, child number 0
-------------------------------------
select /* TST */ * from test
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| TEST | 5 | 65 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
No comments:
Post a Comment