SQL> select name, open_mode from v$database;
NAME
OPEN_MODE
--------- --------------------
DB11GDEV READ
WRITE
SQL> select distinct sid from v$mystat;
SID
----------
62
Identify the sqlid of query that creating issue. For a
demo purpose I identified a query which creates the issue (i.e bkrm52bzasgd2). Below query will fetch the top sqls running
on the database.
col type for a10
col username for a10
select
(select username from dba_users where user_id = a.user_id) usernm,
sql_id,
plan_hash,
type,
cpu,
wait,
io,
total
from (
select
ash.inst_id, ash.user_id, ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
sum(decode(ash.session_state,'ON CPU',1,0)) cpu,
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) wait ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) io ,
sum(decode(ash.session_state,'ON CPU',1,1)) total
from gv$active_session_history ash,
audit_actions aud
where SQL_ID is not NULL
and ash.sql_opcode=aud.action
and ash.sample_time > sysdate - &minutes /( 60*24)
group by inst_id, user_id, sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) a where rownum <= 20
/
Enter value for minutes: 20
USERNM SQL_ID PLAN_HASH TYPE CPU
WAIT IO TOTAL
---------- -------------
---------- ---------- ---------- ---------- ---------- ----------
FINANCE bkrm52bzasgd2 3244424597 SELECT 5 0 54 59
SYS 5h7w8ykwtb2xt 4166561850 INSERT 0 0 1 1
SYS 1cq3qr774cu45 1031030155 INSERT 0 0 1 1
SYS 84qubbrsr0kfn 3675384499 INSERT 1 0 0 1
4 rows selected.
Elapsed: 00:00:00.01
Let’s create a
tuning task id from below procedure.
SQL> set echo on
set time on
set timing on
set serveroutput on
declare
stmt_task VARCHAR2(40);
begin
stmt_task :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'bkrm52bzasgd2');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task
);
end;
/
task_id: TASK_5745
PL/SQL
procedure successfully completed.
__________________________________________________________________________________
****************************Run the SQL TUNING TASK********************************
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
14:21:55
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_5745');
PL/SQL
procedure successfully completed.
__________________________________________________________________________________
**********You
can monitor the processing of the tuning task with the statement ************
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
14:22:38
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_5745';
TASK_NAME
STATUS
------------------------------ -----------
TASK_5745 COMPLETED
14:22:44
SQL> SELECT TASK_NAME, STATUS,pct_completion_time,progress_metric FROM
DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_5745';
TASK_NAME STATUS PCT_COMPLETION_TIME PROGRESS_METRIC
------------------------------ -----------
------------------- ---------------
TASK_5745 COMPLETED 0 0
We can see our
tuning advisory completed. Let’s see the recommendation as below:
14:22:50
SQL> set long 10000
set
longchunksize 10000
set linesize
132
set pagesize
200
SQL> spool
SQL_TUNE_bkrm52bzasgd2.log
SQL>
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_5745')
AS recommendations FROM dual;
RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------
GENERAL
INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_5745
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 07/17/2018 14:21:55
Completed at
: 07/17/2018 14:22:03
-------------------------------------------------------------------------------
Schema Name:
FINANCE
SQL ID : bkrm52bzasgd2
SQL Text : select EMP_CODE,EMPLOYEE_NAME from
finance.EMPLOYEE_RECORDS where EMPLOYEE_CODE>
'41002'
-------------------------------------------------------------------------------
FINDINGS
SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding
(see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found
for this statement.
Recommendation (estimated benefit: 93.06%)
------------------------------------------
- Consider accepting the recommended SQL
profile to use parallel execution
for this statement.
execute
dbms_sqltune.accept_sql_profile(task_name => 'TASK_5745',
task_owner => 'SYS',
replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 16
will improve its response time
93.07% over the original plan. However, there
is some cost in enabling
parallel execution. It will increase the
statement's resource consumption by
an estimated 10.91% which may result in a
reduction of system throughput.
Also, because these resources are consumed
over a much smaller duration, the
response time of concurrent statements might
be negatively impacted if
sufficient hardware capacity is not
available.
The following data shows some sampled
statistics for this SQL from the past
week and projected weekly values when
parallel execution is enabled.
Past week
sampled statistics for this SQL
-----------------------------------------
Number of executions
0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec)
0
Projected
statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec)
0
2- Index Finding (see
explain plans section below)
--------------------------------------------------
The
execution plan of this statement can be improved by creating one or more
indices.
Recommendation
(estimated benefit: 99.92%)
------------------------------------------
-
Consider running the Access Advisor to improve the physical schema design
or
creating the recommended index.
create
index FINANCE.IDX$$_166D0001 on
FINANCE.EMPLOYEE_RECORDS(EMP_CODE","EMPLOYEE_NAME");
Rationale
---------
Creating the recommended indices
significantly improves the execution plan
of this statement. However, it might be
preferable to run "Access Advisor"
using a representative SQL workload as
opposed to a single statement. This
will allow to get comprehensive index
recommendations which takes into
account index maintenance overhead and
additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3244424597
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40817 | 438K| 92696 (1)| 00:18:33 |
|* 1 |
TABLE ACCESS FULL| MATERIAL_RECORDS_TESTBKP | 40817 | 438K| 92696 (1)| 00:18:33 |
----------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 -
filter("ILI_MATERIAL_NUMBER">'A41002')
2- Using New Indices
--------------------
Plan hash value: 944928231
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40817 | 438K|
65 (2)| 00:00:01 |
|* 1 |
INDEX FAST FULL SCAN| IDX$$_166D0001 | 40817 | 438K|
65 (2)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 -
filter("ILI_MATERIAL_NUMBER">'A41002')
3- Using Parallel Execution
---------------------------
Plan hash value: 1097799476
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40817 |
438K| 6425 (0)| 00:01:18 | |
| |
| 1 |
PX COORDINATOR | | |
| | | |
| |
| 2 |
PX SEND QC (RANDOM)| :TQ10000 | 40817 |
438K| 6425 (0)| 00:01:18 | Q1,00 | P->S | QC (RAND) |
| 3 |
PX BLOCK ITERATOR | | 40817 | 438K|
6425 (0)| 00:01:18 | Q1,00 | PCWC | |
|* 4 |
TABLE ACCESS FULL| MATERIAL_RECORDS_TESTBKP | 40817 | 438K|
6425 (0)| 00:01:18 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
4 -
filter("ILI_MATERIAL_NUMBER">'A41002')
-------------------------------------------------------------------------------
14:23:05
SQL> select * from dba_sql_profiles;
no rows
selected
We have two
findings by advisor. Either we can do SQL profiling or create one or more
indexes. Profiling will execute the query fast but required more resources. Let’s
implement 2nd advisory which will improve the performance of query
by creating one or more indices. This will reduce the cost from 92696 to 65.
14:32:18
SQL> create index FINANCE.IDX$$_166D0001 on
FINANCE.EMPLOYEE_RECORDS(EMP_CODE","EMPLOYEE_NAME");
Now we are gathering the stat to get the effective result.
SQL>
exec DBMS_STATS.GATHER_TABLE_STATS (ownname
=> 'FINANCE' , tabname => ' EMPLOYEE_RECORDS',cascade => true, estimate_percent =>
5,method_opt=>'for all indexed columns size 1', granularity => 'ALL',
degree => 1);
Let’s check the active plan and see if the cost reduced as below
query.
8:10:19
SQL> select plan_table_output from
table(dbms_xplan.display_cursor('&sql_id',&child));
Enter value
for sql_id: bkrm52bzasgd2
Enter value
for child: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bkrm52bzasgd2, child number 0
-------------------------------------
select EMP_CODE,EMPLOYEE_NAME
from
finance.EMPLOYEE_RECORDS where EMPLOYEE_CODE>
'41002'
Plan hash value: 944928231
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 57 (100)|
|
|* 1 |
INDEX FAST FULL SCAN| IDX$$_166D0001 | 34637 | 372K|
57 (2)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
1 - filter("ILI_MATERIAL_NUMBER">'A41002')
19 rows selected.
We can see the cost reduced to 57 and now the query takes
only 00:00:01 seconds to complete.
