Home » RDBMS Server » Performance Tuning » Index maintenance
Index maintenance [message #65231] Tue, 22 June 2004 22:37 Go to next message
Arindam Saha
Messages: 15
Registered: May 2004
Junior Member
My database version is 9.0.1 running on Windows NT 4.0. The concurrent no. of sessions are around 75 during peak business hours. I have configured 8 shared servers and maximum are 30.  The main problem of my database is the locking contention which is resulting performance bottleneck.  I assume the reason is improper indexing. The database encountered ORA-00600 with ORA-4030 error code quite frequently. I perform the workload analysis quite frequently using Oracle expert tuning utility of OEM and every time I get some recommendations for dropping and creating indexes. I execute the recommendations but not getting the optimal performance benefit. Can anybody tell me the right way to analyze the db workload and index maintenence please. The physical memory accessable by Oracle is 2GB. Shall I get better performance if the db is configured with 3GT feature.
Re: Index maintenance [message #65233 is a reply to message #65231] Wed, 23 June 2004 08:16 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Running in shared server mode shouldn't be necessary with only 75 concurrent users. It makes tracing really difficult, because a running sql_trace on a single user session typically results in having multiple trace files.

Anyway, I'd start by looking for unindexed FK columns. That's an easy fix if it's the problem.

From http://osi.oracle.com/~tkyte/unindex/index.html

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

-- AHM changed SQL to 'covered_by_indx_on_cols'

select decode( b.table_name, NULL, '****', 'ok' ) Status, 
	   a.table_name, a.columns, b.covered_by_indx_on_cols
( select substr(a.table_name,1,30) table_name, 
		 substr(a.constraint_name,1,30) constraint_name, 
	     max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
	     max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
	     max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) covered_by_indx_on_cols
    from user_ind_columns 
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.covered_by_indx_on_cols (+) like a.columns || '%'

-- quick and dirty query to to generate candidate indexes
-- for single column FKs
select 'create index '||uc.constraint_name ||' on '||
uc.table_name ||'('||column_name||') tablespace ???;'
from user_constraints uc , user_cons_columns ucc
where uc.table_name = ucc.TABLE_NAME
and uc.constraint_name = ucc.constraint_name
and constraint_type ='R';

Previous Topic: create table
Next Topic: Urgent tunning
Goto Forum:

Current Time: Wed Feb 21 19:35:20 CST 2024