I have a query which takes times to execute. When a user connects to the system and I monitor database I also get the SQL statement which takes times. but when user complete his task and
after days he talks to me report takes too much time. I analyze system but i can't find any solution.
select s.PROGRAM_ID from v$sql s where s.SQL_ID = '6d2tyam6rkj96' ;
Output
PROGRAM_ID
----------
54913
And I also find procedure name which used in a report as below:-
select * from user_objects s where s.OBJECT_ID = 54913;
OBJECT_NAME
------------
MONTHLY_SALES_AREA_WISE
select * from dba_hist_sqlstat t where t.sql_id = '6d2tyam6rkj96';
I getting 3 rows with different snap_id but I do not view my program_id in history dynamic view of Oracle.
So how can I view program_id in dba_hist_* dynamic performance view or any other way I getting object name or object id?
First up: the dba_hist* views are part of AWR. Which you need the Diagnostics and Tuning licenses for. Ensure you have these!
I'm not sure what you're trying to do here by looking up the program_id.
Surely you want to look for the text of the SQL statement?
You can get this from v$sql:
select sql_text from v$sql
where sql_id = 'a5ks9fhw2v9s1';
SQL_TEXT
select * from dual
This will show you all the tables accessed.
Or, perhaps more likely, you should be tracing the application to see what it's doing. If you enable tracing, you can get a trace file. This contains the SQL statements executed. Along with details of how long they took.
Analyzing this with tkprof or other tools enables you to find what's taking so long. And thus start finding ways to make it faster!
Read more about tracing at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof