Monitoring Sort Activity - Exercise

Check for high disk sort Percentage

Objective: Check the SGA for disk sorts.

Exercise Scoring

This exercise is worth a total of 10 points. To receive full credit, you will need to correctly modify a SQL query based on the instructions below. Once you have completed your answer, you will submit it your answer.

Background and Overview You need an alert report for all databases where more than 20% of sorts are done on disk. Your DBA manager has asked you to write an SQL query that will report the number of memory sorts, the number of disk sorts, and the PERCENTAGE of disk sorts for your database. You will need to modify the SQL query from the previous lesson to compute the percentage of disk sorts.

Instructions

SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE 'sort%';
Re-write the query above to produce output like this:
SQL> @sort_pct
MEMORY_SORTS DISK_SORTS    PERCENT
------------          ----------         ----------
193518              27                 .01395219

Hints: You will need to specify V$SYSSTAT twice in the query, once for names like sorts (memory)%' and again for names like `sorts (disk)%'. If you have access to Oracle you can test your query in SQL*Plus.
Submitting your exercise Type your query in the text box below. Click the Submit button to submit the exercise.