Session profile using ASH

Worked on the scripts in this zip today: http://www.bobbydurrettdba.com/uploads/sessionprofiledbahist.zip

I wanted to see a session profile from the DBA_HIST_ACTIVE_SESS_HISTORY view.  My idea was to include the time the session was inactive as well as the time it was waiting on an event or using the CPU.

On both these scripts you have to manually update the date range.

ashsessionlist.sql – this just gives you a list of the sessions for a given date range.  Sorts them from most to least active.  Also shows the total timeframe so you can tell how much inactive time there was compared with active.

ashsessionprofile.sql – takes a given instance, SID, and sequence number and produces a nice session profile.  The key to any session profile is to include some comparison of the waits and cpu with the overall elapsed time.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply