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).
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, 2002 1:38 PM To: Multiple recipients of list ORACLE-L Good day to everyone... I have two questions related to Linux and Solaris... * I need do find memory
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) forALL my queries Scenario 2: I use the naming convention b) forALL 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
RE: RAC (Real Application Clusters)
Scott Wrote :- This software configuration is only supported on EMC 8XXX series towers (firmware 5567.35.20 or higher) and the Hitachi HDS 9910 and 9960 series( firmware 01-16-40-00/00 or higher). It also recommended you use JNI controllers. Your disk drives will also have to support SCSI-3 persistent reservations and the PR flag has to be set on the disks. The PR reservations are used for IO fencing in the case of any split-brain conditions. I am currently running RAC R1 and R2 using DBE/AC on SUN E6500's and I have been very impressed with their product. Qs Do you mean you are using a EMC 8XXX series towers OR Hitachi HDS 9910 OR 9960 series with your SUN E6500 on Solaris 8 ? Qs What do JNI Controllers , SCSI-3 persistent Drives Reservations , PR Flag mean ? Qs What does Oracle IA Stand for ? Qs Am I Correct in assumign that CFS is a Mounted File System (NON-Raw) ? Thanks -Original Message- Sent: Saturday, September 28, 2002 4:22 AM To: Multiple recipients of list ORACLE-L Greg, For the most part RAW is still a requirement for RAC. There are more Cluster File system Options now then there where 6 months ago. Windows/2000 - Oracle now provides a CFS. I believe you can download it from OTN. I don't have NT installed so I can't comment on its reliability or stability. Linux - Sistina has a CFS at $1000/node, Polyserv has a CFS and Oracle is currently developing a CFS for Linux. Sistina is certified for Oracle9i R1 and R2. I am not sure about Polyserv but I think it is certified. I currently use raw devices on Linux but should be installing Sistina sometime in October. AIX - has a CFS and it is certified by Oracle. Not sure if this for HACMP or RS6000/SP or both. HP - I don't think there is a CFS available for HP/UX but their newly acquired Compaq TRU64 and OpenVMS has a CFS. HP is eventually doing away with TRU64 so maybe they will roll this technology into their HP/UX MC cluster software. Solaris 2.7 and lower you will have to use raw devices. Solaris 2.8 and greater you can use Veritas DBE/AC 3.5, This package includes the Veritas Cluster Volume Manager, Cluster File System, Oracle Disk Manager (ODM, Formerly known as Quick/IO qio), and thier Cluster Server. This software currently supports Oracle9i R1 and R2. This software configuration is only supported on EMC 8XXX series towers(firmware 5567.35.20 or higher) and the Hitachi HDS 9910 and 9960 series( firmware 01-16-40-00/00 or higher). It also recommended you use JNI controllers. Your disk drives will also have to support SCSI-3 persistent reservations and the PR flag has to be set on the disks. The PR reservations are used for IO fencing in the case of any split-brain conditions. I am currently running RAC R1 and R2 using DBE/AC on SUN E6500's and I have been very impressed with their product. I am also running RAC on SUN e3500's using RAW and I have found that the CFS has made my life much easier. However if you choose to use shared Oracle homes you can have problems with Oracle's IA. The IA doesn't like to share logs and the other files the IA creates and manages. You will have to create separate homes for the agent or symbolically link these files to local directories on these nodes. TRU64 has Context Dependent Symbolic Link (CDSL) facility to separate the $ORACLE_HOME/network directory from the shared Oracle home installation. This is employed by using a specific keyword in the filename (or a symbolic link) that distinguishes the name of the current member node of the cluster. The other thing I want to comment on is the RAC is implemented differently on different hardware. This is not true. RAC is implemented the same on all hardware. However there are some options that may implemented based on a particular platform. RAC Guard is an option that was specifically geared towards TRU64. In fact Oracle thought it worked so good they licensed the technology from COMPAQ TRU64 and implemented it in RAC guard II so it is available on all platforms. It is true that some platforms may implement clustering better than other platforms, but this is at the hardware level not necessarily at the Oracle level. Don't forget SQLNET with TAF. Besides OCI you also have to be able to use SQL*Net and thin drivers do not use either. Jesse, It's hard to say what questions to ask but if I was just learning the product I would stick to a few basic rules. If it appears to work like magic ask how they got it to work. If it sounds to good to be true, ask to have them back it up with facts. Has anyone done this before? Can they provide references? The thing is don't be afraid to ask any question. It is surprising how many people won't ask questions because they think it is stupid but everyone else is thinking the same thing. Also the answer may have information for you to ask another question. The other thing is that if something is said that you don't understand send it to this list. There is enough talent on this list to know when the smoke is
Re: Does anyone know the HACMP for oracle 8i in an AIX box? -- Attachment
it works hukangang hukangang@iciticTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Does anyone know the HACMP for oracle 8i in an AIX box? [EMAIL PROTECTED] 28-09-2002 16:08 Please respond to ORACLE-L === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: datafile sizing question
What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN Question
Hi ALL Is there any way with RMAN to overwrite the backup files if they already exist. Example generating backup files with the same names each time. Thans Kamel B. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: GL2Z/ INF DBA BENLATRECHE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT: For the MicroSoft bashers
Same here ;P -Original Message- Sent: 27 September 2002 21:53 To: Multiple recipients of list ORACLE-L H...even after flushing the cache, MS is still the top link for me on Google (except for the news stories). Rich -Original Message- From: Thomas Day [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 2:33 PM To: Multiple recipients of list ORACLE-L Subject: Re: OT: For the MicroSoft bashers They got that changed rather quickly. MS is no where on the first page now. IF you combine go to hell with Microsoft you get 7 hits but the top 2 are sites commenting on this story. Ain't it great to have clout. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: MIcrosoft Blackmail
DENNIS, I think that I did not explain my idea. I do not understand the complain of Thomas. I do not see any harm in a company choosing its dealers based on their commitment to the goals of my company. Microsoft has a right to prefer dealer who embrace the .net, or do you think that anybody have the right to tell a PRIVATE company who to deal with? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 5:18 PM Yechiel - But all things are NEVER equal. So companies end up doing stupid things because of some larger motive. You end up buying crappy computers because your boss thinks it will impress the CEO with how you are loyally supporting someone that somehow supports your company. Ironic isn't it. When the PC industry began, the computer industry was firmly dominated by IBM. PC enthusiasts were a bunch of starry-eyed dreamers that though they could wrestle computing away from the computer priesthood and bring freedom to everyman. In many ways the Internet has made that dream come true. But then we have Microsoft talking about creating a new security system for my computer that on one hand will protect me from bad things and on the other hand will protect the products of large corporations from me. In a great number of ways Microsoft resembles the IBM of the past. obligatory Oracle reference Of course Larry Ellison only wishes he had these type of issues to deal with. /obligatory Oracle reference Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 8:43 AM To: Multiple recipients of list ORACLE-L What exactly is your problem? Lets say that you are a factory that sells paper. You need to buy a computer system. One supplier also sell printers and the other advocate paperless office. All things being equal, which one will you give your business to?? Yechiel Adar Mehish - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Thursday, September 26, 2002 1:13 AM This came to our DBA team today.I'd appreciate your thoughts. I'm not a business guy, just a plain old Apps DBA, but this really pisses me off. Is it common practice by MS? It is important from an Architecture point of view that we understand all the various approaches to web services (also known as grid computing -- see my recent report). Microsoft's dot Net initiative is their approach to this grand overarching software strategy. There is a second reason why we might be interested specifically in dot Net. Subsidiary XYZ earns $xyz a year for us from Microsoft by [performing certain services], etc. Microsoft has told our management that one of their criteria for evaluating their vendors will be how good of a MS customer is the potential vendor. Specifically, has the vendor bought in to the dot Net strategy. Now we aren't going to make our global enterprise solutions strategy decisions based upon that point alone, but it's not something we are going to ignore either. Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.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).
RE: datafile sizing question
with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
[no subject]
Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1 client 1 project 2 client 1 project 3 client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh
[no subject]
HELP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Opening an 8.0.5 Database with 8i
Hi I got a question from a co-worker about opening an 8.0.5 database using 8.1.7 Since I don't have 8.1.7 here to test it, does anybody know wether this is possible or not ? I guess not, maybe it can exclusively mounted/opened followed by some script to upgrade it to 8.1.7 ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Opening an 8.0.5 Database with 8i
Under NT when doing the upgrade from 8.0.5 to 8i the database will be mounted once the 8i services are created (assuming -startmode auto has been supplied). There are various data dictionary objects that need upgrading via the upgrade script (u0800050.sql) so it's a good idea to restrict access immediately after creating the service and before running the upgrade script. I would assume a similar scenario exists on unix based systems. So the answer is yes - you can OPEN an 8.0.5 database under 8.1.7 but as for successful use - who knows? Lewis Bishop --- Barclays Enable - ISS - E-NTRUST/Bexleyheath NT Oracle Database Consultant Watling Street, Bexleyheath, Kent, DA6 7RR (Mail Van R) Phone : 020 8298 3418 Mobile: 07950 380857 Email : [EMAIL PROTECTED] Enabling Competitive Advantage for Barclays in IT and Business Processing -Original Message- Sent: 30 September 2002 12:28 To: Multiple recipients of list ORACLE-L Hi I got a question from a co-worker about opening an 8.0.5 database using 8.1.7 Since I don't have 8.1.7 here to test it, does anybody know wether this is possible or not ? I guess not, maybe it can exclusively mounted/opened followed by some script to upgrade it to 8.1.7 ? Regards, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information, attachments and opinions contained in this message are those of its author only and do not necessarily represent those of The Woolwich and or any other members of the Barclays Group and are intended solely for the use of the individual or entity to whom they are addressed. The sender may not be authorised to give financial advice, and nothing in this message should be construed as offering such advice. The message may contain privileged and confidential information and you may not copy, distribute or take any action in reliance on it. If you have received this email in error please notify the Information Security Manager at [EMAIL PROTECTED] Replies to this email may be monitored for operational or business reasons. Woolwich plc. Registered in England Number : 3295699. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bishop 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).
RE:
ARE YOU AN IDIOT? -Original Message- Sent: Monday, September 30, 2002 6:33 AM To: Multiple recipients of list ORACLE-L Subject: HELP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Help can't restore archive logs from networker through RMAN
Title: RE: Help can't restore archive logs from networker through RMAN Guys, hanging on restoring archive logs only and need for urgent recovery. Oracle suggest doing a trace on allocate channel but can't find syntax for 8.0.6 just 8i which doesn't work. Help!
RE: datafile sizing question
Title: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Does the case of an Oracle query statement affect query perfo
As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: 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) forALL my queries Scenario 2: I use the naming convention b) forALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. -- 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.*2
RE: Remember me? Oracle DBA veteran considering getting certifi
Paula, Your experience sounds very similar to mine which I documented on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm . I used the Exam Cram series and was very happy with them. I am booked for the 8i upgrade next week but despite using 8i for however long it has been available I cannot believe how much there is to learn. I can see myself putting off the exam once again John -Original Message- Sent: 30 September 2002 04:48 To: Multiple recipients of list ORACLE-L 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- mailto:[EMAIL PROTECTED] ] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L 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 http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW v$session_longops select with last_update_time desc and use the sid ... it is pretty cool .. it will also tell you for current operation how many seconds are remaining. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW
This is a query I build to see this, midnight Saturday
Comment welcome
column % Done format 999.99
column opname format a15
column sql_text format a70
column T Left format 9
select
a.sid,
(a.sofar/a.totalwork)*100 % Done,
to_char(a.last_update_time, 'HH24:MI:SS'),
a.username,
a.time_remaining T Left,
a.opname ,
s.sql_text
from v$session_longops a, v$session
b, v$sqltext s
where a.sid =b.sid
and b.sql_address = s.address
and a.sofar a.totalwork
order by b.sid, last_update_time
/
George
George
Leonard
Oracle Database
Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel:(+27 11) 575
0573
Fax:(+27 11) 576
0573
E-mail:[EMAIL PROTECTED]
Web: http://www.didata.co.za
You Have The Obligation
to Inform One Honestly of the risk, And As a Person
You Are Committed to
Educate Yourself to the Total Risk In Any Activity!
Once Informed
Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure
Themselves as They See Fit!
-Original Message-
From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]
Sent: 30 September 2002 14:38 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Determine process of
index build - HOW
v$session_longops select with last_update_time
desc and use the sid ... it is pretty cool .. it will also tell you for
current operation how many seconds are remaining.
Raj
__
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
having an opinion is an art!
***
This message contains information intended solely for the addressee,
which is confidential or private in nature and subject to legal privilege.
If you are not the intended recipient, you may not peruse, use,
disseminate, distribute or copy this message or any file attached to this
message. Any such unauthorised use is prohibited and may be unlawful. If
you have received this message in error, please notify the sender
immediately by e-mail, facsimile or telephone and thereafter delete the
original message from your machine.
Furthermore, the information contained in this message, and any
attachments thereto, is for information purposes only and may contain the
personal views and opinions of the author, which are not necessarily the
views and opinions of Dimension Data (South Africa) (Proprietary) Limited
or its subsidiaries and associated companies ("Dimension Data"). Dimension
Data therefore does not accept liability for any claims, loss or damages
of whatsoever nature, arising as a result of the reliance on such
information by anyone.
Whilst all reasonable steps are taken to ensure the accuracy and
integrity of information transmitted electronically and to preserve the
confidentiality thereof, Dimension Data accepts no liability or
responsibility whatsoever if information or data is, for whatsoever
reason, incorrect, corrupted or does not reach its intended destination.
*
Re:
try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1client 1 project 2client 1 project 3client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query
No, there will not be any noticable difference in performance. -Mark On Mon, 2002-09-30 at 03:58, Shantanu Datta wrote: 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. -- -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query perfo
Oracle sees (a) and (b) as two different queries and parses them both. For them to be identical the text must match, including the white spaces. I suggest a small test under *identical conditions* to see if execution time varies ;) - Kirti -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 2:58 AMTo: Multiple recipients of list ORACLE-LSubject: 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) forALL my queries Scenario 2: I use the naming convention b) forALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
Re: datafile sizing question
Title: RE: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of COUNTER, all the way up to values like 250,000 or 500,000, if you like. The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time. The time spent in the PL/SQL loop should be roughly linear with the value of COUNTER, the point being that each ALLOCATE EXTENT takes roughly the same amount of time. You might observe an "elbow" in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment. On my laptop, I've seen the curve stay linear up into the 100,000s. The time spent in DROPmay not vary a great deal; it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous. Try it out! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 6:33 AM Subject: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit.
Re: datafile sizing question
Fragmentation or tablespace fragmentation does not simply mean more than one extent, as it appears you are assuming. Also, it is an obsolete concept where LMTs are involved, in all but a few difficult-to-imagine situations. Please read one or more of the following: Craig Shallahamer's All about Oracle database Fragmentation on www.orapub.com, Cary Millsap's Oracle7 Space Management or Juan Loaiza et al's How to Stop Defragmenting and Start Living both on www.hotsos.com, or Tim Gorman's Myths about Extents and Performance at www.evdbt.com/papers.htm... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 2:43 AM What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query perfo
Raj, Do you have any test cases or white papers to support your statement? Especially the part about "if you mix-n-match that will make Oracle do more work." never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 8:33 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Does the case of an Oracle query statement affect query perfo As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: 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) forALL my queries Scenario 2: I use the naming convention b) forALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
Re: RMAN Question
No, you can't do that. I just run a OS job everyday which removes the old backups to make room. HTH, Ruthg - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 5:28 AM Hi ALL Is there any way with RMAN to overwrite the backup files if they already exist. Example generating backup files with the same names each time. Thans Kamel B. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: GL2Z/ INF DBA BENLATRECHE INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Raj Print http://otn.oracle.com/deploy/availability/pdf/defrag.pdf http://otn.oracle.com/deploy/availability/pdf/defrag.pdf - very well-written, direct from Oracle's site, so he will accept it as official. BTW - In this paper, for Oracle 8 and above, the correct extent sizes are 120-k, 4-m, and 128-m. The reasons you want to use these specific sizes are explained in the paper. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 7:33 AM To: Multiple recipients of list ORACLE-L Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
RE: MIcrosoft Blackmail
Yechiel - Sorry, I was reacting to your analogy of the paper company. I agree that dealers are an entirely different matter. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 5:33 AM To: Multiple recipients of list ORACLE-L DENNIS, I think that I did not explain my idea. I do not understand the complain of Thomas. I do not see any harm in a company choosing its dealers based on their commitment to the goals of my company. Microsoft has a right to prefer dealer who embrace the .net, or do you think that anybody have the right to tell a PRIVATE company who to deal with? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 5:18 PM Yechiel - But all things are NEVER equal. So companies end up doing stupid things because of some larger motive. You end up buying crappy computers because your boss thinks it will impress the CEO with how you are loyally supporting someone that somehow supports your company. Ironic isn't it. When the PC industry began, the computer industry was firmly dominated by IBM. PC enthusiasts were a bunch of starry-eyed dreamers that though they could wrestle computing away from the computer priesthood and bring freedom to everyman. In many ways the Internet has made that dream come true. But then we have Microsoft talking about creating a new security system for my computer that on one hand will protect me from bad things and on the other hand will protect the products of large corporations from me. In a great number of ways Microsoft resembles the IBM of the past. obligatory Oracle reference Of course Larry Ellison only wishes he had these type of issues to deal with. /obligatory Oracle reference Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 8:43 AM To: Multiple recipients of list ORACLE-L What exactly is your problem? Lets say that you are a factory that sells paper. You need to buy a computer system. One supplier also sell printers and the other advocate paperless office. All things being equal, which one will you give your business to?? Yechiel Adar Mehish - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Thursday, September 26, 2002 1:13 AM This came to our DBA team today.I'd appreciate your thoughts. I'm not a business guy, just a plain old Apps DBA, but this really pisses me off. Is it common practice by MS? It is important from an Architecture point of view that we understand all the various approaches to web services (also known as grid computing -- see my recent report). Microsoft's dot Net initiative is their approach to this grand overarching software strategy. There is a second reason why we might be interested specifically in dot Net. Subsidiary XYZ earns $xyz a year for us from Microsoft by [performing certain services], etc. Microsoft has told our management that one of their criteria for evaluating their vendors will be how good of a MS customer is the potential vendor. Specifically, has the vendor bought in to the dot Net strategy. Now we aren't going to make our global enterprise solutions strategy decisions based upon that point alone, but it's not something we are going to ignore either. Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com --
RE: datafile sizing question
I haven't seen any papers.. I was told this (4096 is the exact number) in an Internals class. However, there are lots of papers out there saying multiple extents are not a problem, and you should be able to find them on the web. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: MIcrosoft Blackmail
Microslop is not a private company, and has not been for quite some years now. Jared On Monday 30 September 2002 03:33, Yechiel Adar wrote: DENNIS, I think that I did not explain my idea. I do not understand the complain of Thomas. I do not see any harm in a company choosing its dealers based on their commitment to the goals of my company. Microsoft has a right to prefer dealer who embrace the .net, or do you think that anybody have the right to tell a PRIVATE company who to deal with? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 5:18 PM Yechiel - But all things are NEVER equal. So companies end up doing stupid things because of some larger motive. You end up buying crappy computers because your boss thinks it will impress the CEO with how you are loyally supporting someone that somehow supports your company. Ironic isn't it. When the PC industry began, the computer industry was firmly dominated by IBM. PC enthusiasts were a bunch of starry-eyed dreamers that though they could wrestle computing away from the computer priesthood and bring freedom to everyman. In many ways the Internet has made that dream come true. But then we have Microsoft talking about creating a new security system for my computer that on one hand will protect me from bad things and on the other hand will protect the products of large corporations from me. In a great number of ways Microsoft resembles the IBM of the past. obligatory Oracle reference Of course Larry Ellison only wishes he had these type of issues to deal with. /obligatory Oracle reference Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 8:43 AM To: Multiple recipients of list ORACLE-L What exactly is your problem? Lets say that you are a factory that sells paper. You need to buy a computer system. One supplier also sell printers and the other advocate paperless office. All things being equal, which one will you give your business to?? Yechiel Adar Mehish - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Thursday, September 26, 2002 1:13 AM This came to our DBA team today.I'd appreciate your thoughts. I'm not a business guy, just a plain old Apps DBA, but this really pisses me off. Is it common practice by MS? It is important from an Architecture point of view that we understand all the various approaches to web services (also known as grid computing -- see my recent report). Microsoft's dot Net initiative is their approach to this grand overarching software strategy. There is a second reason why we might be interested specifically in dot Net. Subsidiary XYZ earns $xyz a year for us from Microsoft by [performing certain services], etc. Microsoft has told our management that one of their criteria for evaluating their vendors will be how good of a MS customer is the potential vendor. Specifically, has the vendor bought in to the dot Net strategy. Now we aren't going to make our global enterprise solutions strategy decisions based upon that point alone, but it's not something we are going to ignore either. Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re:
On Mon, Sep 30, 2002 at 04:23:22AM -0800, Farnsworth, Dave wrote: ARE YOU AN IDIOT? I think so. - ListGuru GENERAL Command HELP - This help file contains basic information about each command recognized by ListGuru. More detailed help is available through these commands: HELP command -- Gives detailed help about a given command (listed below) HELP DETAILED -- Complete and exhaustive help on ALL commands HELP USAGE -- A general primer on how to use your mailing list HELP FAQ-- A list of Frequently Asked Questions (FAQ) Interacting with ListGuru: -- ListGuru is a Mailing List Manager (MLM) which understands the commonly used commands of many other MLM's, including ListProc, listserv, Majordomo, SmartList, Mailbase and Listcaster, among others. All commands should be sent by E-mail to the following address: [EMAIL PROTECTED] -- Note spelling closely... The Subject: line is ignored, so do not place commands on it. Commands go in the message BODY, one command per line. You can send as many commands in a single message as you wish. Each command has a specific format, as outlined below. In the explanations below, replace any word enclosed by angle brackets, with an appropriate response. For example: INFO list would be replaced with: INFO GARDENING-L Other command replacements: list means the mailing list name (always suffixed with -L) real name means your given name or surname, not E-mail address password means a password given to you for closed lists search textmeans arbitrary text, not case sensitive option means a particular option, dependent on the command filename means a filename (no pathnames are allowed) commandmeans any ListGuru command descriptionmeans arbitrary text, case sensitive If you have any difficulties or questions regarding ListGuru, contact: [EMAIL PROTECTED] The following commands are recognized by ListGuru (in alphabetical order): -- ALLMAIL list Displays a short summary (who, when, what) of all messages received and sent out since the last time a digest was produced (generally midnight of the previous day, but could be longer on low-traffic lists). See Also: CONFIRM, LASTMAIL ARCHIVES Displays a list of all mailing lists which have file archives and which you are currently a subscriber to. See Also: GET, INDEX, SEARCH, SUBMIT, VIEW BIOGRAPHY list INDEX BIOGRAPHY list user BIOGRAPHY list ALL BIOGRAPHY list BIOGRAPHY list DELETE The general intent of the BIO command is to provide a way for list members to create a short biography for themselves, which is then available to all other members of the same list. BIOGRAPHY can be shortened to BIO if you prefer -- both spellings work equally well. *** NOTE *** This command is fairly detailed, so it is recommended that you either issue a HELP DETAILED or a HELP BIO command to get the full set of instructions for using this command. Form #1: BIO list INDEX Returns a complete list of whose bio is available for the given list. As an example, you could do a BIO GARDENING-L INDEX command and ListGuru would send back a list of all BIO's so far submitted for the GARDENING-L list. Form #2: BIO list user Sends back a BIO for a specific user. Usually it is the next command you issue after the INDEX form. You will be sent back the complete biography text as submitted by that specific user. Be sure to use the name listed in the INDEX form to get information on the right person. Form #3: BIO list ALL Similar to form #2, but sends ALL biographies that are available for the list you specify. A quick way to get familiar with everyone, instead of just individual users. Form #4: BIO list This is how you submit your OWN biography. When you use this form of the command, it should be the only command you send in that message, and the message MUST contain a uuencoded file containing your biography. At the current time, MIME attachments are not supported, so the attached file must first be uuencoded, then sent with your message. If you have problems with uuencode, contact [EMAIL PROTECTED] for assistance. Form #5: BIO list DELETE This form deletes any biography entry YOU have submitted for the list specified. Note you cannot delete anyone elses entry; only your own. See Also: INDEX, SUBMIT CONFIRM list Confirms whether you are a subscriber to a particular list or not.
RE: Determine process of index build - HOW
Title: RE: Determine process of index build - HOW And if you have OEM/DBA Studio, you can watch the progress graphically.. :) - Kirti -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:38 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Determine process of index build - HOW v$session_longops select with last_update_time desc and use the sid ... it is pretty cool .. it will also tell you for "current operation" how many seconds are remaining. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
Utl_file and OPENVMS
List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? Listing from the Oracle tables: DTS_PARAMETER_NAME DTS_PARAMETER_VALUE -- -- LOAD_USERIDLOADITUP LOAD_PASSWORD ILOADIT LOAD_SERVICE_NAME GLC_ALPHADEV-TCP LOAD_PAR_FILE_DIR ORADSK:[ORACLE8.DATA.PAR] LOAD_DATA_FILE_DIR Disk1:[Pro_ics.Iqfiles] LOAD_CONTROL_FILE_DIR ORADSK:[ORACLE8.DATA.CONTROL] LOAD_LOG_FILE_DIR ORADSK:[ORACLE8.DATA.LOG] LOAD_BAD_FILE_DIR ORADSK:[ORACLE8.DATA.BAD] LOAD_DISCARD_FILE_DIR ORADSK:[ORACLE8.DATA.DISCARD] Listing from the package that writes the parameter file: Procedure Create_New_Par_File ( P_Current_Table_Name In Varchar2 , P_Run_Date In Date , P_Load_UseridIn Varchar2 , P_Load_Password In Varchar2 , P_Load_Service_Name In Varchar2 , P_Load_Par_File_Dir In Varchar2 , P_Load_Data_File_Dir In Varchar2 , P_Load_Control_File_Dir In Varchar2 , P_Load_Log_File_Dir In Varchar2 , P_Load_Bad_File_Dir In Varchar2 , P_Load_Discard_File_Dir In Varchar2 ) as Begin Declare L_Par_File_Hand Utl_FIle.File_Type; -- Local variable to hold the File Pointer for the parameter file. Begin I PLACED A DBMS_OUTPUT STATEMENT HERE AND IT DISPLAYED THE PROPER DIRECTORY LOCATION FOR THE FILE TO BE WRITT EN TO. -- Open a new parameter file L_Par_File_Hand := Utl_File.Fopen(P_Load_Par_File_Dir,P_Current_Table_Name||'.PAR','w'); -- Print the following lines into the parameter file. Utl_File.Put (L_Par_File_Hand,'UserId='||P_Load_UserId||'/'||P_Load_Password||'@'||P_Load_Service_Name); Utl_File.New_Line(L_Par_File_Hand); If Not P_Current_Table_Name = 'GLCRET' Then
RE: data file sizing question
Title: RE: data file sizing question Thanks Tim, I'll try to do this exercise today/tomorrow ... The reason I mentioned papers is Managers are easily impressed by thing that are done by outsiders (they are considered experts, in-house knowledge is never sufficient). You probably know what I mean. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 10:18 AM To: Multiple recipients of list ORACLE-L Subject: Re: data file sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of performance implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents. Extremely small extents can obviously affect a multi-block read if they consistently limit the number of blocks that can be read. Since testing this requires some non-trivial resources (i.e. test data and disk space) to prove, I'll leave the proving to those who have both (in addition to time). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of COUNTER, all the way up to values like 250,000 or 500,000, if you like. The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time. The time spent in the PL/SQL loop should be roughly linear with the value of COUNTER, the point being that each ALLOCATE EXTENT takes roughly the same amount of time. You might observe an elbow in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment. On my laptop, I've seen the curve stay linear up into the 100,000s. The time spent in DROP may not vary a great deal; it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous. Try it out! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
OT: oracle-dba.com domain auction
With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe
RE: extremely long parse time
Title: extremely long parse time Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds on the wall clock to parse: select null as table_cat, owner as table_schem, table_name, 0 as NON_UNIQUE, null as index_qualifier, null as index_name, 0 as type, 0 as ordinal_position, null as column_name, null as asc_or_desc, num_rows as cardinality, blocks as pages, null as filter_condition from all_tables where table_name = 'INDEXENTRIES' union select null as table_cat, i.owner as table_schem, i.table_name, decode (i.uniqueness, 'UNIQUE', 0, 1), null as index_qualifier, i.index_name, 1 as type, c.column_position as ordinal_position, c.column_name, null as asc_or_desc, i.distinct_keys as cardinality, i.leaf_blocks as pages, null as filter_condition from all_indexes i, all_ind_columns c where i.table_name = 'INDEXENTRIES' and i.index_name = c.index_name and i.table_owner = c.table_owner and i.table_name = c.table_name and i.owner = c.index_owner order by non_unique, type, index_name, ordinal_position Matt Adams - GE Appliances - [EMAIL PROTECTED]Their fundamental design flaws are completelyhidden by their superficial design flaws. - Douglas Adams -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Friday, September 27, 2002 4:28 PMTo: Multiple recipients of list ORACLE-LSubject: RE: extremely long parse time Matt, Is it Oracle 9? If yes, time is in microseconds. Alex. -Original Message-From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 26, 2002 10:30 AMTo: Multiple recipients of list ORACLE-LSubject: extremely long parse time why would a query take 148 seconds to parse? It is a two way union where the first half is going against all_tables and the second half is a join between all_indexes and all_ind_columns. The shared pool has 50M large parts of it are free. I generated a 10046 (level 12) trace, and I just don't see anything out of whack, except of the c=14868 in the PARSE #1 line. Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams
RE: Does the case of an Oracle query statement affect query perfo
Title: RE: Does the case of an Oracle query statement affect query perfo Tom, Well it simply comes to when Oracle will parse the query and try to find a matching sql to hash to in SGA, if it finds one, it will hash to the same one, else it will have to create a new hash entry. In pre-8i (before the cursor_sharing days) it would treat uppercase and lowercase queries are different. I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Subject: RE: Does the case of an Oracle query statement affect query perfo Raj, Do you have any test cases or white papers to support your statement? Especially the part about if you mix-n-match that will make Oracle do more work. never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional 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.*2
RE: Two Q's for SAP DBA's
Thank, Jared, Service.sap.com has a limited number of notes on Oracle partitioning (even though they went to great lengths to negotiate that option into their customer's Oracle licenses). And none that I've found that combine the topics of data archive partitioning. If I do find something, I'll pass it on. Mike -Original Message- Sent: Friday, September 27, 2002 10:57 AM To: [EMAIL PROTECTED]; Hand, Michael T On Wednesday 25 September 2002 12:33, Hand, Michael T wrote: Got a couple of questions for those of you dealing with SAP: 1) Has anyone heard of a timeline as to when (or if) SAP R3 will support Oracle 9.x? And, No, and I doubt it. 2) Has anyone implemented table partitions as a method of space management in conjunction SAP archiving? The table dependencies within SAP Archive objects would seem to make this difficult at best? Considered it, but haven't researched. Have you tried service.sap.com? Or whatever the support url is. Jared Thanks for any feedback. Mike Hand Polaroid Corp - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Memory Based FS on Solaris 8
It wouldn't Cause Other regulat File systems to Crash. However, if the Server crashes, you wouldn't be able to restart your database ! Database Instance recovery requires the online redo logs which wouldn't be available when your server restarts. Hemant At 11:53 PM 29-09-02 -0800, you wrote: 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
RE: datafile sizing question
Title: RE: datafile sizing question Thanks you Dennis, Rachel, Tim for the pointers. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 9:58 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question Raj Print http://otn.oracle.com/deploy/availability/pdf/defrag.pdf http://otn.oracle.com/deploy/availability/pdf/defrag.pdf - very well-written, direct from Oracle's site, so he will accept it as official. BTW - In this paper, for Oracle 8 and above, the correct extent sizes are 120-k, 4-m, and 128-m. The reasons you want to use these specific sizes are explained in the paper. *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Does the case of an Oracle query statement affect query perfo
I believe Raj is referring to the fact that Oracle will reuse SQL from the SQL Cache if the statement has been parsed already, but they must match verbatim. for example: a)SELECT column1, column2 FROM table WHERE column0 = 5; b)SELECT COLUMN1, COLUMN2 FROM TABLE WHERE COLUMN0 =5; will be stored in the sql cache with 2 different hash id's, so each will be stored separately in the cache. I have always found it recommended that a certain upper/lower case naming convention be followed to avoid this situation. -Joe --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Raj, Do you have any test cases or white papers to support your statement? Especially the part about if you mix-n-match that will make Oracle do more work. never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT: oracle-dba.com domain auction
What are you hoping to get for it? -Joe --- JOE TESTA [EMAIL PROTECTED] wrote: With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query perfo
All that he is referring to is the possibility that "mixing-n-matching" will cause the same SQL statement to be hashed differently, thus stored individually in the Shared SQL Area cache, thus more "hard parses" unnecessarily. More "hard-parses" is indeed "more work"... Though technically correct, there are many steps betweensomeone coding a SQL statement and this end-result of additional hard-parses... If a developer or end-user is working via a precompiler/interpreter such as PRO*C, SQLJ,or PL/SQL or many other reporting tools, then the upper- and lower-case issues will be largely made irrelevant as the precompiler/interpreter tends to set all SQL command-text some similar convention before passing to the RDBMS (i.e.all upper-case and remove all unnecessary white-space, etc)... If it is not SQL developers writing this SQL into program-modules but instead end-users working interactively, then you have to ask yourself how many times they can type in and execute SQL in order for the increased number of "hard-parses" to matter. Assume 200 ad-hoc interactive end-user sessions, each typing in and executing slightly different SQL 20 times per day. That's 4000 more "hard-parses" -- no big deal... There are likely more circumstances to consider... However, if the people doing this coding are developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD (i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to the RDBMS parser where it will indeed cause additional hard-parses. Since this code might be embedded inside a high-concurrency application, this problem could grow quite serious, especially if the developers follow-up this particular "bad habit" with other bad habits such as embedded literal data values, etc... As always, the severity of the problem is dependent on specific circumstances. It could be no problem at all, it could be the harbinger for serious problems... - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 7:48 AM Subject: RE: Does the case of an Oracle query statement affect query perfo Raj, Do you have any test cases or white papers to support your statement? Especially the part about "if you mix-n-match that will make Oracle do more work." never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 8:33 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Does the case of an Oracle query statement affect query perfo As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: 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) forALL my queries Scenario 2: I use the naming convention b) forALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
dabase hang on update statement
Hi list,
oracle 8.1.7.2
os AIX 4.3.3
Occasionally we had hang situation on our Financial production database.
The batch job supposed to finish within 2 hours at 3:30 am. But
sometimes it just hang there and never finished the process. We have to
kill the job in oracle session and os level. Since oracle just marked
killed status and takes long time to clean the resource. We have precise
tool to check all the activities within that time being. The problem
statement is a update sql.
I summarized the db activity here;
at 3:14 am, there were two similar update statement against same big
table. The first on shown a big cpu consumption but finished at 3:30am.
The second started around same time shown big cpu use at beginning and
hang there forever until we killed it at 9:00 am. The database shown big
i/0 wait on the statement. My question is why i/0 wait? It just hang the
whole database and didn't do any thing. Do you have any ideas? I am not
sure the statement was commited or not.
Thanks,
Joan
UPDATE PS_PAYMENT_TBL SET CANCEL_ACTION = 'P' WHERE PYMNT_ID
= :1 ANDPOST_STATUS_AP = 'P' ANDCANCEL_ACTION IN
('R','H','C')
UPDATE PS_PAYMENT_TBL SET POST_STATUS_AP =
'P', IN_PROCESS_FLG = 'N' WHERE PROCESS_INSTANCE =
:1 AND PYMNT_ID = :2 AND IN_PROCESS_FLG = 'Y'
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Does the case of an Oracle query statement affect query perfo
I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. That should have nothing to do with the case of a statement and everything to do with using literals. AFAIK, cursor_sharing does not change the case of a statement Saying that the case used to type in the statement causes a performance hit is not true. The performance hit comes from not standardizing the SQL statement, so that Oracle has to reparse it because although it's identical, the case is different so the statement is seen as different. You can use all uppercase, all lowercase, any combination of the two you want, as long as you are consistent. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Tom, Well it simply comes to when Oracle will parse the query and try to find a matching sql to hash to in SGA, if it finds one, it will hash to the same one, else it will have to create a new hash entry. In pre-8i (before the cursor_sharing days) it would treat uppercase and lowercase queries are different. I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, September 30, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Raj, Do you have any test cases or white papers to support your statement? Especially the part about if you mix-n-match that will make Oracle do more work. never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional 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.*2 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Title: RE: datafile sizing question If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation Performance Degradation ? If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ? NOTE - We have been Manually Specifyingthe Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER" for respective Tablespaces to Check Growth to Larger Numbers of Extents 100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes Oracle 8.1.7 -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of COUNTER, all the way up to values like 250,000 or 500,000, if you like. The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time. The time spent in the PL/SQL loop should be roughly linear with the value of COUNTER, the point being that each ALLOCATE EXTENT takes roughly the same amount of time. You might observe an "elbow" in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment. On my laptop, I've seen the curve stay linear up into the 100,000s. The time spent in DROPmay not vary a great deal; it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous. Try it out! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 6:33 AM Subject: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit.
Partitionin or not partition?
Hi dba's, I have the following questions about partitioning. Whe have a 20 million row's table (about 2G) and we are thinking if it's worth tho partition it. The table doesn't contain historical data , the data are inserted or deleted (very few updates) (about 20 insert/deletes per day) and is refreshed every night toward several (8) snapshot databases. One of the candidate field for the partition should be a field that contains a regional code (but in this case the partitions should be of very different sizes). On the snapshot databases the table is spread across several drives (raid 0+1) and the partition would go all on the same mount point, do you think the partition would increase the performances? Do you have any advices, hints for me? Thanks to all Francesco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cantisano Francesco - Matrix INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT: oracle-dba.com domain auction
Dr. Evil voice One million dollars... BWAHAHAHAHAHAHAHAHAHAHAHAHAHA -Original Message- Sent: Monday, September 30, 2002 10:33 AM To: Multiple recipients of list ORACLE-L What are you hoping to get for it? -Joe --- JOE TESTA [EMAIL PROTECTED] wrote: With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Unsuscribe
-Mensaje original-De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Tim GormanEnviado el: lunes, 30 de septiembre de 2002 17:04Para: Multiple recipients of list ORACLE-LAsunto: Re: Does the case of an Oracle query statement affect query perfo All that he is referring to is the possibility that "mixing-n-matching" will cause the same SQL statement to be hashed differently, thus stored individually in the Shared SQL Area cache, thus more "hard parses" unnecessarily. More "hard-parses" is indeed "more work"... Though technically correct, there are many steps betweensomeone coding a SQL statement and this end-result of additional hard-parses... If a developer or end-user is working via a precompiler/interpreter such as PRO*C, SQLJ,or PL/SQL or many other reporting tools, then the upper- and lower-case issues will be largely made irrelevant as the precompiler/interpreter tends to set all SQL command-text some similar convention before passing to the RDBMS (i.e.all upper-case and remove all unnecessary white-space, etc)... If it is not SQL developers writing this SQL into program-modules but instead end-users working interactively, then you have to ask yourself how many times they can type in and execute SQL in order for the increased number of "hard-parses" to matter. Assume 200 ad-hoc interactive end-user sessions, each typing in and executing slightly different SQL 20 times per day. That's 4000 more "hard-parses" -- no big deal... There are likely more circumstances to consider... However, if the people doing this coding are developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD (i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to the RDBMS parser where it will indeed cause additional hard-parses. Since this code might be embedded inside a high-concurrency application, this problem could grow quite serious, especially if the developers follow-up this particular "bad habit" with other bad habits such as embedded literal data values, etc... As always, the severity of the problem is dependent on specific circumstances. It could be no problem at all, it could be the harbinger for serious problems... - Original Message - From: Mercadante, Thomas F To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 7:48 AM Subject: RE: Does the case of an Oracle query statement affect query perfo Raj, Do you have any test cases or white papers to support your statement? Especially the part about "if you mix-n-match that will make Oracle do more work." never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 8:33 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Does the case of an Oracle query statement affect query perfo As long as you stick to either (a) or (b) you will be okay ... if you mix-n-match that will make Oracle do more work. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Shantanu Datta [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 3:58 AMTo: Multiple recipients of list ORACLE-LSubject: 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) forALL my queries Scenario 2: I use the naming convention b) forALL my queries Will there be any difference in the execution time of the same queries in Scenario 1 vs 2? Thanx in advance, Shantanu. --
RE: Does the case of an Oracle query statement affect query perfo
Title: RE: Does the case of an Oracle query statement affect query perform Thanks Tim, I didn't knew these differences between PROC/PLSQL and other modules ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:04 AM To: Multiple recipients of list ORACLE-L Subject: Re: Does the case of an Oracle query statement affect query perfo All that he is referring to is the possibility that mixing-n-matching will cause the same SQL statement to be hashed differently, thus stored individually in the Shared SQL Area cache, thus more hard parses unnecessarily. More hard-parses is indeed more work... Though technically correct, there are many steps between someone coding a SQL statement and this end-result of additional hard-parses... If a developer or end-user is working via a precompiler/interpreter such as PRO*C, SQLJ, or PL/SQL or many other reporting tools, then the upper- and lower-case issues will be largely made irrelevant as the precompiler/interpreter tends to set all SQL command-text some similar convention before passing to the RDBMS (i.e. all upper-case and remove all unnecessary white-space, etc)... If it is not SQL developers writing this SQL into program-modules but instead end-users working interactively, then you have to ask yourself how many times they can type in and execute SQL in order for the increased number of hard-parses to matter. Assume 200 ad-hoc interactive end-user sessions, each typing in and executing slightly different SQL 20 times per day. That's 4000 more hard-parses -- no big deal... There are likely more circumstances to consider... However, if the people doing this coding are developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD (i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to the RDBMS parser where it will indeed cause additional hard-parses. Since this code might be embedded inside a high-concurrency application, this problem could grow quite serious, especially if the developers follow-up this particular bad habit with other bad habits such as embedded literal data values, etc... As always, the severity of the problem is dependent on specific circumstances. It could be no problem at all, it could be the harbinger for serious problems... *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: Remember me? Oracle DBA veteran considering getting certifi
Title: RE: Remember me? Oracle DBA veteran considering getting certifi Ah - brethren - nice and complete adventure documented - thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 8:38 AM To: Multiple recipients of list ORACLE-L Subject: RE: Remember me? Oracle DBA veteran considering getting certifi Paula, Your experience sounds very similar to mine which I documented on http://www.hcresources.co.uk/ocp.htm http://www.hcresources.co.uk/ocp.htm . I used the Exam Cram series and was very happy with them. I am booked for the 8i upgrade next week but despite using 8i for however long it has been available I cannot believe how much there is to learn. I can see myself putting off the exam once again John -Original Message- Sent: 30 September 2002 04:48 To: Multiple recipients of list ORACLE-L 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- mailto:[EMAIL PROTECTED] ] Sent: Saturday, September 28, 2002 5:28 PM To: Multiple recipients of list ORACLE-L 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 http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query perfo
Rachel, This is what I thought, but list members say differently. I just tried a simple test: Ran the following two queries: select count(*) from tomsqltest; SELECT COUNT(*) FROM TOMSQLTEST; and then: select hash_value,executions,sql_text from v$sql where upper(sql_text) like '%TOMSQLTEST%' / HASH_VALUE EXECUTIONS SQL_TEXT -- -- -- 2930079574 3 select hash_value,executions,sql_text from v$sql w here upper(sql_text) like '%TOMSQLTEST%' 542760132 1 SELECT COUNT(*) FROM TOMSQLTEST 1802081865 1 select count(*) from tomsqltest Looks like Raj is correct. Both statements are listed as separate and different entries in the v$sql area. Learned something new today! I can go home and have a beer! Wooo-H! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 30, 2002 11:53 AM To: Multiple recipients of list ORACLE-L perfo I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. That should have nothing to do with the case of a statement and everything to do with using literals. AFAIK, cursor_sharing does not change the case of a statement Saying that the case used to type in the statement causes a performance hit is not true. The performance hit comes from not standardizing the SQL statement, so that Oracle has to reparse it because although it's identical, the case is different so the statement is seen as different. You can use all uppercase, all lowercase, any combination of the two you want, as long as you are consistent. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Tom, Well it simply comes to when Oracle will parse the query and try to find a matching sql to hash to in SGA, if it finds one, it will hash to the same one, else it will have to create a new hash entry. In pre-8i (before the cursor_sharing days) it would treat uppercase and lowercase queries are different. I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, September 30, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Raj, Do you have any test cases or white papers to support your statement? Especially the part about if you mix-n-match that will make Oracle do more work. never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional 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.*2 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-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: extremely long parse time
H, also running on 8.1.7.2 on HPUX 11, this comes back to me sub-second. My explain plan pukes in TOAD, but that's a TOAD issue...everything looks good in SQL*Plus. 1) Are you using CBO? 2) If yes from 1, verify that there are no stats gathered in SYS. 3) Try init.ora optimizer_max_permutations = 2000. The default is 8 in 8 and 8i and 2000 in 9i. Aside from cursor_sharing=force, that's the only parameter I have that I think could affect parse times that severely. I also have: optimizer_index_caching = 90 optimizer_index_cost_adj = 50 in my init.ora, in case those might also somehow affect parse time. I wouldn't think it would in this case, since these should be CBO-only and there shouldn't be stats on the data dictionary. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, September 30, 2002 10:04 AM To: Multiple recipients of list ORACLE-L Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds on the wall clock to parse: select null as table_cat, owner as table_schem, table_name, 0 as NON_UNIQUE, null as index_qualifier, null as index_name, 0 as type, 0 as ordinal_position, null as column_name, null as asc_or_desc, num_rows as cardinality, blocks as pages, null as filter_condition from all_tables where table_name = 'INDEXENTRIES' union select null as table_cat, i.owner as table_schem, i.table_name, decode (i.uniqueness, 'UNIQUE', 0, 1), null as index_qualifier, i.index_name, 1 as type, c.column_position as ordinal_position, c.column_name, null as asc_or_desc, i.distinct_keys as cardinality, i.leaf_blocks as pages, null as filter_condition from all_indexes i, all_ind_columns c where i.table_name = 'INDEXENTRIES' and i.index_name = c.index_name and i.table_owner = c.table_owner and i.table_name = c.table_name and i.owner = c.index_owner order by non_unique, type, index_name, ordinal_position Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Matt, Is it Oracle 9? If yes, time is in microseconds. Alex. -Original Message- Sent: Thursday, September 26, 2002 10:30 AM To: Multiple recipients of list ORACLE-L why would a query take 148 seconds to parse? It is a two way union where the first half is going against all_tables and the second half is a join between all_indexes and all_ind_columns. The shared pool has 50M large parts of it are free. I generated a 10046 (level 12) trace, and I just don't see anything out of whack, except of the c=14868 in the PARSE #1 line. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Oracle 8i R3, and 9i R2 on Same NT Box
Hello, Does anybody out there run Oracle 8.1.7 and Oracle 9.2 on the same NT (or Windows 2000) box? Is it running smoothly. Any difficulties installing or running both versions on the same box? I ask because one of my colleagues has encountered difficulties installing and running Oracle 8.1.7 and Oracle 9.2 on the same NT box. Thanks for any input. Sam Bootsma, OCP Technical Support Analyst CPAS Systems Inc. 416-422-0563 x237 [EMAIL PROTECTED] http://www.cpas.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Bootsma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT: oracle-dba.com domain auction
At 06:58 AM 9/30/02 -0800, you wrote: With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe Well, just for fun I bid on it (so at least it's not at $101.50). But then I got this spam from someone: From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Sat, 28 Sep 2002 02:53:56 PDT >From member: brightinitiatives-com If you're interested we own OracleDBAs.com and are willing to sell. If not, my apologies for contacting you. Best - Maybe everyone's just in deep negotiations with brightinitiatives.com, hoping to get a better deal than $102.50 ;) Kent -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kent Wayson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: datafile sizing question
Title: RE: datafile sizing question I don't know if you intended to "shout", but using color for your reply certainly does so... Can you prove any benefit from the extraordinaryactions of overriding LMT extent control and using EXP/IMP, ALTER TABLE ... MOVE, ALTER INDEX ... REBUILD,or whatever, when number of extents is the only criteria? If so, was it DML or DDL statementswhich demonstratedimproved performance? Also, if there was any testing performed, then how did you isolate the issue of "number/size of extents" away from the issues of "row-migration" and 'blocks made empty due to deletion activity'? What you are referring to as "fragmentation" (whichhas never meantsimply "the number of extents") does not equate to "performance degradation" at all. There is a difference in the possible performanceimpact of number/size of extents on DML statements as opposed toDDL statements, as previously explained. Simply put, the "conventional wisdom" left overregarding extents indictionary-managed tablespaces has little or no application in the world since the introduction of locally-managed tablespaces. For example, ahundred million extents for a table or indexwould *not* impact the performance of indexed scans (i.e.DML)in *any* way whatsoever (due to single-block, random-access reads). However, this situation would be catastrophic to any DDL involving extent allocation/deallocationin a dictionary-managed tablespace (requiring database recreation) as *every* other DDL involving extent allocation/deallocation could be crippled (due to abused cluster tables in the UET$/SEG$ cluster in the data dictionary). By the same token, dropping or truncatinga table with a hundred million extents would take a while even in locally-managed tablespaces, but the impact would not be as globally catastrophic for the rest of the segments in the database or for the database's data dictionary itself. - Original Message - From: VIVEK_SHARMA To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Monday, September 30, 2002 9:05 AM Subject: RE: datafile sizing question If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation Performance Degradation ? If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ? NOTE - We have been Manually Specifyingthe Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER" for respective Tablespaces to Check Growth to Larger Numbers of Extents 100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes Oracle 8.1.7 -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of
Re: Oracle 8i R3, and 9i R2 on Same NT Box
This is interesting. I have installed and run multiple Oracle versions on the same Unix box but not NT. Sam Bootsma wrote: Hello, Does anybody out there run Oracle 8.1.7 and Oracle 9.2 on the same NT (or Windows 2000) box? Is it running smoothly. Any difficulties installing or running both versions on the same box? I ask because one of my colleagues has encountered difficulties installing and running Oracle 8.1.7 and Oracle 9.2 on the same NT box. Thanks for any input. Sam Bootsma, OCP Technical Support Analyst CPAS Systems Inc. 416-422-0563 x237 [EMAIL PROTECTED] http://www.cpas.com -- ltiu 3/4 OCP 9i Eh? -- 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).
urgent help: replication, shareplex
Hi All, I needed some help here involving shareplex. We run two databases (an OLTP type, and a repository type) on the same E10K domain which has 8 CPUs and 8GB of RAM, using Hatachi SAN (RAID 5). Shareplex is being used to replicate a table from the OLTP type to the repository. The current volume we are getting is about 40 inserts per second to the OLTP. And at this rate shareplex is lagging behind doing the replication, for 24 hours now. And I see the shareplex process running at 10% while all Oracle processes are below 1% of the CPU. The situation is complicated, because it involves a hosting company. For instance, I would like to run the two databases on two separate domains but they chose not to. So they are blaming our application for doing commit on every insert being the problem. I agree that (and I will make the change) to commit every 1000 inserts or so. However, I don't believe that's the root of the problem. From what I understand, shareplex is log based replication and should not be as resource instensive. And 40 per/second isn't a huge volume per se and I am sure shareplex can handle a lot more than that. So any suggestions, feedbacks are welcome. Thanks Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Does the case of an Oracle query statement affect query perfo
I never said both wouldn't be listed separately... in fact I said they WOULD. I said cursor_sharing would NOT change case, but would only affect the statement if you used a literal in it. --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Rachel, This is what I thought, but list members say differently. I just tried a simple test: Ran the following two queries: select count(*) from tomsqltest; SELECT COUNT(*) FROM TOMSQLTEST; and then: select hash_value,executions,sql_text from v$sql where upper(sql_text) like '%TOMSQLTEST%' / HASH_VALUE EXECUTIONS SQL_TEXT -- -- -- 2930079574 3 select hash_value,executions,sql_text from v$sql w here upper(sql_text) like '%TOMSQLTEST%' 542760132 1 SELECT COUNT(*) FROM TOMSQLTEST 1802081865 1 select count(*) from tomsqltest Looks like Raj is correct. Both statements are listed as separate and different entries in the v$sql area. Learned something new today! I can go home and have a beer! Wooo-H! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, September 30, 2002 11:53 AM To: Multiple recipients of list ORACLE-L perfo I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. That should have nothing to do with the case of a statement and everything to do with using literals. AFAIK, cursor_sharing does not change the case of a statement Saying that the case used to type in the statement causes a performance hit is not true. The performance hit comes from not standardizing the SQL statement, so that Oracle has to reparse it because although it's identical, the case is different so the statement is seen as different. You can use all uppercase, all lowercase, any combination of the two you want, as long as you are consistent. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Tom, Well it simply comes to when Oracle will parse the query and try to find a matching sql to hash to in SGA, if it finds one, it will hash to the same one, else it will have to create a new hash entry. In pre-8i (before the cursor_sharing days) it would treat uppercase and lowercase queries are different. I don't have papers to substantiate this, but in our 9012 database before we started using cursor_sharing we used to run out of our 600M SGA, but since we started using CS, it went down. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, September 30, 2002 9:48 AM To: Multiple recipients of list ORACLE-L Raj, Do you have any test cases or white papers to support your statement? Especially the part about if you mix-n-match that will make Oracle do more work. never heard of this before and I am interested if it is true. Tom Mercadante Oracle Certified Professional 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.*2 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
Re: MIcrosoft Blackmail
Wow! What a Freudian slip. I had intended to type 'Microsoft', though I was *thinking* Microslop. Jared Jared Still [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/30/2002 07:03 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: MIcrosoft Blackmail Microslop is not a private company, and has not been for quite some years now. Jared On Monday 30 September 2002 03:33, Yechiel Adar wrote: DENNIS, I think that I did not explain my idea. I do not understand the complain of Thomas. I do not see any harm in a company choosing its dealers based on their commitment to the goals of my company. Microsoft has a right to prefer dealer who embrace the .net, or do you think that anybody have the right to tell a PRIVATE company who to deal with? Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 5:18 PM Yechiel - But all things are NEVER equal. So companies end up doing stupid things because of some larger motive. You end up buying crappy computers because your boss thinks it will impress the CEO with how you are loyally supporting someone that somehow supports your company. Ironic isn't it. When the PC industry began, the computer industry was firmly dominated by IBM. PC enthusiasts were a bunch of starry-eyed dreamers that though they could wrestle computing away from the computer priesthood and bring freedom to everyman. In many ways the Internet has made that dream come true. But then we have Microsoft talking about creating a new security system for my computer that on one hand will protect me from bad things and on the other hand will protect the products of large corporations from me. In a great number of ways Microsoft resembles the IBM of the past. obligatory Oracle reference Of course Larry Ellison only wishes he had these type of issues to deal with. /obligatory Oracle reference Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 8:43 AM To: Multiple recipients of list ORACLE-L What exactly is your problem? Lets say that you are a factory that sells paper. You need to buy a computer system. One supplier also sell printers and the other advocate paperless office. All things being equal, which one will you give your business to?? Yechiel Adar Mehish - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Thursday, September 26, 2002 1:13 AM This came to our DBA team today.I'd appreciate your thoughts. I'm not a business guy, just a plain old Apps DBA, but this really pisses me off. Is it common practice by MS? It is important from an Architecture point of view that we understand all the various approaches to web services (also known as grid computing -- see my recent report). Microsoft's dot Net initiative is their approach to this grand overarching software strategy. There is a second reason why we might be interested specifically in dot Net. Subsidiary XYZ earns $xyz a year for us from Microsoft by [performing certain services], etc. Microsoft has told our management that one of their criteria for evaluating their vendors will be how good of a MS customer is the potential vendor. Specifically, has the vendor bought in to the dot Net strategy. Now we aren't going to make our global enterprise solutions strategy decisions based upon that point alone, but it's not something we are going to ignore either. Therefore, I support investigating SQL server, Biz Talk, and dot Net, but I emphasize the word INVESTIGATING. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to:
FW: urgent help: replication, shareplex
Oh, and I forgot to add that the table that's being replicated is partitioned in the repository database. with local indexes. Each partition holds a day worth of volume. -Original Message- From: Ji, Richard Sent: Monday, September 30, 2002 12:08 PM To: Multiple recipients of list ORACLE-L (E-mail) Subject: urgent help: replication, shareplex Hi All, I needed some help here involving shareplex. We run two databases (an OLTP type, and a repository type) on the same E10K domain which has 8 CPUs and 8GB of RAM, using Hatachi SAN (RAID 5). Shareplex is being used to replicate a table from the OLTP type to the repository. The current volume we are getting is about 40 inserts per second to the OLTP. And at this rate shareplex is lagging behind doing the replication, for 24 hours now. And I see the shareplex process running at 10% while all Oracle processes are below 1% of the CPU. The situation is complicated, because it involves a hosting company. For instance, I would like to run the two databases on two separate domains but they chose not to. So they are blaming our application for doing commit on every insert being the problem. I agree that (and I will make the change) to commit every 1000 inserts or so. However, I don't believe that's the root of the problem. From what I understand, shareplex is log based replication and should not be as resource instensive. And 40 per/second isn't a huge volume per se and I am sure shareplex can handle a lot more than that. So any suggestions, feedbacks are welcome. Thanks Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: OT: oracle-dba.com domain auction
Hey Kent, I just saw your e-mail address and was curious...what does MPS use Oracle for? Big DBs? Little ones? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, September 30, 2002 12:03 PM To: Multiple recipients of list ORACLE-L At 06:58 AM 9/30/02 -0800, you wrote: With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe Well, just for fun I bid on it (so at least it's not at $101.50). But then I got this spam from someone: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Selecting Next X Values From Dual
You may find this article interesting: http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html Jared Gary Chambers [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/27/2002 11:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Selecting Next X Values From Dual Stephane... Thanks for the reply. SELECT sequence.nextval FROM table WHERE ROWNUM x It just seems too kludgy, and I didn't think creating a table with dummy data for the count would be a good way to go. Any suggestions? Why 'too kludgy' ? If you are inside a PL/SQL package have you though of a bulk collect into an array of numbers? It seems kludgy to me to have a table of n-rows of dummy data to grab a specific number of values from a sequence. Is this a common practice? Gary Chambers //- // Lucent Technologies GIO/Unix // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: extremely long parse time
Title: RE: extremely long parse time We are using First_rows for the optimizer mode, but the last_analyzed column in DBA_TABLES and DBA_INDEXES is NULL for all objects owned by SYS. The really wierd part is: Changing the query to use rule based optimization (via the /*+ RULE */ hint caused it to execute sub-second. Why would optimization mode affect parsing? Is query optimization considered part of the parsing routine? Matt -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:36 AM To: '[EMAIL PROTECTED]' Cc: Adams, Matthew (GEA, MABG, 088130) Subject: RE: extremely long parse time H, also running on 8.1.7.2 on HPUX 11, this comes back to me sub-second. My explain plan pukes in TOAD, but that's a TOAD issue...everything looks good in SQL*Plus. 1) Are you using CBO? 2) If yes from 1, verify that there are no stats gathered in SYS. 3) Try init.ora optimizer_max_permutations = 2000. The default is 8 in 8 and 8i and 2000 in 9i. Aside from cursor_sharing=force, that's the only parameter I have that I think could affect parse times that severely. I also have: optimizer_index_caching = 90 optimizer_index_cost_adj = 50 in my init.ora, in case those might also somehow affect parse time. I wouldn't think it would in this case, since these should be CBO-only and there shouldn't be stats on the data dictionary. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds on the wall clock to parse: select null as table_cat, owner as table_schem, table_name, 0 as NON_UNIQUE, null as index_qualifier, null as index_name, 0 as type, 0 as ordinal_position, null as column_name, null as asc_or_desc, num_rows as cardinality, blocks as pages, null as filter_condition from all_tables where table_name = 'INDEXENTRIES' union select null as table_cat, i.owner as table_schem, i.table_name, decode (i.uniqueness, 'UNIQUE', 0, 1), null as index_qualifier, i.index_name, 1 as type, c.column_position as ordinal_position, c.column_name, null as asc_or_desc, i.distinct_keys as cardinality, i.leaf_blocks as pages, null as filter_condition from all_indexes i, all_ind_columns c where i.table_name = 'INDEXENTRIES' and i.index_name = c.index_name and i.table_owner = c.table_owner and i.table_name = c.table_name and i.owner = c.index_owner order by non_unique, type, index_name, ordinal_position Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time Matt, Is it Oracle 9? If yes, time is in microseconds. Alex. -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 26, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: extremely long parse time why would a query take 148 seconds to parse? It is a two way union where the first half is going against all_tables and the second half is a join between all_indexes and all_ind_columns. The shared pool has 50M large parts of it are free. I generated a 10046 (level 12) trace, and I just don't see anything out of whack, except of the c=14868 in the PARSE #1 line.
RE: Does the case of an Oracle query statement affect query
Well, if oracle stores all the meta data in UPPERCASE, then for a query in which object names are written in lowercase characters will force the parser to convert it into UPPERCASE for comparison, in which case there should be a negligible difference if the name of the objects referenced by the query is written in UPPERCASE. But that should be so negligible, that it can be safely and wisely ignored Regards Naveen -Original Message- Sent: Monday, September 30, 2002 6:18 PM To: Multiple recipients of list ORACLE-L No, there will not be any noticable difference in performance. -Mark On Mon, 2002-09-30 at 03:58, Shantanu Datta wrote: 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. -- -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dabase hang on update statement
Joan,
You may be able to diagnose this when it happens again by logging
in as SYS using svrmgrl.
Run the following SQL statement if the logon was successful:
select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
order by s.username, upper(e.event)
/
This may indicate a wait on an internal resource. It won't tell you
why it's waiting, but you'll know which area to look at.
Jared
Joan Hsieh [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/30/2002 08:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:dabase hang on update statement
Hi list,
oracle 8.1.7.2
os AIX 4.3.3
Occasionally we had hang situation on our Financial production database.
The batch job supposed to finish within 2 hours at 3:30 am. But
sometimes it just hang there and never finished the process. We have to
kill the job in oracle session and os level. Since oracle just marked
killed status and takes long time to clean the resource. We have precise
tool to check all the activities within that time being. The problem
statement is a update sql.
I summarized the db activity here;
at 3:14 am, there were two similar update statement against same big
table. The first on shown a big cpu consumption but finished at 3:30am.
The second started around same time shown big cpu use at beginning and
hang there forever until we killed it at 9:00 am. The database shown big
i/0 wait on the statement. My question is why i/0 wait? It just hang the
whole database and didn't do any thing. Do you have any ideas? I am not
sure the statement was commited or not.
Thanks,
Joan
UPDATE PS_PAYMENT_TBL SET CANCEL_ACTION = 'P' WHERE PYMNT_ID
= :1 ANDPOST_STATUS_AP = 'P' ANDCANCEL_ACTION IN
('R','H','C')
UPDATE PS_PAYMENT_TBL SET POST_STATUS_AP =
'P', IN_PROCESS_FLG = 'N' WHERE PROCESS_INSTANCE =
:1 AND PYMNT_ID = :2 AND IN_PROCESS_FLG = 'Y'
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joan Hsieh
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 8i R3, and 9i R2 on Same NT Box
Yes, I am. I needed an 8i client because some programs still can't talk 9i. The only issues I've run into are with the products themselves. Some Quest products have problems with multiple Oracle Homes. Their solution is to hack the Registry to make the LAST_ORACLE_HOME the one to point the Quest products to. Obviously, this is a hack and the product should be fixed, but you should at least be aware of the issue. Other than that, I *highly* recommend NEVER uninstalling ANY Oracle product on Windows if you are also working with ODBC or 3rd party tools. I've only had success wiping out all Oracle Homes and reinstalling. Granted, my WinTuke (Win2K) box is mostly used as a client. Servers may be different. Just my $0.02... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Subject: Oracle 8i R3, and 9i R2 on Same NT Box Hello, Does anybody out there run Oracle 8.1.7 and Oracle 9.2 on the same NT (or Windows 2000) box? Is it running smoothly. Any difficulties installing or running both versions on the same box? I ask because one of my colleagues has encountered difficulties installing and running Oracle 8.1.7 and Oracle 9.2 on the same NT box. Thanks for any input. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Cache OCI Calls to Improve Oracle Performance on Solaris[tm] Syst
Anyone tried this one? http://soldc.sun.com/articles/oci_cache.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Utl_file and OPENVMS
Hey Ron, If I'm not mistaken, access to the UTL_FILE directories needs to be given to the account that started the Oracle instance and not an Oracle schema name. Also, make sure that your entries for the filenames and directories in the UTL_FILE packages are UPPER CASE, or at least match the init.ora parameter UTL_FILE_DIR in case. ODS-2 volumes in VMS (the only one supported by Oracle for 8i) only allow UPPER CASE file names. It's probably a good idea to uppercase the RMS filenames and directories in your procedure and in the init.ora Make sure the file you are creating is a valid VMS name. From DCL in the Oracle instance's account (default is ORACLE), try CREATE myfilename, where myfilename is the exact name that would be passed to UTL_FILE, with the directory. If this succeeds, you can terminate the CREATE statement with a CTRL-Z or CTRL-C in most cases. Also, beware that because DCL automagically uppercases everything in the command line that isn't in quotes, the CREATE is not a good test for case-sensitivity. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 9:53 AM To: Multiple recipients of list ORACLE-L Subject: Utl_file and OPENVMS List, I have a package that creates files on the server. The directory location and file name are obtained from tables in oracle. The procedure works as designed on Novell 7.3.4 and no changes were needed when the database way loaded on Linux Oracle 8.1.7. I am trying to move the database from the Novell to an OPENVMS Oracle 8.1.7 server and I can't get the package to write the files to the OS directory. The package is created by the Oracle user DTSUSER and executed by DTSUSER. There is no OPENVMS user DTSUSER. The sysadmin assures me that the permissions are correct to write to the directory. I have place a Dbms_output in the package to display the directory information and it looks correct. Is there anything different that has to be done to an OPENVMS server that will allow a package to write to a directory using the Utl_File package? [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: extremely long parse time
Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or DBA_ANALYZE_OBJECTS? Is the COST column in your PLAN_TABLE null??? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Monday, September 30, 2002 12:38 PM To: Multiple recipients of list ORACLE-L We are using First_rows for the optimizer mode, but the last_analyzed column in DBA_TABLES and DBA_INDEXES is NULL for all objects owned by SYS. The really wierd part is: Changing the query to use rule based optimization (via the /*+ RULE */ hint caused it to execute sub-second. Why would optimization mode affect parsing? Is query optimization considered part of the parsing routine? Matt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 8i R3, and 9i R2 on Same NT Box
I am currently running 8.1.5, 8.1.7, 9.0.1.2 and 9.2 on the same Win2k Pro machine. I rarely have more than 2 running at the same time due to memory considerations, but I have not encountered any problems related to install/runtime. I always make sure I install in order of oldest to newest. I also do the once a year cleaning (reformat c:\ and reinstall everything...). Dan Fink -Original Message- Sent: Monday, September 30, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Yes, I am. I needed an 8i client because some programs still can't talk 9i. The only issues I've run into are with the products themselves. Some Quest products have problems with multiple Oracle Homes. Their solution is to hack the Registry to make the LAST_ORACLE_HOME the one to point the Quest products to. Obviously, this is a hack and the product should be fixed, but you should at least be aware of the issue. Other than that, I *highly* recommend NEVER uninstalling ANY Oracle product on Windows if you are also working with ODBC or 3rd party tools. I've only had success wiping out all Oracle Homes and reinstalling. Granted, my WinTuke (Win2K) box is mostly used as a client. Servers may be different. Just my $0.02... Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Sam Bootsma [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Subject: Oracle 8i R3, and 9i R2 on Same NT Box Hello, Does anybody out there run Oracle 8.1.7 and Oracle 9.2 on the same NT (or Windows 2000) box? Is it running smoothly. Any difficulties installing or running both versions on the same box? I ask because one of my colleagues has encountered difficulties installing and running Oracle 8.1.7 and Oracle 9.2 on the same NT box. Thanks for any input. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Strange performance problem
I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: extremely long parse time
Title: RE: extremely long parse time OK, I think we're on to something here. The DBA_TAB_COL_STATISTICS shows no rows for tables owned by sys (although strangely, owner is not a column of this table). However, the DBA_ANALYZE_OBJECTS view IS listing objects owned by SYS, which implies that they have been analyzed in the past. Since I don't see any of the statisics filled in on the DBA_TABLES entries for tables owned by SYS, what would you recommend doing at this point? analyze table sys.X delete statistics? Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 1:27 PM To: '[EMAIL PROTECTED]' Cc: Adams, Matthew (GEA, MABG, 088130) Subject: RE: extremely long parse time Hmmm...anything SYS in DBA_TAB_COL_STATISTICS or DBA_ANALYZE_OBJECTS? Is the COST column in your PLAN_TABLE null??? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time We are using First_rows for the optimizer mode, but the last_analyzed column in DBA_TABLES and DBA_INDEXES is NULL for all objects owned by SYS. The really wierd part is: Changing the query to use rule based optimization (via the /*+ RULE */ hint caused it to execute sub-second. Why would optimization mode affect parsing? Is query optimization considered part of the parsing routine? Matt
Re: ok so i'm bored working on rman scripts
Joe, What are you trying to do with rman? Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 2:17 PM so i took the first 2 stories and put them on http://www.oracle-dba.com/bdbafh nothing pretty, maybe i'll mess with it later today, text only so far. feel free to submit your part of the on-going saga to [EMAIL PROTECTED] joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT: oracle-dba.com domain auction
Yes, obviously this wasn't meant to go to the list... blush Rich -Original Message- From: Jesse, Rich Sent: Monday, September 30, 2002 12:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: OT: oracle-dba.com domain auction Hey Kent, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA work load - BDBAFH #1
The phone rings. Another user. Still pissed off, I pick it up... Database Administration, can I help you? I answer professionally. Troubled silence on the phone. Politeness is a very bad signal from me and they know it. He was rattled. Uh, hi, this is Joe, technical lead on that super-critical project for Benefits? Is this a good time? I have some stuff I need moved to production? The last was a statement, but it came out like a question. With a slight quaver in the voice, too. Excellent. Technical lead my arse. Could you be more specific so I can schedule you appropriately? He hesitated. Gosh, I sounded for real. I almost convinced myself. The victim approached warily. I've got a bunch of PL/SQL packages and some outlines to speed up the queries with special hints. The scripts are all ready for your review, they include the create statements, the grants, everything. I'm forwarding the email package to you with signoffs from IT, the user department, and your own from reviewing our design and test results. This last was delivered with almost pathetic eagerness. Good boy. Good, simple, foolish boy. If he could see me he'd be terrified by my grin. Joe, I need you to help me out. You've just given me 10 minutes of work, but I'm due for lunch in 5 minutes. What do you suggest I do? Joe knew better, he really did. But his team had been up all night finishing and the prize was so close... Look, I really hate to impose. But we've missed several major deadlines, and department head has made it clear if we screw up again he'll outsource the whole project and have us laid off. I need it now so we can make sure everything's perfect for the big production run at COB today. Consider it done, I promised cheerfully and hung up. I surprised him, and maybe myself, with my good spirits. Especially since I was more than 5 minutes late, closer to fifteen, and my buddies were already into their second beer when I joined them for lunch. But the extra 10 minutes had been well worth it considering what I managed to do to those hints with the outline editor. Just the same, though, I turned off my cell phone in case the twit called to find out why his 5-second queries took almost an hour. Can't have him taking me for granted, can I? Later that evening, after quaffing several (all right, numerous) more ales with the boys, I dialed in from home to check how things were going. Mr. Tech Lead was still logged in, no doubt desperately trying to determine why things were taking forever. Poor Mr. Tech Lead, another sleepless night. I logged off, turned out the light, and slept like a baby. Next morning, hangover. The phone rings. I snatch it up angrily... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Strange performance problem
I have seen something like this in the past and it was because there were two tables - named the same in two different schemas (public synonym, private synonym and all that mess).. Do you know whether this could be the same case as yours? Also check to see if the explain plan differs when u run it under different schemas. Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 1:33 PM I got a call from a customer earlier. He said that he was trying to run a query and it was taking way too long. He ran the same query last Friday and it came back in seconds. I looked at it in OEM and noticed that two of the tables were being accessed by full table scans. These tables have 22,000+ and 24,000+ rows each. I took the sql from OEM and ran it in a svrmgrl session (connected internal), and it came back in seconds. His still hadn't come back. To further complicate things, I connected as the owner of the tables (the same user he was using) and ran the query again. This time, I ended up killing it after 10 minutes. I'm confused as to what can cause such a difference in performance from sys to another user. The server did crash sometime over the weekend. He said that it was fine before the crash. I ran a dbverify on all of the data files and came up with nothing. The vitals are: Oracle 8.1.6.0.0 Digital Unix V4.0F (Rev. 1229) Unfortunately, upgrading Oracle isn't an option because the processor is too old. Oracle won't support it on any versions higher than 8.1.6. A patchset may be possible, though. Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Babu Nagarajan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 work load - BDBAFH #1
LMAO -Original Message- Sent: Monday, September 30, 2002 2:58 PM To: Multiple recipients of list ORACLE-L The phone rings. Another user. Still pissed off, I pick it up... Database Administration, can I help you? I answer professionally. Troubled silence on the phone. Politeness is a very bad signal from me and they know it. He was rattled. Uh, hi, this is Joe, technical lead on that super-critical project for Benefits? Is this a good time? I have some stuff I need moved to production? The last was a statement, but it came out like a question. With a slight quaver in the voice, too. Excellent. Technical lead my arse. Could you be more specific so I can schedule you appropriately? He hesitated. Gosh, I sounded for real. I almost convinced myself. The victim approached warily. I've got a bunch of PL/SQL packages and some outlines to speed up the queries with special hints. The scripts are all ready for your review, they include the create statements, the grants, everything. I'm forwarding the email package to you with signoffs from IT, the user department, and your own from reviewing our design and test results. This last was delivered with almost pathetic eagerness. Good boy. Good, simple, foolish boy. If he could see me he'd be terrified by my grin. Joe, I need you to help me out. You've just given me 10 minutes of work, but I'm due for lunch in 5 minutes. What do you suggest I do? Joe knew better, he really did. But his team had been up all night finishing and the prize was so close... Look, I really hate to impose. But we've missed several major deadlines, and department head has made it clear if we screw up again he'll outsource the whole project and have us laid off. I need it now so we can make sure everything's perfect for the big production run at COB today. Consider it done, I promised cheerfully and hung up. I surprised him, and maybe myself, with my good spirits. Especially since I was more than 5 minutes late, closer to fifteen, and my buddies were already into their second beer when I joined them for lunch. But the extra 10 minutes had been well worth it considering what I managed to do to those hints with the outline editor. Just the same, though, I turned off my cell phone in case the twit called to find out why his 5-second queries took almost an hour. Can't have him taking me for granted, can I? Later that evening, after quaffing several (all right, numerous) more ales with the boys, I dialed in from home to check how things were going. Mr. Tech Lead was still logged in, no doubt desperately trying to determine why things were taking forever. Poor Mr. Tech Lead, another sleepless night. I logged off, turned out the light, and slept like a baby. Next morning, hangover. The phone rings. I snatch it up angrily... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Conboy, Jim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
disable validate on a partitioned table?
Title: disable validate on a partitioned table? I read the following in the Oracle 8.1 manual: Oracle8i SQL Reference, Release 3 (8.1.7), Part Number A85397-01 SQL Statements: CREATE SYNONYM to DROP ROLLBACK SEGMENT, 3 of 31 DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed. If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index. This seems to say that with the disable validate constraint, Oracle will not need to do a full table scan during a load to find out if the value is unique, even though there is no index. How is that possible? Assuming that it's true, wouldn't it be beneficial to have the constraint disabled only during the exchange partition or sql*load time? When the load is done, the constraint should be re-enabled? Would this be a real-life example of how the disable validate constraint would be created? create table bank_account (account# number (6) not null, name varchar2 (30) ) partition by range (account#) (partition bank_account_part_0 values less than (10), partition bank_account_part_1 values less than (20), partition bank_account_part_max values less than (maxvalue) ) ; alter table bank_account add (constraint bank_account_uq1 unique (account#) disable validate ) ; N.B. The unique constraint is on the partition column.
TSPITR Question
List I have the opportunity to learn first-hand about TSPITR today. I need to recover a good bit of data that was recently deleted, and do not have a recent enough export to work from. My question is this Im reading the documentation now, and one of the big, bold Notes is that you should *NOT* try TSPITR for the first time on a production database, or if you have a time constraint. From looking at the instructions, I believe that I understand what to do, and while I would love to test this on another instance, I may not be able to. So I ask you do you know of any gotchas that I need to be aware of?? Thanks In Advance, Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] | --
RE: oracle-dba.com domain auction
I will up the price to $150.00 -Original Message-From: JOE TESTA [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 10:58 AMTo: Multiple recipients of list ORACLE-LSubject: OT: oracle-dba.com domain auction With as much interest as i'd seen, i'd thought it would have been above $102.50, oh well unless you all run up the price in the last few days, guess i'll just be holding onto it for a while. joe
Data guard
Hi Does some one set up data guard in oracle 8.1.7? can some on send stuff how to administor data guard? If Data guard has set up in between server1 and server2 then how to stop and restart instance and data guard? Thx -Seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Perl::DBI problems after charset change
Hey all, We've just changed our charactersets on our 8.1.7.2 (and 8.1.7.4) DBs from US7ASCII to WE8ISO8859P1 using the Oracle-approved ALTER DATABASE CHARACTER SET WE8ISO8859P1 and accompanying commands. Everything works like a champ, but our Perl::DBI connections now all cause an invisibile ORA-1017 invalid username/password error. The error never appears in the client -- the only reason I know this happens is because I'm auditing for it. Other than having our audit log tablespace fill up, this leaves me a little worried and I don't know how to track it down. Of course, since the apps all work without reporting the error we never caught it in our testing. The version of Perl is 5.005_03, the Oracle client is 8.0.5.0.0 on Solaris, and I don't know what version of DBI or DBD::Oracle. I've tested my much newer versions of Perl, Oracle client and DBI/DBD::Oracle under windows and no audit is generated. I've also connected using SQL*plus from the 8.0.5.0.0 client without audit. I'm going to try and debug this without affecting the production systems, but has anyone encountered this before? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA work load - BDBAFH #1
I don't know how he can live with himself LMAO -Original Message- Sent: Monday, September 30, 2002 2:58 PM To: Multiple recipients of list ORACLE-L The phone rings. Another user. Still pissed off, I pick it up... Database Administration, can I help you? I answer professionally. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
STILL HAPPENING: Metalink problems loading images?
OK, this is still bombing. nslookup gts214.us.oracle.com fails here as well as thru Telocity (DirecTV). Feedback from MetaLink says to flush my cache sigh. Anyone else having problems getting images from Metalink? - Or do I need to talk with our networking group about the firewall? This link, taken directly from the Metalink My Headlines page, errors out for me with could not locate remote server: http://gts214.us.oracle.com:8000/images/metalink/usaeng/navbar/metalink/nav_ library_off.gif TIA! Rich -- Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: STILL HAPPENING: Metalink problems loading images?
Title: RE: STILL HAPPENING: Metalink problems loading images? On what kind of page do you see the error? I can't get to gts214... either but when I go to the Metalink start page I don't see any images missing. -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] OK, this is still bombing. nslookup gts214.us.oracle.com fails here as well as thru Telocity (DirecTV). Feedback from MetaLink says to flush my cache sigh. Anyone else having problems getting images from Metalink?
RE: disable validate on a partitioned table?
Title: disable validate on a partitioned table? As you know for a partitioned table: unique constraints could be enforced by a local index or global index. For local index: the unique key will be part of the partitioning key. So for a partitioned table with a unique key that is a part of the partitioning key, Loading a partition or exchanging it does not require a full table scan or reading all partitions. Uniqueness will be checked in memory during the load/exchange process for only one partition. But if the unique key is not part of the partitioning key, reading the key data from all the partitions and checking it against the new loaded data in memory for any duplicates will be required. Waleed -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 4:48 PMTo: Multiple recipients of list ORACLE-LSubject: disable validate on a partitioned table? I read the following in the Oracle 8.1 manual: Oracle8i SQL Reference, Release 3 (8.1.7), Part Number A85397-01 SQL Statements: CREATE SYNONYM to DROP ROLLBACK SEGMENT, 3 of 31 DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed. If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index. This seems to say that with the disable validate constraint, Oracle will not need to do a full table scan during a load to find out if the value is unique, even though there is no index. How is that possible? Assuming that it's true, wouldn't it be beneficial to have the constraint disabled only during the exchange partition or sql*load time? When the load is done, the constraint should be re-enabled? Would this be a real-life example of how the disable validate constraint would be created? create table bank_account (account# number (6) not null, name varchar2 (30) ) partition by range (account#) (partition bank_account_part_0 values less than (10), partition bank_account_part_1 values less than (20), partition bank_account_part_max values less than (maxvalue) ) ; alter table bank_account add (constraint bank_account_uq1 unique (account#) disable validate ) ; N.B. The unique constraint is on the partition column.
RE: disable validate on a partitioned table?
Title: RE: disable validate on a partitioned table? -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] As you know for a partitioned table: unique constraints could be enforced by a local index or global index. For local index: the unique key will be part of the partitioning key. So for a partitioned table with a unique key that is a part of the partitioning key, Loading a partition or exchanging it does not require a full table scan or reading all partitions. Uniqueness will be checked in memory during the load/exchange process for only one partition. I see. So it will require a full partition scan but not a full table scan. That makes sense. In any case my question was moot because I was unable to do an alter table ... exchange partition ... on a table with a disable validate key, even though the documentation says that's one of the times when it would be useful. When I tried doing the exchange partition I received ORA-25132 UNIQUE constraint (JRK.COUNTRY_UQ1) disabled and validated in ALTER TABLE EXCHANGE PARTITION
Re: ok so i'm bored working on rman scripts
Ruth, just some experiments, going to implement it for ERP at longaberger, so i need to do proof of concept. not stuck(yet) just experimenting. joe Ruth Gramolini wrote: Joe, What are you trying to do with rman? Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 2:17 PM so i took the first 2 stories and put them on http://www.oracle-dba.com/bdbafh nothing pretty, maybe i'll mess with it later today, text only so far. feel free to submit your part of the on-going saga to [EMAIL PROTECTED] joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: TSPITR Question
Brian - Since nobody seems to have responded to your question, yes, there are plenty of opportunities for gotchas with TSPITR. To recover deleted data, you may want to take a look at LogMiner. Less risk. Normally, to recover deleted data, you will be performing the TSPITR on a test (or recovery) database so you avoid losing the data changes that were made to your production system after the deletion. Otherwise, you are performing a full database point in time recovery. The concept behind TSPITR is to perform recovery on a small subset of your database somewhere separate from your production database, then once you've recovered the data you need, export and import it back to the production system. Without more details on your situation and your experience level, about the most help I can be is to say take a full backup first. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 4:03 PM To: Multiple recipients of list ORACLE-L List - I have the opportunity to learn first-hand about TSPITR today. I need to recover a good bit of data that was recently deleted, and do not have a recent enough export to work from. My question is this - I'm reading the documentation now, and one of the big, bold Notes is that you should *NOT* try TSPITR for the first time on a production database, or if you have a time constraint. From looking at the instructions, I believe that I understand what to do, and while I would love to test this on another instance, I may not be able to. So I ask you - do you know of any gotchas that I need to be aware of?? Thanks In Advance, Brian -- | Brian McGraw /* DBA */ Infinity Insurance | | mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] | -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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).
Re: Way of extracting record
Hi guru , I need your advise , currently our customer have a production and development system , if there is a problem log being raise , then we need to port the data from development to production but not the whole database sometime is only certain record. Is there any method to use instead of generate insert statement for necessary table(PROD) and run the statement (DEV) ? I do think of using XML but I don't know how to do it -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Perl::DBI problems after charset change
Rich, Do you have export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 in the client environment where perl is running? -Mark On Mon, 2002-09-30 at 17:48, Jesse, Rich wrote: Hey all, We've just changed our charactersets on our 8.1.7.2 (and 8.1.7.4) DBs from US7ASCII to WE8ISO8859P1 using the Oracle-approved ALTER DATABASE CHARACTER SET WE8ISO8859P1 and accompanying commands. Everything works like a champ, but our Perl::DBI connections now all cause an invisibile ORA-1017 invalid username/password error. The error never appears in the client -- the only reason I know this happens is because I'm auditing for it. Other than having our audit log tablespace fill up, this leaves me a little worried and I don't know how to track it down. Of course, since the apps all work without reporting the error we never caught it in our testing. The version of Perl is 5.005_03, the Oracle client is 8.0.5.0.0 on Solaris, and I don't know what version of DBI or DBD::Oracle. I've tested my much newer versions of Perl, Oracle client and DBI/DBD::Oracle under windows and no audit is generated. I've also connected using SQL*plus from the 8.0.5.0.0 client without audit. I'm going to try and debug this without affecting the production systems, but has anyone encountered this before? TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] It is not enough to have a good mind. The main thing is to use it well. -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: extremely long parse time
Title: RE: extremely long parse time Matt, optimizer_mode = FIRST_ROWS means CBO, and for SYS schema without statistics. Oracle 8.1.7.2 on HP-UX 11. optimizer_mode = CHOOSE I run your original SELECT, then with the hint FIRST_ROWS. From trace file: PARSING IN CURSOR #1 len=888 dep=0 uid=20 oct=3 lid=20 tim=264275830 hv=275513964 ad='a1e7360' select ... END OF STMT PARSE #1:c=37,e=271,p=7,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=264275831 ... PARSING IN CURSOR #1 len=906 dep=0 uid=20 oct=3 lid=20 tim=264293190 hv=3966396081 ad='bfbf750' select /*+ FIRST_ROWS */ ... END OF STMT PARSE #1:c=13234,e=13619,p=1,cr=58,cu=0,mis=1,r=0,dep=0,og=2,tim=264293190 Look at og Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose In my case RBO took 2.71 sec, CBO 136.19 sec. Alex. -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 10:38 AM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time We are using First_rows for the optimizer mode, but the last_analyzed column in DBA_TABLES and DBA_INDEXES is NULL for all objects owned by SYS. The really wierd part is: Changing the query to use rule based optimization (via the /*+ RULE */ hint caused it to execute sub-second. Why would optimization mode affect parsing? Is query optimization considered part of the parsing routine? Matt -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 11:36 AM To: '[EMAIL PROTECTED]' Cc: Adams, Matthew (GEA, MABG, 088130) Subject: RE: extremely long parse time H, also running on 8.1.7.2 on HPUX 11, this comes back to me sub-second. My explain plan pukes in TOAD, but that's a TOAD issue...everything looks good in SQL*Plus. 1) Are you using CBO? 2) If yes from 1, verify that there are no stats gathered in SYS. 3) Try init.ora optimizer_max_permutations = 2000. The default is 8 in 8 and 8i and 2000 in 9i. Aside from cursor_sharing=force, that's the only parameter I have that I think could affect parse times that severely. I also have: optimizer_index_caching = 90 optimizer_index_cost_adj = 50 in my init.ora, in case those might also somehow affect parse time. I wouldn't think it would in this case, since these should be CBO-only and there shouldn't be stats on the data dictionary. HTH! GL! :) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time Nope, Oracle 8.1.7.2 on HP-UX 11. 148 seconds on the wall clock to parse: select null as table_cat, owner as table_schem, table_name, 0 as NON_UNIQUE, null as index_qualifier, null as index_name, 0 as type, 0 as ordinal_position, null as column_name, null as asc_or_desc, num_rows as cardinality, blocks as pages, null as filter_condition from all_tables where table_name = 'INDEXENTRIES' union select null as table_cat, i.owner as table_schem, i.table_name, decode (i.uniqueness, 'UNIQUE', 0, 1), null as index_qualifier, i.index_name, 1 as type, c.column_position as ordinal_position, c.column_name, null as asc_or_desc, i.distinct_keys as cardinality, i.leaf_blocks as pages, null as filter_condition from all_indexes i, all_ind_columns c where i.table_name = 'INDEXENTRIES' and i.index_name = c.index_name and i.table_owner = c.table_owner and i.table_name = c.table_name and i.owner = c.index_owner order by non_unique, type, index_name, ordinal_position Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, September 27, 2002 4:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: extremely long parse time Matt, Is it Oracle 9? If yes, time is in microseconds. Alex. -Original Message- From: Adams, Matthew (GEA, MABG, 088130) [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 26, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: extremely long parse time why would a query take 148 seconds to parse? It is a two way union where the first half is going against all_tables and the second half is a join between all_indexes and all_ind_columns. The shared pool has 50M large parts of it are free. I generated a 10046 (level 12) trace, and I just don't see anything out of whack, except of the c=14868 in the PARSE #1 line.
RE: Way of extracting record
1. You can use SQL*Plus COPY command to copy data from Production into development database. 2. You can create a link from Development database to Production database, and then use insert into Development database table by selecting data from table@Production database. 3. You can use export with QUERY option to export just the rows you need from each table in Production and import those into Development database. However, it will depend on how easy it is to get to the required data by the QUERY option (available with Oracle 8i). I would go with #1 first. Check SQL*Plus Reference Guide for (1) and (2) Check Utilities Guide for (3). HTH.. - Kirti -Original Message- Sent: Monday, September 30, 2002 9:18 PM To: Multiple recipients of list ORACLE-L Hi guru , I need your advise , currently our customer have a production and development system , if there is a problem log being raise , then we need to port the data from development to production but not the whole database sometime is only certain record. Is there any method to use instead of generate insert statement for necessary table(PROD) and run the statement (DEV) ? I do think of using XML but I don't know how to do it -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: disable validate on a partitioned table?
Title: RE: disable validate on a partitioned table? This is probably b/c the unique key does not include the partitioning key. Waleed -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 8:33 PMTo: Multiple recipients of list ORACLE-LSubject: [Possible Spam - go to http://spam.fmr.com] RE: disable validate on a partitioned table? -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] As you know for a partitioned table: unique constraints could be enforced by a local index or global index. For local index: the unique key will be part of the partitioning key. So for a partitioned table with a unique key that is a part of the partitioning key, Loading a partition or exchanging it does not require a full table scan or reading all partitions. Uniqueness will be checked in memory during the load/exchange process for only one partition. I see. So it will require a "full partition" scan but not a full table scan. That makes sense. In any case my question was moot because I was unable to do an "alter table ... exchange partition ... " on a table with a disable validate key, even though the documentation says that's one of the times when it would be useful. When I tried doing the exchange partition I received ORA-25132 UNIQUE constraint (JRK.COUNTRY_UQ1) disabled and validated in ALTER TABLE EXCHANGE PARTITION
RE:
cannot perform an aggregate function on an expression containing an aggregate or subquery is the error i am getting while i am executing the query. SELECT name FROM (SELECT c.name, COUNT(p.clientid) p_count FROM client c, project p WHERE c.clientid = p.clientid GROUP BY c.name) a, (SELECT MAX(COUNT(clientid)) p_max FROM project GROUP BY clientid) b WHERE a.p_count = b.p_max clientid and name are the columns in client table and projectid and clientid are the columns in project table. santosh -Original Message- Ignaszak Sent: Monday, September 30, 2002 6:09 PM To: Multiple recipients of list ORACLE-L try it: select name from (select c.name, count(p.id) p_count from clients c, projects p where c.id = p.cl_id group by c.name) a, (select max(count(id)) p_max from projects group by cl_id) b where a.p_count = b.p_max Regards, Leszek At 03:23 2002-09-30 -0800, you wrote: Hello all, I have a query - i have 2 tables - client and project fields in project table - clientid/projectid fields in client table - clientid/name i want to get the maximum orders one client has got. i mean a project having the greatest clients how to write it in single query ?? like project 1client 1 project 2client 1 project 3client 2 in the above case, the query should return client ( 1 ). Thanks and regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leszek Ignaszak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Santosh Varma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
