using dba_hist_sqlstat
Rajeshwaran Jeyabal, June 13, 2019 - 12:18 pm UTC
how about using dba_hist_sqltext and dba_hist_sqlstat like this?
but this is subjected to AWR retention period (default is 8 day). since each execution of plsql call is tracked in AWR repository - can't we use something like this to get the executions count at hourly basis like this?
demo@PDB1> select t1.snap_id,t1.sql_id,t2.sql_text,t1.executions_delta
2 from dba_hist_sqlstat t1,
3 dba_hist_sqltext t2
4 where t1.sql_id = t2.sql_id
5 and t2.command_type = 47
6 and lower(t2.sql_text) like '%dbms_output.%'
7 order by t1.snap_id ;
SNAP_ID SQL_ID SQL_TEXT EXECUTIONS_DELTA
---------- ------------- ------------------------------------------------------- ----------------
497 9babjv8yq8ru3 BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 66
507 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
510 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 4
525 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 4
536 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
544 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
546 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
548 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
557 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
570 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 4
572 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
573 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 3
583 b9c6ffh8tc71f BEGIN dbms_output.enable(NULL); END; 4
13 rows selected.
demo@PDB1>
June 13, 2019 - 2:26 pm UTC
AWR only stores "top" statements. So this will miss rarely executed fast procedures.
Review feedback
Sanjiv Singh, June 13, 2019 - 1:21 pm UTC
Thank You for your quick response, reason for avoiding audit root is this will go by change request route and application wants to avoid it
June 13, 2019 - 2:30 pm UTC
The "application" wants to avoid it!?
I know change request processes can be excessively bureaucratic in some companies. But honestly this is a weak reason not do it. If you genuinely care about finding out which procedures are executed, this is best method.
using dba_hist_sqlstat
Rajeshwaran Jeyabal, June 14, 2019 - 2:57 am UTC
AWR only stores "top" statements. So this will miss rarely executed fast procedures.
AWR "report" only shows "top" statements.
but since the above sql is from AWR repository - that contains the samples for every seconds across all the connections - by looking into AWR repository do we still have the chance of missing something?
only thing i can think off is - if a sql or pl/sql block executes in fraction of seconds, then they can't be available in repository - kindly advice.
Review feedback
Sanjiv Singh, June 14, 2019 - 11:15 am UTC
application owner is seeing risk and don't want to go by change request route as no original developers are there resolve any issues. Is there way to collect name and owner for SP's with v$ tables
June 14, 2019 - 1:38 pm UTC
Enabling auditing is standard database functionality. The risk is tiny.
There's no solution using v$ tables that is guaranteed to capture every execution.
Trace manually
A reader, June 15, 2019 - 12:27 pm UTC
hey Sanjiv Singh,
May be you can also create a "usage table" which contain like run_date,run_by_user,terminal_detail,......
And place it inside your procedure so when procedure run also data insert into "usage table".
I hope you understand.
More review acceptable..
June 17, 2019 - 8:33 am UTC
Yep, though that would require changing every procedure. Which the OP doesn't want to do...