Databases Reference
In-Depth Information
How to do it...
Let's start by opening a SQL*Plus session and executing the following commands:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Query a dynamic performance view to know the kind of sorts executed from
instance start-up:
SELECT * FROM v$sysstat WHERE name like '%sorts%';
3.
Read the memory required for a sort operation in a query:
SET AUTOT TRACE EXP
SELECT prod_id, cust_id, time_id FROM sales ORDER BY time_id;
SET AUTOT OFF
4. Connect as
SYSDBA
and reset the dynamic performance views by restarting
the database instance:
CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP OPEN
5. Launch the script prepared before (prefix the filename with the folder in which
the script is located):
@2602_05_TestSort.sql
6.
Show optimal, one-pass, and multi-pass sorts:
SELECT
(low_optimal_size / 1024) as Low_KB,
((high_optimal_size + 1) / 1024) as High_KB,
(optimal_executions * 100 / total_executions)
as Pct_Optimal,
(onepass_executions * 100 / total_executions)
as Pct_OnePass,
(multipasses_executions * 100 / total_executions)
as Pct_MultiPasses
FROM v$sql_workarea_histogram
WHERE total_executions <> 0
order by 1;
