Strange execution plan picking random index to do bitmap conv on.
[Sorry, also posted with the wrong subject title - RE: (un)intelligent agent] Hi all, Does anyone have an idea why the query is picking a random local bitmap index whose single column is not used in the query to do a conversion on? Regards, Adrian --- SELECT 1 FROM small_t bo_ WHERE exists ( select 1 from big_t where bo_.be_id = inv5) -- | Operation | Name Rows | Bytes| Cost | Pstart| Pstop | -- | SELECT STATEMENT |352 | 17K| 29 | | | | FILTER || || | | | TABLE ACCESS FULL |SMALL_T 352 | 17K| 29 | | | | PARTITION RANGE SINGLE || || KEY | KEY | |TABLE ACCESS BY LOCAL INDEX ROWID |BIG_T 250K|2M| 26561 | KEY | KEY | | BITMAP CONVERSION TO ROWIDS || || | | | BITMAP INDEX FULL SCAN |TRANSACTIONT_BIX9 | || KEY | KEY | -- Version 8.1.7.4.1, WinNT TABLETYPEINDEX_NAME NUM_ROWS BLVL DSTNCT_KYS CF COLUMN_NAME -- BIG_TBITMAP TRANSACTIONT_BIX9 24041302 1 2 1953 INV9 BIG_T is partitioned on INV5. There is no index on INV5. NAME TYPEVALUE --- -- object_cache_optimal_sizeinteger 102400 optimizer_features_enablestring 8.1.7 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 8 optimizer_mode string CHOOSE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE 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: (un)intelligent agent
Hi all, Does anyone have an idea why the query is picking a random local bitmap index whose single column is not used in the query to do a conversion on? Regards, Adrian --- SELECT 1 FROM small_t bo_ WHERE exists ( select 1 from big_t where bo_.be_id = inv5) -- | Operation | Name Rows | Bytes| Cost | Pstart| Pstop | -- | SELECT STATEMENT |352 | 17K| 29 | | | | FILTER || || | | | TABLE ACCESS FULL |SMALL_T 352 | 17K| 29 | | | | PARTITION RANGE SINGLE || || KEY | KEY | |TABLE ACCESS BY LOCAL INDEX ROWID |BIG_T 250K|2M| 26561 | KEY | KEY | | BITMAP CONVERSION TO ROWIDS || || | | | BITMAP INDEX FULL SCAN |TRANSACTIONT_BIX9 | || KEY | KEY | -- Version 8.1.7.4.1, WinNT TABLETYPEINDEX_NAME NUM_ROWS BLVL DSTNCT_KYS CF COLUMN_NAME -- BIG_TBITMAP TRANSACTIONT_BIX9 24041302 1 2 1953 INV9 BIG_T is partitioned on INV5. There is no index on INV5. NAME TYPEVALUE --- -- object_cache_optimal_sizeinteger 102400 optimizer_features_enablestring 8.1.7 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 8 optimizer_mode string CHOOSE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE 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: Strange execution plan picking random index to do bitmap conv on.
Sorry, its late on Friday and need beer... The bitmap index is partitioned in INV5. -Original Message- Sent: 07 November 2003 15:30 To: Multiple recipients of list ORACLE-L on. [Sorry, also posted with the wrong subject title - RE: (un)intelligent agent] Hi all, Does anyone have an idea why the query is picking a random local bitmap index whose single column is not used in the query to do a conversion on? Regards, Adrian --- SELECT 1 FROM small_t bo_ WHERE exists ( select 1 from big_t where bo_.be_id = inv5) -- | Operation | Name Rows | Bytes| Cost | Pstart| Pstop | -- | SELECT STATEMENT |352 | 17K| 29 | | | | FILTER || || | | | TABLE ACCESS FULL |SMALL_T 352 | 17K| 29 | | | | PARTITION RANGE SINGLE || || KEY | KEY | |TABLE ACCESS BY LOCAL INDEX ROWID |BIG_T 250K|2M| 26561 | KEY | KEY | | BITMAP CONVERSION TO ROWIDS || || | | | BITMAP INDEX FULL SCAN |TRANSACTIONT_BIX9 | || KEY | KEY | -- Version 8.1.7.4.1, WinNT TABLETYPEINDEX_NAME NUM_ROWS BLVL DSTNCT_KYS CF COLUMN_NAME -- BIG_TBITMAP TRANSACTIONT_BIX9 24041302 1 2 1953 INV9 BIG_T is partitioned on INV5. There is no index on INV5. NAME TYPEVALUE --- -- object_cache_optimal_sizeinteger 102400 optimizer_features_enablestring 8.1.7 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 8 optimizer_mode string CHOOSE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Turner, Adrian A SITI-ITPSIE 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: What is happening to OTN 9iRel2 page?
Try this... http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage -Original Message- Sent: 26 September 2003 02:20 To: Multiple recipients of list ORACLE-L http://www.oracle.com Bottom you have Technologies ; In that Click Technology network to get to http://otn.oracle.com/index.html Choose Documentation from one of the top icons. You should be in http://otn.oracle.com/documentation/index.html. Click on 9i Rel2 to get to http://otn.oracle.com/documentation/index.html Click on View Library to and YOU GET 8.1.7 library?? Click on List of books , choose SQL , ChooseSQL Reference You are back again to http://otn.oracle.com/documentation/index.html Where are we? GovindanK Message sent via Zuvio Mail Get your own FREE email account with SPAM and Antivirus protection! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Govindan K 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace I'm even more confused now. TEMP ran out of space at 20GB - I'm only inserting 12.2GB! So I kicked it off again with the hint just as /*+ APPEND */ and it completed successfully in 1/6th the time to failure of the parallel insert. -Original Message- From: Turner, Adrian A SITI-ITPSIE Sent: 16 September 2003 15:16 To: Multiple recipients of list ORACLE-L Subject: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
RE: Nosort parallel dml uses TEMP tablespace
Thanks for the input Praveen, if only it were that easy. The sql is a straight insert into tab1 select * from tab2 but with a parallel dml hint. Remove the parallel and it doesnt use TEMP. Theres no distinct, group by, order by or indexes on the target table (there are 12 or so on the source table). As I said I've been reading metalink notes like mad.. Note:50592.1 says Parallel Insert SYNTAX: ALTER session enable parallel dml; INSERT /*+ APPEND PARALLEL(d2 4) */ into d2 SELECT ...HOW IT WORKS: Each slave creates a TEMPORARY segment in target tablespace with INITIAL=NEXT from the tables storage clause. Each slave then populates its own segment in a similar manner to parallel SQLLOAD except that the rows are taken from the SELECT row source. On completion of the INSERT segments are MERGED one TEMPORARY segment with all trailing extents being trimmed - Note: we DO trim the temporary segment that we are merging into. So apparently Each slave creates a TEMPORARY segment in target tablespace but I am getting them appearing in the TEMP tablespace, not the tablespaces containing the partitions. And the volume of TEMP required does not compute either. BTW there are 27 partitions in the target table, which does not match the number parallel slaves Cheers, Adrian -Original Message- Sent: 17 September 2003 09:26 To: Turner, Adrian A SITI-ITPSIE Cc: [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L; [EMAIL PROTECTED] Adrian, Please see the 2nd point. This will always use Temporary tablespace even if you have some other tablespace for the table getting inserted. Operations Requiring Sorting Index creation Parallel insert operation involving index maintenance ORDER BY or GROUP BY clauses DISTINCT values selection UNION, INTERSECT, or MINUS operators Sort-merge joins ANALYZE command execution Please ignore this if you know it already. The Sort Process The Oracle server sorts in memory if the work can be done within an area smaller than the value (in bytes) of the parameter SORT_AREA_SIZE. If the sort needs more space than this value: 1 The data is split into smaller pieces, called sort runs; and each piece is sorted individually. 2 The server process writes pieces to temporary segments on disk; these segments hold intermediate sort runs data while the server works on another sort run. 3 The sorted pieces are merged to produce the final result. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes. Regards, Praveen __ Praveen Shetty Oracle DBA Technology Solutions, IS. National Grid Transco, Hinckley. Phone: 715-32703 (Int) ,+44-1455-892703 (Ext) mail 2:[EMAIL PROTECTED] __ Vohra Vishal 17/09/03 09:04 To: Pathania Birinder/PB251/Solihull/[EMAIL PROTECTED], Praveen Shetty/PKS02/Solihull/[EMAIL PROTECTED] cc: Subject: RE: Nosort parallel dml uses TEMP tablespace Gud One ! ! ! !! Regards, Vishal Vohra EBMS 715-32569 Mob:07952883716 - Forwarded by Vohra Vishal/VV011/Solihull/Transco on 17/09/03 09:09 - Turner, Adrian A SITI-ITPSIE [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ell.com cc: Sent by: Subject: RE: Nosort parallel dml uses TEMP tablespace [EMAIL PROTECTED
RE: Nosort parallel dml uses TEMP tablespace
Title: RE: Nosort parallel dml uses TEMP tablespace
Thanks
for having a look Tanel; that makes great sense.
I'll
have a quick play to convince myself but I'd say its a lesson
learned.
BTW
did you too experience more temp allocation than you would have
expected?
Thanks
again,
Adrian
-Original Message-From: Tanel Poder
[mailto:[EMAIL PROTECTED]Sent: 17 September 2003
14:00To: Multiple recipients of list ORACLE-LSubject:
Re: Nosort parallel dml uses TEMP tablespace
Hi!
Btw, I did a little testing with PARALLEL 2, and
yes it seems to be a parallel slave communications issue, despite the segments
show type "SORT".
When i traced sorting with 10032 10033, I
saw sort segment allocation, but no real sorting occurring. When I compared
first rows in source table with target one,I saw that when in source
table I had rows (1,2,3,4,5,6,7), then in beginning of target table, I had
(1,3,5,7,etc..). That means I had only one session reading data (query
coordinator), which then evenly distributed data to it's slaves.
You should includePARALLEL hint to your
select statement as well, with same number of slaves (in addition to insert
+append one), that way PX can work parallel_to_parallel way, thus for each
producing (query) slave there is a consuming slave and data doesn't have to be
distributed or splitted from one source to several queues (like with
parallel_from_serial). That way no data is intermediately stored to temporary
segments.
This worked for me, but since PX is a complex
mechanism, youmight see different results.
Tanel.
- Original Message -
From:
Tanel
Poder
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, September 17, 2003
2:44 PM
Subject: Re: Nosort parallel dml uses
TEMP tablespace
Hi!
It could be that parallel slaves are passing
intermediate results using TEMP tablespace for some reason. (When table
queues are full in some cases or smth like that). It can depend on parallel
execution plan as well (whether it's parallel_to_parallel or
parallel_to_serial data distribution).
If you used only /*+ APPEND */ without
parallel, then operations were done serially by one session only, thus no
intermediate result passing between slaves (using table queues) were needed.
You might want to post your question to
comp.databases.oracle.server newsgroup, I've seen Jonathan Lewis answering
more advanced performance related questions there.
Tanel.
- Original Message -
From:
Turner, Adrian A SITI-ITPSIE
To: Multiple recipients of list
ORACLE-L
Sent: Wednesday, September 17, 2003
11:39 AM
Subject: RE: Nosort parallel dml uses
TEMP tablespace
I'm even more confused now.
TEMP ran out of space at 20GB -
I'm only inserting 12.2GB!
So I kicked it off again with the
hint just as /*+ APPEND */ and it completed successfully in 1/6th the time
to failure of the parallel insert.
-Original
Message----- From: Turner, Adrian A
SITI-ITPSIE Sent: 16
September 2003 15:16 To: Multiple recipients of list ORACLE-L
Subject:
Nosort parallel dml uses TEMP
tablespace
Apologies if this has been
covered before but metalink is not clear on the reasons behind
it
The database is running
version 9204 EE on WinNT Sp6; and the statement is a parallel direct
load into partitioned table selecting from a 12.2GB source
table.
ALTER SESSION
ENABLE PARALLEL DML ;
INSERT /*+ APPEND
PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW
(select * from
TRANSACTIONS);
I'm seeing segments created
in the temp tablespace (from v$sort_usage)
Sess# User Name
TABLESPACE
CONTENTS
ext BLOCKS
SZ_MB - - --- -
-- -- 9
SYS
TEMP2
TEMPORARY 1629
208512 1629
11
SYS
TEMP2
TEMPORARY 1629
208512 1629
12
SYS
TEMP2
TEMPORARY 1629
208512 1629
13
SYS
TEMP2
TEMPORARY 1629
208512 1629
I've enough space and it'll
finish by tomorrow morning which is the deadline but does anyone know
why TEMP is required and what the end to end process
is?
I would have expected to
have seen temporary segments created, but in the partitions own
tablespace. It doesnt se
Nosort parallel dml uses TEMP tablespace
Title: Nosort parallel dml uses TEMP tablespace Apologies if this has been covered before but metalink is not clear on the reasons behind it The database is running version 9204 EE on WinNT Sp6; and the statement is a parallel direct load into partitioned table selecting from a 12.2GB source table. ALTER SESSION ENABLE PARALLEL DML ; INSERT /*+ APPEND PARALLEL(TRANSACTION_NEW,4) */ INTO TRANSACTION_NEW (select * from TRANSACTIONS); I'm seeing segments created in the temp tablespace (from v$sort_usage) Sess# User Name TABLESPACE CONTENTS ext BLOCKS SZ_MB - - --- - -- -- 9 SYS TEMP2 TEMPORARY 1629 208512 1629 11 SYS TEMP2 TEMPORARY 1629 208512 1629 12 SYS TEMP2 TEMPORARY 1629 208512 1629 13 SYS TEMP2 TEMPORARY 1629 208512 1629 I've enough space and it'll finish by tomorrow morning which is the deadline but does anyone know why TEMP is required and what the end to end process is? I would have expected to have seen temporary segments created, but in the partitions own tablespace. It doesnt seem optimal to me. Thanks in advance for your help, Regards, Adrian
RE: Oracle Instances not starting up via 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). :- :- :-__ :-Do you Yahoo!? :-SBC Yahoo! DSL - Now only $29.95 per month! :-http://sbc.yahoo.com :--- :-Please see the official ORACLE-L FAQ: http://www.orafaq.net :--- :-Author: Jose Luis Delgado :- 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.net -- Author: Nuala Cullen 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.net -- Author: Niall Litchfield 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.net -- Author: 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: Why are SPFILEs binary anyway? (was RE: How to make SPFILE in sync with INIT.ORA ?)
') 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.net -- Author: DENNIS WILLIAMS 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.net -- Author: Arup Nanda 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.net -- Author: Niall Litchfield 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.net -- Author: Arup Nanda 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.net -- Author: Pardee, Roy E 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: RE: Ab Initio, Unix Scripting, and Datawarehousing
://www.orafaq.net -- Author: DENNIS WILLIAMS 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.net -- Author: Stephane Paquette 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: AW: Cannot delete datafile from O/S -- Help !!
My workaround is to offline the tablespace first; seems to work and havent experienced any issues - but then maybe I'm just lucky. ;-) -Original Message- Sent: 07 March 2003 15:54 To: Multiple recipients of list ORACLE-L Though this is how it works on NT, I was pleasantly surprised the other day when I was able to delete the files from a tablespace that had just been dropped on Win2k. Jared On Friday 07 March 2003 04:53, Stefan Jahnke wrote: Hi Khanna(?) Happens alot. Windows still holds a handle to the file. Just wait 'till next scheduled reboot (shouldn't be too long;). Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprungliche Nachricht- Von: Prem Khanna J [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 7. Marz 2003 11:54 An: Multiple recipients of list ORACLE-L Betreff: Cannot delete datafile from O/S -- Help !! Guys, SQL drop tablespace DATA14 including contents; Tablespace dropped. the name of the datafile in DATA14 tablespace is DATA141.DBF SQLselect * from v$tablespace where name = 'DATA14'; no rows selected SQLselect * from v$tdatafile where name = 'DATA14.DBF'; no rows selected when i try to delete the file DATA14.DBF from o/s , it says : Cannot delete DATA14. There has been a sharing violation.The source or destination file may be in use. why is it so ? how can i delete the file from O/S ? can someone help me ? the ENV is 8.1.6 / win2k. TIA. Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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.net -- Author: Turner, Adrian A SITI-ITPSIE 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: Count(*) last 30 seconds
Title: Message Ramon, Our Win2k boxes get between 1000-2000 gets a second off a SAN. Are you using compressed folders to store your datafiles? Whats Multi_block_read_countset to? Set MBRC to 32 (32x8K=256K). Make your extent sizesare divisable by 256Kto reduce gets Regards Adrian -Original Message-From: Broodbakker, Mario [mailto:[EMAIL PROTECTED]]Sent: 12 February 2003 14:09To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds That's not so bad: 14644 physical reads in 30 seconds..that's about 500 I/O sec. Depending on your disk layout that's pretty optimal, I think. Mario -Original Message-From: Ramon E. Estevez [mailto:[EMAIL PROTECTED]]Sent: woensdag 12 februari 2003 14:19To: Multiple recipients of list ORACLE-LSubject: RE: Count(*) last 30 seconds Hermant, Sergey The table has 13 columns, the PK is formed for the first 11. There is no deletion nor update, just inserts in the table. I had truncated the tables sometimes testing the procedure that load the rows. This is the result with an auto trace. COUNT(*)-- 1466196 Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=896 Card=1) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'DM_VENTAS' (Cost=896 Card=1466196) Statistics-- 0 recursive calls 0 db block gets 14677 consistent gets 14644 physical reads 0 redo size 386 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Hemant K ChitaleSent: Tuesday, February 11, 2003 10:24 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Count(*) last 30 secondsYou are doing Full-Table-Scans.1. What's the average row length ? How many columns does the table have ?2. How many "consistent gets" does the count(*) cause ? [ie, how many blocks does it actually have to read ?]3. Are all these Physical Reads ? Is the DB_CACHE_SIZE large enough to hold most of theblocks ? What is the query-run-time if you re-run the query immediately again ?HemantAt 08:19 AM 11-02-03 -0800, you wrote: Hi list,I issue a select count(*) from mytable and last 30 seconds.The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space.I consider that time exagerated.The TBS is LMT with a Uniform size of 128 MB.The block size is 8MB, version 9.2.0.1.0 in Windows 2000.Where should I start looking ???TIARamon E. Estevez[EMAIL PROTECTED]809-565-3121 Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
