SQL Tuning From SQL*PLUS

DECLARE
l_sql_tune_task_id  VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap  => 3542,
end_snap    => 3565,
sql_id      => ‘amr9u05zfmqv3′,
scope       => DBMS_SQLTUNE.scope_comprehensive,
time_limit  => 60,
task_name   => ‘amr9u05zfmqv3_AWR_tuning_task’,
description => ‘Tuning task for statement amr9u05zfmqv3  in AWR.’); DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘amr9u05zfmqv3_AWR_tuning_task’);

EXEC DBMS_SQLTUNE.interrupt_tuning_task(task_name => ‘amr9u05zfmqv3_AWR_tuning_task’);

EXEC DBMS_SQLTUNE.resume_tuning_task(task_name => ‘amr9u05zfmqv3_AWR_tuning_task’);

EXEC DBMS_SQLTUNE.cancel_tuning_task(task_name => ‘amr9u05zfmqv3_AWR_tuning_task’);

SELECT task_name, status FROM dba_advisor_log WHERE owner = ‘SYS’ and TASK_NAME=’amr9u05zfmqv3_AWR_tuning_task’;

SELECT DBMS_SQLTUNE.report_tuning_task(‘amr9u05zfmqv3_AWR_tuning_task’] AS recommendations FROM dual;

View to find Sessions that Consume CPU

CREATE OR REPLACE FORCE VIEW SYSTEM.CPU_PER_SESSION
(SID, SERIAL#, USERNAME, OSUSER, MODULE,
STATUS, PROCESS_ID, SCNDS_IDLE, SCNDS_SINCE_LOGON, CPU_USAGE,
SWITCH_CODE)
AS
SELECT
SID, serial#, username, osuser, MODULE, status, process_id, scnds_idle,
scnds_since_logon, cpu_usage,
DECODE
(xyz,
2, NULL,
‘BEGIN DBMS_RESOURCE_MANAGER.switch_consumer_group_for_sess(‘
|| SID
|| ‘,’
|| serial#
|| ‘,”FTS_WEB”); END;’
) AS switch_code
FROM (SELECT   DECODE (c.username,
‘SYS’, 2,
‘SYSTEM’, 2,
NULL, 2,
1
) AS xyz, a.SID, c.serial#, c.username, c.osuser,
c.MODULE, c.status, d.spid AS “PROCESS_ID”,
DECODE (c.username,
NULL, 0,
c.last_call_et
) AS “SCNDS_IDLE”,
((SYSDATE – c.logon_time) * 60 * 60 * 24
) AS “SCNDS_SINCE_LOGON”,
trunc(a.VALUE/100) AS cpu_usage
FROM v$sesstat a, v$sysstat b, v$session c, v$process d
WHERE c.SID = a.SID
AND c.paddr(+) = d.addr
AND a.statistic# = b.statistic#
AND b.NAME = ‘CPU used by this session’
ORDER BY xyz, c.status ASC, a.VALUE DESC);

COMMENT ON TABLE SYSTEM.CPU_PER_SESSION IS ‘Occasionally someone will run a script or a process that will consume the CPU to a point that everything else slows way down.  Querying this view can help you find which user is at fault, and will help you switch  the offending session to a resource group with restriced use of the CPU. Look for a  session which has a STATUS that is ACTIVE. This indicates a script that is still  running. Also look to see if the CPU_USAGE is roughly the same as SCNDS_SINCE_LOG  (or more). CPU_USAGE is actually the seconds of cpu usage. This number may be higher  than total SCNDS_SINCE_LOG if you have more than one CPU on your box.

CREATE PUBLIC SYNONYM CPU_PER_SESSION FOR SYSTEM.CPU_PER_SESSION;

then ,

select * from CPU_PER_SESSION;

This script is created by Edward Stoever.

Thanks edward.