Re: high latch free

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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l