difference between library cache and sql area
A reader, April 19, 2004 - 10:46 am UTC
hi
What is the difference between sql area and library cache in v$sgastat? According to the docs
The library cache includes the shared SQL areas, private SQL areas (in the case of a multiple transaction server), PL/SQL procedures and packages, and control structures such as locks and library cache handles
Is SQL area not Shared sql area?
April 19, 2004 - 12:23 pm UTC
which "sql area" do you refer.
there is a shared pool -- which holds the shared sql, cached dictionary information, plsql, and much much more.
shared sql is a component of the shared pool.
what i mean is
A reader, April 19, 2004 - 6:04 pm UTC
difference between these
shared pool library cache 84392088
shared pool sql area 811576440
I thought sql area is part of library cache?
April 19, 2004 - 7:38 pm UTC
nope. the shared pool is many times referred to as the "sql area" - that is perhaps the root cause of confusion.
ok that is confusing
A reader, April 22, 2004 - 2:53 pm UTC
ok so you mean
shared pool library cache 84392088
shared pool sql area 811576440
sql area is shared pool? then what is library cache? part of sql area? then why we see them as different rows in v$sgastat?!?!
April 23, 2004 - 9:09 am UTC
the shared pool is made up of LOTS AND LOTS of stuff.
you see *lots* of rows for the shared pool in there don't you. the shared pool is a big place, the typically largest component is the sql area, many times the sql area is called "the shared pool" conversationally.
what does these store
A reader, April 24, 2004 - 4:12 am UTC
shared pool library cache 84392088 ===> what's stored here?
shared pool sql area 811576440 ===> what is stored here?
read the doc as the other reader and now I am confused too!
April 26, 2004 - 5:04 am UTC
library cache -- table defs, constraints, indexes, etc. "data dictionary" stuff.
shared pool sql area -- your sql, its plan, its security needs, etc.
in order to parse "select * from emp"
we need to understand:
o what is emp
o what are the columns in emp
o what indexes might there be
o what sort of access do I need to get to emp
o and so on.
that is 'library cache'
then, once we understand that -- we optimize 'select * from emp' and set up the execution plan. thats the shared sql part.
Shared_pool question
ARU, September 13, 2004 - 1:55 am UTC
Hi Tom,
ps8sys > select count(*) from v$sql;
COUNT(*)
----------
6751
ps8sys > select count(*) from v$sqltext;
COUNT(*)
----------
46155
ps8sys > select count(*) from v$sqlarea;
COUNT(*)
----------
6023
What exactly do these views have and some book says v$sqlarea has first eighty chars of sql that are being
issued and the full text can be found in v$sqltext.
But the v$sqltext has 40000 extra rows than v$sqlarea. What are these rows for ? What extra info does it have?
Also what exactly is v$sql for?
Thanks,
Regards,
ARU.
September 13, 2004 - 7:58 am UTC
v$sqlarea is an aggregation of v$sql. v$sql has "repeated sql text" that represent child cursors (eg: if you have a table T and I have a table T and we both issue "select * from t", there will have to be two queries in there -- one with a plan to access your T and one with a plan to access mine. V$SQL will show both with stats for each separated -- V$SQLAREA will aggregate them up)
v$sqltext -- if you look at it -- has the sql stored in pieces -- so it might take dozens of rows in that table to hold a single sql query.
SQL Area Questions
Su Baba, July 26, 2006 - 7:04 pm UTC
Questions:
(1) Initially, only 0.46 MB of memory was allocated to "sql area".
After we executed PLSQL Block #1, 71 MB of "sql area" is allocated
(as shown in SQL #2). It appears Oracle has aged out a number
of SQLs executed in PLSQL BLock #1 since the SQL is executed and
parsed 1000 times, but there are only 616 copies of the SQL
(as shown in SQL #3).
How does Oracle determine to stop allocating memory to "sql area"
at 71 MB? Is there a parameter to determine this?
(2) Since there are 616 copies of the SQL executed in PLSQL Block #1 and
each copy consumes 165,109 bytes of memory (SQL #4), the total
memory consumption would be 97 MB (SLQ #5). But only 71 MB has been
allocated to "sql area". Is this the right way to calculate
memory consumption?
show sga
Total System Global Area 1652555776 bytes
Fixed Size 1292144 bytes
Variable Size 595593360 bytes
Database Buffers 1048576000 bytes
Redo Buffers 7094272 bytes
show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----
shared_pool_size big integer 216M
ALTER SYSTEM FLUSH SHARED_POOL;
-- ---------------------------------------------------------------
-- SQL #1
-- ---------------------------------------------------------------
SELECT name, bytes/(1024*1024) MB
FROM v$sgastat
WHERE pool = 'shared pool' AND
name IN ('free memory', 'sql area')
ORDER BY bytes DESC;
NAME MB
---------------------------------------- ----------
free memory 93.7437401
sql area .462810516
-- ---------------------------------------------------------------
-- PLSQL Block #1
--
-- Run a SQL 1000 times without using bind variables.
-- ---------------------------------------------------------------
set serveroutput on
set timing on
set time on
DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1..1000 LOOP
OPEN l_rc FOR
'SELECT /* No Bind */ object_Name
FROM all_objects
WHERE object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round( (dbms_utility.get_time-l_start)/100, 2 ) || ' seconds...');
END;
/
26.55 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.59
-- ---------------------------------------------------------------
-- SQL #2
-- ---------------------------------------------------------------
SELECT name, bytes/(1024*1024) MB
FROM v$sgastat
WHERE pool = 'shared pool' AND
name IN ('free memory', 'sql area')
ORDER BY bytes DESC;
NAME MB
---------------------------------------- ----------
sql area 71.0730934
free memory 17.3584671
-- ---------------------------------------------------------------
-- SQL #3
-- ---------------------------------------------------------------
SELECT COUNT(DISTINCT sql_id) cnt,
plan_hash_value
FROM v$sqlarea
GROUP BY plan_hash_value
HAVING COUNT(*) > 100
;
CNT PLAN_HASH_VALUE
---------- ---------------
616 37563033
-- ---------------------------------------------------------------
-- SQL #4 - shows SQL executed in PLSQL BLock #1 uses ~ 160K of
-- memory.
-- ---------------------------------------------------------------
SELECT sharable_mem + persistent_mem + runtime_mem mem_usage
FROM v$sqlarea
WHERE plan_hash_value = 37563033 AND
rownum = 1;
MEM_USAGE
----------
165109
-- ---------------------------------------------------------------
-- SQL #5 - Since there are 616 copies (SQL #3) of the SQL
-- executed in PLSQL Block #1 and each consumes 165109 bytes
-- of memory. The total memory consumed should be ~97MB.
-- ---------------------------------------------------------------
SELECT 616 * 165109/1024/1024 MB FROM dual;
MB
----------
96.995491
July 26, 2006 - 8:01 pm UTC
No, you cannot control the memory allocation in the shared pool really, it just happens.
Things are seriously version dependent as well.
shareable memory is sharable. If you and I share something, there is only one of them....
Su Baba, July 26, 2006 - 8:31 pm UTC
Even though you cannot control the memory allocation in the shared pool, the shared pool itself is constrainted by the shared_pool_size? It can never go beyond that number?
Regarding Question #2 above, in that particular test case, since there is only one user (it's a local system on my PC), and every SQL executed in PLSQL Block #1 is a "new" SQL, there actually wouldn't be any "sharable" memory, right? So the number quoted in SQL #5, 97MB, should be accomodated in the "sql area"?
July 26, 2006 - 9:49 pm UTC
everything is very version dependent. In 9i and before - the shared pool size was the least it would be (the shared pool would be larger - we added to it to hold our structures)
in 10g, the shared pool size is the shared pool size (after rounding up to the nearest granule).
Su Baba, July 26, 2006 - 8:52 pm UTC
Back to the example ("SQL Area Questions" - July 26, 2006) above,
the "same" SQL is executed 1,000 times (PLSQL BLock #1), but only 616 copies are stored in v$sqlarea (SQL #3). From this I assume 384 of them got aged out of the shared pool. Is this info stored in the system dictionary? How do I find out if my shared pool is too small? (I understand this is not a "good" example since I'm not using bind variables.)
July 26, 2006 - 9:50 pm UTC
the information is not stored there(in dictionary)
you would use the shared pool advisor - part of statspack, a v$ view, available in OEM for viewing as well.
Sharable_mem size of a short SQL text
A reader, July 28, 2007 - 2:35 pm UTC
Tom,
From this query:
select sql_text,sharable_mem,executions,version_count, cpu_time
from v$sqlarea order by sharable_mem DESC;
I wonder why it takes 106040 bytes of sharable memory with such a short statement like the following? The version_count is only 1.
(Yes, it is not using bind, and I am working with the developer on the *bind* business.)
SQL_TEXT
SHARABLE_MEM EXECUTIONS VERSION_COUNT CPU_TIME
-------------------------------------------------------
DELETE FROM T_MM_TDE031 WHERE ID ='Q879830001803080288'
106040 1 1 0
This statement is executed by user OPRUSER who has the synonym T_MM_TDE031 to TOWER.T_MM_TDE031 table. With synonyms the OPRUSER can insert/update/delete tables from different schema owners. ID is the primary key of the table.
I can't imagine why it needs 106040 bytes to parse this statement (hard-parse).
Thanks,
jc
July 28, 2007 - 6:03 pm UTC
triggers, constraints, foreign keys, indexes to maintain, whatever.
hard to say without "an example" - eg: your DDL.
Follow up on "what does these store April 24, 2004 - 4am US/Eastern "
Bipul Kumar, August 28, 2007 - 12:33 pm UTC
Hi Tom,
What does "dictionary cache" stores ?
Can you give an example of what will be stored on these 3 pools?
1. Library cache stores
2. Dictionary Cache stores
3. SQL Area stores
Thanks
bipul
September 04, 2007 - 2:40 pm UTC
they just store bits and pieces of data we use to parse and execute your sql.
dictionary stuff - table definitions, meta data about the structures
library cache stuff - plsql code, java code, stored outlines, various other bits of metadata
sql area - your compiled sql plans.