Monitoring Sort Activity - Exercise

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.

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.

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

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.