Execute to Parse %: 如果该值偏小,说明分析(硬解析与软解析 )的比例较大,快速解析(即软软解析)较少。解决方法: 增大session_cached_cursors(每个会话 可以缓存多少个cursor,让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能)
SQL执行过程中硬解析占比过高,影响SQL执行效率,其原因有可能是游标缓存不足,通过如下语句查询可以看到使用率已经100%,建议根据实际业务增大游标缓存。
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
SQL> SELECT 'session_cached_cursors' PARAMETER,
LPAD(VALUE, 5) VALUE,
DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE
FROM (SELECT MAX(S.VALUE) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME = 'session cursor cache count'
AND S.STATISTIC# = N.STATISTIC#),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
LPAD(VALUE, 5),
TO_CHAR(100 * USED / VALUE, '990') || '%'
FROM (SELECT MAX(SUM(S.VALUE)) USED
FROM V$STATNAME N, V$SESSTAT S
WHERE N.NAME IN
('opened cursors current', 'session cursor cache count')
AND S.STATISTIC# = N.STATISTIC#
GROUP BY S.SID),
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');
PARAMETER VALUE USAGE
---------------------- ---------- -----
session_cached_cursors 50 100%
open_cursors 300 20%
对session_cached_cursors参数进行修改。
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
SQL> alter system set session_cached_cursors=100 scope=spfile;
System altered.
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 542851072 bytes
Fixed Size 2254952 bytes
Variable Size 163579800 bytes
Database Buffers 373293056 bytes
Redo Buffers 3723264 bytes
Database mounted.
Database opened.
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 100