#眉標=Oracle、Shared Pool、PGA #副標=管理Oracle資料庫必要的功力(3) #大標=改善語法解析效率增進資料庫效能 #作者=文/圖 張天慧 ===<反灰>============= SELECT shared_pool_size_for_estimate “Pool Size MB”,  estd_lc_size “Est Lc MB”, estd_lc_memory_objects “Est Lc Mem. Obj.”,  estd_lc_time_saved “Est Lc Time Saved”, estd_lc_time_saved_factor “Est Lc Time Saved Fac.”, estd_lc_memory_object_hits “Est Lc Mem. Obj. Hit ” FROM V$SHARED_POOL_ADVICE; ================ ===<反灰>============= STATISTICS_LEVEL= ALL | TYPICAL | BASIC SGA_TARGET = integer [K | M | G] ================ ===<反灰>============= SQL> alter system set statistics_level=typical ; SQL> alter system set sga_target=1024M; ================ ===<反灰>============= SELECT ROUND(FREE_SPACE / 1024 / 1024, 1) "FREE SPACE MB", REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED; ================ ===<反灰>============= SELECT SUBSTR(OWNER, 1, 10) OWNER, SUBSTR(TYPE, 1, 12) TYPE, SUBSTR(NAME, 1, 20) NAME, EXECUTIONS, SHARABLE_MEM “MEMORY USED”, SUBSTR(KEPT || ' ', 1, 4) "KEEP" FROM V$DB_OBJECT_CACHE WHERE TYPE IN ('TRIGGER', 'PROCEDURE', 'PACKAGE BODY', 'PACKAGE') ORDER BY EXECUTIONS DESC; ================ ===<反灰>============= SELECT owner, name sharable_mem, kept, loads FROM V$DB_OBJECT_CACHE WHERE loads > 1 ORDER BY loads DESC; ================ ===<反灰>============= SELECT owner, name, sharable_mem, kept FROM V$DB_OBJECT_CACHE WHERE sharable_mem > 51200 AND kept = 'NO' ORDER BY sharable_mem DESC; ================ ===<反灰>============= Select namespace, gets, gethitratio*100 "GET%", pins, pinhitratio*100 "PIN%", reloads, invalidations from v$librarycache; ================ ===<反灰>============= SELECT (REQUEST_MISSES / (REQUESTS + 0.0001)) * 100 "REQUEST MISSES RATIO", (REQUEST_FAILURES / (REQUESTS + 0.0001)) * 100 "REQUEST FAILURES RATIO" FROM V$SHARED_POOL_RESERVED ; ================ ===<反灰>============= select a.value as Total,b.value as "Hard Pase", round(b.value / a.value, 2) as Ratio from v$sysstat a, v$sysstat b where a.name = 'parse count (total)' and b.name = 'parse count (hard)'; ================ ===<反灰>============= select sql_text, executions from (select sql_text, executions, rank() over(order by executions desc) exec_rank from v$sql) where exec_rank <= 10; ================ ===<反灰>============= select disk_reads, sql_text from (select sql_text,   disk_reads,   dense_rank() over(order by disk_reads desc) disk_reads_rank from v$sql) where disk_reads_rank <= 10; ================ ===<反灰>============= select buffer_gets, sql_text from (select sql_text,   buffer_gets,   dense_rank() over(order by buffer_gets desc) buffer_gets_rank from v$sql) where buffer_gets_rank <= 10; ================ ===<反灰>============= select b.username "USER NAME", a.disk_reads "DISK READ", a.executions EXECUTIONS, a.disk_reads / decode(a.executions, 0, 1, a.executions) "READ_EXEC%", a.sql_text "SQL TEXT" from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 100000 order by a.disk_reads desc; ================ ===<反灰>============= WORKAREA_SIZE_POLICY= AUTO|MANUAL SORT_AREA_SIZE = integer HASH_AREA_SIZE = integer ================ ===<反灰>============= ALTER SESSION SET WORKAREA_SIZE_POLICY= AUTO; ALTER SYSTEM SET WORKAREA_SIZE_POLICY= MANUAL; ALTER SESSION SET SORT_AREA_SIZE = 102400; ALTER SYSTEM SET SORT_AREA_SIZE = 102400; ALTER SESSION SET HASH_AREA_SIZE = 102400; ALTER SYSTEM SET HASH_AREA_SIZE = 102400; ================ ===<反灰>============= select name, to_char(decode(unit, 'bytes', value / 1024 / 1024, value),   '999,999,999.9') value, decode(unit, 'bytes', 'mbytes', unit) unit from V$PGASTAT; ================ ===<反灰>============= SELECT round(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024) "PGA Target (MB)", ESTD_PGA_CACHE_HIT_PERCENTAGE "Catch Hit Ratio", ESTD_OVERALLOC_COUNT "Over Allocate Count" FROM v$pga_target_advice; ================ ===<反灰>============= SELECT sql_text,   sum(ONEPASS_EXECUTIONS) onepass_cnt,   sum(MULTIPASSES_EXECUTIONS) mpass_cnt FROM V$SQL s, V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY sql_text HAVING sum(ONEPASS_EXECUTIONS + MULTIPASSES_EXECUTIONS) > 0; ================ ===<反灰>============= select program c1, pga_used_mem, pga_alloc_mem, pga_max_mem from v$process order by c4 desc; ================