Thread: Pqo
-
Pqo
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?
HTH.
Shripad Godbole
OCP DBA (8,8i,9i)
"Let's document it and call it a feature."
-
parallel query execution
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
-
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.
Last edited by sgodbole; 03-08-2003 at 11:24 PM.
HTH.
Shripad Godbole
OCP DBA (8,8i,9i)
"Let's document it and call it a feature."
-
PQS
Hi
Here just I am trying to show you the usage of parallel query slaves.
Initially the status of parallel query slaves are
Code:
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!)
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|