Pqo

[sgodbole]

Oracle 8.1.7.4 on Compaq Tru64, no OPS.

I am trying to run a simple select * or count(*) query in parallel and when I check v$pq_* views and v$sysstat view, it doesn't show me that the query is executed in parallel. The table has 840000 rows. I even tried hints, but those views doesn't show any information about parallel execution.

My parallel_* parameters in init.ora are as follows:

SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ------- ------
fast_start_parallel_rollback string LOW
optimizer_percent_parallel integer 0
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_broadcast_enabled boolean FALSE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 64
parallel_min_percent integer 0
parallel_min_servers integer 12
parallel_server boolean FALSE

NAME TYPE VALUE
------------------------------------ ------- ------
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0

Any ideas?

[Thomasps]

Hi

First checkout the execution plan. I did the following way.

SQL> set autotrace on explain
SQL> select count(*) from emp;

COUNT(*)
----------
12288
Execution Plan
------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'


SQL> select /*+ parallel (emp 4) */ count(*) from emp;

COUNT(*)
----------
12288


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q5000
3 2 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=41) :Q5000


2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)

3 PARALLEL_COMBINED_WITH_PARENT
oracle Doc says

Parallel execution is designed to effectively use multiple CPUs and disks to answer queries quickly. When multiple users use parallel execution at the same time, it is easy to quickly exhaust available CPU, memory, and disk resources.

Oracle provides several ways to manage resource utilization in conjunction with parallel execution environments, including:

The adaptive multiuser algorithm, which reduces the degree of parallelism as the load on the system increases. You can turn this option with the PARALLEL_ADAPTIVE_MULTI_USER parameter of the ALTER SYSTEM statement or in your initialization parameter file.

User resource limits and profiles, which allow you to set limits on the amount of various system resources available to each user as part of a user's security domain.

The Database Resource Manager, which allows you to allocate resources to different groups of users.

ie there are certain other factors controlling your query parallel execution. First start from the explain.

All the best


Thomas

Thomas

[sgodbole]

Thanks for the reply. Even I did the same thing and the explain plan show those parallel operations, however, query against v$pq_slave or v$pq_sessstat does not show any slave processes used for that query and all slaves are idle. Those busy_time_cur and busy_time_total columns in v$pq_slave show zeros.

SQL> show user
USER is "ISOGATEWAY"
SQL> select table_name, degree from user_tables where table_name like 'GTWS%';

TABLE_NAME DEGREE
------------------------------ ----------
GTWSAMPLE 4

SQL> select index_name, degree from user_indexes where table_name like 'GTWS%';

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
PK_GTWSAMPLE1 4

SQL> select count(*) from gtwsample;

COUNT(*)
----------
872545


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q115440
00

3 2 PARTITION HASH* (ALL) :Q115440
00

4 3 INDEX* (FAST FULL SCAN) OF 'PK_GTWSAMPLE1' (UNIQUE) :Q115440
(Cost=430 Card=879770) 00



2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT 0 FROM :I."GTWSAMPLE"."P

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT

SQL> select slave_name, status, busy_time_cur, busy_time_total from v$pq_slave;

SLAV STAT BUSY_TIME_CUR BUSY_TIME_TOTAL
---- ---- ------------- ---------------
P000 IDLE 0 0
P001 IDLE 0 0
P002 IDLE 0 0
P003 IDLE 0 0
P004 IDLE 0 0
P005 IDLE 0 0
P006 IDLE 0 0
P007 IDLE 0 0
P008 IDLE 0 0
P009 IDLE 0 0
P010 IDLE 0 0

SLAV STAT BUSY_TIME_CUR BUSY_TIME_TOTAL
---- ---- ------------- ---------------
P011 IDLE 0 0

12 rows selected.

[Thomasps]

Hi

Here just I am trying to show you the usage of parallel query slaves.

Initially the status of parallel query slaves are

SQL> select slave_name, status, busy_time_cur, busy_time_total from v$pq_slave;
                                                                               
no rows selected

That is initially there are no parallel query slaves active if not using. Again I run a query with parallel option with degree requested 8 but got parallel slaves defines (Max_parallel_slaves) 5. as follows. (used Catisian product to simulate a long query... Don't confuse!)

select /*+ degree(a 8) */ a.name,sum(b.salary)  
from emp a, emp b 
group by a.name;

Then the status of PQ is observed as

SQL> l
1* select slave_name, status, busy_time_cur, busy_time_total from v$pq_slave
SQL> /

SLAV STAT BUSY_TIME_CUR BUSY_TIME_TOTAL
---- ---- ------------- ---------------
P000 BUSY 2 2
P001 BUSY 2 2
P002 BUSY 2 2
P003 IDLE 0 0
P004 BUSY 2 2


Now, there are 4 query slaves are "Busy" out of 5 and 1 is "idle". Eventhough I requested for 8 slaves oracle adjusted the number of slaves to the defined one based on certian Algorithms.

After running the query the status back to as follows.

SLAV STAT BUSY_TIME_CUR BUSY_TIME_TOTAL
---- ---- ------------- ---------------
P000 IDLE 0 5
P001 IDLE 0 5
P002 IDLE 0 5
P004 IDLE 0 5

One salve was idle for a long time so it is removed by oracle. And the busy 4 slaves become idle.


Hope this solve your doubts.


Reagrds

Thomas