Re: Parallel query option

Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> schreef in berichtnieuws 8fbmk5$udl$1_at_nnrp1.deja.com...
> 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
>

It seems that no parallel server is doing anything.

> 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
>

All slaves are Idle this is no good sign

> 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?
>
>

Parallel query is true so it is installed It isn't a switch but you install it together with your database server.

Be sure you do full table scans. Do a select * from tablename; after doing this check v$pq_sesstat, you must find some other figures. If not ???

Greetings ,

John

> 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.
Received on Wed May 10 2000 - 00:00:00 CDT