Oracle

January 23, 2013

Storage Virtualization and Dynamic Tiering

Filed under: Uncategorized — srivenu @ 9:28 am

One of my recent projects, a DW project for a Telecom company in South Africa, involved configuring Hitachi VSP storage. Its good to see how the storage technologies are evolving, freeing the DBA’s from the nuts and bolts activity of configuring the storage.
I turned nostalgic, remembering how things were during the start of my career. At the start, while using direct attached storage, i used to carefully design file systems according to their IO pattern and performance requirements and carve them on different sectors of the hard disks. Later on volume managers simplified things. I still vividly remember configuring the high-end EMC Symmetrix 8830 boxes (as well as HP Superdome 24000) in 2001. Configuring that high-end storage too wasn’t free from the nuts and bolts activity. I was closely working with the EMC engineers in carving out Hypers and Metas. We still worked at sector level for better performance of some classes of data like REDO.
I feel that enhancements in storage virtualization and Dynamic Tiering are going to reduce a lot of storage configuration effort and headache for the DBA’s. It would be interesting to watch how the storage technologies fan out in the future.

March 29, 2012

Tuning sql with predicates on Scalar Subqueries

Filed under: Uncategorized — srivenu @ 12:13 am

Due to lack of standardization on technical terms, i want to mention at the outset that by “Scalar subqueries” i meant subqueries appearing in the FROM clause. The Scalar subquery executes for each row of the driving query and returns one row. The scalar subqueries could be converted into outer joins, but if there are predicates in the scalar subqueries or on values returned by them, we need to take care of how we write the predicates using outer joins lest they become outer join killers.

Following is one sql that i worked on recently.


SELECT	....
FROM	(
	SELECT	TA.ID,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'FT'
		) 	TY,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'EOI'
		)	EOI,
		(
		SELECT 	VAL
		FROM 	PROP
		WHERE 	ID = TA.ID
		AND 	NAME = 'IG'
		)	IG,
		....
	FROM 	TASK TA
	WHERE	TA.NAME = 'HPE'
	)
WHERE	IG = 'N'
AND	EOI = '123456'
AND	TY = 'PP'
/

Following is the plan and some relevant execution stats


------------------------------------------------------------------------------------
| Id  | Operation            | Name                    | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |                         |      1 |      1 |    5003 |
|*  2 |   INDEX RANGE SCAN   | IDX_TASK_NA_ID          |      1 |      0 |    5003 |
|*  3 |    INDEX RANGE SCAN  | IDX_PROP_NA_ID_VA       |    902 |    902 |    2801 |
|*  4 |     INDEX RANGE SCAN | IDX_PROP_NA_ID_VA       |    613 |    613 |    1910 |
|*  5 |      INDEX RANGE SCAN| IDX_PROP_NA_ID_VA       |      0 |      0 |       0 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / TA@SEL$2
   3 - SEL$5        / PROP@SEL$5
   4 - SEL$4        / PROP@SEL$4
   5 - SEL$3        / PROP@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TA"."NAME"='HPE')
       filter((='N' AND ='123456' AND ='PP'))
   3 - access("PROP"."NAME"='IG' AND "PROP"."ID"=:B1)
   4 - access("PROP"."NAME"='EOI' AND "PROP"."ID"=:B1)
   5 - access("PROP"."NAME"='FT' AND "PROP"."ID"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - "PROP"."VAL"[VARCHAR2,254]
   4 - "PROP"."VAL"[VARCHAR2,254]
   5 - "PROP"."VAL"[VARCHAR2,254]

The following select on TASK table retrieves 902 rows.


SELECT	TA.ID, ......
FROM	TASK TA
WHERE	TA.NAME = 'HPE'

The IDX_TASK_NA_ID index on TASK table is on columns NAME, ID, …(and a few other in that order)
The IDX_PROP_NA_ID_VA index on PROP table is on the columns NAME, ID, VAL (in that order)

The code path of the plan can be deduced as follows


Step 1 - Get columns ID, .. from IDX_TASK_NA_ID where TA.NAME = 'HPE'
Step 2 -  for each row from Step 1, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'IG' and PROP.ID = TA.ID
Step 3 -   for each row from Step 2, check if IG = 'N' if true go to Step 4 else go to Step 1 and retrieve next row
Step 4 -    for each row from Step 3, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'EOI' and PROP.ID = TA.ID
Step 5 -     for each row from Step 4, check if EOI = '123456', if true go to Step 6 else go to Step 1 and retrieve next row
Step 6 -      for each row from Step 5, get data from IDX_PROP_NA_ID_VA where PROP.NAME = 'PP' and PROP.ID = TA.ID
Step 7   -     for each row from Step 6, check if TY = 'PP', if true select the row else go to Step 1 and retrieve next row

Steps 2, 4 & 6 may in fact not result in an actual index access due to something called “scalar subquery caching”. You could refer to this “exchange” between the masters for more info.
So from the execution stats, we can make out that,
At Step 1, 902 rows were retrieved from TASK table.
At Step 2, the IDX_PROP_NA_ID_VA index was accessed 902 times to retrieve PROP.NAME for that ID, out of the 902 rows, only 613 rows had PROP.NAME = ‘IG’.
At Step 4, the IDX_PROP_NA_ID_VA index was accessed 613 times to retrieve PROP.NAME for that ID, out of the 613 rows, 0 rows had PROP.NAME = ‘EOI’.
The rest of the steps were not executed at all.

The key to tuning the above query is to see that the predicates are ordered in the right way and see that the max throw away occurs at the first occassion itself. This would have been easier to control if outer joins were used instead of the scalar subquery variant. I couldn’t find (atleast in 10g) any hint or outline to order the evaluation of scalar subqueries. But we are not without hope as we could either use the ORDERED_PREDICATE hint or put the predicates in required order and the CBO automatically takes the order into consideration.

Following are the stats for the different variations in the order of predicates. Using the predicate EOI = ’123456′ (which has the max throwaway) at the start will result in the best performance.




********************************************************************************

WHERE   TY = 'PP'
AND     EOI = '123456'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.04          0       5914          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.04          0       5914          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5914 pr=0 pw=0 time=42585 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5914 pr=0 pw=0 time=42556 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=19535 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=16792 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   TY = 'PP'
AND     IG = 'N'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.05          0       7816          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06          0       7816          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7816 pr=0 pw=0 time=59340 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=7816 pr=0 pw=0 time=59308 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2808 pr=0 pw=0 time=21716 us)(object id 169317)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2806 pr=0 pw=0 time=18735 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=11329 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     TY = 'PP'
AND     EOI = '123456'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.04          0       6905          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.04          0       6905          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=6905 pr=0 pw=0 time=41378 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=6905 pr=0 pw=0 time=41352 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14822 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1902 pr=0 pw=0 time=9199 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=9992 us)(object id 169317)

********************************************************************************

WHERE   IG = 'N'
AND     EOI = '123456'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.03          0       5003          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.03          0       5003          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=5003 pr=0 pw=0 time=31018 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=5003 pr=0 pw=0 time=30988 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2801 pr=0 pw=0 time=14333 us)(object id 169317)
    613    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=1910 pr=0 pw=0 time=10980 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     IG = 'N'
AND     TY = 'PP'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=22878 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=22853 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=17653 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

WHERE   EOI = '123456'
AND     TY = 'PP'
AND     IG = 'N'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       3106          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       3106          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3106 pr=0 pw=0 time=18483 us)
      0   INDEX RANGE SCAN IDX_TASK_NA_ID (cr=3106 pr=0 pw=0 time=18454 us)(object id 165635)
    902    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=2814 pr=0 pw=0 time=14361 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)
      0    INDEX RANGE SCAN IDX_PROP_NA_ID_VA (cr=0 pr=0 pw=0 time=0 us)(object id 169317)

********************************************************************************

March 6, 2012

Blocking_Instance and Blocking_Session values in GV$SESSION could be wrong

Filed under: Uncategorized — srivenu @ 10:26 am

Recently in one of our RAC systems (64bit – 10.2.0.4.0 on AIX 5.3), I observed several sessions waiting on “enq: TX – row lock contention”.
The waiters locked out at the following update


UPDATE 	CUST
SET 	UPDATED_DATE = :1 
WHERE 	CUSTID = :2

I tried to find out the enqueue holders


select 	blocking_instance, blocking_session, inst_id, sid
from 	gv$session 
where 	event = 'enq: TX - row lock contention'
order	by 1, 2, 3, 4
/


                                     Inst
BLOCKING_INSTANCE BLOCKING_SESSION   ance    Sid
----------------- ---------------- ------ ------
                1              489      1    962
                1              489      3    642
                1              840      3    777
                1              875      1    953
                1              875      3    883
                1              986      3    928
                1              986      3    971
                1             1042      1    821
                1             1079      3    533

                2              495      2    762
                2              495      3    729
                2             1079      3    604

                4              841      4    733
                4              887      4   1005
                4              968      3    916
                4             1029      3    581

Sid 1079 on Instance 1 is one of the enqueue holders.

And following are the details of sid 1079 on instance 1


col sid head "Sid" form 99999
col pid head "PID" form 9999
col serial# form 99999 head "Ser#"
col username head "DB User" form a8
col program head "Program" form a10
col module head "Module" form a10
col action head "Client|Action" form a12
col machine head "Client|Machine" form a15
col cprocess head "Client|Process|ID" form a9
col osuser head "Client|OS User" form a7 
col spid head 'Oracle|Back|ground|Process|ID' form a8


select 	s.sid, s.serial#, p.pid, s.process cprocess, s.osuser osuser,
	s.program, s.module, s.action, s.machine machine, s.username username, p.spid
from 	v$session s,
	v$process p
where 	s.paddr(+)=p.addr
and	sid = 1079
order 	by 1
/

                                                                                                  Oracle
                                                                                                  Back
                    Client                                                                        ground  
                    Process   Client                        Client       Client                   Process 
   Sid   Ser#   PID ID        OS User Program    Module     Action       Machine         DB User  ID      
------ ------ ----- --------- ------- ---------- ---------- ------------ --------------- -------- --------
  1079      1    18 13541454  oracle  oracle@abc                         abcora1racprod           13541454
                                      def1racpro
                                      d (DBW0)

The holder is the DBW0 process on instance 1
Why does a DBW0 process hold up a foreground session on a TX enqueue on a user table?
Having experienced this issue several times in the past, i decided to put in a small note to warn others not to take the Blocking_Instance and Blocking_Session values from GV$SESSION view at face value.

I normally use the following sql to find out the holders of “enq: TX – row lock contention”


col wi head "Wai|ti|ng|In|St|an|ce" form 9999
col wu head "Waiting|User" form a10
col wsid head "Waiting|Sid" form 99999
col wser head "Waiting|Ser#" form 99999

col hi head "Ho|ld|ing|In|St|an|ce" form 9999
col hu head "Holding|User" form a10
col hsid head "Holding|Sid" form 99999
col hser head "Holding|Ser#" form 99999

select  hs.inst_id hi, hs.username hu, hs.sid hsid, hs.SERIAL# hser, 
	ws.inst_id wi, ws.username wu, ws.sid wsid, ws.SERIAL# wser, 
	t.xidusn, t.xidslot, t.xidsqn, t.status
from 	gv$session ws, gv$session hs, gv$transaction t
where 	hs.taddr = t.addr
and	hs.inst_id = t.inst_id
and	t.xidusn = trunc(ws.p2/65536)
and	t.xidslot = mod(ws.p2,65536)
and	t.xidsqn = ws.p3
and	ws.event like 'enq: TX%'
order 	by hs.inst_id, hs.sid, hs.SERIAL#, ws.inst_id, ws.sid
/

The output is something like this



   Ho                              Wai
   ld                               ti
  ing                               ng
   In                               In
   St                               St                             undo    undo     undo
   an Holding    Holding Holding    an Waiting    Waiting Waiting   Seg    Slot      Seq
   ce User           Sid    Ser#    ce User           Sid    Ser#    No      No       No Status
----- ---------- ------- ------- ----- ---------- ------- ------- ----- ------- -------- ------
    1 ABCDEFGH       489   16589     1 ABCDEFGH       962    7535     5      27   394378 ACTIVE
    1 ABCDEFGH       489   16589     2 ABCDEFGH       423     365     5      27   394378 ACTIVE
    1 ABCDEFGH       840   61100     2 ABCDEFGH       580    1346    97      37    12608 ACTIVE
    1 ABCDEFGH       875   64492     1 ABCDEFGH       953   40122    56      43    87618 ACTIVE
    1 ABCDEFGH       875   64492     2 ABCDEFGH       726   20828    56      43    87618 ACTIVE
    1 ABCDEFGH       967   47330     2 ABCDEFGH       815    3000    55      42   116791 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH       764   54442    71       8   212341 ACTIVE
    1 ABCDEFGH       986   60240     2 ABCDEFGH      1017    4489    71       8   212341 ACTIVE
    1 ABCDEFGH      1042   42742     1 ABCDEFGH       821   42845    58       0   100629 ACTIVE

    2 ABCDEFGH       425   14272     1 ABCDEFGH      1028   42640   113      14   291960 ACTIVE
    2 ABCDEFGH       425   14272     4 ABCDEFGH       717   20891   113      14   291960 ACTIVE
    2 ABCDEFGH       495    8828     1 ABCDEFGH       458   28807   195      40    55394 ACTIVE
    2 ABCDEFGH       495    8828     2 ABCDEFGH       762   39933   195      40    55394 ACTIVE
    2 ABCDEFGH       706   54438     1 ABCDEFGH       960   24251   209      18     7352 ACTIVE
    2 ABCDEFGH       872   55363     4 ABCDEFGH       887   19939   212      30    21108 ACTIVE
    2 ABCDEFGH       893   60900     1 ABCDEFGH       843   38790   240      33     2689 ACTIVE

March 4, 2012

“Bind Variable Peeking” vs “Bind Variable Capture”

Filed under: Uncategorized — srivenu @ 6:35 am

Bind variable peeking is a feature introduced in 9i. The CBO looks (peeks) at the underlying values of bind variables to generate a better execution plan. The bind values peeked can be seen in the OTHER_XML column in V$SQL_PLAN or BIND_DATA column in V$SQL. The space used for peeked binds in V$SQL_PLAN is determined by the parameter “_xpl_peeked_binds_log_size”. In some circumstances (ex – sql with large inlists of bind variables) the size may be exceeded and we might not see all the peeked bind values. But that doesnt mean that the variable value is not peeked.

Bind variable capture is a feature introduced in 10g. It is just a periodic capture of the underlying values of bind variables. It had nothing to do with plan generation(i.e the plan is not generated for this set of bind values). It only helps us to periodically look at new set of bind values for that sql. The captured values are available from V$SQL_BIND_CAPTURE. The capturing interval is determinded by the parameter “_cursor_bind_capture_interval”. The space used for captured binds is determined by the parameter “_cursor_bind_capture_area_size”. In some circumstances (ex – sql with large inlists of bind variables) the size may be exceeded and we might not see all the captured bind values. In such cases setting “_cursor_bind_capture_interval” to the max value of 3999 helps in looking at most of the captured values.

I got a sql (with sql_id – ac95hnhh878hc) like this


select 	......
from 	SUB
where 	(
		(
		ID in 	(
			:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, 
			:21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38,
			.......................
			.......................
			:993, :994, :995, :996, :997, :998, :999, :1000
			) 
		and 	CIRCLE = :1001
		) 
	and 	STATUS in (:1002, :1003,:1004, :1005)
	)
/


To look at the peeked binds for the above sql, you could use any of the following sql.

select 	* 
from 	table(DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, 'advanced'))
/

or

select 	* 
from 	table(DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, '+peeked_binds'))
/

or 

(i had to write the case for bind_data in below sql in a funny sort of way due to some issue on my db version)

col sql_id head "SQL ID" form a13
col child_number head "Chi|ld|No" form 9999
col pos  head "Bind|Posi|tion" form a4
col nam head "Bind|Name" form a10
col dty head "Data|type|ID" form a4
col datatype_string head "Datatype" form a14
col mxl head "Max Bind|Length" form a4
col was_captured head "Bind|value|Capt|ured|?" form a5
col bind_data_fm head "Actual Value of Bind" form a25
col bind_data_raw head "Raw Value of Bind" form a25

select	'&1' sql_id,
	decode('&2',null,0,'&2') child_number,
	pos,
	nam,
	dty,
	case 	when dty = 1 then 'VARCHAR2'
		when dty = 2 then 'NUMBER'
		else dty 
	end 	datatype_string,
	mxl,
	case 	when dty = 1
			then utl_raw.cast_to_varchar2(bind_data)
	end	||
	case 	when dty = 2
			then utl_raw.cast_to_number(bind_data)
	end 	bind_data_fm,
	bind_data bind_data_raw
from 	(
	select	extractvalue(value(d), '/bind/@pos') as pos,
		extractvalue(value(d), '/bind/@nam') as nam,
		extractvalue(value(d), '/bind/@dty') as dty,
		extractvalue(value(d), '/bind/@mxl') as mxl,
		extractvalue(value(d), '/bind') as bind_data
	from	xmltable('/*/*/bind' passing (
			select	xmltype(other_xml) as xmlval
			from	v$sql_plan
			where	sql_id = '&1'
			and	child_number = decode('&2',null,0,'&2')
			and 	other_xml is not null
			)
		) d
	)
/


The output was something like this for DBMS_XPLAN.DISPLAY_CURSOR('ac95hnhh878hc', null, 'advanced'))

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |       |       | 18795 (100)|          |       |       |
|   1 |  PARTITION RANGE SINGLE|               |  1167 | 59517 | 18795  (18)| 00:03:46 |   KEY |   KEY |
|   2 |   PARTITION HASH ALL   |               |  1167 | 59517 | 18795  (18)| 00:03:46 |     1 |    29 |
|*  3 |    TABLE ACCESS FULL   |           SUB |  1167 | 59517 | 18795  (18)| 00:03:46 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :V1 (VARCHAR2(30), CSID=31): '1234567890'
   2 - :V2 (VARCHAR2(30), CSID=31): '2345678910'
   3 - :V3 (VARCHAR2(30), CSID=31): '3456789120'
.................
.................
 511 - :V511 (VARCHAR2(30), CSID=31): '9876543210'
 512 - :V512 (VARCHAR2(30), CSID=31): '9999999999'
 513 - :V513 (VARCHAR2(30), CSID=31, Not Captured)
 514 - :V514 (VARCHAR2(30), CSID=31, Not Captured)
.................
.................
1001 - :v1001 (NUMBER, Not Captured)
1002 - :v1002 (VARCHAR2(30), CSID=31, Not Captured)
1003 - :v1003 (VARCHAR2(30), CSID=31, Not Captured)
1004 - :v1004 (VARCHAR2(30), CSID=31, Not Captured)
1005 - :v1005 (VARCHAR2(30), CSID=31, Not Captured)

The output shows that the bind variable :v1001 is “Not Captured”. This does not mean that it is not peeked.
I had confirmed this by using 2 different executions with different CIRCLE values.
For CIRCLE value 14, the CBO uses a INDEX RANGE SCAN & for CIRCLE value 23, the CBO uses a Full Table Scan.
So even though the output showed 1001 – :v1001 (NUMBER, Not Captured), there was a change in the plan, which meant that the value was peeked.
As mentioned at the start, the max value of binds peeked is determined by the parameter “_xpl_peeked_binds_log_size” and it maxes out at 8192.

To look at the captured binds, i use the following sql. (“_cursor_bind_capture_interval” is set to 3999)


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='&1'
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
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
1yuk67czbhum1     2 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 0123456789
1yuk67czbhum1     2 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 1111111111
1yuk67czbhum1     2 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 2222222222
1yuk67czbhum1     2 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :398         398     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 6767676767
1yuk67czbhum1     2 :399         399     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 7878787878
1yuk67czbhum1     2 :400         400     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :401         401     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1001       1002     2 NUMBER                               22 YES   17-feb-12 18:58:44 99
1yuk67czbhum1     2 :1002       1002     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL1
1yuk67czbhum1     2 :1003       1003     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL2
1yuk67czbhum1     2 :1004       1004     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL3
1yuk67czbhum1     2 :1005       1005     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL4

February 20, 2012

Setting _cursor_bind_capture_area_size for large inlists

Filed under: Uncategorized — srivenu @ 10:23 am

In one of the databases i was working on (Oracle 10.2.0.4.0 – 64bit on AIX6.1), there is a sql like this


select 	......
from 	SUB
where 	(
		(
		ID in 	(
			:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, 
			:21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38,
			.......................
			.......................
			:993, :994, :995, :996, :997, :998, :999, :1000
			) 
		and 	CIRCLE = :1001
		) 
	and 	STATUS in (:1002, :1003,:1004, :1005)
	)
/

I checked the bind variables captured for this 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='&1'
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
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
1yuk67czbhum1     1 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 0123456789
1yuk67czbhum1     1 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 1111111111
1yuk67czbhum1     1 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 2222222222
1yuk67czbhum1     1 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     1 :39           39     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 6767676767
1yuk67czbhum1     1 :40           40     1 VARCHAR2(32)                         32 YES   17-feb-12 12:20:30 7878787878
1yuk67czbhum1     1 :41           41     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :42           42     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     1 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1001       1002     2 NUMBER                               22 NO
1yuk67czbhum1     1 :1002       1002     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1003       1003     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1004       1004     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     1 :1005       1005     1 VARCHAR2(32)                         32 NO

Since the parameter _cursor_bind_capture_area_size was set to the default value of 400, the values of only 40 bind variables (each 10 bytes in size) have been captured.
But I wanted to see the value for bind variable :1001 (in the predicate CIRCLE = :1001). Since it is at the 1001 position, i thought i needed to set _cursor_bind_capture_area_size to something above 10003. When i tried to set it to a value of 10003, i got an error. The max allowed value for that parameter is 3999. I wanted to check what would happen if i set it to the max value and following is what is captured.


                                                                                   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
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
1yuk67czbhum1     2 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 0123456789
1yuk67czbhum1     2 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 1111111111
1yuk67czbhum1     2 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 2222222222
1yuk67czbhum1     2 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :398         398     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 6767676767
1yuk67czbhum1     2 :399         399     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 7878787878
1yuk67czbhum1     2 :400         400     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :401         401     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1001       1002     2 NUMBER                               22 YES   17-feb-12 18:58:44 99
1yuk67czbhum1     2 :1002       1002     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL1
1yuk67czbhum1     2 :1003       1003     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL2
1yuk67czbhum1     2 :1004       1004     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL3
1yuk67czbhum1     2 :1005       1005     1 VARCHAR2(32)                         32 YES   17-feb-12 18:58:44 VAL4

I then tried setting it to 3998 and this is what is captured.


                                                                                   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
------------- ----- ---------- ----- ----- -------------- --------- ----- -------- ----- ------------------ ---------------
1yuk67czbhum1     2 :1             1     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 0123456789
1yuk67czbhum1     2 :2             2     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 1111111111
1yuk67czbhum1     2 :3             3     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 2222222222
1yuk67czbhum1     2 :4             4     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 3333333333
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :398         398     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 6767676767
1yuk67czbhum1     2 :399         399     1 VARCHAR2(32)                         32 YES   17-feb-12 19:19:01 7878787878
1yuk67czbhum1     2 :400         400     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :401         401     1 VARCHAR2(32)                         32 NO
.......................................................................................................................
Continues like this
.......................................................................................................................
1yuk67czbhum1     2 :999         999     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1000       1000     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1001       1002     2 NUMBER                               22 NO
1yuk67czbhum1     2 :1002       1002     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1003       1003     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1004       1004     1 VARCHAR2(32)                         32 NO
1yuk67czbhum1     2 :1005       1005     1 VARCHAR2(32)                         32 NO

So looks like setting parameter _cursor_bind_capture_area_size to the max value has some significance.
If you set _cursor_bind_capture_area_size to 3999, it is going to capture all the single element bind variables and small inlists and try to cut down from storing all values in large inlists. This would be useful if you have large inlists (I think it would be very rare to see large number of bind variables without inlists).

Next Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.