Re: Parallel query option

Database is Enterprise Edition 8.0.5, running on NT4. Machine has 4 processors. Data is on a RAID-5 set, utilizing 4 physical disk units.

In init<sid>.ora:
  parallel_max_servers = 16
  parallel_min_servers = 8

SQL> select * from v$pq_sysstat;

STATISTIC                          VALUE
------------------------------ ---------
Servers Busy                           0
Servers Idle                           8
Servers Highwater                      8
Server Sessions                       24
Servers Started                        0
Servers Shutdown                       0
Servers Cleaned Up                     0
Queries Initiated                      4
DML Initiated                          0
DFO Trees                              4
Sessions Active                        0

In the above, I understand that "idle" and "highwater" means we've never user more than the minimum parallel processes, and are using none at the time I queried it. Not sure what "server sessions" or the other values mean here.

SQL> select * from v$pq_slave;

SLAV STAT SESSIONS
---- ---- ---------

P000 IDLE         4
P001 IDLE         4
P002 IDLE         4
P003 IDLE         4
P004 IDLE         2
P005 IDLE         2
P006 IDLE         2
P007 IDLE         2

I see that this is showing the status of each of the parallel processes, currently at the minimum count. Not sure what "sessions" means here. All the other numbers (not shown) are zero.

SQL> select * from v$option;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -------
Partitioning                                                     TRUE
Objects                                                          FALSE
Parallel Server                                                  FALSE
Advanced replication                                             TRUE
Bit-mapped indexes                                               TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Point-in-time tablespace recovery                                TRUE

Looks like we have "Parallel execution" turned on. Was there a specific switch for this somewhere, or is it derived from parallel_max_servers and parallel_min_servers?

In article <8f9v09$2pjc$1_at_buty.wanadoo.nl>,   "John van der Steen" <steenjoh_at_iquip.nl> wrote:
> Hi Ed,
>
> first what database version do you work with?
> Do you have multiple processors? If you don't you probably won't
 benefit
> from parallel query
> Is the data spread on different disks? If it isn't the disk spindle
 can't
> read two places on disk at the same time so you won't benefit here
 either.
>
> There are some views ( V$PQ_SYSSTAT , V$PQ_SESSTAT, V$PQ_SLAVE) where
 you
> can notice activity.
> Check V$OPTION if parallel query is true.
>
> Make sure you are doing full table-scans because else the cost based
> optimizer would probably not choose to do a parallel query. This
 doesnt mean
> you must force full table-scans but if it isn't doning a full table
 scan it
> won't use PQO.
>
> Greetings,
>
> John
>
> Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> schreef in berichtnieuws
> 8f9pic$rhm$1_at_nnrp1.deja.com...
> > One of our database is used for a data warehouse and the queries
 tend
> > to be big, complex multi-table joins - 10 tables is not uncommon.
> > Suspecting that parallel query option might help performance, I am
 just
> > starting to look at that, so this may be the question of someone
> > looking to buy a clue . ..
> >
> > I ran an Oracle Expert (from the OEM Tuning Pack) analysis on the
 DB.
> > In recommended reducing parallel_min_servers to zero. The
 justification
> > was "This instance is currently configured to utilize parallel
 query,
> > and is creating query servers at instance startup. THE DEGREE OF
> > PARALLELISM IN THE DICTIONARY IS NOT CONFIGURED TO ALLOW PARALLEL
 QUERY
> > PROCESSING. (emphasis mine) At the same time, there is no evidence
 that
> > hints are being provided through the SQL statements."
> >
> > OK, so I queried dba_tables to see what the values of DEGREE, and
> > INSTANCES was for the application tables. I was surprised to see
 that
> > several (though by no means all) of the tables had DEGREE values of
> > greater than one, while some had vaules of 'default."
> >
> > So I guess I have a couple of questions. First, what do I look at to
> > see if we are actually getting parallel processing, and to what
 degree?
> > Second, if we're NOT getting it, what more do I need to do to enable
> > it?
> >
> >
> > --
> > Ed Stevens
> > (Opinions are not necessarily those of my employer)
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

--
Ed Stevens
(Opinions are not necessarily those of my employer)


Sent via Deja.com http://www.deja.com/
Before you buy.