Re: Statspack Will Not Install Oracle 9.2.0.4
Ethan: You can remove the order by if you are not using RAC. Basically it is to avoid two identical SQLs inserted at the SAME time in RAC setup. If you are using RAC just add another column in the order by condition. (st1.hash_value,ss.text_subset,st1.piece) I don't have the bug # handy. But I was told this is fixed in 10g ;) KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Suggestions Needed: Latch free - library cache
Ryan: Same named user with large number of connections is not a problem. Things will become bad only IFF the large number of different users using the same set of public synonymns. KG --- Ryan [EMAIL PROTECTED] wrote: how do you feel about connection pooling? Our software engineers implemented that here? Am I wrong to be concerned about large numbers of users using the same named user? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 6:39 AM Steve, By distinct user do you mean distinct username? Or sid/serial# combination? In my case, we use connection pooling, while there may be up to 300 sessions, they are all the same named user. Rachel --- Steve Adams [EMAIL PROTECTED] wrote: Hi All, Someone has alerted me to this thread, and asked for a comment. On a quick scan, and it seems to me that you've mostly got it right. The problem is that when an SQL statement that refers to its base objects via public synonyms is shared by multiple distinct Oracle users, then name resolution and permission checking need to repeated for each distinct user, and because the results of these actions are cached on the shared cursor, they increase the cost of subsequent such operations. That is, public synonyms cause extended latch retention as well as additional latching. For example, if 500 distinct users share 200 SQL statements that refer 300 times to 100 base tables via public synonyms. Then there will also be 100 * 500 non-existent objects in both the dictionary cache and the library cache; 200 * 500 cursor authorization structures; and 300 * 500 negative dependency records in the library cache. These last two things are cached as segmented arrays that are scanned linearly - thus the increased latch retention. If your application doesn't have hundreds of distinct Oracle users, or if you can afford the extra latch gets and longer latch retention, then you will probably not notice all of this unless you start doing library cache dumps. That is, the use of public synonyms is a major scalability threat, but does not normally cause performance problems. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: Suggestions Needed: Latch free - library cache
Yong: I have not followed the thread completely. So I may be missing something obvious ;) BEGIN-NON TECHNICAL Many applications (for example Oracle Applications) use public synonyms heavily and running with better (or acceptable) performance. We should not really worry about the milli second performance improvements comparing with the coding/application development flexibilities offered by public synonymns. If you look at Steve's test carefully, the improvement or over head in the public synonym to private synonym is around 10% of the CPU time and latch gets. How much performance improvement you can expect in practical systems with the additiona 10% of latch gets/CPU times comparing with the overall system performance. I would expect less than (LESS THAN) 1-2% in total response time. END NON TECHNICAL The actual over head is coming from the negative dependency tracking or high version count in the Library cache. But this will be an issue only when too many users are connected and accssing the database with too many accounts (different parsing user_id). But this is rarely a situation in many of the packaged application as most of the application connects to the database using a single account (like 'APPS' user in Oracle eBusiness suite) and no negative dependency or multi version of SQLs are an issue here (at least in a single instance oracle). The over head can be little higher in RAC environment as the Library Cache and Row cache is globally co-ordinated. However I have not really seen major problem (may be I have not observed them too keen) with the public synonyms as most of the packaged applications I have worked are using a single oracle account to connect to the database. It is just my personal observation, and may be Jonathan can have a different opinion. Regards, KG PS : Aplogies if some one has already addressed this issue, I have been traveling and didn;t have enough free time to read all the posts. Seen lots of posts flooding on this topic and jumped in out of curiosity. --- Yong Huang [EMAIL PROTECTED] wrote: To the OP: Other people point out common reasons for library cache latch contention. A less common reason is extensive use of public synonyms. If that's the reason, you also see row cache objects latch contention. I'm not sure that's right. If everyone uses a public synonym, then you get one sql text, and one cursor. I think the contention appears because everyone has to have a 'non-existent' reference in memory to say that they don't own an object with the same name as the public synonym - consequently if you have lots of users who have to check long chains of 'non-existent' then the latches get held for longer periods of time. Hi Jonathan, I don't see how your statement contradicts the claim that heavy use of public synonyms causes contention for not only library cache latches but also row cache objects latches. What I had in mind is Steve Adams' test. Here's the URL http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I understand right, the additional row cache objects latch gets are for synonym translations, particularly public synonym translations. Yong Huang = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DB_WRITER_PROCESSES vs DBWR_IO_SLAVES
Thomas: I think you are right on monitoring the db writer performance. You should look the write complete waits/ free buffer waits to understand the db writer contention. I am not sure what prompted your Sr.DBA to think on increasing the IO slaves or db writers. KG --- Thomas Jeff [EMAIL PROTECTED] wrote: Running 9.2.0.2 on AIX 4.3.3. We recently experienced a situation where a datawarehouse database crashed due to a bug dealing with DBWR_IO_SLAVES 0. Oracle's recommended fix was to set the DBWR_IO_SLAVES to 0, which I did. Now, my understanding is that if AIO is enabled on the box, the rule of thumb appears to be to use DB_WRITER_PROCESSES and set DBWR_IO_SLAVES = 0. Now, the Sr DBA here is screaming about the performance since I made the change, in particular, he says he's seeing high redo latch contention and redo log buffer waits which he says validates his contention that we need to get back to using DBWR_IO_SLAVES. I admit I'm confused. I don't clearly see the relationship that he complaining about. My idea of 'monitoring' was to assess free buffer waits, and to monitor the batch schedules to see if any degradation in timing had occurred, and since the change, neither has been an issue. Am I missing something?What stats should I be assessing to contrast the use of DB_WRITER_PROCESSES vs DBWR_IO_SLAVES? And yes, I've googled and metalinked and and most information is mainly pertinent to 7.3.4 and 8i and of a more general nature. And yes, DISK_ASCYCH_IO is set to TRUE. Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Books on rac
AS OF NOW, I have not come across a book which talks about RAC at a detailed level (at least to the level we expect). There are couple of books in the market, but they cover very little on RAC (Concepts and Internals) But, the Oracle Documentation (At least for the RAC part) is very good and it is very readable and has all the information you need. --- Joe Testa [EMAIL PROTECTED] wrote: any recommendations? of course besides the oracle docs and technet, which i think i downloaded all that i need. joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: recreable/freeable chunks
Tanel: You are right. But the actual freeing process (from the re-creatable chunks) is quite different from what you have explained. The recreatable chunks (they are freeable, but can be re-creatable if required as you have explained) will be falling in two catagories. They are either pinned or un pinned. The unpinned list again has two separate lists (two LRU lists, one for transient and another one is for recurrent), and the freeing process starts from transient list. IF we don't get the required memory, then it is moved to the other list, i.e recurrent chunk LRU and the search ends when the required size is found. I think 8 chunks are freed in the previous list before it shifting to the recurrent list. If the process doesn't get the required free memory, it will try to allocate from hidden memory before signalling ORA-04031. --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! I wrote about recreatable chunk handling from memory and mixed up chunk freeing routines and chunk's data recreation routines: A callback routine is used when a process wants to free a recreatable chunk - it can't reuse it just like that, because the original allocator (parent object) still has pointers pointing to this chunk, so the same object who allocated the chunk, has to do the freeing as well. That's my understanding, it'd be interesting to know whether it's correct ;) Tanel. Recreatable: This chunk is used, but its contents can be regenerated if needed (such are execution plans for cursors for example), thus Oracle can automatically reuse these chunks for others (of course when these chunks aren´t in use, thus unpinned). So when this information orignially stored in recreatable chunk is needed again, a callback function is executed, to recreate the contents in a chunk - callback functions containing instructions to recreate the information are registered for each recreatable chunk in heap manager's area if I recall correctly...) Information about unpinned recreatable chunks are held in heap LRU lists. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hit Ratio
Yong:
I have not seen all the threads on this. So there are chances some body
might have
covered this/I may be missing some interesting things..But the issue is,
tuning or measuring the
database performance ONLY with Hit Ratios. By high hit ratios Damagement
will tend to understand , that percentage of data is read from the
cache/memory
and try to add memory till the get closer to 100..
I think what we need to understand is the interpretation of Hit ratio. 90%
HIT
ratio does not mean 90% of the data is read from the disk. It just tells a
block
or buffer which was read in to cache is RE-READ 9 times before it goes to
disk.
I have seen many sites with oversized buffer cache/shared pool targetting
100%
hit ratio and suffering huge latch contention. I have been to a site
recently where a FLUSH
shared pool took nearly 5 minutes and checkpoint took close to a minute,
with 99.99%CHR.
But simulating high wait times by yout tricks for a particular session may
bump the wait times
You may probably generate high times for enqueue or any of the IO
events. But when you use 10046 or V$session_wait for a particular session,
the bumped numbers will not be affecting the diagnosability of your
problem.
But if you want to start questioning, you can question the timing details of
the
wait events. Oracle uses gettimeofday () to get the time of the wait events
and
if you alter the system time couple of times, that may give some odd numbers
to the entire timing data. But the bottomline is , Hit ratios are
beautiful numbers
but, you can not relate the pattern to the performance. May be you can
compare
the hit ratio when the system is good/bad and figure out there is a change
in IO
pattern between those interval.. IMHO and YMMV.
Regards,
K Gopalakrishnan
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 8:59 PM
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor.
Only
if you carefully comtemplate it will you see that there's no relevance of
the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event
times. If
you need very long db file reads, create a big table and keep scanning it.
If
you need long enqueue waits, create a table and insert a row. Create 10 or
100
sessions (depending on your patience) and delete from that table and wait.
The
fact that you can get arbitary wait times does not reduce the efficacy of
wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very
insufficient
used alone, to tune the database. The reason is that they don't contain
enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as
scan
rate, CPU usage and netstat output more often, hit ratios in some sar
output
are still occasionally used. Most ratios could still be distored by a
rogue
user repeatedly doing, say, find / for inodes or find / -exec grep
SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage
patterns
invalidates your numbers even if you're using a well respected tuning
method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command
Re: rebuilding indexes - sure to cause a ruckus
Richard: I think that is the simple way of questioning other person's capacity. Remember this statment (borrowed from some one !!) If you are telling something is simple, you are questioning the other person's intelligence !!' KG -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ** can two processes use the same rollback extent
Yes Jonathan: I think that 400 byte concept came from one of the TPC benchmarks during Oracle 7 (or ORacle 6) days where the average undo size for a transcation was around 380+ bytes. So they had rounded that to 400 bytes and a maximum of 5 blocks will be linked to the freelist in the undo segment header. THis is called 'free extent pool' and the structure and the blocks are clearly visible in the undo header dumps. Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 1:59 PM A single block can only be 'owned' by one transaction at a time, but when a transaction commits, it may put its last undo block into that segment's 'free pool' list if there is a lot of free space left in the block so that it can be made available for new transactions . (Gaja Vaidyanatha quoted 400 bytes as the limit). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 3:59 AM i can answer the first part of your question.. YES, two transactions can write on the same extent of an RBS, i think the restriction is at the block level, this was true until 8i, might have changed in 9 + -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC and Forms6i
Hi, RAC is just a database option. If RDBMS 9.2 is certified against a product means, 9.2 RAC is also certifid on that product. But all the RAC features may not work with the certified option.. (Is it too confusing?!( Okay.. The TAF feature in RAC (okay... it is not a RAC feature!!) will not work with forms. Is this what you are looking or something else? Regards, Gopal --- Bruno Vanters [EMAIL PROTECTED] wrote: Hi all, Oracle Forms Reports patch 15 is certified with Oracle 9.2 (according to Metalink). Does that mean that Forms are certified against RAC too? Can anyone confirm that, having/knowing any working RAC9.2+Forms6i environments? Thanks, Bruno Vanters Junior Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruno Vanters INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 'internal' role and 9i
INTERNAL_USE is an keyword (to enable an undocumented feature) in ALTER DATABASE command. THis can be used to convert the database character set if the existing char set (national charset) is the superset of the db charset. You can just run the ALTER Database command to convert the db charset. Best Regards, K Gopalakrishnan -Original Message- Barry Deevey Sent: Tuesday, November 18, 2003 7:09 AM To: Multiple recipients of list ORACLE-L As of yet I'm unsure how the application would be affected if I rename the role - I need to do some investigation. I tried this in Oracle 8 and it worked fine - It just seems to be oracle 9 that doesn't like it. I've also checked v$reserved_words and INTERNAL is not listed, INTERNAL_USE and INTERNAL_CONVERT are. As a test I created roles for INTERNAL_USE and INTERNAL_CONVERT, hoping that it would not allow me to create them, but it did, so I then ran the grant again and it also allowed it. Now I'm really confused!! -Original Message- Tim Gorman Sent: 18 November 2003 13:34 To: Multiple recipients of list ORACLE-L Barry, Why make life difficult? It's just a role, not a data object referenced by applications (hopefully). Change it's name to something that is not a reserved word and move on. There is a list of reserved words in the SQL Language reference. Hope this helps... -Tim Hello all, I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm getting loads of the same error: IMP-00017: following statement failed with ORACLE error 9275: GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL IMP-3: ORACLE error 9275 encountered ORA-09275: CONNECT INTERNAL is not a valid DBA connection I'm aware that connect internal does not exist in 9i, but 'internal' is a role. So as a test I dropped the role, recreated it and then manually tried to grant it something - The same error occurred: SQL select * from dba_roles where role like 'INTER%'; ROLE PASSWORD -- INTERNAL NO SQL SQL drop role internal; Role dropped. SQL create role internal; Role created. SQL GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL * ERROR at line 1: ORA-09275: CONNECT INTERNAL is not a valid DBA connection SQL This doesn't make any sense to me. Can anybody help to shed any light on this?? TIA for any response, they're much appreciated. Cheers, Barry. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barry Deevey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barry Deevey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: col_usage$ question
Raj: Wolfgang is right. It is populated by SMON (I think every 15mins SMON flushes the data to COL_USAGE) and the predicate columns are updated (or collected) from the hard parse of the SQLs. Best Regards, K Gopalakrishnan -Original Message- Wolfgang Breitling Sent: Tuesday, November 18, 2003 8:59 AM To: Multiple recipients of list ORACLE-L That is a new table in Oracle 9 and is used by Oracle to track what columns are used in predicates. At present the only use of that information that I am aware of is in the procedure dbms_stats.gather_table_stats ( ..., method_opt = 'for columns ... size auto'); At 09:29 AM 11/18/2003, you wrote: Does anyone know what this table (sys.col_usage$) is used for? To me it sounds like something that CBO might appreciate ... but any ideas? It is referenced by dbms_stats and dbms_stats_internal packages ... Thanks in advance Raj --- - Rajendra dot Jamadagni at nospamespn dot com Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: alter database character set (Was: RE: 'internal' role and 9i)
INSERT STANDARD DISCLAIMERS Yes. You can use INTERNAL_USE keyword to convert the database character set. I think there is a utility called 'csscan' character set scanner which can be used to determine the possibility of the INTERNAL_USE conversion. END DISCLAIMERS Best Regards, K Gopalakrishnan -Original Message- Yong Huang Sent: Tuesday, November 18, 2003 9:00 AM To: Multiple recipients of list ORACLE-L Gopal, Are you saying with an undocumented parameter or command, I can alter database (national) character set us7ascii even if my current (national) character set is utf8? Yong Huang --- K Gopalakrishnan [EMAIL PROTECTED] wrote: INTERNAL_USE is an keyword (to enable an undocumented feature) in ALTER DATABASE command. THis can be used to convert the database character set if the existing char set (national charset) is the superset of the db charset. You can just run the ALTER Database command to convert the db charset. Best Regards, K Gopalakrishnan -Original Message- Barry Deevey Sent: Tuesday, November 18, 2003 7:09 AM To: Multiple recipients of list ORACLE-L As of yet I'm unsure how the application would be affected if I rename the role - I need to do some investigation. I tried this in Oracle 8 and it worked fine - It just seems to be oracle 9 that doesn't like it. I've also checked v$reserved_words and INTERNAL is not listed, INTERNAL_USE and INTERNAL_CONVERT are. As a test I created roles for INTERNAL_USE and INTERNAL_CONVERT, hoping that it would not allow me to create them, but it did, so I then ran the grant again and it also allowed it. Now I'm really confused!! __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RBO to CBO migration books/ material
Suhen: John K has a nice paper (IOUG-A?) at his website. Google for his name or 'RBM CBO Migration minefields' Regards, Gopal -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 10g new features
Yong: I was not joking. Actually there is a command to rename tablespaces in 9i and that is undocumented. IIRC it is with some FROM and TO options or something similar to that. Let me check that come back to you offline.. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 9:39 AM Gopal was joking about the fact that Jared only said 'alter tablespace rename' instead of ALTER TABLESPACE RENAME DATAFILE. In fact, 9.2 SQL Reference has this Moving and Renaming Tablespaces: Example This example moves and renames a datafile associated with the tbs_01 tablespace from 'diskb:tbs_f5.dat' to 'diska:tbs_f5.dat': If you only read the subtitle here (first line), you *will* be surprised. Yong --- Mladen Gogala [EMAIL PROTECTED] wrote: Actually, I don't understand what you mean. Here is 9.2: SQL create tablespace test datafile '/data/oradata/data/test01.dbf' 2 size 10M extent management local autoallocate 3 segment space management auto; Tablespace created. SQL alter tablespace test rename to test01; alter tablespace test rename to test01 * ERROR at line 1: ORA-01904: DATAFILE keyword expected SQL So, what did you mean? On 2003.11.03 21:59, K Gopalakrishnan wrote: Jared: 'alter tablespace rename' is not the REAL 10g feature. It is available from 9.2 onwards... :) Hope you know what I mean,, KG - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 04, 2003 12:54 AM Subject: 10g new features Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: What is a local write wait?
This event is one of those 'you would never see' in normal database operations. As I have mentioned in my earlier post, existance of this event translates in to some serious I/O issues or broken disk in RAID05 or something similar to that.. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, November 03, 2003 10:24 PM Thanks KG. There were underlying OS I/O issues. The SA is looking to fix the same. Regards Raj __ Rajesh L. Rao DCI - Triad, Oracle DBA IBM Global Services, JPMC Account Phone: 516 5746065 E-mail: [EMAIL PROTECTED] K Gopalakrishnan To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject: Re: What is a local write wait? o.com Sent by: [EMAIL PROTECTED] ity.com 11/01/2003 09:54 AM Please respond to ORACLE-L Rajesh: Typically DBWR has to free up some buffers when you want to read something from the disk. During this process there are chances that you will be waiting for your local buffer (i.e blocks dirtied/invalidated by your session) to be written to disk. During this time the waits are shown as local write waits. BTW do you have any other write waits or just seeing local waits? And also are you noticing any timeouts for this waits? Typically we wait for local wait up to one second and spin (or retry) again.. Timeouts for local write indicates a serious problem unless you have tiny buffer cache or extremely slow disk' KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 01, 2003 12:54 PM Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 10g new features
Jared: 'alter tablespace rename' is not the REAL 10g feature. It is available from 9.2 onwards... :) Hope you know what I mean,, KG - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 04, 2003 12:54 AM Subject: 10g new features Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared
Re: What is a local write wait?
Rajesh: Typically DBWR has to free up some buffers when you want to read something from the disk. During this process there are chances that you will be waiting for your local buffer (i.e blocks dirtied/invalidated by your session) to be written to disk. During this time the waits are shown as local write waits. BTW do you have any other write waits or just seeing local waits? And also are you noticing any timeouts for this waits? Typically we wait for local wait up to one second and spin (or retry) again.. Timeouts for local write indicates a serious problem unless you have tiny buffer cache or extremely slow disk' KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 01, 2003 12:54 PM Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Alter tablespace begin backup
Jeremiah Wilton has a nice note on his site.. Check it at http://www.speakeasy.org/~jwilton/oracle/hot-backup.html = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 10046 level 8 trace - help required with 'direct path
Yong: They are temp files. The file# for the temp files start with db_files+1. Because of this, there may be chances your pl will have 1000+ though you have only hand ful of datafiles. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 10:39 PM Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: using temp tables for staging databases?
Hi, The walking in the freelist is just 5 blocks (or the value of _walk_insert_threshold number of blocks) and I belive tanel is talking about (_release_insert_threshold) unlinking from freelist, which also default to 5 blocks. KG - Original Message - From: Binley Lim To: Multiple recipients of list ORACLE-L Sent: Monday, October 27, 2003 4:59 PM Subject: Re: using temp tables for staging databases? What you have described is expected behaviour - if"the next insert would drop amount of free space less than PCTFREE", the block is unlinked. What Ihad notconsidered is what happenswhen the block is still below PCTUSED? As usual, Steve Adams' website explains this very nicely - it is not unlinked, just walked over, so the next smaller insert is likely to succeed. And as you pointed out, It would indeed take rows that are a large proportion of blocksize for this effect to have an impact. - Original Message - From: Tanel Poder To: Multiple recipients of list ORACLE-L Sent: Saturday, October 25, 2003 10:09 PM Subject: Re: using temp tables for staging databases? No, blocks won't fill up to PCTFREE in case a block is already above PCTUSED and the next insert would drop amount of free space less than PCTFREE. Blocks are just unlinked from freelist in this case. That means if you normally have 10byte inserts and occasionally have 4000 byte inserts in your table, then you might be wasting space due to premature unlink of blocks in freelist.
Re: state objects
Sultan: PGA is a private memory area which holds actual memory components (like sort area, event settings,parameter settings and UGA). State objects are just the data structure which holds the info about the OS resources (b)locked by the instance. In other words, state objects can be called as global and PGA components are local to that process (in crude terms.) KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, October 26, 2003 10:59 AM Hi K Gopalakrishnan , Wishing you the same Thanks I understood what is state objects,but another question arisses,when this state objects having process status then what this PGA really do and why that process status recorded again in PGA? Please clarify - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 8:59 PM Wishing you all a very Happy Diwali.. The festival of lights ! State object is a structure inside the shared pool which keeps the details about every components (for example, process or session or enqueues) and their status (or state). Basically it is an operating system resource related to the oracle instance (or an OS resource held by oracle instance) which has multiple states (free/init and dead I think). The background process PMON is responsible for freeing those resources to the OS should any of the state object dies because of the process failure. Typically the SYSTEMSTATE dump will have the details about the state objects. Part of this info is visible in the V$sysstat. You will see the state object as SO in the system state dump. Along with you get the owner (holding that SO) and the state of the state object (is that too confusing?) which tells whether that SO is in the freelist or initialized or dead. For better understanding of the various state objects I would recommend to take a systemstate/process state dump and have a look at the trace files. Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:29 PM Does anybody have a definition for the Oracle shared pool component State Object? An Object that holds the state of something? Using Google, I get some hints, but just wondered if someone has something definite. Sultan - your question got my curiosity aroused. I'm sorry I lost your reply that confirmed you were indeed looking at the SGA. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 8:59 AM To: '[EMAIL PROTECTED]' Sultan - Are you referring to the Oracle shared pool components that are identified as state objects? A quick Google also revealed that Java has state objects. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 3:14 AM To: Multiple recipients of list ORACLE-L Can someone please explain what is 'state objects' in oracle and what is that real work? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sultan Syed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
Re: state objects
Wishing you all a very Happy Diwali.. The festival of lights ! State object is a structure inside the shared pool which keeps the details about every components (for example, process or session or enqueues) and their status (or state). Basically it is an operating system resource related to the oracle instance (or an OS resource held by oracle instance) which has multiple states (free/init and dead I think). The background process PMON is responsible for freeing those resources to the OS should any of the state object dies because of the process failure. Typically the SYSTEMSTATE dump will have the details about the state objects. Part of this info is visible in the V$sysstat. You will see the state object as SO in the system state dump. Along with you get the owner (holding that SO) and the state of the state object (is that too confusing?) which tells whether that SO is in the freelist or initialized or dead. For better understanding of the various state objects I would recommend to take a systemstate/process state dump and have a look at the trace files. Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:29 PM Does anybody have a definition for the Oracle shared pool component State Object? An Object that holds the state of something? Using Google, I get some hints, but just wondered if someone has something definite. Sultan - your question got my curiosity aroused. I'm sorry I lost your reply that confirmed you were indeed looking at the SGA. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 8:59 AM To: '[EMAIL PROTECTED]' Sultan - Are you referring to the Oracle shared pool components that are identified as state objects? A quick Google also revealed that Java has state objects. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 22, 2003 3:14 AM To: Multiple recipients of list ORACLE-L Can someone please explain what is 'state objects' in oracle and what is that real work? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Seven Deadly Sins in Oracle?
List, Anyone has the copy of Steve Adams' 'Seven deadly sins in Oralce' paper. I guess Steve presented this in OOW '01 and I am looking for that paper.. If anyone has a copy of that note, please mail me privately. Thanks ! Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 10046 trace question
Title: 10046 trace question Raj: THe simple option is to run the ALTER SESSION command to set some session level parameter like 1=1 and get the timings. Giving the EXECUTE on DBMS_SYSTEM is not a good idea. KG - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 22, 2003 10:09 PM Subject: 10046 trace question Hi all, I am monitoring a production database and while we have performance issues looked at, I have 10046^8 running on all user session in this RAC db. The scenario is as follows ... user logs in through a windows terminal server, opens multiple sessions (oracle forms) to connect to database. Whenever they see a performance issue (AKA slowness) they hit a button on their windows session, that sends an email to us informing that user experienced slowness at say 10am. Now normally because users don't exit their session till COB, the trace files are still incomplete at the time when user reported slowness. While these trace files are useful to look at next day, there is no way (that I know of) to go into the trace file and answer questions like "what was this user doing around 10am" ... is there? Also is there an easy way to put a marker in the trace file (something like dbms_system.ksdddt) that can be invoked preferable triggered from a script ... and then we can go back to trace file and find out what the session was doing by looking at trace lines around the marker? I don't know if anyone has done this before, or I am really trying to offset the US debt by collecting pennies? Any help in this regard is greatly appreciated. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! **This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**4
RE: Cache a table
Mike: I guess we are aware there is no concept of LRU or MRU in current versions of Oracle and I don't think CACHE option will influence the behavior. With the new algorithm the MFU blocks are already in the hot end (unless they are read using CR read in that case they will be in cold end since we set the _db_aging_freeze_cr to TRUE) and we don't need to cache the blocks explicitely. You can monitor the behavior of this using the X$BH (espicially the last two columns TCH and TIM). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Find CPU clock speed on HP-UX 11
Does this work? echo selclass qualifier cpu;info;wait;infolog|cstm (Got it from Google and don't have a HP-UX now. So may or may not work) KG --- Pawan Dalmia [EMAIL PROTECTED] wrote: Hi I am also not aware of HP but on solaris u can find using prtdiag command U can find this command in /usr/platform/sun4u/sbin Regards Pawan Dalmia Database Administrator Orange-9820018753 Extn -5064 -Original Message- Sent: Thursday, October 16, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Hi! I'm new to HP-UX and need to find out how many processors are in a mchine and at what clock speed they operate. I can get the number of CPUs through glance or top, but I have no idea about the clock speed. How would I get that information? I would know how to do it on Solaris though... ;) Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pawan Dalmia INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Find CPU clock speed on HP-UX 11
Hi, It is very simple in Solaris. Just try psrinfo or psrinfo -v. You will get the CPU details. Not sure abt HP though :( KG --- Daiminger, Helmut [EMAIL PROTECTED] wrote: Hi! I'm new to HP-UX and need to find out how many processors are in a mchine and at what clock speed they operate. I can get the number of CPUs through glance or top, but I have no idea about the clock speed. How would I get that information? I would know how to do it on Solaris though... ;) Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LONG data type in 10g
YES. --- Nancy Hu [EMAIL PROTECTED] wrote: Does any one know if 10g still supports LONG data type? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: LONG data type in 10g
SQL*Plus: Release 10.1.0.0.0 - Beta on Thu Oct 16 20:35:19 2003 Copyright (c) 1982, 2003, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL startup; ORACLE instance started. Total System Global Area 170990484 bytes Fixed Size 760724 bytes Variable Size 121634816 bytes Database Buffers 25165824 bytes Redo Buffers 23429120 bytes Database mounted. Database opened. SQL desc view$; Name Null?Type - OBJ# NOT NULL NUMBER AUDIT$NOT NULL VARCHAR2(38) COLS NOT NULL NUMBER INTCOLS NOT NULL NUMBER PROPERTY NOT NULL NUMBER FLAGS NOT NULL NUMBER TEXTLENGTH NUMBER TEXT LONG SQL desc trigger$; Name Null?Type - OBJ# NOT NULL NUMBER TYPE# NOT NULL NUMBER UPDATE$ NOT NULL NUMBER INSERT$ NOT NULL NUMBER DELETE$ NOT NULL NUMBER BASEOBJECTNOT NULL NUMBER REFOLDNAME VARCHAR2(30) REFNEWNAME VARCHAR2(30) DEFINITION VARCHAR2(4000) WHENCLAUSE VARCHAR2(4000) ACTION#LONG ACTIONSIZE NUMBER ENABLEDNUMBER PROPERTY NOT NULL NUMBER SYS_EVTS NUMBER NTTRIGCOL NUMBER NTTRIGATT NUMBER REFPRTNAME VARCHAR2(30) ACTIONLINENO NUMBER --- Mladen Gogala [EMAIL PROTECTED] wrote: Much more menacing question: Are the columns DBA_VIEWS.TEXT and DBA_TRIGGERS.TRIGGER_BODY still of the type long? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bitmap conversion on a index that is not bitmapped ???
John: Optimizer is a smart boy!!! He knows the column has few distinct values and decides the BITMAP access would be appropriate and making BITMAP plans from the BTree indexes. If you delete the stats for that index, you will get the old behavior. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: bitmap conversion on a index that is not bitmapped ???
Missed one important point. You can disable that by setting _b_tree_bitmap_plans to FALSE. Deleting the stats may not be a right apporach as that may screwup some other plans. Sorry for missing the important one.. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 10:39 PM John: Optimizer is a smart boy!!! He knows the column has few distinct values and decides the BITMAP access would be appropriate and making BITMAP plans from the BTree indexes. If you delete the stats for that index, you will get the old behavior. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: More info about _db_writer_max_writes
Hans: This parameter limits the max # of buffers can be pending for an AIO operation. Usually you never need to tune (!) this parameter as the default holds good for most platforms. BTW what is the size of the log buffer? You will never have more than 4k pending buffers unless you have a really huge (!) log buffer. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 06, 2003 9:19 PM Hi All, The current value for my AIX 4.3.3 / 8.1.7.4 instance parameter _db_writer_max_writes is 4096. I've just raised my aio maxreqs to 16384. Would it be beneficial to increase _db_writer_max_writes? HTH, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: x$ constructs and memory
Pete: Sorry for the delay. I was traveling back to Bangalore from San Francisco when you sent the message. There is a procedure in the DBMS_SYSTEM package called KCFRMS which resets certain timing information from the X$KCFIO (which is exposed as V$FILESTAT). And also there is an event which can be used to flush the buffer cache and that will reset the part of the X$BH stats (very similar to ALTER SYSTEM FLUSH BUFFER CACHE in 10g and above!!). Regards, Gopal - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 04, 2003 3:24 AM Hi Gopal, I have followed this thread with interest and i was waiting for you to elaborate on the following statement, specifically what undocumented procedures ? kind regards Pete code and you can not create/update/delete them. However there are some undocumented procudures , thru which you can reset certain tables. Regards, Gopal -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Steps to convert a RAC to a single instance.
Murali: Other than init parameter changes, you need to turn the RAC components off from the oracle binaries. Otherwise RAC will be still running in the oracle irrespective of init parameter. TO Disable RAC.. follow these steps; 1. Shutdown ALL instances on that oracle home 2. Go to $OH/rdbms/lib 3. Use the make command to relink the oracle binaries without RAC Components.. i.e. make -f ins_rdbms.mk rac_off 4. Relink the binaries i.e make -f ins_rdbms.mk ioracle. Thats all !!! Now you have Oracle Binaries without RAC. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: x$ constructs and memory
mind. As I began to ponder this (I asked the resident marmot, but he must be a SQL*Server expert...), I came up with several questions. Where in memory (sga or other) do the x$ constructs reside? Some of them are 'populated' by reading file-based structures (control file, datafile headers, undo segments). Does this information reside in memory or is it loaded each time the x$ construct is accessed? What happens when these x$constructs begin to consume large amounts of memory? Is there an upper bound? Daniel Fink -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: x$ constructs and memory
Mladen: I hope you are not kidding.. X$ table (!) definitions are defined in the source code and you can not create/update/delete them. However there are some undocumented procudures , thru which you can reset certain tables. Regards, Gopal - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:54 PM With all due respect, I don't believe that it is a fixed area. You can create X$ tables by running certain catalog scripts. I believe that the description of X$ tables is located logically close to the description of the data dictionary, which would mean shared pool, not the fixed one. Now, can we get back to bears? -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tanel Poder Sent: Monday, September 29, 2003 1:45 PM To: Multiple recipients of list ORACLE-L Subject: Re: x$ constructs and memory What I have not checked so far is how an ALTER SYSTEM increasing a parameter affects the SGA. In practice it's a realloc() (functionally speaking). It would seem reasonable to me to have a shared memory segment to hold all parameters which can by dynamically changed. I wouldn't touch it if parameters are decreased, but I would have to realloc it in case of a massive increase. Hmm, I guess that I would allow some spare memory initially, performance penalty would otherwise be severe. Which all makes the 10g dynamic rearrangement quite sensible ... Hi! I think the behaviour depends on which parameter you are changing. If you're changing shared_pool_size to higher size, then just additional extents of memory are allocated and heap header is updated. If you set sort_area_size higher, nothing particular happens, except some maximum is increased in UGA I believe and during next sort you can go up to that limit. Some parameters like enqueue_resources can't be changed in the fly, because they are fixed, they stay in fixed area of SGA, fixed area isn't managed as heap as I understand, it does not have any free or LRU lists, because it's physical structure remains unchanged during the lifetime of an instance. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
Re: any else try trace analyzer?
Dan: Have you tried the itfprof at www.ubtools.com , which also gives lots of meaningful info from the 10046 trace. It is free, online, web based and what else you need ? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Standby database.
Avnish: That is not possible in 8.1.7. You need to be atleast 9.2 to do that. --- [EMAIL PROTECTED] wrote: We are planning to migrate one 600GB OPS (2 nodes) database from AIX 433 Oracle 816 to AIX5.1 Non OPS Oracle 8174. Is it possible to use Oracle standby database of two different versions of OS and database. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to extract data directly from a datafile ?
Richard: Not sure whether somebody else also tried this. Sometime back we (yours turley and few others ) tried developing a tool to convert a datafile from one OS format to another OS Format (little endian-big endian or Solaris to NT etc) by reading the datablocks from one format and writing to another format. The original plan was to have a iDUL kind of tool with a fileconverter to covert oracle datafiles from one OS to another OS. So the user will have a choice to download the data from data files or convert from one os to another OS. Since oracle started addressing this facility/feature in the current (for future?) versions we have dropped that idea as we don't want to compete with Oracle :D I would be interested in knwoing abt your tool too .. KG --- Richard Ji [EMAIL PROTECTED] wrote: Good idea. I am actually planning on write something like that myself. I have wrote on a smaller scale one just to extract data for our company's database (8.1.7.4) and it was extremely handy in one of our recovery situation where a complete recovery wasn't possible. And I am using Java as well. So maybe we can combine efforts. Richard Ji -Original Message- Sent: Wednesday, September 17, 2003 5:50 PM To: Multiple recipients of list ORACLE-L Shame, maybe you could email the guy who is writing it and ask for details of where he is with it? kind regards Pete = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: latch free wait event
Hi, You can query the v$session_wait (or event 10046 if you are a big fan of 10046) to get the latch#. If I Remeber right the P2 value in the session wait (or p3?) gives the latch number and you can join this with v$latch_name to get the name of the latch. Once you find out the waiting (or spinning) latch you can get an easy fix based on the latch. Regards, K Gopalakrishnan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 05, 2003 5:14 AM The number of waits is irrelevant. What matters is the time waited which in your case shows 0, but I assume that is because you have not set timed_statistics to true. Without that the data from v$system_event are worthless. Cary will probably step in here and tell you that even with timed_statistics that data is at best of dubious worth. At 02:49 PM 9/4/2003 -0800, you wrote: System-wide Wait Analysis for current wait events Average Event Total SecondsTotal Wait NameWaits Waiting Timeouts (in secs) - - - - --- latch free1,4590 1,393 .000 After querying v$system_event my biggest concern is the latch free wait event. I understand that latch free is the process waits for a latch that is currently busy ( held by another process).How can I drill down and find the cause of this? I have a feeling it is about rollback or redo logs. thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC - lms process
Robert: It is normal behavior only. But you didn't say about the OS and Cluster Software. Make sure you use the highspeed private interconnect for the block transfer between instances. I have seen in many sites public interconnect has used for cache transfer. Other than that, you may want to try bindling lms process to the specific processor if your OS supports that. And also increasing the priority also helps. And there is an underscore parameter where you can change the lock down converts, you can try that after checking with Oracle Support ! --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Running RAC on 9.2.0.3 the lms processes seem to be very busy. Anyone have any thoughts on that? Robert -- = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Collaboration Suite
Brian: I have just implemented OCS for one of our client in the middle east and it will be live from this month end. It is working like a charm. Let me know if you have any specific questions. I will try to answer. Basically it is an excellent product for end users and not so good for administrators (at least the current versions) Best Regards, K Gopalakrishnan -Original Message- Brian Haas Sent: Wednesday, August 20, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Hello all, We're looking at Oracle collaboration suite and I'm wondering if anyone here is using it? If so, how is it working? Any issues? I know Oracle corp is using it for all their internal mail so I assume it could handle our 500 or so users just fine. Thanks, -Brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Haas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC interconnects
Raj, In current versions of RAC there is no other way (other than specifying all the IPs in the Cluster_interconnects parameter) to force ALL available private networks for GC traffic. I think this 'feature' is available/planned for 10g. I have not tested that in beta. Check with Oracle support for any known issues (bugs??) for this parameters. Otherwise you can use them..! KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: KG, yes we see the GC traffic a lot, when there are 4 production instances on each node and that is why we were interested in finding out if we could make oracle use both interconnects. Any ideas? Raj -Original Message- Sent: Saturday, August 09, 2003 3:49 AM To: Multiple recipients of list ORACLE-L Raj: Raj: Use the cluster_interconnects parameter to specify all the IPs used for GC. But there is a catch. But sometimes a NIC failure will be seen as Node failure (though it is a very very rare case). BTW Why do you want to use both NICs for GC traffic? DO you see lots of waits for messages? KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Hi all, Is it possible to somehow tell Oracle to use all available interconnects (between nodes) for the GC traffic? We have 2 private interconnects but we see all the traffic only on one of them. The other is used only if the first one is not available. If Oracle could use both, it would balance the load too ?? Is it possible? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC interconnects
Raj: Raj: Use the cluster_interconnects parameter to specify all the IPs used for GC. But there is a catch. But sometimes a NIC failure will be seen as Node failure (though it is a very very rare case). BTW Why do you want to use both NICs for GC traffic? DO you see lots of waits for messages? KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Hi all, Is it possible to somehow tell Oracle to use all available interconnects (between nodes) for the GC traffic? We have 2 private interconnects but we see all the traffic only on one of them. The other is used only if the first one is not available. If Oracle could use both, it would balance the load too ?? Is it possible? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1 = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle SCN Question
Hi, It is just combination of Base and Wrap. So your number will be 8589934593 KG --- Nick Wagner [EMAIL PROTECTED] wrote: Internally, Oracle represents SCNs as a base and a wrap. The wrap is a 16-bit number and the base is a 32-bit number. A dump of a redo log file would display the SCN as 0x.. However, the SCN is represented as a number in a number of the dynamic performance views (v$log.first_change# for example). What does this numeric value represent? Is this just the SCN base, or is it a combination of base and wrap. If the wrap is 1, 2, or 3, etc., what is the value displayed in the dynamic performance views for the SCN? For example, if the SCN was 0x0002.0001, what would that evaluate to as a single number? Thanks, Nick Wagner -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nick Wagner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC
Suhen: Check the Metalink note 166830.1 for the details. KG --- Suhen Pather [EMAIL PROTECTED] wrote: Kay, Can we get the Metalink note to run a single node RAC on Linux. TIA Suhen -Original Message- Sent: Tuesday, 22 July 2003 4:40 PM To: Multiple recipients of list ORACLE-L Not required.. We used to run OPS on single node :)) for testing purposes. There are some step by step instructions in Metalink abt how to do that. --- Goulet, Dick [EMAIL PROTECTED] wrote: You need at least 2 servers. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 21, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Is it possible to setup RAC in one box only. Just to play with it . Dont have more hardware . -ak = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC
AK: Very much possible and very simple if you are using Linux or Windoze. I think there is a note on Metalink with step by step instructions for setting a RAC on single node -Linux. It is slightly complicated if you want to run RAC on windows. I know couple of guys here who are running RAC on Windows on their laptop. You need to get a USB disk (or flash disk) to set up the raw partitions and you can run RAC on WIndows too... --- AK [EMAIL PROTECTED] wrote: Is it possible to setup RAC in one box only. Just to play with it . Dont have more hardware . -ak = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC
Not required.. We used to run OPS on single node :)) for testing purposes. There are some step by step instructions in Metalink abt how to do that. --- Goulet, Dick [EMAIL PROTECTED] wrote: You need at least 2 servers. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 21, 2003 2:49 PM To: Multiple recipients of list ORACLE-L Is it possible to setup RAC in one box only. Just to play with it . Dont have more hardware . -ak = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC system Calls
Title: RE: RAC system Calls Raj: I am discussing this issue with Ravi offline. From the initial IPC dumps, It looks like the private interconnect is not properly configured/used for cache fusion. I am waiting for more details from him.. Best Regards,K Gopalakrishnan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Friday, July 11, 2003 7:34 AMTo: Multiple recipients of list ORACLE-LSubject: RE: RAC system Calls That may be because with RAC there is more activity .. GCS traffic and more stats to update ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, July 10, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Raj, Yes. Timed_statistics=true. But I see the 'times' even in single instance(non-rac)database but relatively called negligible number of times. -Ravi.
RE: RAC system Calls
Ravi: Do you have a statspack report? I would like to see that. But in any case, 45% kernel is just too much? BTW have you verified the private interconnect is used for cache fusion transfer.. Make sure the cache fusion is not going thru the public network. Best Regards, K Gopalakrishnan -Original Message- Ravi Kulkarni Sent: Thursday, July 10, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Hello List, We are running Benchmark tests on Solaris 2-Node RAC. Consistently noticed the following : - Very high Kernel usage (averaging 45%) on TOP - Statspack has IPC Send Completion sync waits (70% Total ela time) - On trussing top process, found Oracle to be issuing huge number of times system calls in addition to read/writes(which I think are select/inserts). Has anyone noticed this in your environment. I am guessing these to be inter-instance pings, but could not get any hits in Doc/Metalink to confirm this. times call is clocking lot of CPU. Is this normal ? Any pointers would be helpful ? If this is out of context, is there a separate list for RAC? Thanks, Ravi. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle 9.2 RAC on Win2K?
Branimir: Comments inlined .. --- Branimir Petrovic [EMAIL PROTECTED] wrote: I need help with clearing up one conceptual issue: My understanding is that with Oracle RAC one set of physical database files sitting in the middle - shared storage are being accessed by multiple Oracle instances running on multiple physical servers (nodes). All instances attacking (sharing) the very same set of data files at the same time. Right or wrong? Not necessarily. You can run RAC in a single machine also. I have few guys here who run RAC in their windoze laptops. But in general your understanding is very correct Provided the answer on the above question happens to be yes - I'd like to ask List Folks how feasible is to assemble and successfully (smoothly?) run 0.5-1 TB database, use Oracle RAC and high performance shared storage (say SAN) served (pumped) by a number of Windows 2K servers? The number of Win2K servers I have on mind is at least 4 beefy (as beefy as it gets in Windows wrld) Win2K boxes each running Win2K AS with lots of RAM and at least 4 CPUs, with perspective of adding more later. Installing and Configuring RAC is as simple as you install Oracle databases. But the scalability is limited to just 4 nodes in Windows. I think this limitation is coming from Windows Clusters and NOT from Oracle. And also for Windows, you can use the OCFS (Oracle Cluster File System) and no need to create the RAW partitions. ( I think now a days all platforms have their own CFS (for Linux and windoze oracle gives the CFS) and no need to use RAW partitions for OPS/RAC). It would be nice (for me) to know if new nodes can be added to the cluster at any later time to improve performance (in order to deal with increase in usage or to accomodate growth over period of time). The nice thing is , yes you can add new nodes dynamically (depends on the OS) and the number of nodes is limited by the OS. For example, in Windoze and Solaris you can only have max 4 nodes. HP-UX, AIX and Linux clusters supports up to 8 nodes. IBM-SP clusters supports upto 128 nodes and again, as I have said earlier all these limitations come from the respective OS/Hardware vendors. From Oracle side , there is no limit for the number of nodes in RAC clusters. It would be very nice to know if number of nodes is or is not limited (otherwise than by raw I/O capabilities of the shared storage). Has anyone seen/run/stumbled over similar beast, if so - does it fly or it stinks? I think the mandatory RAW partition requirement only comes with AIX (or does oracle support GPFS?) and you don't need to worry about RAW partition limitations. Thanks (for any help, hints, links, etc.), Branimir P.S. I've looked at number of metalink articles and found none yet to scratch this specific itch of mine. Oracle RAC on Win2K is for some bizarre reason REQUIREMENT. So I was not the only one in this earth who caught in that trap.. Nice to know somebody else also caught in that. We had implemeted RAC on W2k Advanced Server some time back .. TAF (Select failover) will not work in W2k and make sure TAF is not the MANDATORY requirement. Preconnect is an alternate if your customer agrees for that. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Migrating 8i OPS to RAC 9.2
Hi Vivek, In terms of technical, there is not much difference in normal database upgrade from 8i to 9i and OPS to RAC upgrade. You just need to install the 9i RAC software in the cluster and upgrade the database. But on the other side, Oracle does not see the OPS to RAC transformation as a MIGRATION or UPGRADE. They treat them as two different software components and you need to have a separate licence for RAC, that means you can not upgrade your OPS licence to RAC licence. Other than the installation/upgrade issues, RAC tuning requires deeper understandings of the cache fusion and the GCS,GES internals. For example, the cache fusion will not happen after certain number of lock converts/downgrade-upgrades and it will use DISK to tranfer the blocks between instances. Based on the nautre of the database and the workload, you may want to incrase or decrease the number of times a block can be trasfered over the wire and decide after X number of wire transfers, you can force the disk transfer. But you can still use the GC_FILES_TO_LOCKS parameters in the RAC instances if you know your application very well and I have seen the GC parameters in some of the Oracle TPC benchmarks. And the other interesting thing in RAC is the CR copies are created by the owner, not the requester. But in OPS the CR copy will be created by the requester and the owner has no responsibility other than just downgrading the locks (X to NULL). Like this there are so many small small things are changed in RAC comparing with OPS and some of the basic OPS concepts are no longer valid in RAC (and RAC Tuning). Good luck for your RAC Migration and do let us know if you have faced any of the complexities in the upgrade/migration/or whatever.. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Migrating 8i OPS to RAC 9.2
Mladen: I am not aware any of the documentations which explains this cache fusion to ping pong feature(!). There are 3 components in the typical CR prosessing.. Let us assume there is a resource R which is mastered by the instance A and owned by the instance B on X mode. And also we assume the resource R is requested by Instance C. In this case the requester enquires the status of that resource to the master database and got to know that is owned by instance B. So now it is Instance B's respoisibility to constuct the CR and send it to Instnace C. Here, there is something called light work rule (X$KCLCRST.LIGHT?) which decides the CR construction and block transfer over interconnect (again the CR processing for S to N locks are different based on the setting of _cr_grant_local parameter) or thru the disk transfer. Basically the current holder of the resource maintains a fairness counter, which is incremented every time it sends CR copy over the interconnect to the requester and there is a threshold for the number of CR copies created for that resource. Once the ceiling is hit, instead of creating the CR copies, the LMD simply downconverts the lock to NULL and informs the convertion to Intance A. In simple terms it is like a normal OPS ping. The owner downgrades the lock and the requester reads from the disk after getting approval from the lock master. The threshold is controlled by the parameter _fairness_threshold and IIRC that defaults to 4 or 5. So every 6th (or 5th) CR request will most of the times results in a PING and I have seen good performance improvements in most of the RAC databases by changing this parameter. Best Regards, K Gopalakrishnan (Currently I am in a country (for a week) where I have very limited access to the internet , So I may not be able to reply if you have any more questions And I don't have any oracle database/documentation to test/verify. So please take the advise with a pinch of salt !) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: AQ, RAC and delay
Hemant: You are right. 7 seconds is the MAX difference, but the control files are synced every 3 seconds during the 'split brain' check. So most of the cases, it is 3 seconds with the maximum of 7 seconds subject to the MCPD settings. KG Best Regards,K Gopalakrishnan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Hemant K ChitaleSent: Thursday, June 19, 2003 5:38 AMTo: Multiple recipients of list ORACLE-LSubject: Re: AQ, RAC and delayAlthough MCPD may default to 700centiseconds [although I have seen a much lower value on 9.2 on Tru64],in most cases, delays of 7seconds don't occur [and shouldn't be acceptable ?!].I do have a 9iRAC environment but am comfortably using mcpd=0.And I have AQ in an 8.1.7 single-instance environment.So I can't make any statements about AQ in OPS/RAC !HemantAt 07:05 AM 17-06-03 -0800, you wrote: A colleague casually mentioned that in RAC, the test to de-queue an AQ set-up from BOTH instances didn't succeed. The side where we were enqueuing was able to see queued messages faster than the 'other' side. About 7-8 seconds ... that got me thinking ... is this an effect of MCPD which defaults to like 7 seconds?Do you use AQ in RAC and enqueue/dequeue on both nodes? What is your experience? ps: mcpd - max commit propagation delay TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Hemant K ChitaleOracle 9i Database Administrator Certified ProfessionalMy personal web site is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Single Block Read
Naveen: Table Access by INDEX ROWID Best Regards, K Gopalakrishnan -Original Message- Naveen Nahata Sent: Friday, June 13, 2003 3:21 AM To: Multiple recipients of list ORACLE-L -Original Message- From: K Gopalakrishnan [mailto:[EMAIL PROTECTED] Starting from 8i the db file sequential read is always a single block read which TYPICALLY happens during index scans.. K Gopalakrishnan KG, What are the scenarios other than index scans when a single block read can happen (8i as you said) and hence db file sequential read be reported? Regards Naveen DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file sequential read [WAS:wait event puzzler]
Mladen: Prior to 8i (or 8) you see the multiblock sequential reads while doing the reads from the sort segments or temporary segments. But in 8i sort IO is done as direct IO and we see some other waits. Starting from 8i the db file sequential read is always a single block read which TYPICALLY happens during index scans.. Are you convinced? Best Regards, K Gopalakrishnan --- Gogala, Mladen [EMAIL PROTECTED] wrote: A SINGLE block? I was convinced that it was = DB_FILE_MULTIBLOCK_READ_COUNT blocks. Are you sure? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan -Original Message- Meng, Dennis Sent: Wednesday, June 11, 2003 11:02 AM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC, fail over and timeout .. Big Question
Title: RAC, fail over and timeout .. Big Question Raj: I am afraid, there are not much alternatives you can think of. Even the SELECT fail over will takearoundfew seconds since it has to attach the PGA to the second instance and run the query from where it is failovered, though you can overcome this by enabling pre-connect. Pls let me know if you got any nice ideas ;) Best Regards,K Gopalakrishnan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Jamadagni, RajendraSent: Thursday, June 12, 2003 7:50 AMTo: Multiple recipients of list ORACLE-LSubject: RAC, fail over and timeout .. Big Question Hi all, here is a (hopefully) tricky one ... We have a two node RAC (9202), two applications run on either side, no problems there. We have a business critical process that runs all the time on node T2. This process needs to have a set response time or it affects business. And it is written in VB. Currently we have already handled the situations when a node is down (or machine is down) it fails over to the other side and continues. What we need to cater for is when DB is up, but sick (i.e. not responding). We need to be able to specify a timeout in the queries and when we get no response in the specified time, we need to automatically fail over to the other side. The queries in the application are optimally written, they are sub-second or single digit second queries. So, a query taking a long time would be about 10-15 seconds. While we are fishing for ideas, has anyone implemented anything like this? Any ideas? Thanks in advance Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: Some rollback doubts
Rajesh: I am not sure which document you are referring here. If that documentation says more than one transaction can not use a rollback segment data block means, I would say the documentation is incorrect in this case. But this can not happen concurrently. IT can happen serially. Each rollback segment's header will have something called free extent pool, which will link at least 5 undo segment blocks which have more than 400 bytes of free space. These blocks will be used to store undo information for the subsequent transactions. This 'free extent pool' will be clearly visible in the segment header dumps. You can dump the rollback segment header by using the alter system dump undo header 'undo_segment_name' command. However this free extent pool just keep 5 undo blocks with free space. I hope this answers both of your questions.. Best Regards, K Gopalakrishnan -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 12, 2003 10:00 AM To: Multiple recipients of list ORACLE-L From the docs : Many active transactions can write concurrently to a single rollback segment--even the same extent of a rollback segment; however, each data block in a rollback segment's extent can contain information for only a single transaction. 1) Now, when oracle writes to the last extent in the cycle, and sees that the first extent has an active transaction, it allocates a new extent. Well, why should it? Cant it write to the first extent? 2) When a new transaction needs to use the rollback segment, and say it gets allocated to a rollback segment with a few other transactions, how does it decide which extent to use from the cycle of extents? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: selectivity of predicates with LIKE - diff between 8i and 9i
By any chance are you using CURSOR_SHARING parameter in your 8i version? I think the default selectivity of 5% is used while costing the like operator and with the binds (and with an underscore parameter which I think defaults TRUE) it is treated as equality . If not you can set the underscore parameter _like_with_bind_as_equality to get the index costing. KG Best Regards, K Gopalakrishnan -Original Message- Boris Dali Sent: Wednesday, June 11, 2003 10:21 AM To: Multiple recipients of list ORACLE-L Dear List, Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO? We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS. After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i: [EMAIL PROTECTED] @target COUNT(1) -- 291 [EMAIL PROTECTED] l 1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%' -- 8i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- - 0 3 1 SELECT STATEMENT (choose) Cost (3,1,20) 10 1 SORT (aggregate) 21 3 2 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40) -- 9i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- --- 0 39 1 SELECT STATEMENT (choose) Cost (39,1,19) 10 1 SORT (aggregate) 21 39 8415 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885) -- 8i: Access path: index (index-only) INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE (obj_id=307169 - DIS_IND_ALBUM_TITRE_1) CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06 ... BEST_CST: 3.00 PATH: 4 Degree: 1 -- 9i: Access path: index (index-only) Index: DIS_IND_ALBUM_TITRE_1 TABLE: DIS_TAB_ALBUM_TITRE RSC_CPU: 0 RSC_IO: 39 IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02 ... BEST_CST: 39.00 PATH: 4 Degree: 1 In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below): INDEX#: 307169 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 161254NULLS: 0 DENS: 6.2014e-06 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 - 3 But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39: INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 157906NULLS: 0 DENS: 6.3329e-06 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions: 1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39? 2) Is there a simple way to get it back on track to CST=2 without hints or stored outlines - some spfile parameter would be ideal? 3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291). Would histograms be the right way to get CMPTD CDN closer to the reality in this case? Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M) Thanks for any help, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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
RE: wait event puzzler
Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan -Original Message- Meng, Dennis Sent: Wednesday, June 11, 2003 11:02 AM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
Hi RWB: Check the Note 189702.1 in Metalink. Its official from Oracle. ;) Best Regards, K Gopalakrishnan -Original Message- [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: HP-Sun Cross Platform Migration - Exp/Imp, CTAS over dblink or ..
Sashidar: In THEORY HP datafiles and Sun data files are compaible. That is you can just move the HP data files to the Sun server and recreate the data files. Both data files are in same endianness and they are cross platform compatible. There is no tool to convert a datafile from one OS format to another OS format. Some time back, some one (Guess who??) tried developing a tool and the idea was dropped since Oracle started addressing this issue as a standard feature inside the RDBMS product. So there is no third party tool available in the market to do that conversion and I don't expect any new tools to do that. Having said that, that above said method is NOT officially supported by Oracle in current versions. You have to wait for some more time to get that officially supported by Oracle (clue!!) If you have a faster interconnect CTAS will be much faster than export import method, I remember seeing some comparitive numbers in Metalink.. Have you checked that? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RMAN Catalog: 1 vs. many - Opinions
Gaja: Having dedicated RMAN server is indeed a good idea, but RMAN catalog is not designed for that much (!!?) high concurrency. If you look at the commands (executed by the RMAN during backup) very closely, you will see lots of SELECT for UPDATE and this will cause a big bottleneck in the backup process. It happened at one of our client place where they backup 200+ databases with a single RMAN catalog at 2-3 hrs interval. The workaround suggested was a) Have more RMAN Catalogs b) Run the backup in different times. So again it 'all depends ' Best Regards, K Gopalakrishnan -Original Message- Krishna Vaidyanatha Sent: Friday, April 04, 2003 4:04 PM To: Multiple recipients of list ORACLE-L Paul et al., I agree with Paul's setup of a dedicated RMAN catalog server. It is not a bad idea to have the datafiles for this database on mirrored volumes. I also think a good practice related to saving the catalog against tape media errors is to do a full export on the RMAN Catalog DB and stash the export dumpfile away on a mirrored volume away from the datafiles. If this DB has nothing but the RMAN catalog, it should not be very large (let me know if this is otherwise) and thus the full export will be a good method to have a logical backup. One more thing to save the skin on your back, at a time of need. When Murphy is around I think the word mercy vanishes from the English dictionary. Cheers, Gaja --- [EMAIL PROTECTED] wrote: I have setup a server for just rman catalog backed up to tape with clones offsite for DR. It makes it easier as scripts are parameterized to keep aware of backup statuses = etc. Having this on a centralized management server and can't believe this isn't just common practice. Just make sure no single point of failure. Oracle OCP DBA -Original Message- Sent: Friday, April 04, 2003 3:39 PM To: Multiple recipients of list ORACLE-L Deepak You may also wish to consider not using catalog(s) -- control file backups. This might make it easier to add and remove databases in your environment. I'm presuming most databases are on their own server. If you want to be able to run a single query that will report any backup problems across all your databases, you will use a single catalog. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, April 04, 2003 2:09 PM To: Multiple recipients of list ORACLE-L We are in the process of moving from traditional hot backups to one using RMAN. Total databases are around 200+. Should we be using 1 or few RMAN catalogs, or 1 catalog per database? Thanks, Deepak __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deepak Sharma INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = __ Do you Yahoo!? Yahoo! Tax Center - File online, calculators, forms, and more http://tax.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: Which process is taking up so much CPU???
ALTER SYSTEM SET TIMED_STATISTICS=TRUE; You don't need to bounce the database ;) Best Regards, K Gopalakrishnan -Original Message- WILLIAMS Sent: Friday, March 28, 2003 6:49 AM To: Multiple recipients of list ORACLE-L Fermin Add this line to your init.ora file. timed_statistics = true Then shutdown, startup your Oracle instance. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 28, 2003 6:24 AM To: Multiple recipients of list ORACLE-L Thank you all for your help. I wonder where I should set TIMED STATISTICS = TRUE, if any of you has the time to answer I'd be grateful, but I will look for it in the docs. Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de DENNIS WILLIAMS Enviado el: jueves, 27 de marzo de 2003 18:49 Para: Multiple recipients of list ORACLE-L Asunto: RE: Which process is taking up so much CPU??? Fermin The spid column in the v$process column matches the Unix process i.d. You may need to track it back through the Oracle shadow process. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, March 27, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I usually track our HP-UX 11.0 system with the 'top' command so I can notice when the system is under slow performance. If that happens, I use Toad to look for any active Oracle SQL query which may be heavy enough for degrading the performance. I think my question is simple, but since I am a newbie on this... how can I see who is executing an Oracle SQL that is taking all our CPU provided that I only see his PID with the TOP command? I only see the oracle process, but I don't know how to get the username and the SQL beside him. Thank you for your answers! Fermin. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: log buffer space
Jonathan: I have just sent a mail which has the test statistics. I would appreciate your comments on that.. Alternatively, people who are curious may want to test the log writer writing habits using the event 10046^8. KG Best Regards, K Gopalakrishnan -Original Message- Lewis Sent: Monday, March 17, 2003 7:14 AM To: Multiple recipients of list ORACLE-L I've just tried a different test, along the following lines, which seems to confirm that LGWR is triggered when the buffer is about 1/3 full. Set log_buffer to an easy number such as 600K. Create table with one column of a nice large size, e.g. varchar2(1000); Take snapshot of redo size, redo writes, redo wastage figures from v$sysstat. Insert N rows into table. Taks snapshot and find changes. Vary the number of rows inserted until M rows does not result in a redo write M+1 rows results in a redo write. Check the redo size for M and M+1 rows. Under both 8.1.7.4 and 9.2.0.2, I found that log writer seemed to be consistently triggered at a couple of KB below 1/3 of the log_buffer. (One oddity that caused me a little hassle with 9.2 at first was that I set the log buffer to 512K, but the actual log buffer size (per v$sga) was actually closer to 640K, so the trigger occurred at 212K when I was expecting it to be 170K. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 16 March 2003 13:28 Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: db file scattered read
Mladen, I guess the rdbms kernel will be passing the startblock-stop block addresses and will be passing to the readv (or pread?) system calls. A single multiblock read can not read two different sets (!) of contiguos blocks.. Or I am thinking in the different direction?? Best Regards, K Gopalakrishnan -Original Message- Mladen Sent: Monday, March 17, 2003 7:45 AM To: Multiple recipients of list ORACLE-L How can you tell that DB_FILE_MULTIBLOCK_READ will not span extents? I was unable to confitrm that on Metalink. -Original Message- From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: db file scattered read If 14706+3 is one extent and another extent begins at 14710, it will NOT read 14706+8. A DB_FILE_MULTIBLOCK_READ will not span extents. Hemant At 09:04 AM 14-03-03 -0800, you wrote: Here is a part of trace file . I am finding that oracle is trying to read 8 or 3 or 7 blocks at a time . But block numbers are all sequential i.e. it will read 3 blocks starting from 14706 and then 8 blocks starting from 14710 ( 14706+3 ). Why it doesn't read 8 blcoks always it multi_block_read is set to 8 ? Any Idea . Also what is ela=1 ,does it mean elapsed time is 1 sentisec ? WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14706 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14710 p3=8 WAIT #32: nam='db file scattered read' ela= 2 p1=4 p2=14718 p3=8 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14727 p3=3 WAIT #32: nam='db file scattered read' ela= 0 p1=4 p2=14731 p3=7 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14739 p3=3 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=14744 p3=8 WAIT #32: nam='db file scattered read' ela= 1 p1=4 p2=129784 p3=8 Thanks -ak Hemant K Chitale My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: log buffer space
Arup: Sorry for the delay ;-) I have not seen this is documented anywhere, other than 'Oracle Performance Tuning' OReilly Peter Mark Gurry (page 304) where he claims the log writer writes when it is 2/3 full... Here is the Original Text. QUOTE Log Buffer The log buffer contains the information showing the changes that have been made to database buffer blocks. When the log buffer reaches one-third full (two-thirds full in Oracle 7.3), a user performs a commit, or a write takes place to the database,.. /UNQUOTE I don't have any Oracle 7.3 database, (for that matter no database now as I composing this in Zurich Airport waiting for a connecting flight to Bombay..), So I may not be able to test that. But last time I verified was on an Oracle 8.1 database where the log file writes used to be in the order up to 2/3 full. You can do a simple test to prove this point. You can use oradebug to trace the log writer process and do a CTAS of any big table (with a big log buffer) and you will be able to see the writes and number of blocks written in a single write. I am surprised , this is not documented anywhere in the Oracle Documentation or any of the Oracle University course notes. Best Regards, K Gopalakrishnan -Original Message- Sent: Friday, March 14, 2003 11:04 AM To: Multiple recipients of list ORACLE-L Dennis, The 1MB condition was in 8i as well, at least in 8.1.7, as I mentioned in my original post. I was always under impresssion that the flush is triggered by the buffer being 1/3rd full; but KG mentioned it was 2/3rd, not 1/3rd and I was wondering where he got that information from and if it's documented. It true, that will certainly invalidate most of the what the fine manuals and Oracle Support analysts have said. Any ideas, anybody? Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 11:18 AM Add one more condition: New in Oracle 9i, it will write when 1 meg is reached, so the 1/3 criteria is never reached if you use a big buffer. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 14, 2003 8:59 AM To: Multiple recipients of list ORACLE-L KG, Thanks for the response. Just for my curiosity, is it documented somewhere that the log buffer if flushed when 2/3rd full? From the Manual /server.920/a96524/c09procs.htm#3158 (please check the URL in OTN, the first part is not complete), here is an excerpt MANUAL LGWR writes one contiguous portion of the buffer to disk. LGWR writes: a.. A commit record when a user process commits a transaction b.. Redo log buffers a.. Every three seconds b.. When the redo log buffer is one-third full c.. When a DBWn process writes modified buffers to disk, if necessary /MANUAL At least this was the behavior I tested in 8.1.7; although I have not tested in 9i. This has also been stated by several Oracle Support Analysts. Thanks. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 14, 2003 7:32 AM Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: log buffer space
Arup: NO FLAMES The second condition is not quite true. It is 2/3 full in the current versions. /NO FLAMES It is very easy to test with the event 10046^8. KG --- Arup Nanda [EMAIL PROTECTED] wrote: AK, If the log buffer is at least 4MB, then increasing it will not help, rather it may hurt. The log buffer is flushed when any of the the follwoing occur (i) 1 MB is filled up (2) 1/3rd is filled up (3) every 3 seconds (4) when a checkpoint occurs (5) when a commit occurs. Therefore, see if any of these could be the problem. It's easy to check #s 4 and 3. As Kirti suggested, the problem could be due to the redo logs being on a busy disk, or even a slow one. HTH. Arup - Original Message - From: Deshpande, Kirti To: Multiple recipients of list ORACLE-L Sent: Thursday, March 13, 2003 8:13 PM Subject: RE: log buffer space Increasing log_buffer size is an option, if it is really small. I would also check if the redo logs are on a busy disk. If so, try moving those (or other busy data files on the same disk) to other not-so-busy disks. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: fgac tracing
Murali: It is event 10730. You can set that at session level. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oracle Tools
Sulatan, Have a look at xb.com. There is something called PocketDBA. KG --- Sultan [EMAIL PROTECTED] wrote: Hi gurus, Is there any Oracle tools available to use it in Handheld Computers.(like PDA or IPAQ) Thks in advance = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 9iR2 Oracle SGA Memory
Robert: There is no single LRU (if you want to call LRU lists) in Oracle9i. There are 7-8 replacement lists in 9i and they are like LRU, LRUW, CKPT Q lists (like thread checkpoint, file checkpoint and recovery checkpoint lists), LRU list for OPS/RAC ping blocks, and some other lists. And also each list will internally have 2 lists (MAIX and AUX). THe dirty, replaceable buffers will be linked to the AUX lists and DBWR will start writing from the AUX lists when the MAIN lists are in normal operation. With these numerours lists there are some priorities assigned to each of the lists during writes and buffers from LRU Ping lists will have the highest write priority and normal aging buffers will have the lowest priority. So coming back to your original question, The LRU list is still there, but at a little bit more detailed level. KG --- Freeman Robert - IL [EMAIL PROTECTED] wrote: Ok... with the changes to the touch point memory management methodology in 9i, I found myself wondering, Does Oracle still scan the equivalent of the LRU list to find free blocks, and if so is it still called the LRU list, or has the name of this list been changed along with the architecture changes or has the architecture for assigning free memory changed completely? Anyone want to share some knowledge? RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: File Table Overflow on Oracle DB Server
Vivek: I remember you getting the same problem some time back. I guess you need to increase the nfiles kernel paramter (it defaults to maxuser*constant or something similar to that) Just bump the max users or change the nfile parameter. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Top 10 DBA Do's and Don'ts anyone - Here is my list, comments
MccDBA: It is just Robert's Don't list ;) but you can always give your opinion abt that. Would you mind telling us 'Why you don't agree on them?' KG --- dist cash [EMAIL PROTECTED] wrote: I don't agree with don't #1 and #5. From: Stephane Faroult [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Top 10 DBA Do's and Don'ts anyone - Here is my list, comments Date: Mon, 24 Feb 2003 00:23:37 -0800 Here is the list of top 10 do's and don't that I came up with. #1 - Do Maintain your Expertise #2 - Do Use the DBMS_STATS Package to Collect Statistics #3 - Do Use Bind Variables #4 - Do Put your Production Database in ARCHIVELOG Mode #5 - Do Use Locally Managed Tablespaces #6 - Do Monitor Your Database #7 - Do Practice Recoveries #8 - Do Get Involved with User Groups and Other Resources #9 - Do Establish Standards and Change Control Processes #10 - Do Think Ahead Bonus! - Do tune to Reduce Logical IO's Not Physical IO's. (With regards to Cary!) Oracle Database Top 10 Don'ts #1 - Don't Waste Time Re-Organizing Your Databases #2 - Don't Use .Log or Other Common Extensions For Your Database File Names #3 - Don't Leave Your Database Open To Attack #4 - Don't Decide Against Hot Backups #5 - Don't Use ASSM #6 - Don't Forget the 80/20 Rule #7 - Don't Stack Views #8 - Don't Be a Normalization Bigot #9 - Don't Forget to Document Everything #10 - Do Not Use Products You are Not Licensed For. Bonus!! - Do Not Assume A Good or Bad Hit Ratio Means Anything Ok, anyone wanna comment? Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! Robert, DO #3 and DON'T #7 are developer stuff, not DBA stuff ... I would gladly replace DO #3 by 'Relentlessly preach good practice to developers'. I can hardly talk to a developer without mentioning DBMA_APPLICATION_INFO in the first 30 seconds :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dist cash INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: corrupted block
Hi, If you can afford to forget the data in the corrupted block you can use the event 10231 to skip the corrupted block during table scan. Set the event and you can do a CTAS with a new table name and then you can rename that as original table after dropping the original table. Here is the syntax: alter session set events '10231 trace name context forever, level 10' If you want to see the contents of that skipped blocks, you can use the event 10232 which dumps the contents of that blocks to the trace files. And if you are comfortable in reading block dumps, you can write a simple INSERT statement to put those rows in to the new table. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Oh Where Oh Where Is My Redo Coming From
Hi, The simpler approach is to check the user level redo (or session level redo) using the v$sysstat,sesstat views and you can find the programmes associated with those huge (!) redo. Dumping the redologs and analyzing is just complex when you have a simple solution ;) = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automatic Segment Space Management
Kirti: I have not complely following this thread. But I am sure it is worth mentioning even if someone already mentioned also. In ASSM there would be two highwatermarks called low high water mark and high high water mark. The high high water mark is the actual high water mark (like in the Freelist Managed Segments) and the low high water mark is the new one which is introduced in ASSM. i.e till low HWM all blocks are completely used and from LHWM to HWHM there could be some blocks unused. During sequential scan it has to read till HHWM (i.e it should scan empty block also). But I think by scanning the L2 bitmaps the process can find the unused blocks and skip that during sequential scanning, though I have not tested it thoroughly. Best Regards, K Gopalakrishnan Bangalore, INDIA (Now in Austria) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automatic Segment Space Management
of this underscore parameter. In my 9.2 database it's 6, just as yours. I did my test using upto three concurrent tranactions; guess I'll need to test with 7 or more. However, even if 7 concurrent transactions update the block's rows, and the limit is 6, then the waits should be based in ITL (Interested Trasnaction List) Waits, not BBW. since this is not due to a session not being able to get a particular buffer to the SGA, rather the lack of resources to get the CR copy of a buffer. Has anyone done this test? I'll certainly take it up later to build up on my upcoming article on ITL Waits. Regards, Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, February 19, 2003 2:45 PM Arup, Just picking up the thread on the BBWs. (Btw, I asked this question in this list - never got an answer!) The following undocumented parameter limits the numbe of CR copies in the Block buffers. Name Value - -- Description -- -- --- _db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba What if there are more than 6 concurrent update requests for the same block. Would that not result in BBW? John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those === message truncated === = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Automatic Segment Space Management
Kirti: There are some (or more) errors in my previous post. The process can not find the partly filled/free blocks until it scans the L1 bitmaps (I said L2 bitmap in the previous post as I was in half-sleep while composting the message) and the L2 bitmaps just point the scanning process to the respective L1 bitmaps. And the other overhead in ASSM is, there are chances you will waste around 1-5% (depending on the block size,etc) blocks for just keeping the meta data and this could be a overhead along with the space waster in between the Low HWN and high HWM. Best Regards K Gopalakrishnan --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Kirti: I have not complely following this thread. But I am sure it is worth mentioning even if someone already mentioned also. In ASSM there would be two highwatermarks called low high water mark and high high water mark. The high high water mark is the actual high water mark (like in the Freelist Managed Segments) and the low high water mark is the new one which is introduced in ASSM. i.e till low HWM all blocks are completely used and from LHWM to HWHM there could be some blocks unused. During sequential scan it has to read till HHWM (i.e it should scan empty block also). But I think by scanning the L2 bitmaps the process can find the unused blocks and skip that during sequential scanning, though I have not tested it thoroughly. Best Regards, K Gopalakrishnan Bangalore, INDIA (Now in Austria) --- Deshpande, Kirti [EMAIL PROTECTED] wrote: In a very limited tests that I performed with ASSM (quite some time ago), I found that it tends to use a bit more space than non-ASSM. Something to keep in mind when FTS is used to access tables. Not sure if this changed in 9.2.0.2.x. - Kirti = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC recomended books
Vladimir: I have not seen any GOOD book for RAC (for that matter even OPS). But the Oracle documentation is very good at least in that front. There are some other books coming (or already published???) for RAC, check amazon.com for the details. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 10046 trace - weird library misses
Hi,
It is just telling that you are doing HARD PARSE.
Hard Parse is indicated in the trace file as
'misses in library cache';
Best Regards,
K Gopalakrishnan
-Original Message-
Gorbounov,Vadim
Sent: Thursday, February 13, 2003 6:49 AM
To: Multiple recipients of list ORACLE-L
Dear friends,
I traced one of our test cases and found something weird.
Did anybody else observe this?
Env:
server - 9.0.1.4, Solaris.
client - weblogic 7, uses original oracle thin 9.0.1 jdbc driver to connect.
In fact, I can reproduce all this from SQLPlus
Here is an excerpt from tkprof below - why every parse is a hard parse?
Looks like the problem doesn't appear when 10046 is not set, and it appers
ONLY on pl/sql blocks returning data to client, normal selects OK. Looks
like bug again. Any workaround?
And what are these Misses in library cache during execute?
9.2.0.2 on Linux works fine, i.e. no misses once it has been parsed.
BEGIN :1 := FN_GET_STATUS_ID(:2,:3); END;
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse 40 0.07 0.08 0 0 0
0
Execute 80 0.62 1.55 64 1492 0
80
Fetch0 0.00 0.00 0 0 0
0
--- -- -- -- -- --
--
total 120 0.69 1.63 64 1492 0
80
Misses in library cache during parse: 40
Misses in library cache during execute: 40
Optimizer goal: CHOOSE
Parsing user id: 40
This select
select LOADED_VERSIONS, EXECUTIONS, LOADS,PARSE_CALLS, parsing_user_id
from v$sql
where sql_text like 'BEGIN :1 := FN_GET_STATUS_ID(:2,:3); END;';
gives out whole bunch of these record groups
LOADED_VERSIONS EXECUTIONS LOADS PARSE_CALLS PARSING_USER_ID
--- -- -- --- ---
1 1 1 1 40
1 1 1 0 40
repeated N times
Thank you for you time
Vadim G
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gorbounov,Vadim
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: K Gopalakrishnan
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Deep OPS tuning info
Rui, It is very difficult to identify the problem by just looking the contention for dlm resource hash list. What are the current values of lm_% parameters. Basically that latch protects the resources in LM resource structures in the lock manager resource table. And I would compare the values from the V$resource_limit to the lm_% parameters to get an idea about the resource utilization. BTW Do you see Dynamic resources/locks allocated message in the alert logs? Best Regards, K Gopalakrishnan -Original Message- MARREIROS,RUI (HP-Portugal,ex1) Sent: Thursday, February 13, 2003 8:25 AM To: Multiple recipients of list ORACLE-L Hi all: I'm tuning an OPS env. and the things are getting quite deep, DLM latch issues and so on (e.g. lots of waits on dlm resource hash list). So I was wondering if any of you know of any site or doc. which could help me on this (OPS tuning specific and very deep information). Of course the first steps in the process was to submit the statspack report to oraperf and so on , but right now we are dealing with some issues that surpass the obvious things. So if any you could provide any tip or site to look for a tip I´ll be very glad. thanks in advance Rui Galamba Marreiros Solution Consultant - HP Services, Consulting Integration Quinta da Fonte , Edificio D. Sancho I, 2780 Porto Salvo, Portugal Telf: +351 214828500 Fax: +351 21 4838431 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MARREIROS,RUI (HP-Portugal,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to dump UGA ??
Jonathan: How about calling the DBMS_SYSTEM.READ_EV procedure? I think this is simpler than going to the UGA dumps.. THe restriction with DBMS_SYSTEM is, you can not get the event settings of the other sessions. But Dumps can. Best Regards, K Gopalakrishnan -Original Message- Lewis Sent: Wednesday, February 05, 2003 10:10 AM To: Multiple recipients of list ORACLE-L If you want to see if tracing is on: alter session set events 'immediate trace name events level 1'; (level 2 for process level events, level 4 for system). You can obviously use the oradebug equivalent, or the dbms_support package to fire this at another session. However - if you can read the session's trace file to find out the result of this command, then you will be able to see from the content of the trace file that it is (or isn't) tracing anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 04 February 2003 20:10 Thanks Jonathan, I was trying to find where (and how) does it show that sql trace (or event 10046) is enabled in the session. Any pointers?? TIA Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Tuesday, February 04, 2003 2:14 PM To: Multiple recipients of list ORACLE-L If you use oradebug you can do: oradebug dump heapdump 4 or oradebug dump heapdump 4100 These options dump the UGA heap. The first is structure only, the second includes content and could get quite large. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: index hint ignored?
Title: Message
Hi,
THe
syntax is incorrect. Incorrect HINTs are treated as
comments.
replace the comma with blank space and your hint will
work
as
expected.
Best Regards,K Gopalakrishnan
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Cunningham,
GeraldSent: Friday, January 31, 2003 10:31 AMTo:
Multiple recipients of list ORACLE-LSubject: index hint
ignored?
Hi
there.
I have a
non-unique index on a table, and I'm trying to force Oracle to usethe
index - but it always does a FTS. Why? (I've tried it with and
withoutthe alias)
SQL
set autotrace traceonlySQL SELECT /*+ INDEX(A,vehicle_veh_year_indx)
*/ DISTINCT veh_year 2 FROM TIREADVISOR.vehicle A
3 ORDER BY veh_year DESC;
20 rows
selected.
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118
Card=20 Bytes=80)
1
0 SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)
2 1 SORT (UNIQUE) (Cost=67 Card=20
Bytes=80) 3
2 TABLE ACCESS (FULL) OF 'VEHICLE'
(Cost=16 Card=19607 Bytes=78428)
===
select TABLE_NAME, INDEX_NAME,
COLUMN_NAME, COLUMN_POSITIONfrom dba_ind_columnswhere INDEX_OWNER =
'TIREADVISOR'and TABLE_NAME = ('VEHICLE') order by
1,2,4,3TABLE_NAME
INDEX_NAME--
--COLUMN_NAME
COLUMN_POSITION
---VEHICLE
VEHICLE_PKVEH_ID
1
VEHICLE
VEHICLE_VEH_YEAR_INDXVEH_YEAR
1
Thanks for any
help!
-
Jerry
RE: Global Stats
Kirti: I think the interval is changed to 5 minutes from 3 hours starting from 9i (rel2?). Best Regards, K Gopalakrishnan -Original Message- Kirti Sent: Wednesday, January 29, 2003 8:19 PM To: Multiple recipients of list ORACLE-L Lisa, Monitoring, by itself, does not fire any automatic analyze. It simply montiors the DML activity on the monitored table and counts inserts/deletes/upates. Those counts may not be 100% accurate, but are very close. These can be viewed in dba_tab_modifications, and are dumped there by SMON every 3 hours or so (in 9i there is a new procedure, flush_database_monitoring_info, to flush these counts to this view on demand). These counts do not affect the ones maintained in *_tables views. Monitoring is basically there to help identify which tables may need statistics computed again. 'Gather stale' option will only analyze tables that have undergone DML activity (inserts/deletes/updates) that amounts to more than 10% of the number of rows (from previous analyze) in the table. And 'gather auto' option 'figures' out what tables to analyze, but you must execute dbms_stats. So, there is nothing automatic in gathering table stats. You can test it yourself. remember there is a last_analyzed column ;) HTH, - Kirti -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED]] Sent: Wed 1/29/2003 9:09 AM To: Multiple recipients of list ORACLE-L Cc: Subject:RE: Global Stats Hi Jared, Actually I think monitoring won't work in my case. Data loads fire throughout the day and the docs say that in 8i, analyze can fire based upon table monitoring sometime within 3 hours after data changes. I would rather include a manual fire of analyze in my data load and avoid any locking issues or contention for resources. In addition, if temp space is blown during auto-analyze (fired based upon monitoring), would I know about it? Just my thoughts. Am I wrong? Lisa -Original Message- Sent: Wednesday, January 29, 2003 3:55 AM To: Multiple recipients of list ORACLE-L You may want to read up on table monitoring. Jared On Tuesday 28 January 2003 11:10, Koivu, Lisa wrote: Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Global Stats
Kirti: Sorry for the typo. It is 15 minutes. --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Kirti: I think the interval is changed to 5 minutes from 3 hours starting from 9i (rel2?). Best Regards, K Gopalakrishnan -Original Message- Kirti Sent: Wednesday, January 29, 2003 8:19 PM To: Multiple recipients of list ORACLE-L Lisa, Monitoring, by itself, does not fire any automatic analyze. It simply montiors the DML activity on the monitored table and counts inserts/deletes/upates. Those counts may not be 100% accurate, but are very close. These can be viewed in dba_tab_modifications, and are dumped there by SMON every 3 hours or so (in 9i there is a new procedure, flush_database_monitoring_info, to flush these counts to this view on demand). These counts do not affect the ones maintained in *_tables views. Monitoring is basically there to help identify which tables may need statistics computed again. 'Gather stale' option will only analyze tables that have undergone DML activity (inserts/deletes/updates) that amounts to more than 10% of the number of rows (from previous analyze) in the table. And 'gather auto' option 'figures' out what tables to analyze, but you must execute dbms_stats. So, there is nothing automatic in gathering table stats. You can test it yourself. remember there is a last_analyzed column ;) HTH, - Kirti -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED]] Sent: Wed 1/29/2003 9:09 AM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Global Stats Hi Jared, Actually I think monitoring won't work in my case. Data loads fire throughout the day and the docs say that in 8i, analyze can fire based upon table monitoring sometime within 3 hours after data changes. I would rather include a manual fire of analyze in my data load and avoid any locking issues or contention for resources. In addition, if temp space is blown during auto-analyze (fired based upon monitoring), would I know about it? Just my thoughts. Am I wrong? Lisa -Original Message- Sent: Wednesday, January 29, 2003 3:55 AM To: Multiple recipients of list ORACLE-L You may want to read up on table monitoring. Jared On Tuesday 28 January 2003 11:10, Koivu, Lisa wrote: Hi everyone, Back to the lovely world of Oracle :) I've been reading up on statistics. Out of the 8.1.7 doco: /* Partitioned schema objects may contain multiple sets of statistics. They can have statistics which refer to the entire schema object as a whole (global statistics), they can have statistics which refer to an individual partition, and they can have statistics which refer to an individual subpartition of a composite partitioned object. Unless the query predicate narrows the query to a single partition, the optimizer uses the global statistics. Because most queries are not likely to be this restrictive, it is most important to have accurate global statistics. Intuitively, it may seem that generating global statistics from partition-level statistics should be straightforward; however, this is only true for some of the statistics. For example, it is very difficult to figure out the number of distinct values for a column from the number of distinct values found in each partition because of the possible overlap in values. Therefore, actually gathering global statistics with the DBMS_STATS package is highly recommended, rather than calculating them with the ANALYZE statement */ The table I need to generate stats for is currently 32GB and grows by ~2GB per week. Even the smallest estimate with calculating global stats will take a long long time and I may not be able to spring for all the required temp space. How does the list feel about global stats? Does anyone agree with the documentation that they most important? I'm thinking my partitioned statistics are the most important. Any input is appreciated. Thanks Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day
RE: Global Stats
Hi Kirti: THe algorithm is slightly differnt in 9i. In 8i SMON queries the dictionary (dba_tab_modifications) in once in 3 hours to get the stale stats, and then the stats are flushed to the dictionary from the SGA and the dictionary is updated . The operation is reverse in 9i. THe stats are written to dictionary from SGA once in 15 minutes and then the tables are examined to get the stale stats. Metalink note 102334.1 has some details.. Best Regards, K Gopalakrishnan Bangalore, INDIA --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Gopal, I should have waited a bit longer.. It was about 12 minutes, when I replied... Okay, I will test it out tomorrow.. It's getting late :( Now, go eat your lunch.. it's about lunch time for you... :) Regards, - Kirti -Original Message- From: K Gopalakrishnan [mailto:[EMAIL PROTECTED]] Sent: Wed 1/29/2003 10:58 PM To: Multiple recipients of list ORACLE-L Cc: Subject: RE: Global Stats Kirti: Sorry for the typo. It is 15 minutes. --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Kirti: I think the interval is changed to 5 minutes from 3 hours starting from 9i (rel2?). Best Regards, K Gopalakrishnan = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: v$Views and d$Tables
For V$Views definitions have a look at catalog.sql, which has some information. I understand by D$tables, you mean the dictionary tables. If that is the case, the sql.bsq (under the $ORACLE_HOME/rdbms/admin) will be of use. KG = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Caught YAPPing
Rajesh: You can schedule statspack snaps at 30 minutes interval and then compare the response times at each interval.. But the question is 'what do you mean by average response time?' KG Best Regards, K Gopalakrishnan -Original Message- [EMAIL PROTECTED] Sent: Thursday, January 16, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Hello Everyone, Is there anything, called an Average Response Time for an Oracle database? I got a request from one of the application owners as under : Snip If I can get a log of AVERAGE response time for 1/2 hour periods starting at 9am and running thru 5 pm I would be in heaven. If I cannot get this, what would be of similar help. Here is an example of what I would like to see, say, for today: Time Average Response time 9-9:30 2.3 sec 9:30-10 3.0 sec 10-10:303.3 sec 10:30-113.3 sec 11-11:304.5 sec and so on (one report for each day) When we get to 10 seconds we have trouble. If we have spikes we can try to figure out why. ALso, the cost of the above would be a factor. We have NO money for this. End Snip How can I get this from system or session wait tables or v$sysstat? Any ideas? Regards Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Minimum required init.ora parameters
The only one parameter.. control_files. Rest are optional. Best Regards, K Gopalakrishnan -Original Message- WILLIAMS Sent: Monday, January 13, 2003 7:44 AM To: Multiple recipients of list ORACLE-L Hemant - I was just going from my hastily written class notes. Sorry about misspelling the parameter. However, just tried it and 8.1.6 won't start without the COMPATIBLE parameter set in init.ora. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 8:55 AM To: Multiple recipients of list ORACLE-L compatibility or compatible is not a mandatory init.ora parameter. I would think that only the first three are required. Hemant At 06:08 AM 13-01-03 -0800, you wrote: Nirmal - I believe there are four: db_name control_file db_block_size compatibility This is from John Hibbard, a great Oracle Education instructor. But why not try for yourself? Save off your init.ora, then create a new init.ora with just the above parameters. If Oracle comes up, then remove parameters. If there is another parameter, Oracle will tell you. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 4:59 AM To: Multiple recipients of list ORACLE-L List, I'm interested to know the minimum required parameters to startup the database. Pls anybody list out that? Nirmal., -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Some of you may find this useful
Read Only Dependencies in the KGL. Best Regards, K Gopalakrishnan -Original Message- Mike (NESL-IT) Sent: Friday, January 10, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Ron, that's a bit of a puzzle because it should have been available since 7.3.2. Which user are you using to query it? Also, anyone, I know what x$kglrd does but anyone have any idea what the RD in the table name means? Read? Row? Data? Dependency? I'm open to suggestions. Regards, Mike Hately -Original Message- Sent: 10 January 2003 15:50 To: Multiple recipients of list ORACLE-L Stephane, My creativity has been stumulated, simulated, and mutated. What version of Oracle are you using? x$kglrd ...table or view does no exist on 8.1.7 rel 3 Ron [EMAIL PROTECTED] 01/10/03 05:03AM break on proc column QUERY format A40 word_wrapped select substr(KGLNAOWN || '.' || KGLNACNM, 1, 35) proc, KGLNADNM QUERY from x$kglrd where KGLNAOWN != 'SYS' order by 1, kgldepno / If it doesn't stimulate your creativity I can do nothing for you :-). Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-00600: [2662]
Mogens: Yes. You are right. You have to bump the SCNs globally (i.e. across all data files to higher number, say 1 Billion) using the event 10015 and ADJUST_SCN. Then we can safely open the database and rebuild that. KG Best Regards, K Gopalakrishnan -Original Message- Norgaard Sent: Monday, January 06, 2003 11:59 PM To: Multiple recipients of list ORACLE-L This response from Oracle is not a good one. Kirti is on the right track regarding the SCN stuff as far as I remember. Other than that, I know that Peter Gram and Bjorn Engsig (among a lot of other guys) might be able to suggest something, so I've CC'ed them. I have my own law on ora-600s: If you supply all the required information (tracefiles, alertlogs, repro-case, etc.) chances are Support is not gonna need them. If you miss out just one of those things, chances are Support will tell you they cannot proceed without that vital piece of information. In other words: There's no hope :-))) Could also be related to db-links between v7 and v8... But back to the good questions from Kirti: Have you set the funny _-parameters he mentions? Mogens PS: I do beleive there are ways around this with an alter system or alter session command where you bump the SCN some level, but Peter/Bjorn/someone can confirm this. Sinardy Xing wrote: Hi guys, This is what I get from Oracle == Hi Sinardy, I've check several bug looking for the cause of this problem. However, what I've found is that Development could not diagnosis the problem other than the possibily hat there was some memory corruption that gave a bad SCN. Although, they could sometimes determine the root cause prior to the problem occurring by using certain parameters were set in the database. Those parameters are: a)_db_block_cache_protect. b)_db_block_checking c)DB_BLOCK_CHECKSUM They also recommended running dbverify on all datafiles tomake sure all files are clean on disk. IMPORTANT == These parameter may help later determine what cause the ORA-600 [2662] (and it's not for certain that a cause can be found). They parameters does have some performance overhead, but unfortunately this is the only way that can help in catching and we have to wait until the problem happens again. So, the bottomline is there is no list of reason for why this error occuring other than the error occurs when a data block SCN is ahead of the current SCN. Sorry Sinardy, we don't have more information. Thanks! Wonda = What can I do, help me please... Sinardy -Original Message- Sent: 07 January 2003 13:09 To: Multiple recipients of list ORACLE-L Sinardy ORA-0600 is really an encoded message from Oracle that reads : 'Thank you for helping find yet another bug in our software. You can now release your sphincter (some herbal tea might help), especially if this has cropped up in production. We suggest you do not try to solve this one by yourself, which is why the arguments are supplied. Kindly call OWWS with your CSI number, tell them exactly which version and platform you ran this on, and if we have encountered this previously, there is a small chance that we could have a patch. If not, please do not hold your breath waiting, while we assign this to an experienced software engineer, or perhaps a recent college grad, in which case, you're toast !' However, we will ask you to ftp up to our ftp site loads of dumps and traces which might prove useful. Seriously though, ORA-0600 are mysteries. Your best chance is to get Tim Gorman to look at it, he has an amazing knowledge of these. And of course, Tim should not work for free either. (Tim, it was 10% commission we agreed on, right ? ) :-) Ferenc Mantfeld -Original Message- From: Sinardy Xing [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 07, 2003 2:34 PM To:Multiple recipients of list ORACLE-L Subject: ORA-00600: [2662] Hi all, I hit by this error ORA-00600: internal error code, arguments: [2662], [0], [54151123], [0], [54173017], [16781180], [], [] Can you help me where to find info about this error Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL
Re: ORA-00600: [2662]
Sinardy: Have you force opened the database? (_allow_resetlogs_corruption). In this case the SCN Mismatch (block SCN vs Currnet SCN) is causing the OERI-2662, and you need to bump the SCNS globally to get the database up. Speak to Oracle Support and ask for the details to bump the SCN, then you can open the database. You need to rebuild the database after force opening. KG --- Sinardy Xing [EMAIL PROTECTED] wrote: Hi all, I hit by this error ORA-00600: internal error code, arguments: [2662], [0], [54151123], [0], [54173017], [16781180], [], [] Can you help me where to find info about this error Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-00600: [2662]
Other than the allow_resetlogs_corruption, _disable_logging also will greet you with OERI-2662 if the shutdown is not normal. KG --- Sinardy Xing [EMAIL PROTECTED] wrote: Hi all, I hit by this error ORA-00600: internal error code, arguments: [2662], [0], [54151123], [0], [54173017], [16781180], [], [] Can you help me where to find info about this error Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sinardy Xing INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Physical reads
Pradeep: It is NOT number of read request to the I/O sub-system. It is number of blocks read from the disk. Note that the physical read is not necessarily a physical read. There are chances the block could be from Memory (file system buffer cache). This happens when you have quick IO and/or a large file system buffer cache. So, in general 'physical reads' are the number of blcoks read in to the buffer cache from the disk !! --- [EMAIL PROTECTED] wrote: Hi All in list, I have taken the report.txt using utlbstat and utlestat. Can I assume that the physical reads value as number of physical I/Os to the disk happened? = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache on sysdate? --From 9i performance planning manual
Raj: Both are same. It is internally translated as a select call to dual. KG --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Can someone please explain me why you have to use select sysdate from dual / when my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take an explanation Raj = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cache on sysdate? --From 9i performance planning manual
Raj: SQL trace or the event 10046 is NOT the right to trace the PL/SQL executions. You need to either use the dbms package dbms_trace or event 10938 to populate the SYS table PLSQL_TRACE_EVENTs and select from that table. SQL_TRACE is just SQL Trace. NOT PL/SQL Trace.. Best Regards, K Gopalakrishnan Bangalore, INDIA --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: I thought the very same Tim ... But ... oraclei@elara-ABC1 sys SQL*Plus: Release 9.2.0.2.0 - Production on Thu Dec 26 13:14:58 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected. SQL alter session set sql_trace=true; Session altered. SQL declare 2 a date; 3 begin 4 dbms_output.enable(10); 5 a := sysdate; 6 dbms_output.put_line(to_char(a, 'MM-DD- HH24:MI:SS')); 7 end; 8 / PL/SQL procedure successfully completed. SQL exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production oraclei@elara-ABC1 and /var/opt/oracle/logs/ABC/abc1_ora_9511050.trc Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production ORACLE_HOME = /usr/opt/oracle/current System name:AIX Node name: elara Release:1 Version:5 Machine:0023565A4C00 Instance name: ABC1 Redo thread mounted by this instance: 1 Oracle process number: 30 Unix process pid: 9511050, image: oracle@elara (TNS V1-V3) *** SESSION ID:(36.2802) 2002-12-26 13:15:08.044 APPNAME mod='sqlplus@elara (TNS V1-V3)' mh=0 act='' ah=0 = PARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1016529793013758 hv=3943786303 ad='32ae5788' alter session set sql_trace=true END OF STMT EXEC #1:c=0,e=108,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1016529793011934 *** 2002-12-26 13:15:54.498 = PARSING IN CURSOR #1 len=127 dep=0 uid=0 oct=47 lid=0 tim=1016529838377159 hv=174346551 ad='301694f8' declare a date; begin dbms_output.enable(10); a := sysdate; dbms_output.put_line(to_char(a, 'MM-DD- HH24:MI:SS')); end; END OF STMT PARSE #1:c=0,e=8986,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1016529838377153 EXEC #1:c=0,e=443,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1016529838377721 XCTEND rlbk=0, rd_only=1 ??? Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, December 26, 2002 12:59 PM To: Multiple recipients of list ORACLE-L In PL/SQL, every statement which references SYSDATE or USER or USERENV or similar functions (such as your second suggestion) is automatically translated into an individual SELECT xxx FROM DUAL statement, behind the scenes. You don't have a choice. Call it a weakness of PL/SQL... This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: help me find out why rac instance died
Hi Chao: THe Instance 2 in your Cluster (rac2) was dead during the fast reconfiguration (Check the reason in the alert log file.. which says reason 2). You generally do a reconfig (or fast reconfig) when you add/remove instances from the Cluster setup, which is not (I hope) in your case. THere are some kernel events to trace the reconfigurations, and an underscore parameter (I think it is _imr_active !) to disable the 29740 usually not recommended. For investigation , review the check point, LMON trace files and check the OS log files. Best Regards, K Gopalakrishnan -Original Message- Sent: Saturday, December 21, 2002 4:54 AM To: Multiple recipients of list ORACLE-L Hi, I have a rac 9.2.0.2 Database running on RedHat AS2.1,it has been up and running for about 4 monthes(One month ago, i patched it to 9202). Last night, one instance died unexpectedly,while another instance still running.Though not much business is affected, I want to know why it died, But i am unable to find it out, so looking for your help. Here is some information, I tested the interconnect and the service Network card, both running fine(at 7:00 am), and the disk system is also ok. Alert log file: kjxgrtmc2: Member 1 thread 2 mounted *** 2002-12-21 04:02:05.645 kjxgrgetresults: Detect reconfig from 0, seq 6, reason 2 *** 2002-12-21 04:01:57.014 kjxgrrcfgchk: Initiating reconfig, reason 2 *** 2002-12-21 04:01:57.014 kjxgmrcfg: Reconfiguration started, reason 2 kjxgmcs: Setting state to 6 0. *** 2002-12-21 04:01:57.021 Name Service frozen kjxgmcs: Setting state to 6 1. *** 2002-12-21 04:04:29.911 kjxgrdtrt: Evicted by 0, seq (7, 6) error 29740 detected in background process ORA-29740: evicted by member 0, group incarnation 7 ksuitm: waiting for [5] seconds before killing DIAG -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Single-task message waits
Rick: ANjo Kolk classifies this wait event under one of the IDLE wait events.i.e nothing to worry if you see them in excessive. Best Regards, K Gopalakrishnan -Original Message- Sent: Tuesday, December 03, 2002 3:10 PM To: Multiple recipients of list ORACLE-L Hey all, Does anyone have an idea as to how to tackle single-task message waits on 8.1.7? The most I could find on this is that it may have to do with Context indexes, which are referenced in the massive package I'm helping test with a developer. I'd really like to drop the amount of waits I'm seeing on this because it accounts for ~300-400ms/sec wait according to Spotlight. I had a 10046 trace running too, but I'm having problems setting max_dump_file_size for another process, so I only have 10MB worth of logfile (about 3 minutes of a 30 minute process). But that's another post. :) TIA, Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
