Printable View
Hi,
In our production database, we've two index tablespaces. These are continuously increasing (about 300 mb per day). Some days before i've added some more datafiles to these tabelspace. But it is not possible to add space everytime. So what should be the reason behind it?How can I analyze this? How do I proceed to solve this issue?
Thanks in advance.
Enable index usage monitoring and drop the unused indexes to recover the space.
I dont think you can do anything else as index consumes data as your loading the data into base tables. Either not to load the data or drop the indexes to avoid space usage
have you cheked which indexes are growing most?
have you cheked how the tables realted to these indexes are growing?
are these table growing as exepcted?
did you make any estimation how the tables and indexes are going to grow over the time?
You'll just have to stop those pesky users from using the database...Quote:
Originally Posted by minal_yawale
Take tree dump of the index that you suspected growing fast and post the same here.
Tamil
the first thing shoud be checking if the table is growing. taking dumps is rather severe for a beginner like him
Thanks for ur suggetions.
I've started analyzing the indexes.
Then fired this query:
SELECT name,height,lf_rows,del_lf_rows, USED_SPACE
FROM INDEX_STATS;
It shows following output (for one indx)
NAME HEIGHT LF_ROWS DEL_LF_ROWS USED_SPACE
---------------------------------------------
PK_ORDER_ID 1 22 0 570 and total space of that indx is 10 MB and used space =570, pct_used=8,LF_BLK_LEN=8000,BTREE_SPACE=8000
What does it mean ? Used space is in bytes right ?
Also I've tried this query
alter index PK_ORDER_ID monitoring usage
select index_name,used from v$object_usage where index_name='PK_ORDER_ID'
But it shows nothing.
I am not able to understand what to do from this result. Can u pls guide me about this and tell steps how to proceed further ?
Thanks for your valuable time.
Can you answer the following question?
Does the table data grow with respect to the index growth?
Tamil
Yes, that table data also growing with respect to indexes.
so whats the problem, data id going into your tables and the indexes are growing?
Increasing the table data but not in preportion to indx tablespace. Free space in Indx tablespace is decreasing everyday by 400 Mb.
But now to free that index tablespace? Is there any way to find the data from the index tablespace before 2 years and delete the same.
As i am first time facing this issue not able to understand how to manage the space of indx tablespace.
Can u please help me to solve this issue?
Thanks in advance.
the index is as big as it needs to be, you cant avoid that
yuo could try rebuilding them and watch them grow to that size again though
Increasing the table data but not in preportion to indx tablespace. Free space in Indx tablespace is decreasing everyday by 400 Mb.
But now to free that index tablespace? Is there any way to find the data from the index tablespace before 2 years and delete the same.
As i am first time facing this issue not able to understand how to manage the space of indx tablespace.
Can u please help me to solve this issue?
how to taketree dump of the index that growing fast?
Thanks in advance.
The rate of growth of your indexes will be mostly determined by their size (width of indexed columns) and their number. If you have a lot of both or either then your indexes will take up a lot of space.
Can you post a DESCRIBE of the table and a list of the indexes and columns? (use the user_indexes view)
HI,
I've found out those indexes which r growing everyday.
There r 3 indexes of size 10 GB each which r growing.
Can I drop these indexes? Whether it will release space?
What will be the issues after dropping these?
Thanks in advance.
do you need the indexes? are they being used?
On our development database,
data part - 9 GB
index part - 13 GB.
Its not logical that index part is larger than data part.
So If i will do rebuilding the indexes whether it will increase space.
Suppose at the start i rebuild index of 1 GB ,does it make any issue?
because as per my knowledge rebuilding indexes requires free space.
And free space :
In data tablepace:5 GB
In index tablespace : 5 GB.
Let me know your comment on it as soon as possible.
Thanks in advance.
why is it no logical - happens a lot
if you insist on re-creating them (no idea why) - drop and re-create
I've rebuilded some of indexes in our development database.
After that i've checked the space status:
It shows:
FILE_NAME TOTAL_SPACE USED_SPACE FREE_SPACE
C:\ORACLE\ORADATA\INDX01.DBF14,088.75 9,208.63 4,880.13
D:\ORADATA\INDX02.DBF 5,240.00 4,624.00 615.94
All values in MB.
It shows that free space in indx01.dbf is 4880 MB and in indx02.dbf is 615 MB. but previously there was reverse case.
The free space in indx01.dbf is 615 MB and in indx02.dbf is 4880 Mb. Why this happened?
Then i tried to alter datafile indx01.dbf resize 11000 MB,it shows that used space specified is beyond the used space in datafile.
Can u please tell me what is the reason behind it?
Thanks in advance.
it will put it extents where it wants them.
so overall you have gained no space but wasted time rebuilding. Give up, the indexes are at the size they want to be
Do you delete larege amounts of data from these tables, if not then you will hardly reclaim any space.
As for reducing the datafile size you need to find which extents are at the end of the datafile you can then rebuild them so it will move the extents
Use the script below:
SET VERIFY OFF
PROMPT This database contains the following datafiles:
SELECT file_name FROM dba_data_files;
ACCEPT fname PROMPT 'Specify a datafile whose contents are to be determined: '
TTITLE CENTER 'Database Segments in File &fname' SKIP 2
COLUMN stype FORMAT A15 JUSTIFY C HEADING 'Segment Type
COLUMN sown FORMAT A15 JUSTIFY C HEADING 'Segment Owner'
COLUMN sname FORMAT A40 JUSTIFY C HEADING 'Segment Name'
BREAK ON stype SKIP 1 ON sown SKIP 1
SELECT e.segment_type stype,
e.owner sown,
e.segment_name sname
FROM dba_extents e,
dba_data_files f
WHERE f.file_name = '&fname'
AND e.file_id = f.file_id
ORDER BY e.segment_type, e.owner, e.segment_name;
TTITLE OFF
CLEAR BREAKS
UNDEFINE fname
HTH
hi,
Yesterday on our testing database we deleted the data from table about 10 GB. After that free space in data tablespace got increased . Then i rebuilded the indexes to remove the deleted entries from indx tablespace. but i want free space in indx tbs also.
We've taken backup of tht data so that i future if we require tht we can restore it.
Whether is it a good way to drop and recreate tht indexes so tht we get free space in indx tablespace?
Does it cause any loss?
Please guide me.
Thanks for your time.
loss of what?, rebuilding indexes (whther useful or not ) wont lose data
This is an extract from another forum "Ask Tom", so thanks Tom.
-------------------------------------------------------------
So, what do you think was happening here? Well, the fact is that indexes, like
people, have a certain "weight" they like to be at. Some of us are chubby --
some skinny -- some tall -- some short. Sure, we can go on a diet -- but we
tend to gravitate BACK to the weight we were. The same is true for indexes --
what happened to this person is their index wanted to be wide and fat and EVERY
MONTH they rebuilt it (put it on a diet). It would spend the first half of the
month then getting fat again and generating gobs of redo due to the block splits
it was undergoing to get there.
In this case, rebuilding the index on their system had these effects:
o the system would generate 4.5 times the redo
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load
until the system got back to where the system actually wanted to be. And then
-- AND THEN -- they (the dba's) would do it all over again!!!! They would
destroy the equilibrium that the system worked so hard to get to. Bravo!!
(can you tell what my opinion is on regularly scheduled index rebuilds??? I
despise them).
If you are suffering from fragmentation -- your problem is not the index
rebuilds. It would be because you are using DICTIONARY managed tablespaces.
Switch over to locally managed tablespaces and you'll never have fragmentation
again. So, my suggestion -- one last index rebuild:
o create a locally managed tablespace
o alter indexrebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );
and then forget about them. You will rebuild an index in response to IDENTIFIED
and DEGRADED performance -- period. You will not rebulid indexes based on a
schedule. Period.
It is the RARE index that needs to be rebuilt.
It is not a rule that indexes need to be rebuilt.
--------------------------------------------
Are you using bitmap indexes.
You can try rebuild your large indexes once a week
No, i've b-tree index , If i drop and recreate those indexes whether it will create space in the tablespace also on disk.
consider this as first option.Quote:
Originally Posted by robertbalmer
is this a composite/bitmap?
Yesterday i fired following qury to rebuild index:
alter index index_name rebuild ONLINE
Then i got these errors:
ERROR at line 1:
ORA—01652: unable to extend tenp segnent by 256 in tablespace TEMP
ORA—27059: skqfrsz: could not reduce file size
OSD—O4005: SetrilePointer() failure, unable to read fron file
0/S—Error: (05 112) There is not enough space on the disk.
My temporay tablespace is 2 GB and free space in index tablespace is 40 GB and the index is of 10 GB.
But free space on the drive where temp01.dbf is reside is 760 kb . but before tht i've removed 4 GB of datafiles to another drive. Still its showing 760 KB. Why it is so? I will add tempfile to tht temp tablespace to anothr drive and will try again.
Now if i give command
alter index index_name rebuild unrecoverable tablespace indx, then whether it will give me same error?thanks in advance.
do you not have a dba around who can help you?
NO there is no other DBA to help me ,i've to do it myself
1) buy a new disk
2) increase your temp tablespace
3) rebuild you invalid indexes
4) STOP TO REBUILD YOUR INDEXES
5) STOP TO REBUILD YOUR INDEXES
6) STOP TO REBUILD YOUR INDEXES
7) STOP TO REBUILD YOUR INDEXES
8) STOP TO REBUILD YOUR INDEXES
9) STOP TO REBUILD YOUR INDEXES
10) go to point 4
I've added space to temp tablespace but still its giving the same error i.e. unable to extend in temp and thr is not enough space on disk.
Can u pls tell me how much free space i require in temp tablespace to rebuild index of 10 GB?
Thanks in advance.
Whats the count of ur table table again? :confused: As far as my knowledge is concerned is it better and faster to drop and re-create the index than rebuilding it. The existing temp will just be enough.
It seems to me like it's a sizing issue and the data growth is not correctly reflected in your database sizing document. So You better modify the sizing document and ask for the extra disks. Temporary fix drop the unused indexes after index monitoring.