oh ya... additional info event_session_ipt_call_t = 23,720,678 rows event_t = 29,136,220 rows regards ujang On 5/1/07, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx> wrote:
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