Search This Blog

Total Pageviews

Saturday, 12 November 2022

gv$sql with objects



sql_id and objects 



efine sql_id='7jycxu86n60qh'   -------

SET HEADING ON
SET PAGESIZE 1000 LINESIZE 500
COL text FOR A50 wrap 
COL ctext FOR A50 wrap 
col PARSING_SCHEMA_NAME for a20
col LAST_LOAD_TIME  for a27
col FIRST_LOAD_TIME for a27
col OWNER for a20
col OBJECT_NAME for a20
select vs.sql_id,vs.last_load_time, ao.OWNER, parsing_schema_name, first_load_time , ao.OBJECT_NAME, vs.program_line#, executions exe, vs.sqltype, vs.sql_id, vs.rows_processed rows_processed, concurrency_wait_time, elapsed_time/1000000 elapsed_secs, elapsed_time/1000000/(case when executions = 0 then 1 else executions end) elap_per_exec_secs, vs.sql_text ctext
from gv$sql vs, all_objects ao
where vs.PROGRAM_ID = ao.OBJECT_ID and parsing_schema_name not in ('SYS','SYSTEM') 
--and parsing_schema_name in ('')
--and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','MDSYS')
and vs.sql_id='&sql_id'
order by vs.last_load_time desc , vs.parsing_schema_name, vs.first_load_time desc , program_id, vs.program_line#;

No comments:

Oracle DBA

anuj blog Archive