# 查看当前共享池配置
SQL> SHOW PARAMETER shared_pool_size;

NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 0

SQL> SHOW PARAMETER shared_pool_reserved_size;

NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_reserved_size big integer 0

# 调整共享池大小
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

System altered.

# 配置共享池保留区
SQL> ALTER SYSTEM SET shared_pool_reserved_size = 200M SCOPE=BOTH;

System altered.

# 查看共享池统计信息
SQL> SELECT
pool,
name,
bytes/1024/1024 AS mb
FROM v$sgastat
WHERE pool = ‘shared pool’
AND name IN (‘free memory’, ‘library cache’, ‘sql area’);

POOL NAME MB
———— —————————— —
shared pool free memory 500
shared pool library cache 800
shared pool sql area 600

# 查看当前游标共享配置
SQL> SHOW PARAMETER cursor_sharing;

NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string EXACT

# 调整游标共享配置
SQL> ALTER SYSTEM SET cursor_sharing = ‘FORCE’ SCOPE=BOTH;

System altered.

# 查看游标统计信息
SQL> SELECT
name,
value
FROM v$sysstat
WHERE name IN (‘parse count (total)’, ‘parse count (hard)’, ‘cursor authentications’);

NAME VALUE
—————————— ———-
parse count (total) 100000
parse count (hard) 5000
cursor authentications 1000

# 计算软解析率
SQL> SELECT
(1 – (hard_parse / total_parse)) * 100 AS soft_parse_ratio
FROM (
SELECT
value AS hard_parse
FROM v$sysstat
WHERE name = ‘parse count (hard)’
),
(
SELECT
value AS total_parse
FROM v$sysstat
WHERE name = ‘parse count (total)’
);

SOFT_PARSE_RATIO
—————-
95

# 查看库缓存统计信息
SQL> SELECT
namespace,
gets,
gethits,
pins,
pinhits,
reloads,
invalidations
FROM v$librarycache;

NAMESPACE GETS GETHITS PINS PINHITS RELOADS INVALIDATIONS
————— ——- ———- ——- ——— ——— ————-
SQL AREA 100000 95000 200000 190000 500 100
TABLE/PROCEDURE 50000 48000 100000 98000 200 50
BODY 20000 19500 40000 39500 50 20
TRIGGER 10000 9800 20000 19800 20 10
INDEX 50000 49000 100000 99500 100 30
CLUSTER 5000 4900 10000 9900 5 2
OBJECT 2000 1950 4000 3950 5 1
PIPE 100 98 200 198 0 0

# 计算库缓存命中率
SQL> SELECT
namespace,
ROUND(gethits/gets*100, 2) AS gethit_ratio,
ROUND(pinhits/pins*100, 2) AS pinhit_ratio
FROM v$librarycache
WHERE gets > 0
AND pins > 0;

NAMESPACE GETHIT_RATIO PINHIT_RATIO
————— ———— ————
SQL AREA 95.00 95.00
TABLE/PROCEDURE 96.00 98.00
BODY 97.50 98.75
TRIGGER 98.00 99.00
INDEX 98.00 99.50
CLUSTER 98.00 99.00
OBJECT 97.50 98.75
PIPE 98.00 99.00

# 查看库缓存锁等待情况
SQL> SELECT
event,
wait_class,
total_waits,
time_waited,
average_wait
FROM v$system_event
WHERE event LIKE ‘library cache%’;

EVENT WAIT_CLASS TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
—————————— ——————– ———– ———– ————
library cache lock Concurrency 1000 5000 5.0
library cache pin Concurrency 500 2500 5.0

# 查看会话级别的库缓存锁等待
SQL> SELECT
s.sid,
s.username,
sw.event,
sw.wait_class,
sw.seconds_in_wait
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = ‘ACTIVE’
AND sw.event LIKE ‘library cache%’;

SID USERNAME EVENT WAIT_CLASS SECONDS_IN_WAIT
— ————- —————————— ——————– —————
123 SCOTT library cache lock Concurrency 10
124 HR library cache pin Concurrency 5

# 查看库缓存锁的详细信息
SQL> SELECT
p1 AS address,
p2 AS hash_value,
p3 AS namespace
FROM v$session_wait
WHERE event = ‘library cache lock’;

ADDRESS HASH_VALUE NAMESPACE
—————- ———- ———-
0000000081234567 1234567890 SQL AREA
0000000087654321 9876543210 TABLE/PROCEDURE

# 分析库缓存锁的对象
SQL> SELECT
s.sid,
s.username,
l.lock_address,
l.lock_hash,
l.namespace,
l.mode_held,
l.mode_requested
FROM v$session s,
x$kgllk l
WHERE s.saddr = l.kgllkuse
AND l.kgllkmod > 0;

SID USERNAME LOCK_ADDRESS LOCK_HASH NAMESPACE MODE_HELD MODE_REQUESTED
— ————- —————- ———- ————— ——— ————–
123 SCOTT 0000000081234567 1234567890 SQL AREA 3 0
124 HR 0000000087654321 9876543210 TABLE/PROCEDURE 2 3

# 查看库缓存对象
SQL> SELECT
address,
hash_value,
namespace,
object_name,
locks,
pins
FROM v$db_object_cache
WHERE namespace = ‘SQL AREA’
AND locks > 0
ORDER BY locks DESC
FETCH FIRST 10 ROWS ONLY;

ADDRESS HASH_VALUE NAMESPACE OBJECT_NAME LOCKS PINS
—————- ———- ———- —————————— —— —-
0000000081234567 1234567890 SQL AREA SELECT * FROM employees WHERE 5 10
employee_id = :1
0000000087654321 9876543210 SQL AREA SELECT * FROM departments 3 8
WHERE department_id = :1

# 查看SQL语句
SQL> SELECT
sql_id,
sql_text,
parse_calls,
executions
FROM v$sql
WHERE sql_id IN (‘abcd1234’, ‘xyz7890’);

SQL_ID SQL_TEXT PARSE_CALLS EXECUTIONS
————- ————————————————- ———– ———-
abcd1234 SELECT * FROM employees WHERE employee_id = :1 100 1000
xyz7890 SELECT * FROM departments WHERE department_id = :1 50 500

# 实时监控库缓存锁
SQL> SELECT
event,
wait_class,
COUNT(*)
FROM v$session_wait
WHERE event LIKE ‘library cache%’
GROUP BY event, wait_class;

EVENT WAIT_CLASS COUNT(*)
—————————— ——————– ——–
library cache lock Concurrency 5
library cache pin Concurrency 3

# 使用AWR报告监控库缓存锁
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

# 输入报告类型：html
# 输入开始快照ID：100
# 输入结束快照ID：101
# 输入报告文件名：awrrpt.html

# 查看AWR报告中的库缓存锁部分
# Top 5 Wait Events
~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
———————————— ———– ———– —— —— ———-
library cache lock 1000 5000 500 40.0 Concurrency
library cache pin 500 2500 500 20.0 Concurrency

# 查看库缓存锁历史
SQL> SELECT
h.snap_id,
h.event_name,
SUM(h.total_waits) AS total_waits,
SUM(h.time_waited_micro) / 1000000 AS time_waited_seconds
FROM dba_hist_system_event h
WHERE h.snap_id BETWEEN 100 AND 101
AND h.event_name LIKE ‘library cache%’
GROUP BY h.snap_id, h.event_name
ORDER BY h.snap_id;

SNAP_ID EVENT_NAME TOTAL_WAITS TIME_WAITED_SECONDS
——- —————————— ———– ——————
100 library cache lock 500 25
100 library cache pin 250 12
101 library cache lock 500 25
101 library cache pin 250 12

# 查看会话库缓存锁历史
SQL> SELECT
h.session_id,
h.event_name,
COUNT(*) AS wait_count,
SUM(h.wait_time + h.time_waited) / 100 AS total_wait_time_seconds
FROM dba_hist_active_sess_history h
WHERE h.sample_time BETWEEN SYSDATE – 1/24 AND SYSDATE
AND h.event_name LIKE ‘library cache%’
GROUP BY h.session_id, h.event_name
ORDER BY total_wait_time_seconds DESC
FETCH FIRST 10 ROWS ONLY;

SESSION_ID EVENT_NAME WAIT_COUNT TOTAL_WAIT_TIME_SECONDS
———- —————————— ———– ———————-
123 library cache lock 100 500
124 library cache pin 50 250