Oracle

December 25, 2011

Be very when careful tinkering with the STATISTICS_LEVEL setting

Filed under: Uncategorized — srivenu @ 11:24 am

Sometime back i was looking into the bad performance of some sql’s after the upgrade of a database from 10.2.0.4 to 10.2.0.5. Some of the sql’s started performing poorly since upgrade.

I started with one sql and I was in for a surprise when i checked the bind values peeked for that sql.

col sql_id head "SQL ID" form a13
col child_number head "Chi|ld|No" form 9999
col name head "Bind|Name" form a10
col POSITION head "Bind|Posi|tion" form 9999
col DATATYPE head "Data|type|ID" form 9999
col DATATYPE_STRING head 	"Datatype" form a14
col PRECISION head "Precision" form 9999
col scale head "Scale" form 9999
col MAX_LENGTH head "Max Bind|Length" form 9999
col WAS_CAPTURED head "Bind|value|Capt|ured|?" form a5
col lc head "Last|Captured" form a18
col VALUE_STRING head "Value|of Bind" form a15

select 	SQL_ID,
	CHILD_NUMBER,
	NAME, POSITION,
	DATATYPE, DATATYPE_STRING,
	PRECISION, SCALE, MAX_LENGTH, 
	WAS_CAPTURED, to_char(LAST_CAPTURED,'dd-mon-yy hh24:mi:ss') lc, 
	VALUE_STRING
from 	V$SQL_BIND_CAPTURE
where  	sql_id='f0yqgyfc7cz2v'
order	by 1,2,4
/


                                                                                   Bind
                                                                                   value
                Chi             Bind  Data                                         Capt
                 ld Bind        Posi  type                                Max Bind ured  Last               Value
SQL ID           No Name        tion    ID Datatype       Precision Scale   Length ?     Captured           of Bind
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
f0yqgyfc7cz2v     0 :B1            2     2 NUMBER                               22 NO
f0yqgyfc7cz2v     0 :B1            4     2 NUMBER                               22 NO
f0yqgyfc7cz2v     0 :B1            3     2 NUMBER                               22 NO
f0yqgyfc7cz2v     0 :B2            1     1 VARCHAR2(32)                         32 NO


No bind values!
Quick check and i found it to be the same same case with the other sql’s as well.

I then did a quick check for the whole system.

select  SQL_ID,
        CHILD_NUMBER,
        NAME, POSITION,
        DATATYPE, DATATYPE_STRING,
        PRECISION, SCALE, MAX_LENGTH,
        WAS_CAPTURED, to_char(LAST_CAPTURED,'dd-mon-yy hh24:mi:ss') lc,
        VALUE_STRING
from    V$SQL_BIND_CAPTURE
where   WAS_CAPTURED != 'NO'
order   by 1, 2, 4
/

no rows selected

I did a quick check for all parameter values containing the word bind.

col ksppinm head "Parameter" form a33
col ksppdesc head "Parameter Description" form a48
col ksppstvl head "Value" form a15
col ksppstdf head "Is|Defa|ult?" form a4 trunc
col alsession head "Alt|Sess|ion?" form a4 trunc
col alsystem head "Alter|Sys|tem?" form a4 trunc
col ismod head "Is|Modi|fied|?" form a4 trunc
col isadj head "Is|Adju|sted|?" form a4 trunc
col ksppstvf form 999

select 	ksppinm,ksppstvl,ksppstdf,  
	decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') alsession,  
	decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') alsystem,
	decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod, 
	decode(bitand(ksppstvf,2),2,'TRUE','FALSE') isadj,  
	ksppdesc 
from 	x$ksppi x, x$ksppcv y
where 	x.indx = y.indx
and	upper(x.ksppinm) like '%BIND%'
order 	by 1
/



                                                                 Is   Is
                                                  Is   Alt  Alte Modi Adju
                                                  Defa Sess Sys  fied sted
Parameter                         Value           ult? ion? tem? ?    ?    Parameter Description
--------------------------------- --------------- ---- ---- ---- ---- ---- ------------------------------------------------
_cursor_bind_capture_area_size    0               TRUE FALS IMME FALS FALS maximum size of the cursor bind capture area
_cursor_bind_capture_interval     900             TRUE FALS IMME FALS FALS interval (in seconds) between two bind capture f
                                                                           or a cursor

_cursor_cache_frame_bind_memory   FALSE           TRUE FALS FALS FALS FALS frame & bind buffer caching
_like_with_bind_as_equality       FALSE           TRUE TRUE IMME FALS FALS treat LIKE predicate with bind as an equality pr
                                                                           edicate

_optim_peek_user_binds            TRUE            TRUE TRUE IMME FALS FALS enable peeking of user binds
_optimizer_rownum_bind_default    10              TRUE TRUE IMME FALS FALS Default value to use for rownum bind
_px_bind_peek_sharing             TRUE            TRUE TRUE IMME FALS FALS enables sharing of px cursors that were built us
                                                                           ing bind peeking

_xpl_peeked_binds_log_size        8192            TRUE FALS IMME FALS FALS maximum bytes for logging peeked bind values for
                                                                            V$SQL_PLAN (0 = OFF)

_xsolapi_sql_use_bind_variables   TRUE            TRUE TRUE DEFE FALS FALS OLAP API enable bind variables optimization

9 rows selected.

The setting of “_cursor_bind_capture_area_size” to 0 did not look right.
The value for 400 for the other 10.2.0.4 databases.

I thought (but it did not seem right to me) that “_cursor_bind_capture_area_size” defaulted to 0 in 10.2.0.5
But on another 10.2.0.5, it was the default value of 400.

I then set it to 400

alter system set “_cursor_bind_capture_area_size” = 400
/

But the binds were still not getting captured. So i decided to

alter system flush shared_pool
/

And immediately i observed BIND value’s being captured again

select  SQL_ID,
        CHILD_NUMBER,
        NAME, POSITION,
        DATATYPE, DATATYPE_STRING,
        PRECISION, SCALE, MAX_LENGTH,
        WAS_CAPTURED, to_char(LAST_CAPTURED,'dd-mon-yy hh24:mi:ss') lc,
        VALUE_STRING
from    V$SQL_BIND_CAPTURE
where   WAS_CAPTURED != 'NO'
order   by 1, 2, 4
/


921 rows selected.

A quick search on the web revealed this good note by Yong Huang.
Yes it would be good to have a hierarchical view of all parameters based on their dependencies.
I wonder if there are any cyclical dependencies.

It seems that on this system the STATISTICS_LEVEL parameter was set to BASIC after the upgrade to 10.2.0.5 on Oracle’s suggestion for some other bug tracing purpose.

Moral of the story is to be very cautious when modifying the STATISTICS_LEVEL parameter as this could have a recursive impact on several other dependent parameters

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: