-- Check The Details Of Running SQL
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '10600'
ORDER BY a.spid, c.piece
 
-- Trace Query ID Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s  WHERE s.sql_id='fdr7qnnnnmm64'
 
-- Execution Time Of SQL_ID By Session Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.sql_child_number,
h.sql_plan_hash_value,  h.module,
SUM(h.wait_time + h.time_waited) / 1000 "Total Wait Time (Sec)"
FROM dba_hist_active_sess_history h --alternative table is v$active_session_history
WHERE h.sql_id = 'fdr7qnnnnmm64'
--AND h.module = 'XLAACCPB'
--AND h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
GROUP BY h.session_id, h.session_serial#, h.sql_id,
h.sql_child_number, h.sql_plan_hash_value,h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC
 
-- Execution Time Of SQL_ID By Date Using Active Session History (ASH)
SELECT trunc(h.sample_time) "Execution Date", h.sql_id, h.sql_child_number,
h.sql_plan_hash_value, h.module,
SUM(h.wait_time + h.time_waited) / 1000 "Total Wait Time (Sec)"
FROM dba_hist_active_sess_history h --alternative table is v$active_session_history
WHERE h.sql_id = 'fdr7qnnnnmm64'
--AND h.module = 'XLAACCPB'
--AND h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
GROUP BY trunc(h.sample_time), h.sql_id,
h.sql_child_number, h.sql_plan_hash_value, h.module
ORDER BY trunc(h.sample_time), SUM(h.wait_time + h.time_waited) DESC
 
-- Get The Detail Explain  Plan Using SQL ID
SELECT plan_table_output FROM TABLE
(dbms_xplan.display_cursor('fdr7qnnnnmm64'))
 
-- Get The Detail Explain  Plan With Advanced Details Using SQL ID
SELECT plan_table_output FROM TABLE
(dbms_xplan.display_cursor('fdr7qnnnnmm64','0','ADVANCED'))
 
-- Get The History Of Explain Plan Execution Using SQL ID
SELECT plan_table_output FROM TABLE
(dbms_xplan.display_awr('fdr7qnnnnmm64'))
 
-- Get The History Of Explain Plan Execution With Advanced Details Using SQL ID
SELECT plan_table_output FROM TABLE
(dbms_xplan.display_awr('fdr7qnnnnmm64','2761201612', NULL,'ADVANCED'))

DECLARE
 
cl_sql_text CLOB;
 
hint_spec sys.sqlprof_attr;
 
BEGIN
 
--SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id='fdr7qnnnnmm64';
 
SELECT sql_text INTO cl_sql_text FROM dba_hist_sqltext WHERE sql_id = 'fdr7qnnnnmm64';
 
SELECT
extractvalue(VALUE(d), '/hint') AS outline_hints
BULK COLLECT
INTO
hint_spec
FROM
xmltable('/*/outline_data/hint'
passing (
SELECT
xmltype(other_xml) AS xmlval
FROM
dba_hist_sql_plan --alternative table is v$sql_plan
WHERE
sql_id = 'fdr7qnnnnmm64'
AND plan_hash_value = '82930460'
AND other_xml IS NOT NULL
)
) d;
 
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text, 
profile => hint_spec,
name => 'PROFILE NAME', 
force_match => TRUE);
 
END;
 
/