Displaying an Execution Plan for a Query
SQL> set autotrace on
SELECT last_name, first_name
FROM employees NATURAL JOIN departments
WHERE employee_id = 101;
AUTOTRACE Option Execution Plan Shown Statistics Shown Query Executed
-------------------------------------------------------------------------------------
AUTOT[RACE] OFF No No Yes
AUTOT[RACE] ON Yes Yes Yes
AUTOT[RACE] ON EXP[LAIN] Yes No Yes
AUTOT[RACE] ON STAT[ISTICS] No Yes Yes
AUTOT[RACE] TRACE[ONLY] Yes Yes Yes, but query output is suppressed.
AUTOT[RACE] TRACE[ONLY] Yes No No
EXP[LAIN]
SQL> set autot trace exp
run sql...
SQL> set autot trace stat
SQL> set timi on
SQL> /
43 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1862 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
43 rows processed
swith of the trace one the tracing is done ..
sql>set autot off
to know the status os auto trace
SQL> show autot
autotrace OFF
Customizing Execution Plan Output
1.
explain plan for
SELECT last_name, first_name
FROM employees JOIN departments USING(department_id)
WHERE employee_id = 101;
Explained.
SELECT * FROM table(dbms_xplan.display);
to see the basic most basic execution plan ..
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC'));
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
SELECT * FROM table(dbms_xplan.display(null,null,'TYPICAL -BYTES -ROWS'));
Reading an Execution Plan
set autotrace trace explain
Monitoring Long-Running SQL Statements
Keep in mind that V$SQL represents SQL held in the shared pool, and is aged out faster than the data in V$SQLSTATS, so this query will not return data for SQL that has been already aged out of the shared pool.
With a query against the V$SESSION_LONGOPS view, you can quickly get an idea of how long a given query component will execute, and when it will finish:
SELECT username, target, sofar blocks_read, totalwork total_blocks,
round(time_remaining/60) minutes
FROM v$session_longops
WHERE sofar <> totalwork
and username = 'HR';
USERNAME TARGET BLOCKS_READ TOTAL_BLOCKS MINUTES
------------ -------------------- ----------- ------------ ----------
HR HR.EMPLOYEES_BIG 81101 2353488 10
Identifying Resource-Consuming SQL Statements That Are Currently Executing
Look at the V$SQLSTATS view, which gives information about currently or recently run SQL statements. If you wanted to get the five recent SQL statements that performed the most disk I/O, you could issue the following query:
SELECT sql_text, disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5;
the top five SQL statements by CPU time, sorts, loads, invalidations, or any other column, simply replace the disk_reads column in the foregoing query with your desired column. For instance, if you were more interested in the queries that ran the longest, you could issue the following query:
SELECT sql_text, elapsed_time FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY elapsed_time DESC)
WHERE rownum <= 5;
Sometimes, there are SQL statements that are related to the database background processing of keeping the database running, and you may not want to see those statements, but only the ones related to your application. If you join V$SQLSTATS to V$SQL, you can see information for particular users. See the following example:
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'SCOTT'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;
Keep in mind that V$SQL represents SQL held in the shared pool, and is aged out faster than the data in
V$SQLSTATS, so this query will not return data for SQL that has been already aged out of the shared pool.
Seeing Execution Statistics for Currently Running SQL
Initialization parameter STATISTICS_LEVEL must be set to TYPICAL or ALL, and
CONTROL_MANAGEMENT_PACK_ACCESS must be set to DIAGNOSTIC+TUNING for SQL monitoring to occur. Diagnostic and Tuning Pack Licenses are required to use V$SQL_MONITOR.
You can use the V$SQL_MONITOR view to see real-time statistics of currently running SQL and see the resource consumption used for a given query based on such statistics as CPU usage, buffer gets, disk reads, and elapsed time of the query. Let’s first find a current executing query within our database:
SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';
SID SQL_TEXT
---------- --------------------------------------------------------
100 select department_name, city, avg(salary)
from employees_big join departments using(department_id)
join locations using (location_id)
group by department_name, city
having avg(salary) > 2000
order by 2,1
For the foregoing executing query found in V$SQL_MONITOR, we can see the resource utilization for that statement as it executes:
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
WHERE SID=100
AND status = 'EXECUTING';
SID BUFFER_GETS DISK_READS CPU_SECONDS
---------- ----------- ---------- -----------
100 149372 4732 39.1
The V$SQL_MONITOR view contains currently running SQL statements, as well as recently run SQL statements. If you wanted to see the sessions that are running the top five CPU-consuming queries in your database, you could issue the following query:
SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)
WHERE rownum <= 5;
SID BUFFER_GETS DISK_READS CPU_SECONDS
---------- ----------- ---------- -----------
20 1332665 30580 350.5
105 795330 13651 269.7
20 259324 5449 71.6
20 259330 5485 71.3
100 259236 8188 67.9
Note:
SQL statements are monitored in V$SQL_MONITOR under the following conditions:
• Automatically for any parallelized statements
• Automatically for any DML or DDL statements
• Automatically if a particular SQL statement has consumed at least 5 seconds of CPU or I/O time
• Monitored for any SQL statement that has monitoring set at the statement level
Forcefully turn on & turn off the sql monitor a hint can be used
SELECT /*+ monitor */ ename, dname
FROM emppart JOIN dept USING (deptno);
If we wanted to see all executions for a given query (based on the SQL_ID column), we can get that information by querying on the three necessary columns to drill to a given execution of a SQL query:
SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)
WHERE rownum <= 5;
SQL_ID SQL_EXEC_START SQL_EXEC_ID BUFFER_GETS DISK_READS CPU_SECS
------------- ------------------- ----------- ----------- ---------- ----------
21z86kt10h3rp 2013-08-26:14:06:02 16777218 591636 6 28.3
21z86kt10h3rp 2013-08-26:14:06:36 16777219 507484 0 27.8
21z86kt10h3rp 2013-08-26:14:07:17 16777220 507484 0 27.6
For parallel query
Keep in mind that if a statement is running in parallel, one row will appear for each parallel thread for the query, including one for the query coordinator. However, they will share the same SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID values. example for a parallelized query, along with parallel slave information denoted by the PX_SERVER# column:
SELECT sql_id, sql_exec_start, sql_exec_id, px_server# px#, disk_reads,
cpu_time/1000000 cpu_secs, buffer_gets
FROM v$sql_monitor
WHERE status = 'EXECUTING'
ORDER BY px_server#;
SQL_ID SQL_EXEC_S SQL_EXEC_ID PX# DISK_READS CPU_SECS BUFFER_GETS
------------- ---------- ----------- --- ---------- -------- -----------
55x73dhhx277n 2013-08-26 16777216 1 98 .673897 11869
55x73dhhx277n 2013-08-26 16777216 2 100 .664898 12176
55x73dhhx277n 2013-08-26 16777216 3 72 .481927 8715
55x73dhhx277n 2013-08-26 16777216 4 143 .752885 17283
55x73dhhx277n 2013-08-26 16777216 0 .007999 27
to perform a simple aggregation for a given query, in this case, our parallelized query, the aggregation is done on the three key columns that make up a single execution of a given SQL statement:
SELECT sql_id,sql_exec_start, sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp'
GROUP BY sql_id, sql_exec_start, sql_exec_id;
SQL_ID SQL_EXEC_S SQL_EXEC_ID BUFFER_GETS DISK_READS CPU_SECONDS
------------- ---------- ----------- ----------- ---------- -----------
21z86kt10h3rp 2013-08-26 16777218 591636 6 28.3
If you wanted to perform an aggregation for one SQL statement, regardless of the number of times is has been executed, simply run the aggregate query only on the SQL_ID column, as shown here:
SELECT sql_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_seconds
FROM v$sql_monitor
WHERE sql_id = '21z86kt10h3rp '
GROUP BY sql_id;
Monitoring Progress of a SQL Execution Plan
couple of ways to get information to see where a query is executing in terms of the execution plan. First, by querying the V$SQL_PLAN_MONITOR view, you can get information for all queries that are in progress, as well as recent queries that are complete.
To see information for the foregoing query while it is currently running, you can issue a query like the one shown here (some rows have been removed for conciseness):
column operation format a25
column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;
SID SQL_ID STATUS LINE OPERATION ROWS
----- ------------- --------- ---- --------------------------- -------
423 7wjb00vsk8btp EXECUTING 0 SELECT STATEMENT 0
1 FILTER 0
2 SORT GROUP BY 0
3 HASH JOIN 9785901
4 MERGE JOIN 27
5 TABLE ACCESS BY INDEX ROWID 27
6 INDEX FULL SCAN 27
7 SORT JOIN 27
8 VIEW 23
9 HASH JOIN 23
10 INDEX FAST FULL SCAN 23
11 INDEX FAST FULL SCAN 23
12 TABLE ACCESS FULL 9785901
set pages 9999
set long 1000000
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR
(sql_id=> '7wjb00vsk8btp ',type=>'HTML')
FROM dual;
We want the detail aggregated, and we want to see just the most basic level of detail
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR
(sql_id=>'7wjb00vsk8btp ',event_detail=>'NO',report_level=>'BASIC') FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
select department_name, city, avg(salary)
from employees_big join departments using(department_id)
join locations using (location_id)
group by department_name,city
having avg(salary) > 2000 order by 2,1
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : HR (423:25891)
SQL ID : 7wjb00vsk8btp
SQL Execution ID : 16777219
Execution Started : 08/26/2013 16:20:52
First Refresh Time : 08/26/2013 16:20:58
Last Refresh Time : 08/26/2013 16:21:12
Duration : 20s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@lxdnt24b (TNS V1-V3)
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 20 | 20 | 0.03 | 1 | 372K |
=================================================
Refer to the Oracle PL/SQL Packages and Types Reference for a complete list of all the parameters that can be used to execute the REPORT_SQL_MONITOR function. It is a very robust function, and there are a myriad of permutations to report on, based on your specific need.
Identifying Resource-Consuming SQL Statements That Have Executed in the Past
SELECT * FROM (
SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,
sum(disk_reads_total) disk_reads_total,
sum(executions_delta) execs_delta,
sum(executions_total) execs_total
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY 2 desc)
WHERE rownum <= 5;
SQL_ID DISK_READS_DELTA DISK_READS_TOTAL EXECS_DELTA EXECS_TOTAL
------------- ---------------- ---------------- ----------- -----------
36bdwxutr5n75 6306401 10933153 13 24
0bx1z9rbm10a1 1590538 1590538 2 2
0gzf8010xdasr 970292 1848743 1 3
1gtkxf53fk7bp 969785 969785 7 7
4h81qj5nspx6s 869588 869588 2 2
Since the actual text of the SQL isn’t stored in DBA_HIST_SQLSTAT, you can then look at the associated DBA_HIST_SQLTEXT view to get the SQL text for the query with the highest number of disk reads:
SELECT sql_text FROM dba_hist_sqltext
WHERE sql_id = '7wjb00vsk8btp ';
To see explain plan for historical sql statement
If you want to see explain plan information for historical SQL statements, there is an associated view available to retrieve that information for a given query. You can access the DBA_HIST_SQL_PLAN view to get the explain plan information for historical SQL statements.
SELECT id, operation || ' ' || options operation, object_name, cost, bytes
FROM dba_hist_sql_plan
WHERE sql_id = '7wjb00vsk8btp '
ORDER BY 1;
ID OPERATION OBJECT_NAME COST BYTES
---- --------------------------- ----------------- ---- ----------
0 SELECT STATEMENT 549
1 FILTER
2 SORT GROUP BY 549 858
3 HASH JOIN 546 7668102
4 MERGE JOIN 4 837
5 TABLE ACCESS BY INDEX ROWID DEPARTMENTS 2 513
6 INDEX FULL SCAN DEPT_LOCATION_IX 1
7 SORT JOIN 2 276
8 VIEW index$_join$_004 2 276
9 HASH JOIN
10 INDEX FAST FULL SCAN LOC_CITY_IX 1 276
11 INDEX FAST FULL SCAN LOC_ID_PK 1 276
12 TABLE ACCESS FULL EMPLOYEES_BIG 541 15729449
Comparing SQL Performance After a System Change
Note : you must be licensed for Real Application Testing in order to use the SQL Performance Analyzer.
Note: ADVISOR system privilege is needed to perform the analysis tasks using DBMS_SQLPA.
1. Create an analysis task based on a single or series of SQL statements.
2. Run an analysis for those statements based on your current configuration.
3. Perform the given change to your environment (like a database upgrade).
4. Run an analysis for those statements based on the new configuration.
5. Run a “before and after” comparison to determine what impact the change has on the performance of your SQL statement(s).
6. Generate a report to view the output of the comparison results.
following example for a single query. First, we need to create an analysis task.
For the database upgrade example, this would be done on an appropriate test database that is Oracle 12c. In this case, within SQL Plus, we will do the analysis for one specific SQL statement:
variable g_task varchar2(100);
EXEC :g_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sql_text => 'select last_name
|| ',' || first_name, department_name from employees join departments using(department_id)');
In order to properly simulate this scenario on our Oracle 12c database, we then set the optimizer_features_enable parameter back to Oracle 11g. We then run an analysis for our query using the “before” conditions—in this case, with a previous version of the optimizer:
alter session set optimizer_features_enable='11.2.0.3';
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task,execution_type=>'test execute',execution_name=>'before_change');
After completing the before analysis, we set the optimizer to the current version of our database, which, for this example, represents the version to which we are upgrading our database:
alter session set optimizer_features_enable='12.1.0.1.1';
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task,execution_type=>'test execute', execution_name=>'after_change');
Now that we have created our analysis task based on a given SQL statement, and have run “before” and “after” analysis tasks for that statement based on the changed conditions, we can now run an analysis task to compare the results of the two executions of our query. There are several metrics that can be compared. In this case, we are comparing “buffer gets”:
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>:g_task,execution_type=>'COMPARE
PERFORMANCE',execution_name=>'compare change',execution_params =>
dbms_advisor.arglist('comparison_metric','buffer_gets'));
Finally, we can now use the REPORT_ANALYSIS_TASK function of the DBMS_SQLPA package in order to view the results. In the following example, we want to see output only if the execution plan has changed. The output can be in several formats, the most popular being HTML and plain text.
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool compare_report.txt
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(:g_task, 'TEXT', 'CHANGED_PLANS','ALL')
FROM DUAL;
Some of the possible key reasons to consider doing a “before and after” performance analysis include the following:
• Initialization parameter changes
• Database upgrades
• Hardware changes
• Operating system changes
• Application schema object additions or changes
• The implementation of SQL baselines or profiles
you may want to narrow down the information shown from the REPORT_ANALYSIS_TASK function to show information only on SQL statements such as the following:
• Those statements that show regressed performance
• Those statements with a changed execution plan
• Those statements that show errors in the SQL statements
