Usage of TOAD in Oracle PeopleSoft Production Environment
July 11, 2011 1 Comment
As you the reader might be aware, TOAD is database development and administration tool that helps us to monitor the Database.
Here, we are going to discuss about the usage of TOAD in a PeopleSoft Production Database. Most PeoplSoft geeks knows that all the process which we run in the PoepleSoft Pure Internet Architecture will be scheduled in as a Process/JOB in the Process Scheduler, which we can look by navigating into the following navigation
PeopleTools > Process Scheduler > Process Monitor
Note: All the process which we run in the scheduler will have a session in the Database and it will be in Active mode when we run it and it will go to Inactive mode as soon as it ends.
We can view all the Active/InActive sessions of the database by using any one of the Database Administration Tool. Here for instance we are taking TOAD as our Tool to identify, fix the myth behind this process.
So, Basically by navigating into the following location in TOAD, we can able to open all the Database Sessions.
DBA > Session Browser
The Status tab will display all the Active/ InActive Sessions which has/had sessions in the Database.
Here we have to choose the suspect and see what could be the reason behind the over run. We could identify it by viewing the following option in the session browser.
IO – To identify whether the process is performing any I/O related activities, When you don’t see a change in their Block reads, physical Reads etc., you can assume that the process has an issue in the IO.
Waits – Will tell you wait cycle for each IO operation that we perform, by using the wait time we can determine the status of the process.
Current Statement – This is used to fetch the current SQL that is executed. by examining the Statement we should able to trace the reason for the overrun.
Note: If you find “SQL BODY UNAVILABLE” which means that you don’t have access to view the SQL.
Locks – will tell you if the process has locks init, in such case, releasing the lock will take the process to the closure.
Long Ops – It is used to differentiate the Time Remaining and Elapsed Time.
Statistics – Will reveal the list of CPU time cycles taken for each operation performed within process.
By analysing the above option, an Admin must able to reveal the myth behind the process overrun.