Re: different fields in v$sqlarea

ryan,
i may be wrong in making some of these observations, but i would anyway.
> Is buffer_gets the same thing as 'consistent gets' in autotrace outputs? Its
> how many latch gets oracle takes on the buffer cache?

buffer gets can be both consistent gets and db block gets. There are basically 2 types of latches protecting the access of Buffer Cache, they are cache buffers lru chain latches & cache buffers chains latches.
Every access to a buffer (or buffer header), even a lookup on the cache buffer hash chain needs to take the correspoding cache buffers chains latch. This latch is taken at several points in oracle code (I think you can see all those paths using this query - select "WHERE" from v$latch_misses where parent_name like 'cache buffers chains') This latch is taken more times than the consistent gets and db block gets combined.
cache buffers lru chain latches is taken to manipulate buffers (or buffer headers) on the LRU chain. Normally the number os times these latches are acquired is almost equal to (or a bit more than) the Physical Reads.

> What is meant by 'shared memory used by a cursor'? What is considered
> shared?
> I'm assuming that a parent cursor is the initial parse of the cursor. A
> child cursor is created when a soft parse is done and new values are 'bound'
> to the cursor. Am I correct?

These 2 questions are related. The parse creates a parent cursor ( or loosely called that) which contains only the SQL_TEXT and some other info which is shared among all users. So if 2 users USERA and USERB have created table x and gave a query like this 'select * from x where a like 'AB%', there is only one parent cursor which contains this text 'select * from x where a like 'AB%'
But each user is referring to his own object X so each will have his only context area (which you have called as a child cursor) which stores the context of the users like the object referenced (even non-existence of objects also !) and other information (like execution plan etc) which varies on execution. The parent is allocated in parse phase and the child during execute phase for each user. In the beginning of execute the child is allocated and for anything but selects it is freed at the end of execute. The total memory used by the parent and child is the Shared Memory shown in V$SQLAREA. There are actually 6 different sub heaps for each KGL object. If you want to see the memory used by each, you can query kglobhs0, kglobhs1, kglobhs2, kglobhs3, kglobhs4, kglobhs5, kglobhs6 from x$kglcursor where kglnaobj = <your query>.
regards
Srivenu Received on Thu May 27 2004 - 02:59:48 CDT