all,
both tables are partitioned, with key:
EVENT_T PARTITION BY RANGE ("POID_ID0")
EVENT_SESSION_IPT_CALL_T PARTITION BY RANGE ("OBJ_ID0")
but I_EVENT_IPT_CALL__TRANSID index only 1 column index trans_id.
I guess because of that index, so we could have problem with latch free
event, or any other clues?
how if I create index I_EVENT_IPT_CALL__TRANSID index for 2 column ( trans_id
and OBJ_ID0)
or should I increase parallel* parameter ?
thanks b4 for ur help
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PARSING IN CURSOR #1 len=501 dep=0 uid=27 oct=3 lid=27 tim=3983522707959
hv=1849606656 ad='f7daf0c0'
.............................
where event_session_ipt_call_t.trans_id = :1 and event_v.poid_id0 =
event_session_ipt_call_t.obj_id0
END OF STMT
PARSE #1:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3983522707949
BINDS #1:
bind 0: dty=1 mxl=128(35) mal=00 scl=00 pre=00 oacflg=00 oacfl2=10
size=128 offset=0
bfp=ffffffff7cd64210 bln=128 avl=35 flg=05
value="94C5D870 F24A11DB A1EBA229 DEC3BCFE"
WAIT #1: nam='PX Deq: Join ACK' ela= 1000 p1=268500992 p2=1 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 1 p1=268500997 p2=1 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 197 p1=268500997 p2=2 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 80 p1=268500997 p2=3 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 136 p1=268500997 p2=4 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 41 p1=268501000 p2=1 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 7 p1=268501004 p2=1 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 73 p1=268501004 p2=2 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 197 p1=268501005 p2=1 p3=0
WAIT #1: nam='PX Deq: Join ACK' ela= 193 p1=268501005 p2=2 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 1152 p1=268500992 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 601 p1=268500998 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 408 p1=268500998 p2=2 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 2 p1=268500998 p2=3 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 8 p1=268500998 p2=4 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 228 p1=268500998 p2=5 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 131 p1=268501001 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 1 p1=268501005 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 2 p1=268501005 p2=2 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 56 p1=268501005 p2=3 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 68 p1=268501005 p2=4 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 1 p1=268501005 p2=5 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 68 p1=268501005 p2=6 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 1 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Parse Reply' ela= 208 p1=200 p2=2 p3=0
EXEC #1:c=0,e=14291,p=0,cr=116,cu=0,mis=0,r=0,dep=0,og=4,tim=3983522722487
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 2 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 2 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 1 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 1 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 1 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 1 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Execute Reply' ela= 72 p1=200 p2=2 p3=0
WAIT #1: nam='PX Deq: Table Q Normal' ela= 2 p1=200 p2=1 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 2 p1=0 p2=1 p3=0
WAIT #1: nam='PX Deq: Signal ACK' ela= 0 p1=0 p2=1 p3=0
FETCH
#1:c=10000,e=3442,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=3983522726146
WAIT #1: nam='latch free' ela= 1366 p1=15032416248 p2=4 p3=0
WAIT #1: nam='latch free' ela= 147 p1=15032416248 p2=4 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 1824 p1=1413697536 p2=1
p3=0
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=49901 op='TABLE ACCESS FULL
CONFIG_CREDIT_TERMCAUSES_T '
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 4 | 780
| 116 | | | | | |
| 1 | NESTED LOOPS
| | 4 | 780 | 116 | | |
66,00 | P->S | QC (RAND) |
| 2 | PARTITION RANGE ALL
| | | | | 1 | 49 |
66,00 | PCWP | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_SESSION_IPT_CALL_T
| 4 | 176 | 104 | 1 | 49 | 66,00 | PCWP | |
|* 4 | INDEX RANGE SCAN |
I_EVENT_IPT_CALL__TRANSID | 4 | | 99 | 1 | 49 |
66,00 | PCWP | |
| 5 | PARTITION RANGE
ITERATOR | | | | |
KEY | KEY | 66,00 | PCWP | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID|
EVENT_T | 1 | 151 | 3 | KEY | KEY |
66,00 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 7 | INDEX UNIQUE SCAN | IN_I_EVENT__ID
| 1 | | 2 | KEY | KEY | 66,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EVENT_SESSION_IPT_CALL_T"."TRANS_ID"='23D2547E F24A11DB
8CB4C324 E1FE7D4F')
7 - access("EVENT_V"."POID_ID0"="EVENT_SESSION_IPT_CALL_T"."OBJ_ID0")
Note: cpu costing is off
--
regards
ujang
