Does the case of an Oracle query statement affect query performance?
Hi, Pardon me for such a naive question, coz I am a novice when it comes to Oracle. This is basically got to do with how Oracle parses a query. Consider the following queries: a) SELECT column1, column2 FROM table WHERE column0 = 5; b) SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; Scenario 1: I use the naming convention a) for ALL my queries Scenario 2: I use the naming convention b) for ALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. -- BEGIN:VCARD VERSION:2.1 N:Datta;Shantanu FN:Shantanu Datta ORG:Hurix Systems Pvt. Ltd. TITLE:Software Engineer TEL;WORK;VOICE:+91 (22) 692-3888 X 243 TEL;WORK;FAX:+91 (22) 826-5948 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;231, Solitaire Corporate Park,=0D=0A151, Andheri-Kurla Road,;ANDHERI (E);M= UMBAI, Maharashtra;400093;India LABEL;WORK;ENCODING=QUOTED-PRINTABLE:231, Solitaire Corporate Park,=0D=0A151, Andheri-Kurla Road,=0D=0AANDHERI (E= ), MUMBAI, Maharashtra 400093=0D=0AIndia URL: URL:http://www.hurix.com EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20020510T085122Z END:VCARD
Memory Based FS on Solaris 8
With Online Redo Logfiles placed on Memory Based File system i.e. tmpfs on Solaris 8 with Oracle 8.1.7.2) can Heavy / Data Intensive SQL Loads (DIRECT=TRUE , PARALLEL) Cause Other regular File systems to Crash ? SQL Loading happening for 5 Tables Concurrently Also Within Each Table 16 Parallel SQL Loads happening Data SQL Loaded into Tables of Sizes from 2-5 GB Thanks -Original Message- Sent: Tuesday, June 25, 2002 4:53 AM To: Multiple recipients of list ORACLE-L I hesitated mentioning that parameter in this forum, but I figured what the heck? Could be fun, in a sick way... :-) Once I was teaching a DBA class and mentioned "_DISABLE_LOGGING". Immediately, I saw every head in the class look down, scribbling furiously! I had to backtrack very quickly and warn of the consequences of disabling redo logging (i.e. database corruption if not shutdown normally for any reason)... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, June 24, 2002 2:48 PM > Hi Tim > > Yes, I have tried the _disable_logging, does not work on all platforms. DB > starts up fine, but redo log is generated, evidenced by log switching going > on. > > Also if I do a normal DML (large-ish one to verify), then dump the redo log, > I see my transaction there, so for a 420R, running Solaris8 and Oracle > 9.0.1, it would seem that _disable_logging does not work. > > I don't want to complicate the picture even further with transportable > tablespaces, which would mean that I would need to store all dependent > objects (in this case indexes only) in the same tablespace, which I could > easily achieve by rebuilding all indexes using a dynamic SQL. > > Informatica BTW does not only do single level inserts, version 5.0 onwards > has a 'bulk load' feature, but I am not sure what this actually does. > Previously Sagent also had a 'direct load' switch, which meant that it wrote > all of its data to large (very large) flat files and then used Sql*Loader > direct path to load. Fast, but Sagent at the time was very unreliable, > because on identical runs, it would sometimes load all the data, sometimes > only a portion, and every time, would report no errors and everything hunky > dory, until you went looking for your data. I remember that took me about a > week of arguing to prove that Sagent was at fault. > > Thanks for the suggestion of the Non volatile RAM (NVRAM) unit, it makes the > most sense. I will suggest this to my damagers. > > Regards: > Ferenc Mantfeld > Senior Performance Engineer > Siebel Performance Engineering > Melbourne, 3000, VIC, Australia > Only Robinson Crusoe had all his work done by Friday > > > -Original Message- > Sent: Sunday, 23 June 2002 9:03 PM > To: Multiple recipients of list ORACLE-L > > > Have you considered setting "_DISABLE_LOGGING = TRUE" > instead? It could be just as disastrous... ;-) > > Buying an NVRAM unit would probably be more sensible, since > at least then you have some probability of the file-system > on such a unit surviving node failure or restart. > > I don't use Informatica, but I believe it mainly does > single-row inserts, so not using the APPEND hint is a > blessing anyway. After all, who likes one row in each > database block? However, I could be wrong about that and it > may actually be performing multi-row/array insertions... > > I don't know what your loads are like, but how about > something like this instead? > > - create a small database with _DISABLE_LOGGING set to > TRUE > - use Informatica to load into a tablespace on that small, > sacrificial db > - use "transportable tablespace" to copy the tablespace to > your real DW > > Just an idea (better you than me to try it!)... > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Sunday, June 23, 2002 8:53 PM > > > > Hi All > > > > does anyone have any white paper or info on how to > configure a dedicated > > portion of real memory as a virtual drive on Solaris ? I > want to move my > > online redo logs (4 X 128 M single threaded) for a 300 GB > DW onto it, to > > speed up Informatica ETL, since Informatica does not allow > me to specify /*+ > > APPEND */ mode of insert. I know I will not bypass the SQL > layer this way, > > but at least, the LGWR will be writing to memory instead > of disk. Thanks in > > advance. > > > > Regards: > > Ferenc Mantfeld > > Senior Performance Engineer > > Siebel Performance Engineering > > Melbourne, 3000, VIC, Australia > > Only Robinson Crusoe had all his work done by Friday > > > > > > -Original Message- > > Sent: Saturday, 22 June 2002 9:03 PM > > To: Multiple recipients of list ORACLE-L > > > > > > On Solaris > > > > ps -ef -opid,ppid,vsz=VIRTMEM -orss=PHYSMEM > -opmem,pcpu,user,args > > > > use: > > > > psrinfo -v > > prtconf | grep Mem > > format > > uname -a > > > > HTH > > > > Richard > > > > -Original Message- > > Sent: Saturday, June 22, 200
OT: fast tape drive for AIX
list, we are looking for a fast tape drive to backup all the volume groups on our IBM H70.. around 100GB+, our current backup takes around 5-6 hours !!! any ideas about a faster tape drive ? or an optical one ? regards -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Remember me? Oracle DBA veteran considering getting certifi
Title: RE: Remember me? Oracle DBA veteran considering getting certifi Sorry I didn't respond sooner - been up to my neck recovering from a bad controller. Anyway - 8i. If Mike Ault wrote a cram book for 9i upgrade I would get that one too. Please don't tell me that 8i ceritfication is retired. -Original Message- From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: Remember me? Oracle DBA veteran considering getting certifi Which version you are talking about? 8i or 9i upgrade certification Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Sat, 28 Sep 2002 08:53:19 -0800 Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 10-12. Total hours to prepare : 30 hours Resources: Exam Cram by Mike Ault and self-test exam Any additional costs - none Didn't want to study on clients time so ended up studying mostly between the hours of 2:00 a.m. and 8:00 a.m. in the morning. Hope the others go well and can get this done before Oracle changes the criteria. _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle DBA veteran considering getting certification
Congratulations Paula, and thanks for sharing "how I did it". Like you, I believe that today there are still plenty of hiring managers that will count experience far heavier than certification. I just worry that if they are considering someone with equal experience, the OCP might tip their decision. Ironically, those who have been out of work have been in a better position to study for their certification than those of us who have been working steadily. - Which level of Oracle are you pursuing? Oracle 8i? - Which test did you take? I have been very pleased with Jason Couchman's "Oracle 8i DBA Practice Exams" from Oracle Press. One thing he points out is that for the experienced DBA, the different modules are definitely of different difficulty levels. Here is my assessment so far: - SQL and PL/SQL is one of the hardest. You need to know all the SQL functions. Discourages too many experienced DBAs from continuing the certification process, if they were caught napping by this first test. - Database Administration - the easiest for an experienced DBA. Take this first. - Tuning - Pretty easy since most of us end up tuning from time to time. - Backup & Recovery - One of the hardest. Most of us don't spend much time recovering (if we're lucky), but nobody can deny it is a critical function. Of course, I took this one first. - Net Administration - I don't have a good feel for this one. I don't do much network stuff, so I have a lot to learn. Someone posted a statement awhile back to the effect: "look at it from the test developer's point of view." Here is what I think the objectives are: 1. Ensure that a client can hire an OCP with reasonable assurance that they are reasonably competent. 2. Test basic competence. Make it difficult enough and practical enough to weed out most of the inexperienced people. 3. Test breadth of knowledge. This trips up most of us experienced people. For example, maybe you've never worked with MTS. An OCP should at least know some basic facts about MTS just so a hiring manager doesn't shake his head in disbelief that he just hired someone that never heard of MTS. On the other hand, Oracle database is very complex and richly featured so you can't expect an OCP to be an expert in every facet. 4. Tie it to the Oracle Education courses. Oracle didn't get where it was by leaving money on the table. If it can get most people to take the courses and most course graduates glide through the exams, then for Oracle it is a win-win. You can't make the test so difficult that you get so few certified people that it never gets any mass appeal. But you can't make it so easy that its value is ridiculed (aside from this list). I'll tell you something more ominous. I am a Licensed Professional Engineer (mechanical engineer). This is a really old certification track, that is administered by a national professional engineer's society (wouldn't work here, the professional societies like IEEE aren't strong enough, and Oracle will resist competition, since they sell educational classes). These things tend to vary over time. In the early days, you could simply send in a licensing fee. That is how they grandfathered in all the working professionals, and forestalled a rebellion. Then they instituted an easy test. By the time I came along, the test was a real bear, and you couldn't take it until you had 4 years of experience (4 years to forget). Technically, you could come in off the street, no degree, and pass the test. Unlikely, but theoretically possible. Now, they have accomplished most of their goals and they more certify the school than the engineer. Or they are trying to raise the number of licensed engineers. My apologies for boring most of you, but personally, I find some interesting parallels. We may actually see the certification process get harder now that the wide acceptance seems to be coming. -Original Message- Sent: Saturday, September 28, 2002 11:53 AM To: Multiple recipients of list ORACLE-L Well, Given the IT market I felt that it was worth getting certified even though I haven't had any problems and been working with Oracle as DBA for over 8 years. However, I decided that I didn't want to spend a lot of money or time to do it. I have 2 small children, work, - yadayadayada(sp?). I got the self-test for the first test, studied using that and read Mike Ault's Exam cram book from front to back (excellent resource, concise, straightforward, good examples - just a couple of errors in whole book). Total test time was about 30 hours. Took the exam this morning in 60 minutes (120 alloted), got 49 out of 57 questions correct and passed. I really want to thank Mike Ault for the excellent concise Cram book and intend to continue on this same path for the other exams. Unfortunately, Mike didn't write all of them - however, I am hoping they are all of the same level of quality. I haven't taken a course in Oracle (any) for about 5 year and SQL/PLSQL in about 1
Oracle DBA veteran considering getting certification/9i
Thanks..I was more interested in 9i upgrade exam. Mike Ault don't have book for this 9i upgrade as far as I know...However, I just passed my 9i upgrade today ..My study material was 1-Robert Freeman 9i New Features Book 2-Daniel's OCP 9i New Features Book 3-STS Software question(now password is validated from their site for online test question purchase by using purchaser's email account and their account password so no free distribution from purchaser). However they offer 30% discount now a days meaning test cost under $70 ISO $99. 4-Some more sources from web... 5-Personal experience It is just for info for whoever is interested and no publicity of material For any specific question, send me email directly (please not for STS test) Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Sat, 28 Sep 2002 14:13:18 -0800 I think he meant Oracle 9i Exam 1 of 4 - Intro to SQL. On Saturday 28 September 2002 14:28, Mohammad Rafiq wrote: > Which version you are talking about? 8i or 9i upgrade certification > > Regards > Rafiq > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Sat, 28 Sep 2002 08:53:19 -0800 > > Well, > > Given the IT market I felt that it was worth getting certified even though > I haven't had any problems and been working with Oracle as DBA for over 8 > years. However, I decided that I didn't want to spend a lot of money or > time to do it. I have 2 small children, work, - yadayadayada(sp?). I got > the self-test for the first test, studied using that and read Mike Ault's > Exam cram book from front to back (excellent resource, concise, > straightforward, good examples - just a couple of errors in whole book). > Total test time was about 30 hours. Took the exam this morning in 60 > minutes (120 alloted), got 49 out of 57 questions correct and passed. I > really want to thank Mike Ault for the excellent concise Cram book and > intend to continue on this same path for the other exams. Unfortunately, > Mike didn't write all of them - however, I am hoping they are all of the > same level of quality. I haven't taken a course in Oracle (any) for about > 5 year and SQL/PLSQL in about 10-12. > > Total hours to prepare : 30 hours > Resources: Exam Cram by Mike Ault and self-test exam > Any additional costs - none > Didn't want to study on clients time so ended up studying mostly between > the hours of 2:00 a.m. and 8:00 a.m. in the morning. > > Hope the others go well and can get this done before Oracle changes the > criteria. > > > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: linux and Oracle Apps jinit
Hi Ron, > Has anyone found a way to use linux as a client to Oracle Applications? > The self service stuff runs > just fine but the Oracle Forms requires that damnable jinit. This is > the only problem left before I > can give MS the big Heave Ho. I am also in the process of trying to find if I can get Linux to be a client for Oracle Apps and the only lead I had was that there was once a Solaris version that can be still made to work and I am trying to find out if I can get that. Other than that, I have heard Windows is the only platform that jinit runs on :-( Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm/ - Consolidate POP email and Hotmail in one place -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: DBA place in the business (was RE: DBA work load)
Hello Peter We have an infrastructure division that divides into two departments: system programming and DBA. Organization chart for us will be: CEO -> CIO -> Infrastructure -> DBA. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 11:13 AM > > I've found the thread on DBA workload valuable and interesting. It endorses > points made repeatedly over the past years, basically the highly variable > nature of the job. > > This variability is giving us a small problem. Our dba work (shared between > two of us) tends to function in the background, and of course because we do > it so damn well (!!), our impact on the running of the organisation is > pretty low. Kind of 'reverse exception' effect, if you will. > > There is now a desire to formalise the role of the dba function within the > organisation, and nobody has the first idea of how to define, in an > organisational / structural sense just how the dba role slots in. I'm > talking about organsiational charts, herarchies etc, that sort of thing. Not > just across the org, but particularly within the IT domain too. > Specifically, dba impacts from the low-level hardware side, right up to > application development, with everything in between. And that already spans > several existing lines of management responsibility. Our problem has added > spice as we are (trying) to operate a matrix management system, which > repeatedly throws up intriguing political dimensions. > > Anybody ever been down this particular route? > > Any thoughts much appreciated, > > peter > edinburgh > > > * > This e-mail message, and any files transmitted with it, are > confidential and intended solely for the use of the addressee. If > this message was not addressed to you, you have received it in error > and any copying, distribution or other use of any part of it is > strictly prohibited. Any views or opinions presented are solely those > of the sender and do not necessarily represent those of the British > Geological Survey. The security of e-mail communication cannot be > guaranteed and the BGS accepts no liability for claims arising as a > result of the use of this medium to transmit messages from or to the > BGS. The BGS cannot accept any responsibility for viruses, so please > scan all attachments.http://www.bgs.ac.uk > * > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Robson, Peter > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
