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

1.PLAN_table

column query_plan format a55

column cardinality format 99999

column cost format 99999

delete from plan_table;

set lines 100

set pages 100

set echo on

EXPLAIN PLAN FOR

SELECT *

FROM hr.employees JOIN hr.departments USING (department_id);

SELECT RTRIM (LPAD (' ', 2 * LEVEL) ||

RTRIM (operation) || ' ' ||

RTRIM (options) || ' ' ||

object_name) query_plan,

cost, cardinality

FROM plan_table

CONNECT BY PRIOR id = parent_id

START WITH id = 0 ;

SELECT * FROM TABLE(dbms_xplan.display());

2. Query the top 10 sql statements that consume the most total time:

SELECT sql_id, child_number, sql_text, elapsed_time

FROM (SELECT sql_id,

child_number,

sql_text,

elapsed_time,

cpu_time,

disk_reads,

rank() over(ORDER BY elapsed_time DESC) AS elapsed_rank

FROM v$sql)

WHERE elapsed_rank

select * from table(dbms_xplan.display_cursor('bdfmh55d9vy9y',0,'TYPICAL'));

The DBMS_XPLAN.display function displays the execution plan in PLAN_TABLE, while DISPLAY_CURSOR displays the execution plan information cached in v$sql_plan.

explain plan for SELECT department_name, last_name, job_title

FROM hr.employees

JOIN hr.departments

USING (department_id)

JOIN hr.jobs

USING (job_id)

7 ORDER BY department_name, job_title;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'TYPICAL -BYTES'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 3301068746

--------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 10 (20)| 00:00:01 |

| 1 | SORT ORDER BY | | 106 | 10 (20)| 00:00:01 |

|* 2 | HASH JOIN | | 106 | 9 (12)| 00:00:01 |

| 3 | MERGE JOIN | | 106 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 107 | 4 (25)| 00:00:01 |

| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 3 (0)| 00:00:01 |

| 8 | TABLE ACCESS FULL | JOBS | 20 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("EMPLOYEES". "JOB_ID"="JOBS". "JOB_ID")

6 - access("EMPLOYEES". "DEPARTMENT_ID"="DEPARTMENTS". "DEPARTMENT_ID")

filter("EMPLOYEES". "DEPARTMENT_ID"="DEPARTMENTS". "DEPARTMENT_ID")

22 rows selected.

SQL> explain plan for select department_name,last_name from hr.employees join hr.departments using(department_id);

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +PREDICATE'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1473400139

----------------------------------------------------------

| Id | Operation | Name |

----------------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | MERGE JOIN | |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |

| 3 | INDEX FULL SCAN | DEPT_ID_PK |

|* 4 | SORT JOIN | |

| 5 | VIEW | index$_join$_001 |

|* 6 | HASH JOIN | |

| 7 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX |

| 8 | INDEX FAST FULL SCAN | EMP_NAME_IX |

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("EMPLOYEES". "DEPARTMENT_ID"="DEPARTMENTS". "DEPARTMENT_ID")

filter("EMPLOYEES". "DEPARTMENT_ID"="DEPARTMENTS". "DEPARTMENT_ID")

6 - access(ROWID=ROWID)

22 rows selected.

Virtual index:

A virtual index is an index for which no corresponding physical entity has been created. The purpose of virtual indexes is to determine whether an index can be used for SQL optimization without consuming time, CPU, IO and a lot of storage space to actually create the index.

SQL> explain plan for select * from sh.sales where quantity_sold>10000;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +COST'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1744557519

-------------------------------------------------------------------------

| Id | Operation | Name | Cost (%CPU)|

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 (0)|

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 4 (0)|

| 2 | INDEX RANGE SCAN | INDEX_QU_SOLD | 3 (0)|

-------------------------------------------------------------------------

9 rows selected

SQL> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

SQL> create index sh.sales_vi1 on sh.sales(quantity_sold) nosegment;

Index created.

Track Oracle Execution:

SQL> alter session set sql_trace=true;

Session altered.

SQL> begin

2 dbms_session.session_trace_enable(waits=>true,binds=>false,plan_stat=>'all_executions');

3 end;

4 /

PL/SQL procedure successfully completed.

Identification of trace files:

SQL> alter session set tracefile_identifier=GUY;

Session altered.

[oracle@node2 trace]$ ls -l *GUY*

-rw-r----- 1 oracle asmadmin 36056 Jun 21 14:54 MECBS2_ora_24731_GUY.trc

-rw-r----- 1 oracle asmadmin 328 Jun 21 14:54 MECBS2_ora_24731_GUY.trm

Get status of trace file:

SELECT s.sql_trace,

s.sql_trace_waits,

s.sql_trace_binds,

traceid,

tracefile

FROM v$session s

JOIN v$process p

ON (p.addr = s.paddr)

WHERE audsid = userenv('SESSIONID');

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.