RelationalDBDesign RelationalDBDesign


Sorting Operations   «Prev  Next»

Monitoring Sort Activity - Exercise

Monitoring sort activity


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/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.