Shared SQL Areas and Private SQL Areas:
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Shared SQL Areas and Private SQL Areas:

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Topic: SGA=> Shared Pool => Library Cache


    Shared SQL Areas and Private SQL Areas:

    Oracle represents each SQL statement it executes with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statementís private SQL area.

    [See: Oracle documet part number a67781.pdf - Oracle 8i Concepts, Page : 186)

    Suppose same SQL statement using by 5 users (Cache hit). So by the last statement "However, each user must have a separate copy of the statementís private SQL area." , there must be 5 SQL statements in 5 different private SQl Area and one in Shared Library Cache... So total Six SQl statements in the memory..? is it true...? Please somebody explain...

    Thanks
    Thomas P S
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hi friends...

    Any comments on my doubt..

    Thanks

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The "private" area is where the bind variables will get stored for each user. The SQL is in the shared pool once and there are 5 different copies of the different bind varaibles.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    same book, just after what you copied, go and look for the definitions of Shared SQL Area and Private SQL Area, it's different : the shared one contains the parse tree, the execution plan for the query. private area just contains data relative to the user and to *his* execution of the query.
    when 5 users do the same query, execution plan and so on are in the shared area, and each user has a private area with data concerning his own usage of the query

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thanks Pipo and Marist

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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