「诊断脚本」
mos上提供了一个用于分析和解决ORA-4031错误的脚本，使用srdc_db_ora4031sp.sql诊断ORA-4031的内存问题，脚本内容如下：
REM srdc_db_ora4031sp.sql - Collect information for ORA-4031 analysis on shared pool
define SRDCNAME='DB_ORA4031SP'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off VERIFY off TRIMSPOOL on HEADING off
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on
REM
spool &SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===
REM
SET PAGESIZE 9999
SET LINESIZE 256
SET TRIMOUT ON
SET TRIMSPOOL ON
COL 'Total Shared Pool Usage' FORMAT 99999999999999999999999
COL bytes FORMAT 999999999999999
COL current_size FORMAT 999999999999999
COL name FORMAT A40
COL value FORMAT A20
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SET MARKUP HTML ON PREFORMAT ON
/* Database identification */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Database identification:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT name, platform_id, database_role FROM v$database;
SELECT * FROM v$version WHERE banner LIKE 'Oracle Database%';
/* Current instance parameter values */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Current instance parameter values:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT n.ksppinm name, v.KSPPSTVL value
FROM x$ksppi n, x$ksppsv v
WHERE n.indx = v.indx
AND (n.ksppinm LIKE '%shared_pool%' OR n.ksppinm IN ('_kghdsidx_count', '_ksmg_granule_size', '_memory_imm_mode_without_autosga'))
ORDER BY 1;
/* Current memory settings */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Current instance parameter values:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT component, current_size FROM v$sga_dynamic_components;
/* Memory resizing operations */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Memory resizing operations:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT start_time, end_time, component, oper_type, oper_mode, initial_size, target_size, final_size, status
FROM v$sga_resize_ops
ORDER BY 1, 2;
/* Historical memory resizing operations */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Historical memory resizing operations:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT start_time, end_time, component, oper_type, oper_mode, initial_size, target_size, final_size, status
FROM dba_hist_memory_resize_ops
ORDER BY 1, 2;
/* Shared pool 4031 information */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool 4031 information:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT request_failures, last_failure_size FROM v$shared_pool_reserved;
/* Shared pool reserved 4031 information */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool reserved 4031 information:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT requests, request_misses, free_space, avg_free_size, free_count, max_free_size FROM v$shared_pool_reserved;
/* Shared pool memory allocations by size */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Shared pool memory allocations by size:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT name, bytes FROM v$sgastat WHERE pool = 'shared pool' AND (bytes > 999999 OR name = 'free memory') ORDER BY bytes DESC;
/* Total shared pool usage */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Total shared pool usage:' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT SUM(bytes) "Total Shared Pool Usage" FROM v$sgastat WHERE pool = 'shared pool' AND name != 'free memory';
/* Cursor sharability problems */
/* This version is for >= 10g; for <= 9i substitute ss.kglhdpar for ss.address!!!! */
SET HEADING OFF
SELECT '**************************************************************************************************************' FROM dual
UNION ALL
SELECT 'Cursor sharability problems (this version is for >= 10g; for <= 9i substitute ss.kglhdpar for ss.address!!!!):' FROM dual
UNION ALL
SELECT '**************************************************************************************************************' FROM dual;
SET HEADING ON
SELECT sa.sql_text,sa.version_count,ss.*
FROM v$sqlarea sa,v$sql_shared_cursor ss
WHERE sa.address=ss.address AND sa.version_count > 50
ORDER BY sa.version_count ;
SPOOL OFF
EXIT
输出样例
+----------------------------------------------------+
| Diagnostic-Name: DB_ORA4031SP
| Timestamp: 2014-07-22 12:12:19 +03:00
| Machine: ...
| Version: 11.2.0.3.0
| DBName: V1120
| Instance: v1120
+----------------------------------------------------+
**************************************************************************************************************
Database identification:
**************************************************************************************************************
NAME PLATFORM_ID DATABASE_ROLE
---------------------------------------- ----------- ----------------
V1120 12 PRIMARY
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
**************************************************************************************************************
Current instance parameter values:
**************************************************************************************************************
NAME VALUE
---------------------------------------- --------------------
__shared_pool_size 452984832
_dm_max_shared_pool_pct 1
_enable_shared_pool_durations TRUE
_io_shared_pool_size 4194304
_kghdsidx_count 1
_ksmg_granule_size 16777216
_memory_imm_mode_without_autosga TRUE
_shared_pool_max_size 0
_shared_pool_minsize_on FALSE
_shared_pool_reserved_min_alloc 4400
_shared_pool_reserved_pct 5
shared_pool_reserved_size 22649241
shared_pool_size 0
**************************************************************************************************************
Current instance parameter values:
**************************************************************************************************************
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ----------------
shared pool 452984832
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 1090519040
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 352321536
ASM Buffer Cache 0
**************************************************************************************************************
Memory resizing operations:
**************************************************************************************************************
START_TIME END_TIME COMPONENT OPER_TYPE OPER_MODE INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
-------------------- -------------------- ---------------------------------------------------------------- ------------- --------- ------------ ----------- ---------- ---------
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 shared pool STATIC 0 452984832 452984832 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 large pool STATIC 0 16777216 16777216 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 java pool STATIC 0 16777216 16777216 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 streams pool STATIC 0 33554432 33554432 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT buffer cache STATIC 0 1090519040 1090519040 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 ASM Buffer Cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 RECYCLE buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT 2K buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT 4K buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT 8K buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT 16K buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 DEFAULT 32K buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:27 KEEP buffer cache STATIC 0 0 0 COMPLETE
22-JUL-2014 11:40:27 22-JUL-2014 11:40:28 DEFAULT buffer cache INITIALIZING 1090519040 1090519040 1090519040 COMPLETE
**************************************************************************************************************
Historical memory resizing operations:
**************************************************************************************************************
**************************************************************************************************************
Shared pool 4031 information:
**************************************************************************************************************
REQUEST_FAILURES LAST_FAILURE_SIZE
---------------- -----------------
0 0
**************************************************************************************************************
Shared pool reserved 4031 information:
**************************************************************************************************************
REQUESTS REQUEST_MISSES FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE
---------- -------------- ---------- ------------- ---------- -------------
0 0 10073952 839496 12 839496
**************************************************************************************************************
Shared pool memory allocations by size:
**************************************************************************************************************
NAME BYTES
---------------------------------------- ----------------
free memory 225008264
SQLA 33521944
KGLH0 30844560
KGLS 14472616
XDBSC 13834056
ASH buffers 8388608
row cache 7593704
write state object 7140912
PLMCD 6887760
KGLHD 6464288
db_block_hash_buckets 5836800
KGLSG 5266008
PLDIA 4839400
dbwriter coalesce buffer 4194816
KCB Table Scan Buffer 4194816
kglsim hash table bkts 4194304
KSFD SGA I/O b 4190328
file state object 3979200
event statistics per sess 3245824
KQR M PO 3067920
KQR L PO 3029048
ksunfy : SSO free list 3025600
obj stats allocation chun 2580480
dbktb: trace buffer 2457600
dirty object counts array 2097152
FileOpenBlock 2023616
private strands 1907712
KTI-UNDO 1901664
KGLDA 1364064
KSKQ SGA NODEINFO 1359872
message pool freequeue 1340568
KOKTD 1215248
SQLP 1171144
**************************************************************************************************************
Total shared pool usage:
**************************************************************************************************************
Total Shared Pool Usage
------------------------
228005680
**************************************************************************************************************
Cursor sharability problems (this version is for >= 10g; for <= 9i substitute ss.kglhdpar for ss.address!!!!):
**************************************************************************************************************
结果解读
1. 当前内存设置（Current Memory Settings）
这部分显示了数据库当前的内存配置，包括共享池的大小、已分配和未分配内存的量。这些信息可以帮助你理解当前内存配置是否足够支持数据库的操作需求。
「如何使用」：比较当前共享池大小与系统负载和错误发生前的内存使用情况，判断是否需要调整。
2. 内存调整操作（Memory Resizing Operations）
这部分列出了数据库在运行期间进行的内存调整操作，例如自动或手动调整共享池大小的实例。它提供了关于内存调整尝试的时间点和调整的大小。
「如何使用」：分析这些操作来确定内存调整是否频繁发生，以及这些调整是否成功缓解了内存压力。
3. 历史内存调整操作（Historical Memory Resizing Operations）
这部分记录了过去的内存调整历史，包括成功和失败的调整。这有助于识别长期内存管理模式和潜在的问题。
「如何使用」：利用这些历史数据评估长期内存策略的有效性，判断是否需要采取新的策略来优化内存配置。
4. 存在共享性问题的SQL游标 （Cursor sharability problems）
这部分的主要目的是识别可能存在共享性问题的SQL游标。游标共享性问题通常意味着相同的SQL语句由于某些因素（例如不同的执行计划或会话特定的设置）不能被多个用户会话共享，导致数据库性能下降和额外的内存使用。
「如何使用」：这个查询帮助数据库管理员（DBA）识别由于不能共享而多次解析的SQL语句。当一个SQL语句有多个版本存在时，每个版本都需要额外的内存，且可能导致CPU使用率增高。
如何使用这些信息
根据这些输出信息，你可以采取以下步骤进行调整和优化：
「评估共享池大小」：根据当前和历史的内存设置，决定是否需要增加共享池的大小。
「调整内存参数」：根据需要，调整相关的内存参数，以提供更多的连续空间，避免
ORA-4031
错误。「优化SQL语句」：提高SQL语句的共享度和执行效率。
「总结」
这个脚本在数据库性能调优和诊断中是一个非常实用的工具，尤其是在处理大型应用和复杂查询的环境中。通过分析这些输出，DBA可以采取相应措施减少解析次数，优化内存使用和提升查询性能。
「欢迎关注我们的公众号，获取更多技术分享与经验交流。」
文章转载自数据库驾驶舱，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
