Dear Team,
For last couple of weeks our production server is experiencing some slowness. When i queried v$sql its showing lots of sqls which is parsed more than its execution. Also there are cases where sqls parse calls are almost equal to its execution. I found sqls executed by the database as well as sqls executed by application. But both these types of sqls are using bind variables.
Example of System executed sqls :
delete from dependency$ where d_obj#=:1
executions 2418720 parse_calls 2418734
SELECT SUBSTR(MLINK,2), TNAME, USLOG, SUBSTR(AUTO_FAST,1,1), BITAND(FLAG, :B4 ), BITAND(FLAG, :B3 )
FROM SYS.SNAP$ WHERE SOWNER = :B2 AND VNAME = :B1 AND INSTSITE = 0
executions 222683 parse_calls 222760
update sum$ set containerobj#=:2,containertype=:3,containernam=:4,fullrefreshtim=:5,increfreshtim=:6,lastrefreshscn=:7,lastrefreshdate=:8,refreshmode=:9,pflags=:10,mflags=:11,numdetailtab=:12,numaggregates=:13,numkeycolumns=:14,numjoins=:15,numinlines=:16,fromoffset=:19,fromlen=:20,objcount=:21,metaversion=:22, xpflags=:23,numwhrnodes=:24, numhavnodes=:25, numqbnodes=:26, qbcmarker=:27, markerdty=:28, rw_mode=:29, rw_name=:30, dest_stmt=NULL, src_stmt=NULL, spare1=:31, evaledition#=:33, unusablebefore#=:34, unusablebeginning#=:35 where obj#=:1
executions 637934 parse_calls 638641
and many others.
I have found that there are many procedures, functions and packages which is experiencing high libraary cache wait events or other types of latching wait events. We are having 21 gb of sga and pga_max_target of 20 gb. Its a linux box running oracle version 12.1.0.2.0 standard version.
Is it insufficient sga which is aging out sqls?
Looking forward to your advice.
Thanks and regards.
As these are system SQL (statements the database runs to process your statements), there's not much you can do about their parse/execution ratio. Other than find out which process is triggering them. And to stop doing that ;)
So dig around to see where these statements are coming from.
My guess: you can see library cache waits & statements on dependency$ when compiling PL/SQL. So maybe something is invalidating your code.
Are you doing anything that would invalidate your procedures/functions?
e.g. running DDL? Issuing recompiles?