Database Reference
In-Depth Information
C:> set ORACLE_SID=ORCL
C:> set DBI_USER=ndebes
C:> set DBI_PASS=secret
C:> set DBI_DSN=DBI:Oracle:
C:> sql_workarea_active.pl
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
The Perl program does not display any data until one or more work areas are allocated. We
will use the script
sort_random_strings.sql
to run
SELECT
. . .
ORDER
BY
in SQL*Plus. Following
are the script's contents:
set timing on
set autotrace traceonly statistics
SELECT * FROM random_strings ORDER BY 1;
exit
The SQL*Plus command
SET
AUTOTRACE
with the options
TRACEONLY
and
STATISTICS
is very
useful in this context, since it executes the statement without printing the result set to the
screen. Furthermore it collects and displays execution statistics from
V$SESSTAT
. In a separate
window from the one running
sql_workarea_active.pl
, execute the script
sort_random_
strings.sql
with SQL*Plus, as shown here:
C:> sqlplus ndebes/secret
@sort_random_strings.sql
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
1000000 rows selected.
Elapsed: 00:00:18.73
Statistics
---------------------------------------------------
133 recursive calls
7 db block gets
18879 consistent gets
16952 physical reads
0 redo size
138667083 bytes sent via SQL*Net to client
733707 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1000000 rows processed
Surprisingly, the available memory was insufficient and the sort spilled to disk. Following
is the output from
sql_workarea_active.pl
, which shows that the session performed a one-
pass sort, since it only got a work area size of 51.2 MB:
