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

Thread: Global Tempoary tables

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Global Tempoary tables

    Hi. We have a new OLTP database that uses Permanent and Global Temporary tables. We are using the cost based optimizer. We are experiencing performance problems. Do we need to compute stats on temporary tables? Are temporary tables handled differently by the optimizer? Please help. Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    have you actually worked out what the performance problems are, or are you just guessing

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Hi. Sorry. I should have been more specific.
    We are using the DBMS package to compute stats on the entire schema. We found that the application has been experiencing performance problems. As soon as we delete statistics on the global temporary tables, the performance improves. This is the general feedback that we are geting our users.
    We will turn on tracing to get a clearer understanding on what is going on. In the meantime, I was just wondering whether Global Tempoary tables needed stats.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You can set statistics on them, but because their contents are likely to change so often it is unlikely that analyzing them on some regular schedule is going to help
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What's your oracle release?

    f it's 9i/10g, Set the optimizer_dynamic_sampling parameter value to 4 or 5.
    Trace the sql and examine the access paths.

    Another option is:
    Create a table of the structure of GTT, load data, gather statistics for the new table. Then export the statistics into a table. There is one column (i think c1) that stores the table name, update this column on the stat table with original GTT name. Then import the stats back into dictionary.

    This fixed perf issue.

    Tamil

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