Oracle Performance Tuning Learning 0621

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 for the top 10 SQL statements with the most total time consumed:

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 < 10;

Get execution plan through sql_id:sql> select * FROM table (dbms_xplan.display_cursor (' bdfmh45d9vy9y ', 0, ' typical '));

The Dbms_xplan.display function shows the execution plan in plan_table, while Display_cursor shows information about the execution plan 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 ")

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)

Rows selected.

Virtual index:

A virtual index is an index that does not create a corresponding physical entity. The purpose of a virtual index is to interpret whether an index can function with SQL optimization without consuming time, CPU consumption, and consuming Io, which has consumed a lot of storage space to actually create an 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.

Identify trace files:

Sql> alter session set Tracefile_identifier=guy;

Session altered.

[Email protected] trace]$ ls-l *guy*
-RW-R-----1 Oracle asmadmin 36056 June 14:54 Mecbs2_ora_24731_guy.trc
-RW-R-----1 Oracle asmadmin 328 June 14:54 MECBS2_ORA_24731_GUY.TRM
To get the status of a 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 ');

Oracle Performance Tuning Learning 0621