Re: type of join in sql
Hi The choice of of whether to use nested loop, hash join, sort-merge join is driven by the cost (logical ios performed ) and cardinality ( count of rows source for given operation ). Optimizer formulates several plan - before the best plan among them is chosen Approach one can take to benefit in the performance would be 1. If nested loops join - would give better performance for your query then perform thefollowing a. check if the involved tables have indexes b. verify if the indexes, tables involved have the statistics have been calculated frequently c. the ordering of the tables in the 'from clause' should be in such a way that the largest table (more rows) needs to be placed at the end or towards the end in the 'from clause' for eg : select c1, c2 from b, a where a.id = b.id === (a is largest table ) d. Attempt should be made such that cost of sort-merge join would be more expensive than the nested loops join (for eg) - so that the optimizer will choose nested loops 1. if permitted hints (USE_NL ) can be used 2. optimizer_index_cost_adj ( can be used - so that indexes will be used in the plan ) Question : if optimize finds that are more qualifying records in inner table then it will prefer to go for sort merge and will do full scan of inner table , but if it thinks there are less records in inner table it will user nested loop . am I correct ? Answer: Driving table in nested loops join is the one which is used to select each row from ( when an ORDERED HINT is used the 'first table' in the from clause assuming 2 tables is the 'driving' table the other table is called 'inner table ' ) fro eg: select c1, c2 from a, b where a.id = b.id usually the driving table is a and the inner table is b ( you can double verify this in the 10053 output ) Usually the 'inner table' if it has more rows than the 'driving table' === that is when the nested loops is advantageous - that is considered the same by the optimizer as well Thanks, Kavi Grabowy, Chris wrote: bp, Checkout a white paper entitled: A Look Under The Hood of CBO: The 10053 Event. You can find this whitepaper on the hotsos.com website. Your probably going to be looking at the General Plans section of the trace. Goodluck. Chris -- BigP [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/23/2002 06:58 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:type of join in sql can some body shed some light on how does the optimizer decides to choose the kind of join i.e. nested loop, sort merge or hash join . In one of queries if i tweak the join condition it changes the type of join and start using index , otherwise it doesn't . I thought it depends on statistics .. if optimize finds that are more qualifying records in inner table then it will prefer to go for sort merge and will do full scan of inner table , but if it thinks there are less records in inner table it will user nested loop . am I correct ? TIA , bp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
Re: Buffer busy waits are 10.96% of non-idle waits
Hi, Your major problems with buffer busy waits are in the Data blocks class: (1), undo block(2), segment header (3) For Data block Class: Solution: 1. Reduce no of rows by changing pctfree/pctused 2. check when the last time your indexes were re-built and rebuild them often (indexes causing inserts into the same block will be reduced) For undo block class : Solution: 1. Increase the size of the rollbacksegment For segment header : Solution: 1. Add more freelists and or freelist group 2. Check your extent sizes (may be it is too small) - Hope this helps, Thanks, Kavi oraora oraora wrote: Hi Kavitha, querying v$waitstat gives me the o/p below. CLASS COUNT TIME -- -- -- data block 131525173 225446798 sort block 0 0 save undo block 0 0 segment header 4968 16264 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1582 14 undo block 45965 3008 the data block above belongs to a datafile USERS01.DAT which has all the tables and indexes the application uses. the top 25 SQL statements are always SELECT statements. they get executed repeatedly. is it b'coz all SQLs are with literals and no bind variables ? it's a highly read OLTP system. will not -- using bind variables instead of literals -- seperating tables and indexes to diferent tabelspace solve my problem ? Regards, prem. On Fri, 12 Jul 2002 Kavitha Muthukumaren wrote : Hi , TOAD gives this alarm often. what does it mean ? which view will give me the wait statistics ? Answer : == Please run STATSPACK to if this is one of the top waitevents to check if the percentage of wait - can be treated as problematic one SELECT p1 File, p2 Block, p3 Reason FROM v$session_wait WHERE event='buffer busy waits' Repeatedly run the above statement and collect the output. After a period of time sort the results to see which file blocks are showing contention: it occurs when a session cannot access a block because it is in use by another session. The two most common causes are insufficient free lists for a table or insufficient rollback segments. --- IS THIS THE REASON ? Answer: == Yes on top of the wait event could also occur could also occur if a. if the application is going against a set of same blocks (hot blocks) Thanks, Kavi _ There is always a better job for you at Monsterindia.com. Go now http://monsterindia.rediff.com/jobs -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
Re: Buffer busy waits are 10.96% of non-idle waits
Hi Ganesh, First of all the problem here is Buffer busy waits When you have determined the Buffer busy waits is one of the top wait events causing problems then 1. You could query v$waitstat to see the breakdown of waits according to 'class' 2. You could query x$KCBFWAIT to find the hottest file 3. Combine the results and you cuold find out the 'objects' involved 4. In oraora oraora (prem) case Top ' classes' were Data blocks(1), undo header (2) segment header(3) Question : === What does the extent size has to do with segment header contention. There is no segment header contention , this is Buffer Busy waits problem Answer: == Segment header class being one of the top class == to avoid this wait in the future the following needs to be performed a. Adding more free lists (since you did not have enough freelists to keep with the insert) b. Extent sizes of the involved objects were small (tables keeps growing) therefore increasing the extent sixes would also help Question : === And about index rebuilds.. Why do u say that this will cause a Problem for the waits he is Experencing. Data block Class: was one of the top waits Solution: == 1. Technically it is called right-hand indexes because of which above wait of the data block class occurs to avoid this rebuild indexes or choose some other type of indexes like reverse key indexes - Hope it is clear now Thanks, Kavi Ganesh Raja wrote: Fro my clarity .. What does the extent size has to do with segment header contention. And about index rebuilds.. Why do u say that this will cause a Problem for the waits he is Experencing. Hope to get some clarity on this... Thanks Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 == Live to learn... forget... and learn again. == -Original Message- Muthukumaren Sent: Friday, July 12, 2002 6:13 PM To: Multiple recipients of list ORACLE-L Hi, Your major problems with buffer busy waits are in the Data blocks class: (1), undo block(2), segment header (3) For Data block Class: Solution: 1. Reduce no of rows by changing pctfree/pctused 2. check when the last time your indexes were re-built and rebuild them often (indexes causing inserts into the same block will be reduced) For undo block class : Solution: 1. Increase the size of the rollbacksegment For segment header : Solution: 1. Add more freelists and or freelist group 2. Check your extent sizes (may be it is too small) - Hope this helps, Thanks, Kavi oraora oraora wrote: Hi Kavitha, querying v$waitstat gives me the o/p below. CLASS COUNT TIME -- -- -- data block 131525173 225446798 sort block 0 0 save undo block 0 0 segment header 4968 16264 save undo header0 0 free list 0 0 extent map 0 0 bitmap block0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 1582 14 undo block 45965 3008 the data block above belongs to a datafile USERS01.DAT which has all the tables and indexes the application uses. the top 25 SQL statements are always SELECT statements. they get executed repeatedly. is it b'coz all SQLs are with literals and no bind variables ? it's a highly read OLTP system. will not -- using bind variables instead of literals -- seperating tables and indexes to diferent tabelspace solve my problem ? Regards, prem. On Fri, 12 Jul 2002 Kavitha Muthukumaren wrote : Hi , TOAD gives this alarm often. what does it mean ? which view will give me the wait statistics ? Answer : == Please run STATSPACK to if this is one of the top waitevents to check if the percentage of wait - can be treated as problematic one SELECT p1 File, p2 Block, p3 Reason FROM v$session_wait WHERE event='buffer busy waits' Repeatedly run the above statement and collect the output. After a period of time sort the results to see which file blocks are showing contention: it occurs when a session cannot access a block because it is in use by another session. The two most common causes are insufficient free lists for a table or insufficient rollback segments. --- IS THIS THE REASON ? Answer: == Yes on top of the wait event could also occur could also occur if a. if the application is going
Re: connect as internal
Hi Mitchell, what is different between those 3 login 1. sqlplus system login, connect as internal Answer: when you are doing this you are user 'sys' because you have changed from system to being 'internal' connect internal - technically is user 'sys', and is the way of authenticating to the database 2. sqlplus system/manager login Answer: when you are doing this you are user 'system' 3. sqlplus system login , connect system as sysdba Answer: when you are doing this you are user 'system' you have given 'system' sysdba privilege ( which 'system' can perform everything which is allowed for sysdba privilege + on-top of the privileges that it already has) importantly by logging this way 'system' can never be equal to 'connect internal' Thanks, Kavi mitchell wrote: Hi I just view readme about installing Oracle Patch on Oralce 8.1.6 One thing mentioned as Invoke sqlplus, connect as internal and run catalog catproc etc .. I wonder why Connect as internal. what is different between those 3 login 1. sqlplus system login, connect as internal 2. sqlplus system/manager login 3. sqlplus system login , connect system as sysdba Thanks in advance Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
Re: Buffer busy waits are 10.96% of non-idle waits
Hi , TOAD gives this alarm often. what does it mean ? which view will give me the wait statistics ? Answer : == Please run STATSPACK to if this is one of the top waitevents to check if the percentage of wait - can be treated as problematic one SELECT p1 File, p2 Block, p3 Reason FROM v$session_wait WHERE event='buffer busy waits' Repeatedly run the above statement and collect the output. After a period of time sort the results to see which file blocks are showing contention: it occurs when a session cannot access a block because it is in use by another session. The two most common causes are insufficient free lists for a table or insufficient rollback segments. --- IS THIS THE REASON ? Answer: == Yes on top of the wait event could also occur could also occur if a. if the application is going against a set of same blocks (hot blocks) Thanks, Kavi oraora oraora wrote: Guys, Buffer busy waits are 10.96% of non-idle waits TOAD gives this alarm often. what does it mean ? which view will give me the wait statistics ? it occurs when a session cannot access a block because it is in use by another session. The two most common causes are insufficient free lists for a table or insufficient rollback segments. --- IS THIS THE REASON ? what should be the value of this , i mean , the safe limits ? How do i approach and solve this issue ? kindly enlighten me. TIA. _ There is always a better job for you at Monsterindia.com. Go now http://monsterindia.rediff.com/jobs -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
Re: Oracle Performance Tuning steps
Please run STATSPACK - which will give systemwide database performance diagnostics - you could check the hitratios, top wait events and resource consuming queries once report taken - upload it to www.oraperf.com - for suggestions check metalink article NOTE.149121.1 Gathering a StatsPack snapshot - if you can log into metalink Thanks, Kavi Chetan wrote: Thanks , I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. What's the best approch anyways to hunt down the problem in a situation like this ? - Chetan BigP<[EMAIL PROTECTED]>wrote: cheten ,find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics ?-Bigp - Original Message - From: Chetan To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 26, 2002 3:38 AM Subject: Oracle Performance Tuning steps Hi guys , Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap. Here is some information about the db. Database size - 20GB Optimizer - CHOOSE Disk Structure - RAID 1+0 No. of processors - 4 Block Size - 8K Archivelog mode : ARCHIVELOG Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody worked with STATSPACK before ? Can anybody tell me what should accurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries. Also can someone tell me the ideal backup strategy for this database considering the fact that it's a 24x7 system. Thanks in advance . Chetan Chindarkar Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup Do You Yahoo!? Sign-up for Video Highlights of 2002 FIFA World Cup begin:vcard n:;Kavitha x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Kavitha Muthukumaren end:vcard
