RE: How to find the last execution time of a Procedure.
Life is much easier, just use audit execute on proc name No need for the x$tables :) Regards, Waleed -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:19 AM To: Multiple recipients of list ORACLE-L Hi All, Is there anyway to find out from data dictionary views when was a database procedure/function last executed. Would like know the solution for 8i and 9i databases. We have some older code in the databases and do not know if any application is using it or not. I appreciate your help. Thanks Best Regards, Prasad * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * -- 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: Khedr, Waleed 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!!
Jared and Bruce: Thanks for everything. Waleed -Original Message- Sent: Thursday, January 22, 2004 11:14 AM To: Multiple recipients of list ORACLE-L 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: Khedr, Waleed 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: Unusable partition index -- working funny
My guess it's firing the first time but is not taking effect during the current transaction may be because it fires as a recursive sql within the main sql. Not a good idea to put this in a trigger. Regards, Waleed -Original Message- Sent: Tuesday, January 20, 2004 7:19 PM To: Multiple recipients of list ORACLE-L Hello All, I have a strange problem... I have a table on which i am doing an update. Its a partition table and the local index on the column which is being updated is in an unusable state. I have a database trigger at statement level (before update of col_a for ) where i do an execute immediate ' alter session set skip_unusable_indexes = true'; i log into sqlplus as the owner of the table and do the following SQL connect [EMAIL PROTECTED] Enter password: ** Connected. SQL UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1 where 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1166444 where * ERROR at line 1: ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of such index is in unusable state SQL / 1 row updated. --- My question is why does the trigger not fire for the first time... When i do the / i am able to update the table which means the trigger is firing the 2nd time. Any help would be greatly appriciated.. thanks, sathish. -- http://www.fastmail.fm - Consolidate POP email and Hotmail in one place -- 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: Khedr, Waleed 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: chk integrity of a .dmp exp
The SHOW option for import might be helpful Waleed -Original Message- Sent: Thursday, January 08, 2004 12:59 AM To: Multiple recipients of list ORACLE-L Hi, What is the procedure to check the integrity of a exported .dmp file? I have: an exported .dmp file of a critical database. I want: to check the integrity, the contents, whether it can be successfully restored. I already know about: creating another test database and trying to import this exported file. This is not what I am looking for. Is there a utility that displays the contents of a .dmp in tree format AND verify the data integrity that the ,dmp can be successfully restored? Thanks L. MOhan Arun __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: L. Mohan Arun 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: Khedr, Waleed 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: Exporting a partition with transport tablespace
Probably he needs to set: pga_aggregate_target :) Regards, Waleed -Original Message- Sent: Monday, December 29, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Your brain is getting full. You should stop studying so much. See what it does to you? -Original Message- Yep, I didn't remember the exact clause in the exchange partition syntax. Tanel. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: large pl/sql table sucking up all memory on a server
Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: any single serial session will never get more than 5% of pga
the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Khedr, Waleed 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: Jonathan Lewis 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: Khedr, Waleed 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: any single serial session will never get more than 5% of pga
To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Khedr, Waleed 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: ora-4030 pga memory allocation running wild
This is scary, I'm planning to upgrade 9.2.0.4 from 9.2.0.2. I don't know how removing pga_aggegrate_target will help reducing memory!! Does the program have any memory tables, etc? Did you monitor the PGA size from the Oracle side using v$sesstat? A sql by itself can't consume this memory except there is a major bug some where, which I doubt! Please keep us updated. Thanks Waleed -Original Message-From: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 23, 2003 10:24 AMTo: Multiple recipients of list ORACLE-LSubject: ora-4030 pga memory allocation running wild Hi, I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding 2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere. Last week we migrated from on oracle 7 environment where this program ran smoothly for years. At the same time we migrated the OS also and started with new machines. The ux kernel parameter for max data segment size is 2Gb. I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has 4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things. This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider? Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory Thanks in advance, Jeroen
RE: Writing a delay in PL/SQL?
I'm curious why? some testing? Regards, Waleed -Original Message- Sent: Saturday, November 29, 2003 8:34 PM To: Multiple recipients of list ORACLE-L I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Khedr, Waleed 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 miss-spelled hint changes explain plan ...
Title: Using miss-spelled hint changes explain plan ... Did you see any changes in the Execution plan? Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, November 20, 2003 10:20 AMTo: Multiple recipients of list ORACLE-LSubject: Using miss-spelled hint changes explain plan ... Okay, I found this by accident ... I was trying to get explain plan on a query that involved a in-line-query that selects from financials database. This in-line query was alias'd as "A". Current cost of query was ~2800. So, to test for explain plan I added hint /*+ diving_site(A) */ -- note the spelling mistake here ... the cost of the query changed to 25. I corrected the spelling mistake and make it driving_site, cost went back to ~2900. Has anyone experienced this before? Our original db is 9202 and financials is on a RBO, 805 db accessed through a db_link. 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 ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5
RE: html output
sqlplus -M "html on" -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 19, 2003 6:50 PMTo: Multiple recipients of list ORACLE-LSubject: html output I am lookingfor autility package for throwing output in html format from a query . This should use utl_file to write the file ( no sqlplus markup ). Is there any package /procedure oracle has to do this job? This is just a html report and it will be sent to users by email. ( this is not a OAS/IAS report ). Thanks -ak
RE: granting SELECT privilege on SYS.X$ TABLES
You need to check if the SYS.X_$tables are views or tables. I believe they are views for the X$Tables Waleed -Original Message- Sent: Friday, November 14, 2003 8:54 PM To: Multiple recipients of list ORACLE-L I always thought that one could not grant SELECT privilege on the SYS.X$ tables, and to make them accessible to another user one would have to create a view on the table (as mentioned on Steve Adams' ixora website: http://www.ixora.com.au/scripts/prereq.htm create_xviews.sql) However someone told me recently that you could grant SELECT on sys.X_$... When I tried this, I saw results that confused me. In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to 1- grant select on SYS.X_$KTFBFE to another_user ; 2- grant select on SYS.X_$KTFBHC to another_user ; 3- grant select on SYS.X_$KTFBUE to another_user ; BUT 4- grant select on SYS.X_$KDXST to another_user ; returns ORA-00942 table or view does not exist. In database B, using the same ORACLE_HOME as database A (i.e. identical Oracle version and OS) even the first three grant statements returned ORA-00942 When I tried it on more recent Oracle databases on Windows / SunOS servers, it worked intermittently: Oracle 9.0 (SunOS): all GRANTS failed Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed Oracle 10.1 beta (Windows 2000): all GRANTS failed Does anyone know the reason for this strange behaviour? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Khedr, Waleed 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: (looong) PCTFREE, PCTUSED and ASSM
I believe it's direct read from files in parallel execution, nothing gets read from cache. Cached blocks for the table get flushed to files before the direct read. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Yep, the situation can get bad for parallel execution, especially if blocks read aren't cached... But for serial FTS I haven't seen such a problem, I did even a test to verify it on 9.2.0.4, and did see behaviour as I expected - all blocks were scanned using multiblock reads and rows were returned in order the contents of them were found, instead of pointers. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:14 AM Actually row migration is a big problem for FTS also(whether serially or using PQ). You end up waiting for too many db file sequential read single block reads instead of MBRC in (direct path read, db file scattered read) Regards, Waleed -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: (looong) PCTFREE, PCTUSED and ASSM
Using PQ, the segment is split into multiple sub-segments using rowid range scan. Since there is no guarantee that the migrated row will be in the same sub-segment that has the pointer to the migrated row, the PQ slave might need to resolve the issue real time. I mean the migrated row might exist in a different range that will be scanned by another PQ slave. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 10:49 AM To: Multiple recipients of list ORACLE-L As a strange thing, from 10046 trace I saw that normal table scanning was done using direct reads, this was expected behaviour, but the lookups of migrated rows were reflected as 'db file sequential reads'. And even more, there were 3 subsequent sequential read waits for the same datablock in a row, it seems that a PX slave isn't even able to cache one datablock in it's PGA, in case of finding migrated rows... (or a wait event is registered for reading from cache...) I was just wondering, why a PQ FTS requires resolving migrated rows immediately, instead of reading them when scan hits their location. Could it be some concurrency issue, that if a row migrates to another location during the scan, then results could get inconsistent? It is not a direct read issue, because I experimented using _serial_direct_read parameter, and for regular FTS, no migrated rows were resolved ahead. There's lot to learn... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:34 PM I believe it's direct read from files in parallel execution, nothing gets read from cache. Cached blocks for the table get flushed to files before the direct read. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Yep, the situation can get bad for parallel execution, especially if blocks read aren't cached... But for serial FTS I haven't seen such a problem, I did even a test to verify it on 9.2.0.4, and did see behaviour as I expected - all blocks were scanned using multiblock reads and rows were returned in order the contents of them were found, instead of pointers. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:14 AM Actually row migration is a big problem for FTS also(whether serially or using PQ). You end up waiting for too many db file sequential read single block reads instead of MBRC in (direct path read, db file scattered read) Regards, Waleed -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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
RE: (looong) PCTFREE, PCTUSED and ASSM
I think it will behave the same in serial scans but we may not see any waits since there is good chance the blocks are cached in buffer cache. Waleed -Original Message- Sent: Wednesday, November 12, 2003 4:45 PM To: Multiple recipients of list ORACLE-L Yep, that's exactly what I thought, that due migration the row might go to a PX granule read by another slave... but I still don't see enough reason why migrated rows are handled different from serial scan. During serial scan you also might have rows in beginning of segment migrating to end of it, so there has to be some other reason as well. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 6:24 PM Using PQ, the segment is split into multiple sub-segments using rowid range scan. Since there is no guarantee that the migrated row will be in the same sub-segment that has the pointer to the migrated row, the PQ slave might need to resolve the issue real time. I mean the migrated row might exist in a different range that will be scanned by another PQ slave. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 10:49 AM To: Multiple recipients of list ORACLE-L As a strange thing, from 10046 trace I saw that normal table scanning was done using direct reads, this was expected behaviour, but the lookups of migrated rows were reflected as 'db file sequential reads'. And even more, there were 3 subsequent sequential read waits for the same datablock in a row, it seems that a PX slave isn't even able to cache one datablock in it's PGA, in case of finding migrated rows... (or a wait event is registered for reading from cache...) I was just wondering, why a PQ FTS requires resolving migrated rows immediately, instead of reading them when scan hits their location. Could it be some concurrency issue, that if a row migrates to another location during the scan, then results could get inconsistent? It is not a direct read issue, because I experimented using _serial_direct_read parameter, and for regular FTS, no migrated rows were resolved ahead. There's lot to learn... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 4:34 PM I believe it's direct read from files in parallel execution, nothing gets read from cache. Cached blocks for the table get flushed to files before the direct read. Regards, Waleed -Original Message- Sent: Wednesday, November 12, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Yep, the situation can get bad for parallel execution, especially if blocks read aren't cached... But for serial FTS I haven't seen such a problem, I did even a test to verify it on 9.2.0.4, and did see behaviour as I expected - all blocks were scanned using multiblock reads and rows were returned in order the contents of them were found, instead of pointers. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:14 AM Actually row migration is a big problem for FTS also(whether serially or using PQ). You end up waiting for too many db file sequential read single block reads instead of MBRC in (direct path read, db file scattered read) Regards, Waleed -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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
RE: Interesting PL/SQL Puzzle
You have not provided me with anything (I cannot post these things to public forums because of my email)! The only thing you said bad code, good code! I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. The idea is to add value instead of waiting to have the final words! Regards, Waleed -Original Message- Sent: Monday, November 10, 2003 11:34 PM To: Multiple recipients of list ORACLE-L Khedr, Waleed wrote: The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) I've provided a selfexplanatory fix of the 'bad' code, please review it. You code uses standard.like, and a lot isntances of booleans -- each IF condition, same could relate to out variables (ls), and I hope you understood why the package is used. I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage I cannot provide you with tech. details open the C (native) code and see. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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: (looong) PCTFREE, PCTUSED and ASSM
://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: Khedr, Waleed 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: Interesting PL/SQL Puzzle
out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- end if; end; / - -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Khedr, Waleed 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: Interesting PL/SQL Puzzle
pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- -- if var1 like pat3 then if var2 like pat1 then out1 := pat2; else out1 := pat4; end if; end if; -- end; / -Original Message- Sent: Monday, November 10, 2003 5:34 PM To: Multiple recipients of list ORACLE-L Hi! NAMESHARABLE_MEM --- TEST_PLSQL1 185607 TEST_PLSQL5 9123 A lot of junk, right? :) PL/SQL engine works with interpretive code, it does not have any optimizations -- here I do simplify, so do not consider this statement as an absolute truth -- like, e.g. most of the C compilers have. It has its own rules that are not clear, usually. dbms_profiler won't help here (It could mislead, however. For a good example see recent post of Raj), IMHO. As I told: I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage These two things are simple but important. So, I would sugget to change it to (sorry for dirty coding): CREATE OR REPLACE PACKAGE test IS PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); END; / CREATE OR REPLACE PACKAGE BODY test IS pat1 CONSTANT varchar2(1000) := '%tt%'; pat2 CONSTANT varchar2(1000) := 'lll'; pat3 CONSTANT varchar2(1000) := '%dfddiii%'; pat4 CONSTANT varchar2(1000) := 'y'; ls VARCHAR2(1000); b1 BOOLEAN; b2 BOOLEAN; PROCEDURE test_plsql2 ( var1 in out varchar2 , var2 in out varchar2 , out1 in out varchar2 , out2 in out varchar2 ); begin if false then b1 := var1 LIKE pat3; b2 := var2 LIKE pat1; -- if b1 then if b2 then ls := pat2; else ls := pat4; end if; end if; . out1 := ls; END; or something alike, hope you get the idea. On my system it gives: 00:01:28.12 vs 00:00:08.60. When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. I think this statement of yours does answer your original question -- bad PL/SQL coding -- bad NC results. HTH, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Khedr, Waleed wrote: Below are two dummy procs that are good enough to explain the issue (Jared forgive me for posting this big code). All the code in proc test_plsql1 is inside an IF clause that will not run. [...] proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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: Interesting PL/SQL Puzzle (found the cause)
It seems it's not because of bad code :) Replace any LIKE by = in the TEST_PLSQL1 it becomes ten times faster, although none of them gets executed! Waleed -Original Message- Sent: Sunday, November 09, 2003 12:39 PM To: Multiple recipients of list ORACLE-L I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Yechiel Adar 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: Khedr, Waleed 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Interesting PL/SQL Puzzle
I had the same thought, also thought that if you have something like this that does not get executed: if VAR1 like '%abcbdbdbbbfdbfdfdfd%' The compiler (or the semi-compiler) still has to allocate memory for VAR1 and load it. I'm testing and will update the list, if I managed to find anything! Thanks Waleed -Original Message- Sent: Sunday, November 09, 2003 12:15 PM To: Multiple recipients of list ORACLE-L I do not know anything about the way oracle semi compiled the code but I will try a wild guess (anybody who better please correct me): Maybe (a big MAYBE) oracle translate: if then... else as: If cond then do; else go to line 250. And the go to line 250 is counting lines until it arrive to the correct line number. So decreasing the source lines speed up the execution. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 08, 2003 8:09 PM I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Yechiel Adar 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: Khedr, Waleed 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).
Interesting PL/SQL Puzzle
I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Interesting PL/SQL Puzzle
I did, but it did not help. As I explained there is nothing in the code that gets executed as I explained in my code. Two IF statements (IF FALSE THEN) that will be always FALSE. So nothing of the code inside the if gets executed. Interestingly when I start to remove some of the code inside IF THEN, it starts to speed up. Waleed -Original Message- Sent: Saturday, November 08, 2003 2:34 PM To: Multiple recipients of list ORACLE-L Without knowing the actual code, I'd use dbms_profiler and run this test say a thousand time. Analysis of collected data will help you find the problem spots. -Original Message- Sent: Saturday, November 08, 2003 1:09 PM To: Multiple recipients of list ORACLE-L I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Khedr, Waleed 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: Interesting PL/SQL Puzzle
The stored proc does not do any DML/DDL. It does strings search/manipulation. I run it 30 million times in 30 minutes using 32 threads. There is no wait time, it's pure CPU time that simply does not go down except when I remove the code that does not execute. -Original Message- Sent: Saturday, November 08, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: pattern search
Check LIKE LIKE Conditions The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set. LIKEC uses Unicode complete characters. LIKE2 uses UCS2 codepoints. LIKE4 uses USC4 codepoints. -Original Message- Sent: Friday, November 07, 2003 2:20 AM To: Multiple recipients of list ORACLE-L Hi All, I need a means to search for a pattern (With basic wildcard characters like %, _, ^, []). How do I do this in oracle. I also need to get back the string that matches the pattern. Is there any predefined function or procedure that does this. Would like to avoid implementing this on my own. TIA, ShivaM DISCLAIMER: This e-mail contains proprietary information some or all of which may be legally privileged. It is for the intended recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the author by replying to this e-mail. If you are not the intended recipient, you must not use, save, disclose, distribute, copy, print or relay this e-mail. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Shiva Maran 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: Khedr, Waleed 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: Getting Number of Rows in CTAS across DBLink
Use pl/sql block with execute immediate 'create table as ...' Number of rows should be in sql%rowcount (immediately after execute immediate). Waleed -Original Message-From: Arup Nanda [mailto:[EMAIL PROTECTED]Sent: Friday, November 07, 2003 3:34 PMTo: Multiple recipients of list ORACLE-LSubject: Getting Number of Rows in CTAS across DBLink List, When I create a table as select * from another table across a dblink, how do I find out how many rows were created in the table? Is there a statistic somewhere, documented or otherwise, that tells me how many rows were fetched? Currently I am using a rather convoluted approach - using the statistic, bytes received via SQL*Net to dblink, and dividing that by the average row size to get an approximate idea of the number of rows. However, this approximation is far from even reasonably accurate; and since the rowsize can change radically, it can be way off the mark. Any help or pointers will be highly appreciated. Thanks. Arup Nanda
RE: long time to kill a session
Did you try orakill? -Original Message-From: Mauricio "Vilez [mailto:[EMAIL PROTECTED]Sent: Thursday, November 06, 2003 4:14 PMTo: Multiple recipients of list ORACLE-LSubject: long time to kill a session Hi, the limit of connections on my Dedicated Serveris 400. The Server is on Windows NT I have a script that kills all conections that failsdue to communication.When I run the The script, the status of the sessions change into killbut it takes a long time to really kill them. How Can I free those connections faster? Regards,Mauricio Vlez Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard
RE: External Tables question
Title: External Tables question What about rownum? Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 11:25 AMTo: Multiple recipients of list ORACLE-LSubject: External Tables question I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA 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 ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
RE: nologging for IOT
A trick, use a regular table and create an index that has all the needed columns. Waleed -Original Message- Sent: Wednesday, November 05, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Igor Neyman 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: Khedr, Waleed 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: External Tables question
select * from (Select rownum m_id, table_name from dba_tables) -Original Message- Sent: Wednesday, November 05, 2003 12:10 PM To: Multiple recipients of list ORACLE-L cant you use rownum with a 'merge'? From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:34:33 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: External Tables question What about rownum? Waleed -Original Message- Sent: Wednesday, November 05, 2003 11:25 AM To: Multiple recipients of list ORACLE-L I am trying to use external tables, but can't seem to find one thing that I'd like (I have already RTFM'd but may have missed some part). Is there a way I could load the line number of the text file as a column in the table? line number isn't hard coded but can record number be used (somehow)? Any ideas? TIA 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 ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: nologging for IOT
- 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!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Khedr, Waleed 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: IMP using the same DMP file
Title: IMP using the same DMP file I do not see a problem. The file can be read only. -Original Message-From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED]Sent: Wednesday, November 05, 2003 4:50 PMTo: Multiple recipients of list ORACLE-LSubject: IMP using the same DMP file Hi, We were just wondering if you can IMP into two instances using the same dmp file at the same time? We need to refresh both our development and test instances with data from our production database and doing both at once might save some time. 8.1.7 and Unix. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145
RE: What is a local write wait?
I found this on the Metalink: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FORp_id=183745.995 Waleed -Original Message- Sent: Saturday, November 01, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- 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: Khedr, Waleed 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: Finding overlapping time periods - suggestions please
Title: Finding overlapping time periods - suggestions please
Easy, this should
do it:
Create
a time dimensions--drop table
test_date_dim;create table test_date_dim (time_dt
date); Fill the dimension
for one day only--beginfor i
in 1..24*60 loopinsert into test_date_dim values (trunc(sysdate)+i/
(24 * 60));end
loop;commit;end;
Check the dimension
contents--select
to_char(time_dt,'mm/dd/yy hh24:mi:ss') mtimestamp from
test_date_dim; Create the
activity table--create table
test_activity ( activity_id number, start_dt date, end_dt date);insert into
test_activity values (1, to_date('10:00','hh24:mi'),
to_date('12:00','hh24:mi'));insert into test_activity values (3,
to_date('11:00','hh24:mi'), to_date('13:00','hh24:mi'));insert into
test_activity values (4, to_date('11:30','hh24:mi'),
to_date('13:30','hh24:mi'));insert into test_activity values (7,
to_date('13:30','hh24:mi'),
to_date('16:00','hh24:mi'));commit;
Check the activity table--select
* from test_activity; Easy
Solution--select
activity_id
activity_id,
count(*)
elapsed,
count(decode(activity_cnt,1,null,time_dt))
elapsed_multitask,
count(decode(activity_cnt,1,time_dt,null))
elapsed_single,
round(sum(decode(activity_cnt,1,0,1/activity_cnt)))
prorated_multi_minutes,
count(decode(activity_cnt,1,time_dt,null))
+
round(sum(decode(activity_cnt,1,0,1/activity_cnt))) prorated_minutes
from (select time_dt,
b.activity_id, count(distinct
b.activity_id) over (partition by time_dt) activity_cntfrom
test_date_dim a, test_activity bwhere a.time_dt = b.start_dt and
a.time_dt b.end_dt)group by
activity_id-
ACTIVITY_ID ELAPSED
ELAPSED_MULTITASK ELAPSED_SINGLE
PRORATED_MULTI_MINUTES
PRORATED_MINUTES1
120
60
60
25
853
120
120
0
55
554
120
90
30
40
707
150
0
150
0
150
Regards,
Waleed
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Friday,
October 31, 2003 1:25 PMTo: Multiple recipients of list
ORACLE-LSubject: Finding overlapping time periods - suggestions
please
I was wondering if anyone had the need to find
overlapping time periods and how to identify them efficiently.
Here is the scenario:
Elapsed minutes refer to
the actual "clock" time either spent on a given task. Thus an
activity that started at 9:00 am and finished at 11:00 am on the same day
is said to have 120 elapsed minutes.
If one task overlaps
another (either completely or partially with another task), then the tasks
are said to be "multitasked". In that case the system will store the
portion of the elapsed time that was multitasked as "elapsed multitask
minutes" and the portion of the time that was not overlapped as "elapsed
single minutes". In addition, for the portion of time that two or
more activities were simultaneously taking place; their time will be
divided by the number of simultaneous activities and stored as "prorated
multi minutes". The sum of Elapsed Single Minutes and Prorated
Minutes will equal the actual clock time that a vehicle was
active.
The following example
should help to illustrate these concepts. In the table below a list
of fictitious activities for a vehicle are shown in addition to how the
time is allocated to the various measures:
Activity
Start Time End
Time Elapsed Minutes Elapsed
Multitask Minutes Elapsed Single
Minutes Prorated Multi Minutes Prorated
Minutes1 10:00
12:00 120 60
60 25
853
11:00 13:00 120
120 0
55
554
11:30 13:30 120
90 30
40
707
13:30 16:00 150
0 150
0
150Totals
510
270 240
120 360The vehicle was active from 10:00 to 16:00, a total of
6 hours (360 minutes) which is equal to the total of Prorated
Minutes.
The vehicle performed 8 ½
hours (510 minutes) of work during that 6-hour time span. This can
be arrived at by adding the total of Elapsed Multitask Minutes (270) + the
total of Elapsed Single Minutes
(240).
RE: how to get rid of default
default null -Original Message- Sent: Thursday, October 30, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Hi. I have created a field in a table with a default clause. - f1 number(1) not null default 1. How can I get rid of the default now? thanks Gene __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Olga Gurevich 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: Khedr, Waleed 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: dual
Do you think it
will work if it has no rows ?
Waleed
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Thursday, October 30, 2003
1:44 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: dualYou may find
this interesting. Looks like a
'where rownum = 1' is always imposed on dual. Same results on 8.1.7.4 and 9.2.0.4
Don't try this on anything other than a
trashable test database. Jared === 10:42:04 dv03@dt 10:42:05 dv03 10:42:05
dv03set echo on 10:42:05
dv03 10:42:05 dv03create table
jkstill.dual as select * from sys.dual; Table created. 10:42:05
dv03 10:42:05 dv03select *
from jkstill.dual; D
- X 1 row selected.
10:42:05 dv03 10:42:05 dv03drop table jkstill.dual;
Table dropped. 10:42:05 dv03 10:42:05 dv03insert into sys.dual values('Y'); 1 row created. 10:42:05 dv03insert into sys.dual values('Z'); 1 row created. 10:42:05 dv03 10:42:05
dv03commit; Commit
complete. 10:42:05 dv03
10:42:05 dv03select * from
sys.dual; D - X
1 row selected. 10:42:05 dv03 10:42:05 dv03create table jkstill.dual as select * from
sys.dual; Table created.
10:42:05 dv03 10:42:05 dv03select * from jkstill.dual;
D - X Y Z
3 rows selected. 10:42:05 dv03 10:42:05 dv03drop table jkstill.dual; Table dropped. 10:42:05 dv03 10:42:05
dv03delete from sys.dual; 1
row deleted. 10:42:05
dv03delete from sys.dual; 1
row deleted. 10:42:05
dv03delete from sys.dual; 1
row deleted. 10:42:05
dv03 10:42:05 dv03insert into
sys.dual values('X'); 1 row
created. 10:42:05
dv03commit; Commit
complete. 10:42:05 dv03
10:42:05 dv03 10:42:05 dv03create table jkstill.dual as select *
from sys.dual; Table
created. 10:42:05 dv03
10:42:05 dv03select * from
jkstill.dual; D - X
1 row selected. 10:42:05 dv03 10:42:05 dv03drop table jkstill.dual; Table dropped. 10:42:05 dv03
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
10/30/2003 08:54 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:
dualList, here is a rtfm question which I was scared to ask, but
itsbothering me too much so I just can't stay quite :"why do
multiple inserts into sys.dual complete sucessfully when
connectedassysdba, but a subsequent select * from dual show only 1 row
?"-- 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.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
RE: Finding the session causing compile to hang
The old view v$access can show who is using what. Waleed -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 7:14 PM To: Multiple recipients of list ORACLE-L David, You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. More generally, use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. HTH, Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 10:59 AM I need to figure out a way to see if a procedure is running before attempting a compile and I can't figure out what tables to look in. Here's a test I set up create or replace procedure sleep(i_val number) is begin dbms_lock.sleep(i_val); end; / exec sleep(60); I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to spot the sleep stored procedure or it's session. Of course I could look in v$session and see it in this example but in a stored procedure that has more to it you will only see the current step it is at in the procedure and not the procedure itself. I'm trying to be able to identify sessions that hold the lock/latch on a stored procedure so I can kill them when sometimes the session is disconnected and just hangs. Thx, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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: Khedr, Waleed 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: bitmap index not used
Probably the histogram is deciding that. Remove the histogram and see if there is any changes. Waleed -Original Message- Sent: Wednesday, October 29, 2003 3:10 PM To: Multiple recipients of list ORACLE-L Hi, I have a puzzle. A bitmap index on a varchar2(25) column. table has 7131413 rows, of which 7125290 are null for the column in question, the rest of the rows are unique values. There is a histogram on this column, it has 2 buckets. A select statement using this column in the where clause generates two plans, one which uses the index and one which doesn't. The deciding factor is the length of the string in the filter. select * from table where microchip_number = 'avid1' this produces a plan which uses the bitmap select * from table where microchip_number = 'avid12' (and any additional characters in the string ) this produces a plan which uses a full table scan. I am stumped about how to figure out why the optimizer makes a choice based upon the length of the string in the predicate. Much obliged, Josh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Josh Collier 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: Khedr, Waleed 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: 10046 trace data question
Title: 10046 trace data question I believe it's from v$timer This view lists the elapsed time in hundredths of seconds. Time is measured since the beginning of the epoch, which is operating system specific, and wraps around to 0 again whenever the value overflows four bytes (roughly 497 days). Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 29, 2003 5:04 PMTo: Multiple recipients of list ORACLE-LSubject: 10046 trace data question Does anyone know where tim= comes from? Is it from a certain epoch? e.g. PARSING IN CURSOR #15 len=6 dep=2 uid=5 oct=44 lid=5 tim=1042250821743271 hv=1053795750 ad='1eed99f0' COMMIT END OF STMT PARSE #15:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1042250821743266 XCTEND rlbk=0, rd_only=1 EXEC #15:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1042250821743458 = PARSING IN CURSOR #1 len=2882 dep=1 uid=5 oct=47 lid=5 tim=1042250821743528 hv=3326928535 ad='16ff4a88' I am writing a program that takes a trace file and reconstructs the whole trace against a timeline. My first run looks like this ... As you can see, because this is first pass, I ma skipping a lot of details. Those will eventually come in ... don't know how yet ... my imagination is running wild. 2003-10-27 09:27:21.465000 Session Started. 2003-10-27 09:27:21.465000 PARSE Cursor#15 [ 0 microseconds] 2003-10-27 09:27:21.465192 EXEC Cursor#15 [ 192 microseconds] 2003-10-27 09:27:21.465259 EXEC Cursor#1 [ 67 microseconds] 2003-10-27 09:27:21.466318 PARSE Cursor#1 [ 1059 microseconds] 2003-10-27 09:27:21.466642 PARSE Cursor#8 [ 324 microseconds] 2003-10-27 09:27:21.466721 EXEC Cursor#8 [ 79 microseconds] 2003-10-27 09:27:21.467023 FETCH Cursor#8 [ 302 microseconds] 2003-10-27 09:27:21.467099 PARSE Cursor#9 [ 76 microseconds] 2003-10-27 09:27:21.469147 EXEC Cursor#9 [ 2048 microseconds] 2003-10-27 09:27:21.469228 EXEC Cursor#1 [ 81 microseconds] 2003-10-27 09:27:21.473288 PARSE Cursor#1 [ 4060 microseconds] although I am doing all calculations by hand, it would be nice to know where tim= is coming from any ideas? If you are curious why I am doing this? We get emails when users experience delays that are (or deemed) unacceptable. Next day we take the trace file and try to look at it, but without a good timeline it is difficult to find that a user did between 10:15am and 10:20am. That's why I am writing this program. 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 ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5
RE: Execution Plan is good but HIGH CPU
It's 800 sec for 5617 exec/fetch calls. It looks like it's a sql inside a cursor loop or stored proc that gets called from some app. If you are unhappy, try to get rid of the cursor logic and get everything done in one sql call. Waleed -Original Message- Sent: Tuesday, October 28, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Hi, Execution plan looks good but the query is consuming 800 seconds CPU timewhy? SELECT sampleavail, sample_cost_amount, sample_sale_amount, discount_room, discount_case, discount_half_case, allow_cut, retail_cut_amount, cost_cut_amount, gp_room from tbljnwpbookvendortype t1, tbljnwpbookvendor t2 where t1.jnwpbvid = t2.jnwpbvid and t2.prsuid = :b3 and t2.wpbkid = :b2 and t1.wpptid = :b1 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 5618 0.63 0.58 0 0 0 0 Fetch 5617800.05 782.07 01409683 0 4187 --- -- -- -- -- -- -- total11236800.68 782.66 01409683 0 4187 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 109 (DDTBL) (recursive depth: 1) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 NESTED LOOPS 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDOR' 0 INDEX (UNIQUE SCAN) OF 'TBLJNWPBOOKVENDOR_UQ1' (UNIQUE) 0TABLE ACCESS (BY INDEX ROWID) OF 'TBLJNWPBOOKVENDORTYPE' 0 INDEX (RANGE SCAN) OF 'TBLJNWPBOOKVENDORTYPE_IDX1' (NON-UNIQUE) Muqthar Ahmed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed 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: Khedr, Waleed 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: 9I RAC corporate standard.
I'm glad you had some energy left to describe your pain getting RAC to work on Linux (specially RHAS developer). I regret every minute of pain I spent trying to do the same! Waleed -Original Message- Sent: Tuesday, October 28, 2003 6:39 PM To: Multiple recipients of list ORACLE-L Well, we evaluated 9iRAC on some cheap-o Linux boxes as a proof-of-concept, with the hardware idea based on http://www.bradmark.com/site2/products/pdfs/9irac_config.pdf As far as I'm concerned, RAC's a major pain, unstable and not yet worth the risk -- for us. The idea for us being that we could move a DB or three to this RAC system with a no-cost OS on commodity hardware giving us HA and some load-balancing. I tried installing RHAS 2.1 ($60 duhveloper edition), but it's so old (based on RH7.2) that it couldn't identify our newish hardware (Intel D845 MB). Without a clear path to add kernel mods to allow HW identification, I installed SuSE SLES8. Yipe! Never did get far on that one. Way too many library/kernel issues to consider it. I finally ended up testing on RH9 because it could identify our hardware, I have some familiarity with it, and there are docs on the web (e.g. http://www.puschitz.com) to help get Oracle9i installed on it. I didn't have time to try United Linux, although it does come with a 2.4-19 kernel. Once that was resolved, I wanted to use a filesystem for Oracle, given the limitations of RAW on SCSI under Linux (max 15 partitions), so I downloaded OCFS 1.09. Well, it wouldn't install because of RH9's newer kernel (it was only made to work on RHAS2.1). And when I tried to compile the source, I got errors. So I patched the OCFS source with a modified version of a JFS patch for RH9 and it worked. Unfortunately though, it didn't perform, peaking out at about 1.2MB/s peak throughput and I switched to RAW (40-50 times faster). There's also the ocfstool that you need for monitoring because OCFS only allows contiguous file extents. Veritas is supposed to have a VxFS for Linux as a beta soon... There's not enough room here for me to go over the software install hell to get RAC actually on the systems. And anything Java-based (Installer, DBCA, OEM, etc.) most of the time flat out refused to run without any errors. I thought this was odd considering I didn't have any problem with other non-Oracle Java programs. Finally, when I called in a problem to Oracle Support regarding DBCA, I thought I had a decent tech until I was warned by him that my SHMMAX kernel setting was too high because it was over physical RAM. Also, I've had a helluva time trying to understand the 9iRAC client setup. I haven't found any Oracle docs yet that explain it well. Granted, some/much/all of this is probably because I'm running on an unsupported version of Linux. My problem with that is that it shouldn't freakin matter. With my luck at getting 9.2.0.4 to run on Gentoo, I just might try 9iRAC on there... :) I would *love* to try 9iRAC on OpenVMS. It should be by far the easiest to install and maintain, given the clustering is builtin to the OS. Gotta go redo some lvols now... GL! You'll need it! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Spears, Brian [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 4:24 PM To: Multiple recipients of list ORACLE-L Subject: 9I RAC corporate standard. Hi, Has anyone started to implement 9I Rac as a corporate standard... IE. many or all the apps being deployed on 9I RAC clusters? We are looking at doing it and wanted to know what other people had as experience in doing it or on the way to attempting it. If so, what hardware platform are you using? HP Itanium or Linux boxes etc? -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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
RE: Perm job opening in MA
LOL :) -Original Message- Sent: Tuesday, October 28, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Don't get me wrong, I've recently changed positions and am not interested, but what are phone skills? I know how to use a phone, and I can do it in yoga position with one hand tied behind my back. I've never used a phone under water or in space. I use it on a regular basis while commuting or in restaurants. It helps tremendously with finding a free seat. As for the communication over the phone, you should hear my inventive use of the English language when I'm talking to telemarketers. Creative assumptions about their ancestry and its position on the evolution tree and sexual preferences of their parents are the most common opener after which I usually take the poor soul to the place where no telemarketer has gone before. Do I have the right idea about the phone skills or you have in mind some extremely innovative use of phone which would be inappropriate for a good catholic like me? On 2003.10.28 20:09, John Spencer wrote: I hope I am not breaching any rules, but I would like to make it public that I am currently trying to fill a temp to perm position for a Sr level Oracle/customer support person in Massachusetts. This person must have strong Oracle and Sun Solaris skills and some Java (J2EE and Java beans) experience. Must have excellent phone skills and the ability to work with customers on installs and other issues. Experience must include stored procedures and triggers. Local candidates only please. Please reply directly to me at [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Thanks again for your time. Regards, John Spencer Sr. Staffing Consultant ProStart Inc. 603-893-7772 ext 45 603-893-7704 fax mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Coalescing tablespace
I always saw this note (and hated it): http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=31116.1 Hope it helps, Waleed -Original Message- Sent: Sunday, October 26, 2003 8:04 PM To: Multiple recipients of list ORACLE-L Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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: Khedr, Waleed 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: index full scan over an index fast full scan in an analytic f
Correct, I did not read the post carefully, thought he was saying Full scan does not return rows in order, so was stating the fact that rows come sorted using Index Full Scan. My fault! Waleed -Original Message- Sent: Saturday, October 25, 2003 5:34 AM To: Multiple recipients of list ORACLE-L Hi! You can't have ascending nor descending indesc fast full scans. This (asc/desc) only works with range or regular full scans. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 25, 2003 4:59 AM But the fact is, the access path is still a valid path if the user needs the data returned ordered on the indexed columns. Index_asc or Index_desc should do the job without extra cost. Waleed -Original Message- Sent: Friday, October 24, 2003 9:39 PM To: Multiple recipients of list ORACLE-L function? Tanel Tanel Poder wrote: As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) Full scan will search from index root block using branch blocks to first leaf block. And since all leaf blocks have pointers to next and previous leaf block in index, sequentially reading only leaf blocks is sufficient for returning all values in index, in order (keys are ordered inside leaf blocks as well). FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: index full scan over an index fast full scan in an analytic f
But the fact is, the access path is still a valid path if the user needs the data returned ordered on the indexed columns. Index_asc or Index_desc should do the job without extra cost. Waleed -Original Message- Sent: Friday, October 24, 2003 9:39 PM To: Multiple recipients of list ORACLE-L function? Tanel Tanel Poder wrote: As an addition to Vladimir's response: I cannot provide you with detailed information -- can only give pointers to the documentation -- otherwise it would look suspicious :) Full scan will search from index root block using branch blocks to first leaf block. And since all leaf blocks have pointers to next and previous leaf block in index, sequentially reading only leaf blocks is sufficient for returning all values in index, in order (keys are ordered inside leaf blocks as well). FFS will scan from index header block (note that index segment header and index root block are different ones) up to segment high water mark using multiblock reads and ignoring contents of root, branch, bitmap, extent map, freelist group blocks. Rows are returned as they've read from blocks, thus no order can be guaranteed. Rows are returned as they've read from blocks, thus no order can be guaranteed. Not rows, but blocks returned as is in order they being read. Keys (rows) are ordered inside leaf blocks -- as you wrote above. So, inside the blocks the order is consistent but blocks are 'mixed' whilst read. Things (parameters etc.) are changing, as Cary pointed out, principles are not. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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: Cache a table
(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: Richard Foote 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: Khedr, Waleed 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: stupid dbms_job question
Check if the job fails for some reason, any trace files for j??? in udump or bdump Also check job_queue_processes setting Waleed -Original Message- Sent: Thursday, October 23, 2003 11:14 AM To: Multiple recipients of list ORACLE-L id prefer to handle this in the database. From: Gene Sais [EMAIL PROTECTED] Date: 2003/10/23 Thu AM 10:29:33 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: stupid dbms_job question It's called cron :). Or you could run a shell script that executes then sleeps for 5 mins. [EMAIL PROTECTED] 10/23/03 10:09AM im trying to submit a job that runs every 5 minuts. Only way I can get the submit to work is as follows... variable jobno number; variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno); commit; end; i then do: dbms_job.interval(:jobno,'trunc(sysdate+1/96)'; commit; my next_date column in dba_jobs is set to 15 minutes in the future, HOWEVER, it doesnt actually run. The time passes, the next_date does not get set again to nother 15 minutes in the future and the job doesnt run. Ive read the manual. Read metalink. read asktom and Im obvious too stupid to figure this one out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Can I concatenate several rows without a procedure?
My guess the output will be limited to 4000 characters. Waleed -Original Message- Sent: Wednesday, October 22, 2003 12:24 AM To: Multiple recipients of list ORACLE-L Very impressive! I will definitly try this. Thanks Again, Jake On Tue, Oct 21, 2003 at 06:44:25PM -0800, Larry Elkins wrote: Stephane, Pretty slick trick!!! But I can't believe that you, of all people, didn't throw in an analytic just to confuse things even more, plus, avoid that second pass on sliced_kipling ;-) SQL l 1 select translate(ltrim(text, '/'), '/', ' ') verse 2 from (select text, row_number() over (partition by verse order by verse, lvl desc) rn 3from (select verse, level lvl, sys_connect_by_path(chunk, '/') text 4 from sliced_kipling 5 connect by verse = prior verse 6 and piece - 1 = prior piece)) 7* where rn = 1 SQL / VERSE Oh, East is East, and West is West, and never the twain shall meet, Till Earth and Sky stand presently at God's great Judgment Seat; But there is neither East nor West, Border, nor Breed, nor Birth, When two strong men stand face to face, tho' they come from the ends of the earth! And hey, it reduced sorts and consistent gets in this particular case ;-) Ok, my head hurts from dumb SQL tricks, someone else take it further from here ;-) Later, Larry G. Elkins [EMAIL PROTECTED] SQL select * from sliced_kipling; VERSE PIECE CHUNK -- -- -- 1 1 Oh, East is East, 1 2 and West is West, 1 3 and never the twain shall meet, 2 1 Till Earth and Sky stand 2 2 presently at God's great Judgment Seat; 3 1 But there is neither East nor West, 3 2 Border, 3 3 nor Breed, 3 4 nor Birth, 4 1 When two strong men stand face to face, 4 2 tho' they come from the ends of the earth! 11 rows selected. SQL @magic_query VERSE -- -- Oh, East is East, and West is West, and never the twain shall meet, Till Earth and Sky stand presently at God's great Judgment Seat; But there is neither East nor West, Border, nor Breed, nor Birth, When two strong men stand face to face, tho' they come from the ends of the earth! SQL l 1 select translate(ltrim(x.text, '/'), '/', ' ') verse 2 from (select verse, level lvl, sys_connect_by_path(chunk, '/') text 3from sliced_kipling 4connect by verse = prior verse 5 and piece - 1 = prior piece) x, 6(select verse, max(piece) piecemax 7 from sliced_kipling 8 group by verse) y 9 where x.verse = y.verse 10and x.lvl = y.piecemax 11* order by x.verse SQL I am not sure though that I satisfy the 'simple SQL' requirement :-). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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). -- Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jake Johnson 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: who writes the OCP tests?
Probably a Sybase DBA -Original Message- Sent: Wednesday, October 22, 2003 1:49 PM To: Multiple recipients of list ORACLE-L all oracle employees? Are any of you involved in the 9i PL/SQL test? I just took it today and passed it. I didnt study at all. Id like to state that it is an incredibly stupid test. Ive been told the PErformance tuning one is bad also... Some gripes: 1. Only package that I was asked about was dbms_lob and of the 66 questions maybe 5-7 were on this package so about 10%. 2. 40-50% of the test was on triggers. A bit disproportionate dont you think? 3. I was asked about 6-8 questions on how to call procedures and functions and they slipped in a few defaults to 'trick' you. That is WAY too many. 4. Nothing on Arrays, PL/SQL tables, Bulk Binds, or dynamic sql. 5. In my overview it said I need to study the following(but they never asked me about them) UTL_TCP, UTL_HTTP, dbms_ddl. DBMS_DDL is antiquated and I cant think of anything I need it for. I wrap everything in execute immediate(which wasnt on the test). How many people actually use UTL_TCP and UTL_HTTP anyway? I dont. Far more people use other features that werent covered. im done griping. This was REALLY bad. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: questions regarding nologging
Alter table ...Move TS nologging is 100% equivalent to CTAS and should generate minimal amount of logging even if it ran serially (no PQ). Actually the type of command is considered CREATE TABLE So I'm not sure how the original poster was able to determine that the operation generated huge redo logs! This could be possible if the redo logs has to do with Extents management and the needed RBS to manage it (specially if the extents are very small and the TS is dictionary based). Waleed -Original Message- Sent: Wednesday, October 22, 2003 4:54 PM To: Multiple recipients of list ORACLE-L That's because nologging attribute only affects the direct operations, i.e. the the operations that prebuild blocks and add them below the flood watermark. That includes sqlloader with direct=y, inserts with /*+ append */ hint and CTAS. Normal SQL based operations are not affected. On 10/22/2003 04:39:34 PM, Roger Xu wrote: Hi Gurus, I have a couple of questions regarding nologging. 1) alter table tabname move tablespace tbsname nologging; How come this sql still generated same amount of redo logs equal to the size of the table? 2) alter index idxname rebuild tablespace tbsname nologging; This sql only generate minimum redo logs. But the index ends up LOGGING=NO in dba_indexes view. How do I turn the logging on for this index? Thanks, Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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 Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Khedr, Waleed 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: Cache a table
From 9.2 doc: The LRU Algorithm and Full Table Scans When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache. You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table. Waleed -Original Message- Sent: Tuesday, October 21, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Mike: I guess we are aware there is no concept of LRU or MRU in current versions of Oracle and I don't think CACHE option will influence the behavior. With the new algorithm the MFU blocks are already in the hot end (unless they are read using CR read in that case they will be in cold end since we set the _db_aging_freeze_cr to TRUE) and we don't need to cache the blocks explicitely. You can monitor the behavior of this using the X$BH (espicially the last two columns TCH and TIM). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What is difference between SYSDATE and SYSDATE@! ??
Actually it works also when there is space between sysdate and @!. SQL select sysdate @! from dual; [EMAIL PROTECTED] - 17-OCT-03 -Original Message- Sent: Friday, October 17, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Actually, [EMAIL PROTECTED] is a synonym for @#$%! Sysdate or f***ng sysdate. As such, [EMAIL PROTECTED] has its function and it fulfills it perfectly. On 2003.10.17 14:41, Mercadante, Thomas F wrote: Uh. I think that SYSDATE works, while [EMAIL PROTECTED] does not. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, October 16, 2003 12:20 PM To: Multiple recipients of list ORACLE-L HI ALL What is difference between SYSDATE and [EMAIL PROTECTED] ?? Waleed Haggagy -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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 add ' (comma) at the begining and end of each line? Ei
small C, java, Perl, etc program can get it done quickly. -Original Message- Sent: Thursday, October 16, 2003 10:20 AM To: Multiple recipients of list ORACLE-L Either Unix or Windows Hi List I have 1000 lines in my data file. I want to add '(comma) at the begining and end of each line. For example, abf jd djkhk jd3 Shold be convrted to 'abf', 'jd', 'djkhk', 'jd3', Any help will be really appreciated. Thanks Sami __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA 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: Khedr, Waleed 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: ORA-02270: no matching unique or primary key for this column-
I ran the same
ddl in 9.2 and had no problems. Check for tables having the same names in other
schemas
Waleed
-Original Message-From: laura pena
[mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003
11:19 AMTo: Multiple recipients of list ORACLE-LSubject:
ORA-02270: no matching unique or primary key for this column-list -9iR2
DB
Help I am getting the following error on a 9.2.0.3 RAC on Solaris
DB:
Any help would be greatly appreciated THanks -Lizz
SQL ALTER TABLE AE ADD ( CONSTRAINT FK_AE FOREIGN KEY
(CDR_ID) REFERENCES CDR_TABLE (CALL_ID))/
2 3 4 ALTER TABLE AE ADD
(*ERROR at line 1:ORA-02270: no matching unique or
primary key for this column-list
1Here is how I created both tables:
CREATE TABLE VOICELOG.AE(
CDR_ID
VARCHAR2(16)
NOT NULL, AE_SEQ_ID
NUMBER(5)
NOT NULL, AE_FILE
VARCHAR2(64)
NOT NULL, AE_TYPE
VARCHAR2(1)
NOT NULL, AUDIO_TYPE
VARCHAR2(6)
NOT NULL, AE_TEXT
VARCHAR2(255))TABLESPACE DYNDATA_EXLGPCTFREE
25INITRANS 4STORAGE
(
MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)LOGGINGNOCACHENOPARALLEL/
REM This is a compsite partitioned table
CREATE TABLE VOICELOG.CDR_TABLE(
CALL_ID
VARCHAR2(16)
NOT NULL,
CDR_START_DT
DATE
NOT NULL,
CDR_DNIS
VARCHAR2(20)
NOT NULL,
CDR_ANI
VARCHAR2(20)
NOT NULL
)
TABLESPACE DYNDATAP1_MPCTFREE
10INITRANS 2STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
)PARTITION BY RANGE (CDR_START_DT) SUBPARTITION BY
HASH(CDR_START_DT) SUBPARTITIONS 4 STORE IN (DYNDATAP1_LG
,DYNDATAP2_LG,DYNDATAP1_LG,DYNDATAP2_LG)
( PARTITION p_111998 VALUES LESS THAN
(TO_DATE('111998','MM'))
SUBPARTITIONS 4 STORE IN (DYNDATAP1_LG, DYNDATAP2_LG,DYNDATAP1_LG,
DYNDATAP2_LG),
.
2 Loaded data in via import from an 8.1.7
system
3 Here is how I created indexes and primary
keys
CREATE UNIQUE INDEX PK_AE ON AE(CDR_ID,
AE_SEQ_ID)NOLOGGINGTABLESPACE
STATICIDX_EXLGPCTFREE 10INITRANS
2STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)NOPARALLEL/
ALTER TABLE AE ADD ( CONSTRAINT PK_AE PRIMARY KEY (CDR_ID,
AE_SEQ_ID) USING INDEX TABLESPACE
STATICIDX_EXLG PCTFREE
10 INITRANS 2
STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
))
CREATE UNIQUE INDEX PK_CDR_TABLE ON
CDR_TABLE(CALL_ID)NOLOGGINGTABLESPACE
DYNIDXP1_LGPCTFREE 15INITRANS
2STORAGE
(
MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)NOPARALLEL/
ALTER TABLE CDR_TABLE ADD CONSTRAINT
PK_CDR_TABLE PRIMARY KEY (CALL_ID) USING
INDEX/
Thanks in advance.
-Lizz
Do you Yahoo!?The
New Yahoo! Shopping - with improved product
search
RE: Issue:RE: ORA-02270: no matching unique or primary key for th
You
have the unique index defined as global index so my guess any partition
operation (parallel load, etc) will disable the index.
-Original Message-From: laura pena
[mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003
2:14 PMTo: Multiple recipients of list ORACLE-LSubject:
Issue:RE: ORA-02270: no matching unique or primary key for this
column-
Seems this table cdr_table has a disabled constraint:
1 SELECT constraint_name, constraint_type, status,
deferrable, deferred FROM 2* user_constraints where
table_name='CDR_TABLE'
CONSTRAINT_NAME
C STATUS DEFERRABLE
DEFERRED-- - --
-SYS_C005049
C ENABLED NOT DEFERRABLE
IMMEDIATESYS_C005050
C ENABLED NOT DEFERRABLE
IMMEDIATESYS_C005051
C ENABLED NOT DEFERRABLE
IMMEDIATEPK_CDR_TABLE
P DISABLED NOT DEFERRABLE IMMEDIATE
So my question now is does anyone know who you get into a disabled state?
Since my creation statements for this primary key, I did put to disable...
puzzled.
Many Thanks,
-Lizz
"Khedr, Waleed" [EMAIL PROTECTED]
wrote:
I ran the
same ddl in 9.2 and had no problems. Check for tables having the same names
in other schemas
Waleed
-Original Message-From: laura pena
[mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15,
2003 11:19 AMTo: Multiple recipients of list
ORACLE-LSubject: ORA-02270: no matching unique or primary key
for this column-list -9iR2 DB
Help I am getting the following error on a 9.2.0.3 RAC on Solaris
DB:
Any help would be greatly appreciated THanks -Lizz
SQL ALTER TABLE AE ADD ( CONSTRAINT FK_AE FOREIGN KEY
(CDR_ID) REFERENCES CDR_TABLE
(CALL_ID))/ 2 3 4
ALTER TABLE AE ADD (*ERROR at line 1:ORA-02270: no
matching unique or primary key for this
column-list
1Here is how I created both tables:
CREATE TABLE VOICELOG.AE(
CDR_ID
VARCHAR2(16)
NOT NULL, AE_SEQ_ID
NUMBER(5)
NOT NULL, AE_FILE
VARCHAR2(64)
NOT NULL, AE_TYPE
VARCHAR2(1)
NOT NULL, AUDIO_TYPE
VARCHAR2(6)
NOT NULL, AE_TEXT
VARCHAR2(255))TABLESPACE DYNDATA_EXLGPCTFREE
25INITRANS 4STORAGE
(
MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)LOGGINGNOCACHENOPARALLEL/
REM This is a compsite partitioned table
CREATE TABLE VOICELOG.CDR_TABLE(
CALL_ID
VARCHAR2(16)
NOT NULL,
CDR_START_DT
DATE
NOT NULL,
CDR_DNIS
VARCHAR2(20)
NOT NULL,
CDR_ANI
VARCHAR2(20)
NOT NULL
)
TABLESPACE DYNDATAP1_MPCTFREE
10INITRANS 2STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
)PARTITION BY RANGE (CDR_START_DT) SUBPARTITION BY
HASH(CDR_START_DT) SUBPARTITIONS 4 STORE IN (DYNDATAP1_LG
,DYNDATAP2_LG,DYNDATAP1_LG,DYNDATAP2_LG)
( PARTITION p_111998 VALUES LESS THAN
(TO_DATE('111998','MM'))
SUBPARTITIONS 4 STORE IN (DYNDATAP1_LG,
DYNDATAP2_LG,DYNDATAP1_LG, DYNDATAP2_LG),
.
2 Loaded data in via import from an 8.1.7
system
3 Here is how I created indexes and primary
keys
CREATE UNIQUE INDEX PK_AE ON AE(CDR_ID,
AE_SEQ_ID)NOLOGGINGTABLESPACE
STATICIDX_EXLGPCTFREE 10INITRANS
2STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)NOPARALLEL/
ALTER TABLE AE ADD ( CONSTRAINT PK_AE PRIMARY KEY (CDR_ID,
AE_SEQ_ID) USING INDEX
TABLESPACE STATICIDX_EXLG PCTFREE
10 INITRANS 2
STORAGE
(MAXEXTENTS
UNLIMITED
PCTINCREASE
0
))
CREATE UNIQUE INDEX PK_CDR_TABLE ON
CDR_TABLE(CALL_ID)NOLOGGINGTABLESPACE
DYNIDXP1_LGPCTFREE 15INITRANS
2STORAGE
(
MAXEXTENTS
UNLIMITED
PCTINCREASE
0
BUFFER_POOL
DEFAULT
)NOPARALLEL/
ALTER TABLE CDR_TABLE ADD
CONSTRAINT PK_CDR_TABLE PRIMARY KEY
(CALL_ID) USING INDEX/
Thanks in advance.
-Lizz
Do you Yahoo!?The
New Yahoo! Shopping - with improved product
search
Do you Yahoo!?The
New Yahoo! Shopping - with improved product
search
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Not again :) At least we have to justify our pay :) Waleed Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 5:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparatePlease explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared "M Rafiq" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparateJohnWhat about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis.RegardsRafiqReply-To: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Date: Tue, 14 Oct 2003 13:34:24 -0800Hemant,This applies on 11i only. I would rebuild all indexes supporting theWF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have beenworking on some AOL table(space) problems in the background and noticed thatin 11i by default, we are not be purging _all_ the WF data that we should bepurging. I believe the current Purge routine purges activity rows whosepersistence has expired and are marked 'TEMPORARY' and ignores those thatare COMPLETE (see below). My contention is that it should be deleting oldrows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.You could check this using the following SQLsselect activity_status, count(*)from applsys.wf_item_activity_statusesgroup by activity_status;select item_type,activity_status,count(*)fromapplsys.wf_item_activity_statuses where activity_status='COMPLETE'group by item_type,activity_status;Once the 'correct' purge is complete, the 'holey' indexes will need to berebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM toreasonable levels.Let me know what your install shows up.John KanagarajDB Soft IncPhone: 408-970-7002 (W)Grace - Getting something we do NOT deserveMercy - NOT getting something we DO deserveClick on 'http://www.needhim.org' for Grace and Mercy that is freelyavailable!** The opinions and facts contained in this message are entirely mine and donot reflect those of my employer or customers **-Original Message-Sent: Tuesday, October 14, 2003 8:39 AMTo: Multiple recipients of list ORACLE-LJohn,I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and thetable itself, occassionally].This Saturday I will also be rebuilding some ALR indexes.Which WorkFlow Indexes do you rebuild ?HemantAt 11:44 AM 13-10-03 -0800, you wrote:--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing)._Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: M RafiqINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: bitmap conversion on a index that is not bitmapped ???
It seems that you have tow predicates in the where clause that can take advantage of two different indexes. Also you have "AND" condition that Oracle tries to take advantage of using "BITMAP AND" operation. Since you like the performance of the plan that uses the BITMAP conversion, I can say that index "'AIF.AIF_OUTBOUND_IX01" is more selective than "'AIF.AIF_OUTBOUND_IX03" And in the second plan that one that does not use the BITMAP, if you force it to use index IX01, it would be faster. Degree has nothing to do to the index performance except when doing Index FFS. Regards, Waleed -Original Message-From: Fedock, John (KAM.RHQ) [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 12:50 PMTo: Multiple recipients of list ORACLE-LSubject: bitmap conversion on a index that is not bitmapped ??? Platform is 8.1.7.4, HP-UX 11.00 I have a complex view. After rebuilding all indexes this weekend, the view got much faster. I did a trace on it. Now, a few days later, the view is getting much slower.Yes, an analyze has run since then, doing an 'estimate statistics', but anestimate stats was done after the rebuilds as well.The difference in the explain is below - please note that bitmapped conversion info (in bold): Good plan: ..67 .FIRST ROW..66 ..VIEW OF 'GAPP_USER. (CARD=1 BYTES=13 )..65 ...SORT (AGGREGATE) (CARD=1 BYTES=121 )..64 NESTED LOOPS (COST=53 CARD=1 BYTES=121 )..61 .TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' (COST=51 CARD=1 BYTES=86 )..60 ..BITMAP CONVERSION (TO ROWIDS)..59 ...BITMAP AND..55 BITMAP CONVERSION (FROM ROWIDS)..54 .INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' (NON-UNIQUE) (COST=4 )..58 BITMAP CONVERSION (FROM ROWIDS)..57 .SORT (ORDER BY)..56 ..INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX01' (NON-UNIQUE) (COST=11 )..63 .TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' (COST=1 CARD=65 BYTES=2275 )..62 ..INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) (CARD=65 ) Bad plan: ..61 .FIRST ROW..60 ..VIEW OF 'GAPP_USER. (CARD=1 BYTES=13 )..59 ...SORT (AGGREGATE) (CARD=1 BYTES=121 )..58 NESTED LOOPS (COST=52 CARD=1 BYTES=121 )..55 .TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' (COST=51 CARD=1 BYTES=86 )..54 ..INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' (NON-UNIQUE) (COST=3 CARD=1 )..57 .TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' (COST=1 CARD=65 BYTES=2275 )..56 ..INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) (CARD=65 ) The interesting thing is, the table in question does not have any bit mapped indexes on it. Some notes on MetaLink show other people questioning the same issue, but no concrete info is given. Any ideas out there? TIA. John John Fedock "K" Line America, Inc. www.kline.com * [EMAIL PROTECTED]
RE: SUPPRESS SQL STATEMENTS
Don't run the script while you're inside sqlplus. Run it from outside: sqlplus un/pw @your-script -Original Message- Sent: Monday, October 13, 2003 12:49 PM To: Multiple recipients of list ORACLE-L A script dumps out table info. (sqlplus on aix 4.3.3 and oracle 8.1.7). I cannot suppress the PROMPT@path/scriptname and PROMPT spool off statements from the report output. The script contains both set heading off and set feedback off as part of the formatting. Posssible solutions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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: Khedr, Waleed 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: SUPPRESS SQL STATEMENTS
Use the silent option -S in sqlplus -Original Message- Sent: Tuesday, October 14, 2003 12:29 PM To: Multiple recipients of list ORACLE-L The script is run from a cron job with a ksh script calling the sql from outside sqlplus. I've solved the problem by using sed to cull the offensive lines, but I should be able to use sqlplus formatting statements to give the correct results. I did include message off (from a previous email), but the results remain the same sofar. --- Khedr, Waleed [EMAIL PROTECTED] wrote: Don't run the script while you're inside sqlplus. Run it from outside: sqlplus un/pw @your-script -Original Message- Sent: Monday, October 13, 2003 12:49 PM To: Multiple recipients of list ORACLE-L A script dumps out table info. (sqlplus on aix 4.3.3 and oracle 8.1.7). I cannot suppress the PROMPT@path/scriptname and PROMPT spool off statements from the report output. The script contains both set heading off and set feedback off as part of the formatting. Posssible solutions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller 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: Khedr, Waleed 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: Johan Muller 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: Khedr, Waleed 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: Redos gone crazy--a job for audit?
Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Khedr, Waleed 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: Can someone please verify this for me?
It worked for me 9.2.0.2 Solaris 2.8 But I'm surprised since I always thought that roles are disabled in stored procs. Even it worked for me but it's still disabled in the stored proc after the execute immediate. Waleed -Original Message- Sent: Thursday, October 09, 2003 11:09 AM To: Multiple recipients of list ORACLE-L I have a problem with the new procedure based roles, Secure Application Roles. The following is taken from an example in ASKTOM. Basically, I'm trying to setup a role that is enabled or not by a procedure. The original code from Tom: [EMAIL PROTECTED] l 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role identified by password'; 6* end; [EMAIL PROTECTED] create role new_role identified by password; Role created. [EMAIL PROTECTED] set role none; Role set. [EMAIL PROTECTED] select * from session_roles; no rows selected [EMAIL PROTECTED] set role new_role; set role new_role * ERROR at line 1: ORA-01979: missing or invalid password for role 'NEW_ROLE' [EMAIL PROTECTED] exec turn_on_role; PL/SQL procedure successfully completed. [EMAIL PROTECTED] select * from session_roles; ROLE -- NEW_ROLE [EMAIL PROTECTED] Now, if I try this using what I need: 1 create or replace procedure turn_on_role 2 authid current_user 3 as 4 begin 5 execute immediate 'set role new_role'; 6* end; and then try to run it: exec turn_on_role; I get a ORA-6565 error: Cannot execute SET ROLE from within stored procedure Any ideas what am I missing here? 9.2.0.1, Win2K. Did the usual searches everywhere including Metaclick, nothing that I can relate to... TIA for any help. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Khedr, Waleed 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: Redos gone crazy--a job for audit?
Sample the top sessions from v$sesstat for statname 'redo size' (statistic# 115 in my database) Then joining to v$sql should give you the sql that generates that redo. Waleed -Original Message- Sent: Thursday, October 09, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Khedr, Waleed 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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Khedr, Waleed 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).
Some bug in 9.2
Title: Re: Avoiding full table scan This problem started really to be annoying. Suddenly some PQ processes die and the system is never able to bring them back. If some sqlis submitted and was lucky enough to request a group of processes that include one of this dead processes, it ends up running without PQ at all. We have RAC 9.2.0.2 on Solaris 2.8. Here is the trace content: *** SESSION ID:(343.56680) 2003-10-09 21:14:17.807kxfpg1srv could not start local P008 We get this trace in the udump, and nothing goes to bdump. Did any of you experience this problem? Thanks Waleed
RE: USERENV('SESSIONID') on RAC
They work fine for me on RAC 9.2.0.2
Does this help:
select unique sid from v$mystat
Waleed
-Original Message-
Sent: Thursday, September 25, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
Hey all,
Is there a way to get your own executing program from a 9.2.0.4 RAC node?
USERENV('SESSIONID') and SYS_CONTEXT('USERENV','SESSIONID') each return a
big fat zero on RAC.
My ultimate goal is to get the executing session's program, and the only
place I can find that info is in V$SESSION. And the only way I know to get
the current session's row from V$SESSION is to join it with
USERENV('SESSIONID'). If there's a better/different way to do this, I'm
listening.
Thanks!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
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 copy recursive files in Windows? like unix cp -r
xcopy -Original Message- Sent: Wednesday, October 08, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hi List: How to copy recursive files in Windows? like unix cp -r Thanks in advance, Sami __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA 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: Khedr, Waleed 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: EMC striping question
This could be true on the request level. But it will not stop it from pipelining other requests on the spindle level, so that when spindle A is done for request 1 it works on request 2. This increases the overall throughput of the system. Then using Async IO should help here. I'm talking about EMC raid 0+1 hardware striping, if you are talking about RAID-S, this will be a different story because the parity bits needs to be maintained. Regards, Waleed -Original Message- Sent: Friday, October 03, 2003 9:50 AM To: Multiple recipients of list ORACLE-L Hi All, Today I saw an archived thread on orafaq about striped volumes in an EMC Symmetrix. Gaja mentioned that writing to a striped volume is performed in a sequential fashion i.e. spindle B will not start writing block 2 before spindle A has completed writing block 1. Is this still true for a Symmetrix with 5568 firmware? Perhaps it's a better idea to let the OS handle the striping? Regards, Hans de Git _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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: Khedr, Waleed 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).
Sequence and order by
Is this documented anywhere? SQL drop sequence test_seq; Sequence dropped. SQL SQL create sequence test_seq; Sequence created. SQL SQL select test_seq.nextval 2 from dual; NEXTVAL -- 1 SQL SQL select test_seq.nextval 2 from dual 3 order by dummy; select test_seq.nextval * ERROR at line 1: ORA-02287: sequence number not allowed here Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Sql query : select max timestamp value from table
select ip, max(timestamp) from table group by ip; -Original Message-From: Johan Muller [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 10:45 AMTo: Multiple recipients of list ORACLE-LSubject: Sql query : select max timestamp value from table I have multiple timestamps values for single ip in a table, I need the max(timestamp)for each ip I select out. example: timestamp ip 2003-09-29 13:20:23 68.209.182.42003-09-29 13:20:44 68.209.182.42003-10-02 12:53:38 68.209.182.42003-10-02 12:35:06 68.75.94.1582003-10-02 12:52:03 68.97.33.69 Thus select distinct ip, max(timestamp) from table group by ip, timestamp; returns every timestamp value per ip. Any ideas on how to get only the max(timestamp) for each ip?
RE: Physical I/O and databases other than oracle
Title: RE: Physical I/O and databases other than oracle So to look good, I should unplug all the CPU boards except one or two to end up with CPU limitation :) Regards, Waleed -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]Sent: Thursday, October 02, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Physical I/O and databases other than oracle According to one recently published source*, in a well-tuned database system, the server should be CPU-limited. The reasoning here is that in a perfectly tuned system, the other bottlenecks of I/O, network, etc. have been eliminated, so the system is then limited by the speed and number of CPUs. This is an ideal system, of course, and we all know that it is common to have less than ideal numbers of disks or I/O controllers to spread the load. * "The Art and Science of Oracle Performance Tuning", Christopher Lawson, 2003, p.184. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com "the most trusted source for STORAGE MANAGEMENT SERVICES" The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you.
Deterministic Functions: am I missing something?
drop table test_det; create table test_det (c1 date); create or replace function f2 (p1 in number, p2 in number) return number deterministic is pragma autonomous_transaction; begin insert into test_det values (sysdate); commit; return(p1 * p2); end; / select f2 (1,3) from dual where f2 (1,3) = 3; F2(1,3) -- 3 select * from test_det; C1 - 02-OCT-03 02-OCT-03 It did not save an execution even in the same sql call! Oracle 9.2.0.2 Solaris 2.8 Regards, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Seems odd to me....(bug?)
Could it be that the table has a trigger and it's trying to insert into
another table that does not exist?
Can you read from the table?
Waleed
-Original Message-
Sent: Thursday, October 02, 2003 6:40 PM
To: Multiple recipients of list ORACLE-L
Oracle EE 8.1.7.2
HP-UX 11
Can anyone explain this?
1* INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)
SQL /
INSERT INTO EMPLOYEE_ROLE VALUES ('C', 'CSR',NULL)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
1* select count(*) from user_tables where table_name='EMPLOYEE_ROLE'
SQL /
COUNT(*)
--
1
SQL CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES;
CREATE TABLE EMPLOYEE_ROLE AS SELECT * FROM USER_TABLES
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Any ideas?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chris Stephens
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: Khedr, Waleed
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: vertical serches on a table - how to
be a banana and a pineapple at the same time for a particular ID. select ID, col1, col2 from tableX where (col1='banana') or ((col1='mango' and col2='banana') and (col1='grape' and col2='pineapple') ); Any idea how I can do a vertical search on the table. Thanks for any help you can provide. susan _ Help protect your PC. Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Susan Tay 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: Khedr, Waleed 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: interesting sql question
select pn.name from (select /*+ no_merge */ count(*) boat_cnt from boat) bt, bid bd, person pn where bd.sid = pn.sid group by pn.name, boat_cnt having count(bd.boat_id) = boat_cnt Waleed -Original Message- Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Im taking a database theory class(no I dont need help with my homework). There is an interesting query in the book that I have never seen posed before. The solution would be hideously slow if there was even a moderate amount of data in the tables. How would you write it? Given 3 tables: and columns in the tables: TABLE: Person Primary Key: SID COLUMN: NAME TABLE: BIDS Primary Key: BID Foreign Key: SID FOREIGN KEYT: BOAT_ID Column: Date Boat: Primary Key: BOAT_ID Column: Color Find any person who has reserved all the boats. The I dont have the solution with me, but there is a 'NOT EXISTS', then in the subquery there is a minus and a correlated 'where' clause.'. That query wouldnt move. How would you solve this? Also, according to the 'SQL Standard', SQL is supposed to support op codes such as 'ALL' or 'ANY' So you can say: Find all people who are older than any person with blue eyes. Or find all the people who are older than 'ALL' the people with blue eyes. Just to reiterate. Not looking for help with my homework. My professor isnt an Oracle guy so he doesnt know. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: Oracle Compress Option
Title: RE: Oracle Compress Option Disk is not cheap if you pay for high availability configuration. I compress historical data on daily basis and was able to save 70 percent of the disk space. Imagine the amount of savings for five TB. Two major issues: 1) Oracle says updates will be slow on compressed tables, but I say don't even try to update a compressed table, uncompress first otherwise you will end up with a segment that is not good at all for scattered reads. 2) You can not add columns to the table when it's compressed, so if you compressed a big table and need a new column you need to recreate the table without compression. So adding many extra columns before compression is a good idea. It's mainly good for data warehouses applications. Regards, Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, September 25, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle Compress Option I think 9202 doesn't like to export compressed tables in direct mode ... so watch out for that ... I implemented, tested and next day reverted back to regular tables due to this export issue. Disk is cheap. A BAARF party member wannabe !! 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- From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 24, 2003 10:05 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Compress Option "Compress to impress?" by Julian Dyke is a good presentation on this topic (see for instance http://www.ukoug.org/calendar/jan03/jan30ab.htm). I do have the article - 202 K with no compression, 147 K with compression :). Let me know if you're interested, and I'll email it directly to you. Mogens [EMAIL PROTECTED] wrote: Does anybody has any experience with Oracle 9I compression option. I did some test on 9202 with a table of more 14 million rows. Table has total 7 indexes. Surprising both table and indexes are using more space after compression. Before compression space used is 13064MB and after compression 13184MB. In both the cases I did export from source table and stored in two different tablespaces. Any insight on that and any disadvantages of using that. Thanks
RE: SQL AREA and LIBARARY CACHE size?
see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams 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). DISCLAIMER: The information contained in this message is intended only and solely for the addressed individual or entity indicated in this message and for the exclusive use of the said addressed individual or entity indicated in this message (or responsible for delivery of the message to such person) and may contain legally privileged and confidential information belonging to Tata Consultancy Services. It must not be printed, read, copied, disclosed, forwarded, distributed or used (in whatsoever manner) by any person other than the addressee. Unauthorized use, disclosure or copying is strictly prohibited and may constitute unlawful act and can possibly attract legal action, civil and/or criminal. The contents of this message need not necessarily reflect or endorse the views of Tata Consultancy Services on any subject matter. Any action taken or omitted to be taken based on this message is entirely at your risk and neither the originator of this message nor Tata Consultancy Services takes any responsibility or liability towards the same. Opinions, conclusions and any other information contained in this message that do not relate to the official business of Tata Consultancy Services shall be understood as neither given nor endorsed by Tata Consultancy Services or any affiliate of Tata Consultancy Services. If you have received this message in error, you should destroy this message and may please notify the sender by e-mail. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: ManojKr Jha 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: Khedr, Waleed 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: Oracle Compress Option
Title: RE: Oracle Compress Option Something else I forgot, full segment scans becomes faster, since he segment is 70 percent smaller. So this could help balancing resource utilization between the CPUs and IO. Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, September 25, 2003 9:50 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle Compress Option Waleed, I get your point ... We have 6 RAC instances that run active-active ... and compared to availability requirements, we (incl management) decided that disk is cheap. I guess it is relative ... 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-From: Khedr, Waleed [mailto:[EMAIL PROTECTED]Sent: Thursday, September 25, 2003 9:35 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle Compress Option Disk is not cheap if you pay for high availability configuration. I compress historical data on daily basis and was able to save 70 percent of the disk space. Imagine the amount of savings for five TB. Two major issues: 1) Oracle says updates will be slow on compressed tables, but I say don't even try to update a compressed table, uncompress first otherwise you will end up with a segment that is not good at all for scattered reads. 2) You can not add columns to the table when it's compressed, so if you compressed a big table and need a new column you need to recreate the table without compression. So adding many extra columns before compression is a good idea. It's mainly good for data warehouses applications. Regards, Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Thursday, September 25, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Oracle Compress Option I think 9202 doesn't like to export compressed tables in direct mode ... so watch out for that ... I implemented, tested and next day reverted back to regular tables due to this export issue. Disk is cheap. A BAARF party member wannabe !! 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- From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 24, 2003 10:05 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle Compress Option "Compress to impress?" by Julian Dyke is a good presentation on this topic (see for instance http://www.ukoug.org/calendar/jan03/jan30ab.htm). I do have the article - 202 K with no compression, 147 K with compression :). Let me know if you're interested, and I'll email it directly to you. Mogens [EMAIL PROTECTED] wrote: Does anybody has any experience with Oracle 9I compression option. I did some test on 9202 with a table of more 14 million rows. Table has total 7 indexes. Surprising both table and indexes are using more space after compression. Before compression space used is 13064MB and after compression 13184MB. In both the cases I did export from source table and stored in two different tablespaces. Any insight on that and any disadvantages of using that. Thanks
RE: equivalent for isdate, isnumeric
Boolean is A PL/SQL data type won't work in sql. Deterministic is nice to have to reduce the number of times this function gets called for the same value. Regards, Waleed -Original Message- Sent: Thursday, September 25, 2003 10:05 PM To: Multiple recipients of list ORACLE-L Tanel Poder wrote: Boolean is a datatype existing and usable in Oracle. Deterministic is an Oracle way to tell a function is deterministic, i.e. always returning the same result on the same input. Required for FBIs for example. http://tahiti.oracle.com Could you please kindly provide an example of its usage in SQL. Please create an FBI using the original function below (as is, no modifications or wrappers). It would be really intersting how deterministic functionality would work for a PL/SQL function that returns BOOLEAN datatype. Thank you! -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. boolean is not SQL datatype and it's unclear what deterministic means here. Mladen Gogala wrote: create or replace function isnumeric(str varchar2) return boolean deterministic as num number:=0; begin num:=to_number(str); return(true); exception when others then return(false); end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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: equivalent for isdate, isnumeric
I was just explaining Deterministic independently of the function. -Original Message- Sent: Friday, September 26, 2003 1:50 AM To: Multiple recipients of list ORACLE-L Khedr, Waleed wrote: Boolean is A PL/SQL data type won't work in sql. Deterministic is nice to have to reduce the number of times this function gets called for the same value. Called from where? :) I think Tanel, Mladen and you missed the ironical point -- if it can't be called from SQL why it is a deterministic one? It was very simple question asked for fun -- Mladen who said: Hey, I'll sue you for using my code. My code is fair and balanced and you cannot use it without paying royalties. :) So, he put deterministic clause then. :) P.S.: Tanel, I do not need an example, it was a joke. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -Original Message- Sent: Thursday, September 25, 2003 10:05 PM To: Multiple recipients of list ORACLE-L Tanel Poder wrote: Boolean is a datatype existing and usable in Oracle. Deterministic is an Oracle way to tell a function is deterministic, i.e. always returning the same result on the same input. Required for FBIs for example. http://tahiti.oracle.com Could you please kindly provide an example of its usage in SQL. Please create an FBI using the original function below (as is, no modifications or wrappers). It would be really intersting how deterministic functionality would work for a PL/SQL function that returns BOOLEAN datatype. Thank you! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: Khedr, Waleed 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 generate unique file names on Windows.
You can use sqlplus to generate the script for you. -Original Message- [mailto:Murali_Pavuloori/[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 1:45 PM To: Multiple recipients of list ORACLE-L Hello, I am trying to write a script on windows that would export the db every night. Can someone tell me how to generate unique file names on windows... What I am looking for is the windows equivalent of echo `date +%m%d%y` Thanks in advance. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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: Khedr, Waleed 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: possible to have a primary key with a bitmap indx?
Restrictions on Unique Indexes You cannot specify both UNIQUE and BITMAP -Original Message- Sent: Tuesday, September 23, 2003 5:35 PM To: Multiple recipients of list ORACLE-L It's not possible: SQL create bitmap index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate * ERROR at line 1: ORA-01418: specified index does not exist SQL drop index emp_empno_b; Index dropped. SQL create index emp_empno_b on emp(empno); Index created. SQL alter table emp add constraint emp_pk primary key(empno) using index emp_empno_b novalidate; Table altered. SQL -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 4:55 PM To: Multiple recipients of list ORACLE-L Subject: possible to have a primary key with a bitmap indx? is it possible to have a primary key that is enforced with a bitmap index? if so what is the syntax? -- 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: Khedr, Waleed 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: weird
You need to monitor it on the transaction level not rolled up to the user level. Waleed -Original Message- Sent: Friday, September 19, 2003 11:10 AM To: Multiple recipients of list ORACLE-L No question here. Just something weird. This is a long-running insert with NO NONE ZERO ZIP ZILCH NADA commit. It makes me wonder if something weird is going on, or if I am overlooking something in the query. SQL select a.username,sum(b.used_ublk) x from v$session a, v$transaction b where a.taddr=b.addr group by a.username; USERNAMEX -- -- SYSTEM418 1 row selected. SQL / USERNAMEX -- -- SYSTEM893 1 row selected. SQL / USERNAMEX -- -- SYSTEM 2 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3181 1 row selected. SQL / USERNAMEX -- -- SYSTEM 3204 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Khedr, Waleed 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: Anyone have a copy of DUL ??
Why all the interest about reading Oracle files? There is no magic in doing this! It does not matter how sophisticated the server is, the data simply resides in data files. You can view it easily using: cat file | strings Regards, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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: parallel processes
2 producers 2 consumers -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 23, 2003 8:44 PMTo: Multiple recipients of list ORACLE-LSubject: parallel processes while rebuilding indexes i gave parrallel 2 . But I see there are four ora_p00 processes . ??? -ak
RE: Interview Questions for a Unix Solaris System Admin
you forgot to list the answers :) Waleed -Original Message- Sent: Tuesday, July 15, 2003 3:14 PM To: Multiple recipients of list ORACLE-L Okay, here are my favorites for senior candidates (I'm giving all my secrets away...): 1) What is an inode? Bonus: What important piece of file information is NOT stored in the inode? 2) What is priority paging and how does it work? (mildly dated, but useful if they claim to have been around for a while) 3) What does sr stand for in vmstat output? 4) How would I configure the gigabit ethernet interface to force it to be full duplex? 5) How does RAID-5 work? Bonus question: how does raid-4 work? Extra-extra bonus question: how does raid-3 work? 6) What's the difference between the passwd and the shadow files? 7) What's the difference between the dsk and rdsk devices in /dev? Bonus question: what's the difference between a block and a character device? 8) How do journaling filesystems work? 9) What's the difference between ssh and telnet? Why is one preferable over the other? 10) What's the difference between the e4000 and the e4500 (or e6000 and e6500, etc. - also a bit dated, but there's still a million of the things out there) 11) What happens on an E6500 when I add boards in the bottom two slots? (I won't ask this if the person has never touched an E6500) 12) On an Sbus e-class I/O tray, what performance considerations do I have to keep in mind when I'm installing Sbus cards? 13) Why is NIS bad? 14) What's the difference between TCP and UDP? 15) How does DNS work? Bonus question: is DNS TCP or UDP? Then I usually throw in some amorphous questions: tell me about a performance problem you tracked down and solved, how do you normally secure a freshly installed Solaris server, etc. Then I follow up with product specific questions - oracle, sun cluster, veritas volume manager, storage, etc. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Interview Questions for a Unix Solaris System Admin question #1: Do you realize that your DBA is a God, and you will obey his/her edicts without question? question #2: Are you aware of the daily offering of food/beer required to keep in your God's (DBA's) good graces? etc... Scott Shafer San Antonio, TX 210.581.6217 -Original Message- From: M.Godlewski [SMTP:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 1:30 PM To: Multiple recipients of list ORACLE-L Subject:Interview Questions for a Unix Solaris System Admin I've been asked to interview a system admin candidate for our Solaris shop. I've search Google and altavista, but haven't come up with any after 1999 interview questions. Does anyone have a list of interview question or a link to some? tia M _ Do you Yahoo!? The New Yahoo! Search http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com - Faster. Easier. Bingo. -- 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: Matthew Zito 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: Khedr, Waleed 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
RE: Compressed tables
Title: RE: Compressed tables Sorry, I think it's here too: http://otn.oracle.com/products/bi/pdf/o9ir2_compression_twp.pdf Also one of the things I like about it is moving some of the disk IO system contention to the CPUs when there is extra CPU resources that is not being utilized. Waleed -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 09, 2003 2:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Compressed tables Waleed, the attached paper was compressed by Listguru ... for you, with good compression ratio too. 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- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 09, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: Compressed tables I'm using it for six months now without problems. I was able to save more than 70% of the used disk space in big data warehouse project. Attached is a pdf paper that might be helpful: Regards, Waleed
RE: Compressed tables
I'm using it for six months now without problems. I was able to save more than 70% of the used disk space in big data warehouse project. Attached is a pdf paper that might be helpful: Regards, Waleed Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company -Original Message- Sent: Wednesday, July 09, 2003 2:10 PM To: Multiple recipients of list ORACLE-L So, there I am, reading about 9iR2's compressed tables feature at: http://otn.oracle.com/oramag/webcolumns/2003/techarticles/poess_tablecomp.ht ml (wrap URL if necessary, or go to http://tinyurl.com/gg3p ) Other than it being the best thing since sliced bread, what's the downside? Are there restrictions similar to compressed indexes (e.g. no online rebuilds)? How much does CPU usage go up (all compression/decompression must consume CPU)? Just curious, really. I have an immediate aversion to any product that has a term with the initials CF and was wondering if compressed tables is a CF or not. TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA Disclaimer: This message is probably virus-free! -- 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). o9ir2_compression_twp.pdf Description: Binary data
RE: WARNING: Re: [suse-oracle] AIO trouble
me too. But it was blocked and filtered before coming to me! -Original Message- Sent: Thursday, June 05, 2003 4:55 PM To: Multiple recipients of list ORACLE-L FYI - When this message came in to me it had the PE_BUGBEAR.B virus. Mike -Original Message- Sent: Thursday, June 05, 2003 11:07 AM To: undisclosed-recipients Michael Hasenstein wrote: Which SuSE Linux? SLES 8 Which kernel (run rpm -q k_smp)? k_smp-2.4.19-195 What hardware? 2 Processor Xeon 2GHz 2 Gb RAM a this time cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) 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: Khedr, Waleed 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: Interesting!
I think it's good to have (forgive me Jared :)).
It reminds me with Pascal language.
Waleed
-Original Message-
Sent: Tuesday, June 03, 2003 9:40 PM
To: Multiple recipients of list ORACLE-L
My first real PL/SQL program used this technique. Of course since it was my
first program I ran into a very annoying variable scope issue. Of course
after spending hours debugging the code, I realized I should have taken the
advice of our DBA, the infamous Jared Still, and just used a package. I
have since looked askew at the nesting of procedures. I can't remember the
actual error I encountered. It probably had nothing or very little to do
with my nested procedures, but in my mind I can't consider this to be a good
practice. A package does the same job, and so much cleaner.
P.S. I think this was the first time I noted Jared's annoying habit of
being right
Steve McClure
-Original Message-
Waleed
Sent: Tuesday, June 03, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L
I've just discovered this, never thought if it was possible or not.
Just wanted to share it with you, so forgive me if you know it already.
Procedure can contain other procedures:
create or replace procedure test_test1 as
procedure test_test2 as
begin
dbms_output.put_line('hello2');
end;
function c1 ( p1 in number) return number as
begin
return(p1 * 2);
end;
begin
dbms_output.put_line('hello1');
test_test2;
dbms_output.put_line( c1(10));
end;
Regards,
Waleed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
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: Steve McClure
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: Khedr, Waleed
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).
Interesting!
I've just discovered this, never thought if it was possible or not.
Just wanted to share it with you, so forgive me if you know it already.
Procedure can contain other procedures:
create or replace procedure test_test1 as
procedure test_test2 as
begin
dbms_output.put_line('hello2');
end;
function c1 ( p1 in number) return number as
begin
return(p1 * 2);
end;
begin
dbms_output.put_line('hello1');
test_test2;
dbms_output.put_line( c1(10));
end;
Regards,
Waleed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Khedr, Waleed
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: question about large pool
From the doc: LARGE_POOL_SIZE lets you specify the size (in bytes) of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. (Parallel execution allocates buffers out of the large pool only when PARALLEL_AUTOMATIC_TUNING is set to true.) Waleed -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed 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
RE: Bulk collect got truncated? RESOLVED
regarding practical limits on PL/SQL tables? We've got an issue here which may potentially invalidate data, no error messages. Here is the case: Oracle9i Enterprise Edition Release 9.0.1.4.0, Solaris. DECLARE TYPE t_subsvcparmid IS TABLE OF sub_svc_parm.sub_svc_parm_id%TYPE; esubsvcparmid t_subsvcparmid; BEGIN SELECT /*+ index(sub_svc_parm, sub_svc_parm_ix2) */ sub_svc_parm_id BULK COLLECT INTO esubsvcparmid FROM sub_svc_parm WHERE parm_id = 10; DBMS_OUTPUT.PUT_LINE(esubsvcparmid.LAST); .. The select is expected to return close to 200K records, and usually it does, but sometimes number of records is restricted to 65535 = 2^16-1, it can be seen both from DBMS_OUTPUT and 10046 trace: FETCH #2:c=437,e=33876545,p=14895,cr=66960,cu=0,mis=0,r=65535,dep=1,og=4,tim=1 052942377932150 ^^^ Did anybody see such weird behavior? TIA Vadim Gorbounov Liberate Tech. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim 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: Khedr, Waleed 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: Gorbounov,Vadim 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: Khedr, Waleed 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: Gorbounov,Vadim 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: Mark Moynahan 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: Gorbounov,Vadim INET: [EMAIL
RE: skip scan index
A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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: skip scan index
Skip scan will show in the execution plan as skip scan. Not true that it will show as regular index scan. Waleed -Original Message- Sent: Wednesday, May 28, 2003 1:20 PM To: Multiple recipients of list ORACLE-L A skip scan can be a index scan, full scan or range scan type access. It simply allows a unusable column to be deselected from the index (for lack of a better word) during these operations. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 11:15 AM A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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: Freeman Robert - IL 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: Khedr, Waleed 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: skip scan index
Easy test case: CREATE TABLE TEST_SKIP ( C1 NUMBER NOT NULL, C2 NUMBER NOT NULL, C3 NUMBER NULL ); CREATE UNIQUE INDEX TESTSKIP1 ON TEST_SKIP(C1,C2); select --+ index_ss(test_skip, ) c1,c2,c3 from test_skip where c2 = 10; OPERATIONOPTIONS OBJECT_NAME SELECT STATEMENT [NULL] [NULL] TABLE ACCESS BY INDEX ROWID TEST_SKIP INDEXSKIP SCAN TESTSKIP1 Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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: skip scan index
I'm talking about the way it get executed not the statistics or the cost. The cost is completely dependent on the distribution of the data. For example if we have table (c1 number, c2 number) and a primary key on (c1, c2). And the data looks like this: c1 c2 A 1 A 2 A 3 A 4 . . . . A A 1 B 1 B 2 B 3 . . . . . . B B 1 And I run this sql using skip scan: select c1,c2 from table where c2 = 100 This will be almost similar if you execute this (two unique lookups): select c1,c2 from table where c1 = 'A' and c2 = 100 union all select c1,c2 from table where c1 = 'B' and c2 = 100 There will be extra cost related to finding the unique value of c1 but will be much cheaper compared to full index scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 2:52 PM To: Multiple recipients of list ORACLE-L True enough, it will show as index skip scan, but if you take a look at the statistics, you'll see that the nubmer of blocks read roughly corresponds to the number of blocks in the index. It is also logical, because without the first column, the only way to find the desired key is to read the whole index. Indexes are B*tree structures which are searched using modified version of binary search. The ordering is so called lexicographical order, which means that the column 1 is compared first, then column 2 if there is equality in the column 1 and so forth until we reach differing columns. Without knowing column 1, you MUST read them all and see which ones contain the sought for column 2. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 2:17 PM To: Multiple recipients of list ORACLE-L Skip scan will show in the execution plan as skip scan. Not true that it will show as regular index scan. Waleed -Original Message- Sent: Wednesday, May 28, 2003 1:20 PM To: Multiple recipients of list ORACLE-L A skip scan can be a index scan, full scan or range scan type access. It simply allows a unusable column to be deselected from the index (for lack of a better word) during these operations. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 11:15 AM A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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
RE: skip scan index
to be deselected from the index (for lack of a better word) during these operations. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 11:15 AM A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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: Freeman Robert - IL 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: Khedr, Waleed 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: Gogala, Mladen 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
RE: skip scan index
means that the column 1 is compared first, then column 2 if there is equality in the column 1 and so forth until we reach differing columns. Without knowing column 1, you MUST read them all and see which ones contain the sought for column 2. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 28, 2003 2:17 PM To: Multiple recipients of list ORACLE-L Skip scan will show in the execution plan as skip scan. Not true that it will show as regular index scan. Waleed -Original Message- Sent: Wednesday, May 28, 2003 1:20 PM To: Multiple recipients of list ORACLE-L A skip scan can be a index scan, full scan or range scan type access. It simply allows a unusable column to be deselected from the index (for lack of a better word) during these operations. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 5/28/2003 11:15 AM A short cut to test the new feature is using the hint index_ss(table,index). Index skip scan is not an index scan or fast full scan. Regards, Waleed -Original Message- Sent: Wednesday, May 28, 2003 7:00 AM To: Multiple recipients of list ORACLE-L Okay, I have a developer here who has been reading the docs (this can be dangerous!) we are adding functionality to one of our applications, this will involve using multiple fulfillment houses, so we'll be adding the fulfillment vendor id to the order table. Easy, this is not a problem. We want to be able to search by order date and by fulfillment vendor id/order date Traditional design would be to add two indexes: one on order date, and a concatenated one on fulfillment vendor id/order date. The developer is telling me to create a skip scan index instead of two different ones. MY reading in the FM tells me that skip scan index is not a type of index, but rather a way Oracle uses to use an index even if the leftmost column is not in the query. Is there any benefit in my building only the one index? Our order volume is not so high (and never will be) that there is a visible performance impact if I have the two indices. This is 9i, 9.2.0.1, will be upgrading to 9.2.0.2 in the near future. Solaris Any suggestions/comments/war stories would be appreciated. I know I've seen Jonathan post on skip scan indexes before but I can't find the specific reference at the moment. Rachel __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- 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: Khedr, Waleed 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: Freeman Robert - IL 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: Khedr, Waleed 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
RE: lengthy URL's
I just tried it, and surprisingly it was blocked from the firewall. Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company -Original Message- Sent: Wednesday, May 28, 2003 6:15 PM To: Multiple recipients of list ORACLE-L Just a slightly OT post here. It isn't unusual when writing to this list to come up with a few URL's to include in an email. The problem at times is that the URL's can easily be 200 characters in length, meaning that those reading your email must cut and paste the URL's to make use of them. An easy solution to this is to make use of www.tinyurl.com It's easy: cut your lengthy URL from the browser, go to www.tinyurl.com, paste your lnnng URL, and get a short one in return. Anyone using the tiny URL will be redirected to the original. It's a free service, but they accept donations. Personally, I've been using it so much I gave them $10 the other day. Jared -- 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: Khedr, Waleed 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: skip scan index
Thanks for the update.
You still have a good point about the structure and the format of the
branching blocks!
My guess (for my example), the branching blocks might look like this:
Br1from: A,1to A,5000
Br2from: A,5001 to A,1
Br3from: B,1to B,5000
Br4from: B,5001 to B,1
It is easy for Oracle to say that c1 has(or assume it has) unique values
('A', 'B').
Regards,
Waleed
-Original Message-
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 5/28/03 8:57 PM
Here is the idea:
Index test_skip1 is located in the tablespace INDX which has
one file, FILE#=5
I restart the database, execute your query, then see V$FILESTAT for
blocks read.
(select PHYBLKRD from v$filestat where file#=5;)
Then restart the database, execute query asking for a fast full scan and
see
how many blocks do get read. If the number is the same, then the
conclusion is inevitable.
So, here we go:
SQL set autotrace on explain
SQL select /*+ index_ss(test_skip1 ) */
c1,c2
from test_skip1 where c2 = 100;
23
C1 C2
-- --
1100
2100
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=302 Bytes=78
52)
10 INDEX (SKIP SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=11 Car
d=302 Bytes=7852)
SQL select PHYBLKRD from v$filestat where file#=5;
PHYBLKRD
--
10
---DATABASE RESTART---
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production
SQL set autotrace on
SQL select /*+ index_ffs(t test_skip1_pk ) */
c1,c2
from test_skip1 t where c2 = 100;
23
C1 C2
-- --
1100
2100
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=302 Bytes=785
2)
10 INDEX (FAST FULL SCAN) OF 'TEST_SKIP1_PK' (UNIQUE) (Cost=4
Card=302 Bytes=7852)
Statistics
--
300 recursive calls
0 db block gets
777 consistent gets
724 physical reads
0 redo size
464 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
2 rows processed
SQL select PHYBLKRD from v$filestat where file#=5;
PHYBLKRD
--
722
That means that fast full scan will read 722 blocks where skip scan will
read only 10,
which means that you were right and I was wrong. Obviously, my
metodology was incorrect
or 9.2.0.1 database that I've tested it on has had a bad bug, which
would really be
surprising and unusual. Anyway, you are right. That, in turn, implies
that oracle
indexes are not classic B*Tree structures as I was lead to believe but
are spiked with
an unknown liquor. Thanks for helping me clarify this.
On 2003.05.28 18:29 Khedr, Waleed wrote:
It's like any other execution plan, good in certain data distributions
and
bad in others.
But I do not think it's correct that skip scan requires reading the
whole
index (it's even clear in this test).
Waleed
-Original Message-
Sent: Wednesday, May 28, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L
OK. I don't have the 9i instance that I can use for testing right now,
but tonight, at home, I'll give you the counter example. The bottom
line
is that the only way to execute a skip scan with a B*Tree index is to
go and read it whole. No other way.
Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, May 28, 2003 4:40 PM
To: Multiple recipients of list ORACLE-L
Not true, try this:
create table test_skip1 ( c1 number,c2 number, primary key (c1,c2));
begin
for i in 1..10 loop
insert into test_skip1 values (1,i);
insert into test_skip1 values (2,i);
end loop;
end;
alter session set sql_trace = true;
select --+ index_ss(test_skip1, )
c1,c2
from test_skip1
where c2 = 100;
select blocks from dba_segments where segment_name = 'SYS_C0038241'
-- pk
index
blocks = 384
---
-- From the tkprof output
---
select --+ index_ss(test_skip1, )
c1,c2
from test_skip1
where c2 = 100
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch1 0.00 0.00 0 8 0
2
