Using SQL Snapshot

To capture a long-running SQL statement, perform the following steps:

1. From the INSTANCE menu, choose SQL > Snapshot and select the sorting criteria you want to use:
   • Statement CPU sorts by the SQL statement.
   • Session CPU sorts by the session number.
   • Session Block Gets sorts by the block gets of a session.
   • Session Physical I/O sorts by the physical input and output of a session.
2. Move the slider to select the number of users whose SQL statements you want to see, and click Accept. The maximum number is 20, and the default is 10. The current active users or sessions are listed in descending order, and the list includes the user name, the PID, the SID, and the value of the sorting criteria.
3. To view the SQL statement, select the user or session whose SQL statement you want to see, and click Accept.
    The SQL Text dialog box displays the SQL statement for the user or session.
4. Choose any of the following actions:
   • run Oracle's Explain Plan utility
   • quit SQL Snapshot
   • cancel and view another SQL statement

5. To run Oracle's Explain Plan utility, select Run Explain Plan and then click Accept. You must enter the user's name and the password for that username.
    The user name for the session is displayed in the SQL Text dialog box.

   Warning

   Note

   You may need to enter the SYS user name and the password.

   The console window displays the actions for the Explain Plan utility. For more information about the Explain Plan utility, see your Oracle documentation.

6. To view another SQL statement, click Cancel to return to the SQL Text dialog box, or quit SQL Snapshot by selecting Quit Snapshot.

   Warning

   Note

   To retrieve CPU data, the TIMED_STATISTICS parameter in the init.ora file must be set to TIMED_STATISTICS=TRUE. If the parameter is not set to true, SQL Snapshot does not retrieve any CPU data. To change the parameter, you must shut down the database, change the parameter in the init.ora file, and restart the database.