atch: shared pool 优化探索

最新推荐文章于 2024-11-15 16:20:38 发布

原创最新推荐文章于 2024-11-15 16:20:38 发布 · 912 阅读

0 ·

CC 4.0 BY-SA版权

优化专栏收录该内容

20 篇文章

订阅专栏

本文深入分析了SQL解析过程，通过Top10 Foreground Events来探讨性能瓶颈，并提出针对性优化策略，重点关注共享池（sharedpool）的利用效率，以及如何通过调整SGA大小和参数设置来提升数据库性能。

首先来看赤裸裸的问题直击：

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
latch: shared pool	1,592	20.2K	12682	25.0	Concurrency
db file sequential read	3,690,036	17.9K	5	22.2	User I/O
DB CPU		9937.8		12.3
db file scattered read	4,091,506	8083.1	2	10.0	User I/O
read by other session	621,696	6147.1	10	7.6	User I/O
library cache: mutex X	372	3674.9	9879	4.6	Concurrency
latch free	162	2574.3	15891	3.2	Other
log file sync	14,943	564	38	.7	Commit
cursor: pin S wait on X	27	356.6	13207	.4	Concurrency
library cache load lock	19	345.7	18196	.4	Concurrency

再来分析下shared pool的功用

共享池（shared pool）的目的就是共享SQL或PL/SQL代码，即把SQL代码的结果在这里缓存，其中PL/SQL的代码

不仅在这里缓存也在这里共享。shared pool由2部分组成即库高速缓存（Library cache）和数据字典高数缓存

（Data dict cache) 我们可以如下查看共享池的大小

SQL> show parameter shared_pool_size

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size		     big integer 0
SQL> show parameter sga_target

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
sga_target			     big integer 14G
SQL>

知道了根源那么我们就可以判定latch: shared pool是由SQL解析缓存等引起的，第一想到的是调整sga大小，动态或手动

其次那也就无非一下几种原因：

1.最可能的就是硬解析

牵扯到硬解析肯定就要去考虑绑定变量，共享游标（参数cursor_sharing）

SQL> show parameter cursor_sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT
SQL>

但是本例貌似不像硬解析原因，理由如下

Parses (SQL):	64.5	26.9
Hard parses (SQL):	1.0	0.4

由上可见硬解析才占了 1.0/64.5*100%=1.55%

下面语句可以查看没有使用绑定变量的sql情况

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC;

因为一共有95条记录以下简单列出几条

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORCE_MATCHING_SIGNATURE unshared count
------------------------ --------------
SELECT ttt.cust_phone as CUST_PHONE,ttt.begin_time as BEGIN_TIME,ttt.service_type as TYPE,  ttt.length as LENGTH,tt.times as TIMES,u.user_name AS USER_NAME FROM cc_hollycrmapp.tbl_record ttt,  (SELECT t.cust_phone,max(t.begin_time) AS begin_time,COUN
T(*) AS times FROM cc_hollycrmapp.tbl_record t	where t.cust_phone='18320899701' GROUP BY t.cust_phone)  tt,cc_hollycrmapp.tbl_sys_users u WHERE ttt.cust_phone=tt.cust_phone AND ttt.begin_time=tt.begin_time AND ttt.user_id=u.user_id
     5809074626252118280	   4633

select message1_.MESSAGEID as MESSAGEID, message1_.CHANNELID as CHANNELID, message1_.KEYWORD as KEYWORD, message1_.MESSAGETOPIC as MESSAGET4_, message1_.MESSAGEBODY as MESSAGEB5_, message1_.MESSAGEURL as MESSAGEURL, message1_.MESSAGEACTION as MESSAGE
A7_, message1_.MESSAGETYPE as MESSAGET8_, message1_.MESSAGELEVEL as MESSAGEL9_, message1_.MESSAGESTATUS as MESSAGE10_, message1_.CREATER as CREATER, message1_.BEGINDATE as BEGINDATE, message1_.ENDDATE as ENDDATE, message1_.CREATEDATE as CREATEDATE, m
essage1_.ATTACHEMENTNAME as ATTACHE15_, message1_.READFLAG as READFLAG, message1_.BUSINESSID as BUSINESSID from TBL_MSG_USER_MESSAGE usermessag0_, TBL_MSG_MESSAGE message1_ where (usermessag0_.READSTATUS=0 )and(usermessag0_.CUSTOMERNO='WUQIAN' )and(u
sermessag0_.MESSAGEID=message1_.MESSAGEID )and(message1_.BEGINDATE<='2015-12-22 16:58:16' )and(message1_.ENDDATE>'2015-12-22 16:58:16' )
     4236029317084897534	   4190

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORCE_MATCHING_SIGNATURE unshared count
------------------------ --------------

SELECT a.area_name AS PROVINCE,aa.area_name AS CITY FROM cc_hollycrmapp.tbl_code_area a, cc_hollycrmapp.tbl_code_area aa,cc_hollycrmapp.tbl_code_area_mobile m WHERE m.mobile_title='1331659'  AND aa.area_code=m.area_code AND a.area_code=aa.parent_prov
ince(+)
     5424992258198287322	   3020

select count(*) as x0_0_ from TBL_QC_RESULT qcresult0_ where (qcresult0_.STATUS in('1' , '2'))and(qcresult0_.TARGET_CONT_IDX='DVS20151221170144089-7448' )
     1495393791543204614	   1831

select qcresult0_.ID as ID, qcresult0_.CRITERION_ID as CRITERIO2_, qcresult0_.CRITERION_VERSION as CRITERIO3_, qcresult0_.TARGET_AGENT as TARGET_A4_, qcresult0_.TARGET_CONT_TYPE as TARGET_C5_, qcresult0_.TARGET_CONT_IDX as TARGET_C6_, qcresult0_.QC_U

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORCE_MATCHING_SIGNATURE unshared count
------------------------ --------------
SER as QC_USER, qcresult0_.QC_TIME as QC_TIME, qcresult0_.QC_WAY as QC_WAY, qcresult0_.KEY_ERROR_COUNT as KEY_ERR10_, qcresult0_.NOT_KEY_ERROR_COUNT as NOT_KEY11_, qcresult0_.KEY_RESULT as KEY_RESULT, qcresult0_.NOT_KEY_RESULT as NOT_KEY13_, qcresult
0_.FINAL_RESULT as FINAL_R14_, qcresult0_.STRONGPOINT as STRONGP15_, qcresult0_.SHORTPOINT as SHORTPOINT, qcresult0_.ADVICE as ADVICE, qcresult0_.REMARK as REMARK, qcresult0_.STATUS as STATUS, qcresult0_.BLANK1 as BLANK1, qcresult0_.BLANK2 as BLANK2,
 qcresult0_.BLANK3 as BLANK3, qcresult0_.BLANK4 as BLANK4, qcresult0_.BLANK5 as BLANK5, qcresult0_.COMPLAINT_ID as COMPLAI25_, qcresult0_.COMPLAINT_CONTENT as COMPLAI26_, qcresult0_.UPDATE_RESULT as UPDATE_27_, qcresult0_.UPDATE_REASON as UPDATE_28_,
    18061785689780261340	   1824

select count(*) as TOTAL_NUM  FROM cc_hollycrmapp.tbl_record t,cc_hollycrmapp.tbl_sys_users u WHERE t.user_id=u.user_id  and u.agent_dn = '6047'  and t.service_type='I'  and t.begin_time>='2015-12-07 00:00:00'  and t.begin_time<='2015-12-13 12:12:53'
  order by t.begin_time
    17950504111244851135	   1812


SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORCE_MATCHING_SIGNATURE unshared count
------------------------ --------------
SELECT * FROM ( SELECT tt.cust_phone as CUST_PHONE,tt.begin_time as BEGIN_TIME,  tt.end_time as END_TIME,tt.length as LENGTH ,tt.file_name as FILE_NAME,tt.user_name AS USER_NAME,tt.agent_dn as AGENT_DN,tt.service_type as DIRET  ,ROWNUM AS ROW_NUM fro
m (SELECT ROWNUM AS rm,t.cust_phone,t.begin_time,t.end_time,t.length,t.file_name,u.user_name,u.agent_dn,t.service_type	FROM cc_hollycrmapp.tbl_record t,cc_hollycrmapp.tbl_sys_users u WHERE t.user_id=u.user_id  and u.agent_dn = '6128'  and t.service_
type='I'  and t.begin_time>='2015-12-08 00:00:00'  and t.begin_time<='2015-12-08 10:52:47'  order by t.begin_time ) tt) where ROW_NUM>0 and  ROW_NUM<=100
    15440612456729430316	   1708

delete call_rec where 1=1  and segstart >=  1450756080 and segstart < 1450757580
     5664050145556274538	    939

delete ag_actv where 1=1  and event_time >=  1450587600 and event_time < 1450602000

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FORCE_MATCHING_SIGNATURE unshared count
------------------------ --------------
    14000516793726580255	    887

delete haglog where 1=1  and login >=  1450779120 and login < 1450780620
     5495545422845773602	    648