This discussion is archived
3 Replies Latest reply: Feb 11, 2013 11:23 AM by Sven W. RSS

Performance of WITH query.

Chiwatel Newbie
Currently Being Moderated
Hi All,

I have a WITH query (inline view) and when I run it against my pre-production database (millions of rows) it hangs and never comes back. However, when I run it on my development database (just a few rows), it comes back fairly quickly. So clearly, the number of rows is the issue here.

The question is what should I check on the Oracle database side to find out what is going on ? I have checked the PGA and the temporary segments (which seem to be fine) but I am failing to see what other components I should check.

What does the WITH command impact on the database level ?

Many thanks for your help.
  • 1. Re: Performance of WITH query.
    BluShadow Guru Moderator
    Currently Being Moderated
    Chiwatel wrote:
    Hi All,

    I have a WITH query (inline view)
    Actually a WITH query is called "Subquery Factoring"

    http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_10002.htm#i2077142
    and when I run it against my pre-production database (millions of rows) it hangs and never comes back. However, when I run it on my development database (just a few rows), it comes back fairly quickly. So clearly, the number of rows is the issue here.
    Yep, it would seem that way.
    The question is what should I check on the Oracle database side to find out what is going on ? I have checked the PGA and the temporary segments (which seem to be fine) but I am failing to see what other components I should check.
    Read the two threads linked to in this FAQ: {message:id=9360003} and post relevant details.
    What does the WITH command impact on the database level ?
    It can improve queries where the subquery is used more than once in the query, as well as simply making some queries easier to read for the developer.
    The optimizer will decide whether it's best to materialize the results of the query as an internal temporary table to reference in the main query, or whether to embed the query as a sub query, depending on the statistics, cardinality, selectivity etc. as any other query is optimized.
  • 2. Re: Performance of WITH query.
    636309 Newbie
    Currently Being Moderated
    Chiwatel wrote:
    I have checked the PGA and the temporary segments (which seem to be fine)
    Hi, For my own understanding, could you please explain exactly what was checked in the PGA and temporary segments?
  • 3. Re: Performance of WITH query.
    Sven W. Guru
    Currently Being Moderated
    Chiwatel wrote:
    I have a WITH query (inline view) and when I run it against my pre-production database (millions of rows) it hangs and never comes back. However, when I run it on my development database (just a few rows), it comes back fairly quickly. So clearly, the number of rows is the issue here.

    The question is what should I check on the Oracle database side to find out what is going on ? I have checked the PGA and the temporary segments (which seem to be fine) but I am failing to see what other components I should check.

    What does the WITH command impact on the database level ?
    Whenever you run a SQL statement PGA is almost never used (or very very little of it). One exception would be if you have user defined functions in your select. Any SQL statement in general uses the SGA.
    The execution plan in put into the shared area. The data is loaded from the disc to the buffer cache and so on. If it is fairly large it might also use temp space, meaning some operations like large sort operations can not be fully done in memory and therefore will be moved to costly hard drive space (temp tablespace).

    PGA (private global area) is mostly used for things like package state.

    For more information see this: http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm

    Edited by: Sven W. on Feb 11, 2013 8:23 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points