Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: When are records deleted from V$SQLAREA?

    Greeting All.
    I had identified several long SQL statements in V$SQLAREA earlier, but later on they disappeared from the view. While I still see some records with older LAST_ACTIVE_TIME there, but some newer ones disappeared. Does anyone know how this view (or the shared SQL Area) is maintenanced? What critera is used to remove a SQL statement?

    Appreciate your advice.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As Far As I Know, the SQL statements are not removed per se, but older & unused SQL statements can be overwritten by newer SQL statements.
    It has little to do with the age of the statement but how frequent or recent a SQL statement is used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    I agree with you. But LAST_ACTIVE_TIME represents the last time the SQL was executeD, right? If so, why were the recently executed SQLs overwritten, while some other SQLs not recently executed are still there?
    Last edited by DBA-Jr; 02-15-08 at 17:04.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Because it is not time based per se. An LRU (least recently used) algorithm is used, and each piece of code receives a 'touch count'. When the SQL is executed it is inserted at the least recently used point of the Most Recently used end of the least recently used list (fun innit? ) i.e. the middle.
    When the touch count reaches a value of 2 the SQL moves to the top of the LRU (the MRU of the MRU of the LRU). As other SQL is moved to the top, your SQL moves down the list.
    Take 2 pieces of SQL : SQLA and SQLB.
    SQLA Executed Touchcount0. Time 9.00 position Mid
    SQLB Executed Touchcount0 Time 9.01 position mid
    SQLA Executed Touchcount1 Time 9.02 position mid
    SQLA Executed Touchcount2 Time 9.03 position Top
    SQLB Executed Touchcount1 Time 9.04 position mid

    So now, even tho SQLB was executed after SQLA, it is lower down the LRU list. And would therefore age out before SQLA

    HTH
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •