Home » RDBMS Server » Performance Tuning » Querying GTT in Parallel Mode (Oracle 10.2.0.2)
Querying GTT in Parallel Mode [message #552205] Tue, 24 April 2012 05:47 Go to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello

I am inserting data into a global temporary table and then using 'parallel' hint to query from this temporary table

I remember reading that the queries on the temp table may not run in parallel as the parallel sessions may not be able to see the data in the temporary table

However the execution plan as well as px_session, v$sql indicate that the query on the temporary table in fact run in parallel mode

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
SQL_ID  7d68g52g0mskz, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(t,4) */ * from dbo_gtt t order by id,object_id

Plan hash value: 5815349

--------------------------------------------------------------------------------------------------------------------------
---------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------
---------
|   1 |  PX COORDINATOR         |          |      1 |        |  99999 |00:00:01.46 |       3 |       |       |          |
  |
|   2 |   PX SEND QC (ORDER)    | :TQ10001 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
  |
|   3 |    SORT ORDER BY        |          |      0 |      1 |      0 |00:00:00.01 |       0 |    11M|  1311K|  424K (0)|
  |
|   4 |     PX RECEIVE          |          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
  |
|   5 |      PX SEND RANGE      | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
  |
|   6 |       PX BLOCK ITERATOR |          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
  |
|*  7 |        TABLE ACCESS FULL| DBO_GTT  |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
  |
--------------------------------------------------------------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access(:Z>=:Z AND :Z<=:Z)

select px_servers_executions from v$sql where sql_text like 'select%dbo_gtt t%';

PX_SERVERS_EXECUTIONS
---------------------
                    0
                    4

select sid, qcsid, server#, degree from v$px_session where qcsid = 228;

       SID      QCSID    SERVER#     DEGREE
---------- ---------- ---------- ----------
       247        228          1          4
       196        228          2          4
       224        228          3          4
       234        228          4          4
       226        228          1          4
       252        228          2          4
       212        228          3          4
       229        228          4          4
       228        228

9 rows selected.


unfortunately I do not have access to get trace (tkprof) report

What must have happened during the execution?

Thanks and Regards
Orapratap
Re: Querying GTT in Parallel Mode [message #552284 is a reply to message #552205] Tue, 24 April 2012 19:55 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
According to the 10.2 SQL Reference the parallel hint will be ignored.

However in the 11.2 manual, only parallel update, delete and merge are restricted, implying parallel INSERT and SELECT are allowed.

So, any chance you are doing this on an 11g database instead of 10g?

Ross Leishman
Re: Querying GTT in Parallel Mode [message #552295 is a reply to message #552284] Tue, 24 April 2012 23:48 Go to previous messageGo to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello Ross

Many thanks for your reply

I am using Oracle 10g

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


Thanks and Regards
Orapratap
Re: Querying GTT in Parallel Mode [message #552456 is a reply to message #552295] Thu, 26 April 2012 00:43 Go to previous message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
It works indeed!!


select /*+ PARALLEL( dbo_gtt, 4 ) */ count(*) from dbo_gtt;

break on dfo_number skip 1 on tq_id skip 1 on server_type

select
    dfo_number,
    tq_id,
    server_type,
    process,
    num_rows,
    bytes,
    waits,
    timeouts,
    avg_latency,
    instance
from
    v$pq_tqstat
order by
    dfo_number,
    tq_id,
    server_type desc;

FO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES      WAITS   TIMEOUTS AVG_LATENCY   INSTANCE
--------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
        1          0 Producer   P001                1         36         17          0           0          1
                                P003                1         36         11          2           0          1
                                P002                1         36         21          1           0          1
                                P000                1         36          6          0           0          1
                     Consumer   QC                  4        144         54          9           0          1



I got reference in the following links

http://www.jlcomp.demon.co.uk/testing_03.html
and
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912905298920

Thanks and Regards
Orapratap
Previous Topic: Tuneup the SQL
Next Topic: Tablespace full warning
Goto Forum:
  


Current Time: Wed Aug 27 08:44:57 CDT 2014

Total time taken to generate the page: 0.11104 seconds