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.
January 23, 2013
Storage Virtualization and Dynamic Tiering
March 29, 2012
Tuning sql with predicates on Scalar Subqueries
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
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”
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
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).
