high latch free

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Tue, 1 May 2007 15:34:05 +0700
Message-ID: <3edcb66e0705010134m50fb0cb6x95a48ac61392571c@mail.gmail.com>

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 '

| 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

Received on Tue May 01 2007 - 03:34:05 CDT