???????????
?????????DBA ?????? Oracle ????????? Top SQL?????? SQL ????л?????? SQL ??????????????????? Top SQL ??????Щ?????????????Oracle DBA ????????? SQL ????????????????????? Oracle ???????????????????? SQL ????????????
????????????????
???????????棬DBA ??????????? Top SQL??SQL ID ?c7452agj0s0t6????????9%??????????

??????? SQL ??????棬??????? SQL c7452agj0s0t6 ???????????飬?????????? SQL c7452agj0s0t6 ?????????????????

?????? SQL ??????У???????????????? V$ACTIVE_SESSION_HISTORY ??????
????SELECT GROUP_TYPE?? BUCKET_START?? BUCKET_END?? TM_GROUP_TYPE?? TM_BUCKET_START??
????TM_BUCKET_END?? SUM(TM_CPU_FIRST_BUCKET_VALUE) TM_CPU_FIRST_BUCKET_VALUE??
????SUM(TM_CPU_MIDDLE_BUCKETS_VALUE) TM_CPU_MIDDLE_BUCKETS_VALUE??
????... ?????? SQL ???
????FROM TABLE(SYS.GV$(CURSOR(
????... ?????? SQL ???
????SELECT ASH0.*
????FROM V$ACTIVE_SESSION_HISTORY ASH0
????... ?????? SQL ???
??????? SQL ???????????????????и? SQL ????????? PZ ???????Щ???? QC??query coordinator????????????????????? V$ACTIVE_SESSION_HISTORY ????в?????????????????????????д? SQL ??????????????????? QC ?????????

????????SQL ID ???????????
?????????? SQL c7452agj0s0t6 ?????????????? Oracle 11g ?е??1????????? SQL ID ??10046???????????????￡?
????alter system set events 'sql_trace [sql:c7452agj0s0t6] wait=true??bind=true??plan_stat=all_executions??level=12';
??????????2????? trace ????????????
????select value from v$diag_info where name='Default Trace File';
????????????? PRDDB_oraxxx.trc ??? grep c7452agj0s0t6???????????????￡???????????????? testapp????? dep=2????? SQL c7452agj0s0t6 ??????????? SQL?????????????????? SQL???????????????????? SQL ???????? SQL ??????
*** 2016-10-14 09:34:13.921
*** SESSION ID:(329.41269) 2016-10-14 09:34:13.921
*** CLIENT ID:() 2016-10-14 09:34:13.921
*** SERVICE NAME:(PRDDB) 2016-10-14 09:34:13.921
*** MODULE NAME:(python@testapp (TNS V1-V3)) 2016-10-14 09:34:13.921
*** ACTION NAME:() 2016-10-14 09:34:13.921
... ?????????
=====================
PARSING IN CURSOR #139987897061528 len=11079 dep=2 uid=87 oct=3 lid=87 tim=1476408854404253 hv=3792438054 ad='21b01ba960' sqlid='c7452agj0s0t6'
??????? SQL ID ????????????￡?
????alter system set events 'sql_trace [sql:c7452agj0s0t6] off';
????????????????????
???????????? SQL ?????? c7452agj0s0t6?????????????????10046??????????? SQL c7452agj0s0t6 ??????????????????????????? APPUSER ?? logon ?????????′????????????????10046?????
create or replace trigger sys.set_trace
after logon on database
when (user in ('APPUSER')??
declare
begin
execute immediate 'alter session set statistics_level=all';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever?? level 12''';
end set_trace;
/
????????????????????????????????????? PRDDB1_ora_10452.trc ???? SQL ID c7452agj0s0t6????????????? orasrp ??????????10046???????. orasrp ? Oracle Session Resource Profiler???????λ????? DBA ??????10046???????????????http://oracledba.ru/orasrp/
????orasrp PRDDB1_ora_10452.trc PRDDB1_ora_10452.html
????orasrp????? Oracle ????? tkprof??????????????????????????????????????????????????(Session Call Graph)???????????SQL hash value=3792438054 ? SQL c7452agj0s0t6??????2???? SQL hash value = 2036392974??

???? SQL ??????￡???? SQL c7452agj0s0t6 ???? dbms_sqltune.report_sql_monitor ??????? SQL Monitor ???????????????????????????2?????? job??????????????????????? SQL monitor ???棬??????й??????????????????????? job ???????????????? SQL monitor ????????????????
????select dbms_sqltune.report_sql_monitor(type=>:1?? sql_id=>:2?? sql_exec_id=>:3?? report_level=>'ALL') monitor_report from dual;
???????????????????? logon ????????????????????????????
????drop trigger sys.set_trace;
?????塢???
?????????????????? Oracle ?з????? SQL ID ????????10046????????????? SQL ??????????? PL/SQL ???? dbms_sqltune.report_sql_monitor??????????У??????????????????? PL/SQL ?????????Oracle ??????????????????????з????????? DBA ???????? orasrp ??10046?????????????????????????о????????????????????Ρ?