Re: OPS instalation - pretty urgent
!! Please do not post Off Topic to this List !! Actually, failover (TAF - for connection, session, and select) works fine without MTS. I've built six 8i OPS systems in the last year or so using TAF without MTS. (None had direct Java clients though.) MTS is not required for the older multiple descriptions in a description list type of load balancing that has been around since Oracle7. The 8i method (load_balance=on) is much more flexible and simpler to configure though. Actually, almost all of the 8i OPS systems I've done used Tuxedo middleware. Since connections were already multiplexed through Tuxedo, we didn't need MTS for that. We let Tux do the load balancing and manage some of the failover also - so we could transparently (to the client) resubmit failed update, insert, and delete transactions. Tux query processes and other connections (reports, etc.) that performed only queries just used Net8 TAF to reduce the complexity at the Tux layer. I haven't used Oracle Connection Manager except in a few experiments some time ago, but believe that it does require MTS. And... if you don't need MTS, you don't need dispatcher cross-registration. -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 3:45 PM !! Please do not post Off Topic to this List !! Don, failover, dispatcher cross registration and server load balancing require MTS. Second, I'm not on AIX, that is why I was guessing about semaphores. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! Hi Jonathan, Thx for your input. If I look at v$filestat I see many columns. Searching the documents don't give me the explanation for them. Where do I get this info or maybe you can explain. AVGIOTM seems to be the column I need (units 10ms??), correct .? Jack Jonathan Lewis [EMAIL PROTECTED]@fatcity.com on 13-09-2001 22:20:50 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) !! Please do not post Off Topic to this List !! I have had, and heard of, poor performance from EMC boxes before now because of the big black box principal. You might check out James Morle's book (scaling Oracle 8i) for some thoughts. From an Oracle perspective, you may find that simply checking v$filestat will demonstrate quite clearly that the average read time off disk is very poor - the last big site I went to were getting read times of worse than 100 millisecs - when EMC were claiming to offer better than 20 millisecs. If this doesn't help, there is a C program on my web-site (under a Miscellanous or Performance article on choosing a block size) which allows you to create a file, and then start emulating random Oracle-read I/Os - this should give you a quick way of testing the real response time of the black box. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 13 September 2001 16:30 !! Please do not post Off Topic to this List !! Hi, The other workload I would say was about the same (Relatively speaking) Both machines did have idle time on the CPU (I/O intensive job). The quicker test machine was a bit stretched on memory at the time (running about 8 databases) but not too bad. The only thing I can think of is pi** poor performance (3 p's you don't want in marketing) of the symmetrix disks. One point this symmetrix is loaded with 36 Gb disks and all of them are sliced to pieces and than allocated to filesystems. In theory you can be sharing disks with other high I/O apps (E-mail system etc..). I do not however have any insight in what is where physically. Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = 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
Re: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! The other workload I would say was about the same (Relatively speaking) Both machines did have idle time on the CPU (I/O intensive job). What are the dominant wait events - in Oracle? It sounds like its probably I/O, but you might want to verify - if for no other reason than to have some proof to justify some EMC time and/or a Sym reorg. The quicker test machine was a bit stretched on memory at the time (running about 8 databases) but not too bad. The only thing I can think of is pi** poor performance (3 p's you don't want in marketing) of the symmetrix disks. One point this symmetrix is loaded with 36 Gb disks and all of them are sliced to pieces and than allocated to filesystems. In theory you can be sharing disks with other high I/O apps (E-mail system etc..). If this is more than just theory, the message from John Hallis most likely hit the nail on the head. I'll lay odds of 3:1 for disk/cache contention in the Sym. I do not however have any insight in what is where physically. My condolences. EMC should be able to help here though, if you don't have another way (ecc, DBtuner, etc.). They should be able to come in, look at the Sym, and tell you what is going on - in cache, against disks, etc. - and also tell you whether it should be a problem or not. There is a common thread to all these good responses you are getting - I/O tuning is still critical, in spite of any vendor propaganda to the contrary. -Don Granaman [OraSaurus - Honk if you remember UFI!] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automanagement of extent sizing
!! Please do not post Off Topic to this List !! ...or copy command of sqlplus. Regards. --- Suhen Pather [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! You cannot use the alter table move on a table with LONG columns. * ERROR at line 1: ORA-00997: illegal use of LONG datatype I would normally use export/ import Regards Suhen I must be spacing out here, I found where I got the idea that you can not use alter tablespace move with a long, The drawback to using this method is the you cannot move a table with a LONG or LONG RAW. You must exp that table and imp it into a table. http://asktom.oracle.com/pls/ask/f?p=4950:8:123086::NO::F4950_P8_DISPLAYID,F 4950_P8_CRITERIA:47812348053,%7BALTER%7D%20and%20%7BTABLE%7D%20and%20%7Bmove %7D But I can't find it in the docs and don't have access to 8i a the moment to test. I swear I saw it in the docs too. Anyone back me up on this? - E -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 4:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: Automanagement of extent sizing !! Please do not post Off Topic to this List !! Ethan - Can you use the SQL command ALTER TABLE MOVE? In a quick glance at the documentation, it seems like you could probably use it on a table with a LONG. Test it first, obviously. Dennis Williams DBA Lifetouch, Inc. [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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: OPS instalation - pretty urgent
!! Please do not post Off Topic to this List !! Hi SOrry about this.. But I have actually seen the Net8 (shipped with Oracle 8.1.7 client CD) using Oracle 8.1.6 Enterprise Edition Database Server, where the failover works WITHOUT mts. However, Gopal, can you please clarify whether IBM insists on implementing OPS ONLY WITHIN a frame? Kindly note, my question is WHETHER we can implement OPS (Real Application Clusters) across two IBM frames. Thanks a lot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cyril Thankappan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! Hi Don, wait_events that are dominant. db_file_scattered_reads, db_file_sequential_reads, db_file_parallel_write,sort_segment_request are the dominant wait (right under SQL*Net message from client rdbms ipc message) So yes I know I have an I/O problem. It's just that I'm stuck with an EMC storage solution that I can not look into. I need evidence to go to SA/management and say that disk layout is no good or something along that line. I do know for a fact that each individual disk is 36Gb and sliced in (i believe) 4,5Gb slices. You can therefore be sharing disks with other I/O intensive apps. As for monitoring tools, I'm the lowly DBA that has no business on UNIX so I need the UNIX people to do this for me. They will help as they are always cooperative, but also very busy, so I need to show them some facts and figures. TIA Jack Don Granaman [EMAIL PROTECTED]@fatcity.com on 14-09-2001 10:20:17 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) !! Please do not post Off Topic to this List !! The other workload I would say was about the same (Relatively speaking) Both machines did have idle time on the CPU (I/O intensive job). What are the dominant wait events - in Oracle? It sounds like its probably I/O, but you might want to verify - if for no other reason than to have some proof to justify some EMC time and/or a Sym reorg. The quicker test machine was a bit stretched on memory at the time (running about 8 databases) but not too bad. The only thing I can think of is pi** poor performance (3 p's you don't want in marketing) of the symmetrix disks. One point this symmetrix is loaded with 36 Gb disks and all of them are sliced to pieces and than allocated to filesystems. In theory you can be sharing disks with other high I/O apps (E-mail system etc..). If this is more than just theory, the message from John Hallis most likely hit the nail on the head. I'll lay odds of 3:1 for disk/cache contention in the Sym. I do not however have any insight in what is where physically. My condolences. EMC should be able to help here though, if you don't have another way (ecc, DBtuner, etc.). They should be able to come in, look at the Sym, and tell you what is going on - in cache, against disks, etc. - and also tell you whether it should be a problem or not. There is a common thread to all these good responses you are getting - I/O tuning is still critical, in spite of any vendor propaganda to the contrary. -Don Granaman [OraSaurus - Honk if you remember UFI!] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = 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
which patch number?
!! Please do not post Off Topic to this List !! Hi Which oracle patch number is required to upgrade from 8.1.7.0 to 8.1.7.2? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automanagement of extent sizing
!! Please do not post Off Topic to this List !! I've been using LMTs in production systems since 1999 with no problems. I've been doing uniform extents for (almost) everything (enforced through controlling the DDL) since 1990 - and wouldn't have it any other way. My strategy is simple - assign objects to tablespaces bases on three criteria. 1) How big are they (or will they get) 2) What is the nature of the object (Table? Index? Read-only? Truncate/Reload, etc.) 3) Separate objects that are likely to contend with each other into different tablespaces. Pretty basic stuff really. Other basic best practices, in my opinion, are: * A few different extent sizes are all you need - for the entire database (except SYSTEM). The most common example is to use only 128K, 4M, and 128M for anything (except perhaps rollback segments and temp - which may each have their own uniform extent size and, of course, SYSTEM). These aren't carved in stone, but are good choices. * Size for the future, not the present. You really don't want to have to move those monster objects from a tablespace of one extent size to one of the next larger extent size very often. * A few different datafile sizes are all you really need - perhaps 3, 4 or maybe 5. Extend this extent sizing policy a bit further - to datafiles. Unless you have a really compelling reason for gargantuan datafiles, don't use them! For example, 15 * 2 GB datafiles for a 30 GB tablespace is preferred over 3 * 10 GB datafiles or 1 * 30 GB datafile. The additional checkpoint overhead for additional datafiles is not usually significant and the smallest unit of backup and recovery is a datafile. (Lets not cloud the issue with discussions of incremental backups, etc.) Would you rather have to restore and recover a 2 GB datafile or a 10 GB datafile? The former I would think. I know that autoextend is popular, but consider fragmentation at the filesystem level also. Consider this: You create N * datafiles on a brand new filesystem, using virgin disks and load them up with data. A month later, all of these have autoextended 5 times each (assume - in round robin fashion). How many different contiguous file chunks are now on this filesystem? Answer: 5 * N. Each file would have 5 discontiguous extents. Is this a problem? Probably not much of one, but it depends... I prefer to just create datafiles out of a small set of sizes and add a new datafile when a tablespace needs more space. The other advantage is that moving files about is a lot simpler - you only have a few files sizes and a few hole sizes (hole size = size of space vacated by a moved datafile). If you are using raw devices, remember two things. 1) A single raw volume can contain at most one datafile - it never gets fragmented the way a filesystem can. 2) You CAN resize or autoextend a datafile on a raw volume - up to the size of the volume. The paper so frequently mentioned How to Stop Defragging and Start Living is a good one and the latest in the line of evolution, but it certainly isn't the first one or the only one. This philosophy has been around, but perhaps not well known or commonly adopted, for well over a decade. Popular published papers on this topic go back to at least the mid-1990s. One classic is Cary Millsap's Space - The Final Frontier. For many years, at every major conference there were at least a couple of presentations that were some variation on this same theme. Don't be concerned too much about uniform extents. LMTs are relatively new, but the practice is very well established and thoroughly proven. I will add one last thing though. There is a potential gotcha. If you have an object that is routinely cleaned out and reloaded (say via drop|truncate and recreate|reload) in batch, you may not want 1000+ extents. When in doubt about where to put something, based on its size and probable number of extents, it is usually better to round up. -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 3:15 PM !! Please do not post Off Topic to this List !! How many people are using the new 8i and 9i feature for automatic space management? I'm specifically interested in the management of extent sizing. For those of you that are using it, what has your experience been with it? What have you learned? What are the pros and cons? What sort of strategy have you used to implement it? We are considering using this feature for a new project. Is this a good idea? What would be the right way to implement this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To
RE: explain plan is changing ...
!! Please do not post Off Topic to this List !! Does estimate without samples size or percentage not just use 1024 as the sample size?. If you look at dba_tab_columns the samples size will be in there. If the table is not too big could you try it with compute statistics or estimate statistics with 20 percent sample? Iain Nicoll -Original Message- Sent: 13 September 2001 21:26 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! I have analyzed them today via: analyze table ... estimate statistics; analyze table ... estimate statistics for all indexed columns; No data were added/modified agter that --- Nicoll, Iain (Calanais) [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! Have they been analyzed recently? as if you were using histograms then if the last two months were added after your last analyze it would think they were fairly rare. Cheers Iain Nicoll -Original Message- Sent: 13 September 2001 19:11 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi all: I have a query, which behaves differently depending on the input data (month/year). I have more than 15month worth of data in the database. The query is completed under 1 minute for the first 13 month, but for the last two months it just doesn't finish. I have cancelled it after 36 minutes. The explain plans are differ in that the quick query uses more hash jonts, while slow one utilizes more nested loops. All the tables are analyzed. This must have something to do with the data distribution, but what? Can anyone shed some light onto that? tia __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: g g INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Nicoll, Iain (Calanais) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: USE_NL with or without ORDERED
!! Please do not post Off Topic to this List !!
I think this is the best description I have seen
so far of the way in which the optimiser has
evolved.
Currently I believe that the optimizer HAS to
obey hints if the hints can be applied to the
paths it has been constrained to check. If this
were not so, the new 'plan stability' feature
of 8.1 could not work as it relies on storing
hints in the database and applying them at
run time.
However, in the latest versions of Oracle
you need to ensure that Oracle is not
allowed to check any paths that do not
match your exact requirements - and
use_nl all by itself is not strict enough.
Take a look at how many hints Oracle
pushes into user_outline_hints the next
time you try to produce a really simple
plan.
For your entertainment - here's an example
even more surprising than the one you produced:
select
/*+ use_nl(t2) */
t1.cols, t2.cols
from
table1 t1, table2 t2
where
t1.id = t2.id
and t1.restriciton = {literal};
desired plan:
nested loop
full scan of t1
indexed access into t2
Actual path:
hash
full scan of table 1
full scan of table 2
How did Oracle manage to come up
with a plan that visited the tables in the
right order, and STILL ignore the use_nl
hint for getting into the second table.
Left as an exercise to the interested reader -
but I will post the answer in a couple of days
if anyone wants it.
Hi Jonthan,
thanks for the explanation. I'm interesting in seeing your answer.
It seems Oracle should rename USE_NL into USE_NL_or_HASH :-)
Regards,
Ed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shevtsov, Eduard
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: which patch number?
!! Please do not post Off Topic to this List !! Hi Patchset : 1882450 Jack Seema Singh [EMAIL PROTECTED]@fatcity.com on 14-09-2001 11:15:17 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) !! Please do not post Off Topic to this List !! Hi Which oracle patch number is required to upgrade from 8.1.7.0 to 8.1.7.2? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CBO - default num_rows in a table
Title: CBO - default num_rows in a table My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: 13 September 01 22:22 To: Multiple recipients of list ORACLE-L Subject: RE: Automanagement of extent sizing !! Please do not post Off Topic to this List !! Cherie - We have been using the autoextend feature for 6 months now and have been really pleased with it. I am now studying the Oracle White Papers on the locally managed and uniform extent philosophy and beginning to follow that scheme. I would recommend studying it carefully. We have had a couple of runaways that ate up a lot of disk, that is the most obvious downside. The upside is obvious in the title of the Oracle white paper Stop Defragging and Start Living. Here are my procedures so far: 1. Use Oracle's new uniform extent recommendations to eliminate free extent fragmentation. Since all extents are the same size, no fragmentation can occur. 2. Use locally-managed tablespaces per Oracle's recommendation. 3. Set all extents in a tablespace to the same size. There are no unusable small free extents, free space is usable by any segment, and administration is minimized. 4. Use only 3 extent sizes: 128K, 4M, and 128M 5. All segments should have less than 1,024 extents. When a table approaches 1,024 extents, it should be moved to the next larger extent size tablespace. 6. Monitor archive log space. 7. Temporary and rollback tablespaces should be divided into 1,024 extents for optimal performance. 8. Export the table before moving it. 9. Use the Oracle alter table XXX move command. 10. Use the Oracle alter index XXX rebuild command. Let me know if you have any more questions, and please share your ideas. Dennis Williams DBA Lifetouch, Inc. [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 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. **
Re: OPS instalation - pretty urgent
!! Please do not post Off Topic to this List !! Hi, on AIX platforms, Oracle actually uses it's post wait driver architecture. It's a substitute for the operating system's semaphores. The background is that due to an exhaustive overhead dealing with OS semaphores (context switch), Oracle handles the waits within the (Oracle) kernel space instead of handing over the control to the OS's kernel space. See Steve Adam's Oracle Internals book. | Regards, | | Stefan Jahnke | | BOV AG | | @:D2 Vodafone, Abt.: FBOM | | Tel.: 0211/533-4893| K Gopalakrishnan schrieb: !! Please do not post Off Topic to this List !! Hi, You are right. IBM uses a light weight process (post wait driver??) instead of typical System V semaphores. It is dynamically allocated and you don't need to explicitly set them in sys config files (Right John??) Best Regards, K Gopalakrishnan Bangalore, INDIA -Original Message- Mladen Sent: Thursday, September 13, 2001 1:45 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Don, failover, dispatcher cross registration and server load balancing require MTS. Second, I'm not on AIX, that is why I was guessing about semaphores. Here are my versions: SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 13 15:36:53 2001 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter password: Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - 64bit Production With the Partitioning and Parallel Server options JServer Release 8.1.7.1.0 - 64bit Production SQL HP-UX pdev1-a B.11.00 U 9000/800 I admit that my advice might be a little bit off when it comes to IBM. HP uses semaphores and we do have tons of those. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). - This Mail has been checked for Viruses Attention: Encrypted mails can NOT be checked! ** Diese Mail wurde auf Viren geprueft Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! - -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CBO - default no of rows
Title: CBO - default no of rows My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John Oracle DBA BTcellnet * [EMAIL PROTECTED] ( 0113 388 6062 Desk ) 07713 066194 BT Mobile ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. **
Re: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! Hi Don, I think I can get your don't believe everything you hear list to 10no make that 14. This is not specific to any storage vendor : 8) I/O access patterns on datafiles and redo logfiles are the same, hence can co-exist without any I/O issues. 9) A logical device with 16 drives will perform exactly like 4 logical devices with 4 drives each. So always create one huge logical volume with all of your drives. 10) Even if you use Parallel Query and Database Partitioning, you can still put everything on the same large logical device. Don't worry about localized I/O isolation it is not relevant. 11) Don't worry about availability issues with the one huge logical volume, even though you will affect every database component with the failure of 1 disk drive. That's because everything is mirrored. 12) We have benchmarked this new I/O methodology on a system with 1440 drives. We did another benchmark with 2400 drives and it worked really well. 13) I/O diagnostics can be done only at the file-level, as object-level I/O diagnostics is extremely difficult if not impossible. Thus, create one huge logical volume and put all of your database components on the same logical devices to eliminate hotspots. 14) We are XXX Corporation, the gods of disks, and we have invented an 7th fibonacci series inverse convoluted extremely complex heat and pressure sensitive algorithm, that will measure the angle of the sun rays coming through the window in your datacenter and take into consideration the time date of the day, determine the gravitational pull of the moon, to manage the cache in our storage array which will eliminate all I/O bottlenecks and cure cancer automatically 7x24xforever. Don't we love our jobs Gaja --- Don Granaman [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! WOW! Is the other workload on these similar when this job runs? Are you sure the problem is the Symmetrix and not something in the OS or instance configuration? Does this job spend a lot of time waiting (in Oracle) on physical I/O - or on something else? (I guess if you don't have access to the machine, you can't find out though. The ultimate tuning challenge!) If the problem is actually Symmetrix I/O, I could only hazard a guess that it might be due to RAID-5 for something inappropriate (hot redo log files?) or extreme I/O contention in the layout. As far as pointers, pitfalls, and suggestions... I really have only one: don't believe everything you hear! For example: (top 10 list) 1) With EMC, RAID-5 won't matter. (it likely still will - for write-intensive stuff) 2) With EMC, you don't want to stripe. (you might - it can still make a big difference) 3) With the cache, I/O won't ever be a bottleneck. (until cache becomes saturated or ...) 4) [Corollary to #3] Throw out all that basic I/O tuning stuff you learned (but back it up to tape first!) 5) The best layout is always SAME - stripe and mirror everything across everything. 6) The check is in the mail. 7) This won't hurt a bit. [ORA-00051] Drat! I crashed before getting to ten! Sorry, I was up all night repairing a bridge... For more serious and less evasive answers, see Gaja's paper at http://www.quest.com/whitepapers/Raid1.pdf -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:55 AM !! Please do not post Off Topic to this List !! Hi All, Does anybody here on the list have experience with EMC/symmetrix storage units.? We have our databases on this machine and I have a feeling the the I/O performance is not very good. I can not proof it since I do not have any experience/data/access to that machine. We do however have a very cooperative UNIX group but they also lack experience with performance on this machine. Who can give me pointers about I/O throughput that can be reached, configuration pittfalls etc.. Example: RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about 2 hours to complete. F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours to complete. Jack === = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
recovery of rman
Hi, Please can someone advise me on how to recreate the recovery catalog from the control file. I have found a command: resync catalog from backup controlfile; I am connecting to rman using nocatalog and then issuing the above, but am getting a syntax errorI have looked on metalink but have had no joy... Any help/pointers would be greatly appreciated Rgds Fawzia This message is confidential and is intended for the addressee only; unless clearly stated that this disclaimer should not apply, this e-mail is not intended to create legally binding commitments on behalf of any company in the British Interactive Broadcasting Holding Limited group, nor do its contents reflect the corporate views or policies of any such company. Any unauthorised disclosure, use or dissemination, either whole or partial, is prohibited. If you are not the intended recipient of the message, please notify the sender immediately.
RE: CBO - default no of rows
!! Please do not post Off Topic to this List !! Ooooh, correct me if I'm wrong on this one, but doesn't the CBO just use the stats that are on the table. Hence if you actually delete the stats for the tables that don't have upto date stat values, you can actually get a performance increase, my tuppence worth. K. hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com http://www.calanais.com/ -Original Message- Sent: 14 September 2001 11:25 To: Multiple recipients of list ORACLE-L My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John Oracle DBA BTcellnet * [EMAIL PROTECTED] * 0113 388 6062Desk * 07713 066194 BT Mobile ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Quest bought EZSQL
!! Please do not post Off Topic to this List !! Don't laugh, that is what a lot of Windows programming firms aimed for: Start a small software firm. Give your employees stock options. Make a very useful utility. Become popular. Your stock value climbs. Microsoft buys out your company at your stock value's peak. Retire. Then the .com frenzy started, but that wasn't as successful. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Acting Head Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Greg Moore [SMTP:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 10:15 PM To: Multiple recipients of list ORACLE-L Subject:Re: Quest bought EZSQL !! Please do not post Off Topic to this List !! apparently EZSQL was bought to suppress competition for TOAD and SQL Navigator. There may be a cottage industry here. Code up a front end, enhance it in your spare time over a year or two so it has most of the useful features. Then start giving it away. Pretty soon Quest will buy you out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automanagement of extent sizing
!! Please do not post Off Topic to this List !! Don, Just to confirm a point that I think that you are making: Each tablespace should only have one extent size in it? Once you get too many extents, you move the object up to the next-size tablespace? Thanks, Cherie Don Granaman granaman@home To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: Automanagement of extent sizing [EMAIL PROTECTED] om 09/14/01 04:30 AM Please respond to ORACLE-L !! Please do not post Off Topic to this List !! I've been using LMTs in production systems since 1999 with no problems. I've been doing uniform extents for (almost) everything (enforced through controlling the DDL) since 1990 - and wouldn't have it any other way. My strategy is simple - assign objects to tablespaces bases on three criteria. 1) How big are they (or will they get) 2) What is the nature of the object (Table? Index? Read-only? Truncate/Reload, etc.) 3) Separate objects that are likely to contend with each other into different tablespaces. Pretty basic stuff really. Other basic best practices, in my opinion, are: * A few different extent sizes are all you need - for the entire database (except SYSTEM). The most common example is to use only 128K, 4M, and 128M for anything (except perhaps rollback segments and temp - which may each have their own uniform extent size and, of course, SYSTEM). These aren't carved in stone, but are good choices. * Size for the future, not the present. You really don't want to have to move those monster objects from a tablespace of one extent size to one of the next larger extent size very often. * A few different datafile sizes are all you really need - perhaps 3, 4 or maybe 5. Extend this extent sizing policy a bit further - to datafiles. Unless you have a really compelling reason for gargantuan datafiles, don't use them! For example, 15 * 2 GB datafiles for a 30 GB tablespace is preferred over 3 * 10 GB datafiles or 1 * 30 GB datafile. The additional checkpoint overhead for additional datafiles is not usually significant and the smallest unit of backup and recovery is a datafile. (Lets not cloud the issue with discussions of incremental backups, etc.) Would you rather have to restore and recover a 2 GB datafile or a 10 GB datafile? The former I would think. I know that autoextend is popular, but consider fragmentation at the filesystem level also. Consider this: You create N * datafiles on a brand new filesystem, using virgin disks and load them up with data. A month later, all of these have autoextended 5 times each (assume - in round robin fashion). How many different contiguous file chunks are now on this filesystem? Answer: 5 * N. Each file would have 5 discontiguous extents. Is this a problem? Probably not much of one, but it depends... I prefer to just create datafiles out of a small set of sizes and add a new datafile when a tablespace needs more space. The other advantage is that moving files about is a lot simpler - you only have a few files sizes and a few hole sizes (hole size = size of space vacated by a moved datafile). If you are using raw devices, remember two things. 1) A single raw volume can contain at most one datafile - it never gets fragmented the way a filesystem can. 2) You CAN resize or autoextend a datafile on a raw volume - up to the size of the volume. The paper so frequently mentioned How to Stop Defragging and Start
Re: Automanagement of extent sizing
!! Please do not post Off Topic to this List !! Don, Thanks for your comprehensive reply. Could you elaborate on how you enforce uniform extents through controlling the DDL? What do you mean by that? Thanks, Cherie Don Granaman granaman@home To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Re: Automanagement of extent sizing [EMAIL PROTECTED] om 09/14/01 04:30 AM Please respond to ORACLE-L !! Please do not post Off Topic to this List !! I've been using LMTs in production systems since 1999 with no problems. I've been doing uniform extents for (almost) everything (enforced through controlling the DDL) since 1990 - and wouldn't have it any other way. My strategy is simple - assign objects to tablespaces bases on three criteria. 1) How big are they (or will they get) 2) What is the nature of the object (Table? Index? Read-only? Truncate/Reload, etc.) 3) Separate objects that are likely to contend with each other into different tablespaces. Pretty basic stuff really. Other basic best practices, in my opinion, are: * A few different extent sizes are all you need - for the entire database (except SYSTEM). The most common example is to use only 128K, 4M, and 128M for anything (except perhaps rollback segments and temp - which may each have their own uniform extent size and, of course, SYSTEM). These aren't carved in stone, but are good choices. * Size for the future, not the present. You really don't want to have to move those monster objects from a tablespace of one extent size to one of the next larger extent size very often. * A few different datafile sizes are all you really need - perhaps 3, 4 or maybe 5. Extend this extent sizing policy a bit further - to datafiles. Unless you have a really compelling reason for gargantuan datafiles, don't use them! For example, 15 * 2 GB datafiles for a 30 GB tablespace is preferred over 3 * 10 GB datafiles or 1 * 30 GB datafile. The additional checkpoint overhead for additional datafiles is not usually significant and the smallest unit of backup and recovery is a datafile. (Lets not cloud the issue with discussions of incremental backups, etc.) Would you rather have to restore and recover a 2 GB datafile or a 10 GB datafile? The former I would think. I know that autoextend is popular, but consider fragmentation at the filesystem level also. Consider this: You create N * datafiles on a brand new filesystem, using virgin disks and load them up with data. A month later, all of these have autoextended 5 times each (assume - in round robin fashion). How many different contiguous file chunks are now on this filesystem? Answer: 5 * N. Each file would have 5 discontiguous extents. Is this a problem? Probably not much of one, but it depends... I prefer to just create datafiles out of a small set of sizes and add a new datafile when a tablespace needs more space. The other advantage is that moving files about is a lot simpler - you only have a few files sizes and a few hole sizes (hole size = size of space vacated by a moved datafile). If you are using raw devices, remember two things. 1) A single raw volume can contain at most one datafile - it never gets fragmented the way a filesystem can. 2) You CAN resize or autoextend a datafile on a raw volume - up to the size of the volume. The paper so frequently mentioned How to Stop Defragging and Start Living is a good one and the latest in the line of
OT: be happy is Friday... PC GAMES time
!! Please do not post Off Topic to this List !! Hi, Anyone play red alert 2 ? do you have cheat codes ? Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: recovery of rman
!! Please do not post Off Topic to this List !! If you have had a catalog in the past and it is backed up somewhere you can get restore in from backup. If you don't have a backup I would just create a new recovery catalog. Then backup all of you databases using rman in order to have current backups in the new recovery catalog. If you have never used a recovery catalog you must create one. Log on as rman and create the catalog. HTH, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 6:55 AM Hi, Please can someone advise me on how to recreate the recovery catalog from the control file. I have found a command: resync catalog from backup controlfile; I am connecting to rman using nocatalog and then issuing the above, but am getting a syntax errorI have looked on metalink but have had no joy... Any help/pointers would be greatly appreciated Rgds Fawzia -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Lots and lots of redo logs
!! Please do not post Off Topic to this List !! I am planning setting up a new database with the redo logs on RAID 1 array (mirror). The amount of space available on the array is 16Gb and only the redo logs will be on there. The application will generate 2Gb of redo per day and will be backed up (cold) each night to tape. If I set up enough groups (MAXLOGFILES) such that the whole array is full of logs (each probably 50Mb in size) can I safely run this in NOARCHIVELOG mode and still expect ARCHIVELOG mode type complete recovery? The application will not overwrite a log till several days (and several backups) after it was last used, and the logs are protected by RAID. Recovery requirement is only to be able to get back to the current state (say that last 24 hours max.) before failure, not recover way back in time. Are there any other issues (eg. performance) that I should consider? Any comments much appreciated. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CBO - default no of rows
!! Please do not post Off Topic to this List !! If you delete the stats it will use rule based, unless a table being join does have stats, then the other objects will be estimated (which I believe is simply based on the number of blocks). Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 7:11 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Ooooh, correct me if I'm wrong on this one, but doesn't the CBO just use the stats that are on the table. Hence if you actually delete the stats for the tables that don't have upto date stat values, you can actually get a performance increase, my tuppence worth. K. hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com http://www.calanais.com/ -Original Message- Sent: 14 September 2001 11:25 To: Multiple recipients of list ORACLE-L My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John Oracle DBA BTcellnet * [EMAIL PROTECTED] * 0113 388 6062Desk * 07713 066194 BT Mobile ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
Title: RE: I/O Performance/bottlenecks on EMC Symmetrix Rules of tuning databases. There is always a bottleneck. Once you solve the bottle neck, refer to rule number 1. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- From: Hallas John [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 10:01 AM To: Multiple recipients of list ORACLE-L Subject: RE: I/O Performance/bottlenecks on EMC Symmetrix At one site I worked using Oracle Financials we were having serious performance problems at what seemed to us random intervals. Spent months looking at the database after the Unix boys had said that there was no way we could have I/O problems with the throughput capabililities of EMC and the Symetrix set up we had. Eventually turned out that 3 systems were sharing the same disks and the disks had not been striped. Therefore other system were causing us performance problems. If you have an EMC support contract which I think you must have you, the SA's get all the free GUI tools that allow them to look at channels and logical/physical layout. Ask them about. John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 13 September 01 12:55 To: Multiple recipients of list ORACLE-L Subject: I/O Performance/bottlenecks on EMC Symmetrix !! Please do not post Off Topic to this List !! Hi All, Does anybody here on the list have experience with EMC/symmetrix storage units.? We have our databases on this machine and I have a feeling the the I/O performance is not very good. I can not proof it since I do not have any experience/data/access to that machine. We do however have a very cooperative UNIX group but they also lack experience with performance on this machine. Who can give me pointers about I/O throughput that can be reached, configuration pittfalls etc.. Example: RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about 2 hours to complete. F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours to complete. Jack = 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: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
Re: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack Bill Buchan [EMAIL PROTECTED]@fatcity.com on 14-09-2001 15:20:18 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) !! Please do not post Off Topic to this List !! I am planning setting up a new database with the redo logs on RAID 1 array (mirror). The amount of space available on the array is 16Gb and only the redo logs will be on there. The application will generate 2Gb of redo per day and will be backed up (cold) each night to tape. If I set up enough groups (MAXLOGFILES) such that the whole array is full of logs (each probably 50Mb in size) can I safely run this in NOARCHIVELOG mode and still expect ARCHIVELOG mode type complete recovery? The application will not overwrite a log till several days (and several backups) after it was last used, and the logs are protected by RAID. Recovery requirement is only to be able to get back to the current state (say that last 24 hours max.) before failure, not recover way back in time. Are there any other issues (eg. performance) that I should consider? Any comments much appreciated. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! You can only have a max of 8 log groups if I remember correctly. Yesterday's backup may have failed, and you may have to recover from the day prior or prior's prior. Keep this in mind as well. With archive log, you can use 6 month old backup, and apply all the logs to become current if for whatever reason all the backups failed and that is all you had. Another thing to keep in mind is if you plan on using log miner, you may want to look through a log file in the past, via archive log. Also keep in mind, DDL and data dictionary costs redo, so your redo usage is almost always more than expected. For example if you have to run initjvml.sql, that generates over 100Mb of redo activity. This under normal cases will spin two of your 50Mb logs. But given these and perhaps other concerns, if your still comfortable, by all means. But in my opinion here is my priorities. 1. Recoverability 2. Performance Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 9:20 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! I am planning setting up a new database with the redo logs on RAID 1 array (mirror). The amount of space available on the array is 16Gb and only the redo logs will be on there. The application will generate 2Gb of redo per day and will be backed up (cold) each night to tape. If I set up enough groups (MAXLOGFILES) such that the whole array is full of logs (each probably 50Mb in size) can I safely run this in NOARCHIVELOG mode and still expect ARCHIVELOG mode type complete recovery? The application will not overwrite a log till several days (and several backups) after it was last used, and the logs are protected by RAID. Recovery requirement is only to be able to get back to the current state (say that last 24 hours max.) before failure, not recover way back in time. Are there any other issues (eg. performance) that I should consider? Any comments much appreciated. Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Dump Oracle Tables To ASCII/Comma Delimited File
!! Please do not post Off Topic to this List !! Instead, get Jared's dump.sql (Dump Tables to Flat File) from http://www.cybcon.com/~jkstill/util/. Review it and change it to get what you need. Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Deepender Kr Gupta [SMTP:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 8:20 AM To: Multiple recipients of list ORACLE-L Subject: Dump Oracle Tables To ASCII/Comma Delimited File Hi everybody, Can anybody tell me the command/tool in oracle that can dump Oracle Tables To ASCII/Comma Delimited File. Regds deepender gupta File: Wipro_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! If the statement is not exactly the same, the new statement will be reparsed. If you are executing it under a different user the statement will be reparsed. If you drop/create an index, it will invalidate the explain plan if that was part of the chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! No takers so far - anybody? Alex Hillman -Original Message- Sent: Thursday, September 06, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Let's assume that SQL statement was parsed by user X. If this or another user reexecute this same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Locally managed tablespace
!! Please do not post Off Topic to this List !! Making Oracle simpler to work with I don't fear would put dba's out of work. A dba's role is far more involved than just the daily janitor work. Planning is also a large part of our job as well. But let's think about this, look at Windows, a monkey can learn how to use it in 30 seconds to a minute, yet we still have many many system administrators. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 7:35 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris - As I understand it, locally managed tablespaces with uniform extents, possibly autoextensible, is the future direction for Oracle. This will allow Oracle to be more easily managed. Probably put us DBAs out of work, but hey something always seems to come up. The documentation states that locally managed tablespaces can create extents faster than dictionary managed, but I have never reached a point where it seemed to make a difference. But I am using the locally managed alternative more because of the nudge from Oracle's direction. Those are my thoughts anyway. Dennis Williams DBA Lifetouch, Inc. [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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Having too many datafiles is not good for performance, updating datafile headers during checkpointing. I disagree, unless you have an unreasonable amount, there is minor almost not noticeable difference. Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:55 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Kishore, Having too many datafiles is not good for performance, updating datafile headers during checkpointing. If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ... If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace. If you are not on 8i then you can use export/ import. Export all tables in the tablespace, Drop the tables, Drop the tablespace, Recreate new tablespace (same name) with single datafile, Import all tables Regards Suhen !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Locally managed tablespace
!! Please do not post Off Topic to this List !! I use LMT's a lot. Advantages 1. Avoids honeycomb fragmentation 2. Simple administration 3. Avoid the need for rebuilding due to fragmented extents 4. Faster when dealing with local extents 5. No need to coalesce (hense eliminate problems with SMON) Disadvantages 6. No rollback generated during space management 7. Reduced data dictionary contention Disadvantage 1. Not very well understood 2. Deciding common sizes for objects in the same tablespace may be a little more difficult. 3. When accessing xxx_EXTENTS all data files bitmaps are hit and may cause large performance problems when dealing with all extents across the database 4. All extent information is spread across many data files, so simple global extent operations may involve visiting many blocks. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 6:15 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Anyone here tried to use those locally managed tablespace? Some DBA here persuade me to use the locally managed TS for the rollback segment,tables, indexes, temp tablespace Can you tell me what are the benefits of using the locally managed tablespace, any disadvantages? Thanks in advance. Chris Harvest. Creative Consulting. __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Oracle8i Statspack
!! Please do not post Off Topic to this List !! Not at all, it is just a great replacement of utlbstat/estat. I find it simply an outstanding resource. The ability to take snap shots every hour, then at any point in time do a report between any two points in time. They are much more detailed than bstat/estat as well. And you can take them proactively as normal procedure. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 6:15 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi, Gurus: Did you try the Statspack? Will this new feature increase the performance a lot? how about the dbms_stats pachage? Thanks in advance. Chris Harvest. Creative Consulting. __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Generally more smaller data files is better than fewer larger ones. There is no performance problem with having 10 200Mb data files rather than 1 2gb data file. In fact, using 10 200mb data files will help a little when it comes to file locking. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:27 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Chris, Can you pl point me to the doc/web site etc. where this is reported. I am having to deal with such an issue with one of my databases that has less than 500 files, but the number is growing rapidly.. Thanks. - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Christopher Spence [SMTP:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 8:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tablespace (datafile reducing) !! Please do not post Off Topic to this List !! Having too many datafiles is not good for performance, updating datafile headers during checkpointing. I disagree, unless you have an unreasonable amount, there is minor almost not noticeable difference. Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:55 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Kishore, Having too many datafiles is not good for performance, updating datafile headers during checkpointing. If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ... If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace. If you are not on 8i then you can use export/ import. Export all tables in the tablespace, Drop the tables, Drop the tablespace, Recreate new tablespace (same name) with single datafile, Import all tables Regards Suhen !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
database disaster recovery
!! Please do not post Off Topic to this List !! I'm curious. With all that's going on in New York, were any Oracle databases lost? Has anyone had to activate a disaster recovery plan and bring up their database at a new location? If so, how did that go? It would be interesting, and possibly instructive, to hear some real-life stories about what went wrong, what went well, etc. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Chris, Are these tests results published someplace? Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 14, 2001 9:50 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Having too many datafiles is not good for performance, updating datafile headers during checkpointing. I disagree, unless you have an unreasonable amount, there is minor almost not noticeable difference. Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:55 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Kishore, Having too many datafiles is not good for performance, updating datafile headers during checkpointing. If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ... If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace. If you are not on 8i then you can use export/ import. Export all tables in the tablespace, Drop the tables, Drop the tablespace, Recreate new tablespace (same name) with single datafile, Import all tables Regards Suhen !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be
RE: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! There is no performance problem with having 10 200Mb data files rather than 1 2gb data file. In fact, using 10 200mb data files will help a little when it comes to file locking. and recovery.. -Original Message- Spence Sent: Friday, September 14, 2001 14:50 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Generally more smaller data files is better than fewer larger ones. There is no performance problem with having 10 200Mb data files rather than 1 2gb data file. In fact, using 10 200mb data files will help a little when it comes to file locking. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:27 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! It is very common for people to see all that free space and want to fill it. Avoid the desire, fill it with a blank file. Disks are cheap and if you store things on the outer platters performance will suffer. Redo logs and other things in oracle waste disk space as drives get bigger, it is unavoidable. But using the space for something totally defeats the purpose of good configurations. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:35 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Someone posted a test they did from this list, some reason I believe it was John, but I can't remember 100%. If the person who did this test on the list please come forward, I remember this discussion, but I may even do it on one of my test boxes at home to simulate it again. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 9:57 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Chris, Can you pl point me to the doc/web site etc. where this is reported. I am having to deal with such an issue with one of my databases that has less than 500 files, but the number is growing rapidly.. Thanks. - Kirti Deshpande Verizon Information Services http://www.superpages.com -Original Message- From: Christopher Spence [SMTP:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 8:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: Tablespace (datafile reducing) !! Please do not post Off Topic to this List !! Having too many datafiles is not good for performance, updating datafile headers during checkpointing. I disagree, unless you have an unreasonable amount, there is minor almost not noticeable difference. Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:55 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Kishore, Having too many datafiles is not good for performance, updating datafile headers during checkpointing. If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ... If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace. If you are not on 8i then you can use export/ import. Export all tables in the tablespace, Drop the tables, Drop the tablespace, Recreate new tablespace (same name) with single datafile, Import all tables Regards Suhen !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Spatial included w/9i ????
!! Please do not post Off Topic to this List !! Can anyone tell me if they know whether Oracle Spatial is included with 9i EE? The on-line documentation for Options is linked to Oracle8 (why I don't know) and is vague. Thanks! -w __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! Chris list, The last time I checked, an ANALYZE also invalidates the SQL in the shared pool, to force a parse and rebuild of the execution plan, on the next execution of the SQL statement. Regards, Gaja --- Christopher Spence [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! If the statement is not exactly the same, the new statement will be reparsed. If you are executing it under a different user the statement will be reparsed. If you drop/create an index, it will invalidate the explain plan if that was part of the chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! No takers so far - anybody? Alex Hillman -Original Message- Sent: Thursday, September 06, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Let's assume that SQL statement was parsed by user X. If this or another user reexecute this same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the
RE: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! Datafile sizing is also dependent on the size of your db. I use 10gb datafile size's for a 1tb database. [EMAIL PROTECTED] 09/14/01 09:50AM !! Please do not post Off Topic to this List !! Generally more smaller data files is better than fewer larger ones. There is no performance problem with having 10 200Mb data files rather than 1 2gb data file. In fact, using 10 200mb data files will help a little when it comes to file locking. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:27 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! I know a few people (actually on the LazyDBA list) have done tests and each time they have said the checkpoint time has been insignificantly altered. I cannot say I have done these tests, and go by the theory of using smaller data files rather than larger ones for portability and locking reasons. I never see any problems with checking pointing due to size. I hope some of the people who have played with this are around and have the results. I do not have anything official to offer on this as I am merely going off what other people have said that have tried it, and the experiences I have had (although not with that many data files). If no one steps up, I may put together some test environment to put concrete fact either way. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:50 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris, Are these tests results published someplace? Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 14, 2001 9:50 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Having too many datafiles is not good for performance, updating datafile headers during checkpointing. I disagree, unless you have an unreasonable amount, there is minor almost not noticeable difference. Tests have been done on many databases with 3500+ data files and shown the difference to be insignificant. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:55 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Kishore, Having too many datafiles is not good for performance, updating datafile headers during checkpointing. If you are on 8i you can create a 2GB tablespace, and use the alter table move command to move all the tables in that tablespace to the new 2GB tablespace. All associated indexes will become unusable, so make sure you alter index rebuild ... If the tablespace has indexes rather than tables then you could do a alter index rebuild ... to move them to the new tablespace. If you are not on 8i then you can use export/ import. Export all tables in the tablespace, Drop the tables, Drop the tablespace, Recreate new tablespace (same name) with single datafile, Import all tables Regards Suhen !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Re: ora-14098: what am I missing (long) - solved
!! Please do not post Off Topic to this List !! Please ignore the previous post. It turned out that one of the indices on the partitioned table was not partitioned locally. That's what I was missing --- Gene Gurevich [EMAIL PROTECTED] wrote: Hi. I'm getting ora-14098 error when executing the following command: alter table rptg_cnt exchange partition p200107 with table xchg_rptg_cnt including indexes. Usually when I see this error it means that either an index is missing on one of the tables or there is some discrepancy in terms of field order. This time around I can't see any difference. When I execute SQL select table_name, index_name, column_name 2 from user_ind_columns 3 where table_name like '%RPTG_CNT' 4 order by 1,2,column_position asc; I see the same three indices with the same fields in each in the same order. I did a desc on both tables and all the fields have the same attributes. What am I missing here? Any ideas? thanks = __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html = __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora-14098: what am I missing (long) - solved
!! Please do not post Off Topic to this List !! Please ignore the previous post. It turned out that one of the indices on the partitioned table was not partitioned locally. That's what I was missing --- Gene Gurevich [EMAIL PROTECTED] wrote: Hi. I'm getting ora-14098 error when executing the following command: alter table rptg_cnt exchange partition p200107 with table xchg_rptg_cnt including indexes. Usually when I see this error it means that either an index is missing on one of the tables or there is some discrepancy in terms of field order. This time around I can't see any difference. When I execute SQL select table_name, index_name, column_name 2 from user_ind_columns 3 where table_name like '%RPTG_CNT' 4 order by 1,2,column_position asc; I see the same three indices with the same fields in each in the same order. I did a desc on both tables and all the fields have the same attributes. What am I missing here? Any ideas? thanks = __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to [EMAIL PROTECTED] To subscribe: send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these terms:http://www.lazydba.com/legal.html = __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: database disaster recovery
!! Please do not post Off Topic to this List !! I agree - but it would possibly be good etiquette to wait some weeks before we asked such questions (and this is not to criticise in any way your post) Just my 2c worth. Cheers Connor --- Jonathan Gennick [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! I'm curious. With all that's going on in New York, were any Oracle databases lost? Has anyone had to activate a disaster recovery plan and bring up their database at a new location? If so, how did that go? It would be interesting, and possibly instructive, to hear some real-life stories about what went wrong, what went well, etc. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Decimal Datatype
!! Please do not post Off Topic to this List !! What are the rules for using the Decimal datatype? I can't find anything in my references. TIA, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: lots and lots of redo logs
!! Please do not post Off Topic to this List !! Christopher R. Spence said: You can only have a max of 8 log groups if I remember correctly. Gee, I thought that the DBA controlled the maximum number of redo logs allowed for a database. There's this thing called a database create statement: (e.g.) CREATE DATABASE DEV LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXLOGHISTORY 1 DATAFILE 'F:\Oracle\oradata\EMS\system01.dbf' SIZE 320M REUSE AUTOEXTEND ON NEXT 32768K MAXDATAFILES 128 MAXINSTANCES 1 CHARACTER SET WE8ISO8859P1 NATIONAL CHARACTER SET WE8ISO8859P1; Funny, how reading the documentation or just looking at your create scripts can answer lots of questions before just simply spewing email. Personally, I try to send fewer, higher quality emails rather than dozens of things that are the first thing that pops into my head. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: recovery of rman
!! Please do not post Off Topic to this List !! I missed a step... you have to register the database using the command register database; in rman once you are connected... Anjan Malik, Fawzia wrote: Hi, Please can someone advise me on how to recreate the recovery catalog from the control file. I have found a command: resync catalog from backup controlfile; I am connecting to rman using nocatalog and then issuing the above, but am getting a syntax errorI have looked on metalink but have had no joy... Any help/pointers would be greatly appreciated Rgds Fawzia Name: Open_Interactive_Disclaimer.txt Open_Interactive_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7bit -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjan Thakuria INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CBO changed path - why??
!! Please do not post Off Topic to this List !! We have had 2 querys go wacko on us. Both are cursors in a large (5000 line) pl/sql package. This interface package runs daily. The cursor execution below ran in less than 30 seconds on Tues; ran 2 hrs 15 minutes Wed. (yikes!) We had the same problem with a similar cursor 2 weeks ago. I've fixed the query by adding more selectivity to the where clause. Here's the real mystery. I pulled the 3 tables from this join from the production box (E4500 Solaris 2.6, Oracle 8.0.5) to our smaller test box. Small test box is running identical stuff (solaris 2.6, Oracle 8.0.5). The query still runs in under 30 seconds on small test box. I dumped all the init parameters (SELECT NAME, VALUE FROM V$PARAMETER) from both databases, then did a diff in the output files. No significant differences that I can see. I'm wondering why the query still runs ok on the test box, but went wacko on the real system. These 3 tables are small (invrows 95,062 rows 21 megs; sub_ad 5,993 rows, 20 megs; sub_pub 45,553 rows 30 megs). All 3 tables have identical indexes on both boxes; all 3 have been analyzed on both boxes. All 3 tables have index on column adno. optimizer is choose on both instances. Sorry this is so long. I'd appreciate any insights. Thx!!! Barb select i.adno, more stuff frominvrows i, sub_ad a, sub_pub p WHERE A.RUNNO=860 and I.ROWTYPE=4 and I.ADNO=A.ADNOand I.VNO=A.VNO and i.adno=p.adnoand i.pubno=p.pubno and a.vno=p.vno and A.VNO=1 and a.startdate a.rdate and a.enddate = to_date(a.cus4name,'mm/dd/') and to_char(a.rdate,'mm/dd/') = to_char(p.mdate,'mm/dd/') ___ autotrace from production (BAD!!) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313) 10 NESTED LOOPS (Cost=1 Card=1 Bytes=313) 21 NESTED LOOPS (Cost=56 Card=7 Bytes=1267) 32 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=10 Card=1 Bytes=133) 42 TABLE ACCESS (FULL) OF 'SUB_PUB' (Cost=46 Card=5820 Bytes=279360) 51 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6791 Bytes=896412) 65 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1 Card=6791) ___ autotrace from test box (Good!) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313) 10 NESTED LOOPS (Cost=1 Card=1 Bytes=313) 21 NESTED LOOPS (Cost=1 Card=1 Bytes=265) 32 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=11 Card=1 Bytes=133) 42 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6906 Bytes=911592) 54 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1 Card=6906) 61 TABLE ACCESS (BY INDEX ROWID) OF 'SUB_PUB' (Cost=1 Card=6911 Bytes=331728) 76 INDEX (UNIQUE SCAN) OF 'I_SUBPUB1' (UNIQUE) ___ tkprof from production (BAD!!) (The tkprof shows 129,696,658 rows returned for sub_pub when the entire table is only 45,000 rows.) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch7 8139.098153.17907 56669565 8361 102 --- -- -- -- -- -- -- total9 8139.118153.19907 56669565 8361 102 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 618 (AMAX) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 102 NESTED LOOPS 54193272NESTED LOOPS 5993 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_AD' 129696658 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_PUB' 27213TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'INVROWS' 155138410 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: be happy is Friday... PC GAMES time
!! Please do not post Off Topic to this List !! Umm... see that message at the top of each post? Please read and heed. Jared ( stressed out list owner ) On Friday 14 September 2001 06:00, Sinardy wrote: !! Please do not post Off Topic to this List !! Hi, Anyone play red alert 2 ? do you have cheat codes ? Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CBO - default num_rows in a table
!! Please do not post Off Topic to this List !! Can't recall at the moment, but if you take a look at the tuning manual, it will tell you what the defaults are. Jared On Friday 14 September 2001 03:20, Hallas John wrote: My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John -Original Message- Sent: 13 September 01 22:22 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Cherie - We have been using the autoextend feature for 6 months now and have been really pleased with it. I am now studying the Oracle White Papers on the locally managed and uniform extent philosophy and beginning to follow that scheme. I would recommend studying it carefully. We have had a couple of runaways that ate up a lot of disk, that is the most obvious downside. The upside is obvious in the title of the Oracle white paper Stop Defragging and Start Living. Here are my procedures so far: 1. Use Oracle's new uniform extent recommendations to eliminate free extent fragmentation. Since all extents are the same size, no fragmentation can occur. 2. Use locally-managed tablespaces per Oracle's recommendation. 3. Set all extents in a tablespace to the same size. There are no unusable small free extents, free space is usable by any segment, and administration is minimized. 4. Use only 3 extent sizes: 128K, 4M, and 128M 5. All segments should have less than 1,024 extents. When a table approaches 1,024 extents, it should be moved to the next larger extent size tablespace. 6. Monitor archive log space. 7. Temporary and rollback tablespaces should be divided into 1,024 extents for optimal performance. 8. Export the table before moving it. 9. Use the Oracle alter table XXX move command. 10. Use the Oracle alter index XXX rebuild command. Let me know if you have any more questions, and please share your ideas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! Chris: If you are executing it under a different user the statement will be reparsed. I'm puzzled. I always thought that different users submitting the identical SQL statement would use the same plan and not need to be reparsed. I understood that to be part of the reasoning behind bind variables and the big advantage of cursor sharing. Please explain. Jon Walthour -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walthour, Jon (GEAE, Compaq) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: database disaster recovery
!! Please do not post Off Topic to this List !! Well, all our servers were fine as of the time our building was evacuated on Tuesday but I got paged on Wednesday that our 100 Wall St. office had lost power and was running on generator. Fortunately the only machines I had there were our standby server (so we're running without a standby at the moment), our development server (I ftp'ed the last export file over to a NJ server and will probably be importing the development schemas to our QA box as an interim measure) and a clone of our production server that we were using for upgrade testing. Somehow I don't think our planned upgrade will happen this weekend... Some of my colleagues had production servers in NY and, with some minor snafus, have brought up the standby servers in NJ and switched everyone over. I am having fond recollections of arguing about 3 years ago that we had to have our standby server in a different datacenter than our production server. When I first joined this group they were both in NY. Jay Miller x48355 -Original Message- Sent: Friday, September 14, 2001 10:50 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! I'm curious. With all that's going on in New York, were any Oracle databases lost? Has anyone had to activate a disaster recovery plan and bring up their database at a new location? If so, how did that go? It would be interesting, and possibly instructive, to hear some real-life stories about what went wrong, what went well, etc. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Generally IDE shows this problem much more than scsi, but on IDE you can see as much as 50% performance degrading, I haven't really tested the difference. If you put the logs on, then fill the rests of the disks with a empty file of that size, you can make sure that the end of the disk is filled with junk and the logs will never pull blocks from that area. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:15 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! How big a performance issue is the location of the log file on disk? Even if I create the minimum of 2 archive log groups, how can I be sure the controller hasn't put these on the outer platters anyway? Since these are log files then the writes will be sequential anyway (does that make a difference?) - showing my hardware ignorance here! As you said: But in my opinion here is my priorities. 1. Recoverability 2. Performance I would have thought that having lots of archive log groups (normally) not overwritten for a few days in addition to properly archived logs would boost my recoverability with negligible impact on performance. (Incidentally the MAXLOGFILES maximum value is 255, on Linux anyway). Thanks - Bill. At 07:10 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! It is very common for people to see all that free space and want to fill it. Avoid the desire, fill it with a blank file. Disks are cheap and if you store things on the outer platters performance will suffer. Redo logs and other things in oracle waste disk space as drives get bigger, it is unavoidable. But using the space for something totally defeats the purpose of good configurations. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:35 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 catalog Recreation Problems/Questions ....
!! Please do not post Off Topic to this List !! Dear List Members, I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog database (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I have no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0 patch level. The box on which catalog DB exists has another production DB which was upgraded to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and then patch needs to be applied. Since patch was already applied, my only option could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL IMPORT. My questions would be: [1] Do I need to do the FULL IMPORT of the catalog database? and then issue the upgrade catalog command twice to upgrade the catalog to work with the latest RMAN version? [OR] [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the regular procedure to create RMAN schema and register the DB and then take the fresh cold backup to start with, followed by the regular daily incremental backups? [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is the 8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0? Could some one please answer my above questions and suggest an appropriate procedure to upgrade the catalog DB to 8.1.7.1.0. Thanks, -- Janardhana Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Christopher R. Spence said: Disks are cheap and if you store things on the outer platters performance will suffer? Chris, I'm not sure what you mean here. The concept of Variable Transfer Rate covers this. Stealing a quote from: Optimal Storage Configuration Made Easy, By Juan Loaiza, Oracle Corporation The transfer rate for a disk drive is not the same for all portions of a disk. The outer sectors of a disk drive move by the disk head faster than the inner sections leading to a faster transfer rate for the outer sectors. This is simply because of the circular shape of a disk drive. Scott -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Crabtree INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! It seems that the I/O wait statistics, along with the increased time it takes for the job to run, is pretty good circumstantial evidence (enough for the grand jury hearing). That should be sufficient motivation to start the ball rolling to collect more detailed information about the physical layout and what is going on inside the Sym. They (management, SA, whomever) can't really expect a definitive analysis (the whole trial argument) and a proposed solution in detail (sentencing recommendation???) since you don't have the level of access necessary to get that information. If you can get very specific wait information and identify the biggest bottlenecks - which datafiles, redo logs, etc. are the worse offenders - it might help build a stronger case. I hesitate a bit on this recommendation because overly specific information of that nature at this time might lead to only a partial solution - a fix to only the most severe immediate problems - rather than to do a more comprehensive review of the physical layout in the Symmetrix. Also, a comparison of total time waited on these I/O events and job run time between the test system and the EMC system should help. You might be able to see a direct correlation between the I/O waits and the run time. The company paid a premium for EMC storage and should be getting more out of it, not less. My experience has been that EMC is actually pretty good about helping out with gathering statistics, etc. - if you can get them in. (Your mileage may vary.) If it is any help... (and anecdotal evidence rarely is) less than a year ago, I took an EMC approved, big black box layout, performed a thorough I/O analysis on the database, and rebuilt the disks in the Sym according to more conventional I/O practices - striping, dedicating redo log disks, distributing contending objects between disks/stripe_sets, etc. The after configuration throughput was eight times greater than the before layout - on identical hardware. And this was with all the disks in question (not the entire Sym though) already being dedicated entirely to a single database. This wasn't an isolated case, just the most dramatic of several. I'm sure that others, especially Gaja, have such stories also. Again, I would seriously suggest reading his white paper at http://www.quest.com/whitepapers/Raid1.pdf . It has a wealth of information on this very topic and, I believe, it has some specific examples of problem layouts,solutions, and gains. (At least the presentation did.) Incidentally, it isn't absolutely necessary to dedicate entire disks to a single database. It is usually preferred - in my opinion, but if you don't dedicate disks, you should treat the I/O tuning exercise as if everything using any particular set of disks is a single database (even if some of it isn't database!). For example, you have DB01 and DB02 sharing a set of disks. Distribute the I/O between disks as if DB01 + DB02 are a single database. Completely independent I/O tuning for DB01 and DB02 probably won't cut it. Often, you have to win the motivational/political battle before you can even really begin the technical battle. It sounds like that is probably where you are now. So, I'll refrain from pollutimg the list with more long generic discussion on this topic. Good luck! -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 4:00 AM !! Please do not post Off Topic to this List !! Hi Don, wait_events that are dominant. db_file_scattered_reads, db_file_sequential_reads, db_file_parallel_write,sort_segment_request are the dominant wait (right under SQL*Net message from client rdbms ipc message) So yes I know I have an I/O problem. It's just that I'm stuck with an EMC storage solution that I can not look into. I need evidence to go to SA/management and say that disk layout is no good or something along that line. I do know for a fact that each individual disk is 36Gb and sliced in (i believe) 4,5Gb slices. You can therefore be sharing disks with other I/O intensive apps. As for monitoring tools, I'm the lowly DBA that has no business on UNIX so I need the UNIX people to do this for me. They will help as they are always cooperative, but also very busy, so I need to show them some facts and figures. TIA Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
Is Virus protection software safe on an NT / Oracle server?
!! Please do not post Off Topic to this List !! Our NT Server admins have been told by the Security department that they need to install virus protection software on the NT / Oracle servers. Does anyone know of any problems between Oracle and any virus software? Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Average response time
!! Please do not post Off Topic to this List !! Hi List, I am trying to calculate the average database response time for a data center audit currently underway. Without expensive monitoring tools, is it possible to determine this from database statistics. So far, I'm using (Service Time + Wait Time) / calls where this translates into Service Time= 'CPU used by this session' from v$sysstat Wait Time = sum(time_waited) from v$system_event (excluding idle events) User calls = 'user calls' from v$sysstat Am I way off the mark here? Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is preparing a paper which addresses this very issue - determing response time from database statistics - but it is only due out later this year. Anybody with any ideas or reasons why the above is not feasible? TIA Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: recovery of rman
!! Please do not post Off Topic to this List !! Couple of things here.. Do u have a backup u can restore from.. If yes restore... If never had a catalog.. Create the schema in the database u want to create the catalog and follow these steps... (pl check the syntax of the commands) 1. connect to the target database and the catalog database. (rman target user/password@string catalog user/pass@string) 2. create catalog; (This should create the catalog for the database.) 3. Might need to resync the catalog (Though I think it is done implicitly). HTH Anjan Malik, Fawzia wrote: Hi, Please can someone advise me on how to recreate the recovery catalog from the control file. I have found a command: resync catalog from backup controlfile; I am connecting to rman using nocatalog and then issuing the above, but am getting a syntax errorI have looked on metalink but have had no joy... Any help/pointers would be greatly appreciated Rgds Fawzia Name: Open_Interactive_Disclaimer.txt Open_Interactive_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7bit -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjan Thakuria INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! How big a performance issue is the location of the log file on disk? Even if I create the minimum of 2 archive log groups, how can I be sure the controller hasn't put these on the outer platters anyway? Since these are log files then the writes will be sequential anyway (does that make a difference?) - showing my hardware ignorance here! As you said: But in my opinion here is my priorities. 1. Recoverability 2. Performance I would have thought that having lots of archive log groups (normally) not overwritten for a few days in addition to properly archived logs would boost my recoverability with negligible impact on performance. (Incidentally the MAXLOGFILES maximum value is 255, on Linux anyway). Thanks - Bill. At 07:10 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! It is very common for people to see all that free space and want to fill it. Avoid the desire, fill it with a blank file. Disks are cheap and if you store things on the outer platters performance will suffer. Redo logs and other things in oracle waste disk space as drives get bigger, it is unavoidable. But using the space for something totally defeats the purpose of good configurations. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 10:35 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi Thanks. That's a very good point. I agree that ARCHIVELOG mode will be needed. However, I still have this big disk just for redo logs, so I'm tempted to fill it anyway. This will be a sort of supplementary backup in case the archive disk (+ database disks) crash before the backup. Of course it is only sort of because, as you say, a runaway process will cycle the logs if it generates lots of redo. However, this seems better use of the disk space than just having a few log groups and leaving the rest of the array empty and unused. Unless there are any other implications? Thanks - Bill. Hi Yes you can untill the day that you have this runaway process that creates 20Gb of redo and than crashes your database 5 minutes before the daily offline backup should kick in. But you are only mirroring, why not put your database in archivelogmode. You do not have so much redo per day that your disks/archiver can't handle it. Now if your archive directory is full oracle won't crash, but just stop untill you free up some space (I believe this is the behaviour anyway). Jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: lots and lots of redo logs
!! Please do not post Off Topic to this List !! I forgot the word default; I know 8 used to be the default. I tend to be doing many different things when I respond to emails. I guess it would be much easier if I didn't post at all. Not like I ever ask questions on the list. But thanks for your flame, specially the one you sent privately stating you wish me to get kicked off the list the other day. Keep up the good work. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:21 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Christopher R. Spence said: You can only have a max of 8 log groups if I remember correctly. Gee, I thought that the DBA controlled the maximum number of redo logs allowed for a database. There's this thing called a database create statement: (e.g.) CREATE DATABASE DEV LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K MAXLOGFILES 16 MAXLOGMEMBERS 2 MAXLOGHISTORY 1 DATAFILE 'F:\Oracle\oradata\EMS\system01.dbf' SIZE 320M REUSE AUTOEXTEND ON NEXT 32768K MAXDATAFILES 128 MAXINSTANCES 1 CHARACTER SET WE8ISO8859P1 NATIONAL CHARACTER SET WE8ISO8859P1; Funny, how reading the documentation or just looking at your create scripts can answer lots of questions before just simply spewing email. Personally, I try to send fewer, higher quality emails rather than dozens of things that are the first thing that pops into my head. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! Gaja, I REALLY like #14 :) Rachel From: Gaja Krishna Vaidyanatha [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: I/O Performance/bottlenecks on EMC Symmetrix Date: Fri, 14 Sep 2001 02:25:24 -0800 !! Please do not post Off Topic to this List !! Hi Don, I think I can get your don't believe everything you hear list to 10no make that 14. This is not specific to any storage vendor : 8) I/O access patterns on datafiles and redo logfiles are the same, hence can co-exist without any I/O issues. 9) A logical device with 16 drives will perform exactly like 4 logical devices with 4 drives each. So always create one huge logical volume with all of your drives. 10) Even if you use Parallel Query and Database Partitioning, you can still put everything on the same large logical device. Don't worry about localized I/O isolation it is not relevant. 11) Don't worry about availability issues with the one huge logical volume, even though you will affect every database component with the failure of 1 disk drive. That's because everything is mirrored. 12) We have benchmarked this new I/O methodology on a system with 1440 drives. We did another benchmark with 2400 drives and it worked really well. 13) I/O diagnostics can be done only at the file-level, as object-level I/O diagnostics is extremely difficult if not impossible. Thus, create one huge logical volume and put all of your database components on the same logical devices to eliminate hotspots. 14) We are XXX Corporation, the gods of disks, and we have invented an 7th fibonacci series inverse convoluted extremely complex heat and pressure sensitive algorithm, that will measure the angle of the sun rays coming through the window in your datacenter and take into consideration the time date of the day, determine the gravitational pull of the moon, to manage the cache in our storage array which will eliminate all I/O bottlenecks and cure cancer automatically 7x24xforever. Don't we love our jobs Gaja --- Don Granaman [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! WOW! Is the other workload on these similar when this job runs? Are you sure the problem is the Symmetrix and not something in the OS or instance configuration? Does this job spend a lot of time waiting (in Oracle) on physical I/O - or on something else? (I guess if you don't have access to the machine, you can't find out though. The ultimate tuning challenge!) If the problem is actually Symmetrix I/O, I could only hazard a guess that it might be due to RAID-5 for something inappropriate (hot redo log files?) or extreme I/O contention in the layout. As far as pointers, pitfalls, and suggestions... I really have only one: don't believe everything you hear! For example: (top 10 list) 1) With EMC, RAID-5 won't matter. (it likely still will - for write-intensive stuff) 2) With EMC, you don't want to stripe. (you might - it can still make a big difference) 3) With the cache, I/O won't ever be a bottleneck. (until cache becomes saturated or ...) 4) [Corollary to #3] Throw out all that basic I/O tuning stuff you learned (but back it up to tape first!) 5) The best layout is always SAME - stripe and mirror everything across everything. 6) The check is in the mail. 7) This won't hurt a bit. [ORA-00051] Drat! I crashed before getting to ten! Sorry, I was up all night repairing a bridge... For more serious and less evasive answers, see Gaja's paper at http://www.quest.com/whitepapers/Raid1.pdf -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:55 AM !! Please do not post Off Topic to this List !! Hi All, Does anybody here on the list have experience with EMC/symmetrix storage units.? We have our databases on this machine and I have a feeling the the I/O performance is not very good. I can not proof it since I do not have any experience/data/access to that machine. We do however have a very cooperative UNIX group but they also lack experience with performance on this machine. Who can give me pointers about I/O throughput that can be reached, configuration pittfalls etc.. Example: RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about 2 hours to complete. F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours to complete. Jack === = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml
RE: find out the rollback
!! Please do not post Off Topic to this List !! What is 2lakh rows? Never heard of that number metric. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 12:45 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi lists can anybody post me how much rollback (approx in bytes/kb/mb) will be generated if I delete my table that has 2 lakh rows. the avg row len is 50. often The query is getting failed due to the error unable to extend the rollback segment. If I have this query, at least I will use a rollback segment that has enough extents. thnx in advance. Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Is Virus protection software safe on an NT / Oracle server?
!! Please do not post Off Topic to this List !! Ehh ... users??? :-))) On Fri 14. September 2001 18:40, you wrote: !! Please do not post Off Topic to this List !! Our NT Server admins have been told by the Security department that they need to install virus protection software on the NT / Oracle servers. Does anyone know of any problems between Oracle and any virus software? Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: I/O Performance/bottlenecks on EMC Symmetrix
!! Please do not post Off Topic to this List !! corollary to rule 2 after a certain point, just stop. It ain't worth it anymore. From: Christopher Spence [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: I/O Performance/bottlenecks on EMC Symmetrix Date: Fri, 14 Sep 2001 05:45:20 -0800 Rules of tuning databases. 1. There is always a bottleneck. 2. Once you solve the bottle neck, refer to rule number 1. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 10:01 AM To: Multiple recipients of list ORACLE-L At one site I worked using Oracle Financials we were having serious performance problems at what seemed to us random intervals. Spent months looking at the database after the Unix boys had said that there was no way we could have I/O problems with the throughput capabililities of EMC and the Symetrix set up we had. Eventually turned out that 3 systems were sharing the same disks and the disks had not been striped. Therefore other system were causing us performance problems. If you have an EMC support contract which I think you must have you, the SA's get all the free GUI tools that allow them to look at channels and logical/physical layout. Ask them about. John -Original Message- Sent: 13 September 01 12:55 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi All, Does anybody here on the list have experience with EMC/symmetrix storage units.? We have our databases on this machine and I have a feeling the the I/O performance is not very good. I can not proof it since I do not have any experience/data/access to that machine. We do however have a very cooperative UNIX group but they also lack experience with performance on this machine. Who can give me pointers about I/O throughput that can be reached, configuration pittfalls etc.. Example: RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about 2 hours to complete. F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours to complete. Jack = 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 http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! You mean it invalidates SQL which has references to the newly analyzed objects, not all SQL in cache - right? Also are you sure that creating index on table will invalidate SQL which references this table or view based on this table? Alex Hillman -Original Message- Sent: Friday, September 14, 2001 11:40 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris list, The last time I checked, an ANALYZE also invalidates the SQL in the shared pool, to force a parse and rebuild of the execution plan, on the next execution of the SQL statement. Regards, Gaja --- Christopher Spence [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! If the statement is not exactly the same, the new statement will be reparsed. If you are executing it under a different user the statement will be reparsed. If you drop/create an index, it will invalidate the explain plan if that was part of the chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! No takers so far - anybody? Alex Hillman -Original Message- Sent: Thursday, September 06, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Let's assume that SQL statement was parsed by user X. If this or another user reexecute this same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
Re: Tablespace (datafile reducing)
Chris, "Generally more smaller data files is better than fewer larger ones." Can you pl let me know if there is any paper or something throwing light on this. Thanks Anjan Gene Sais wrote: !! Please do not post Off Topic to this List !! Datafile sizing is also dependent on the size of your db. I use 10gb datafile size's for a 1tb database. >>> [EMAIL PROTECTED] 09/14/01 09:50AM >>> !! Please do not post Off Topic to this List !! Generally more smaller data files is better than fewer larger ones. There is no performance problem with having 10 200Mb data files rather than 1 2gb data file. In fact, using 10 200mb data files will help a little when it comes to file locking. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:27 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hello DBAs I have a tablespace , who has 10 datafiles of 200MB each, it was here before I even joined. I was thinking for the contention and want to combine them all to one single large datafile of 2gig. How do I get rid off all those datafiles and ghet them in one large datafiles?? Any help in this regard Thank you very much Kishore __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kishore INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Average response time
!! Please do not post Off Topic to this List !! Hi List, I am trying to calculate the average database response time for a data center audit currently underway. Without expensive monitoring tools, is it possible to determine this from database statistics. So far, I'm using (Service Time + Wait Time) / calls where this translates into Service Time= 'CPU used by this session' from v$sysstat Wait Time = sum(time_waited) from v$system_event (excluding idle events) User calls = 'user calls' from v$sysstat Am I way off the mark here? Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is preparing a paper which addresses this very issue - determing response time from database statistics - but it is only due out later this year. Anybody with any ideas or reasons why the above is not feasible? TIA Paul Paul, Firstly a number of statistics are meaningless in V$SYSSTAT and only make sense in V$SESSTAT (and vice-versa) and I believe that 'CPU used by this session' belongs to this category. Usually most resource consumption is traceable to a very tiny fraction of SQL statements, and I doubt that an average will lead you anywhere. My point is that I think that you should try to apply your ideas to relatively small slices of time (polling every minute or so) hoping to catch the real problem queries on the fly, using global statistics to get an idea about what you have missed, and try to do the best out of it. Another idea would be to concentrate on V$SQLAREA and the number of executions and of buffer reads (there is a script named peep.sql in the DBA tool kit of the Oriole site if you need one). I think that associating some average elapsed time to access, say, 1,000 buffers, should not be extremely difficult to do, based on a few suitable examples. By computing the average number of buffer accesses per execution of a query, you could then get something looking reasonably like an average execution time for the query. Of course, a query is not a transaction, and the user's vision of response times may be different and include other elements. But it may be an interesting approach to complement other metrics. -- Regards, Stephane Faroult email: [EMAIL PROTECTED] Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CBO - default num_rows in a table
Title: RE: CBO - default num_rows in a table Jared,Christopher The tuning manual states the following If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information (such as the number of data blocks allocated to these tables) to estimate other statistics for these tables However in the exmaple I gave the no of blacks had been increased dramatically and yet the CBO did not know about it, therefore indicating to me that it uses something other than the no of blocks. I am just trying to get an insight into whatever that something is. John -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: 14 September 01 16:37 To: [EMAIL PROTECTED]; Hallas John Subject: Re: CBO - default num_rows in a table Can't recall at the moment, but if you take a look at the tuning manual, it will tell you what the defaults are. Jared On Friday 14 September 2001 03:20, Hallas John wrote: My question of the day is :- What value does the CBO use as a default number of rows for a table. Background: - We all know that if any tables in a query have been analyzed then CBO is used for the query not RBO (couple of caveats I know but let's continue). So if 3 tables are used in a query and table a has 500 rows (analyzed) table b has 50 rows (never analyzed) and table c has 350 rows (never analyzed) all things being equal then CBO is used but what values does the CBO use for tables b or c to decide which execution plan is best. On a development system yesterday a query was running slow. I realised that we had put in a very large data load (3.5M rows). I analyzed the table and indexes and the query came back in sub second response time. I am trying to figure what value was being used for num_rows prior to the analyze. It cannot be based on blocks allocated/used as thet would have increased after the dataload Thanks John -Original Message- Sent: 13 September 01 22:22 To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Cherie - We have been using the autoextend feature for 6 months now and have been really pleased with it. I am now studying the Oracle White Papers on the locally managed and uniform extent philosophy and beginning to follow that scheme. I would recommend studying it carefully. We have had a couple of runaways that ate up a lot of disk, that is the most obvious downside. The upside is obvious in the title of the Oracle white paper Stop Defragging and Start Living. Here are my procedures so far: 1. Use Oracle's new uniform extent recommendations to eliminate free extent fragmentation. Since all extents are the same size, no fragmentation can occur. 2. Use locally-managed tablespaces per Oracle's recommendation. 3. Set all extents in a tablespace to the same size. There are no unusable small free extents, free space is usable by any segment, and administration is minimized. 4. Use only 3 extent sizes: 128K, 4M, and 128M 5. All segments should have less than 1,024 extents. When a table approaches 1,024 extents, it should be moved to the next larger extent size tablespace. 6. Monitor archive log space. 7. Temporary and rollback tablespaces should be divided into 1,024 extents for optimal performance. 8. Export the table before moving it. 9. Use the Oracle alter table XXX move command. 10. Use the Oracle alter index XXX rebuild command. Let me know if you have any more questions, and please share your ideas. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. **
Higher Consistent Gets...
!! Please do not post Off Topic to this List !!
Hi
I am having problem with a query. This query fetches rows from a table which
has 15 million rows.
The problem is, when I execute this query with subquery, the consistent gets
are 4700. Where us without the subquery the consistent gets are just 400. If
I execute the subquery alone, the consistent gets are just 5.
Here is the main query with subquery results in cons.gets of 4700:
select pd.holiday_id holidayID, pd.package_number l_package_number,
min(pd.tfr_price) l_tpr_price
from fr_search_query pd
where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
and TO_DATE('13/10/2001','dd/mm/')
and pd.location_code in (select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142')
and ROWNUM 301
group by pd.holiday_id , pd.package_number
order by pd.location_name, pd.location_code, pd.accom_unit_name,
pd.accom_unit_code, pd.departure_date, min(pd.adult_price)
Trace results
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
96 SORT (ORDER BY)
96SORT (GROUP BY)
96 COUNT (STOPKEY)
96 NESTED LOOPS
5137 INLIST ITERATOR
5138TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'FR_SEARCH_QUERY'
8566 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
96 VIEW
5136SORT (UNIQUE)
1 CONNECT BY
2 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF
'PK_GN_LOCATION' (UNIQUE)
1 TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1 TABLE ACCESS (FULL) OF 'GN_LOCATION'
Running just the subquery results in cons.gets of just 5.
select location_code
from gn_location
connect by prior location_code=parent_code
start with location_code='3142'
Rows Execution Plan
--- ---
0 SELECT STATEMENT GOAL: CHOOSE
1 CONNECT BY
2INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
(UNIQUE)
1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)
Both the tables, indexes are analyzed. The optimizer mode is choose.
How do I tune this or Am I missing something obivious??
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Raj Gopalan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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 catalog Recreation Problems/Questions ....
!! Please do not post Off Topic to this List !! Dear List, I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog database (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I have no problem upgrading the production DB to 8.1.7.0.0 and the to 8.1.7.1.0 patch level. The box on which catalog DB exists has another production DB which was upgraded to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and then patch needs to be applied. Since patch was already applied, my only option could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL IMPORT. My questions would be: [1] Do I need to do the FULL IMPORT of the catalog database? and then issue the upgrade catalog command twice to upgrade the catalog to work with the latest RMAN version? [OR] [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the regular procedure to create RMAN schema and register the DB and then take the fresh cold backup to start with, followed by the regular daily incremental backups? [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is the 8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0? Could someone Please answer my above questions and suggest an appropriate procedure to upgrade the catalog DB to 8.1.7.1.0. -- Janardhana Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: CBO changed path - why??
!! Please do not post Off Topic to this List !! What I would do is use DBMS_STATS to move the production stats to the staging db and see if the tests lead the same results. Are ya stats up to date? You using analyze or dbms_stats? Do you have comparing explain plans, trace files? I would highly recommend tracing it and checking which step is doing a lot of rows, and comparing that to determine where it is slowing down. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:30 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! We have had 2 querys go wacko on us. Both are cursors in a large (5000 line) pl/sql package. This interface package runs daily. The cursor execution below ran in less than 30 seconds on Tues; ran 2 hrs 15 minutes Wed. (yikes!) We had the same problem with a similar cursor 2 weeks ago. I've fixed the query by adding more selectivity to the where clause. Here's the real mystery. I pulled the 3 tables from this join from the production box (E4500 Solaris 2.6, Oracle 8.0.5) to our smaller test box. Small test box is running identical stuff (solaris 2.6, Oracle 8.0.5). The query still runs in under 30 seconds on small test box. I dumped all the init parameters (SELECT NAME, VALUE FROM V$PARAMETER) from both databases, then did a diff in the output files. No significant differences that I can see. I'm wondering why the query still runs ok on the test box, but went wacko on the real system. These 3 tables are small (invrows 95,062 rows 21 megs; sub_ad 5,993 rows, 20 megs; sub_pub 45,553 rows 30 megs). All 3 tables have identical indexes on both boxes; all 3 have been analyzed on both boxes. All 3 tables have index on column adno. optimizer is choose on both instances. Sorry this is so long. I'd appreciate any insights. Thx!!! Barb select i.adno, more stuff frominvrows i, sub_ad a, sub_pub p WHERE A.RUNNO=860 and I.ROWTYPE=4 and I.ADNO=A.ADNOand I.VNO=A.VNO and i.adno=p.adnoand i.pubno=p.pubno and a.vno=p.vno and A.VNO=1 and a.startdate a.rdate and a.enddate = to_date(a.cus4name,'mm/dd/') and to_char(a.rdate,'mm/dd/') = to_char(p.mdate,'mm/dd/') ___ autotrace from production (BAD!!) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313) 10 NESTED LOOPS (Cost=1 Card=1 Bytes=313) 21 NESTED LOOPS (Cost=56 Card=7 Bytes=1267) 32 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=10 Card=1 Bytes=133) 42 TABLE ACCESS (FULL) OF 'SUB_PUB' (Cost=46 Card=5820 Bytes=279360) 51 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6791 Bytes=896412) 65 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1 Card=6791) ___ autotrace from test box (Good!) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313) 10 NESTED LOOPS (Cost=1 Card=1 Bytes=313) 21 NESTED LOOPS (Cost=1 Card=1 Bytes=265) 32 TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=11 Card=1 Bytes=133) 42 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6906 Bytes=911592) 54 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1 Card=6906) 61 TABLE ACCESS (BY INDEX ROWID) OF 'SUB_PUB' (Cost=1 Card=6911 Bytes=331728) 76 INDEX (UNIQUE SCAN) OF 'I_SUBPUB1' (UNIQUE) ___ tkprof from production (BAD!!) (The tkprof shows 129,696,658 rows returned for sub_pub when the entire table is only 45,000 rows.) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch7 8139.098153.17907 56669565 8361 102 --- -- -- -- -- -- -- total9 8139.118153.19907 56669565 8361 102 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 618 (AMAX) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 102 NESTED LOOPS 54193272NESTED LOOPS 5993 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_AD' 129696658 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SUB_PUB' 27213TABLE
Re: Automanagement of extent sizing
!! Please do not post Off Topic to this List !!
Inline answers...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, September 14, 2001 7:45 AM
!! Please do not post Off Topic to this List !!
Don,
Just to confirm a point that I think that you are making:
Each tablespace should only have one extent size in it?
Yes, in any given tablespace there is ONLY one extent size. Initial = next for
everything and every (initial extent size) = (every other initial extent size) -
for everything in the tablespace. (Except for SYSTEM).
Once you get too many extents, you move the object up to the next-size
tablespace?
If you must. Better in my opinion is to size for growth. For example, if you
have a table that is currently 200M, but it will grow to 20G in two years,
prefer initially putting it into a tablespace appropriate for a 20 GB table.
The percentage of wasted space will be fairly high initially, but will
decrease as it grows - and you won't have to move it later.
Consider that, on average, one half of one extent will be as yet unused -
wasted. As the number of extents grows, that fixed amount of space gets to be
a smaller percentage of the total. For example, a uniform extent policy:
A table consists of N extents
Each and every extent is of size M
Average wasted space = M/2
(If anyone wants to extend this line of reasoning for parallel loads, etc. -
feel free!)
Total space in N extents = N*M
The ratio of wasted space to total space is (M/2)/(N*M) = M/[2(M*N)] = 1/(2N)
{Sanity check!: 4 extents, 0.5 extent unused - 1/8 of space is wasted.
1/(2*4) = 1/8. It checks.}
To convert to a percentage, multiply by 100.
= Average percentage of total space for table extents that is wasted space W
= 100*[1/(2N)] = 50/N
As the value of N (the number of extents) increases, the value of W (the
percentage of wasted space) decreases.
[Note: This, of course, does not consider free space within blocks, ILT space,
and all the other geeky Oracle stuff - nor should it. All that will be present
whether you have one extent or a thousand. It is a separate issue entirely.]
Thanks,
Cherie
You are quite welcome!
-Don Granaman
[OraSaurus - Honk if you remember UFI!]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Don Granaman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: database disaster recovery
!! Please do not post Off Topic to this List !! Jonathan, Don't know -- the NYOUG is trying to check on members, but we've been looking more towards making sure everyone is alive (so far, I have not heard that we lost members) and finding ways to help them than towards disaster recovery plans. Give NY a few weeks and we'll find out right now, it's still people not process we're concerned with. Rachel From: Jonathan Gennick [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: database disaster recovery Date: Fri, 14 Sep 2001 06:50:20 -0800 !! Please do not post Off Topic to this List !! I'm curious. With all that's going on in New York, were any Oracle databases lost? Has anyone had to activate a disaster recovery plan and bring up their database at a new location? If so, how did that go? It would be interesting, and possibly instructive, to hear some real-life stories about what went wrong, what went well, etc. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FW:Using Oracle tools to automate hot backups...
!! Please do not post Off Topic to this List !! Hi DBA's First, as a Belizean/American, I'd like to share my sympathy for those touched by the terrorist attack on the US and anger towards the cowards who would go to such extremes to achieve their goals. Life is real. Life is a journey, we must complete it. Peace!! We are running Oracle 8.0.5 on NT in ARCHIVELOGMODE. I recently scheduled three daily hot backups to disk. The process is as follows: 1.) Perl script moves the old backup folders and creates the three daily folders that hold the hot backups to disk 2.) Batch file runs SQL script to display the Archive Log information 3.) SQL scripts called by .bat files, execute the hot backups by tablespace 4.) Batch file runs SQL script to display the Archive Log information The process is not completely automated however. Nevertheless, these scripts are called by the Windows NT Scheduler from My Computer. Of course, they only work if the machine is turned on. It appears that if I move the system date back the scheduler gets confused and submissions fail. I want to pursue the option of setting up these tasks using the Oracle OEM / RMAN or DBMS_JOB tools. Please advise me on the most efficient and effective tool for this process. Thanks in advance, Denmark Weatherburne Belize Knowledge is power, but it is only useful if it is shared! _ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: database disaster recovery
!! Please do not post Off Topic to this List !! I second that! Market open on Monday might be somewhat revealing... (setq minor-rant-mode ON) I worked as a DBA in the financial/brokerage sector for years and was frequently appalled at what were often loudly touted as high availability and disaster recovery plans - in that sector and others. (setq minor-rant-mode OFF) -Don Granaman [Orasaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 9:50 AM !! Please do not post Off Topic to this List !! I'm curious. With all that's going on in New York, were any Oracle databases lost? Has anyone had to activate a disaster recovery plan and bring up their database at a new location? If so, how did that go? It would be interesting, and possibly instructive, to hear some real-life stories about what went wrong, what went well, etc. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! If the Oracle executing user is different, the execution plan will be different due to possibility of different objects and security. Different sessions under the same user can share using bind variables. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 12:25 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris: If you are executing it under a different user the statement will be reparsed. I'm puzzled. I always thought that different users submitting the identical SQL statement would use the same plan and not need to be reparsed. I understood that to be part of the reasoning behind bind variables and the big advantage of cursor sharing. Please explain. Jon Walthour -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walthour, Jon (GEAE, Compaq) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Average response time
!! Please do not post Off Topic to this List !! Paul, Try this: select ( s.service_seconds + w.wait_seconds) / user_calls service_time from ( select (sum(value)/100) / ( 3600 * 24 ) service_seconds from v$sysstat where upper(name) like '%CPU%' and class in (1,64) -- User and SQL ) s, ( select sum(time_waited/100) wait_seconds from v$system_event where event not like '%timer' and event not like '%from client' ) w, ( select sum(value) user_calls from v$sysstat where name like 'user%' ) u / While you may find this a useful number as a DBA, I'll bet your users won't buy it. On my SAP system it shows a 0.025 second reponse time. While that may be accurate on a per call database, I don't think many queries are returned that quickly. :) Could be that I'm not getting the right numbers, but I don't believe that determining an average response time is quite that simple. Jared Paul.Parker@bm wna.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Average response time om 09/14/01 09:35 AM Please respond to ORACLE-L !! Please do not post Off Topic to this List !! Hi List, I am trying to calculate the average database response time for a data center audit currently underway. Without expensive monitoring tools, is it possible to determine this from database statistics. So far, I'm using (Service Time + Wait Time) / calls where this translates into Service Time = 'CPU used by this session' from v$sysstat Wait Time= sum(time_waited) from v$system_event (excluding idle events) User calls = 'user calls' from v$sysstat Am I way off the mark here? Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is preparing a paper which addresses this very issue - determing response time from database statistics - but it is only due out later this year. Anybody with any ideas or reasons why the above is not feasible? TIA Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FW:Using Oracle tools to automate hot backups...
!! Please do not post Off Topic to this List !! We use rman here. I have used it using the OEM Backup Manager but I find it more convenient to run it from the OS. I schedule jobs on OEM to run the server commands and let the agent take care of it. HTH, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 1:36 PM !! Please do not post Off Topic to this List !! Hi DBA's First, as a Belizean/American, I'd like to share my sympathy for those touched by the terrorist attack on the US and anger towards the cowards who would go to such extremes to achieve their goals. Life is real. Life is a journey, we must complete it. Peace!! We are running Oracle 8.0.5 on NT in ARCHIVELOGMODE. I recently scheduled three daily hot backups to disk. The process is as follows: 1.) Perl script moves the old backup folders and creates the three daily folders that hold the hot backups to disk 2.) Batch file runs SQL script to display the Archive Log information 3.) SQL scripts called by .bat files, execute the hot backups by tablespace 4.) Batch file runs SQL script to display the Archive Log information The process is not completely automated however. Nevertheless, these scripts are called by the Windows NT Scheduler from My Computer. Of course, they only work if the machine is turned on. It appears that if I move the system date back the scheduler gets confused and submissions fail. I want to pursue the option of setting up these tasks using the Oracle OEM / RMAN or DBMS_JOB tools. Please advise me on the most efficient and effective tool for this process. Thanks in advance, Denmark Weatherburne Belize Knowledge is power, but it is only useful if it is shared! _ _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Is Virus protection software safe on an NT / Oracle server?
!! Please do not post Off Topic to this List !! We run a Virus Scanner on our NT Server that houses an Oracle database with no issues. The only problem I have every seen is where a bug was in one of the downloads to update the virus information and it messed up everything from PCs to the Exchange Server. You may not want to put servers on automatic updates. -Original Message- Sent: Friday, September 14, 2001 9:41 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Our NT Server admins have been told by the Security department that they need to install virus protection software on the NT / Oracle servers. Does anyone know of any problems between Oracle and any virus software? Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! AIX has a unique concept of this. Given data spread on a disk. The head will spend more time over the middle tracks as it seeks data that is distributed across the disk. Thus for faster access, place your tablespace on the middle tracks of a disk because the probability that the head will be over it is much greater. Learned this with Syabase on AIX. It may still hold true today. I don't know. Technology had advanced a lot from those day. (But I think the laws of physics are the same ;-)) -Original Message- Sent: Friday, September 14, 2001 10:05 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Christopher R. Spence said: Disks are cheap and if you store things on the outer platters performance will suffer? Chris, I'm not sure what you mean here. The concept of Variable Transfer Rate covers this. Stealing a quote from: Optimal Storage Configuration Made Easy, By Juan Loaiza, Oracle Corporation The transfer rate for a disk drive is not the same for all portions of a disk. The outer sectors of a disk drive move by the disk head faster than the inner sections leading to a faster transfer rate for the outer sectors. This is simply because of the circular shape of a disk drive. Scott -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Crabtree INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: John Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When optimizer reevaluate SQL statement
!! Please do not post Off Topic to this List !! Hi Alex, Yes, I mean invalidate the SQL that is referencing the object(s) that was analyzed. The creation of the index has the same effect. Which means after the index is created, the next execution of the query to that table, will be re-parsed, execution plan re-built and if it makes sense, the index will be used in the plan. Cheers, Gaja --- Hillman, Alex [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! You mean it invalidates SQL which has references to the newly analyzed objects, not all SQL in cache - right? Also are you sure that creating index on table will invalidate SQL which references this table or view based on this table? Alex Hillman -Original Message- Sent: Friday, September 14, 2001 11:40 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Chris list, The last time I checked, an ANALYZE also invalidates the SQL in the shared pool, to force a parse and rebuild of the execution plan, on the next execution of the SQL statement. Regards, Gaja --- Christopher Spence [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! If the statement is not exactly the same, the new statement will be reparsed. If you are executing it under a different user the statement will be reparsed. If you drop/create an index, it will invalidate the explain plan if that was part of the chosen path. I believe statistics also invalidates the plans as well, but not 100% sure on that. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Thursday, September 13, 2001 8:11 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! No takers so far - anybody? Alex Hillman -Original Message- Sent: Thursday, September 06, 2001 4:30 PM To: Multiple recipients of list ORACLE-L Let's assume that SQL statement was parsed by user X. If this or another user reexecute this same statement what are the conditions that this SQL statement will be reparsed? Let's assume that privileges are not changed and tables and/or views are not dropped and views are not changed. And optimizer parameters are not changed. First come to mind is dropping index. What about reanalizing one of the object - theoretically should also reparse. Anything else? Also is there possibility to force reparsing of SQL statement if let say index was added - short of flashing shared pool? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: Is Virus protection software safe on an NT / Oracle server?
!! Please do not post Off Topic to this List !! It will slow down your server. With McAfee, you can exclude the directories that contain oracle datafiles, that minimizes the impact. Virus checking software is a good idea, in my opinion. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Acting Head Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 1:41 PM To: Multiple recipients of list ORACLE-L Subject:Is Virus protection software safe on an NT / Oracle server? !! Please do not post Off Topic to this List !! Our NT Server admins have been told by the Security department that they need to install virus protection software on the NT / Oracle servers. Does anyone know of any problems between Oracle and any virus software? Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: find out the rollback
!! Please do not post Off Topic to this List !! 200, (two hundred thousand:) English is indeed an great language as it changes in its nuances as geographic locations change ..its friday finally! srinivas for estimating rollback, consider taking snapshots of v$rollstat.waits (specified in bytes) before and after a sample delete on your table and extrapolate to identify the amount of rollback you would require for successfully completing your operation hth Deepak --- Christopher Spence [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! What is 2lakh rows? Never heard of that number metric. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 12:45 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi lists can anybody post me how much rollback (approx in bytes/kb/mb) will be generated if I delete my table that has 2 lakh rows. the avg row len is 50. often The query is getting failed due to the error unable to extend the rollback segment. If I have this query, at least I will use a rollback segment that has enough extents. thnx in advance. Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Locally managed tablespace
!! Please do not post Off Topic to this List !! How are you to create the rollback segments? Is this the same or different from the other LMT tablespaces. I see where the Temporary Tablespaces are different. Any other good LMT articles besides the one below? I want to change our database into LMT and can you believe this was a brand new 8.1.6 database created in April and they used LONG datatypes and other old architecture. Kathy -Original Message- Sent: Thursday, September 13, 2001 4:05 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! !! tsiL siht ot cipoT ffO tsop ton od esaelP !! You can read this article to get some info: http://www.oracle.com/oramag/oracle/00-nov/index.html?o60o8i.html Ed -Original Message- Sent: Thursday, September 13, 2001 6:15 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Anyone here tried to use those locally managed tablespace? Some DBA here persuade me to use the locally managed TS for the rollback segment,tables, indexes, temp tablespace Can you tell me what are the benefits of using the locally managed tablespace, any disadvantages? Thanks in advance. Chris Harvest. Creative Consulting. __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CC Harvest INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * * * * * Freedom of Information Act Notice * * * * * The information in this email is subject to the record protection mandated by 5 United States Code 552(b)(4) and relevant judicial opinions. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sherman, Edward INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 on OS390/MVS
!! Please do not post Off Topic to this List !! We're looking at possibly running Oracle on our mainframe, but the perception exists that a more optimum solution would be DB2. I would appreciate hearing from anyone that is running Oracle on OS390, and/or made a decision between DB2 and Oracle. Thanks - Kim Thompson City and County of San Francisco -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Automanagement of extent sizing
!! Please do not post Off Topic to this List !! Prior to the advent of 8i, uniform extents had to be enforced through making sure that every extent in a given tablespace was the same size by using appropriate values in the storage clause in effect at the time of object creation. The easiest method was to just set initial = next and pctincrease = 0 in the tablespace default storage clause and prohibit the use of these particular parameters in individual object creation scripts. The DBA often had to review all object creation scripts and remove/fix any offending parameters. (When using Designer, I could run custom scripts against the API to do the cleanup though.) LMTs and the ability to declaratively enforce uniform extents eliminated this grunge work. Now we just have to decide which tablespace is appropriate. -Don Granaman [OraSaurus - Honk if you remember UFI!] PS: When others heard about this, they often replied But SMON won't coalesce free space if pctincrease=0 at the tablespace level!. My universal reply: Good! I don't want it to! - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 7:50 AM !! Please do not post Off Topic to this List !! Don, Thanks for your comprehensive reply. Could you elaborate on how you enforce uniform extents through controlling the DDL? What do you mean by that? Thanks, Cherie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: RMAN catalog Recreation Problems/Questions ....
!! Please do not post Off Topic to this List !! I think this is what you want to know: you should upgrade your rman database to the highest version of the databases being backed up with rman. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 12:45 PM !! Please do not post Off Topic to this List !! Dear List Members, I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog database (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I have no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0 patch level. The box on which catalog DB exists has another production DB which was upgraded to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and then patch needs to be applied. Since patch was already applied, my only option could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL IMPORT. My questions would be: [1] Do I need to do the FULL IMPORT of the catalog database? and then issue the upgrade catalog command twice to upgrade the catalog to work with the latest RMAN version? [OR] [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the regular procedure to create RMAN schema and register the DB and then take the fresh cold backup to start with, followed by the regular daily incremental backups? [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is the 8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0? Could some one please answer my above questions and suggest an appropriate procedure to upgrade the catalog DB to 8.1.7.1.0. Thanks, -- Janardhana Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Lots and lots of redo logs
!! Please do not post Off Topic to this List !! Excellent. Thanks for the information; maybe I won't fill my whole disk with redo now! - Bill. At 08:40 14/09/01 -0800, you wrote: !! Please do not post Off Topic to this List !! Generally IDE shows this problem much more than scsi, but on IDE you can see as much as 50% performance degrading, I haven't really tested the difference. If you put the logs on, then fill the rests of the disks with a empty file of that size, you can make sure that the end of the disk is filled with junk and the logs will never pull blocks from that area. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Tablespace (datafile reducing)
!! Please do not post Off Topic to this List !! And I have built TB+ sized databases using no datafiles larger than 2 GB. At this level, larger files are worthy of consideration, but they are certainly not required - or even critical. It depends... -Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 10:25 AM !! Please do not post Off Topic to this List !! Datafile sizing is also dependent on the size of your db. I use 10gb datafile size's for a 1tb database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Is Virus protection software safe on an NT / Oracle server?
!! Please do not post Off Topic to this List !! With the advent of worms like Code Red, I would recommend it. You don't need users on the server anymore to get a virus. All you need is an OS full of holes and the system open to the internet. Rodd Holman Original Message On 9/14/01, 12:50:32 PM, Boivin, Patrice J [EMAIL PROTECTED] wrote regarding RE: Is Virus protection software safe on an NT / Oracle server?: !! Please do not post Off Topic to this List !! It will slow down your server. With McAfee, you can exclude the directories that contain oracle datafiles, that minimizes the impact. Virus checking software is a good idea, in my opinion. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Acting Head Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Smith, Ron L. [SMTP:[EMAIL PROTECTED]] Sent: Friday, September 14, 2001 1:41 PM To: Multiple recipients of list ORACLE-L Subject:Is Virus protection software safe on an NT / Oracle server? !! Please do not post Off Topic to this List !! Our NT Server admins have been told by the Security department that they need to install virus protection software on the NT / Oracle servers. Does anyone know of any problems between Oracle and any virus software? Ron Smith Database Administrator [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: RMAN catalog Recreation Problems/Questions ....
!! Please do not post Off Topic to this List !! Ruth, Iam facing the follwing problems/doubts how to upgrade the catalog now. Please try to help me if you can: I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog database (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I have no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0 patch level. The box on which catalog DB exists has another production DB which was upgraded to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and then patch needs to be applied. Since patch was already applied, my only option could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL IMPORT. My questions would be: [1] Do I need to do the FULL IMPORT of the catalog database? and then issue the upgrade catalog command twice to upgrade the catalog to work with the latest RMAN version? [OR] [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the regular procedure to create RMAN schema and register the DB and then take the fresh cold backup to start with, followed by the regular daily incremental backups? [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is the 8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0? Please answer my above questions and suggest an appropriate procedure to upgrade the catalog DB to 8.1.7.1.0 -Original Message- Sent: Friday, September 14, 2001 11:05 AM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! I think this is what you want to know: you should upgrade your rman database to the highest version of the databases being backed up with rman. Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 12:45 PM !! Please do not post Off Topic to this List !! Dear List Members, I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog database (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I have no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0 patch level. The box on which catalog DB exists has another production DB which was upgraded to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and then patch needs to be applied. Since patch was already applied, my only option could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL IMPORT. My questions would be: [1] Do I need to do the FULL IMPORT of the catalog database? and then issue the upgrade catalog command twice to upgrade the catalog to work with the latest RMAN version? [OR] [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the regular procedure to create RMAN schema and register the DB and then take the fresh cold backup to start with, followed by the regular daily incremental backups? [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is the 8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0? Could some one please answer my above questions and suggest an appropriate procedure to upgrade the catalog DB to 8.1.7.1.0. Thanks, -- Janardhana Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Janardhana Babu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San
DAP and Oracle Applications Maintenance pack
!! Please do not post Off Topic to this List !! Fyi, This is the server that doesn't allow parallel downloading with Download Accelerator Plus: ap103aru.us.oracle.com What's different about that one, I wonder? Anyway, attempt #1 at downloading patch 11.5.5, 11.5.4. failed three times so far. I will let you know how it goes. That server is not as busy as it was last week, though - maybe I will be successful. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Acting Head Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: find out the rollback
!! Please do not post Off Topic to this List !! 200,000 @ 50 bytes, I would say just over 40Mb of rollback. Not sure how you came up with 21akh to 200,000 :) Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, September 14, 2001 2:20 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! 200, (two hundred thousand:) English is indeed an great language as it changes in its nuances as geographic locations change ..its friday finally! srinivas for estimating rollback, consider taking snapshots of v$rollstat.waits (specified in bytes) before and after a sample delete on your table and extrapolate to identify the amount of rollback you would require for successfully completing your operation hth Deepak --- Christopher Spence [EMAIL PROTECTED] wrote: !! Please do not post Off Topic to this List !! What is 2lakh rows? Never heard of that number metric. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 12:45 PM To: Multiple recipients of list ORACLE-L !! Please do not post Off Topic to this List !! Hi lists can anybody post me how much rollback (approx in bytes/kb/mb) will be generated if I delete my table that has 2 lakh rows. the avg row len is 50. often The query is getting failed due to the error unable to extend the rollback segment. If I have this query, at least I will use a rollback segment that has enough extents. thnx in advance. Srinivas -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tatireddy, Shrinivas (MED, Keane) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: lots and lots of redo logs
!! Please do not post Off Topic to this List !! I think that Chris was referring to the maximum number of redo log group one COULD create - an Oracle hard limit. I don't know what it is, but I know its not 8 since I have created OPS databases with 12 redo groups for each instance and multiple instances. -Don Granaman [Orasaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 14, 2001 11:20 AM !! Please do not post Off Topic to this List !! Christopher R. Spence said: You can only have a max of 8 log groups if I remember correctly. Gee, I thought that the DBA controlled the maximum number of redo logs allowed for a database. There's this thing called a database create statement: (e.g.) CREATE DATABASE DEV LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K, 'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K [...] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
!! Please do not post Off Topic to this List !! I have a nightly load job that was being tracked by our developers. According to their nightly logs (going back months), a query was running as far back as they can record with a sub-second response time. Then on a particular date (Aug. 23rd), the query started taking more than 20 minutes to complete. It has taken that long to complete ever since. I looked at the explain plan and it looks o.k. Indexes are being used and there are no suspicious full table scans. The init.ora file has not changed since then. We restored a full copy of the database to an alternate host using rman. It should be an exact copy as of Aug. 16th. I ran the query on the copy and on the current production database and the resulting explain plans were identical except for the number of rows returned. Total execution time and cpu times were similar. I looked through our change documentation and I do not see any record of data structure changes or any data changes at all in the database in question. I am sort of at a loss for what to try next. What sort of changes might cause such an extreme degradation in performance as this? This is an 8.1.7 database on Sun Solaris 2.8. The optimization is rule-based. No partitioning. Database is about 80 Gig in size. Following is the explain plan, if anyone is interested: SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID, ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM, ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.26 0.27 0 0 0 0 Execute 2 0.01 0.01 0 0 1 0 Fetch 128982.191026.27 1454639732999 55484 1897 --- -- -- -- -- -- -- total 131982.461026.55 1454639732999 55485 1897 Rows Row Source Operation --- --- 1897 FILTER 2041 NESTED LOOPS 2422HASH JOIN 2341 NESTED LOOPS 2342 NESTED LOOPS 2338 NESTED LOOPS 2338NESTED LOOPS 2346 NESTED LOOPS 2510 NESTED LOOPS 2510 NESTED LOOPS 2510INDEX FAST FULL SCAN (object id 17279) 5018INDEX UNIQUE SCAN (object id 17278) 5018 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 5018INDEX UNIQUE SCAN (object id 17266) 4854 INDEX RANGE SCAN (object id 17270) 4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN 4682 INDEX RANGE SCAN (object id 17283) 4674VIEW ACTIVE_EAS_RPT_PROF_VIEW 100491 SORT UNIQUE 43 UNION-ALL 10 TABLE ACCESS FULL EAS_RPT_PROF 33 FILTER 34NESTED LOOPS 734 NESTED LOOPS 207976 NESTED LOOPS 207976 MERGE JOIN CARTESIAN 706INDEX FAST FULL SCAN (object id 17270) 208680SORT JOIN 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 415950 TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP 415950INDEX UNIQUE SCAN (object id 17266) 208708 INDEX UNIQUE SCAN (object id 17275) 766 TABLE ACCESS FULL EAS_RPT_PROF 4678 TABLE ACCESS FULL USER_SIGNON 2341 INDEX UNIQUE SCAN (object id 17275) 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 4461VIEW ACTIVE_EAS_PERSON_VIEW 2675205 SORT UNIQUE 1105 UNION-ALL 128 NESTED LOOPS 1107INDEX RANGE SCAN (object id 17284) 128TABLE ACCESS BY INDEX ROWID EAS_PERSON 2212 INDEX UNIQUE SCAN (object id 17277) 977 FILTER 1008NESTED LOOPS 288511 NESTED LOOPS 326271 MERGE JOIN CARTESIAN 1107 INDEX RANGE SCAN (object id 17284) 327376 SORT JOIN 295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG 614780 TABLE ACCESS BY INDEX ROWID EAS_PERSON 652540 INDEX UNIQUE SCAN (object id 17277) 289517 INDEX UNIQUE SCAN (object id 17275) 540 SORT AGGREGATE 287TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG 557 INDEX RANGE SCAN (object id 17276) 1346 SORT AGGREGATE 737TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG 1412 INDEX RANGE SCAN (object id 17270) 3938 SORT AGGREGATE 2066TABLE ACCESS BY INDEX ROWID EAS_PERSON_ASSGN_STS_LOG 4035 INDEX RANGE SCAN (object id 17279) 680SORT AGGREGATE 355 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG 696 INDEX RANGE SCAN (object id 17276) 2614
