April 8, 2010
I recently encountered a discussion thread (dbaforums.org/oracle/index.php?showtopic=19435) that asked about how to determine whether or not a SQL statement executed in a program uses indexes or full table scans – the database in question is an unspecified release version of Oracle 10g. One of the responders in that thread pointed to an undated article (praetoriate.com/teas_prae_util11.htm) about the AUTOTRACE functionality of SQL*Plus, while other responders suggested tracing the program’s execution and then using TKPROF.
Please read the article, keeping in mind that the question concerns some release version of Oracle 10g, and see if you are able to answer the following true or false questions. State why you believe that the question is true, or why you believe that the question is false. Any answers that attempt to utilize logical fallacies will be scored as incorrect.
1. SQL*Plus’ AUTOTRACE feature performs statement tracing.
2. Using SQL*Plus’ AUTOTRACE feature requires a PLAN_TABLE in the schema of the user using AUTOTRACE.
3. AUTOTRACE retrieves the actual execution plan, along with the associated statistics for the execution.
4. When a large number of rows will be returned by SQL*Plus, the AUTOTRACE TRACEONLY feature should be used.
5. For SQL performance issues, AUTOTRACE is the first tool of choice for investigating the SQL performance issues.
6. Performance issues that are present when SQL is executed in an application will also be present when the SQL statement is executed in SQL*Plus with AUTOTRACE enabled.
May 25, 2010
The following screen capture is completely unrelated to this blog article, but is relevant to the comments about the blog redesign. This is how the redesigned blog appears on a 16:9 netbook, with 1024 horizontal pixels of resolution:
I had to scroll the window slightly to the right to see the entire content portion of the blog, while the “floating” navigation section is hidden from view. Internet Explorer 8.0 offers a zoom feature near the bottom right of the IE window – that feature (or a similar one in other browsers) might be a solution if your monitor does not offer 1024 pixels of horizontal resolution.