Home » RDBMS Server » Server Administration » base line for new object
base line for new object [message #346240] Sun, 07 September 2008 21:55 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
We have oracle 10g database running in Unix. Is there anyway i can keep a base line of existing indexes within the database because sometimes creating a new index or dropping an existing index creates big performance problem. In order to debug quickly, it will be helpful if i can have a baseline and compare with it if there is any difference whenever there is some performance problem.
Thanks

Report message to a moderator

Re: base line for new object [message #346241 is a reply to message #346240] Sun, 07 September 2008 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is there anyway i can keep a base line of existing indexes
What does this mean?
Which details do you require as part of this "baseline"; which are not contained in DBA_INDEXES?

>sometimes creating a new index or dropping an existing index creates big performance problem.
I suggest that you cease such reckless behavior unless or until you have successfully tested on a development system.
If you stop causing such performance problems, then you having nothing which requires fixing.

Report message to a moderator

Re: base line for new object [message #347431 is a reply to message #346241] Thu, 11 September 2008 11:28 Go to previous message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
create table baseline_indexes as
select a.owner, a.index_name, a.table_owner, a.table_name, b.column_name, b.column_position from dba_indexes a, dba_ind_columns b
where a.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'PERFSTAT')
and a.owner = b.index_owner
and a.index_name = b.index_name
and a.table_owner = b.table_owner
and a.table_name = b.table_name;

and write a script to run everyday to compare the same results against this table. minus clause will do.

regards
--Mak

Report message to a moderator

Previous Topic: Database upgradation
Next Topic: difference between db_name and instance_name
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 08 00:07:31 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.