ora-4031报错解决

根据MOS Information Gathering Script For ORA-4031 Analysis On Shared Pool (Doc ID 1909791.1)

可以执行脚本@srdc_db_ora4031sp.sql,得到内存的grow,shrink时间轴

例如下面

**
Historical memory resizing operations:
**

START_TIME END_TIME COMPONENT OPER_TYPE OPER_MODE INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
—————————— —————————— ———————————————————————————————— ——————- ————- —————— —————- ————— ————-
12-APR-2021 09:13:45 12-APR-2021 09:13:45 DEFAULT buffer cache GROW DEFERRED 1459617792 1509949440 1509949440 COMPLETE
12-APR-2021 09:13:45 12-APR-2021 09:13:45 large pool SHRINK DEFERRED 83886080 33554432 33554432 COMPLETE
13-APR-2021 06:59:27 13-APR-2021 06:59:29 large pool SHRINK DEFERRED 83886080 33554432 33554432 COMPLETE
13-APR-2021 06:59:27 13-APR-2021 06:59:29 DEFAULT buffer cache GROW DEFERRED 1459617792 1509949440 1509949440 COMPLETE
13-APR-2021 10:45:24 13-APR-2021 10:45:24 large pool SHRINK DEFERRED 83886080 33554432 33554432 COMPLETE
13-APR-2021 10:45:24 13-APR-2021 10:45:24 DEFAULT buffer cache GROW DEFERRED 1459617792 1509949440 1509949440 COMPLETE
14-APR-2021 05:28:51 14-APR-2021 05:28:51 large pool SHRINK DEFERRED 83886080 33554432 33554432 COMPLETE
14-APR-2021 05:28:51 14-APR-2021 05:28:51 DEFAULT buffer cache GROW DEFERRED 1459617792 1509949440 1509949440 COMPLETE

OPER_MODE是DEFERRED,说明当时内存严重不足,无法立即GROW/shrink, 需要加大SGA
相对应的是IMMEDIATE,说明free内存充足, 可以马上扩展收缩,只需要加大相对应组件的最小值