Links
• Home
• Oracle DBA Forum
Frequent Oracle Errors
•TNS:could not resolve the connect identifier specified
•Backtrace message unwound by exceptions
•invalid identifier
•PL/SQL compilation error
•internal error
•missing expression
•table or view does not exist
•end-of-file on communication channel
•TNS:listener unknown in connect descriptor
•insufficient privileges
•PL/SQL: numeric or value error string
•TNS:protocol adapter error
•ORACLE not available
•target host or object does not exist
•invalid number
•unable to allocate string bytes of shared memory
•resource busy and acquire with NOWAIT specified
•error occurred at recursive SQL level string
•ORACLE initialization or shutdown in progress
•archiver error. Connect internal only, until freed
•snapshot too old
•unable to extend temp segment by string in tablespace
•Credential retrieval failed
•missing or invalid option
•invalid username/password; logon denied
•unable to create INITIAL extent for segment
•out of process memory when trying to allocate string bytes
•shared memory realm does not exist
•cannot insert NULL
•TNS:unable to connect to destination
•remote database not found'>ora-02019
•exception encountered: core dump
•inconsistent datatypes
•no data found
•TNS:operation timed out
•PL/SQL: could not find program
•existing state of packages has been discarded
•maximum number of processes exceeded
•error signaled in parallel query server
•ORACLE instance terminated. Disconnection forced
•TNS:packet writer failure
•see ORA-12699
•missing right parenthesis
•name is already used by an existing object
•cannot identify/lock data file
•invalid file operation
•quoted string not properly terminated
-none-

-none-

2005-01-08       - By -not available-

Reply:     <<     241     242     243     244     245     246     247     248     249     250     >>  

"dml lock allocation
This latch protects the list of State Objects (dml locks). Every time a
transaction modifies a table, a DML lock is gotten and released when the
change is committed. The number of State Objects for dml locks is
determined by
the init.ora <Parameter:DML_LOCKS > ".

So I would be looking at how often you commit.
Also what sort of turn over are you getting on the online redo logs? It
may well be that they are too small.
You may want to investigate having multiple freelists for the table you
are inserting into, having many parallel
inserts (assuming you have enabled parallel DML), may well be causing a
lot of contention for the header block at
the start of the segment.

Good luck.

Pete

-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of eygle
Sent: 08 January 2005 13:56
To: oracle-l@(protected)
Subject: what 's mean of "enqueue hash chains " latch?


hello;

We have a Oracle 8.1.7.0.0 Database on Solaris8.
With a parallel insert , database slow down heavy suddently.

I find lots of latch wait in database,from statspack(with 15 minutes
elapse) list:

Pct Avg
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
-- ---- ---- ---- ---- ------ -- ---- ------ -- --- -- --- -- ---- ----
-- ---
active checkpoint queue latch 260 0.0 0
cache buffers chains 47,228,551 0.0 552
0.0
checkpoint queue latch 3,263 0.0 0
dml lock allocation 46,899,746 12.1 0.0 0
enqueue hash chains 46,899,333 39.2 0.0 0
enqueues 849 0.0 0
job_queue_processes parameter 12 0.0 0
ktm global data 2 0.0 0
library cache 32,739 0.0 0.0 0
library cache load lock 70 0.0 0
list of block allocation 36 0.0 0

and

Latch Sleep breakdown for DB: QCB Instance: qcb Snaps: 150 -151
- > ordered by misses desc

Get Spin
&
Latch Name Requests Misses Sleeps Sleeps
1- >4
-- ---- ---- ---- ---- --- -- ---- ------ -- ---- --- -- ---- ---
-- ---- ----
enqueue hash chains 46,899,333 18,367,985 6,312
18361675/630
8/2/0/0
dml lock allocation 46,899,746 5,678,834 1,228
5677606/1228
/0/0/0
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
Latch Miss Sources for DB: QCB Instance: qcb Snaps: 150 -151
- > only latches with sleeps are shown
- > ordered by name, sleeps desc

NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
-- ---- ---- ---- ------ -- ---- ---- ---- ---- --- -- ---- -- ---- --
-- ----
dml lock allocation ktaiam 0 615
444
dml lock allocation ktaidm 0 604
784
enqueue hash chains ksqrcl 0 3,778
3,453
enqueue hash chains ksqgtl3 0 2,472
2,832
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---

I can not find more info about "enqueue hash chains ".
What ' s it mean and how to reduce it ?

The SQL of parallel insert is :

insert /*+ append parallel(fc_costgatherresult_m,4) */ into=20
fc_costgatherresult_m
(kjnd,kjqj,pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,ac
countcurrtype,producttype,pk_costitem,originaldwbm,originaldept,calmny,p
k_client,clienttype,pk_credittype)
select /*+ parallel(fc_costcalresult_m,4) */
'2004 ', '11 ',pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,a
ccountcurrtype,producttype,pk_costitem,originaldwbm,originaldept,sum(cal
mny),pk_client,clienttype,pk_credittype
from fc_costcalresult_m where pk_dwbm =3D originaldwbm
group by
pk_dwbm,pk_deptdoc,pk_psndoc,isproduct,pk_productorbalance,accountcurrty
pe,producttype,pk_costitem,originaldwbm,originaldept,pk_client,clienttyp
e,pk_credittype

All the table is partition table.
Table fc_costcalresult_m with nearly 40G data.

And we have the parameter:
dml_locks =3D 2000
enqueue_resources =3D 2200

I also want to know why "dml lock allocation " Requests is so high?

Any suggestion is appreciate.


-- ---- ---- ---- ---- ---- ---- ---- --
eygle from China.
my site: http://www.eygle.com
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l