This is a technical interview question to getting your insight of detailed skill sets as an Oracle DBA or developer. Your answer could tell what level of experience in database server and SQL query performance tuning. Your answer could be:
“In my past job I’ve configured SQL Trace and Autotrace in our Oracle production environment with both SQLPlus and Toad for Oracle tools. I used both SQL Trace and Autotrace almost daily for SQL query analysis and performance tuning. I created explain plan tables for all users who might use Autotrace in SQLPlus. For users who use Toad, I only needed to create one set of explain plan tables under Toad schema and the plan tables can be shared by multiple Toad users. To configure SQL Trace, I need to alter session to set it up. Then I turn the trace on before query execution and turn it off after the SQL run. If I run it in SQLPlus, I would need to use the database utility TKPROF to convert the trace output into readable file with formatted data. In Toad I could the GUI interface to go through the SQL trace process.”
The follow-up question could ask you what is the difference between SQL Trace and Autotrace, and how you determine which one to use first. So your answer can further explain the knowledge like:
“Autotrace provides us with explain plan and performance statistics on the actual execution of the SQL run. SQL Trace provides performance statistics on the SQL run. It must be used in conjunction with the Database utility TKPROF in order to provide meaningful information. The performance statistics for the SQL Trace our more configurable and provide more information; however, Autotrace is easier to get feedback immediately. Often, Autotrace is used first then SQL Trace when more information is needed.”
0 Comments.