Description of the illustration sqltuning_adv_2

This is a screenshot of the Performance Finding Details: Top SQL Statements page. At the top of the page is the following information:

Finding: SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. (Finding History button).
Impact (Active Sessions): 4.6
Percentage of Finding's Impact (%): 47.6 (also displayed as a bar chart)
Period Start Time: June 24, 2009 9:35:50 AM PDT
Period Duration (minutes): 1.5
Filtered: No (Filters button)

Below this information is the Recommendations section. The first item in this section is a button labeled Schedule SQL Tuning Advisor. Next is a table of recommendations. At the top of the table are 4 hyperlinks: Select All, Select None, Show All Details, Hide All Details. The table has the following columns: Select, Details, Category, and Benefit (%). The data for the last column is displayed as a horizontal bar graph in each row, followed by the percentage value. In the Details column, each row has a Show icon, that if clicked, "opens" the row to reveal recommendation details, including action buttons such as Run Advisor Now and Filters. For the "opened" rows, the Details column contains a Hide icon.

This table shown in this screenshot has 4 rows, each a SQL Tuning recommendation. The last 3 rows in the table are not "opened". The first row, which is "opened", contains the following information:

Not selected, Hide icon, SQL Tuning, 20.6 (Benefit %)
Action: Run SQL Tuning Advisor on the SELECT statement with SQL_ID "axabnfyfp4r3p". Additionally, investigate this statement for possible performance improvements. You can supplement the information given here with an ASH report for this SQL_ID. (Run Advisor NoW button) (Filters button)
- SQL Text SELECT E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME FROM HR.EMPLOYEES E, HR.DEPAR... (hyperlinked)
- SQL ID axabnfyfp4r3p (hypterlinked)
Action: Use bigger fetch arrays while fetching results from the SELECT statement with SQL_ID "axabnfyfp4r3p".
- SQL Text SELECT E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME FROM HR.EMPLOYEES E, HR.DEPAR... (hyperlinked)
- SQL ID axabnfyfp4r3p (hypterlinked)
Rationale, "The SQL spent 30% of its database time on CPU, I/O and Cluster waits. This part of database time may be improved by the SQL Tuning Advisor. Look at data given below and an ASH report for further performance improvements."
Rationale, "Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution."
Rationale, "SQL statement with SQL_ID 'axabnfyfp4r3p' was executed 35402 times and had an average elapsed time of 0.0062 seconds."
Rationale, "Waiting for event 'resmgr:cou quantum' in wait class 'Scheduler' accounted for 53% of the database time spent in processing the SQL statement with SQL_ID 'axabnfyfp4r3p'."
Rationale, "Top level calls to execute the PL/SQL statement with SQL_ID '357p6hzg9gat' are responsible for 100% of the database time spent on the SELECT statement with SQL_ID 'axabnfyfp4r3p'."
- SQL Text DECLARE n number; v_employees_last_name ... (hyperlinked)
- SQL ID 357p6hzg9gat (hypterlinked)

End of description.