Re: Determine process of index build - HOW
George,
I'm not going to answer your question (since it already has been), but
here are some ways to speed up the index builds:
1. ALTER SESSION SET SORT_AREA_SIZE=1;
As long as you have 100 MB of free physical memory on the machine,
doing this before the index build will speed up the sort operations that
are a part of the index build. I've even used higher numbers.
2. Add the PARALLEL clause in the index build statement. If your table
is partitioned, then the PARALLEL clause will use the number of
partitions as the degree of parallelism, though you can change that if
you want.
3. Add the NOLOGGING clause in the index. After all, if the build
fails, you're going to run it again not try to recover right?
Using these things together has made a huge improvement. One index
build was running for over 8 hours. After changing these things (using
500MB for the sort area size since the machine had the available RAM)
and the build ran in 10 minutes! This was on a table with over 30
million rows.
Good luck.
Stephen
>>> [EMAIL PROTECTED] 09/28/02 05:48AM >>>
Hi there
How can I see how far my index build has is.
I got a table with 43 mil records. It is taking forever as expected
but
would like to see how far it is,
Any idea.
Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K
George
George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web: http://www.didata.co.za
You Have The Obligation to Inform One Honestly of the risk, And As a
Person
You Are Committed to Educate Yourself to the Total Risk In Any
Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
or Injure Themselves as They See Fit!
***
This message contains information intended solely for the addressee,
which is confidential or private in nature and subject to legal
privilege.
If you are not the intended recipient, you may not peruse, use,
disseminate, distribute or copy this message or any file attached to
this
message. Any such unauthorised use is prohibited and may be unlawful.
If
you have received this message in error, please notify the sender
immediately by e-mail, facsimile or telephone and thereafter delete
the
original message from your machine.
Furthermore, the information contained in this message, and any
attachments thereto, is for information purposes only and may contain
the
personal views and opinions of the author, which are not necessarily
the
views and opinions of Dimension Data (South Africa) (Proprietary)
Limited
or its subsidiaries and associated companies ("Dimension Data").
Dimension
Data therefore does not accept liability for any claims, loss or
damages
of whatsoever nature, arising as a result of the reliance on such
information by anyone.
Whilst all reasonable steps are taken to ensure the accuracy and
integrity of information transmitted electronically and to preserve
the
confidentiality thereof, Dimension Data accepts no liability or
responsibility whatsoever if information or data is, for whatsoever
reason, incorrect, corrupted or does not reach its intended
destination.
*
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephen Andert
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW And if you have OEM/DBA Studio, you can watch the progress graphically.. :) - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:38 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Determine process of index build - HOW v$session_longops select with last_update_time desc and use the sid ... it is pretty cool .. it will also tell you for "current operation" how many seconds are remaining. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW
This is a query I build to see this, midnight Saturday
Comment welcome
column "% Done" format 999.99
column opname format a15
column sql_text format a70
column "T Left" format 9
select
a.sid,
(a.sofar/a.totalwork)*100 "% Done",
to_char(a.last_update_time, 'HH24:MI:SS'),
a.username,
a.time_remaining "T Left",
a.opname ,
s.sql_text
from v$session_longops a, v$session
b, v$sqltext s
where a.sid =b.sid
and b.sql_address = s.address
and a.sofar <> a.totalwork
order by b.sid, last_update_time
/
George
George
Leonard
Oracle Database
Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575
0573
Fax: (+27 11) 576
0573
E-mail:[EMAIL PROTECTED]
Web: http://www.didata.co.za
You Have The Obligation
to Inform One Honestly of the risk, And As a Person
You Are Committed to
Educate Yourself to the Total Risk In Any Activity!
Once Informed &
Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure
Themselves as They See Fit!
-Original Message-
From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]
Sent: 30 September 2002 14:38 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Determine process of
index build - HOW
v$session_longops select with last_update_time
desc and use the sid ... it is pretty cool .. it will also tell you for
"current operation" how many seconds are remaining.
Raj
__
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
having an opinion is an art!
***
This message contains information intended solely for the addressee,
which is confidential or private in nature and subject to legal privilege.
If you are not the intended recipient, you may not peruse, use,
disseminate, distribute or copy this message or any file attached to this
message. Any such unauthorised use is prohibited and may be unlawful. If
you have received this message in error, please notify the sender
immediately by e-mail, facsimile or telephone and thereafter delete the
original message from your machine.
Furthermore, the information contained in this message, and any
attachments thereto, is for information purposes only and may contain the
personal views and opinions of the author, which are not necessarily the
views and opinions of Dimension Data (South Africa) (Proprietary) Limited
or its subsidiaries and associated companies ("Dimension Data"). Dimension
Data therefore does not accept liability for any claims, loss or damages
of whatsoever nature, arising as a result of the reliance on such
information by anyone.
Whilst all reasonable steps are taken to ensure the accuracy and
integrity of information transmitted electronically and to preserve the
confidentiality thereof, Dimension Data accepts no liability or
responsibility whatsoever if information or data is, for whatsoever
reason, incorrect, corrupted or does not reach its intended destination.
*
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW v$session_longops select with last_update_time desc and use the sid ... it is pretty cool .. it will also tell you for "current operation" how many seconds are remaining. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW George, Doing some big index rebuilding myself on this fine weekend. If you know about how big the index will be and the tablespace, you can see how large the temporary segment is at the moment. That can give you a rough idea of how far along things are. It usually takes quite a while for the first extent then things get moving faster. select segment_name, segment_type, round(bytes/1024/1024,2) Mb , initial_extent, next_extent, extents , Owner, max_extents from dba_segments Where segment_type = 'TEMPORARY' and tablespace_name= 'HISTORYC'; Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: George Leonard (ZA) [SMTP:[EMAIL PROTECTED]] Hi there How can I see how far my index build has is. I got a table with 43 mil records. It is taking forever as expected but would like to see how far it is, Any idea. Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za
Determine process of index build - HOW
Hi there
How can I see how far my index build has is.
I got a table with 43 mil records. It is taking forever as expected but
would like to see how far it is,
Any idea.
Oracle 8.1.6.3 (32Bit) on Solaris 2.6 on Sun E10K
George
George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web: http://www.didata.co.za
You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill
or Injure Themselves as They See Fit!
***
This message contains information intended solely for the addressee,
which is confidential or private in nature and subject to legal privilege.
If you are not the intended recipient, you may not peruse, use,
disseminate, distribute or copy this message or any file attached to this
message. Any such unauthorised use is prohibited and may be unlawful. If
you have received this message in error, please notify the sender
immediately by e-mail, facsimile or telephone and thereafter delete the
original message from your machine.
Furthermore, the information contained in this message, and any
attachments thereto, is for information purposes only and may contain the
personal views and opinions of the author, which are not necessarily the
views and opinions of Dimension Data (South Africa) (Proprietary) Limited
or its subsidiaries and associated companies ("Dimension Data"). Dimension
Data therefore does not accept liability for any claims, loss or damages
of whatsoever nature, arising as a result of the reliance on such
information by anyone.
Whilst all reasonable steps are taken to ensure the accuracy and
integrity of information transmitted electronically and to preserve the
confidentiality thereof, Dimension Data accepts no liability or
responsibility whatsoever if information or data is, for whatsoever
reason, incorrect, corrupted or does not reach its intended destination.
*
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: George Leonard (ZA)
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
