Database Reference
In-Depth Information
see each has its own SID; in fact, each is a separate Oracle session and shows up as such in
V$SESSION
during the
execution of our parallel query:
EODA@ORA12CR1> select sid, username, program
2 from v$session
3 where sid in ( select sid
4 from v$px_session
5 where qcsid = 258 )
6 /
SID USERNAME PROGRAM
---------- -------- ---------------------------
12 EODA oracle@heera07 (P007)
23 EODA oracle@heera07 (P004)
26 EODA oracle@heera07 (P000)
94 EODA oracle@heera07 (P001)
102 EODA oracle@heera07 (P005)
169 EODA oracle@heera07 (P006)
177 EODA oracle@heera07 (P002)
258 EODA sqlplus@heera07 (TNS V1-V3)
267 EODA oracle@heera07 (P003)
9 rows selected.
■
if a parallel execution is not occurring in your system, do not expect to see the parallel execution servers in
V$SESSION
. they will be in
V$PROCESS
, but will not have a session established unless they are being used. the parallel
execution servers will be connected to the database, but will not have a session established. See Chapter 5 for details on
the difference between a session and a connection.
Note
In a nutshell, that is how parallel query—and, in fact, parallel execution in general—works. It entails a series
of parallel execution servers working in tandem to produce subresults that are fed either to other parallel execution
servers for further processing or to the coordinator for the parallel query.
In this particular example, as depicted, we had
BIG_TABLE
spread across four separate devices in a single
tablespace (a tablespace with four data files). When implementing parallel execution, it is generally optimal to have
your data spread over as many physical devices as possible. You can achieve this in a number of ways:
•
Using RAID striping across disks
•
Using ASM, with its built-in striping
BIG_TABLE
over many disks
•
Using partitioning to physically segregate
•
Using multiple data files in a single tablespace, thus allowing Oracle to allocate extents for the
BIG_TABLE
segment in many files
