Re: Hello
This looks like the Novarg worm What say people ? If yes, then thank you Listguru for filtering out the binaries [EMAIL PROTECTED] is a mass-mailing worm. The worm will arrive as an attachment with a file extension of .bat, .cmd, .exe, .pif, .scr, or .zip. When the machine gets infected, the worm will set up a backdoor into the system by opening TCP ports 3127 thru 3198. This will potentially allow a hacker to connect to the machine and utilize it as a proxy to gain access to it's network resources. In addition, the backdoor has the ability to download and execute arbitrary files. The worm will perform a DoS starting on February 1, 2004. On February 12, 2004 the worm has a trigger date to stop spreading. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 04:04 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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 to look for in STATSPACK report
No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Anjo Kolk 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).
SV: Nextval in trigger
Hi Roland, Create the sequence before using it, e.g.: create table system_change(ID number); create sequence system_change_id; Insert into system_change values(system_change_id.nextval); select * from system_change; Regards Jesper Haure Nørrevang -Oprindelig meddelelse- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af [EMAIL PROTECTED] Sendt: 26. januar 2004 13:24 Til: Multiple recipients of list ORACLE-L Emne: Nextval in trigger Hallo, I would like to make an insert statement into a table in atrigger, Iam trying to do: Insert into system_change values(system_change_id.nextval) but it gives me an error message which tells me that I havent declared any sequence. How can I fix this ? Thanks in advance. Roland -- 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: Jesper Haure Norrevang 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 to look for in STATSPACK report
that's pretty definitive. :) I did say retaining permanent ownership Is Veritas hosting it for you? --- Anjo Kolk [EMAIL PROTECTED] wrote: No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Anjo Kolk 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
query plan is bad when it is run inside a pl/sql stored procedure
All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar 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: query plan is bad when it is run inside a pl/sql stored procedure
It's a bad query that could probably be resolved throuh an analytic function but I don't normally delve into things like that before having finished my 2nd coffee. You can use hints, in particular, there is a hint to force hash join. On 01/27/2004 06:44:25 AM, S.Sarkar wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: query plan is bad when it is run inside a pl/sql stored
Is the sql really the same query is run from a stored procedure or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)? At 04:44 AM 1/27/2004, you wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: When does Oracle use 'Index Fast Scan'
I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: [EMAIL PROTECTED] 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).
Exam promo code
Does the OTN promo code for exams still exist? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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: String manipulation
Title: Message If you have a way to work this out in shell then there is a simpler solution ... $ export VAR='mystr1~mystr2~mystr3'$ echo $VARmystr1~mystr2~mystr3$ echo $VAR | tr '~' '\012'mystr1mystr2mystr3$ HTH, Nikhil -Original Message-From: Stefick Ronald S Contr ESC/HRIDA [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 3:29 PMTo: Multiple recipients of list ORACLE-LSubject: String manipulation I'm trying to separate a string into 3 values: The string is: mystr1~mystr2~mystr3 Here is the code so far: 1 select substr(subject,1,instr(subject,'~')-1) first, 2 substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) second, 3 substr(subject,instr(subject,'~',1,2)+1,length(subject)) 4 from test_table 5 where test_column=1700455 The result I get is: mystr1 mystr2~mystr3 mystr3 The result I want is: mystr1 mystr2 Mystr3 TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540
RE: What to look for in STATSPACK report
No, the server is in my basement. Anjo. -Original Message- Rachel Carmichael Sent: Tuesday, January 27, 2004 11:44 AM To: Multiple recipients of list ORACLE-L that's pretty definitive. :) I did say retaining permanent ownership Is Veritas hosting it for you? --- Anjo Kolk [EMAIL PROTECTED] wrote: No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Anjo Kolk 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
RE: What to look for in STATSPACK report
- -Original Message- - From: Anjo Kolk [mailto:[EMAIL PROTECTED] - Sent: Tuesday, January 27, 2004 8:29 AM - To: Multiple recipients of list ORACLE-L - Subject: RE: What to look for in STATSPACK report - - - No, the server is in my basement. why? was it being a bad server?;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] The Truth is realized in an instant; the Act is practiced step by step. - Zen saying -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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 to look for in STATSPACK report
0kay, then the alcohol we were consuming fogged my brain :) getting older is a pain in the butt... I do know I was getting emails from Veritas about the oraperf site. That must be where the confusion lies --- Anjo Kolk [EMAIL PROTECTED] wrote: No, the server is in my basement. Anjo. -Original Message- Rachel Carmichael Sent: Tuesday, January 27, 2004 11:44 AM To: Multiple recipients of list ORACLE-L that's pretty definitive. :) I did say retaining permanent ownership Is Veritas hosting it for you? --- Anjo Kolk [EMAIL PROTECTED] wrote: No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Anjo Kolk 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services--
Re: Re: When does Oracle use 'Index Fast Scan'
btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: [EMAIL PROTECTED] 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: Exam promo code
no, but you can buy one on Ebay that is 40% off for $8. A friend of mine did it and it works. From: Boivin, Patrice J [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 08:09:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Exam promo code Does the OTN promo code for exams still exist? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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: [EMAIL PROTECTED] 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: String manipulation
On 01/26/2004 06:29:26 PM, Stefick Ronald S Contr ESC/HRIDA wrote: I'm trying to separate a string into 3 values: The string is: mystr1~mystr2~mystr3 There is trivial, non-PL/SQL solution based on the split function. To see more, type perldoc -f split and you should see the light. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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 to look for in STATSPACK report
Anjo So what was the deal with oraperf.veritas.com if you don't mind my asking? I subscribed to it sometime before Christmas, but when I went to use it a week or so ago, it had disappeared and I had to (re)subscribe to www.oraperf.com. -- David Lord -Original Message- From: Anjo Kolk [mailto:[EMAIL PROTECTED] Sent: 27 January 2004 13:29 To: Multiple recipients of list ORACLE-L Subject: RE: What to look for in STATSPACK report No, the server is in my basement. Anjo. -Original Message- Rachel Carmichael Sent: Tuesday, January 27, 2004 11:44 AM To: Multiple recipients of list ORACLE-L that's pretty definitive. :) I did say retaining permanent ownership Is Veritas hosting it for you? --- Anjo Kolk [EMAIL PROTECTED] wrote: No, It is mine! Anjo. -Original Message- Rachel Carmichael Sent: Friday, January 23, 2004 11:49 AM To: Multiple recipients of list ORACLE-L well, I can't get to the site at the moment to test it.. if I remember correctly, Anjo said he had leased it to Veritas for a couple of years, while retaining permanent ownership. On the other hand, he's on this list, he can confirm or deny that himself! --- Mogens_Nxrgaard [EMAIL PROTECTED] wrote: Hi Tim, Are you sure it's still owned by Veritas? Doesn't look that way when I checked it just now. Mogens Tim Gorman wrote: Helmut, Register with http://www.oraperf.com; and run those STATSPACK reports through the YAPP analyzer, which will reformat them in such a way that they make sense. All of the ratio stuff on the STATSPACK report is ignored by the YAPP analyzer, and instead the reformatting looks at things from the standpoint of response-time analysis, as described in the white papers at http://www.oraperf.com/whitepapers.html;. Yes, I know OraPerf is now owned by Veritas and the real URLs are different, but it'll always be just good old oraperf.com hopefully, no matter who Anjo works for... :-) Hope this helps... -Tim on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote: Hi! We want to introduce a performance monitoring policy here. We are using the STATSPACK utility. What are sections in statspack reports to look for? What are threshold numbers for these values? Does anybody have any power points or papers about it? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Anjo Kolk 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try
Re: FW: Lots of Help needed
Could you ask your Oracle rep. for a reference or two in your industry? They're usually very quick to give those references. You can contact the company in question and ask them for references. HP-UX and terabyte sized oracle 9i database are rather frequent combination, but you should contact their management because DBA usually doesn't have insight into details on the SAN side. I wouldn't trust everything I read on this list. There are pranksters and some rather sarcastic people here. References are the way to go. Cheers, mate. On 01/26/2004 10:14:27 PM, Biddell, Ian wrote: Hi there fellow Oracle people, I am hoping that some of you can help me and/or provide details etc. for my situation I find myself in. So if you bear with me I will describe the situation. I currently support an Oracle 9i (just moved from 7.3.4), windows, tuxedo, client server application for a utilities company in Australia. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: String manipulation
Title: String manipulation select substr(subject,1,instr(subject,'~')-1) first, substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-(instr(subject,'~'))-1) second, substr(subject,instr(subject,'~',1,2)+1,length(subject)) third from test_table where test_column=1700455 / Ray -Original Message-From: Stefick Ronald S Contr ESC/HRIDA [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:29 PMTo: Multiple recipients of list ORACLE-LSubject: String manipulation I'm trying to separate a string into 3 values: The string is: mystr1~mystr2~mystr3 Here is the code so far: 1 select substr(subject,1,instr(subject,'~')-1) first, 2 substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) second, 3 substr(subject,instr(subject,'~',1,2)+1,length(subject)) 4 from test_table 5 where test_column=1700455 The result I get is: mystr1 mystr2~mystr3 mystr3 The result I want is: mystr1 mystr2 Mystr3 TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540 ___ This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.
RE: String manipulation
Title: String manipulation I wrote a PL/SQL package with functions you can use for this. Find it athttp://www.smdi.com/employee/johnf/list.pks andhttp://www.smdi.com/employee/johnf/list.pkb. I wrote it so that only the first call parses the string. Subsequent calls use the already parsed pieces. -Original Message-From: Feighery Raymond [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 27, 2004 9:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: String manipulation select substr(subject,1,instr(subject,'~')-1) first, substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-(instr(subject,'~'))-1) second, substr(subject,instr(subject,'~',1,2)+1,length(subject)) third from test_table where test_column=1700455 / Ray -Original Message-From: Stefick Ronald S Contr ESC/HRIDA [mailto:[EMAIL PROTECTED]Sent: Monday, January 26, 2004 11:29 PMTo: Multiple recipients of list ORACLE-LSubject: String manipulation I'm trying to separate a string into 3 values: The string is: mystr1~mystr2~mystr3 Here is the code so far: 1 select substr(subject,1,instr(subject,'~')-1) first, 2 substr(subject,instr(subject,'~')+1, instr(subject,'~',1,2)-1) second, 3 substr(subject,instr(subject,'~',1,2)+1,length(subject)) 4 from test_table 5 where test_column=1700455 The result I get is: mystr1 mystr2~mystr3 mystr3 The result I want is: mystr1 mystr2 Mystr3 TIA, Scott Stefick MILPDS OCP Oracle DBA [EMAIL PROTECTED] 210-565-2540 ___ This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.
Re: Problem with jobs
Have you checked DBA_JOBS for the last/next execution times? Is the job broken? Please check DBA_JOBS. Jared On Mon, 2004-01-26 at 06:04, Mauricio Vlez wrote: Hi, This is the situation: I'm woriking on NT and there are two 8i databases on it One database can execute jobs normally, but the other one not execute any job. I proved submitting the same procedure to both databases and worked on the first one but not on the second one. If I manually execute this:(On the database that have the problem): Exec dbms_job.run(job_number); Then the job is successful executed (without any error) and is programmed to the next interval but after that its not executed any more. And It doesnt appear broken (Its like the queue process were not working). I know all jobs are not executed because the column last_date on dba_jobs its not updated on any job (so next_date column its not updated) and the procedure of each job are not doing what they must do. Its happens since one week ago and before that they were working well. When I submit the Job I issued commit and I dont receive any error message So the Jobs doesnt appear broken (Its like the queue process were not working) But the initialization parameter are job_queue_processes = 4 job_queue_interval = 10 in init_SID.ora file So I repeat, the jobs were working well until one week ago, and I havent change nothing special on database. the view dba_job_running appear empty all the time. I removed all jobs and I recreated them again and it didnt resolve the problem. Im not using Oracle Enterprise Management, so I cant see diagnostic error messages and in the alert file it doesnt appear nothing related with the jobs. So early at morning I have to run the jobs manually Im thinking on shutdown down database but I want It to be the last option. I hope you can help me Thanks Mauricio __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! -- 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).
Re: When does Oracle use 'Index Fast Scan'
One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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: When does Oracle use 'Index Fast Scan'
This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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: When does Oracle use 'Index Fast Scan'
Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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: Tanel Poder 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).
use_nl and distinct
Friends -- I seem to recall that there was some performance degradation using use_nl when distinct was involved, but can't seem to find a source on google for this. First, has anyone else run into this problem, and second, is there an online source that references the whys and wherefores? Thanks! Bambi. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bellow, Bambi 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: When does Oracle use 'Index Fast Scan'
This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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: When does Oracle use 'Index Fast Scan'
i found numerous cases(I dont have them in front of me) when fast full scan incurred far more logical I/Os than an index range scan. I found this particularly for oltp type get 10 records transactions. However, I forced an index_ffs once and it increased my logical I/Os by 30% but decreased my response time by about 50%. now this is non-scalable. Must be my disk access speeds as you said. It was for a batch process. unfortunately I didnt hold onto my cases. any comments on the 'full scan'. I rarely have seen oracle make good use of this type of index scan. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:14:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: Re: When does Oracle use 'Index Fast Scan'
my question pertains to regular 'index full scans' NOT index fast full scans. any ideas? I rarely ever find this to be an optimal index access method for anything. From: Tanel Poder [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:19:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: Re: When does Oracle use 'Index Fast Scan'
Yes, and my reply was about regular index full scans, according to your question. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:59 PM my question pertains to regular 'index full scans' NOT index fast full scans. any ideas? I rarely ever find this to be an optimal index access method for anything. From: Tanel Poder [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:19:27 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Another situation where index full scans might be handy, would be where hash joins are disabled and sorted output can be used for fast sort-merge join. Btw, multiblock reads are available for regular index range and full scan under some specific conditions as well - I'm talking about readahead and parameter _non_contiguous_multiblock_read for example. However, in my brief tests I've not managed to see this kind of behaviour yet. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 6:04 PM One situation I can think of where a (non-fast) full index scan can be helpful is when the index contains all the columns needed for the query, the query requires all the rows of the table, and the query requires the results to be sorted according to the index. This way, fast full index scan may be slower because you need to sort the rows after retrieving the blocks, whereas the non-fast full index scan does not. Regards, Dave [EMAIL PROTECTED] wrote: I know when oracle uses a fast full scan. Its the full scan that does 1 I/O at a time. I rarely see oracle using it and when it does, it generally means my table(s) aren't properly analyzed. From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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
Re: When does Oracle use 'Index Fast Scan'
Of course you also need to consider the application. Will there be large number of users? Does this query run often, or just occasionally? Scalability comes into play, and a method that requires fewer oracle resources ( latches ) is preferable, if possible. Jared David Hau [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 08:54 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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
Re: OT: Solaris: Finding the cause for disk space growth
i normally go to the mount point (ie highest level dir for that disk) and issue: du -k | sort -n that way you see where the space is going in descending sequence good luck, steve Naveen, Nahata (IE10) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/23/2004 03:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:OT: Solaris: Finding the cause for disk space growth Hi All, Sorry for an OT question, but nowhere else to go. Pretty new to Solaris so might be a naive question. Need a pointer on how to do this. The disk space in the machine is constantly decreasing. And I want to know which files/directories are growing. Is there any way to find out? Regards Naveen
FW: pl/sql array processing?
My following message did not seem to make it to oracle-l.freelists. Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if there is
a way to make it faster by not looping each array elements, but doing some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
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: ADMIN PLZ REPLY - FW: !!Please Read - Oracle-L moving!!
Bruce, Thank you for providing the service for as long as you did (and could). Jared has found us a new home but we will miss Fatcity. Take care! Ruth -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bruce A. Bergman Sent: Thursday, January 22, 2004 11:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: ADMIN PLZ REPLY - FW: !!Please Read - Oracle-L moving!! Yes, this is legitimate. Jared and I have been talking recently about this. This list has just outgrown what Fat City can handle. While I'd like to think that I've always provided adequate-to-good service for the list, it's never been great, and with the list growing, and traffic growing, my concern is that I just won't be able to continue to give the list good service. It makes me sick to think that, because I really have enjoyed giving back to the Oracle-L community, and because y'all have supported ME so well in the past, but I just don't want to see anything deteriorate simply because the volume exceeds what we can handle here. The list archives here will be available as long as Fat City continues to be in existence, so those of you who are searching for old topics are quite welcome to use the facilities here. It won't be going away. Jared has always been an awesome list owner, and I know he'll continue to make sure the list is successful. This move is just an indication of the relevancy and successful growth of the Oracle-L list, and I know it will continue. I wish you all the best in your new home, and I'll see you over there in a minute. :-) thanks, bruce bergman ListMaster, Fat City Hosting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman 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: Ruth Gramolini 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: using oracle 817 driver against oracle 92 database
--- David Hau [EMAIL PROTECTED] wrote: As long as you're not using any 9.2 feature, you should be fine. IIRC, according to Oracle's upgrade policy, the client and the server are compatible within one major version. Even if upgrading to the 92 client is not an emergency, you should at least upgrade the 817 client to the latest patch level 8174 (or the latest minor patch level if it's a Windows client, i.e. 8.1.7.4.10 or something like that; check metalink for the latest.). Regards, Dave 1. Pentium IV issues with JRE. 2. They're going to have to install a 9.2 client eventually, why start off with software that should have already been obsoleted. 3. sqlloader direct path load (as well as exp, imp) 4. sqlplus tracing functionality in 9.2 Pd [EMAIL PROTECTED] wrote: Hi all: I just learned that our application team is using oracle 817 driver to connect to a oracle 92 application (via das and via websphere). Is anyone aware of any big problems with that setup? I am going to recommend to upgrade to oracle9, but I don't think this is an emergency. Am I wrong? thanks Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Drake 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: FW: pl/sql array processing?
Declare
type numTbl is table of number index by binary_integer;
refTbl numTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string will
be lost after each iteration. May be an actual example that makes sense
would motivate more people to respond.
On 01/27/2004 12:54:29 PM, Guang Mei wrote:
My following message did not seem to make it to oracle-l.freelists.
Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if
there is
a way to make it faster by not looping each array elements, but doing
some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
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: Mladen Gogala
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: FW: pl/sql array processing?
Couldn't the declarations be put into a package? We've done this in order
to maintain values for the life of the session.
Rich
Rich JesseSystem/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
Sent: Tuesday, January 27, 2004 12:39 PM
To: Multiple recipients of list ORACLE-L
Declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string will
be lost after each iteration. May be an actual example that makes sense
would motivate more people to respond.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
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: FW: pl/sql array processing?
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote: Couldn't the declarations be put into a package? We've done this in order to maintain values for the life of the session. Yes, they could, I didn't see it in this example. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
possible to load a string with paragraphs?
Hi List, I have a web application that allows users to type notes with paragraphs. Is it possiable to load the string with paragraphs into Oracle (not save the note as a file)? Later on the application has to display the same format for the note when the user queries that record on the web. Thanks for any inputs. _ Check out the coupons and bargains on MSN Offers! http://shopping.msn.com/softcontent/softcontent.aspx?scmId=1418 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd 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: FW: pl/sql array processing?
If mypackage.function(i) is doing some DML operation on i, then the real
way to make it faster is to modify the signature of
mypackage.function(i) to take an array instead, and to do a forall ...
dml operation within mypackage.function(i).
forall is most useful when you want to minimize context switching
between the pl/sql and sql engines for an array. Using forall you'll be
switching context only once whereas using a regular for loop you'll be
switching context for every member of the array.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string
will be lost after each iteration. May be an actual example that
makes sense
would motivate more people to respond.
On 01/27/2004 12:54:29 PM, Guang Mei wrote:
My following message did not seem to make it to oracle-l.freelists.
Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if
there is
a way to make it faster by not looping each array elements, but doing
some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTbl numTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
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: David Hau
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: FW: pl/sql array processing?
Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
just examples, the actual element index is a varible and they are not
continuous. Yes, refTbl can be defined into a package. I guess what I am
asking is if there is a way in pl/sql to do something like
-- FORALL array element indexes (they are non-continuous)
call a package function (parameter: element index)
-- end for
without looping the array.
-- orginal code:
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
Guang
-Original Message-
Mladen Gogala
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote:
Couldn't the declarations be put into a package? We've done this in
order
to maintain values for the life of the session.
Yes, they could, I didn't see it in this example.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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: Guang Mei
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: When does Oracle use 'Index Fast Scan'
ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau 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:
Re: FW: pl/sql array processing?
forall should be used as follows:
forall index in lower_bound..upper_bound
sql statement;
Putting anything other than a sql statement (e.g. a pl/sql block) in a
forall statement defeats its purpose.
If you think about it, forall achieves its performance improvement by
binding arrays to the arguments of a sql statement, instead of binding
individual elements of the array to the sql statement. This is faster
because now the entire array is passed from the pl/sql engine to the sql
engine all in one shot, and so this minimizes context switching between
the pl/sql engine and the sql engine. This is analogous to the array
binding in OCI if you're familiar with OCI or Pro*C programming.
Putting anything other than a sql statement in a forall statement does
not achieve any benefit because you're not switching context to the sql
engine.
So Guang, I think what you should do is move the forall closest to where
you're doing the sql (DML) operation. If you're doing the DML within
mypackage.function(...) then pass the entire array into
mypackage.function(...) and then do the forall within mypackage.function.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
just examples, the actual element index is a varible and they are not
continuous. Yes, refTbl can be defined into a package. I guess what I am
asking is if there is a way in pl/sql to do something like
-- FORALL array element indexes (they are non-continuous)
call a package function (parameter: element index)
-- end for
without looping the array.
-- orginal code:
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
Guang
-Original Message-
Mladen Gogala
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote:
Couldn't the declarations be put into a package? We've done this in
order
to maintain values for the life of the session.
Yes, they could, I didn't see it in this example.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
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: David Hau
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: Problem with jobs
Mauricio, Check the Oracle version. We had similar problems with 8.1.7.2. They got disappeared after we upgraded to 8.1.7.4 -- Thanks, Krishna ~~ NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ~~ Jared Still wrote: Have you checked DBA_JOBS for the last/next execution times? Is the job broken? Please check DBA_JOBS. Jared On Mon, 2004-01-26 at 06:04, Mauricio Vlez wrote: Hi, This is the situation: I'm woriking on NT and there are two 8i databases on it One database can execute jobs normally, but the other one not execute any job. I proved submitting the same procedure to both databases and worked on the first one but not on the second one. If I manually execute this:(On the database that have the problem): Exec dbms_job.run(job_number); Then the job is successful executed (without any error) and is programmed to the next interval but after that its not executed any more. And It doesnt appear broken (Its like the queue process were not working). I know all jobs are not executed because the column last_date on dba_jobs its not updated on any job (so next_date column its not updated) and the procedure of each job are not doing what they must do. Its happens since one week ago and before that they were working well. When I submit the Job I issued commit and I dont receive any error message So the Jobs doesnt appear broken (Its like the queue process were not working) But the initialization parameter are job_queue_processes = 4 job_queue_interval = 10 in init_SID.ora file So I repeat, the jobs were working well until one week ago, and I havent change nothing special on database. the view dba_job_running appear empty all the time. I removed all jobs and I recreated them again and it didnt resolve the problem. Im not using Oracle Enterprise Management, so I cant see diagnostic error messages and in the alert file it doesnt appear nothing related with the jobs. So early at morning I have to run the jobs manually Im thinking on shutdown down database but I want It to be the last option. I hope you can help me Thanks Mauricio -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Krishna Kakatur 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).
UNION ALL Query: Riddle
Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: How to find the last execution time of a Procedure.
Thanks John and everyone for their suggestions.
Best Regards,
Prasad
John Kanagaraj
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
ds.com cc:
Sent by: Subject: RE: How to find the last
execution time of a Procedure.
[EMAIL PROTECTED]
.com
01/23/2004 06:59
PM
Please respond to
ORACLE-L
Raj,
I am no X$ expert either, but X$KGLOB is exposed to us lowly DBAs as
V$DB_OBJECT_CACHE and KGLHDEXC is actually the EXECUTIONS column.
Prasada, you can check V$DB_OBJECT_CACHE for TYPE in ('PACKAGE','PACKAGE
BODY') and KEPT = 'NO' and keep pinning these using DBMS_POOL.KEEP via a
scheduled job. After a while, all those used packages will not only become
KEPT (and provide some side benefit of reducing reloads), you will not have
to store them back into the database... The KEPT = NO will avoid having to
revisit/manipulate those objects that were previously pinned. Of course,
this assumes that there is adeqauet Shared pool space and the Db is not
restarted in-between :)
YMMV!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and
do
not reflect those of my employer or customers **
-Original Message-
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to find the last execution time of a Procedure.
But you better check with experts as my knowledge of x$ is
feather-weight ... also there is a column on x$kglob called
kglhdexc ... to me it seems the execution count (I feel like
Mr. Monk already). so if execution count is 0 then you
can say that it actually got executed.
But if this doesn't work, in the next CTOUG meeting, I'll try
to hide away from you.
YMMV
Raj
---
-
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 1:24 PM
To: Multiple recipients of list ORACLE-L
Thanks for input Raj.
I was also thinking on the same lines (Querying v$views
periodically and
store it in some metadata table) if there is no easier way to
figure out
from DBA_ views.
As far as changing the production code, as you know, It has
to go thru the
dev/test databases first and then go thru the release process
to implement
into the production. It is painful process.
I will use x$kglob instead of changing production code and all
that release
stuff. Thanks for your help, Raj.
Best Regards,
Prasad
860 843 8377
***
***
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
Re: UNION ALL Query: Riddle
Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: When does Oracle use 'Index Fast Scan'
An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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: David Hau INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: When does Oracle use 'Index Fast Scan'
i thought an index_fs only read 1 block per i/o? same with an index range scan because they are using random access? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 5:29 PM An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a 'range scan' to this type of scan with a FIRST_ROWS hint and this reduced performance. This is just a full scan of the index, one block at a time right? When would this ever be superior to a Fast Full Scan or a Range Scan? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Hau 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
Re: When does Oracle use 'Index Fast Scan'
If you look up Logical I/O in the Master Glossary of the Oracle docs, it's defined as: A block read which may or may not be satisfied from the buffer cache. So a logical I/O is always a *block* read. It does not take into acount whether you're doing a multiblock read or not. One reason this is so is because the CBO does not know whether the block is already in the buffer cache or not. If it's already in the cache, then multiblock read is not an issue anymore. The init parameter db_file_multiblock_read_count is what makes the CBO favor fast full index scan over a non-fast scan like full index scan or index range scan. The LIO itself does not take into consideration the aspect of multiblock read. Regards, Dave [EMAIL PROTECTED] wrote: i thought an index_fs only read 1 block per i/o? same with an index range scan because they are using random access? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 5:29 PM An index fast full scan and an index full scan both need to access all the blocks of an index. The only difference between them is that the index_ffs accesses the blocks in the order of the blocks (and uses multiblock read), whereas the index_fs accesses the blocks in the order of the b tree index. In terms of # logical I/Os, they are exactly the same. OTOH, an index range scan by definition is a _range_ scan, and need to access only a subset of the blocks of an index. Because of this, it'll have a lower # logical I/Os than an index_ffs. Regards, Dave [EMAIL PROTECTED] wrote: ive found that index_ffs typically incur higher logical I/Os that index range scans. so its not just access speeds. From: David Hau [EMAIL PROTECTED] Date: 2004/01/27 Tue AM 11:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' This is where the access time of your disks (or SAN) makes a difference. If your disks have really fast access time, then a random-access pattern would not cause much performance degradation and so a range scan would not be slow at all, even though it's traversing the b-tree index structure. If you're only striping together disks with relatively slow access time (e.g. using a striped IDE disk array), then you have high throughput but not that fast an access time. In this case, fast full index scan would be much faster than an index range scan because the fast full scan reads the blocks sequentially and a sequential disk I/O requires only positioning the head once (assuming the disk is not fragmented). The rest of the time depends on the throughput. If you stripe together a large enough number of IDE disks, then your throughput is great but your access time is still the access time of a single IDE drive which is not that fast. This is assuming you need to do a physical I/O to obtain the blocks. Of course, if the blocks already reside in the buffer cache, then it's a different story. Regards, Dave [EMAIL PROTECTED] wrote: btw, in many cases range scan is faster than a fast full scan. Range scan recursively hits the nodes that are needed and skips the ones that are not. So it reads less blocks. So if you are looking for a 'range' or a specific value, range scan beats fast full scan most of the time. Less Logical and Physical I/Os. test it and hint your queries From: David Hau [EMAIL PROTECTED] Date: 2004/01/26 Mon PM 10:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: When does Oracle use 'Index Fast Scan' Correction: the Index Range Scan can be parallelized when it involves multiple partitions. - Dave David Hau wrote: I assume you're talking about the Fast Full Index Scan. This is used when the index contains all the columns necessary to answer the query. It's faster than a Full Table Scan because indexes are smaller than entire rows, so a Fast Full Index Scan will scan fewer blocks than a Full Table Scan. It's faster than an Index Range Scan firstly because Fast Full Index Scan scans the blocks in sequential order, whereas the Index Range Scan traverses the B-tree index structure in scanning the blocks, resulting in a random access I/O pattern which is slower. This is also why the Oracle documentation says that with a Fast Full Index Scan, the result is not sorted by the index key (because the result is not obtained by traversing the index structure.) Secondly, the better performance is also because the Fast Full Index Scan uses multiblock reads and is capable of parallel operation, whereas the Index Range Scan is capable of neither. Regards, Dave. [EMAIL PROTECTED] wrote: I have found that the vast majority of time that Oracle chooses this method, my statistics are stale and the query is sub-optimal. One time, Oracle changed from a
Re: query plan is bad when it is run inside a pl/sql stored
it is the same. '%TATA.COM' is not a variable. sumant --- Wolfgang Breitling [EMAIL PROTECTED] wrote: Is the sql really the same query is run from a stored procedure or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)? At 04:44 AM 1/27/2004, you wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar 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).
Hotsos Oracle-l list dinner
Okay, I know this is a cross-post but. If you are attending the Hotsos Symposium, plan on attending the Tuesday night Oracle-l get-together/dinner and have NOT already emailed me off-list that you are coming, please do so by Friday. Gary Goodman is going to make the reservation for us based on my count I'd like it to be as close to accurate as possible. Thanks all! Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: consistent read gets
Title: Message Thanks Mark Bobak syed - Original Message - From: Bobak, Mark To: Multiple recipients of list ORACLE-L Sent: Monday, January 26, 2004 6:39 PM Subject: RE: consistent read gets (I'm sending the reply to the freelists.org list as well. Hope you all agree that's a reasonable thing to do.) no work - consistent read gets - Oracle needs a block that's consistent w/ a particular SCN, goes to the buffer cache, finds it there. It's done.cleanouts only - consistent read gets - Oracle needs a block that's consistent w/ a particular SCN, goes to the buffer cache, finds it there, but discovers it was recently modified and never cleaned out. It needs to do extra work to clean out the block.rollbacks only - consistent read gets - Oracle needs a block that's consistent w/ a particular SCN, goes to the buffer cache, finds it there, but discovers that the SCN on the block is too recent. So, it refers to data stored in rollback, to roll back the block until it's old enough to be consistent w/ the query SCN.cleanouts and rollbacks - consistent read gets - Oracle needs a block that's consistent w/ a particular SCN, but discovers it needs to cleanout the block, does so, then discovers it needs to roll it back, and does that too. Hope that's clear. In reality, there are lots of different types of situations that can occur. The above is probably a bit simplified, but I think accurate as far as it goes. -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -Original Message-From: Sultan Syed [mailto:[EMAIL PROTECTED] Sent: Monday, January 26, 2004 8:44 AMTo: Multiple recipients of list ORACLE-LSubject: consistent read gets Hi all, Could somebody give example scenariofor the followings please no work - consistent read gets cleanouts only - consistent read gets rollbacks only - consistent read gets cleanouts and rollbacks - consistent read gets And Is ther difference between consistent read gets and consistent gets? Thanks in advance Syed
[oracle-l] FW: pl/sql array processing?
My following message did not seem to make it to oracle-l.freelists. Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if there is
a way to make it faster by not looping each array elements, but doing some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
-
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-
To unsubscribe send email to: [EMAIL PROTECTED]
put 'unsubscribe' in the subject line.
-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
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).
