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: Global Temporary Table Scalability Problem

Re: Global Temporary Table Scalability Problem

From: william milbratz <milbratz_at_hotmail.com>
Date: 6 Feb 2004 15:33:41 -0800
Message-ID: <cee3515e.0402061533.670b2c45@posting.google.com> 





Hi all, 



Thanks greatly for your responses regarding this slow temporary table
performance.



My DB version, btw, is 8.1.7 (forgot to include this info).



Here's some follow up to your suggestions/questions. TKProf output
follows


at the end.



>One workaround, since you have a fairly static

>size, is to use the dbms_stats package to create

>stats on the GTT definition.




On your suggestinot I tried this, but dbms_stats.gather_table_stats
gave me an error:


  ora-20000 Gatheringing statistics for a temporary table is not
supported.
 


>what about a hint of some sort?



We already use the /*+ ordered */ hint to help identify the primary
table..Maybe another hint would get it to go at the indexes on the
temporary table more efficiently.



>would you be able to use SELECT FROM TABLE(CAST...)  syntax or a

pipelined


>function instead of the temp table (depending on version)?




I had in the past used some fancy sql to cast a comma delimited list
to a user-defined type ,i.e. like this:


 where   expert_item_enum IN


     SELECT * FROM



 THE


          ( SELECT cast( Pkg_Util.In_List
           (question_area_enum_list_in)
            AS codetableType )
            FROM dual ) x)





I hadn't gotten to that in this circumstance as I had assumed this was
really slow (it just looks too complex). Also I had problems getting
my cursor declaration to compile.



(i.e. here's an example of such code. If you have insights on the
performance of this, I'd welcome them 


http://groups.google.com/groups?q=oracle+cast+codetabletype&hl=en&lr=&ie=UTF-8&selm=94hk34%24lgm%241%40nnrp1.deja.com&rnum=1
)



TKPROF:


Below is  the tkprof output for the two queries. I've snipped the
actual queries as they're quite long.



Some info:


1) "tmp_principal_tbl" is the global temporary table. It has primary
key index SYS_C00158740


2) "zmp_principal_tbl" (query 2) is a permanent version of that table
(cloned). It has primary key SYS_C00158739



Some observations:


The explain plans are identical to a point. Here's where they differ:



slow (with global temp table)

   8149     FILTER
   8149      NESTED LOOPS
   8149       TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                  'ACCESS_CONTROL_ENTRY'
   8149       INDEX (UNIQUE SCAN) OF 'SYS_C00158740' (UNIQUE)
   8149      NESTED LOOPS
   8149       TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                  'ACCESS_CONTROL_ENTRY'
      0       INDEX (UNIQUE SCAN) OF 'SYS_C00158740' (UNIQUE)
   8149      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'ACCESS_CONTROL_ENTRY'
   8149       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                  (UNIQUE)

fast (with permanent table):
   8149     FILTER
   8149      NESTED LOOPS
   8149       INDEX   GOAL: ANALYZED (FULL SCAN) OF 'SYS_C00158739'
                  (UNIQUE)
   8149       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'ACCESS_CONTROL_ENTRY'
   8149        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                   (UNIQUE)
   8149      NESTED LOOPS
  57043       INDEX   GOAL: ANALYZED (FULL SCAN) OF 'SYS_C00158739'
                  (UNIQUE)
  48894       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'ACCESS_CONTROL_ENTRY'
  57043        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                   (UNIQUE)
   8149      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'ACCESS_CONTROL_ENTRY'
   8149       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                  (UNIQUE)

Here's the actual TKPROF output.
                  




Trace file: ora_2181.trc


Sort options: fchela 



count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for
update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************


<long running query: with temporary table >


call     count       cpu    elapsed       disk      query    current  
     rows




------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.01       0.01          0          0          0  
        0
Execute      2      0.06       0.06          0          0          0  
        0
Fetch       25    464.69     468.47    2514453    4047978      65209  
       25




------- ------  -------- ---------- ---------- ---------- ---------- 



total       28    464.76     468.54    2514453    4047978      65209  
       25





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 299  (WUPE34B)   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
     25  SORT ORDER BY




   8149   FILTER



   8150    NESTED LOOPS


   8150     NESTED LOOPS
   8265      NESTED LOOPS
   8266       TABLE ACCESS FULL POST
  16529       TABLE ACCESS BY INDEX ROWID ELEMENT
  16529        INDEX RANGE SCAN (object id 258369)
  16413      TABLE ACCESS BY INDEX ROWID THREAD
  16528       INDEX UNIQUE SCAN (object id 258333)
  16298     TABLE ACCESS BY INDEX ROWID MSG_BOARD
  16298      INDEX UNIQUE SCAN (object id 258248)
   8149    FILTER
   8149     NESTED LOOPS
   8149      TABLE ACCESS FULL ACCESS_CONTROL_ENTRY
   8149      INDEX UNIQUE SCAN (object id 259565)
   8149     NESTED LOOPS
   8149      TABLE ACCESS FULL ACCESS_CONTROL_ENTRY
      0      INDEX UNIQUE SCAN (object id 259565)
   8149     TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
   8149      INDEX UNIQUE SCAN (object id 258146)
   8149    TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
   8149     INDEX UNIQUE SCAN (object id 258146)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     25   SORT (ORDER BY)
   8149    FILTER
   8150     NESTED LOOPS
   8150      NESTED LOOPS
   8265       NESTED LOOPS
   8266        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'POST'
  16529        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'ELEMENT'
  16529         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'ELEM_ELEMENT_TY_9_IDX' (NON-UNIQUE)
  16413       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'THREAD'
  16528        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'THRE_PK'
                   (UNIQUE)
  16298      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'MSG_BOARD'
  16298       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'MSBO_PK'
                  (UNIQUE)
   8149     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'ACCESS_CONTROL_ENTRY'
   8149      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'


(UNIQUE)



   8149     FILTER
   8149      NESTED LOOPS
   8149       TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                  'ACCESS_CONTROL_ENTRY'
   8149       INDEX (UNIQUE SCAN) OF 'SYS_C00158740' (UNIQUE)
   8149      NESTED LOOPS
   8149       TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                  'ACCESS_CONTROL_ENTRY'
      0       INDEX (UNIQUE SCAN) OF 'SYS_C00158740' (UNIQUE)
   8149      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'ACCESS_CONTROL_ENTRY'
   8149       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                  (UNIQUE)

********************************************************************************<fast


query with permanent table /



call     count       cpu    elapsed       disk      query    current  
     rows




------- ------  -------- ---------- ---------- ---------- ---------- 



Parse        1      0.01       0.01          0          0          0  
        0
Execute      2      0.06       0.07          0          0          0  
        0
Fetch       25      5.36       5.35      22729     387912         17  
       25




------- ------  -------- ---------- ---------- ---------- ---------- 



total       28      5.43       5.43      22729     387912         17  
       25





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 299  (WUPE34B)   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
     25  SORT ORDER BY




   8149   FILTER



   8150    NESTED LOOPS


   8150     NESTED LOOPS
   8265      NESTED LOOPS
   8266       TABLE ACCESS FULL POST
  16529       TABLE ACCESS BY INDEX ROWID ELEMENT
  16529        INDEX RANGE SCAN (object id 258369)
  16413      TABLE ACCESS BY INDEX ROWID THREAD
  16528       INDEX UNIQUE SCAN (object id 258333)
  16298     TABLE ACCESS BY INDEX ROWID MSG_BOARD
  16298      INDEX UNIQUE SCAN (object id 258248)
   8149    FILTER
   8149     NESTED LOOPS
   8149      INDEX FULL SCAN (object id 259563)
   8149      TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
   8149       INDEX UNIQUE SCAN (object id 258146)
   8149     NESTED LOOPS
  57043      INDEX FULL SCAN (object id 259563)
  48894      TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
  57043       INDEX UNIQUE SCAN (object id 258146)
   8149     TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
   8149      INDEX UNIQUE SCAN (object id 258146)
   8149    TABLE ACCESS BY INDEX ROWID ACCESS_CONTROL_ENTRY
   8149     INDEX UNIQUE SCAN (object id 258146)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     25   SORT (ORDER BY)
   8149    FILTER
   8150     NESTED LOOPS
   8150      NESTED LOOPS
   8265       NESTED LOOPS
   8266        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'POST'
  16529        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                   'ELEMENT'
  16529         INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                    'ELEM_ELEMENT_TY_9_IDX' (NON-UNIQUE)
  16413       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'THREAD'
  16528        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'THRE_PK'
                   (UNIQUE)
  16298      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'MSG_BOARD'
  16298       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'MSBO_PK'
                  (UNIQUE)
   8149     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'ACCESS_CONTROL_ENTRY'
   8149      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'


(UNIQUE)



   8149     FILTER
   8149      NESTED LOOPS
   8149       INDEX   GOAL: ANALYZED (FULL SCAN) OF 'SYS_C00158739'
                  (UNIQUE)
   8149       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'ACCESS_CONTROL_ENTRY'
   8149        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                   (UNIQUE)
   8149      NESTED LOOPS
  57043       INDEX   GOAL: ANALYZED (FULL SCAN) OF 'SYS_C00158739'
                  (UNIQUE)
  48894       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'ACCESS_CONTROL_ENTRY'
  57043        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                   (UNIQUE)
   8149      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'ACCESS_CONTROL_ENTRY'
   8149       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'ACCE_PK'
                  (UNIQUE)

********************************************************************************


Received on Fri Feb 06 2004 - 17:33:41 CST












Original text of this message













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