Oracle Query Plans?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle Query Plans?

  1. #1
    Join Date
    Apr 2001
    Posts
    142

    Question

    Hello, I would be grateful with some assistance on the following :


    In oracle, when you run a query where is the query plan stored?

    When you run the same query twice you will get better performance on the second version because;

    The actual statements are stored in the DB BUFFERS
    Sorts are in memory
    Library cache stores the DD calls thus reducing recursive calls.

    Is this all true?

    How can you manually flush the Library cache and DB Buffers?

  2. #2
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi

    -----------------------------
    In oracle, when you run a query where is the query plan stored?

    The parsed version of the query is in the shared pool
    ----------------------------

    When you run the same query twice you will get better performance on the second version because;

    The actual statements are stored in the DB BUFFERS
    No, table data is stored in the buffer cache

    Sorts are in memory
    No, sort are done in the sort area if the sort is to big for the sort area it copy the sort area to the temp tablespace.

    Library cache stores the DD calls thus reducing recursive
    calls.
    TRUE
    ---------------------------

    When you run the same query twice and the data is already flushed from the buffer cache it has to be read in to the buffer cache again.

    If the statement is flushed from the shared pool it has to be reloaded again.

    ALTER SYSTEM FLUSH SHARED_POOL;

    There is no direct command to flush the buffer cache. If you want to flush it (cann't think of any reason) you can bounce the database.

    Tycho

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