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 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
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 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 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
