Database Reference
In-Depth Information
On my four-CPU system, these
CREATE TABLE
statements executed with eight parallel execution servers and one
coordinator. I verified this by querying one of the dynamic performance views related to parallel execution,
V$PX_SESSION
, while these statements were running:
EODA@ORA12CR1> select sid, serial#, qcsid, qcserial#, degree from v$px_session;
SID SERIAL# QCSID QCSERIAL# DEGREE
---------- ---------- ---------- ---------- ----------
375 1275 243 1697 8
18 3145 243 1697 8
135 3405 243 1697 8
244 3065 243 1697 8
376 177 243 1697 8
5 875 243 1697 8
134 2829 243 1697 8
249 467 243 1697 8
243 1697 243
9 rows selected.
■
in creating the
UNIFORM_TEST
and
AUTOALLOCATE_TEST
tables, we simply specified “parallel” on each table,
with oracle choosing the degree of parallelism. in this case, i was the sole user of the machine (all resources available)
and oracle defaulted it to 8 based on the number of Cpus (four) and the
PARALLEL_THREADS_PER_CPU
parameter setting,
which defaults to 2.
Note
The
SID,SERIAL#
are the identifiers of the parallel execution sessions, and the
QCSID,QCSERIAL#
is the identifier
of the query coordinator of the parallel execution. So, with eight parallel execution sessions running, we would like to
see how the space was used. A quick query against
USER_SEGMENTS
gives us a good idea:
EODA@ORA12CR1> select segment_name, blocks, extents
2 from user_segments
3 where segment_name in ( 'UNIFORM_TEST', 'AUTOALLOCATE_TEST' );
SEGMENT_NAME BLOCKS EXTENTS
-------------------- ---------- ----------
AUTOALLOCATE_TEST 153696 131
UNIFORM_TEST 166400 13
Since we were using an 8KB blocksize, this shows a difference of about 100MB; looking at it from a ratio
perspective,
AUTOALLOCATE_TEST
is about 92 percent the size of
UNIFORM_TEST
as far as allocated space goes.
The actual used space results are as follows:
EODA@ORA12CR1> exec show_space('UNIFORM_TEST' );
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ...................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 12,782
