Hi, DBA's
Could you tell me whether there is any opportunity to calculate approximately the time of rebuilding index or not?
I already wrote that I want to rebuild partitioned index, but there is 24 partitions, each partition contains 22 million rows. Because as far as I understand it will be a huge loading on CPU during this operation. And it correspondingly causes performance degradation of the prodaction database.
if such opportunity exists, how can I do it.
Thanks in advance.
Best regards, Igor.
Full Version: How can be calculated the time of rebuilding index?
QUOTE (x-stalker @ Jul 29 2008, 02:03 PM) 
Hi, DBA's
Could you tell me whether there is any opportunity to calculate approximately the time of rebuilding index or not?
I already wrote that I want to rebuild partitioned index, but there is 24 partitions, each partition contains 22 million rows. Because as far as I understand it will be a huge loading on CPU during this operation. And it correspondingly causes performance degradation of the prodaction database.
if such opportunity exists, how can I do it.
Thanks in advance.
Best regards, Igor.
Could you tell me whether there is any opportunity to calculate approximately the time of rebuilding index or not?
I already wrote that I want to rebuild partitioned index, but there is 24 partitions, each partition contains 22 million rows. Because as far as I understand it will be a huge loading on CPU during this operation. And it correspondingly causes performance degradation of the prodaction database.
if such opportunity exists, how can I do it.
Thanks in advance.
Best regards, Igor.
>> calculate approximately the time of rebuilding index
there are many factors involved ... load on your system, cpu horse power you have , speed of you IO subsystem, parallism , logging etc etc.. and all these are local to you only... only you can tell how long this might take after having a test on it at some particular point in time.
>> calculate approximately the time of rebuilding index
Well, you can get a timing from sqlplus:
If you are nohupping them, you can just snap sysdate before and after.
***********************************************************************
>> as far as I understand it will be a huge loading on CPU during this operation.
No, index rebuilding is mostly disk I/O. The steps are to read the structure from the existing index, rebuild the nodes, and then rename.
To minimize time in a batch window, you want to saturate your I/O subsystem and that depends on how many disk spindles you have. If I have 10 disks, I can rebuild in 10 parallel streams.
You can use nohup to submit parallel streams
BTW you specify OPQ for the index rebuild.
How many CPU's do you have on the production server?
Well, you can get a timing from sqlplus:
CODE
set timing on
alter index fred rebuild tablespace newts;
alter index fred rebuild tablespace newts;
If you are nohupping them, you can just snap sysdate before and after.
***********************************************************************
>> as far as I understand it will be a huge loading on CPU during this operation.
No, index rebuilding is mostly disk I/O. The steps are to read the structure from the existing index, rebuild the nodes, and then rename.
To minimize time in a batch window, you want to saturate your I/O subsystem and that depends on how many disk spindles you have. If I have 10 disks, I can rebuild in 10 parallel streams.
You can use nohup to submit parallel streams
CODE
master_reorg.ksh
#!/bin/ksh
# Written by Donald Keith Burleson
# usage: nohup don_reorg.ksh > don_reorg.lst 2>&1 &
# Ensure that running user is oracle . . . . .
oracle_user=`whoami|grep oracle|grep -v grep|wc -l`;
oracle_num=`expr $oracle_user`
if [ $oracle_num -lt 1 ]
then echo "Current user is not oracle. Please su to oracle and retry."
exit
fi
# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
oracle_num=`expr $oracle_up`
if [ $oracle_num -lt 1 ]
then echo "ORACLE instance is NOT up. Please start Oracle and retry."
exit
fi
#************************************************************
# Submit parallel CTAS reorganizations of important tables
#************************************************************
nohup reorg.ksh CUSTOMER >customer.lst 2>&1 &
nohup reorg.ksh ORDER >order.lst 2>&1 &
nohup reorg.ksh ITEM >item.lst 2>&1 &
nohup reorg.ksh LINE_ITEM >line_item.lst 2>&1 &
#!/bin/ksh
# Written by Donald Keith Burleson
# usage: nohup don_reorg.ksh > don_reorg.lst 2>&1 &
# Ensure that running user is oracle . . . . .
oracle_user=`whoami|grep oracle|grep -v grep|wc -l`;
oracle_num=`expr $oracle_user`
if [ $oracle_num -lt 1 ]
then echo "Current user is not oracle. Please su to oracle and retry."
exit
fi
# Ensure that Oracle is running . . . . .
oracle_up=`ps -ef|grep pmon|grep -v grep|wc -l`;
oracle_num=`expr $oracle_up`
if [ $oracle_num -lt 1 ]
then echo "ORACLE instance is NOT up. Please start Oracle and retry."
exit
fi
#************************************************************
# Submit parallel CTAS reorganizations of important tables
#************************************************************
nohup reorg.ksh CUSTOMER >customer.lst 2>&1 &
nohup reorg.ksh ORDER >order.lst 2>&1 &
nohup reorg.ksh ITEM >item.lst 2>&1 &
nohup reorg.ksh LINE_ITEM >line_item.lst 2>&1 &
BTW you specify OPQ for the index rebuild.
How many CPU's do you have on the production server?
Why do you want to or need to rebuild the index? How does time factor in if you only need a few of the partitions to be current? That is, the active partitions are the ones being queried, older ones rarely are.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.