It's a typical DBA scenario....you land up at your desk and are bombarded by users complaining that the system experienced a hiccup of 2 minutes or so in the morning when no one was in office. Transactions were dropped etc....
So what do you do as a DBA....fire up the AWR and ASH reports and try to link the data to the problem cause. Sometimes you just wish you were there to observe what the database was experiencing, in real-time, than doing a post apocalypse analysis.
Lets say the user gives you a perfect time when the system went bonkers....
Step 1 - Find the SNAP ID's corresponding to this time frame.....
So what do you do as a DBA....fire up the AWR and ASH reports and try to link the data to the problem cause. Sometimes you just wish you were there to observe what the database was experiencing, in real-time, than doing a post apocalypse analysis.
Well here is a query that will take you back in time and show you events as if you were viewing the system in real time. The result is that you are able to address problems much quicker and find the guilty sql ids in a snap.
Lets say the user gives you a perfect time when the system went bonkers....
Step 1 - Find the SNAP ID's corresponding to this time frame.....
SELECT SNAP_ID,BEGIN_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 2;
Step 2 - Find the event, sql_ids, sessions IDs as they were during the issue time...
col event for a40
set lines 1000 pages 1000
SELECT SESSION_ID,SQL_ID,EVENT,SAMPLE_TIME FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID BETWEEN 31565 AND 31566 ORDER BY SAMPLE_TIME
HERE IS A SAMPLE OUTPUT FROM A REAL WORLD SCENARIO......
35 log file sync 03-NOV-15 11.05.10.019 AM
39 log file sync 03-NOV-15 11.05.10.019 AM
152 log file sync 03-NOV-15 11.05.10.019 AM
236 log file sync 03-NOV-15 11.05.10.019 AM
238 log file sync 03-NOV-15 11.05.10.019 AM
265 log file sync 03-NOV-15 11.05.10.019 AM
379 log file sync 03-NOV-15 11.05.10.019 AM
383 log file sync 03-NOV-15 11.05.10.019 AM
384 log file sync 03-NOV-15 11.05.10.019 AM
528 log file sync 03-NOV-15 11.05.10.019 AM
534 log file sync 03-NOV-15 11.05.10.019 AM
556 log file sync 03-NOV-15 11.05.10.019 AM
5 a232czr2w2ryy direct path read 03-NOV-15 11.05.20.039 AM
842 a232czr2w2ryy direct path read 03-NOV-15 11.05.20.039 AM
527 log file sync 03-NOV-15 11.05.20.039 AM
474 log file sync 03-NOV-15 11.05.20.039 AM
470 log file sync 03-NOV-15 11.05.20.039 AM
417 log file sync 03-NOV-15 11.05.20.039 AM
414 log file sync 03-NOV-15 11.05.20.039 AM
382 log file sync 03-NOV-15 11.05.20.039 AM
326 log file sync 03-NOV-15 11.05.20.039 AM
297 log file sync 03-NOV-15 11.05.20.039 AM
272 log file sync 03-NOV-15 11.05.20.039 AM
265 log file sync 03-NOV-15 11.05.20.039 AM
238 log file sync 03-NOV-15 11.05.20.039 AM
209 log file sync 03-NOV-15 11.05.20.039 AM
121 log file sync 03-NOV-15 11.05.20.039 AM
558 03-NOV-15 11.05.20.039 AM
320 db file async I/O submit 03-NOV-15 11.05.20.039 AM
291 db file async I/O submit 03-NOV-15 11.05.20.039 AM
707 log file sync 03-NOV-15 11.05.20.039 AM
701 log file sync 03-NOV-15 11.05.20.039 AM
532 log file sync 03-NOV-15 11.05.20.039 AM
670 4ztz048yfq32s direct path read 03-NOV-15 11.05.20.039 AM
6 log file sync 03-NOV-15 11.05.20.039 AM
SO NOW YOU KNOW WHAT WAS HAPPENING EXACTLY AT 11.05 IN YOUR DATABASE WITHOUT LOOKING AT ANY AWR OR ASH REPORTS.....
NOTE -- YOU CAN ALSO INCLUDE P1,P2,P3 VALUES FROM DBA_HIST_ACTIVE_SESS_HISTORY TO FURTHER DRILL DOWN INTO THE EVENTS....
Now Enjoy your AWR and ASH free troubleshooting journey........
No comments:
Post a Comment