| Index Monitoring [message #161246] |
Thu, 02 March 2006 16:25  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi All
I joined a new project, the scene here is
what i have noticed that almost all the column have index,
in short there are too many indexes when i asked developers they say don't know
previous DBA was handling, My question
i want to monitor all index in development instance,
I know for 1 index we use
alter index indexname monitoring usage;
Then query V$object_usage for coulumn used,
My plan is to enable monitoring for all the indexes in Developement and run many queries and reports and inserts and perform many DML operations
and then watch the usage,
How can start monitoring for all the indexes for a Schema, whether they are being used or not
and if this is successful we will implement it in Production.
Second Question can we partition a table online , i.e users are performing transactions., what are the steps to be taken and thing in consideration, because we have table of sizes 4-6 GB planning to do partition , it is an OLTP application.
Thanks
[Updated on: Thu, 02 March 2006 16:35] Report message to a moderator |
|
|
|
| Re: Index Monitoring [message #161259 is a reply to message #161246] |
Thu, 02 March 2006 20:33  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You can generate the SQL by selecting from user_indexes:
set pages 0 feedback off
spool mon.sql
select 'ALTER INDEX ' || index_name || ' monitoring usage;'
from user_indexes;
spool off
@mon
You can rebuild a table online, it's just not very pretty. Take a look at the DBMS_REDEFINITION package.
As a side note, it is not unusual in a ROLAP datamart (users developing their own queries and running them live) to have the larger denormalised dimension tables heavily indexed as they cannot predict which columns a user will query on.
_____________
Ross Leishman
|
|
|
|
| Re: Index Monitoring [message #161371 is a reply to message #161259] |
Fri, 03 March 2006 10:07 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Ross for responding,
Can any one share the experience of paritioning a table with around 18 millions records , we want to try first on Development and then on Production,
Ross i have gone through DBMS_Redefination package, Can you share any live example please.
My target is to impelement partitioning for a huge table, what are the things to be taken into consideration.
Thanks
|
|
|
|
| Re: Index Monitoring [message #161443 is a reply to message #161371] |
Sat, 04 March 2006 03:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
For 18 million rows, you don't need DBMS_REDEFINITION. You should be able to rebuild the table in less than an hour, so just get an outage.
The best way is also the easiest: just create the table in one hit:
CREATE TABLE part_tab
PARTITION BY RANGE (col1)
(PARTITION P200501
VALUES LESS THAN (to_date('20050201','YYYYMMDD'))
TABLESPACE T200501
,PARTTIION P200502
....
)
AS
SELECT * FROM big_tab
Then create indexes when you're done.
_____________
Ross Leishman
|
|
|
|
| Re: Index Monitoring [message #161863 is a reply to message #161246] |
Tue, 07 March 2006 09:55 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
When i run the statement for all the index monitoring it gave error.
ALTER INDEX SYS_IL0000049223C00003$$ monitoring usage
*
ERROR at line 1:
ORA-22864: cannot ALTER or DROP LOB indexes
ALTER INDEX CRDT_IDX monitoring usage;
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Do we need to run stats for Index also when we start monitoring the indexes, If yes How
After starting the index monitoring ,we have to run the queries, some DML and other SQL of the application, Correct ?.
Then see the coulmn USED in V$Object_usage,
If i keep monitoring for say 3 days is it enough to decide the whether index is used or not.
Suppose if the coulmn says that index is not used then can we come to conclusion that this index is not being used ,so we can dropped or Please tell is there any other step to find out the index to be dropped which are not in use, Becuase i have seen too many indexs at my new place.
When we analyze schema do indexes also get analyzed or not.
Thanks
[Updated on: Tue, 07 March 2006 17:01] Report message to a moderator |
|
|
|
| Re: Index Monitoring [message #162288 is a reply to message #161863] |
Thu, 09 March 2006 10:07 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
When can we decide whether the index can be dropped,
After starting to monitor Index,
I am looking the column USED in V$OBJECT_USAGE for 2-3 days results are not good, it is showing NO for more than 75% indexes.
Does that mean the indexes are not used,
Does that mean these indexes can be dropped, ?
Data in table V$OBJECT_USAGE is it session specific, if i disconnect a session and logon again next day does data changes in V$object_usage.
Please any help on this index issues,As i started with my first question there are indexes more than tables for 185 tables 498 indexes are there, I don't know why, as i joined new.
Thanks
[Updated on: Thu, 09 March 2006 15:40] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
