Skip to Main Content
  • Questions
  • Get the details of *historical* queries/sessions/sql_id generating huge redo for last one week.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Kundan.

Asked: August 23, 2017 - 12:51 pm UTC

Last updated: June 06, 2022 - 1:32 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Dear Experts,

To my recent past query from the below link was very helpful.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536412000346656749

Can you please help me on how to get the details of *historical* queries/sessions/sql_id generating huge redo for last one week... how we can get this information.



Regards,



and Chris said...

Assuming you have the diagnostics and tuning packs and have configured AWR, you can find similar information in the dba_hist* tables.

For example to get the rows processed and executions in a snap interval, use:

select s.begin_interval_time, s.end_interval_time, 
       ss.sql_id, st.sql_text, 
       ss.executions_delta, ss.rows_processed_delta 
from   dba_hist_sqlstat ss
join   dba_hist_sqltext st
on     ss.sql_id = st.sql_id
join   dba_hist_snapshot s
on     ss.snap_id = s.snap_id
and    ss.instance_number = s.instance_number
order  by s.begin_interval_time, ss.rows_processed_delta desc

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Learning more about DBA_HIST_SQLSTAT

Lyall Barbour, June 02, 2022 - 12:56 pm UTC

To dive into the answer a bit, for DBA_HIST_SQLSTAT, what does the Oracle Docs mean when it says "since it was brought into the library cache"? Can i see when the last load into the library cache was? maybe even in DBA_HIST_SQLSTAT?

I'm trying to get a before/after picture for a change the application team is doing, and my Googling about this subject brought me here with a very useful query, that i'm trying to learn more about.
Chris Saxon
June 06, 2022 - 1:32 pm UTC

The library cache is where Oracle Database stores executable SQL & PL/SQL code; i.e. the actual instructions the server needs to run the statement.

So since it was brought into the library cache => from the time it was parsed and these instructions were created.

V$SQLAREA returns the FIRST_LOAD_TIME & LAST_LOAD_TIME to show when it was first/last put in the library cache.

You might find these architecture diagrams handy for seeing how the database fits together (go to Database Server -> Database Instance -> System Global Area -> Shared Pool to see the library cache).

https://docs.oracle.com/en/database/oracle/oracle-database/21/dbiad/index.html

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.