Summary:
You can create an SQL TUNING TASK manually ad hoc with the following simple steps.
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
1. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.
In case this is a current sql running use the v$session.
select sql_id from v$session where sid = :x
2. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_137090';
4. When the task has a status=COMPLETED, then run:
SET LONG 5000
SET LONGCHUNKSIZE 4000
SET LINESIZE 200
You can create an SQL TUNING TASK manually ad hoc with the following simple steps.
ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
1. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.
In case this is a current sql running use the v$session.
select sql_id from v$session where sid = :x
2. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'd15cdr0zt3vtp');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_137090
2. Run the SQL TUNING TASKbegin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_137090');
end;
/
3. You can monitor the processing of the tuning task with the statementSELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_137090';
4. When the task has a status=COMPLETED, then run:
SET LONG 5000
SET LONGCHUNKSIZE 4000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_137090') AS recommendations FROM dual;
5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile. begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_137090', task_owner => 'SYSTEM', replace => TRUE);
end;
/
6. You can check the database sql profiles with the statement:
select * from dba_sql_profiles;
In case you want to disable an sql profile use the statement:
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/
E.g:
SYS> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_137090') AS recommendations FROM dual;
RECOMMENDATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_137090
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 03/09/2015 20:11:41
Completed at : 03/09/2015 20:16:12
-------------------------------------------------------------------------------
Schema Name: DBSNMP
SQL ID : d15cdr0zt3vtp
SQL Text : SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD
HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS
failed_count FROM sys.dba_audit_session WHERE returncode != 0
AND TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS') >=
TO_CHAR(current_timestamp - TO_DSINTERVAL('0 0:30:00'),
'YYYY-MM-DD HH24:MI:SS')
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.13%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_137090',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 128 will improve its response time
99.13% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.21% 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
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 4127218927
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 167K (2)| 00:33:33 |
| 1 | SORT AGGREGATE | | 1 | 52 | | |
| 2 | NESTED LOOPS OUTER | | 809 | 42068 | 167K (2)| 00:33:33 |
|* 3 | HASH JOIN RIGHT OUTER | | 809 | 38832 | 167K (2)| 00:33:33 |
| 4 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 208 | 1040 | 1 (0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER | | 809 | 34787 | 167K (2)| 00:33:33 |
| 6 | INDEX FULL SCAN | I_SYSTEM_PRIVILEGE_MAP | 208 | 1040 | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER| | 809 | 30742 | 167K (2)| 00:33:33 |
|* 8 | INDEX RANGE SCAN | I_AUDIT_ACTIONS | 3 | 12 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | AUD$ | 809 | 27506 | 167K (2)| 00:33:33 |
|* 10 | INDEX RANGE SCAN | I_STMT_AUDIT_OPTION_MAP | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SPX"."PRIVILEGE"(+)=(-"AUD"."PRIV$USED"))
5 - access("SPM"."PRIVILEGE"(+)=(-"AUD"."LOGOFF$DEAD"))
7 - access("AUD"."ACTIO
No comments:
Post a Comment