Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: parallel query works on single CPU or not?

Re: parallel query works on single CPU or not?

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1997/05/13
Message-ID: <3378DEA9.5B3@teldta.com> 







L. Tseng wrote:


> 

> Hi,

> 

> We run Oracle Work Group Server 7.3 on NT4.0. The default in init.ora file

> sets 5 max parallel server. I altered a large table (not index) with parallel

> degree 3 but did not see the performance gain. So, my questions are:

> 

>   Does parallel query work on single CPU ?

>   How can you tell if a parallel query is being performed?

>   Is there any other parameters need to be tuned?

> 

> Thanks,

> 

> Leslie

> 






Yes, parallel query is a performance gain in a single cpu machine.  It’s
the old 


multitasking game, if one process is blocked on a resource, another
process can 


get some quality cpu time.  But as always, the more active processes on
the 


system, the less the gain.   



You can determine if parallel query will be performed by looking at the
plan_table 


output from the explain plan utility.  I can’t remember the details but
the 


description(s) gets better with the higher the Oracle version (7.1, 7.2, 
7.3, ?.?).  But, if you are running with the cost based optimizer just
because 


the explain plan says it’s so doesn’t mean it’s so (see next paragraph).



I like to run several queries to see what is happening at run time (see
below).  


You can look at your session in the v$ tables and see that the parallel
query 


servers you are using are associated with your session for the duration
of your 


parallel query.  The v$ tables also have statistics about each parallel
query 


server and some summarized statistics.





QUERY OUTPUT WHEN OPQ IS NOT BEING USED:



           Oracle     Pgm    Oracle  Oracle
Unix       User       Unix   Unix   Session
User       Name       Pid    Pid         ID Serial# TTY#
---------- ---------- ------ ------ ------- ------- -------
                                                 Server
Program Name                                     Type
------------------------------------------------ ---------


Current SQL Statement



oracle     OPS$ORACLE 66878  92992      244    1640 pts/1
sqlplus_at_bart (Pipe Two-Task)                    DEDICATED


select count(*) from ADJUSTMENTS





QUERY OUTPUT WHEN OPQ IS BEING USED (note how all processes are
associated 


with Pgm Unix Pid 66878):


           Oracle     Pgm    Oracle Oracle
Unix       User       Unix   Unix   Session
User       Name       Pid    Pid         ID Serial# TTY#
---------- ---------- ------ ------ ------- ------- -------
                                                 Server
Program Name                                     Type
------------------------------------------------ ---------


Current SQL Statement



oracle     OPS$ORACLE 66878  21256      154     552 pts/1
sqlplus_at_bart (Pipe Two-Task)                    DEDICATED


SELECT /*+ ROWID(A1)*/ COUNT(*) FROM "ADJUSTMENTS" A1 WHERE ROWID

BETWEEN



:1 AND :2

 

oracle     OPS$ORACLE 66878  21508      214    1074 pts/1
sqlplus_at_bart (Pipe Two-Task)                    DEDICATED


SELECT /*+ ROWID(A1)*/ COUNT(*) FROM "ADJUSTMENTS" A1 WHERE ROWID

BETWEEN



:1 AND :2

 

oracle     OPS$ORACLE 66878  92992      244    1640 pts/1
sqlplus_at_bart (Pipe Two-Task)                    DEDICATED


select /*+ FULL(A) PARALLEL(A,2) */ count(*) from ADJUSTMENTS A




SOME OF THE OPQ STATISTICS AVAILABLE:


SYSTEM STATISTICS Report For Oracle Parallel Query Servers
 

                                   Current
Statistic                            Value
------------------------------ -----------
DFO Trees                                1
Distr Msgs Recv'd                        0
Distr Msgs Sent                          0
Local Msgs Recv'd                        4
Local Msgs Sent                          2
Queries Initiated                        1
Server Sessions                          2
Servers Busy                             0
Servers Cleaned Up                       0
Servers Highwater                        2
Servers Idle                             4
Servers Shutdown                         0
Servers Started                          0


Hit enter to continue
 


SLAVE SERVERS STATISTICS Report For Oracle Parallel Query Servers
 

                        Currently    Total
              Times Cur      Busy     Busy
Name  Status   Busy Bus       Pct      Pct
----- ------ ------ --- --------- --------
P000  IDLE        1 NO       .000     .000
P001  IDLE        1 NO       .000     .000
P002  IDLE        0 NO       .000     .000
P003  IDLE        0 NO       .000     .000







REM




REM ==    AUTHOR - Brian P. Mac Lean
REM ==    DATE   - 02-JAN-97
REM ==    FILE   - tool_session.sql




REM





set verify off


set pagesize 23


set linesize 80


set pause on


set pause 'Hit enter to continue'


set feedback off


set showmode off


set echo off


PROMPT



ACCEPT USER_INPUT CHAR PROMPT 'Please enter a SID# or PID# to list:>'
PROMPT

 

col bb heading "Unix|User"             format a10
col cc heading "Oracle|User|Name"      format a10
col dd heading "Pgm|Unix|Pid"          format a6
col ee heading "Oracle|Unix|Pid"       format a6
col ef heading "Oracle|Session|ID"     format 999999
col eg heading "Serial#"               format 999999
col ff heading "TTY#"                  format a7
col gg heading "Program Name"          format a48
col hh heading "Current SQL Statement" format a80
col ii heading "Server|Type"           format a9
select unique
       vs.osuser bb,
       vs.username cc,
       vs.process dd,
       vp.spid ee,
       vs.sid ef,
       vs.serial# eg,
       vs.terminal ff,
       vs.program gg,
       vs.server ii,
       vsql.sql_text hh




  from v$sqlarea vsql, v$process vp, v$session vs
 where (vs.process = '&USER_INPUT' or vp.spid = '&USER_INPUT' or vs.sid
= '&USER_INPUT')


   and vs.paddr = vp.addr
   and vs.sql_address = vsql.address (+)
   and vs.sql_hash_value = vsql.hash_value (+);
 




break on bb nodup on cc nodup on dd nodup on ee nodup on ef nodup on eg
nodup


col gg heading "Accessing|Object"      format a28
select unique

       vs.osuser bb,
       vs.username cc,
       vs.process dd,
       vp.spid ee,
       vs.sid ef,
       vs.serial# eg,
       va.object gg




  from v$access va, v$process vp, v$session vs
 where (vs.process = '&USER_INPUT' or vp.spid = '&USER_INPUT' or vs.sid
= '&USER_INPUT')


   and vs.paddr   = vp.addr
   and vs.sid     = va.sid
order by vs.osuser, vs.username, vs.process, vp.spid, vs.sid;
 




exit;

REM ================================ END OF FILE
===============================







REM




REM ==    AUTHOR - Brian P. Mac Lean
REM ==    DATE   - 10-JAN-97
REM ==    FILE   - tool_pq.sql




REM





set verify off


set pagesize 23


set linesize 80


set pause on


set pause 'Hit enter to continue'


set feedback off


set showmode off


set echo off
 

col num              format 999      heading "Nbr"
col name             format a30      heading "Name"
col type             format 999      heading "Type"
col value            format a15      heading "Value"
col meg              format 99.99    heading "Size|Meg"
col isdefault        format a9       heading "IsDefault"


ttitle "Current Parameter Report For Oracle Parallel Query Servers"
select num, name, type, value, isdefault
from v$parameter


where name like 'parallel%'


order by num;



REM only valid if your current session has run a parallel query
REM  


REM col statistic  format a30        heading "Statistic"
REM col last_query format 99,999     heading "Last|Session|Totals"
REM col session_to format 99,999,999 heading "All|Session|Totals"


REM ttitle "SESSION STATISTICS Report For Oracle Parallel Query Servers"
REM select *


REM from v$pq_sesstat


REM order by statistic;
 

col statistic format a30        heading "Statistic"
col value     format 99,999,999 heading "Current|Value"


ttitle "SYSTEM STATISTICS Report For Oracle Parallel Query Servers"
select *


from v$pq_sysstat


order by statistic;
 

col name             format a5      heading "Name"
col status           format a6      heading "Status"
col sessions         format 99999   heading "Times|Busy"
col cur_busy         format a3      heading "Currently|Busy?"
col cur_busy_pct     format 999.999 heading "Currently|Busy|Pct"
col tot_busy_pct     format 999.999 heading "Total|Busy|Pct"


ttitle "SLAVE SERVERS STATISTICS Report For Oracle Parallel Query
Servers"


select slave_name AS name,

       status AS status,
       sessions AS sessions,
       decode(busy_time_cur, '0', 'NO', 'YES') AS cur_busy,
       (busy_time_cur / (busy_time_cur + idle_time_cur + 1)) * 100 AS
cur_busy_pct,
       (busy_time_total / (busy_time_total + idle_time_total + 1)) * 100


AS tot_busy_pct


from v$pq_slave


order by slave_name;
 


exit;

REM ================================ END OF FILE
===============================


                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo







Who needs friends when you can sit alone in your room and drink?
Received on Tue May 13 1997 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US