--SQL WHICH USE BIND LITERALS 
SELECT SUBSTR(SQL_TEXT,1,40) "SQL",
COUNT(*) ,
SUM(EXECUTIONS) "TOTEXECS" 
FROM V$SQLAREA
WHERE EXECUTIONS < 5
GROUP BY SUBSTR(SQL_TEXT,1,40)
HAVING COUNT(*) > 30
ORDER BY 2

--CHECKING HASH CHAIN LENGTHS:
SELECT HASH_VALUE, COUNT(*)
 FROM V$SQLAREA
 GROUP BY HASH_VALUE
 HAVING COUNT(*) > 5


---WHICH SQLS USE LARGE SHARED POOL

SELECT SUBSTR(SQL_TEXT,1,20) "STMT", COUNT(*),
 SUM(SHARABLE_MEM) "MEM",
 SUM(USERS_OPENING) "OPEN",
 SUM(EXECUTIONS) "EXEC"
 FROM V$SQL
 GROUP BY SUBSTR(SQL_TEXT,1,20)
 HAVING SUM(SHARABLE_MEM) > 1426063 –%10 OF SHARED POOL SIZE
 ;

 --- MONITOR SHARED POOL SIZING OPERATIONS
SELECT TO_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI') END, OPER_TYPE, INITIAL_SIZE,
 TARGET_SIZE, FINAL_SIZE FROM V$SGA_RESIZE_OPS
 WHERE COMPONENT='SHARED POOL
 ORDER BY END;

 ---CURRENT SIZE OF THE SHARED POOL
SELECT BYTES FROM V$SGAINFO WHERE NAME='SHARED POOL SIZE';


---SUGGESTED MINIMUM SHARED POOL SIZE;
SELECT CR_SHARED_POOL_SIZE,
SUM_OBJ_SIZE, SUM_SQL_SIZE,
SUM_USER_SIZE,
(SUM_OBJ_SIZE + SUM_SQL_SIZE+SUM_USER_SIZE)* 1.3 MIN_SHARED_POOL
FROM (SELECT SUM(SHARABLE_MEM) SUM_OBJ_SIZE
FROM V$DB_OBJECT_CACHE WHERE TYPE<> 'CURSOR'),
(SELECT SUM(SHARABLE_MEM) SUM_SQL_SIZE FROM V$SQLAREA),
(SELECT SUM(250*USERS_OPENING) SUM_USER_SIZE FROM V$SQLAREA),
(SELECT TO_NUMBER(B.KSPPSTVL) CR_SHARED_POOL_SIZE
 FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C
 WHERE A.INDX = B.INDX AND A.INDX = C.INDX
 AND A.KSPPINM ='__SHARED_POOL_SIZE' );


---FREE MEMORY I SHARED POOL

SELECT * FROM V$SGASTAT
 WHERE NAME = 'FREE MEMORY'
 AND POOL = 'SHARED POOL';

---TREND INFORMATION ON MEMORY USAGE IN THE SGA
 SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
 TO_CHAR( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'K' "AVG SIZE"
 FROM X$KSMSP GROUP BY KSMCHCLS;


 ----FREE MEMORY CHUNKS 
 SELECT KSMCHIDX "SUBPOOL", 'SGA HEAP('||KSMCHIDX||',0)'SGA_HEAP,KSMCHCOM CHUNKCOMMENT,
DECODE(ROUND(KSMCHSIZ/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6K',6,'6-7K',7,'7-8K',8,
'8-9K', 9,'9-10K','> 10K') "SIZE",
COUNT(*),KSMCHCLS STATUS, SUM(KSMCHSIZ) BYTES
FROM X$KSMSP
WHERE KSMCHCOM = 'FREE MEMORY'
GROUP BY KSMCHIDX, KSMCHCLS,
'SGA HEAP('||KSMCHIDX||',0)',KSMCHCOM, KSMCHCLS,DECODE(ROUND(KSMCHSIZ/1000),0,'0-1K',
 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6K',6,
 '6-7K',7,'7-8K',8,'8-9K', 9,'9-10K','> 10K');


---LIBRARY CACHE STATS 
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
 FROM V$LIBRARYCACHE
 ORDER BY NAMESPACE;

 --LIBRARY CACHE HIT RATIO
SELECT SUM(PINHITS)/SUM(PINS) FROM V$LIBRARYCACHE;

--DICTIONARY CACHE STATS
SELECT PARAMETER
 , SUM(GETS)
 , SUM(GETMISSES)
 , 100*SUM(GETS - GETMISSES) / SUM(GETS) PCT_SUCC_GETS
 , SUM(MODIFICATIONS) UPDATES
 FROM V$ROWCACHE
 WHERE GETS > 0
 GROUP BY PARAMETER;

 --DICTIONARY CACHE HIT RATIO;
 SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;


 ---LIBRARY CACHE PINNING 

CREATE TABLE LRU_TMP AS SELECT * FROM X$KSMLRU;

INSERT INTO LRU_TMP SELECT * FROM X$KSMLRU;


SELECT USERNAME, KSMLRCOM, KSMLRHON, KSMLRNUM, KSMLRSIZ, SQL_TEXT
FROM V$SQLAREA A, LRU_TMP K, V$SESSION S
WHERE KSMLRSIZ > 3000
AND A.ADDRESS=S.SQL_ADDRESS AND A.HASH_VALUE = S.SQL_HASH_VALUE
AND SADDR=KSMLRSES;


---PINNING CANDIDATES 
COL STORED_OBJECT FORMAT A40;
COL SQ_EXECUTIONS FORMAT 999,999;
SELECT /*+ ORDERED USE_HASH(D) USE_HASH(C) */
O.KGLNAOWN||'.'||O.KGLNAOBJ STORED_OBJECT,
SUM(C.KGLHDEXC) SQL_EXECUTIONS
FROM SYS.X$KGLOB O, SYS.X$KGLRD D, SYS.X$KGLCURSOR C
WHERE
O.INST_ID = USERENV('INSTANCE') AND
D.INST_ID = USERENV('INSTANCE') AND
C.INST_ID = USERENV('INSTANCE') AND
O.KGLOBTYP IN (7, 8, 9, 11, 12) AND
D.KGLHDCDR = O.KGLHDADR AND
C.KGLHDPAR = D.KGLRDHDL
GROUP BY O.KGLNAOWN, O.KGLNAOBJ
HAVING SUM(C.KGLHDEXC) > 0
ORDER BY 2 DESC;


----RESERVED AREA SIZE CHECK
 COL FREE_SPACE FOR 999,999,999,999 HEAD "TOTAL FREE"
 COL AVG_FREE_SIZE FOR 999,999,999,999 HEAD "AVERAGE|CHUNK SIZE
 COL FREE_COUNT FOR 999,999,999,999 HEAD "COUNT"
 COL REQUEST_MISSES FOR 999,999,999,999 HEAD "REQUEST|MISSES
 COL REQUEST_FAILURES FOR 999,999,999,999 HEAD "REQUEST|FAILURES"
 COL MAX_FREE_SIZE FOR 999,999,999,999 HEAD "LARGEST CHUNK"
 SELECT FREE_SPACE, AVG_FREE_SIZE, FREE_COUNT, MAX_FREE_SIZE, REQUEST_MISSES, REQUEST_FAILURES
 FROM V$SHARED_POOL_RESERVED;


---RESERVED AREA SIZE PERCENTAGE 
 SELECT A.KSPPINM "PARAMETER",
 B.KSPPSTVL "SESSION VALUE",
 C.KSPPSTVL "INSTANCE VALUE"
 FROM SYS.X$KSPPI A, SYS.X$KSPPCV B, SYS.X$KSPPSV C
 WHERE A.INDX = B.INDX AND A.INDX = C.INDX
 AND A.KSPPINM = '_SHARED_POOL_RESERVED_PCT';