Re: how does oracle store cursors in memory?

From: Scott <oraracdba_at_yahoo.com>
Date: Fri, 13 Feb 2004 20:57:25 -0800 (PST)
Message-ID: <20040214045725.9728.qmail@web41505.mail.yahoo.com>

Ryan, I am attaching a better buffer cache diagram (I hope). Except under certain full table scan conditions Datablocks are not stored in the PGA. Everything is done via the shared_pool and SGA through pointers. If you select address from v$sqlarea this gives you the handle address of the object in the library cache. The theory is that if your application executes a sql statement it should generate a hash and that address may or may not already exists in the shared pool if it does not then your process should generate an execution plan and load the dictionary objects into the “dc” area of the shared pool and your PGA will now have a pointer to that address. If the address already exists then your pga will establish a pointer to the already existing SQL (execution plans and other necessities). Your PGA will also keep certain status information about the status of the cursor as well as pointers to sort segment handles. The bottom line is that the PGA is usually a series of pointers to the SGA. Without source code you have to make some assumptions but you can figure a lot this out using the oradebug command and the GNU debugger (gdb) to dump the processes and use gdb to dig into the structures of the PGA.

Have fun. I remember when I like to do this kinda stuff. Now I am just happy I can get Oracle to install, run and not have any bugs.

Scott

• Ryan <ryan.gaffuri_at_cox.net> wrote:
  > I know that data blocks are stored in the sga in
  > linked lists. When hash maps are created the data
  > blocks are stored in the pga as part of the hash map
  > in arrays/linked lists.
  > How are cursors stored? Now the data for the cursors
  > is going to be in the SGA. Is a cursor just a
  > pointer to the memory location of the data in the
  > SGA? Now I know a 'REF' cursor is basically a named
  > pointer. Am I correct? What data structure does
  > oracle use to store cursors?
  >
  

  ---

  
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  >
  

  ---

  
  > To unsubscribe send email to:
  > oracle-l-request_at_freelists.org
  > put 'unsubscribe' in the subject line.
  > --
  > Archives are at
  > http://www.freelists.org/archives/oracle-l/
  > FAQ is at
  > http://www.freelists.org/help/fom-serve/cache/1.html
  > -----------------------------------------------------------------
  

  ---

Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html

• HTML Attachment decoded to text by Ecartis --
• File: Buffer Cache Overview.htm
• Desc: Buffer Cache Overview.htm

 Buffer Cache Overview: ~~~~~~~~~~~~~~~~~~~~~~ This example applies to

Oracle7 and Oracle8, although in reality there may be multiple LRU chains. InOracle8i the LRU concept is replaced by a touch-count algorithm but the idea is the same. LRU Chain: Most Recent Doubly linked list Least Recent

+------+------+------+ - - - - +------+------+------+------+------+ | | | |
|| PTR | | | | +------+------+------+ - - - -
+------+------+------+------+------+ Newly used buffers | We search for are

placed at this end. | LRU just FREE buffers | points from this end LRUW Chain(Dirty List): | to of the LRU. +------+------+ - - +------+ | buffer Dirty buffers | | | | | | headers. are moved to +------+------+ - - +------+ | LRUW if there (Cleared by DBWR) | is room. | | Hashing is | based on DBA | modulo the | number of buckets. | A LATCH protects | each hash chain | |

,-------. --------------------------------/----------------------+ Double |
Hash | <------------------------------/---------------------+ | linked |

Bucket| ,--------. ,---------V----. ,--------. | | hash | 1 | ----->| Buffer |--->| Buffer |---->| Buffer |--+ | chain. `-------' <-----| Header |<---| Header |<----| Header |<---+ | 40 | | 2 | | 999 | | | | | | | |--------| | |

| | |Usr|Wait| | | | | `--------' `--------------' `--------' ,-------. | |
|: |SO. |--------------' | | | | | | `-------' | | Buffer Handle | | state
object | | | | (actual DB blocks) | | ,-------------------. ,-------. | | |
1| | Hash | | | | | | Bucket| | | +-------------------+ | 2 | -->... |
`---------->| 2 | `-------' <--... | | | | +-------------------+ | : : |
+-------------------+ : `-------------------------->| 40 Data Block | : | |
+-------------------+ : : +-------------------+ ,-------. | 999 | | Hash | |
| | Bucket| +-------------------+ | N | | 1000 | `-------' | | There are a
PRIME +-------------------+ number of Hash buckets. 

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 13 2004 - 22:57:25 CST