Pqo
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Pqo

  1. #1
    Join Date
    Jan 2003
    Posts
    78

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

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jan 2003
    Posts
    78
    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."

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width