The word "Tuning" should be in discussion right at the initial stage of your design and not just after shipping you code to production. In most of the environments have worked, The attitude to ship the code first - meet the timelines and tune later was prominent. Let us see once such use case now,
SQL> select * from tblchk tbl1 where exists
2 (select 1 from tblchk tbl2 where tbl1.id=tbl2.id and tbl2.id_val='ORACLE');
no rows selected
And the Plan
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 487 | 12 | | | |
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 190K| 0 |00:00:00.01 | 487 | 12 | 1645K| 1645K| 488K (0)|
|* 2 | TABLE ACCESS FULL | TBLCHK | 1 | 36663 | 0 |00:00:00.01 | 487 | 12 | | | |
| 3 | TABLE ACCESS FULL | TBLCHK | 0 | 220K| 0 |00:00:00.01 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TBL2"."ID"="TBL1"."ID")
2 - filter("TBL2"."ID_VAL"='ORACLE')
no records, let us check the 10046 trace to dig further
PARSING IN CURSOR #140069498436864 len=112 dep=0 uid=143 oct=3 lid=143 tim=47991926417 hv=368497245 ad='74031300'
sqlid='7w4sr0cazdnkx'
select * from TBLCHK tbl1 where EXISTS
(select 1 from tblchk tbl2 where tbl2.id=tbl1.id and tbl2.id_val='ORACLE')
END OF STMT
PARSE #140069498436864:c=37000,e=37341,p=0,cr=52,cu=0,mis=1,r=0,dep=0,og=1,plh=1090576670,tim=47991926414
EXEC #140069498436864:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1090576670,tim=47991926538
WAIT #140069498436864: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=47991926621
WAIT #140069498436864: nam='Disk file operations I/O' ela= 51 FileOperation=2 fileno=10 filetype=2 obj#=96213 tim=47991928267
WAIT #140069498436864: nam='db file sequential read' ela= 18 file#=10 block#=24289 blocks=1 obj#=96213 tim=47991928350
WAIT #140069498436864: nam='db file sequential read' ela= 10 file#=10 block#=24296 blocks=1 obj#=96213 tim=47991928573
WAIT #140069498436864: nam='db file sequential read' ela= 9 file#=10 block#=24298 blocks=1 obj#=96213 tim=47991928655
WAIT #140069498436864: nam='db file scattered read' ela= 15 file#=10 block#=24335 blocks=2 obj#=96213 tim=47991928977
WAIT #140069498436864: nam='db file sequential read' ela= 13 file#=10 block#=24375 blocks=1 obj#=96213 tim=47991930095
WAIT #140069498436864: nam='db file sequential read' ela= 57 file#=10 block#=24427 blocks=1 obj#=96213 tim=47991930962
WAIT #140069498436864: nam='db file sequential read' ela= 11 file#=10 block#=24429 blocks=1 obj#=96213 tim=47991931099
WAIT #140069498436864: nam='db file sequential read' ela= 10 file#=10 block#=24434 blocks=1 obj#=96213 tim=47991931216
WAIT #140069498436864: nam='db file sequential read' ela= 10 file#=10 block#=24438 blocks=1 obj#=96213 tim=47991931313
WAIT #140069498436864: nam='db file sequential read' ela= 16 file#=10 block#=24560 blocks=1 obj#=96213 tim=47991933732
WAIT #140069498436864: nam='db file sequential read' ela= 14 file#=10 block#=24687 blocks=1 obj#=96213 tim=47991935137
FETCH #140069498436864:c=9000,e=8564,p=12,cr=487,cu=0,mis=0,r=0,dep=0,og=1,plh=1090576670,tim=47991935230
STAT #140069498436864 id=1 cnt=0 pid=0 pos=1 obj=0 op='HASH JOIN RIGHT SEMI (cr=487 pr=12 pw=0 time=8566 us cost=207 size=2280096 card=190008)'
STAT #140069498436864 id=2 cnt=0 pid=1 pos=1 obj=96213 op='TABLE ACCESS FULL TBLCHK (cr=487 pr=12 pw=0 time=8425 us cost=103 size=219978 card=36663)'
STAT #140069498436864 id=3 cnt=0 pid=1 pos=2 obj=96213 op='TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=1320000 card=220000)'
*** 2015-05-28 13:29:31.638
WAIT #140069498436864: nam='SQL*Net message from client' ela= 18769385 driver id=1413697536 #bytes=1 p3=0 obj#=96213 tim=48010704804
CLOSE #140069498436864:c=0,e=16,dep=0,type=0,tim=48010704946
TkProf
select * from TBLCHK tbl1 where EXISTS
(select 1 from tblchk tbl2 where tbl2.id=tbl1.id and tbl2.id_val='ORACLE')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 52 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 12 487 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 12 539 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 143
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 HASH JOIN RIGHT SEMI (cr=487 pr=12 pw=0 time=8566 us cost=207 size=2280096 card=190008)
0 0 0 TABLE ACCESS FULL TBLCHK (cr=487 pr=12 pw=0 time=8425 us cost=103 size=219978 card=36663)
0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=1320000 card=220000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 10 0.00 0.00
db file scattered read 1 0.00 0.00
SQL*Net message from client 1 18.76 18.76
hmm After all these waits events, it has figured that my data is not there. Instead of this can i have something similar to an ledger, which could be used to cross check and validate before even moving on to next step.
After creating a ledger to hold the values take a look at the 10046 and TKProf below -- ledger :) scroll down to know more
PARSING IN CURSOR #140503816846712 len=112 dep=0 uid=143 oct=3 lid=143 tim=47001392270 hv=2403129567 ad='79a60d48' sqlid='a8sqfsu7mtq6z'
select * from tblchk tbl1 where exists
(select 1 from tblchk tbl2 where tbl1.id=tbl2.id and tbl2.id_val='ORACLE')
END OF STMT
PARSE #140503816846712:c=30000,e=30955,p=0,cr=58,cu=0,mis=1,r=0,dep=0,og=1,plh=2472364079,tim=47001392254
EXEC #140503816846712:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2472364079,tim=47001392773
WAIT #140503816846712: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=653 tim=47001393166
FETCH #140503816846712:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2472364079,tim=47001393231
STAT #140503816846712 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=10 us)'
STAT #140503816846712 id=2 cnt=0 pid=1 pos=1 obj=0 op='HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us cost=207 size=2280096 card=190008)'
STAT #140503816846712 id=3 cnt=0 pid=2 pos=1 obj=96213 op='TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=219978 card=36663)'
STAT #140503816846712 id=4 cnt=0 pid=2 pos=2 obj=96213 op='TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=1320000 card=220000)'
*** 2015-05-28 13:13:10.959
WAIT #140503816846712: nam='SQL*Net message from client' ela= 28632273 driver id=1413697536 #bytes=1 p3=0 obj#=653 tim=47030025871
CLOSE #140503816846712:c=0,e=17,dep=0,type=0,tim=47030025999
No db file sequential read wow And the TKProf looks like below -
select * from tblchk tbl1 where exists
(select 1 from tblchk tbl2 where tbl1.id=tbl2.id and tbl2.id_val='ORACLE')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 143
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 FILTER (cr=0 pr=0 pw=0 time=10 us)
0 0 0 HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us cost=207 size=2280096 card=190008)
0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=219978 card=36663)
0 0 0 TABLE ACCESS FULL TBLCHK (cr=0 pr=0 pw=0 time=0 us cost=103 size=1320000 card=220000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 28.63 28.63
So what is this Ledger - How could Oracle do this, with out even going to your data it would say you don't have it :)- it because of this
SQL> select distinct id_val from tblchk;
ID_VAL
-----------
A
B
C
Since i know for sure am not going to hold ORACLE in id_val column , check constraint on ID_VAL column is added
alter table tblchk add CONSTRAINT check_idval CHECK (id_val in ('A','B','C'));
Our Ledger = Check Constraint which directed the optimizer in doing this. Though this looks soo cheap tactic, Isn't this helping me. Why should i do all the scan to just say you don't have it, when am pretty sure that i don't have it though.
