RE: Suggestions Needed: Latch free - library cache
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).
RE: what is in the UGA?
Hi Ryan, The words session specific have to do with the difference between a process and a session. Many Oracle environments run with just one user session per process, but in general there can be multiple user sessions being serviced by a single process. The UGA holds persistent data structures that are specific to a particular session (even though other sessions may be connected through the same process). By contrast, the PGA contains persistent data structures that are specific to the process (not general to the instance) but must be visible to all sessions connected via that process, and the CGA holds transient data structures that are only required for the duration of a single call. The UGA consists of a small fixed area containing a few atomic variables, small data structures and pointers. The rest of the UGA is a heap. Most of the UGA heap is for private SQL and PL/SQL areas. So yes, package variables and bind variable are there (although the bind meta-data is in the SGA) but sort areas, row source buffers, and runtime state data are also major space consumers. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- [EMAIL PROTECTED] Sent: Tuesday, 11 November 2003 12:14 AM To: Multiple recipients of list ORACLE-L I cant find any specifics in the docs. I must be missing something. All I see is 'session specific information'? Does this mean package variables? SQLPLUS bind variables? What does this mean? -- 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).
RE: what is in the CGA?
Hi Pawan, CGA = Call Global Area. It contains data structures that can be freed at the end of the (parse, execute, fetch, ...) call. For example, if a sort while executing a select statement exceeds the sort_area_retained_size any additional sort memory required (up to the sort_area_size) will be allocated in the CGA. Once the execute call has finished the entire CGA is freed, and the extra sort memory with it. Physically, CGAs are subheaps of the PGA. The extents are identified as call heap in PGA heap dumps. There can be more than one CGA present in a PGA heap dump if a recursive call was under way when the PGA heap dump was taken. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Satav, Pawan Sent: Tuesday, 11 November 2003 8:55 PM To: Multiple recipients of list ORACLE-L Good info Steve. But what I want to ask is what is a CGA ? Regards Pawan -Original Message- Sent: Tuesday, November 11, 2003 1:24 PM To: Multiple recipients of list ORACLE-L Hi Ryan, The words session specific have to do with the difference between a process and a session. Many Oracle environments run with just one user session per process, but in general there can be multiple user sessions being serviced by a single process. The UGA holds persistent data structures that are specific to a particular session (even though other sessions may be connected through the same process). By contrast, the PGA contains persistent data structures that are specific to the process (not general to the instance) but must be visible to all sessions connected via that process, and the CGA holds transient data structures that are only required for the duration of a single call. The UGA consists of a small fixed area containing a few atomic variables, small data structures and pointers. The rest of the UGA is a heap. Most of the UGA heap is for private SQL and PL/SQL areas. So yes, package variables and bind variable are there (although the bind meta-data is in the SGA) but sort areas, row source buffers, and runtime state data are also major space consumers. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- [EMAIL PROTECTED] Sent: Tuesday, 11 November 2003 12:14 AM To: Multiple recipients of list ORACLE-L I cant find any specifics in the docs. I must be missing something. All I see is 'session specific information'? Does this mean package variables? SQLPLUS bind variables? What does this mean? -- 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).
RE: x$ constructs and memory
Hi Steve, The X$ interfaces do not use memory persistently, and the memory usage of the X$ tables is fixed and necessary to an instance. Thus memory growth is not possible. Memory growth is possible for the segmented arrays, which some of the X$ interfaces expose. However, it is very unusual, because the defaults are rather generous. If you query V$RESOURCE_LIMIT, you will normally see that the MAX_UTILIZATION falls way short of the INITIAL_ALLOCATION. Even if there is significant growth, it is unlikely to chew up more than a few M of shared pool memory, because the structures involved are each very small. (You do however need to worry about similar growth in the instance lock database in a RAC environment). To answer another question raised later in this thread ... the metadata for X$ objects is present in the library cache during a query and may be cached afterwards, but there is no corresponding metadata in the dictionary cache. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Steve Sent: Wednesday, 1 October 2003 12:49 AM To: Multiple recipients of list ORACLE-L Hi Steve and welcome back, Thanks for that detailed answer BUT... A practical question from the original post remains: What happens when these x$constructs begin to consume large amounts of memory? From your explanation I'm assuming that, beyond monitoring the SGA and PGA, memory consumption of individual X$ in-memory data structures is generally not something we need to worry about. How can we determine how much memory they actually consume? Are there any related tunable parameters of which we should be aware? Thanks, Steve Orr -Original Message- Sent: Monday, September 29, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble 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
RE: x$ constructs and memory
Hi Daniel and list, There are two types of X$ row sources. X$ tables export in-memory data structures that are inherently tabular, and X$ interfaces that call functions to return data is non-tabular, or not memory resident. For example, the array of structs in the SGA representing processes is exported as the X$ table X$KSUPR. Not all of the struct members are exported as columns, but all of the rows are exported. There is a freelist, implemented as a header that points to the first free slot in the array, and a member of each struct to point to the next free slot. The 'process allocation' latch protects this freelist. The most obvious example of an X$ interface to return non-tabular data is X$KSMSP, which returns one row for each chunk of memory in the shared pool. (There are similar X$ interfaces for other memory heaps). As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. So what is done is that when the X$ interface is queried these linked lists are navigated (under the protection of the relevant latch if necessary) an a array is built in the CGA (part of the PGA) from which rows are then returned by the row source. An example of an X$ interface that returns data that is not memory resident is X$KCCLE, which returns one row for each log file member entry in the controlfile. In fact, all the X$KCC* interfaces read data directly from the controlfile. Similarly, the X$KTFB* interfaces return LMT extent information - from the bitmap blocks (for free extents) and from the segment header and extent map blocks (for used extents). Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. These correspond to the transactions and enqueue resources arrays respectively. The reason is that they are no longer fixed arrays. Instead they are segmented arrays that can be dynamically extended by adding discontiguous chunks of shared pool memory to the array. The freelists and latching for these arrays in unchanged however. All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Daniel Fink Sent: Tuesday, 30 September 2003 1:10 AM To: Multiple recipients of list ORACLE-L I was sitting on a mountain here in Colorado, pondering Oracle optimization and an interesting scenario crossed my feeble 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).
RE: x$ constructs and memory
Hi Tanel, Answers inline ... As you may know, heaps are implemented as a heap descriptor and linked list of extents, and within each extent there is a linked list of chunks. Is there a linked list for *all* chunks in a heap as well, regardless of their type, or is there only a list for each type of chunks, free and recreatable ones? Am I correct that permanent chunks don't have to be in any list because they're never deallocated and they should stay in same place anyway? There is an invariant chunk header that identifies the chunk class and implements the linked list of all chunks in an extent. Then there is a class specific header that in the case of permanent, free and recreatable chunks has a pointer for another linked list. I'm not sure why the permanent linked list is needed (other than to make heapdumps efficient). The free and recreatable chunks obviously need theirs for the freelists and LRU lists. Some X$ tables have become X$ interfaces in recent versions, for example X$KTCXB and X$KSQRS. [snip] All you will notice is that the ADDR column of the X$ output now returns addresses which map into your PGA rather than the SGA. In fact, that is in general a good way to work out whether you are looking at an X$ table or an X$ interface. I've noticed that some tables such x$ktcxb and x$kturd return the same ADDR value for all it's rows. I've always thought, that it means a subroutine or function is returning the results instead of a direct read from array, as you described. But x$ksqrs does return different ADDRs for each row (9.2.0.4 on W2K). Am I on wrong tracks here? The implementation of these row sources varies somewhat. Some of them, like X$KSMSP, need to buffer their results in the CGA because the structure might change before the next fetch; others like these ones you've mentioned do not need to, but some of them do so anyway. @ 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).
RE: SQL AREA and LIBARARY CACHE size?
Hi Manoj, Although the stats are confused, the SQL area is in fact a subset of the library cache. Cursors are cached in the library cache as namespace 0. You can exercise limited control over the memory usage by marking objects for keeping with DBMS_SHARED_POOL.KEEP. Otherwise, the retention of chunks and thus the relative size of these and other shared pool areas depends entirely how recently objects have been used, because they all share the same LRU mechanisms (although there is an additional subordinate LRU mechanism for the dictionary cache). @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- ManojKr Jha Sent: Thursday, 25 September 2003 6:35 PM To: Multiple recipients of list ORACLE-L How the ORACLE distribute and mange the size of variouse component in shared_pool? There should be some criteria on which it disribute its toatl available memory to these component. Steve Adams/Jonathan, do you have any about these? Also are there any way to control the number and type of chunks in different buckets. If these can be manage I hope that lots of seriouse problem related to shared pool fragmentation and other issues can be easilly avoided? With Regards, Manoj Kumar Jha --- A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil. --- bhabani s pradhan [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] fmail.com cc: Sent by: Subject: Re: SQL AREA and LIBARARY CACHE size? [EMAIL PROTECTED] om 09/25/03 01:04 PM Please respond to ORACLE-L You cannot control the sizes of sql area and library cache individually Regards On Thu, 25 Sep 2003 ManojKr Jha wrote : Hi, Any body have any idea about how to control the size of library cache and sql area in shared_pool? With Regards, Manoj Kumar Jha A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil. DISCLAIMER: The information contained in this message is intended only and solely for the addressed individual or entity indicated in this message and for the exclusive use of the said addressed individual or entity indicated in this message (or responsible for delivery of the message to such person) and may contain legally privileged and confidential information belonging to Tata Consultancy Services. It must not be printed, read, copied, disclosed, forwarded, distributed or used (in whatsoever manner) by any person other than the addressee. Unauthorized use, disclosure or copying is strictly prohibited and may constitute unlawful act and can possibly attract legal action, civil and/or criminal. The contents of this message need not necessarily reflect or endorse the views of Tata Consultancy Services on any subject matter. Any action taken or omitted to be taken based on this message is entirely at your risk and neither the originator of this message nor Tata Consultancy Services takes any responsibility or liability towards the same. Opinions, conclusions and any other information contained in this message that do not relate to the official business of Tata Consultancy Services shall be understood as neither given nor endorsed by Tata Consultancy Services or any affiliate of Tata Consultancy Services. If you have received this message in error, you should destroy this message and may please notify the sender by e-mail. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: ManojKr Jha INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: SQL AREA and LIBARARY CACHE size?
Hi Tanel, There was a note in the bug database back in 7.1 days that suggested that it was hard coded based on the chunk type, and so that's what I wrote in my book. However, I fear that that might have been wrong. At least it is in recent versions. It is not hard to set up a test in which you can see chunks moving from the transient to recurrent list. My best guess at the moment is that when new recreatable chunks are first unpinned, they go onto the transient list, and then when they have been reused, they go back onto the recurrent list. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Tanel Poder Sent: Thursday, 25 September 2003 9:45 PM To: Multiple recipients of list ORACLE-L As I understand SQL area is a subset of library cache which is a subset of shared pool. Taking advantage the opportunity that Steve is here, I'd like to ask how does Oracle distinguish between recurrent and transient chunks? Is this hardcoded, that certain types of chunks go to transient end of LRU list and others go to rcr end or is this based on some kind of touch count as well, like in buffer cache LRU list? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 2:04 PM Hi Steve, Sorry for it, but I want to correct one thing that is , it is Libarray cache which is subset of SQL area which intern subset of shared pool. With Regards, Manoj Kumar Jha -- -- A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil. -- -- Steve Adams [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ra.com.au cc: Sent by:Subject: RE: SQL AREA and LIBARARY CACHE size? [EMAIL PROTECTED] y.com 09/25/03 03:24 PM Please respond to ORACLE-L Hi Manoj, Although the stats are confused, the SQL area is in fact a subset of the library cache. Cursors are cached in the library cache as namespace 0. You can exercise limited control over the memory usage by marking objects for keeping with DBMS_SHARED_POOL.KEEP. Otherwise, the retention of chunks and thus the relative size of these and other shared pool areas depends entirely how recently objects have been used, because they all share the same LRU mechanisms (although there is an additional subordinate LRU mechanism for the dictionary cache). @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- ManojKr Jha Sent: Thursday, 25 September 2003 6:35 PM To: Multiple recipients of list ORACLE-L How the ORACLE distribute and mange the size of variouse component in shared_pool? There should be some criteria on which it disribute its toatl available memory to these component. Steve Adams/Jonathan, do you have any about these? Also are there any way to control the number and type of chunks in different buckets. If these can be manage I hope that lots of seriouse problem related to shared pool fragmentation and other issues can be easilly avoided? With Regards, Manoj Kumar Jha -- -- --- A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil. -- -- --- bhabani s pradhan [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] fmail.com cc: Sent by: Subject: Re: SQL AREA and LIBARARY CACHE size? [EMAIL PROTECTED] om 09/25/03 01:04 PM Please respond to ORACLE-L You cannot control the sizes of sql area and library cache individually Regards On Thu, 25 Sep 2003 ManojKr Jha wrote : Hi, Any body have any idea about how to control the size of library cache and sql area in shared_pool? With Regards, Manoj Kumar Jha [snip] -- Please see
RE: Tuning help required
Hi New DBA, Further to what Stephane has said below, the following two stats in your initial post are interesting ... consistent gets 559985 table fetch continued row 212027 That suggests that there is a fair amount of row chaining or migration in one of the tables. If it's migration, rather than chaining, you'll get a ~37% reduction in logical reads if you fix it. The following script can often be used to distinguish between chaining and migration. It counts the number of rows for which the first column is not able to be returned from the first row piece. Although it is possible for that to be the case with row chaining, it's more likely a symptom of migration. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all accept OwnerName prompt Owner Name: accept TableName prompt Table Name: prompt set termout off delete from chained_rows where owner_name = 'OwnerName' and table_name = 'TableName' / @utlchain column column_name new_value ColumnName select column_name from dba_tab_columns where owner = 'OwnerName' and table_name = 'TableName' and rownum = 1 / set termout on prompt Analyzing table. Please wait ... analyze table OwnerName . TableName list chained rows into chained_rows / select count(*) continued_rows from chained_rows where owner_name = 'OwnerName' and table_name = 'TableName' / prompt Checking continued rows for migration ... set termout off column start_value new_value StartValue select m.value start_value from sys.v_$mystat m, sys.v_$statname n where n.name = 'table fetch continued row' and n.statistic# = m.statistic# / select /*+ ordered */ sum(vsize(t.ColumnName)) from chained_rows c, TableNamet where c.owner_name = 'OwnerName' and c.table_name = 'TableName' and t.rowid = c.head_rowid / set termout on select m.value - StartValue migrated_rows from sys.v_$mystat m, sys.v_$statname n where n.name = 'table fetch continued row' and n.statistic# = m.statistic# / -Original Message- Stephane Faroult Sent: Wednesday, 24 September 2003 6:50 PM To: Multiple recipients of list ORACLE-L Before checking stats, execution plans and the like take a look at your query. I presume that it is generated, otherwise you would probably say that a date belongs to a month by using a BETWEEN the first and the thirty first rather than listing all the 31 days, would you ? Now perhaps the generator could generate a BETWEEN if you are always interested by consecutive days? Note that the GEO table is totally useless in the FROM clause. You return no data from it, and it is not needed to join together two tables you return data from. If you need it to check some data consistency, it should be in a subquery (IN or EXISTS, depending on the volumes of data to process), but best of all the problem should have been tackled at the root with referential integrity constraints. Even if you may have (always those b***y generators) it should be better located in a subquery - by the way, it might help you dispose of the UNIQUE (calling DISTINCT UNIQUE doesn't make it better :-)). The condition of CUR_SYS_NO should be applied to the column from MEPAI, which you will encounter first, rather than the column from CUR, since they are equal. Now you have to decide which of MEPAI or PRODUCTS should be the table you search first. It depends on the selectivity of your data. Be certain that your table and index statistics are up-to-date. If you still feel that Oracle processes it badly, try playing on the optimizer goal (FIRST_ROWS/ALL_ROWS) and if you get a result which satisfies you add it as a hint. SF - --- Original Message --- - From: New DBA [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 23 Sep 2003 23:39:44 Hi All, I need help in tuning the following query. It takes around 6-7 minutes to run. I hope that someone will be able to go through the details and give me a few pointers. I have gathered a few statistics, but don't know where to go from here. Please view the mail in a fixed size font e.g. courier to preserve the formatting. If the lines wrap over copying and pasting in a text editor might help, though I'm not sure. I apologize for the long message in advance. Following is the query: SELECT UNIQUE MEPAI.MPAI_NAV_MOD , MEPAI.MPAI_NAV_MODS, MEPAI.MPAI_SYS_NO, MEPAI.MPAI_PAI_SYS_NO, MEPAI.MPAI_AS_OF_DATE, PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID, CUR.CUR_CURRENCY_NAME, CUR.CUR_CURRENCY_CODE, CUR.CUR_SYS_NO FROM EPR_CURRENCIESCUR, EPR_GEOGRAPHIES GEO, EPR_PRODUCTS PRODUCTS, MOD_EPR_PRICING_ASSET_INFOMEPAI WHERE MEPAI.MPAI_ISS_SYS_NO = PRODUCTS.ISS_SYS_NO AND MEPAI.MPAI_GEO_SYS_NO = GEO.GEO_SYS_NO AND MEPAI.MPAI_CUR_SYS_NO = CUR.CUR_SYS_NO
RE: Virtual Circuit Status
Hi Erik, MTS uses a common queue for incoming requests (calls) to shared servers, and other queues for responses going back via a dispatcher. These queues and their clients constitute the virtual circuit. When a process is ready to take a request or response from its queue, but finds that there is nothing there, then it waits for 'virtual circuit status' to change. So, if you're using MTS and you're not spending a lot of time in this wait event, then that indicates that your requests and responses are being delayed by queue time (which incidentally is not yet picked up by StatsPack, although it is in the V$ views, nor is it visible in the trace files because the waiting session does not have a corresponding process that is waiting). @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Williams Sent: Thursday, 28 August 2003 4:49 AM To: Multiple recipients of list ORACLE-L Has anyone run into excessive waits on this event? After running my statspack through oraperf.com, I see that I am waiting ~80% of response time on this event. I have read on metalink that this is NOT an idle event, and on asktom.oracle.com that it IS an idle event. We have MTS configured, but all the clients are set to use dedicated server. Should I try reduce this event by tuning MTS or is it truely an idle event? Thanks Erik -- 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).
RE: RE: Raw partition Vs File System
Hi Bill, No luck contacting Bert. My guess is that he did not have a very big SGA, so there was plenty of his 2G of RAM available for filesystem caching. For example, if Oracle had 128M for the shared pool and 128M for its buffer cache (as at the end of his previous article in the series), that leaves about 1.7G for filesystem caching. So file system based tests would get the benefit of 1.7G of cache, whereas raw tests would only get the benefit of 128M of cache. To construct a fair test, it would be necessary to give Oracle 1.7G of db_block_buffers. In the article Bert says that he doubled db_block_buffers, but unless going from about 600M to about 1.2G, then it was not a level playing field. Unfortunately, he does not give those numbers. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 20 November 2001 21:07 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Steve, Did you manage to contact Bert? Would you be willing to let us know what you think was wrong with the test anyway? Many thanks - Bill. Hi All, Does anyone have an email address for Bert? I looked for his email address when I first read that article a week ago, but did not find one. I think I know what was wrong with his test, but it is hard to be sure because he left out a lot of the details. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 9 November 2001 21:20 To: Multiple recipients of list ORACLE-L Did anyone read Bert Scalzo's article in Pipeline Newsletter this month (http://www.revealnet.com/newsletter-v2/linux2.html)? He was benchmarking various filesystems under Linux (EXT2, EXT3, Reiser, IBM JFS and RAW). I was suprised (and so was he) to see worst performance on RAW devices. I presume this is a Linux-specific issue: can anyone suggest why RAW is so poor on Linux (or want to contest the results?!) Thanks - Bill. At 01:29 09/11/01 -0800, you wrote: If you're using a volume manager (veritias, or disk suite), then raw is pretty much just as easy as file systems. You could always do this incrementally - for example, high io stuff (typically redo, temp, possibly rollback) on raw, and all the rest of file systems etc. hth connor --- Vasu Ramasamy [EMAIL PROTECTED] wrote: Hello Gurus, I am trying to install Oracle Server in the environment as given below. I am in the processes of laying out Physical Database layout. I would like to know the pros and cons of going with Raw partition. The environment : Solaris 2.6 Oracle 8.1.7 Size of database - 60GB No. of tables - 3000 (approx.) Max size of few tables - 3 GB to 5 GB. Thanks for your help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vasu Ramasamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com
RE: frequent commit, example ??? HELP
Hi Andrea, Don't do it! Adding commits makes the code more complex, much less efficient and risks violating transactional integrity. See http://www.ixora.com.au/newsletter/2001_09.htm#commits for a detailed explanation of the problems. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 16 November 2001 6:00 To: Multiple recipients of list ORACLE-L Hi all, Thank you all those for responding to the frequent commit question. So members suggested using count, and loop. May I have real example. (OK, I'm bad at pl/sql). Site table has 2 million rows, how to so a commit, let's say 5000 rows. Site_id is unique in site table. How does the counter fit in the following update sql? update site a set a.site_code = (select c.area_code from site_location b, area c where a.site_id = b.site_id and c.area_id = b.area_id); I put a counter is a sample code, and update runs 10 times! then commit, then runs another 10 times! then commit I must miss something. Please give me as mush detail as you can. Thank you so much! Andrea __ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Raw partition Vs File System
Hi All, Does anyone have an email address for Bert? I looked for his email address when I first read that article a week ago, but did not find one. I think I know what was wrong with his test, but it is hard to be sure because he left out a lot of the details. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 9 November 2001 21:20 To: Multiple recipients of list ORACLE-L Did anyone read Bert Scalzo's article in Pipeline Newsletter this month (http://www.revealnet.com/newsletter-v2/linux2.html)? He was benchmarking various filesystems under Linux (EXT2, EXT3, Reiser, IBM JFS and RAW). I was suprised (and so was he) to see worst performance on RAW devices. I presume this is a Linux-specific issue: can anyone suggest why RAW is so poor on Linux (or want to contest the results?!) Thanks - Bill. At 01:29 09/11/01 -0800, you wrote: If you're using a volume manager (veritias, or disk suite), then raw is pretty much just as easy as file systems. You could always do this incrementally - for example, high io stuff (typically redo, temp, possibly rollback) on raw, and all the rest of file systems etc. hth connor --- Vasu Ramasamy [EMAIL PROTECTED] wrote: Hello Gurus, I am trying to install Oracle Server in the environment as given below. I am in the processes of laying out Physical Database layout. I would like to know the pros and cons of going with Raw partition. The environment : Solaris 2.6 Oracle 8.1.7 Size of database - 60GB No. of tables - 3000 (approx.) Max size of few tables - 3 GB to 5 GB. Thanks for your help. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vasu Ramasamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Intasys Billing Technologies Ltd. www.intasysbilling.com 74 Commercial Street, Commercial Quay, Leith, Edinburgh EH6 6LX tel (0)131 625 8200 fax (0)131 625 8201 email [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: TTI Layer Function codes
Hi Raj, I would imagine that the details of the implementation of the two-task common protocol and its interface functions are not the sort of documentation that Oracle is going to make available to customers! Other than the obvious step of contacting Support, check the operating system hardware diagnostic logs for evidence of either memory or network errors, and make sure that you don't have someone trying to use some old client software that is not compatible with the RDBMS version. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 2 November 2001 6:46 To: Multiple recipients of list ORACLE-L Hi all, I am investigating some ORA-600 [12333] errors. According to note 35928.1 the second argument represents TTI Layer Function Code, followed by FUNCTION Code and SEQUENCE. I tried to look up TTI Layer Function codes but couldn't find any reference, does anyone know where I can find them? Thanks Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Arch configuration -- I/O stuck
Hi Pablo, I've seen an 'ls' hang for more than a minute under 10.20 when there were a lot of delayed writes pending on an unrelated file-system. A colleague of mine (Chris Bunting) did some testing to reproduce the problem and concluded that all filesystems of the same type (JFS or HFS) were affected. HP made some kernel changes for 11.0 that have reduced the severity of the problem, but it can still occur. If your case the archive writes are not delayed writes because Oracle explicitly opens the files in synchronous mode, so you should not see a delay any longer than that corresponding to the time that it would take your Symmetrix box to destage the cache allocations for the target LUNs, unless there happens to be simultaneous heavy delayed write activity elsewhere on the system. The failure of the multiblock_read_test.sql script probably indicates that the large table that you scanned already had a large number of block in the cache. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 2 November 2001 6:39 To: Steve Adams; Multiple recipients of list ORACLE-L Steve, thanks for the help and for the url and the advice of stripping. I don't understand what I'm pasting here , I'm executing a 'ls' in a FS that's in a different disk in differents LUNs (on the same Symmetrix), why is it still stucking. Shouldn't it be placed in a different queue?? The 'ls' is probably getting stuck because the I/O is very slow and file system metadata writes are stuck in the I/O queue while locks are held on the file system metadata pending the completion of those writes. One more question, besides what you just advised me, I've been trying to reduce ARCH bandwidth (as I read in a TIP at your site), to spread ARCH work along more time and reduce the impact in foreground processes. So I've set log_archive_buffers from 4 to 2 and today I've tried to set log_archive_buffer_size to the MAX_IO_SIZE of the OS. But I found a problem with this. I tried to check what was the MAX_IO_SIZE, so I used 10046 event and check at scattered reads in a big FTS (as you do in your scripts) and I always got p3=5. I checked this into 2 differents databases running on the same box. Both reported p3=5 (5 blocks I think), but the surprise is that one of them has got db_block_size=4K and the other db_block_size=8K. How can it be possible? according to this test MAX_IO_SIZE could be 20K or 40K. what's wrong here? And something worst, MAX_IO_SIZE can't be so small, right? I thought it was 1MB or 512K in HP-UX 11.0 thannks for your time. TIA --- Steve Adams [EMAIL PROTECTED] escribió: Hi Pablo, The 'ls' is probably getting stuck because the I/O is very slow and file system metadata writes are stuck in the I/O queue while locks are held on the file system metadata pending the completion of those writes. The problem could be that you are saturating the cache allocations for the EMC LUNs containing your archive destination file system. See the answer at http://www.ixora.com.au/q+a/0010/20102738.htm for a bit about the EMC cache allocation policy. To solve the problem you can use LVM to stripe a large number of small LUNs together so as to increase the total amount of cache available for the archival writes. You would also do well to avoid RAID-S of course! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- From: Pablo ksksksk [mailto:[EMAIL PROTECTED]] Sent: Thursday, 1 November 2001 5:45 To: Multiple recipients of list ORACLE-L Subject: Arch configuration -- I/O stuck Hi list, Oracle 7.3.4 HP-UX log_archive_buffer_size=32 (redo log blocks = 1K) log_archive_buffers=4 Filesystem based (no direct I/O) I've been detecting that my box gets stucked eventually for some time. When this happens I can't do even a ls (it actually executes it but it takes a long time). If I check my cpu with TOP, I see 47% idle time and there's no process monopolizing the CPU. But when I check disk activity with sar -d I see that one disk is 100% busy and it's avwait+avserv 1000 ms. The other disks are fine. I then check disk activity with Glance and I can identify the process that's writting/reading on this disk is: ARCH (ARCH is writting a 1.9 GB redo log.) So here are my doubts: 1)If only one disk is saturated (I've got about 30 disks in this box (a SYMMETRIX array) with some controllers), why does the whole box get stucked? why are even other applications connected to other instances running on this box affected? (may be because the HP-UX LVM system gets saturated???) 2) What can I do to avoid this problem?, (reduce log_archive_buffers parameter may be, or increase log_archive_buffer_size) help me on this Thanks
RE: Arch configuration -- I/O stuck
Hi Pablo, The 'ls' is probably getting stuck because the I/O is very slow and file system metadata writes are stuck in the I/O queue while locks are held on the file system metadata pending the completion of those writes. The problem could be that you are saturating the cache allocations for the EMC LUNs containing your archive destination file system. See the answer at http://www.ixora.com.au/q+a/0010/20102738.htm for a bit about the EMC cache allocation policy. To solve the problem you can use LVM to stripe a large number of small LUNs together so as to increase the total amount of cache available for the archival writes. You would also do well to avoid RAID-S of course! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Thursday, 1 November 2001 5:45 To: Multiple recipients of list ORACLE-L Hi list, Oracle 7.3.4 HP-UX log_archive_buffer_size=32 (redo log blocks = 1K) log_archive_buffers=4 Filesystem based (no direct I/O) I've been detecting that my box gets stucked eventually for some time. When this happens I can't do even a ls (it actually executes it but it takes a long time). If I check my cpu with TOP, I see 47% idle time and there's no process monopolizing the CPU. But when I check disk activity with sar -d I see that one disk is 100% busy and it's avwait+avserv 1000 ms. The other disks are fine. I then check disk activity with Glance and I can identify the process that's writting/reading on this disk is: ARCH (ARCH is writting a 1.9 GB redo log.) So here are my doubts: 1)If only one disk is saturated (I've got about 30 disks in this box (a SYMMETRIX array) with some controllers), why does the whole box get stucked? why are even other applications connected to other instances running on this box affected? (may be because the HP-UX LVM system gets saturated???) 2) What can I do to avoid this problem?, (reduce log_archive_buffers parameter may be, or increase log_archive_buffer_size) help me on this Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Synonyms can be VERY bad for performance
Hi John, If the CBO needs to access a table that has no statistics, it does not actually estimate the statistics. It just gets the high water mark by reading the segment header block and uses its default assumption of an average row length of 100 bytes to estimate the cardinality of the table. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.christianity.net.au/ - For all -Original Message- Sent: Thursday, 1 November 2001 10:10 To: Multiple recipients of list ORACLE-L Greg, I may be way off here but FIRST_ROWS will not only force the CBO to be used on SYS objects, it will *estimate* statistics on all related objects. This will result in large elapsed times and I/O during the parse phase (for the ESTIMATE part) as well as the incorrect path and resultant extra I/O during the execute phase. By extension, if you see large values in the parse phase of a tkprof output, it may indicate that stats are being estimated and thus some objects have not been analyzed. (Can someone validate this? Hint, hint: Steve :) John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Listen to great commercial-free christian music 24x7 at www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: A. Bardeen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 3:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: Synonyms can be VERY bad for performance Greg, Hey! I resemble that remark ;) The data dictionary views are optimized to use the RBO or are heavily hinted to force a specific access path. ALL_ROWS and FIRST_ROWS force the CBO to be used so a different access path may be taken resulting in poor performance. Obligatory notes: Note: 35272.1 Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)? Note: 35934.1 TECH: Cost Based Optimizer - Common Misconceptions and Issues Note: 66484.1 Which Optimizer is used HTH, -- Anita --- Greg Moore [EMAIL PROTECTED] wrote: Because we were in first_rows, queries against the data dictionary were optimized in first_rows mode rather than rule. This was despite us not having any statistics on system or sys objects. Is this standard behavior? What about the warnings not to analyze SYS because it's optimized for Rule? Is it true that simply invoking first_rows means that's out the window and you get the CBO used on SYS anyway, even if there are no statistics? I get the feeling we're going to see an email from Anita saying this is bug number XXX, fixed in patch YYY, and we can read all about it in Note ZZZ. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL
RE: How can I tell if a procedure/package is running?
Hi Rahul, Ah, I see. You're looking at KGLHDLMD instead of KGLHDPMD. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 17:18 To: Rahul; Multiple recipients of list ORACLE-L; 'Steve Adams' ADDR INDX INST_ID KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ - - -- --- 2025F078 102 1 315F16D4 315F16D4SELECT COUNT(*) FROM LETTER WHERE TELEPHONENUMBER = :b1 AND CUSTOMERID = :b2 AND ENDDATE = :b3 2025F074 108 1 30A2E4F8 30A2E4F8 NC_SASSGIVE_ASCII_VALUE there are queries and also functions with the X$KGLOB.KGLHDPMD as 1, the result from select * from X$KGLOB where KGLHDLMD =1 are too long to cut-paste here in full. -- From: Steve Adams[SMTP:[EMAIL PROTECTED]] Sent: Tuesday, October 30, 2001 2:45 PM To: Rahul; Multiple recipients of list ORACLE-L Subject: RE: How can I tell if a procedure/package is running? Hi Rahul, 0 means not pinned; 3 means pinned in exclusive mode. I don't know what 1 means. I'm not accustomed to seeing it. Do you have any examples? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- From: Rahul [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 30 October 2001 16:00 To: Multiple recipients of list ORACLE-L Subject: RE: How can I tell if a procedure/package is running? out of curiosity... what does the values 0 and 1 implies ? ( in X$KGLOB.KGLHDPMD) -- From: Steve Adams[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, October 30, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Subject:RE: How can I tell if a procedure/package is running? Hi Doug, You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- How can I tell if a stored procedure or package is in the middle of execution? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How can I tell if a procedure/package is running?
Hi Nirmal, There is some information about the X$ tables available on the Ixora web site. However, knowledge of the X$ tables is not really important for most DBAs. They are only useful in rare, advanced tuning and diagnostic situations. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 19:25 To: Multiple recipients of list ORACLE-L Hi adams What should i get details of X$ tables. How much these tables are important as a DBA.. Rgds, Nirmal, -Original Message- Sent: Tuesday, October 30, 2001 9:45 AM To: Multiple recipients of list ORACLE-L Hi Rahul, 0 means not pinned; 3 means pinned in exclusive mode. I don't know what 1 means. I'm not accustomed to seeing it. Do you have any examples? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 16:00 To: Multiple recipients of list ORACLE-L out of curiosity... what does the values 0 and 1 implies ? ( in X$KGLOB.KGLHDPMD) -- From: Steve Adams[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, October 30, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: How can I tell if a procedure/package is running? Hi Doug, You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- How can I tell if a stored procedure or package is in the middle of execution? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Identifying user and locked table row in Oracle 8i
Hi Tamas, When a session is waiting for a row-level lock you can see the row required in the V$SESSION columns ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW#. A script like Oracle's utllockt.sql can be used to identify the blocker. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Wednesday, 31 October 2001 2:21 To: Multiple recipients of list ORACLE-L Hi, I am facing a locking problem with Forms 6i and Oracle 8iR3 that did not occure with Oracle 8iR2. To pinpoint the problem, I would need to identify the locking user AND the specific record that causes the problem. Does some of you possibly have a script that I can let our customer run when this deadlock situation occures? TIA, Tamas Szecsy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy Tamas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: extent deallocation question
Hi Jeremiah, The cross instance call is a reuse block range call, not a checkpoint object call. Thus a separate scan of the cache is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that the blocks might immediately be allocated to another object. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Monday, 29 October 2001 13:55 To: Multiple recipients of list ORACLE-L Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so, why? Using a normal application, I can delete and insert tens of thousands of rows in just a few seconds. What takes Oracle so long with UET$ and FET$? BTW, this is not a problem that I personally have - it is a purely academic question. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: extent deallocation question
Hi Riyaj, The checkpoint object call is used prior to a direct read of an object. DBW0 scans the cache once based on the obj#. This was introduced in 8.0 as an optimization for parallel query. Previously each extent was checkpointed separately. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 3:10 To: Multiple recipients of list ORACLE-L Steve I thought the drop/truncate of the objects will do 'checkpoint object ' call and DBW in turn will do extent based checkpointing. I am not even going to try argue with you :-) Could you please explain to us : What are the reasons for 'checkpoint object call ' and ' reuse block range calls ? What statement triggers them ? Thanks in advance, Riyaj Re-yas Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Steve Adams [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/29/01 05:00 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: extent deallocation question Hi Jeremiah, The cross instance call is a reuse block range call, not a checkpoint object call. Thus a separate scan of the cache is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that the blocks might immediately be allocated to another object. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Monday, 29 October 2001 13:55 To: Multiple recipients of list ORACLE-L Thanks for the reply. This is the first time I have heard of an object-level checkpoint. I still don't understand a couple things about this. If the wait is for checkpointing of the blocks composing the deallocated extents, why does a 2Gb segment with 10 extents drop so much faster than a 2Gb segment with 20,000 extents? Does the session performing the DDL require the object checkpoint to complete for every extent deallocated, before the DDL proceeds? Why should such a checkpoint be necessary? Why can't the blocks just go on the LRUW list like all the other dirty blocks? If the speed of these object checkpoints is limited by the checkpoint batch portion of the DBWR write batch, can they be sped up by increasing the value of _db_block_checkpoint_batch to a larger proportion of the DBWR write batch (as ascertained from x$kvii)? It seems like if they just put the blocks from deallocated extents on the dirty list, then the checkpointing could be automatically made more agressive through use of the db_block_max_dirty_target feature. That way as soon as a lot of dirty blocks got put on the LRUW list, the checkpoint portion of the write batch could be automatically increased. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote: When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client. Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers. If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits. Jeremiah Wilton [EMAIL PROTECTED] Using dictionary-managed tablespaces, it can sometimes take a very long time to drop or truncate a segment with many tens of thousands of extents, because Oracle takes a long time to update UET$ and FET$. This can be a serious problem for some people because the session performing the DDL holds the ST enqueue for the duration of the extent deallocation. During these long extent deallocations, I observe the session performing DDL waiting on IPC with the database writer. Can anyone tell me why these long extent allocations spend the vast majority of their time waiting on DBW0? Does every block in UET$ and FET$ have to get written out before the deallocate can proceed? If so
RE: How can I tell if a procedure/package is running?
Hi Doug, You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- How can I tell if a stored procedure or package is in the middle of execution? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How can I tell if a procedure/package is running?
Hi Doug, You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- How can I tell if a stored procedure or package is in the middle of execution? - You can view this message online at http://www.ixora.com.au/q+a/0110/30141015.htm To unsubscribe from Ixora Answers send a blank email to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How can I tell if a procedure/package is running?
Hi Rahul, 0 means not pinned; 3 means pinned in exclusive mode. I don't know what 1 means. I'm not accustomed to seeing it. Do you have any examples? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Tuesday, 30 October 2001 16:00 To: Multiple recipients of list ORACLE-L out of curiosity... what does the values 0 and 1 implies ? ( in X$KGLOB.KGLHDPMD) -- From: Steve Adams[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, October 30, 2001 10:45 AM To: Multiple recipients of list ORACLE-L Subject: RE: How can I tell if a procedure/package is running? Hi Doug, You can look at the mode in which the stored procedure or package is pinned in the library cache. This information is in X$KGLOB.KGLHDPMD. A value of 2 indicates that the object is pinned in shared mode by one or more sessions. If necessary, you can join to X$KGLPN to find the sessions holding the pins. See executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm#executing_packages for an example. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- How can I tell if a stored procedure or package is in the middle of execution? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB FILE PARALLEL WRITE
Hi Jack, It is a background wait, so unless user processes are waiting for DBWn in 'free buffer waits' or 'write complete waits' then you don't have a problem, no matter how big the 'db file parallel wait' numbers are. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Wednesday, 24 October 2001 19:15 To: Multiple recipients of list ORACLE-L Hi, I have this DB FILE PARALLEL WRITE as the top event (right after SQL*NET,rdbms.,pmon smon) and according to the documentation this has to do with my DBWR but no mention is done on how to solve this. Is this purely another indication of poor disk performance or should I be looking somewhere else? TIA jack -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: os block size versus oracle bock size
Hi All, Oracle uses direct I/O on W2K so the O/S block size is an irrelevance. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- hi all we have an oracle block size of 8k and i believe our W2K server has a default os block size of 4k. Is this a problem with the performance ? thanks g.g. kor rdw ict groningen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Bind variables v. cursor_sharing = FORCE
Hi Greg and list, I'm not convinced of John's conclusion that 'cursor_sharing' = FORCE outperforms bind variables. Some months ago I tried to reproduce John's results and was not able to, despite trying several different variations on the tests. Tom Kyte claims that 'cursor_sharing' is about 10% more expensive. My results were between 10% and 30%. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Saturday, 20 October 2001 7:21 To: Multiple recipients of list ORACLE-L An excerpt from PIRANHAS IN THE POOL, SQL PERFORMANCE KILLERS Investigating the effects of literal SQL on Oracle performance John Beresniewicz Precise Software Solutio -- Effect of CURSOR_SHARING A primary purpose of the 8.1.6 testing was to assess the impact the new CURSOR_SHARING system parameter and its potential for helping DBAs manage ill-behaved applications with high parse rates of literal SQL. Comparing Test 1 to Test 3 and also Test 0 to Test 4 serves this goal. Comparing these Test results, CURSOR_SHARING exhibits advantages similar to those obtained using bind variables in Test 2: -- Reduced library cache impact. -- Negligible shared pool activity. -- Reduced CPU demands. In fact, Test 3 produced the best elapsed time of all tests in spite of the fact that all the SQL was literal. Thus, it is clear that CURSOR_SHARING can be used to greatly enhance performance of applications that produce high volume literal SQL and thus is a great advantage for the DBA saddled with such applications. CURSOR_SHARING vs. Bind Variables Comparing Test 2 and Test 3 reveals that CURSOR_SHARING = FORCE showed significantly better performance than bind variables in both elapsed time and reduced library cache latching impact. This surprising result deserves further investigation to produce an adequate explanation. Library cache latch impact was significantly reduced as well as shared pool pins and releases. Parsing CPU time increased some but overall CPU was reduced. Perhaps the additional parsing involved in forced cursor sharing also enables increased sharing of shared pool memory heaps. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: event= to capture deadlock debug info
Hi All, I think Jim is using the term deadlock loosely. He appears to be speaking of a hang. The normal recommendation for diagnosing the cause of a hang is to take two 'systemstate' dumps from different sessions in quick succession. However, from 8.1.6 there is a 'hanganalyze' dump that can also be used, and is somewhat more useful. Be warned, however, that I've seen it crash an instance once. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Saturday, 20 October 2001 4:03 To: Multiple recipients of list ORACLE-L Hi James, setting an event at init/session level would basically dump the error state into an trc file. ora-60 or deadlock by defualt writes a message in alert file and also dumps a large trace in udump. so why do you want to explicitly set this event? The default behaviour mimicks the same anyways. hth Deepak --- James Howerton [EMAIL PROTECTED] wrote: DBA's Does anyone know of an event= to set to capture a deadlock condition? On two occasions now a user's session has locked up due to a network problem or three finger salute or who knows what(???) blocking other users from adding records to the system. I have to get it fixed quickly to get them working again so I don't have time to study everything in the locked state before killing the offending users session. It has only happened twice, once at night and once on a holliday. I don't have all of my toys at home to get a quick look. TIA ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem - V$BUFFER_POOL_STATISTICS
Hi Saurabh, Try running $ORACLE_HOME/rdbms/admin/catperf.sql first. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Saturday, 20 October 2001 21:05 To: Multiple recipients of list ORACLE-L Hi all, Is anyone using Oracle Statspack for performance monitoring. I want to use it, i ran the required scripts for creating the PERFSTAT schema, tables, synonyms and STATSPACK package. but the package creation is giving problem with Dictionary table V$BUFFER_POOL_STATISTICS( as it is not found in the database dictionary) The schema script created the stat$buffer_pool_statistics table to use data from above dict table but package could not be compiled successfully as dictionary table is not found. can anybody explain me why it happened. I'm using Oracle 8.1.5 on NT. thanks in adv Saurabh Sharma Mail to : [EMAIL PROTECTED] : [EMAIL PROTECTED] Contact : saurabh00pc @ MSN / Yahoo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA 235
Hi Sinardy, It probably means that a log switch was in progress at the same time as the query. However, the error can be simulated by setting event 10327. So if someone's been setting events and has messed up, that could also explain it. You can use that event for testing if you want to write robust code that handles the error. A good example of such code can be seen in '$ORACLE_HOME/rdbms/admin/recover.bsq'. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Can someone tell me what is the cause of this error FROM sys.v_$loghist lh1, sys.v_$loghist lh2 * ERROR at line 6: ORA-00235: controlfile fixed table inconsistent due to concurrent update -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: distribution of the sleeps on the library cache latches
Hi Ed, I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use of literal SQL. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 18:02 To: [EMAIL PROTECTED] Cc: Steve Adams Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed Hi Ed, My scripts use the rule of thumb you mention, but it is not a black and white issue. I would characterise your contention here as having a few hot spots, but a general library cache wide problem as well. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 18 October 2001 9:25 To: Multiple recipients of list ORACLE-L Hi List, what is the criteria of uneven distribution of sleeps on the library cache latches? Is there a rule of thumb to determine uneven distribution? For example, no of sleeps on a latch is twice bigger than average no of the sleeps on the others latches? Is it correct? Do you estimate the following distribution as uneven? NAME GETS MISSES SLEEPS SLEEP1 SLEEP2 SLEEP3 -- -- -- -- -- -- -- library cache 806881977 103462783105912 3358661020725 217664 library cache 464142903 39375581318015 154644 422509 94864 library cache 283177601 19916481127057 120761 368308 80551 library cache 839438890 79674971478426 195907 479182 95918 library cache 978851575 131045961614737 213383 527238 104408 library cache 279613950 1453222 759127 77395 255984 51334 library cache 834477709 116230003101181 4051021058753 168282 library cache 260953580 1434471 825151 93505 278275 52608 library cache 470252271 52629331484982 162567 489911 103336 library cache 501042073 51344671595443 180043 507939 119648 library cache 1265644171 250131692374937 371608 754426 152126 TIA, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA 235
Hi Sinardy, Sure, the following should illustrate how you might use it for testing ... SQL alter session set events '10327 trace name context forever'; Session altered. SQL select count(*) from v$loghist; select count(*) from v$loghist * ERROR at line 1: ORA-00235: controlfile fixed table inconsistent due to concurrent update SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 19:20 To: Multiple recipients of list ORACLE-L Hi Steve, Can you please give some hint, what is even 10327 ? Regards, Sinardy -Original Message- Sent: Friday, 19 October 2001 3:38 PM To: Sinard Xing; Multiple recipients of list ORACLE-L Hi Sinardy, It probably means that a log switch was in progress at the same time as the query. However, the error can be simulated by setting event 10327. So if someone's been setting events and has messed up, that could also explain it. You can use that event for testing if you want to write robust code that handles the error. A good example of such code can be seen in '$ORACLE_HOME/rdbms/admin/recover.bsq'. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Can someone tell me what is the cause of this error FROM sys.v_$loghist lh1, sys.v_$loghist lh2 * ERROR at line 6: ORA-00235: controlfile fixed table inconsistent due to concurrent update -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: distribution of the sleeps on the library cache latches
Hi Ed, Of course, I'd suggest that the application be enhanced to use bind variables appropriately! ;-) In the interim, I would introduce a script such as 'keeper.sql' from the Ixora web site to keep all the reusable material in the library cache so as to reduce the impact of the flushes. Once that is working as desired, I would increase the flush frequency to an interval of say 1 hour or 30 minutes. The size of the library cache and thus shared pool utilization will still grow over time, but more slowly. I would then reduce the shared pool size to approximately the size that it grew to after 1 day of normal application usage. To then mitigate the risk of ORA-4031 errors I would ensure that 'shared_pool_reserved_size' is allowed to default, but set '_shared_pool_reserved_min_alloc' to its minimum value (which is 4000 or 5000, version dependent). An instance restart once a week would be good too if you can manage that. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 19:09 To: Steve Adams; [EMAIL PROTECTED] Hi Steve, yes, you're absolutely right. I've inhereted that system. The shared_pool_size = 750M. I believe it's HUGE and oversized. The application code is mostly based on literal SQL. The miss rate on the shared pool is normally about 15%-20% with periodical peaks up to 50%. But the previous DBA insist that we shouldn't decrease the size of shared pool as the miss rate will be much higher. He also setup periodical flushing every 3 hours (I assume he did it in order to prevent ORA-4031). If I undestand the things right, deacresing of shared_pool_size will decrease load on shared pool latch _but_ contention on the library latches will be higher because of higher parse rate. Is it correct and what's your advice in my case? Thanks in advance, Ed Hi Ed, I would agree with the _kgl_latch_count change, but the _kgl_bucket_count change seems unwarranted and extreme. Rather I suspect that the size of your library cache hash table rather reflects an oversized shared pool, probably with some use of literal SQL. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- From: Edward Shevtsov [mailto:[EMAIL PROTECTED]] Sent: Friday, 19 October 2001 18:02 To: [EMAIL PROTECTED] Cc: Steve Adams Subject: Re: distribution of the sleeps on the library cache latches Hi Steve, thanks for your reply. I'm thinking about twice increasing number of library latches ( _kgl_latch_count = 23 ) in order to mitigate loading on them. Also I would like to set _kgl_bucket_count = 8 according to output of your script. Do you think it's a good idea in my case. NAME IMPACT SLEEP_RATEHOLDING LEVEL# - -- -- -- -- library cache 60333579.3 0.32% 1729452385 shared pool19313269.2 1.40% 8265405 7 cache buffers chains1950080.11 0.00% 629411 1 row cache objects 738401.912 0.04%3369329 4 session allocation 70758.0784 0.01% 144008 5 cache buffer handles56104. 0.01% 71913 3 redo allocation33494.1227 0.02% 215582 6 cache buffers lru chain 12784.3859 0.00%198869 3 checkpoint queue latch10980.4325 0.00% 52259 7 latch wait list 9976.33016 0.04% 24412 9 redo writing 4846.5256 0.01% 75484 5 Regards, Ed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA 235
Hi Sinardy, Further to that, if you've looked at 'recover.bsq' you'll have seen that it attempts up to 5 retries in the event of ORA-235 errors. If you want to write similarly robust code and use event 10327 to test it, then you might want to use the seldom used LIFETIME qualifier of the event syntax instead of the FOREVER qualifier that I suggested before. For example, you could simulate getting the error twice and then succeeding on the third attempt using the qualifier LIFETIME 2. There is also an AFTER n TIMES qualifier available that can be used to delay the raising of the error. The thing to watch here is that queries against the controlfile based V$ views may need to read more than one block from the controlfile. So although a query against V$THREAD can get away with just one controlfile read, my full scan of V$LOGHIST actually did 19 controlfile reads, so I would need to specify AFTER 19 TIMES to get the query to succeed once before failing. The AFTER n TIMES qualifier can be combined with the LIFETIME qualifier using a comma to get both effects as follows. SQL alter session set events '10327 trace name context after 19 times, lifetime 2'; Session altered. SQL select count(*) from v$loghist; COUNT(*) -- 823 SQL / select count(*) from v$loghist * ERROR at line 1: ORA-00235: controlfile fixed table inconsistent due to concurrent update SQL / select count(*) from v$loghist * ERROR at line 1: ORA-00235: controlfile fixed table inconsistent due to concurrent update SQL / COUNT(*) -- 823 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 19:05 To: Sinard Xing; Multiple recipients of list ORACLE-L Hi Sinardy, Sure, the following should illustrate how you might use it for testing ... SQL alter session set events '10327 trace name context forever'; Session altered. SQL select count(*) from v$loghist; select count(*) from v$loghist * ERROR at line 1: ORA-00235: controlfile fixed table inconsistent due to concurrent update SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Sent: Friday, 19 October 2001 19:20 To: Multiple recipients of list ORACLE-L Hi Steve, Can you please give some hint, what is event 10327 ? Regards, Sinardy -Original Message- Sent: Friday, 19 October 2001 3:38 PM To: Sinard Xing; Multiple recipients of list ORACLE-L Hi Sinardy, It probably means that a log switch was in progress at the same time as the query. However, the error can be simulated by setting event 10327. So if someone's been setting events and has messed up, that could also explain it. You can use that event for testing if you want to write robust code that handles the error. A good example of such code can be seen in '$ORACLE_HOME/rdbms/admin/recover.bsq'. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ - For DBAs @ http://www.secularislam.org/call.htm - For Muslims @ http://www.christianity.net.au/ - For all -Original Message- Can someone tell me what is the cause of this error FROM sys.v_$loghist lh1, sys.v_$loghist lh2 * ERROR at line 6: ORA-00235: controlfile fixed table inconsistent due to concurrent update -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question About Deferred Rollback Segments
Hi there, You can see deferred rollback segments in X$KTTVS or in DBA_SEGMENTS. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 18 October 2001 16:55 To: Multiple recipients of list ORACLE-L Hi, DBAs: From Oracle Online Manual: When a tablespace goes offline so that transactions cannot be rolled back immediately, Oracle writes to a deferred rollback segment. The deferred rollback segment contains the rollback entries that could not be applied to the tablespace, so that they can be applied when the tablespace comes back online. These segments disappear as soon as the tablespace is brought back online and recovered. Oracle automatically creates deferred rollback segments in the SYSTEM tablespace. How can I find out the deferred rollback segments, which dictionary view needed to be queried? I execute a query, insert some datas into the table, and not commited it, then I offline the tablespace with immediate option. Then I issued rollback, it rolled back immediate although the tablespace is offline. I know it has been rolled back with Diferred Rollback Segments, But where can I find it? Thank you for your help. WinterSun Zhao -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WHERE CURRENT OF Question
Hi Bill, The FOR UPDATE clause syntax allows for the possibility of column-level locking, but Oracle only implements row-level locking. So the OF column_name phrase is just ignored. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 6 October 2001 2:40 To: Multiple recipients of list ORACLE-L Why does the following work? I open a cursor with FOR UPDATE OF COLUMN_A and then do an update of COLUMNB, WHERE CURRENT OF the cursor. Surely I shouldn't be allowed to do that? (Ora 8.1.7.2) Any insight appreciated! Thanks - Bill. SQLWKS create table test_table 2 ( 3 column_anumber, 4 column_bnumber 5 ) 6 Statement processed. SQLWKS insert into test_table values (1,2) 2 1 row processed. SQLWKS insert into test_table values (2,3) 2 1 row processed. SQLWKS insert into test_table values (3,4) 2 1 row processed. SQLWKS declare 2 cursor c1 is select * from test_table for update of column_a; 3 begin 4 for i in c1 loop 5 update test_table set column_b = column_b * 2 6 where current of c1; 7 end loop; 8 end; 9 Statement processed. SQLWKS select * 2 from test_table 3 COLUMN_A COLUMN_B -- -- 1 4 2 6 3 8 3 rows selected. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: session_cached_cursors parameter
Hi Jonathan, It actually caches the whole instantiation object, not the just the library cache lock (which serves as the pointer to the shared cursor handle in the SGA). That means that it not only saves locating and locking the library cache object again, but it also saves extra instantiation calls as well. Also because the KGL lock is retained, there is no possibility of the shared cursor being aged out and thus not available on a subsequent session cursor cache hit. The memory cost can be non-trivial. I think it is about 240 bytes per populated slot per session. Although you are right that it is searched linearly, my experience suggests that the CPU saving of session cursor cache hits can exceed the CPU cost of unlatched linear searches with values much larger than 100. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 6 October 2001 4:57 To: Multiple recipients of list ORACLE-L Since no-one's answered this, I'll propose my theory, although I'm not sure it's right. The session_cached_cursors is just an array in the UGA (session memory), which means it will be stored in the SGA when using the multi-threaded server. The function of the cache is to maintain pointers into the shared pool so that on reuse of a closed cursor, Oracle has the option of finding the cursor very rapidly if it is still available, rather than going through all the processing (and particularly latching) needed to find if the 'new' SQL is sharable. I believe the cost is actually a very small increment in memory, plus a CPU cost for scanning the array, which means that an array size over about 100 may put you in the position of losing more CPU than you would otherwise save. Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 03 October 2001 22:03 Does anyboby know how this parameter really works? Does it use PGA memory to cache parsed statements or it uses SHARED_POOL memory for that. If the case is the first one, imagine that cursor1 is flushed out from the Shared Pool, and the session A has it cached (in its PGA I assume), then, in order to use it again, what must the session A do? (it has to place the cursor into the shared pool again and do a soft parse or that would not be necessary...??) thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: cached_blocks.sql
Hi Christopher, Silvina and list,
The 8.1 version of cached_blocks.sql script on the Ixora web site does not have the
incorrect +1, but it did once for a
period of a week or so. The 8.0 version had it until a few minutes ago. I got some
strange results once and concluded
incorrectly that the file numbers in X$BH were offset thus, as those in X$KCBFWAIT
are. But when I started using the
corrected script I got even more strange results and backed out the fix, but it
seems that I overlooked the 8.0
version. Sorry.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Saturday, 8 September 2001 2:27
To: Multiple recipients of list ORACLE-L
That looks like Steve Adam's script, he is pretty busy now it seems.
I guess his is right, but I haven't looked at what your talking about to
closely.
I have one on my site that does something similar.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Friday, September 07, 2001 11:27 AM
To: Multiple recipients of list ORACLE-L
I get the cache_blocks.sql from this site. While I was executing this
scripts I found a mistake.
select
e.owner||'.'||e.segment_name segment_name,
sum(cur) cur_buffers,
sum(cr) cr_buffers
from
( select
min(file#||'.'||dbablk) fb,
sum(decode(state, 1, 1, 0)) cur,
sum(decode(state, 3, 1, 0)) cr
from
sys.x_$bh
where
inst_id = userenv('Instance') and
state in (1, 3)
group by
obj,
class
) b,
sys.apt_extents e
where
e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1) +1
-- This line is comparing the file# wrongly, it should be
like
this
e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1) (without +1)
substr(b.fb, instr(b.fb, '.') + 1) between e.block_id and e.block_id
+ e.blocks - 1 group by
e.owner||'.'||e.segment_name
order by
2
/
I notice this mistake becouse I have another script that shows the
buffers cached in memory and the results of both of them was so
different. The problem is that when it gets the segment_name, it
looks the block in other file.
I hope you mail me to answer if I am wrong.
Thank you.
Silvina Botindari.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Silvina Botindari
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: LOT (Little OT): Interesting Oracle related URL's.
Hi Rachel and list, The person responsible for that site is Marin Dimitrov [mailto:[EMAIL PROTECTED]]. Almost all the papers there are illegal copies (including one of mine). I once asked Marin to explain himself on the matter. His explanation was that the collection was for his personal use only. At the time, he removed the index to make the collection largely invisible to the rest of the net, but it appears to have come back! It can also be seen at http://harbinger.sirma.bg/Oracle/ and an older version is mirrored at http://www.itsystems.lv/gints/files/oracle/oracle.htm. I have copied Marin (and the person responsible for the mirror site) on this mail. Hopefully they will at least remove the indexes again, but I would prefer the papers to disappear too. Maybe if others add their votes to Rachel's and mine they'll take the stuff down. Bulgaria and Latvia may be immune to international copyright law, but individuals are seldom immune to community disapprobation. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 8 June 2001 3:01 To: Multiple recipients of list ORACLE-L I hate to be a spoilsport -- but this link (actually the fact that the paper is on his site) bothers me... DBA101: A Refresher Course http://pillango.sirma.bg/oracle/N092_dba.doc Neither Marlene nor I gave permission for this paper (which is copyrighted) to be posted there. Of course, he is not in the US, so the fact that this is illegal doesn't count. For what it's worth: it is only legal in the US to post a link to a legal copy of this sort of information. It is NOT legal to post copyrighted information without the express permission of the copyright holder. Having said that, I would have gladly sent a copy of the paper to anyone who asked or given permission to post it. But I would prefer to be ASKED. And Mark -- www.oracle-users.com now points you to the NY user group web page, as the company (Oracle User Resources) that sponsored it and the ECO conference, does not exist anymore. Rachel From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: LOT (Little OT): Interesting Oracle related URL's. Date: Thu, 07 Jun 2001 06:41:43 -0800 Thanks for your input guys - a great help! I had a few already - namely the Guru's of the list (That reminds me of a film title in a way :) But there were a few I didn't have. Here is my list so far.. IXOra.com (Steve Adam's Site) http://ixora.com.au/ Oracle Technet http://technet.oracle.com OraPub (Craig Shallahamer's Website) www.orapub.com DBA Support http://www.dbasupport.com/ Thomas Kyte's Website http://osi.oracle.com/~tkyte/ Connor McDonald's Web Site http://www.oracledba.co.uk/ Hotsos (Cary Millsap's Site) www.hotsos.com Jared Still's Website http://www.cybcon.com/~jkstill/util/util_master.html Thomas Cox's Website http://www.geocities.com/tbcox23/ Evergreen Database Technologies http://www.evdbt.com/ OraBugFinder http://www.unal-bilisim.com/products/iorabugfinder/iorabugfinder.html Vampire D's Oracle Den (Christopher R. Spence's Site) www.vampired.net Johnathan Lewis's excellent site http://www.jlcomp.demon.co.uk Lazy DBA www.lazydba.com DBA Village www.dba-village.com UK Oracle Users Group www.ukoug.co.uk K Gopalakrishnan's site http://www.geocities.com/kgkrish http://www.oraclenotes.com/ http://www.orafaq.org http://www.oracle-dba.com http://www.orafans.com http://www.oracle-users.com http://www.oracleguru.com http://www.oramag.com/ http://www.oraperf.com/ http://www.oraxcel.com PAPERS- Oracle Performance and Tuning: Overview http://pillango.sirma.bg/oracle/N014_p%26t_overview.pdf Performance Tuning - Now You are the V8 Expert http://pillango.sirma.bg/oracle/N023_tun_all.doc Identifying Resource Intensive SQL in a production environment - http://pillango.sirma.bg/oracle/N026_tuning2.pdf Database Tuning Methodology - http://pillango.sirma.bg/oracle/N027_DB_Tune.doc SQL Tuning for the Oracle DBA - http://pillango.sirma.bg/oracle/N050_tuning.pdf Practical Tuning Advice for the Oracle8 DBA - http://pillango.sirma.bg/oracle/N064_tuning.pdf Finding the Performance Bottlenecks in Your Application - http://pillango.sirma.bg/oracle/N067_performance.pdf Inside the Oracle Cost Based Optimizer - http://pillango.sirma.bg/oracle/N080_optimizer.doc Seven Deadly SQL Traps and How to Avoid Them - http://pillango.sirma.bg/oracle/N082_tuning.doc Secrets of SQL and Application Tuning - http://pillango.sirma.bg/oracle/N108_tuning.doc Oracle DBA Checklist http://pillango.sirma.bg/oracle/N008_dba_checklist.pdf DBA101: A Refresher Course http://pillango.sirma.bg/oracle/N092_dba.doc Thomas Cox's DBA Checklist. http://www.geocities.com/tbcox23/dba_checklist13.doc Now, does anybody have anything to add to this? Papers that you have found to be invaluable? ] Thanks again Mark -- Please see
RE: LOT (Little OT): Interesting Oracle related URL's.
Hi Ethan, To me, the difference is that Google always points you to the site where the original document is to be found, and if an author updates or withdraws a paper from their web site, Google will revisit the site within a few weeks and either update their cached copy or drop the item from their index as appropriate. Also, authors can ask the search engines not to spider stuff using a robots.txt file. People who put illegal copies on their own web sites probably don't check for a robots.txt file or equivalent meta tags, generally don't have links to the original content, and are most unlikely to have spiders continually checking the validity of their copies. I think that the search engines are mostly OK on both ethics and copyright law. However, I would not be surprised if Google's serving of cached copies were to be regarded as technically illegal by the courts. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 8 June 2001 7:59 To: Multiple recipients of list ORACLE-L Google also stores the text of a lot of these .pdfs in cache. - E -Original Message- Sent: Thursday, June 07, 2001 2:28 PM To: Multiple recipients of list ORACLE-L Hi Steve and list, While we are on the subject, I found my RAID paper on multiple sites including this site : http://www.vampired.net/articles/php/concepts.php and I know for a fact that no one has gotten permission from me or IOUG-A, in this regard. Does this not mirror what is done on Marin's site? Gaja And Mark -- www.oracle-users.com now points you to the NY user group web page, as the company (Oracle User Resources) that sponsored it and the ECO conference, does not exist anymore. Rachel From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: LOT (Little OT): Interesting Oracle related URL's. Date: Thu, 07 Jun 2001 06:41:43 -0800 Thanks for your input guys - a great help! I had a few already - namely the Guru's of the list (That reminds me of a film title in a way :) But there were a few I didn't have. Here is my list so far.. IXOra.com (Steve Adam's Site) http://ixora.com.au/ Oracle Technet http://technet.oracle.com OraPub (Craig Shallahamer's Website) www.orapub.com DBA Support http://www.dbasupport.com/ Thomas Kyte's Website http://osi.oracle.com/~tkyte/ Connor McDonald's Web Site http://www.oracledba.co.uk/ Hotsos (Cary Millsap's Site) www.hotsos.com Jared Still's Website http://www.cybcon.com/~jkstill/util/util_master.html Thomas Cox's Website http://www.geocities.com/tbcox23/ Evergreen Database Technologies http://www.evdbt.com/ OraBugFinder http://www.unal-bilisim.com/products/iorabugfinder/iorabugfinder.html Vampire D's Oracle Den (Christopher R. Spence's Site) www.vampired.net Johnathan Lewis's excellent site http://www.jlcomp.demon.co.uk Lazy DBA www.lazydba.com DBA Village www.dba-village.com UK Oracle Users Group www.ukoug.co.uk K Gopalakrishnan's site http://www.geocities.com/kgkrish http://www.oraclenotes.com/ http://www.orafaq.org http://www.oracle-dba.com http://www.orafans.com http://www.oracle-users.com http://www.oracleguru.com http://www.oramag.com/ http://www.oraperf.com/ http://www.oraxcel.com PAPERS- Oracle Performance and Tuning: Overview http://pillango.sirma.bg/oracle/N014_p%26t_overview.pdf Performance Tuning - Now You are the V8 Expert http://pillango.sirma.bg/oracle/N023_tun_all.doc Identifying Resource Intensive SQL in a production environment - http://pillango.sirma.bg/oracle/N026_tuning2.pdf Database Tuning Methodology - http://pillango.sirma.bg/oracle/N027_DB_Tune.doc SQL Tuning for the Oracle DBA - http://pillango.sirma.bg/oracle/N050_tuning.pdf Practical Tuning Advice for the Oracle8 DBA - http://pillango.sirma.bg/oracle/N064_tuning.pdf Finding the Performance Bottlenecks in Your Application - http://pillango.sirma.bg/oracle/N067_performance.pdf Inside the Oracle Cost Based Optimizer - http://pillango.sirma.bg/oracle/N080_optimizer.doc Seven Deadly SQL Traps and How to Avoid Them - http://pillango.sirma.bg/oracle/N082_tuning.doc Secrets of SQL and Application Tuning - http://pillango.sirma.bg/oracle/N108_tuning.doc Oracle DBA Checklist http://pillango.sirma.bg/oracle/N008_dba_checklist.pdf DBA101: A Refresher Course http://pillango.sirma.bg/oracle/N092_dba.doc Thomas Cox's DBA Checklist. http://www.geocities.com/tbcox23/dba_checklist13.doc === message truncated === -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please
RE: TUSC and Kevin Loney
Hi All, No, I'm waiting for Henry to announce the details of the commercial DBA union! The pre-announcement is at http://www.lazydba.com/xcomment.pl?discussions:6 @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. This is a joke for the benefit of those who have been following the LazyDBA issues. -Original Message- Sent: Wednesday, 6 June 2001 0:35 To: Multiple recipients of list ORACLE-L What about Steve Adams, does somebody know whether he joined some big company also? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How many times has an index been used?
Hi All, A cheaper solution to this is to use an AFTER LOGON trigger to set CREATE_STORED_OUTLINES to true. If the users have the CREATE ANY OUTLINE system privilege, you'll be able to see which indexes are being used in the DBA_OUTLINE_HINTS view. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 29 May 2001 19:55 To: Multiple recipients of list ORACLE-L There is also one called The Big Picture - from Bit by Bit www.bitbybit.co.uk - that scans all source, and SQL, and stores all execution plans in a BDE database. It then scans through all the exectution plans to determine whether an index is used or not. It doesn't however tell you how many times the index has *actually* been used.. Mark -Original Message- Sent: Friday, May 25, 2001 09:18 To: Multiple recipients of list ORACLE-L There is commercial software for determining this. www.teleran.com www.pinecone.com Both rather spendy. Jared On Thursday 24 May 2001 06:10, Wilkes, Steve wrote: Hi, Does anyone know how to determine how many times an index has been used or if it has been used at all? I have seen previous attempts by taking snapshots of v$sqlarea and then automating an explain plan and extracting the information that way. I would have thought that there must be an x$ table that records this information somewhere? Any ideas? Thanks in advance. Steve Wilkes ___ Oracle DBA npower email:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sessions are waiting on buffer busy wait with P3 value 130 in v$s
Hi Sri, If you can identify the segments involved from the p1 and p2 parameters and put those segments into a KEEP buffer pool you should be able to get a lot of relief. Failing that, or if they are too big, consider either increasing db_block_buffers if you can or setting _db_percent_hot_default to something like 80. That would allow up to 80% of the DEFAULT buffer pool to be used for hot buffers thereby improving the caching of the buffers that are causing you to wait. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 25 May 2001 9:26 To: Multiple recipients of list ORACLE-L v$s We've lot buffer busy waits in data block with P3 value 130 in V$session. I've read about this in few notes, but still I do not understand few things. P3=130 means: - Block is being read by another session and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block. Does it mean that the block is being held in an incompatible mode by the first session or set of sessions reading it in shared mode, and current session is trying to lock it exclusively for a DML and waiting for the same. What are steps we can take to avoid this types of buffer busy waits? Thanks for your help. Sri -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Srikannan Gopalsamy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Binding a process to a CPU .
Hi Shreepad, Please see http://www.ixora.com.au/q+a/cpu.htm#15_10_1999. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 22 May 2001 22:16 To: Multiple recipients of list ORACLE-L Hi, I wish to test Parallel Query . HP-UX 10.2 K Class machines , 4 CPU 2 GB RAM , Oracle 7.3.4.4 When the background processes are created , Can I bind ( a slave process related to Parallel query server) them to a specific CPU . ? Is this Possible .? Has anyone checked this out .? Any Problems. shreepad. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shreepad Vaidya INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
Ixora News - May 2001
Hi All, The May 2001 issue of Ixora News is now available online at http://www.ixora.com.au/newsletter/2001_05.htm. In this issue ... Sin-onyms - Are synonyms really bad? Not so fast - Incremental checkpointing can defeat commit cleanouts @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary tablespaces
Hi Glen, A tablespace created with the CREATE TEMPORARY TABLESPACE command uses tempfiles instead of datafiles, and as such is often called a tempfile tablespace. A tempfile tablespace is equivalent to a locally managed datafile tablespace with temporary contents, except that tempfiles do not participate in recovery in anyway and thus do not need to be backed up. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Monday, 21 May 2001 11:15 To: Multiple recipients of list ORACLE-L I am interested to know what the difference between a tablespace that is created as temporary and a temporary tablespace is? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary files and Quick i/o
Hi Johnson, I think I've fixed the web site to allow for this now. No, I'd say the bottom line is to use a raw tempfile. While following Waleed's idea of copying the file to make sure that it is not sparse would be safe, it is still contrary to what Veritas recommended. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 19 May 2001 0:47 To: Multiple recipients of list ORACLE-L Steve, The tip is part of Database creation--- Why Raw datafiles?. So if my database has been running for a while and has come back with a few unable to allocate extents in the temp tabalespace. I could convert that datafile to quick io as the full space is allocated. So it is not sparse anymore. Or create it as a permanent datafile and change the status temporary. Am I correct to assume this as the bottom line of the discussion? Johnson --- Steve Adams [EMAIL PROTECTED] wrote: Hi All, OK, you're all correct. Tempfiles can be sparse (it seems to be platform specific). Nevertheless, I'm unclear why Veritas think that is it any more of a problem for Oracle to get ENOSPC from a sparse tempfile if it is a QIO file than otherwise. Presumably Oracle would just raise ORA-7376 in both cases. The instance would not fail because tempfile I/O is always done direct (not by DBWn). But if Veritas say not to do it, then I suppose that is good enough. The solution of course must be to use raw, rather than a filesystem based tempfile at all, for all the good reasons that would have pushed you toward QIO in the first place. Johnson, Could you please post the URL to the tip you mentioned so that I can fix it. @ Thanks, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 10:06 To: Multiple recipients of list ORACLE-L I noticed this when I was playing with 9i. Regular tablespaces take long time to be created and temporary ones take nothing. Here is the proof: $ ls -l temp01.dbf -rw-rw-r-- 1 oracle dba 209719296 May 17 18:55 temp01.dbf $ du -k temp01.dbf 40968 temp01.dbf $ ls -l users01.dbf -rw-rw-r-- 1 oracle dba 26218496 May 11 20:27 users01.dbf $ du -k users01.dbf 25608 users01.dbf Regards, Waleed -Original Message- Sent: Thursday, May 17, 2001 7:41 PM To: Multiple recipients of list ORACLE-L If you create tablespace like - create temporary tablespace tablespace_name ... file_name created file will be sparse. Alex Hillman -Original Message- Sent: Thursday, May 17, 2001 6:30 PM To: Multiple recipients of list ORACLE-L Hi Johnson, I don't believe that tempfiles can be sparse (although I may be wrong) and I am confident that datafiles cannot be. Maybe you should log a TAR with Oracle support to checkout whether tempfile can be sparse and just use datafiles until then. Anyway, can you please post the URL to the tip you mentioned. While it is the sort of thing I might say, I don't remember actually having said so. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 0:05 To: Multiple recipients of list ORACLE-L Hi, After reading Steve Adams' tip on making temp files quick io. I posed this question to Veritas as they had told us not do so. The answer they give is the following. Temporary datafiles should not be converted to quick i/o files. The following is an excerpt from the administrator's Guide: Tablespaces marked TEMPORARY can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. I would appreciate your thoughts on this? Johnson Job -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED
RE: Temporary files and Quick i/o
Hi Johnson, No, that's a datafile tablespace with temporary contents. A tempfile tablespace is inherently temporary. Check the documentation for the difference between ... CREATE TABLESPACE ... TEMPORARY and CREATE TEMPORARY TABLESPACE ... @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 19 May 2001 4:06 To: Multiple recipients of list ORACLE-L Is not the same achieved by creating a permanent tablespace and then altering it to temporary using alter tablespace command? --- Khedr, Waleed [EMAIL PROTECTED] wrote: No you don't. Just copy the file to the new file and the new file will be created notsparse. -Original Message- Sent: Friday, May 18, 2001 10:11 AM To: Multiple recipients of list ORACLE-L But you need new_file with specific length - let say 500M - how will you do it? Another way to do it - but may be long - is create normal tablespace (not temporary) with data files you need, then drop tablespace and then create temprary tablespace with reuse of created data files. Alex Hillman -Original Message- Sent: Thursday, May 17, 2001 8:36 PM To: Multiple recipients of list ORACLE-L After creating the temp file and shutting down the database, you can use on Solaris cp temp-file new-file and then cp new-file temp-file. After that you can convert it to quick-i/o. Regards, Waleed -Original Message- Sent: Thursday, May 17, 2001 8:06 PM To: Multiple recipients of list ORACLE-L I noticed this when I was playing with 9i. Regular tablespaces take long time to be created and temporary ones take nothing. Here is the proof: $ ls -l temp01.dbf -rw-rw-r-- 1 oracle dba 209719296 May 17 18:55 temp01.dbf $ du -k temp01.dbf 40968 temp01.dbf $ ls -l users01.dbf -rw-rw-r-- 1 oracle dba 26218496 May 11 20:27 users01.dbf $ du -k users01.dbf 25608 users01.dbf Regards, Waleed -Original Message- Sent: Thursday, May 17, 2001 7:41 PM To: Multiple recipients of list ORACLE-L If you create tablespace like - create temporary tablespace tablespace_name ... file_name created file will be sparse. Alex Hillman -Original Message- Sent: Thursday, May 17, 2001 6:30 PM To: Multiple recipients of list ORACLE-L Hi Johnson, I don't believe that tempfiles can be sparse (although I may be wrong) and I am confident that datafiles cannot be. Maybe you should log a TAR with Oracle support to checkout whether tempfile can be sparse and just use datafiles until then. Anyway, can you please post the URL to the tip you mentioned. While it is the sort of thing I might say, I don't remember actually having said so. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 0:05 To: Multiple recipients of list ORACLE-L Hi, After reading Steve Adams' tip on making temp files quick io. I posed this question to Veritas as they had told us not do so. The answer they give is the following. Temporary datafiles should not be converted to quick i/o files. The following is an excerpt from the administrator's Guide: Tablespaces marked TEMPORARY can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. I would appreciate your thoughts on this? Johnson Job __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling
RE: job offer from SAUDI ARABIA
Hi All, We're not allowed to talk about that on the other list! I was trying to reply to the thread Christopher started about how to unsubscribe, but kept getting bogus replies that looked like list mails but were not really. It took me 7 attempts to work out that it was the thread title Bye Christopher that the list owner was filtering on, I eventually got my message through to the list by changing it to B** Christ*ph*r. It seems that the list owner did not want us to talk about Christopher quitting the list, but also did not want us to realise that these mails were not getting through to anyone else, thus the bogus reply just to the sender. What a clever way to kill a thread! Maybe Jared should apply this technique to messages headed job offer from SAUDI ARABIA. :-) @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 19 May 2001 4:06 To: Multiple recipients of list ORACLE-L Chris, We knew you left LazyDBA... ;) - Kirti -Original Message- From: Christopher Spence [SMTP:[EMAIL PROTECTED]] Sent: Friday, May 18, 2001 10:26 AM To: Multiple recipients of list ORACLE-L Subject: RE: job offer from SAUDI ARABIA Interesting for the first email I recieved on list. heh. hey all. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: enqueue waits -- CI
Hi Diego, I don't know of any way in which using DBMS_PIPE might be related to CI enqueue waits. However, even if there is something to their suggestion, if your ID values are 0 and 5 then the CI call you are waiting for is one of the reuse block range calls. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 5:56 To: Multiple recipients of list ORACLE-L As usual, thanks for the answer Steve. I'll try to reduce the number of shrinks in the rollback segments, so this is going to impact on CI enqueue waits. What do you think about Metalink DOC ID 1020355.102 recomendation? (from my previous email) I've also read a metalink document (1020355.102). According to this, one possible cause is that my application is using ***dbms_pipe*** extensively (it may be right, I've seen pipe get event very high). The suggested solution is to increase the shared_pool. I can't access the other documents mentioned in the paper. Eventtotal_waits time_waited pipe get910593342266184 Thank you. DC - Original Message - To: Diego Cutrone [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 1:53 AM Hi Diego, No, those are block buffers. When a segment is dropped, truncated or shrunk (normally a rollback segment) then a reuse block range cross instance call is needed to flush the unwanted blocks from cache. Similarly, before a parallel direct read a checkpoint block range or checkpoint object cross instance call is needed (otherwise changes made prior to the start of the query and committed but not yet flushed to disk could be missed by the direct reads). These are cross-instance calls even in single-instance Oracle because the code allows for the possibility of parallel server, and the DBWn processes in all instance need to flush the cache in their own instances. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- From: Diego Cutrone [mailto:[EMAIL PROTECTED]] Sent: Thursday, 17 May 2001 4:11 To: Multiple recipients of list ORACLE-L Subject: RE: enqueue waits -- CI Thanks for answering Unal, John. John, you were right about the query. I've corrected it. And I'm not getting T[ and CK anymore,now I get TX and CI. I've also done further investigation and I also know now what p2 and p3 mean. This is the updated data: (from the dumps) count TYPE MODE 13 CI 6 p2=0 p3=5 ela=0 19 TX 6 p2=262223 p3=53352 ela=301 75 TX 4 p2=524391 p3=50022 ela=301 75 TX 6 p2=720923 p3=5194 ela=301 104TX 6 p2=196736 p3=52393 ela=301 305TX 6 p2=393276 p3=50281 ela=301 Now, how can I get the rollback segment number involved in the TX ? (I know its from p2 and p3, but how?) --just curious. I'll also take John advise, and I'll try to identify the locking session(s). Now, although CI enqueue waits (cross instance call invocation) are brief on this sample, Sometimes it's not. So I'm trying to understand what it means. According to p2 and p3 flags, they are indicating Flush buffers for reuse as new class, that means that a session needs a buffer (in the shared pool I think) and it has to flush some others in order to get space. am I correct?. I've also read a metalink document (1020355.102). According to this, one possible cause is that my application is using dbms_pipe extensively (it may be right, I've seen event pipe get very high). The suggested solution is to increase the shared_pool. I can't access the other documents mentioned in the paper. Can someone explain to me what means this CI enqueue and how can I reduce it. TIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 6:16 PM Hi Diego, Without going into details, an 'enqueue' wait is mostly due to a user/program initated transaction lock and I see it a lot in Financial databases (I see you are on 10.7?). I deduce you were looking at V$SYSTEM_EVENT - If a user locked one row (probably queried a row in Forms, inadvertly changed one character which issues a 'SELECT for UPDATE, thus locking that row) and anotehr user (through a form or a report) needs to perform DML on that particular row, then (I believe) you will clock up time against the 'enqueue' event. What I would suggest is that you use the following query to determine if someone is locking someone else out: select event, count(*) from v$session_wait group by event If you see the 'enqueue' event in this list, some process is probably
RE: Temporary files and Quick i/o
Hi Johnson, I don't believe that tempfiles can be sparse (although I may be wrong) and I am confident that datafiles cannot be. Maybe you should log a TAR with Oracle support to checkout whether tempfile can be sparse and just use datafiles until then. Anyway, can you please post the URL to the tip you mentioned. While it is the sort of thing I might say, I don't remember actually having said so. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 0:05 To: Multiple recipients of list ORACLE-L Hi, After reading Steve Adams' tip on making temp files quick io. I posed this question to Veritas as they had told us not do so. The answer they give is the following. Temporary datafiles should not be converted to quick i/o files. The following is an excerpt from the administrator's Guide: Tablespaces marked TEMPORARY can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. I would appreciate your thoughts on this? Johnson Job __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary files and Quick i/o
Hi All, OK, you're all correct. Tempfiles can be sparse (it seems to be platform specific). Nevertheless, I'm unclear why Veritas think that is it any more of a problem for Oracle to get ENOSPC from a sparse tempfile if it is a QIO file than otherwise. Presumably Oracle would just raise ORA-7376 in both cases. The instance would not fail because tempfile I/O is always done direct (not by DBWn). But if Veritas say not to do it, then I suppose that is good enough. The solution of course must be to use raw, rather than a filesystem based tempfile at all, for all the good reasons that would have pushed you toward QIO in the first place. Johnson, Could you please post the URL to the tip you mentioned so that I can fix it. @ Thanks, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 10:06 To: Multiple recipients of list ORACLE-L I noticed this when I was playing with 9i. Regular tablespaces take long time to be created and temporary ones take nothing. Here is the proof: $ ls -l temp01.dbf -rw-rw-r-- 1 oracle dba 209719296 May 17 18:55 temp01.dbf $ du -k temp01.dbf 40968 temp01.dbf $ ls -l users01.dbf -rw-rw-r-- 1 oracle dba 26218496 May 11 20:27 users01.dbf $ du -k users01.dbf 25608 users01.dbf Regards, Waleed -Original Message- Sent: Thursday, May 17, 2001 7:41 PM To: Multiple recipients of list ORACLE-L If you create tablespace like - create temporary tablespace tablespace_name ... file_name created file will be sparse. Alex Hillman -Original Message- Sent: Thursday, May 17, 2001 6:30 PM To: Multiple recipients of list ORACLE-L Hi Johnson, I don't believe that tempfiles can be sparse (although I may be wrong) and I am confident that datafiles cannot be. Maybe you should log a TAR with Oracle support to checkout whether tempfile can be sparse and just use datafiles until then. Anyway, can you please post the URL to the tip you mentioned. While it is the sort of thing I might say, I don't remember actually having said so. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 0:05 To: Multiple recipients of list ORACLE-L Hi, After reading Steve Adams' tip on making temp files quick io. I posed this question to Veritas as they had told us not do so. The answer they give is the following. Temporary datafiles should not be converted to quick i/o files. The following is an excerpt from the administrator's Guide: Tablespaces marked TEMPORARY can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. I would appreciate your thoughts on this? Johnson Job -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Temporary files and Quick i/o
Hi All, Another correction. That error number has been changed to 7248 since Oracle8. I think I need a coffee! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 10:01 To: Multiple recipients of list ORACLE-L Hi All, OK, you're all correct. Tempfiles can be sparse (it seems to be platform specific). Nevertheless, I'm unclear why Veritas think that is it any more of a problem for Oracle to get ENOSPC from a sparse tempfile if it is a QIO file than otherwise. Presumably Oracle would just raise ORA-7376 in both cases. The instance would not fail because tempfile I/O is always done direct (not by DBWn). But if Veritas say not to do it, then I suppose that is good enough. The solution of course must be to use raw, rather than a filesystem based tempfile at all, for all the good reasons that would have pushed you toward QIO in the first place. Johnson, Could you please post the URL to the tip you mentioned so that I can fix it. @ Thanks, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 10:06 To: Multiple recipients of list ORACLE-L I noticed this when I was playing with 9i. Regular tablespaces take long time to be created and temporary ones take nothing. Here is the proof: $ ls -l temp01.dbf -rw-rw-r-- 1 oracle dba 209719296 May 17 18:55 temp01.dbf $ du -k temp01.dbf 40968 temp01.dbf $ ls -l users01.dbf -rw-rw-r-- 1 oracle dba 26218496 May 11 20:27 users01.dbf $ du -k users01.dbf 25608 users01.dbf Regards, Waleed -Original Message- Sent: Thursday, May 17, 2001 7:41 PM To: Multiple recipients of list ORACLE-L If you create tablespace like - create temporary tablespace tablespace_name ... file_name created file will be sparse. Alex Hillman -Original Message- Sent: Thursday, May 17, 2001 6:30 PM To: Multiple recipients of list ORACLE-L Hi Johnson, I don't believe that tempfiles can be sparse (although I may be wrong) and I am confident that datafiles cannot be. Maybe you should log a TAR with Oracle support to checkout whether tempfile can be sparse and just use datafiles until then. Anyway, can you please post the URL to the tip you mentioned. While it is the sort of thing I might say, I don't remember actually having said so. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 0:05 To: Multiple recipients of list ORACLE-L Hi, After reading Steve Adams' tip on making temp files quick io. I posed this question to Veritas as they had told us not do so. The answer they give is the following. Temporary datafiles should not be converted to quick i/o files. The following is an excerpt from the administrator's Guide: Tablespaces marked TEMPORARY can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. I would appreciate your thoughts on this? Johnson Job -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Would you increase the shared pool? --URGENT
Hi Pablo, Your shared pool is too big, not too small, and you are parsing too frequently (probably some literal SQL). Your use of synonyms is an exacerbating factor. Your reloads are due to the invalidations. They do not indicate that your shared pool is too small. Of course, you should worry about the major issues first. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 18 May 2001 2:01 To: Multiple recipients of list ORACLE-L Hello Gurus, I need help in this performance issue. Oracle 7.3.4 HPUX 10.20 I 've got this ratios: BC hit ratio 98 dictionary cache hit rate 100 library cache get hit ratio91 library cache pin hit ratio98 Mayor waits are enqueue (57%), buffer busy waits(34%), especially p3=0 for some big tables (buffer being read from disk), and latch free (6%). I'm dealing with enqueue and bbw, but what would you do with the low LC ratio and the shared pool in this case. Latch free waits details LATCH TYPEIMPACT SLEEP RATE LATCH - shared pool 123285 1.05% 27097 library cache58841 0.06%103 cache buffers chains 18108 0.00% 0 This is the library cache stat: NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDAT SQL AREA .73288057 .97035171 47011 13891 TABLE/PROCEDURE .99150783 .99813894 11758 0 BODY .9975282 .9971979 32 0 TRIGGER .99816161 .99591602 61 0 INDEX .03401361 .02267574 5 0 CLUSTER . . 0 0 OBJECT1 1 0 0 PIPE .9414 .9415 0 0 The keep object summary TYPE OBJECTS KEPT RELOADSOWNERS -- - - - - TABLE389 0 166112 SYNONYM 348 0 2574 4 PACKAGE 294 293 131 4 PACKAGE BODY 292 29123 4 TRIGGER 220 22052 9 VIEW 170 0 373 3 SEQUENCE 108 108 13111 NON-EXISTENT 68 2 153 8 PROCEDURE 606047 2 FUNCTION 1616 4 1 PARAMETER VALUE USAGE -- - - session_cached_cursors 100 81% open_cursors 500 85% Shared Pool RECURRENT TRANSIENTFLUSHED PINS ANDORA-4031 CHUNKSCHUNKSCHUNKS RELEASESERRORS - - - - - 3051 5374655389 5351916 0 Shared pool free lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST - -- --- - 0 795521707 46 72 1 45760 516 88 96 8 9480 1 9480 9480 9 41165488179722907 32776 10 48335568109344222 65424 11 20518744 24284788 130072 124383200 26 168584 246496 13 807072 2 403536 447040 TIA ___ Do You Yahoo!? Yahoo! Messenger: Comunicación instantánea gratis con tu gente - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: File open event
Hi Arun, Did you check whether LGWR and CKPT are the main culprits? Yes, I have seen lots of 'file open' waits on a Solaris system running Cached Quick I/O, but they had lots of web servers making frequent short connections to the database, so I did not associate it with QIO. Interesting hypothesis however. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 16 May 2001 22:31 To: Multiple recipients of list ORACLE-L Thanks for the reply Steve, this event was not proping up before until we enabled QUICK I/O and that too after we enabled Cached Quick I/o. one more thing I wanted to know have you faced any issues are come across, When you have enabled QUICK I/O on Sun Solaris it causes causes too much wait I/O,due to this you will see Db sequential read and buffer busy wait.On one of our busiest days it came to an extent where the cpu idle time came to 0%. The only option was to enable Cached Quick I/o which is now causing the reverse, it causing write complete waits and this file open event,which was worrying me. Please let me know if you faced any issue. From: Steve Adams [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: File open event Date: Tue, 15 May 2001 00:10:42 -0800 Hi Arun, Foreground processes open the datafiles only when they need to read from them (or write to them in the case of a sort). The 'file open' wait event represents the opening of and typically the first I/O operation against that datafile, however, sometimes a few extraneous I/O operations against other datafiles seem to be included. The 'truss' output below shows a 'file open' wait delimited by calls to the 'times()' system call, with two extraneous reads included in the wait. Incidentally, it also shows how Oracle duplicates the file descriptor onto a high numbered file descriptor. times(0xEFFF8F78)= 31885993 open64(/database/PRODidxC01.dbf, O_RDWR|O_DSYNC) = 11 getrlimit(RLIMIT_NOFILE, 0xEFFF5100) = 0 fstat64(403, 0xEFFF5068) = 0 fstat64(402, 0xEFFF5068) Err#9 EBADF fcntl(11, F_DUP2FD, 0x0192) = 402 close(11)= 0 fcntl(402, F_SETFD, 0x0001) = 0 ioctl(402, 0x0403, 0xEFFF50C4) = 0 pread64(402, 0602\0\0\081 /A5 . -1A8C.., 8192, 0x25F4A000) = 8192 pread64(405, 0602\0\00380 01 /BC *18.., 8192, 0x07802000) = 8192 pread64(408, 0602\0\00E81 B18 0848AF6.., 8192, 0x2843) = 8192 times(0xEFFF7D00)= 31885997 Large numbers of 'file open' waits are seen in applications that frequently disconnect from the instance and then reconnect later, rather than leaving their connection open. Also, if the instance has been up for a long time, the CKPT process can accumulate a lot of 'file open' waits - several per file per checkpoint. In your case the number of 'file identify' waits makes me suspect the second explanation. In general the file identify operation is just done once per datafile and controlfile. It validates the filename and establishes a 'file information block' in the SGA that is then shared by all processes needing to open any of the datafiles or controlfiles. On the other hand, LGWR identifies each member of the selected logfile group as part of each logfile switch. So a large number of 'file identify' waits is consistent with the instance having been up for a long time with regular log switches. Your average time waited for these waits is acceptable. In general, the average 'file open' time should be of the same order of magnitude as the average 'db file sequential read' time. If not, you should ensure that none of the Oracle files have what the operating system regards as long filenames, or a parent directory with a long filename. See What's in a name? at http://www.ixora.com.au/newsletter/2000_12.htm#file_names in last December's issue of Ixora News on that. In some cases, it also helps to hold the log files open. See Holding the log files open at http://www.ixora.com.au/tips/tuning/log_switch.htm on that. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 12 May 2001 0:26 To: Multiple recipients of list ORACLE-L Can somebody please explain to me what this event is, I am not able to find good documentation on this issue. EVENT TOTAL_WAIT TOTAL_TIME TIME_WAITE AVERAGE_WA -- -- -- -- -- file identify 38343 0 3135 .08176199 file open 9436698 03934742 .416961738 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat
RE: enqueue waits -- CI
Hi Diego, No, those are block buffers. When a segment is dropped, truncated or shrunk (normally a rollback segment) then a reuse block range cross instance call is needed to flush the unwanted blocks from cache. Similarly, before a parallel direct read a checkpoint block range or checkpoint object cross instance call is needed (otherwise changes made prior to the start of the query and committed but not yet flushed to disk could be missed by the direct reads). These are cross-instance calls even in single-instance Oracle because the code allows for the possibility of parallel server, and the DBWn processes in all instance need to flush the cache in their own instances. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 17 May 2001 4:11 To: Multiple recipients of list ORACLE-L Thanks for answering Unal, John. John, you were right about the query. I've corrected it. And I'm not getting T[ and CK anymore,now I get TX and CI. I've also done further investigation and I also know now what p2 and p3 mean. This is the updated data: (from the dumps) count TYPE MODE 13 CI 6 p2=0 p3=5 ela=0 19 TX 6 p2=262223 p3=53352 ela=301 75 TX 4 p2=524391 p3=50022 ela=301 75 TX 6 p2=720923 p3=5194 ela=301 104TX 6 p2=196736 p3=52393 ela=301 305TX 6 p2=393276 p3=50281 ela=301 Now, how can I get the rollback segment number involved in the TX ? (I know its from p2 and p3, but how?) --just curious. I'll also take John advise, and I'll try to identify the locking session(s). Now, although CI enqueue waits (cross instance call invocation) are brief on this sample, Sometimes it's not. So I'm trying to understand what it means. According to p2 and p3 flags, they are indicating Flush buffers for reuse as new class, that means that a session needs a buffer (in the shared pool I think) and it has to flush some others in order to get space. am I correct?. I've also read a metalink document (1020355.102). According to this, one possible cause is that my application is using dbms_pipe extensively (it may be right, I've seen event pipe get very high). The suggested solution is to increase the shared_pool. I can't access the other documents mentioned in the paper. Can someone explain to me what means this CI enqueue and how can I reduce it. TIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 15, 2001 6:16 PM Hi Diego, Without going into details, an 'enqueue' wait is mostly due to a user/program initated transaction lock and I see it a lot in Financial databases (I see you are on 10.7?). I deduce you were looking at V$SYSTEM_EVENT - If a user locked one row (probably queried a row in Forms, inadvertly changed one character which issues a 'SELECT for UPDATE, thus locking that row) and anotehr user (through a form or a report) needs to perform DML on that particular row, then (I believe) you will clock up time against the 'enqueue' event. What I would suggest is that you use the following query to determine if someone is locking someone else out: select event, count(*) from v$session_wait group by event If you see the 'enqueue' event in this list, some process is probably waiting on a lock... You can then trace the user/process via Lock detection scripts (see Metablink) and kill the blocking process. You could also query from sys.dba_waiters which will present an easier picture in this case.. As far as the SQL goes, see below: select chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1,16711680)/63365) Lock, - I believe the value is '65535', rather than 63365) to_char(bitand(p1,65535)) Mode from dual You can learn a lot from the Oracle 8.1 Reference Manual - Appendix A. Oracle Wait Events and App B Oracle Enqueue Names. While they are applicable for 8.1, most of the info is valid for 7.3 as well... Please let us know if you need additional info. John Kanagaraj (A long time member of the Always look at v$session_wait first camp) Oracle Applications DBA Hitach Data Systems, Santa Clara Work : (408) 970 7002 -Original Message- Sent: Tuesday, May 15, 2001 11:50 AM To: Multiple recipients of list ORACLE-L Hi List, I'm trying to identify the possible cause of contention in a database: Oracle 7.3.4.0.0 // HP-UX 10.20 // FINANCIALS As far I can see, event enqueue is on top (followed by some buffer busy waits) EVENT TIME_WAITED AVERAGE_WAIT enqueue 854176 3746.39 (why is the average wait so high?) buffer busy waits 292770 1.53 Enqueue Stats -- TY
RE: File open event
Hi Arun, Foreground processes open the datafiles only when they need to read from them (or write to them in the case of a sort). The 'file open' wait event represents the opening of and typically the first I/O operation against that datafile, however, sometimes a few extraneous I/O operations against other datafiles seem to be included. The 'truss' output below shows a 'file open' wait delimited by calls to the 'times()' system call, with two extraneous reads included in the wait. Incidentally, it also shows how Oracle duplicates the file descriptor onto a high numbered file descriptor. times(0xEFFF8F78)= 31885993 open64(/database/PRODidxC01.dbf, O_RDWR|O_DSYNC) = 11 getrlimit(RLIMIT_NOFILE, 0xEFFF5100) = 0 fstat64(403, 0xEFFF5068) = 0 fstat64(402, 0xEFFF5068) Err#9 EBADF fcntl(11, F_DUP2FD, 0x0192) = 402 close(11)= 0 fcntl(402, F_SETFD, 0x0001) = 0 ioctl(402, 0x0403, 0xEFFF50C4) = 0 pread64(402, 0602\0\0\081 /A5 . -1A8C.., 8192, 0x25F4A000) = 8192 pread64(405, 0602\0\00380 01 /BC *18.., 8192, 0x07802000) = 8192 pread64(408, 0602\0\00E81 B18 0848AF6.., 8192, 0x2843) = 8192 times(0xEFFF7D00)= 31885997 Large numbers of 'file open' waits are seen in applications that frequently disconnect from the instance and then reconnect later, rather than leaving their connection open. Also, if the instance has been up for a long time, the CKPT process can accumulate a lot of 'file open' waits - several per file per checkpoint. In your case the number of 'file identify' waits makes me suspect the second explanation. In general the file identify operation is just done once per datafile and controlfile. It validates the filename and establishes a 'file information block' in the SGA that is then shared by all processes needing to open any of the datafiles or controlfiles. On the other hand, LGWR identifies each member of the selected logfile group as part of each logfile switch. So a large number of 'file identify' waits is consistent with the instance having been up for a long time with regular log switches. Your average time waited for these waits is acceptable. In general, the average 'file open' time should be of the same order of magnitude as the average 'db file sequential read' time. If not, you should ensure that none of the Oracle files have what the operating system regards as long filenames, or a parent directory with a long filename. See What's in a name? at http://www.ixora.com.au/newsletter/2000_12.htm#file_names in last December's issue of Ixora News on that. In some cases, it also helps to hold the log files open. See Holding the log files open at http://www.ixora.com.au/tips/tuning/log_switch.htm on that. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 12 May 2001 0:26 To: Multiple recipients of list ORACLE-L Can somebody please explain to me what this event is, I am not able to find good documentation on this issue. EVENT TOTAL_WAIT TOTAL_TIME TIME_WAITE AVERAGE_WA -- -- -- -- -- file identify 38343 0 3135 .08176199 file open 9436698 03934742 .416961738 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/sql loop assistance
Hi Jacques, It is not reliable if there is row migration. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 11 May 2001 3:07 To: Multiple recipients of list ORACLE-L -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] I'll have to disagree with not using commit within a loop. If you identify what a transaction is within your code, and write it accordingly, using commit where appropriate, you will not get ORA-1002 or ORA-1555, at least not due to your own code. This reminds me, Mr. Still, you posted an example once to the list about doing a fetch in rowid order to avoid revisiting the same block when doing a full table scan of a table, so that reading and updating every row in a table would avoid the dreaded snapshot too old. I told a developer about that today and showed him a code sample, but he says he still had a snapshot too old. Would it be possible for you to post that snippet of code again? Jacques R. Kilchoër x8816 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: COLLECTION ITERATOR PICKLER FETCH
Hi Scott, Pickling is serializing arbitrary object-oriented data structures. That is, converting them into a byte stream for storage, transmission over a network or iterative navigation as in this case. It is a more complex process than you might imagine. If you want more information, search the web for information on Python's pickle module. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 11 May 2001 3:11 To: Multiple recipients of list ORACLE-L Sometimes I really love the developers at Oracle.. This should be good fodder for Eric P. Does anyone have experience with infamous pickler Fetch which shows up in this explain plan the select is on index organized nested tables (Nested objects)? It's obvious that someone in internals has a good sense of humor. I guess this is the way Oracle dereferences the embedded collections? SELECT STATEMENT SORT GROUP BY NESTED LOOPS NESTED LOOPS NESTED LOOPS NESTED LOOPS ` BLAH BLAH BLAH COLLECTION ITERATOR PICKLER FETCH VIEW AD_AGG_DATA_MASTER UNION-ALL PARTITION FILTER TABLE ACCESS BY INDEX ROWID AD_23 INDEX RANGE SCAN AD_23_IDX_1 FILTER TABLE ACCESS BY INDEX ROWID AD_24 INDEX RANGE SCAN AD_24_IDX_1 FILTER TABLE ACCESS BY INDEX ROWID AD_25 INDEX RANGE SCAN AD_25_IDX_1 Scott Crabtree Veritas Certified Survivor -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Crabtree INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Hi Nuno (and list), Changing 'optimizer_index_caching' and 'optimizer_index_cost_adj' does seem to inhibit cursor sharing under 8i. You may want to try the following test under 8.0 and see if it is any different. SQL create table t as select * from dual; Table created. SQL analyze table t compute statistics; Table analyzed. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 1 1 SQL show parameters optimizer_index NAME TYPEVALUE --- -- optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 SQL alter session set optimizer_index_caching = 1; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 2 2 SQL alter session set optimizer_index_cost_adj = 99; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 3 3 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 0:51 To: Multiple recipients of list ORACLE-L [snip] Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer and block size changes = trouble...
Hi Waleed, I ran the test below under 8.1.6.0 on NT using SQL*Plus and I would have expected the same results under 8.1.6.3 on Solaris. Do you possibly have a small shared pool with very quick reuse? @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 10:01 To: Multiple recipients of list ORACLE-L Hi Steve, I tested it on Oracle 8.1.6.3 (Solaris 2.6). Parse_calls gets incremented every time the sql gets executed but the version_count continues to be 1. Regards, Waleed -Original Message- Sent: Wednesday, May 09, 2001 6:15 PM To: Multiple recipients of list ORACLE-L Hi Nuno (and list), Changing 'optimizer_index_caching' and 'optimizer_index_cost_adj' does seem to inhibit cursor sharing under 8i. You may want to try the following test under 8.0 and see if it is any different. SQL create table t as select * from dual; Table created. SQL analyze table t compute statistics; Table analyzed. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 1 1 SQL show parameters optimizer_index NAME TYPEVALUE --- -- optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 SQL alter session set optimizer_index_caching = 1; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 2 2 SQL alter session set optimizer_index_cost_adj = 99; Session altered. SQL select count(*) from t; COUNT(*) -- 1 SQL select parse_calls, version_count from v$sqlarea where sql_text = 'select count(*) from t '; PARSE_CALLS VERSION_COUNT --- - 3 3 SQL @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 10 May 2001 0:51 To: Multiple recipients of list ORACLE-L [snip] Now, start playing with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ. Incidentally, these can be set at session level but to get them picked up you need to do a FLUSH SHARED_POOL. Which kinda defeats the purpose of making them dynamic in the first place, Mr. ORACLE? Or am I missing something obvious? I can imagine someone doing this at the beginning of each batch job and flushing the shared pool each time! Jeez, some database coders don't have a clue about the real world, do they?... [snip] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: V$RECENT_BUCKET and V$CURRENT_VIEWS
Hi there, Have a look at http://www.ixora.com.au/newsletter/2001_03.htm#ideal for an 8i alternative. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Monday, 7 May 2001 11:30 To: Multiple recipients of list ORACLE-L hi gurus, Are there any equivalent views in 8i ? If I want know the value (after increasing db_block_buffers). I know they are not suppported in latest versions ... but are there any alternatives to tune? narender.akula http://www.terralinkltd.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Narender Akula INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: _ROW_CACHE_BUFFER_SIZE
Hi All, Yes, '_row_cache_buffer_size' does nothing for single instance Oracle. There is a tip on setting '_row_cache_cursors' at http://www.ixora.com.au/tips/tuning/row_cache_cursors.htm on the Ixora web site. Ross, unless you can get an ALTER SESSION SET CURRENT_SCHEMA statement in there, the best thing for you to do would be to create private synonyms. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 3 May 2001 7:26 To: Multiple recipients of list ORACLE-L Ross, Is this OPS? According to Steve Adams ( this is how you make mail show up in his mailbox :) that is for OPS. Sorry, that's all I have to offer. http://www.ixora.com.au/q+a/0008/31163237.htm Jared On Wednesday 02 May 2001 11:01, Mohan, Ross wrote: Performance/Internals Wizards! Anyone ever play with _row_cache_buffer_size? I am having trouble with a crappy application and out of control packages ( too big ) and public synonyms out the ying yang. My 734 instance has DD cache size swells that remind me of A Perfect Storm, except I bear zero resemblance to George Clooney. It may be a derived parameter ( depending on shpool size, processes, etc) but, if so, I cannot find the root parameter. I would like to apply an init.ora bandaid to the DD cache without having to touch the crApplication yet. Yes, it would be an indirect fix at best, especially given that V$LATCH_MISSES indicates it is the DD find obj that is the problem, not the parse. sigh How I despise public synonyms, developers, and and and sputtering into silence . If you've played with _row_cache_buffer_size, please let me know. I have done a ROW_CACHE dump via ORADEBUG, but I cannot prove conclusively that the space/size is due to Public Synonyms. Any guidance there would be most welcome, as well. Thanks! - Ross p.s. I have set _row_cache_cursors to 200 ( range is 10-3300, default is 10, and yes i know (a) oracle says the default is fine, and (b) using undocumented parameters is bad ) || -Original Message- || From: Steve Adams [mailto:[EMAIL PROTECTED]] || Sent: Tuesday, May 01, 2001 11:00 AM || To: Multiple recipients of list ORACLE-L || Subject: RE: Oracle What savepoints are active for a given session? || || || Hi Venkata, || || There is no V$ view or X$ table that contains this || information. The only || solution that occurs to me is to dump the savepoints to the || process trace file || with || || alter session set events 'immediate trace name savepoints'; || || and then use UTL_FILE to read and parse the trace file || information. Here is an || example of what you might get: || || SAVEPOINT FOR CURRENT PROCESS || -- ||flag: 0x3 ||name: S3 ||dba: 0x831408, sequence #: 964, record #: 12, savepoint #: 19082 ||status: VALID, next: 3822f60 ||name: S2 ||dba: 0x831408, sequence #: 964, record #: 11, savepoint #: 19046 ||status: VALID, next: 37f63ec ||name: S1 ||dba: 0x831408, sequence #: 964, record #: 10, savepoint #: 18602 ||status: VALID, next: 0 || || This process has three savepoints named S1, S2 and S3 respectively. || || @ Regards, || @ Steve Adams || @ http://www.ixora.com.au/ || @ http://www.christianity.net.au/ || || || -Original Message- || Sent: Tuesday, 1 May 2001 18:20 || To: Multiple recipients of list ORACLE-L || || || HI PLEASE SEND ME ANSWER || Question Title: Oracle What savepoints are active for a || given session? || || Detailed Question: Does anybody know IF and HOW can I get a list of || active savepoints for the current session? I need a native way, not || solutions based on additional application-level || housekeeping. Some query || on the V$ tables/views would be the kind of answer I'm looking for. || Details: Within one stored proc I'd like to obtain a list || (in any form) || of the savepoints issued currently in the current || transaction. Example: || Proc A issues savepoint svA; then proc B issues savepoint || svB; and then || proc C builds and uses a cursor having 'svA' and 'svB' as rows, or || something like that. Of course, there are no intervening commits or || rollbacks. || || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com || -- || Author: Steve Adams || 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
RE: FILE SYSTEM BUFFER IN JFS : ON OR OFF
Hi Raj, Unless your 'db_block_size' is 8192 then you should use direct I/O for all Oracle file systems regardless of usage. Also because Oracle explicitly requests synchronous I/O whenever it opens its datafiles, it is the convosync mount option that counts, rather than the mincache one. Indeed, if you use 'convosync=direct, mincache=dsync' you'll get buffering for trace files or other stuff that might also reside on your Oracle file systems, which is probably what you want. I can't imagine any reason why you would want buffering for rollback segment datafiles, but assuming your block size is 8K, there is a weak argument for buffering temporary segment datafiles. Namely that temporary segment writes are invariably read again, and operating system buffering may save physical I/O on the reads. Oracle does not normally buffer temporary tablespace I/O and so with operating system buffering there is no buffering at all. However, if you machine is dedicated to Oracle and you are willing to allow Oracle to use the memory directly, then a better approach is to use the memory that might otherwise be used for operating system buffering of temporary tablespace writes to increase the 'sort_area_size' and thus avoid those writes entirely. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 28 April 2001 1:15 To: Multiple recipients of list ORACLE-L Hi List , I am in the middle of Major configuration of JFS filesystem on HP-UX 11.0 . I came across the follwing info . Could someone validate this please ? The options you want are mount options available only with Online JFS (not a free product, unfortunately.) The options you want are: RBS TEMP files : mincache=dsync, convosync=dsync DATAFILES REDO files : mincache=direct, convosync=direct TIA . regards, RS -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: not able to dump buffers
Hi Arun, You have requested a BUFFER dump, rather than a BUFFERS dump. The BUFFER dump is intended to do a level 10 dump of a particular buffer. The level number to specify in the event syntax is the decimal tablespace relative data block address. Of course, there is no buffer with an RDBA of 10. That is why your trace file was empty. The levels for the BUFFERS dump are as follows. level 1 dump the buffer headers only level 2include the cache and transaction headers from each block level 3include a full dump of each block level 4 dump the working set lists and the buffer headers and the cache header for each block level 5include the transaction header from each block level 6include a full dump of each block Most levels high than 6 are equivalent to 6, except that levels 8 and 9 are the same as 4 and 5 respectively. For level 1 to 3 the information is dumped in buffer header order. For levels higher than 3, the buffers and blocks are dumped in hash chain order. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 27 April 2001 8:07 To: Multiple recipients of list ORACLE-L Hi, I am trying to take a buffer dump thro the following command alter session set events 'immediate trace name buffer level 10'; but all i am getting is a dump file which does not have any information on it can somebody let me know what i am missing here. the below said information is what there in the dump file Dump file g:\806_home\RDBMS80\trace\ORA00241.TRC Thu Apr 26 16:58:24 2001 ORACLE V8.0.6.0.0 - Production vsnsta=0 vsnsql=c vsnxtr=3 Windows NT V4.0, OS V5.101, CPU type 586 Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.6.0.0 - Production Windows NT V4.0, OS V5.101, CPU type 586 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 11 pid: f1 *** 2001-04-26 16:58:24.879 *** SESSION ID:(10.1) 2001-04-26 16:58:24.879 Dump of buffer cache at level 10 _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ARUN K C INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: multiple archive destinations
Hi Tom, You can have multiple destinations, but each log file is copied to all the destinations. You can also have multiple ARCn processes, but each log file is handled by a single ARCn process. So, you cannot do exactly as you wanted. However, there are two tips on the Ixora web site that be probably help somewhat. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 27 April 2001 1:51 To: Multiple recipients of list ORACLE-L RDBMS: 8.1.6.2 OS: AIX 4.3.3 Our large (600G) insert-intensive (17Meg / min) database has 24 on-line redo logs (8 each on 3 disks used in round-robin fashion) and occasionally has problems with the archiver being unable to keep up with the data fill rate. When this happens on-line redo logs fill faster than the ARC process can write them. I have successfully used more than one ARC process but of course all ARC processes are writing to the same destination (which becomes the limiting factor). What I'd really like to do is use more than one archive log destination - with 2 (or more) ARC processes - each writing to a separate destination. But reading through the manual it looks like the multiple archive destination facility was developed for multiplexing each redo log to more than one destination (for hot-standbys for example) - rather than each redo log to one of several destinations depending on which ARC process is working on it. Is there any way to accomplish my purpose? thanks, ..tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sort_area_size + temp tablespace extent sizing
Hi Ade and list, This is folk lore. Please see http://www.ixora.com.au/q+a/0103/27075321.htm for the explanation. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 25 April 2001 1:01 To: Multiple recipients of list ORACLE-L Hi List, Quick question regarding temp tablespace extent sizing. I have always assumed that the extent size for a temporary tablespace needs to be equal to the size that sort_area_size is set to. A sort that can't fit into memory then goes and grabs temp tablespace extents of a size equal to sort_area_size. However, I have read somewhere that the extent size should be equal to sort_area_size + block size (the block being used for header infomation or something like that) eg. 8k block size and 64k sort area size results in 72K temp tablespace extent size. So, assume I have a sort area size of 128K and block size of 8k, do I need to set temp tablespace extent sizes to 128K or 136K, or doesn't it really matter. Thanks, Ade -- The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Kdb database engine
Hi Guys, You are both right. In the early 90's I worked for Telstra directly as a developer (on an Informix application). I later went back there working for HP and then IBM as a Unix performance specialist. I also applied for an Oracle DBA job there once, but was not successful :-(. I did not hear of KDE-DB (KDB) in that environment, but it does not surprise me that they are using it. Telstra employs about 10% of Australia's IT workforce and when lamenting the lack of standards there we often used to say Telstra has at least one of everything! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 24 April 2001 7:26 To: Multiple recipients of list ORACLE-L Nope, I believe that Mr. Adams used to to work for HP, not for Telstra. Another participant of this group (Leng Kaing) used to work for Telstra. Leng is also an excellent DBA, with vast experience. -Original Message- Sent: Monday, April 23, 2001 1:06 PM To: Multiple recipients of list ORACLE-L M'Laddy of Gogala, Yes, good catch, the acronym can refer to some kernel debuggers, too. In this case, Kdb is the moniker for a superfast no-frills in-memory db. (Praps our own Mr. Adams knows something of this product, since it has sold to Telstra, where I *think* he used to work.) Seems like a small company with a very higly trained staff and nifty little product. - Ross || -Original Message- || From: Gogala, Mladen [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] || Sent: Monday, April 23, 2001 12:21 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Kdb database engine || || || I thought that kdb was a kernel debugger? || || -Original Message- || Sent: Monday, April 23, 2001 11:16 AM || To: Multiple recipients of list ORACLE-L || || || || Anyone out there using the Kdb database? || || -- || Please see the official ORACLE-L FAQ: http://www.orafaq.com http://www.orafaq.com || -- || Author: Gogala, Mladen || INET: [EMAIL PROTECTED] || || Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 || San Diego, California-- Public Internet access / || Mailing Lists || || To REMOVE yourself from this mailing list, send an E-Mail message || to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in || the message BODY, include a line containing: UNSUB ORACLE-L || (or the name of mailing list you want to be removed from). You may || also send the HELP command for other information (like subscribing). || -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Enqueue timeouts
Hi Iain, Let me quote from my book ... "Note also that the 'enqueue timeouts' statistic in V$SYSSTAT does not represent the number of enqueue wait timeouts. Rather, this statistic is incremented when an enqueue request or enqueue conversion is aborted entirely. This can be due to a distributed transaction timeout, but usually relates to locks requested in no-wait mode. ... It is sometimes suggested that 'enqueue_resources' should be increased to combat enqueue waits. But please note that there is absolutely no substance to this suggestion. Oracle will return an ORA-52 or ORA-53 error if it fails to find a free slot in the enqueue resources or enqueue locks fixed arrays respectively. Beyond that, the setting of the 'enqueue_resources' and '_enqueue_locks' parameters is unimportant." If you are really interested in setting these parameters, use V$RESOURCE_LIMIT. However, a more helpful course of action is to look at X$KSQST to investigate what type of enqueues those waits are coming from. There is a script called "enqueue_stats.sql" at http://www.ixora.com.au/scripts/locks.htm on the Ixora web site that might help. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 20 April 2001 21:40 To: Multiple recipients of list ORACLE-L Can anyone offer advice on what the problem may be with the following from v$sysstat on one of our databases. NAME VALUE -- - enqueue timeouts 17499101 enqueue waits446362 enqueue deadlocks 2 enqueue requests 47731729 enqueue conversions 1228500 enqueue releases 30232445 Enqueue_resources is set at 5000, DML_Locks is at 4000 and sessions at 412 so enqueue_resources looks high already but I came across a note which suggested increasing enqueue_resources where timeouts were occurring. Would anything else be causing this or is there a way to calculate what enqueue_resource should be set at. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Never split index and data files ...
Hi Allan, Thanks for that reference. It is a much better treatment than the Oracle paper on the matter. The Oracle paper says "Stripe all files across all disks using a one megabyte stripe width". The Sun paper stops short of saying "all ... all" which is a very significant difference. It says "As an extreme one could take every disk in the system, and stripe each table over every disk. In practice, it is more practical to break up all the disks into a few pools". It also says, "The database layout practice of keeping data and index separate is still useful, but using it for a first-order layout rule is a mistake." This is consistent with what I recommend in the series of tips on disk configuration on the Ixora web site. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 20 April 2001 21:20 To: Multiple recipients of list ORACLE-L There is also an article on Wide Thin Disk Striping at Sun Blue prints http://www.sun.com/software/solutions/blueprints/1000/layout.pdf which expands on this philosophy. Allan From: "Steve Adams" [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: "Never split index and data files ..." Date: Thu, 19 Apr 2001 23:25:25 -0800 Hi All, The author (Anjo Kolk) is an advocate of SAME (stripe and mirror everything). The SAME philosophy is that "everything" should be striped across all the disks available. Separating indexes from their tables is contrary to that philosophy. I don't agree with it, but that's where he's coming from anyway. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 20 April 2001 6:56 To: Multiple recipients of list ORACLE-L Whoaaa, I sure hope someone can, because I have never heard that before? Kev -Original Message- Ghosalkar Sent: Thursday, April 19, 2001 4:36 PM To: Multiple recipients of list ORACLE-L Guys, i was checking my statspack report on oraperf and i came across this statement. "Never split index and data files to different sets of disks." can anyone xplain the logic behind this. Thanks Mandar -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Never split index and data files ...
Hi Ed and list, Most of my bad experiences with SAME have been related to adding disk capacity, rather than its performance which is normally OK. The first time I hit it was about 5 years ago when someone had configured the 30 4G drives as a single striped and mirrored volume 15 disks wide, and then someone else had added 2 more mirrored pairs to allow for data growth. The new disks immediately became a hot spot because they had no striping and could not support the same concurrency as the rest of the system. My worst experience with SAME was an 800G data warehouse (noarchivelog mode) that had a requirement to fit a full cold backup into a 3 hour window. Because of the striping, the best backup performance they could get was single threaded! So their wonderful robotic tape library that could in theory backup 500G per hour was useless. As soon as there were 2 or more backup threads active the disk heads started thrashing and the tapes could not stream! To make matters worse, they had bought the disk farm concept, and there were four other unrelated applications sharing the same EMC arrays and one of those was a 24x7 emergency services thing so we could not reconfigure the disk arrays at all. It took 4 people about 3 months to work out a way to do the required reconfiguration from the Unix level in a 4-day outage window. When the time came, of course something went wrong and the whole change had to be backed out! The eventual solution was to spend several millions of dollars on a whole new bunch of hardware. Of course we did the configuration properly the next time so that there would be no disk or controller level contention between the backup threads. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Saturday, 21 April 2001 3:26 To: Multiple recipients of list ORACLE-L Dick, Don't take this the wrong way...it's NOT meant to be sarcastic: You said "SAME is a great theory, but I can't and haven't seen it perform well in practice, yet." My question to you: Have you seen it in practice at all? An actual working implementation? For that matter; has ANYONE seen it implemented in a production environment? I'm sure it must be somewhere, but I'm curious if anyone knows where. This is a subject that I'm really into right now...that's why I'm prodding a bit! Thanks, Ed Haskins Oracle DBA Verizon Wireless -Original Message- Sent: Friday, April 20, 2001 12:46 PM To: Multiple recipients of list ORACLE-L Steve, I heard about this SAME philosophy at the last NorthEast Oracle Users Group meeting from a individual who works on the utilities for Oracle in the New England Development Office. Although we did not get deeply into the philosophy, I'll agree that it is not the silver bullet, actually it can become a performance detractor. The individual who wrote the paper for Oracle (Anjo Kolk) is a LONG time Oracle person, actually wrote the core of the kernel, so I believe he's probably writing from a purely theoretical point of view. In that light what he's saying would be true, stripe mirror everything and theoretically you should never have an io bottleneck. BUT, many hardware platforms don't handle mirroring very well unless your using a disk array like EMC's. Now that handles the mirror internally so we've alleviated that problem, but EMC likes to break their drives into 'hyper volumes' so your stripping may or may not be across physical drives. Also your stripes can still have the bottle neck of the number of SCSI cards in the computer. In any case taking a little time to insure that redo logs, archive logs, indexes, and data are all REALLY spread out across devices is the only way to go. SAME is a great theory, but I can't and haven't seen it perform well in practice, yet. Dick Goulet Reply Separator Author: "Steve Adams" [EMAIL PROTECTED] Date: 4/19/2001 11:25 PM Hi All, The author (Anjo Kolk) is an advocate of SAME (stripe and mirror everything). The SAME philosophy is that "everything" should be striped across all the disks available. Separating indexes from their tables is contrary to that philosophy. I don't agree with it, but that's where he's coming from anyway. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 20 April 2001 6:56 To: Multiple recipients of list ORACLE-L Whoaaa, I sure hope someone can, because I have never heard that before? Kev -Original Message- Ghosalkar Sent: Thursday, April 19, 2001 4:36 PM To: Multiple recipients of list ORACLE-L Guys, i was checking my statspack report on oraperf and i came across this statement. "Never split index and data files to different sets of disks." can anyone xplain the logic behind this. Thanks Mandar -- Please see the official ORACLE-L FAQ: http
RE: Problem with DBMS_SQL
Hi Bhat, The CREATE TABLE privilege probably needs to be granted directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ PS. Please don't copy "[EMAIL PROTECTED]" on questions to the list. -Original Message- Sent: Wednesday, 18 April 2001 19:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Hi Gurus, I am experincing a problem with a procedure containing DBMS_SQL to create a table. On execution of the script I get the message PL/SQL procedure successfully completed, but the table doesn't get created. In the error log file I can see ORA-01031: insufficient privileges message. Any ideas. HP-UX : Oracle 7.3.4.4.1 Thanks. - Bhat Here is the procedure create or replace PROCEDURE create_table_mbn015 IS dyn_sql LONG; cid INTEGER; a integer; b varchar2(100); abcdinteger; BEGIN cid := DBMS_SQL.OPEN_CURSOR; dyn_sql := 'CREATE TABLE mbn015 STORAGE (INITIAL 5M NEXT 5M) TABLESPACE MUGDBDATA1 AS( SELECT DISTINCT p.item, p.loc, p.cppprodmethod, c.loadoffsetdur, p.scheddate, (p.scheddate - c.loadoffsetdur/1440) calcdate FROM stsc.planorder p,stsc.cppprodmethodstep c WHERE p.item = c.item AND p.loc = c.loc AND p.cppprodmethod = c.cppprodmethod AND c.stepnum = 20)'; DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7); abcd := DBMS_SQL.EXECUTE(cid); dbms_output.put_line(abcd); DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); a := sqlcode; b := substr(sqlerrm,1,100); INSERT INTO errors VALUES (sysdate, 'A:CT', a, b); END create_table_mbn015; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Problem with DBMS_SQL
Hi Bhat,
DBA is a role under Oracle7 (although is was a system privilege under version
6). Roles are not effective in stored procedures. Invoker's rights changes that
somewhat in 8i, but for now you have to grant the system privilege directly to
the procedure owner. You cannot rely on privileges obtained via roles.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Wednesday, 18 April 2001 21:16
To: Multiple recipients of list ORACLE-L
Hi Steve,
The account has DBA privilege and by using a direct DDL I am able to create
the table.
Anything else I can check-up.
Thanks,
- Bhat
-Original Message-
From: Steve Adams [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 18, 2001 7:01 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Problem with DBMS_SQL
Hi Bhat,
The CREATE TABLE privilege probably needs to be granted
directly.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
PS. Please don't copy "[EMAIL PROTECTED]" on questions
to the list.
-Original Message-
Sent: Wednesday, 18 April 2001 19:14
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Hi Gurus,
I am experincing a problem with a procedure containing
DBMS_SQL to create a
table.
On execution of the script I get the message PL/SQL
procedure successfully
completed, but the table doesn't get created. In the error
log file I can
see ORA-01031: insufficient privileges message. Any ideas.
HP-UX : Oracle 7.3.4.4.1
Thanks.
- Bhat
Here is the procedure
create or replace PROCEDURE create_table_mbn015 IS
dyn_sql LONG;
cid INTEGER;
a integer;
b varchar2(100);
abcdinteger;
BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
dyn_sql := 'CREATE TABLE mbn015
STORAGE (INITIAL 5M NEXT 5M)
TABLESPACE MUGDBDATA1
AS( SELECT DISTINCT p.item, p.loc,
p.cppprodmethod, c.loadoffsetdur,
p.scheddate,
(p.scheddate - c.loadoffsetdur/1440) calcdate
FROM
stsc.planorder p,stsc.cppprodmethodstep c
WHERE p.item = c.item AND p.loc = c.loc AND
p.cppprodmethod
= c.cppprodmethod AND c.stepnum = 20)';
DBMS_SQL.PARSE(cid, dyn_sql, dbms_sql.v7);
abcd := DBMS_SQL.EXECUTE(cid);
dbms_output.put_line(abcd);
DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
a := sqlcode;
b := substr(sqlerrm,1,100);
INSERT INTO errors VALUES (sysdate, 'A:CT', a, b);
END create_table_mbn015;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858)
538-5051
San Diego, California-- Public Internet access /
Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).
You may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see th
RE: Bitmap Indexes sizes
Hi Darren, Yes, this sort of space blow-out is to be expected for a bitmap index on a volatile table. It is not so bad with more recent versions, and the MINIMIZE RECORDS PER BLOCK syntax can help. If the snapshot is manually refreshed, you will do better to drop this index prior to each refresh. Otherwise, a simple (aka B*-tree) index should be considered. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 19 April 2001 8:15 To: Multiple recipients of list ORACLE-L I have (had) an index that according to index_stat (after analyze index) and dba_extents that reported to be approximately 53 Mb and took up 11 extents. The percentage increase is 50. I dropped the index and recreated it, after I adjusted the initial extents, it turns out the final size is less then 500k. This seems like a large amount for it to drop. The index is on a snapshot within our data warehouse. Has anybody else seen this ?? Is this a problem with bitmap indexes. ?? Thanks Darren Darren Browett P.EngThis message was transmitted Systems Admin/DBA using 100% recycled electrons Information and Communications Technology. City of Coquitlam P:(604) 927 - 3614 E:[EMAIL PROTECTED] - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Ability for non DBA user to kill session.
Hi All, Further to that, it is necessary to use DBMS_SQL or native dynamic SQL for this as PL/SQL does not support ALTER SYSTEM directly. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 18 April 2001 3:37 To: Multiple recipients of list ORACLE-L Create a procedure as SYS (or someone else powerful) which does the 'alter system' and then grant just the proc to the user hth connor --- lerobe - Lee Robertson [EMAIL PROTECTED] wrote: All, Is there a method for allowing a non DBA user to kill their own (and only their own) session. I have had a trawl through Metalink and have seen various methods (using procedures) of doing it but all of these appear to rely on granting the alter system role to the user. Oracle 8.0.5.0.0 Compaq Tru64 4.0f Regards Lee Lee Robertson Acxiom Tel:0191 525 7344 Fax:0191 525 7007 Email: [EMAIL PROTECTED] The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Background Process
Hi Raj, These are I/O slaves. You get them by setting 'dbwr_io_slaves' and possibly other similar parameters. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 12 April 2001 21:52 To: Multiple recipients of list ORACLE-L DBAs I facing severe IO on the server. I got no clue why. I find three oracle background processes which I never heard of. They are ora_i101_orcl, ora_i102_orcl, ora_i201_orcl. Any idea what is this? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer theory: Question on access paths for outer joins
Hi Jay, Under rule based optimization deficient (outer joined) relations are always last in the join order. In general cost based optimization will do the same, but it is not bound to do so. For example, if T2 below is a complex view, then it cannot be merged into the parent query because it is being outer joined. That would mean that the view must be instantiated and accessed via a sort-merge join or hash join or used to drive query. If so, the optimizer may well choose to instantiate the view and drive the query as a nested loops join from there. However, in general it will no do so without a good reason. In this case however I suspect that the problem is that at least one of the in-line views T0 and T1 are mergeable. Because the merging of in-line views is done before query optimization, the optimizer never gets to consider the cardinality of the potential instantiations of the in-line views. The optimizer is presented with a merged query involving all the base tables for the merged views and PHONE and ACCOUNT. Worse than that, because transitivity analysis is not done for join predicates, you end up with an almost linear join topology. Also, because PHONE and T2 are outer joined, OR expansion and IN-list iterators cannot be used (lest duplicates might be introduced) to obtain index-based access paths to the other base tables involved and thus driving the query from PHONE (or from an index on ACCOUNT with your extraneous predicate) is natural. If this analysis is right, and you are right about the cardinality of the in-line views, the correct approach to optimizing this query is to place a NO_MERGE hint in the query block for the relevant in-line views, move T2 to the end of the from clause, and place ORDERED and USE_HASH(T2) hints in the outer query block. Hope this help, @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ac.id = ph.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to PHONE. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Optimizer theory: Question on access paths for outer joins
Hi Jay, Under rule based optimization deficient (outer joined) relations are always last in the join order. In general cost based optimization will do the same, but it is not bound to do so. For example, if T2 below is a complex view, then it cannot be merged into the parent query because it is being outer joined. That would mean that the view must be instantiated and accessed via a sort-merge join or hash join or used to drive query. If so, the optimizer may well choose to instantiate the view and drive the query as a nested loops join from there. However, in general it will no do so without a good reason. In this case however I suspect that the problem is that at least one of the in-line views T0 and T1 are mergeable. Because the merging of in-line views is done before query optimization, the optimizer never gets to consider the cardinality of the potential instantiations of the in-line views. The optimizer is presented with a merged query involving all the base tables for the merged views and PHONE and ACCOUNT. Worse than that, because transitivity analysis is not done for join predicates, you end up with an almost linear join topology. Also, because PHONE and T2 are outer joined, OR expansion and IN-list iterators cannot be used (lest duplicates might be introduced) to obtain index-based access paths to the other base tables involved and thus driving the query from PHONE (or from an index on ACCOUNT with your extraneous predicate) is natural. If this analysis is right, and you are right about the cardinality of the in-line views, the correct approach to optimizing this query is to place a NO_MERGE hint in the query block for the relevant in-line views, move T2 to the end of the from clause, and place ORDERED and USE_HASH(T2) hints in the outer query block. Hope this help, @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ac.id = ph.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to PHONE. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? - You can view this message online at http://www.ixora.com.au/q+a/0104/11164729.htm To unsubscribe from Ixora Answers send a blank email to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Parallel Query Question
Hi Chuck, I think this should do what you want. select p.inst_idcoord_instance, p.indx coord_pid, s.inst_idslave_instance, s.kxfpdpnum slave_pid from x$kxfpdp s, x$ksupr p where s.kxfpdpnum != 999 and p.addr = s.kxfpdpcpr and p.inst_id = s.kxfpdpcin order by 1, 2, 3, 4 / @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 11 April 2001 7:16 To: Multiple recipients of list ORACLE-L Is there a way to associate parallel query processes with the session that's running the query? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DONE script enclosed: shell script to compare nos.
Hi Raja,
I have not been following this thread until now, but the assumption that each
minor point number will be two digits rather than one may not be valid. Also, if
your objective is to do it entirely in the shell, consider using IFS to avoid
the cut as follows.
#!/bin/sh
v1=$1; v2=$2
ifs="$IFS"; IFS=.
set $v1; v1a=$1; v1b=$2; v1c=$3
set $v2; v2a=$1; v2b=$2; v2c=$3
IFS="$ifs"
if [ "0$v1a" -eq "0$v2a" ] ; then
if [ "0$v1b" -eq "0$v2b" ] ; then
if [ "0$v1c" -eq "0$v2c" ] ; then
echo "$v1 is the same as $v2"
elif [ "0$v1c" -lt "0$v2c" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
elif [ "0$v1b" -lt "0$v2b" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
elif [ "0$v1a" -lt "0$v2a" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 12 April 2001 11:55
To: Multiple recipients of list ORACLE-L
Hello every one,
Thanks for the awk script sent before, I could not use ask as some sites dont
have awk but have nawk, so my script will fail. So alternatively devised this
shell script, which does the job:-
#!/bin/sh
olddir="7.04.03"
olddir1="`echo $olddir| cut -c1 `"
olddir2="`echo $olddir| cut -f2 -d"." `"
olddir3="`echo $olddir| cut -f3 -d"." `"
if [ "$olddir3" = "" ]; then
olddir3=00
fi
fullversion="$olddir1""$olddir2""$olddir3"
VER="7.05"
ver1="`echo $VER| cut -c1 `"
ver2="`echo $VER| cut -f2 -d"." `"
ver3="`echo $VER| cut -f3 -d"." `"
if [ "$ver3" = "" ]; then
ver3="00"
# echo "The third version value is $ver3"
fi
fullver="$ver1""$ver2""$ver3"
if [ "$fullversion" -gt "$fullver" ];then
echo "The $olddir version is greater than $VER version"
elif [ "$fullversion" -lt "$fullver" ];then
echo "The $olddir version is less than $VER version"
else
echo "Both versions are equal"
fi
==
Basically I strip away the release nos. like instead of accepting 7.04.03, I
accept it as 70403 and then compare and output.
Thanks for help.
Regrads,
Raja
On Wed, 11 Apr 2001 02:30:36
lerobe - Lee Robertson wrote:
Why not simply do the following passing two parameters to the script eg.
call the script test.sh and call it so
test.sh 2 1
This results in output "VAR1 is greater than VAR2"
then
test.sh 1 2
this results in output "VAR2 is greater than VAR1"
Cheers.
#!/bin/ksh
VAR1=$1
VAR2=$2
if [ "$VAR1" -gt "$VAR2" ]
then
echo "VAR1 is greater than VAR2"
else
echo "VAR2 is greater than VAR1"
fi
-Original Message-
Sent: 11 April 2001 10:01
To: Multiple recipients of list ORACLE-L
Hi
I'm sure there are better ways but awk does the trick
echo $var1 $var2 | awk '{ if ( $1 $2 ) { print "1"} else { print"0" } }'
cheers alex
Hello,
I need some help in comparing 2 nos. in a unix shell.
1) e.g var1="7.04.03"
and var2="7.05"
I want to test:-
if [ "$var2" "$var1" ];then
then $var2 version is greater than $var1"
fi
2) 2nd condition is :-
var1="7.04.03"
var2="7.04.02"
I want to test:-
if [ "$var1" "$var2" ];then
then $var1 version is greater than $var2"
fi
Can some one send me a code?
How can I do the above in shell? Can you please help me, a seemingly
simple
one!
Raja
Get 250 color business cards for FREE! at Lycos Mail
http://mail.lycos.com/freemail/vistaprint_index.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex Apostolopoulos
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 t
RE: DONE script enclosed: shell script to compare nos.
Hi Raja,
Yes, IFS stand for the "Internal Field Separator".
It is a special shell variable in Bourne based shells.
See the sh(1) man page for more information about it.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Thursday, 12 April 2001 14:01
To: Steve Adams
Steve,
This is another way of doing it, but for my purposes, the software minor
release no. will always be, of the form nn, that is 01. In that case, the script
will work, but the one that you have sent also works, I liked the concept of
IFS, where can I read about that, it must be standing for I something Field
Seperator?
Did a man on vi, but would not give me that. I think I will look at some
appendix or some thing.
Rgds,
Raja
--
On Thu, 12 Apr 2001 12:27:16
Steve Adams wrote:
Hi Raja,
I have not been following this thread until now, but the assumption that each
minor point number will be two digits rather than one may not be valid. Also,
if
your objective is to do it entirely in the shell, consider using IFS to avoid
the cut as follows.
#!/bin/sh
v1=$1; v2=$2
ifs="$IFS"; IFS=.
set $v1; v1a=$1; v1b=$2; v1c=$3
set $v2; v2a=$1; v2b=$2; v2c=$3
IFS="$ifs"
if [ "0$v1a" -eq "0$v2a" ] ; then
if [ "0$v1b" -eq "0$v2b" ] ; then
if [ "0$v1c" -eq "0$v2c" ] ; then
echo "$v1 is the same as $v2"
elif [ "0$v1c" -lt "0$v2c" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
elif [ "0$v1b" -lt "0$v2b" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
elif [ "0$v1a" -lt "0$v2a" ] ; then
echo "$v1 is less than $v2"
else
echo "$v1 is greater than $v2"
fi
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
From: Viraj Luthra [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 12 April 2001 11:55
To: Multiple recipients of list ORACLE-L
Subject: DONE script enclosed: shell script to compare nos.
Hello every one,
Thanks for the awk script sent before, I could not use ask as some sites dont
have awk but have nawk, so my script will fail. So alternatively devised this
shell script, which does the job:-
#!/bin/sh
olddir="7.04.03"
olddir1="`echo $olddir| cut -c1 `"
olddir2="`echo $olddir| cut -f2 -d"." `"
olddir3="`echo $olddir| cut -f3 -d"." `"
if [ "$olddir3" = "" ]; then
olddir3=00
fi
fullversion="$olddir1""$olddir2""$olddir3"
VER="7.05"
ver1="`echo $VER| cut -c1 `"
ver2="`echo $VER| cut -f2 -d"." `"
ver3="`echo $VER| cut -f3 -d"." `"
if [ "$ver3" = "" ]; then
ver3="00"
# echo "The third version value is $ver3"
fi
fullver="$ver1""$ver2""$ver3"
if [ "$fullversion" -gt "$fullver" ];then
echo "The $olddir version is greater than $VER version"
elif [ "$fullversion" -lt "$fullver" ];then
echo "The $olddir version is less than $VER version"
else
echo "Both versions are equal"
fi
==
Basically I strip away the release nos. like instead of accepting 7.04.03, I
accept it as 70403 and then compare and output.
Thanks for help.
Regrads,
Raja
On Wed, 11 Apr 2001 02:30:36
lerobe - Lee Robertson wrote:
Why not simply do the following passing two parameters to the script eg.
call the script test.sh and call it so
test.sh 2 1
This results in output "VAR1 is greater than VAR2"
then
test.sh 1 2
this results in output "VAR2 is greater than VAR1"
Cheers.
#!/bin/ksh
VAR1=$1
VAR2=$2
if [ "$VAR1" -gt "$VAR2" ]
then
echo "VAR1 is greater than VAR2"
else
echo "VAR2 is greater than VAR1"
fi
-Original Message-
Sent: 11 April 2001 10:01
To: Multiple recipients of list ORACLE-L
Hi
I'm sure there are better ways but awk does the trick
echo $var1 $var2 | awk '{ if ( $1 $2 ) { print "1"} else { print"0" } }'
cheers alex
Hello,
I need some help in comparing 2 nos. in a unix shell.
1) e.g var1="7.04.03"
and var2="7.05"
I want to test:-
if [ "$var2" "$var1" ];then
then $var2 version is greater than $var1"
fi
2) 2nd condition is :-
var1="7.04.03"
var2="7.04.02"
I want to test:-
if
RE: Histogram Helper
Hi Steve, Good idea, but unfortunately the table statistics and the basic column statistics (called single-bucket histograms) that are available if you only analyze the table are not sufficient to determine whether the distribution of values for any column is uniform or skewed. That means that we cannot do this with a data dictionary query; we will have to scan the table. Based on your suggestion I have written "consider_histogram.sql" at http://www.ixora.com.au/scripts/query_opt.htm#consider_histogram that will scan a table and do the analysis for a single column only. The fact that it has to do a full scan each time you run it will probably make you use it selectively on columns that are subject to literal predicates in expensive queries. In my opinion, that's probably a good thing. Enjoy! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 6 April 2001 9:17 To: LazyDBA mailing list I just experienced an incredibly DRAMATIC performance boost with a well-placed histogram! Now I'm hungry for "Histogram Helper." I'd like to analyze the database and identify other possible candidates for histograms. (Then maybe review the code after that. I'm using CHOOSE optimization with automatic statistics gathering on all tables and indexes.) Here's a quote from the Oracle Tuning Guide: "In general, you should create histograms on columns that are frequently used in WHERE clauses of queries and have a highly skewed data distribution. For many applications, it is appropriate to create histograms for all indexed columns because indexed columns typically are the columns most often used in WHERE clauses." Has anyone ever created histograms for all indexed columns as suggested above? You could query DBA_INDEXES (or DBA_IND_COLUMNS) and create dynamic SQL as follows: analyze table TBL_NAME compute statistics for all indexed columns size 10; Better yet, you could set the "method_opt" parameter of the DBMS_STATS.GATHER_SCHEMA_STATS procedure to "all indexed columns size 100." But isn't that brute force? I'm thinking our histogram helper should be a little more sophisticated by factoring in an analysis of how skewed the data is using the num_distinct, density, num_buckets, and last_analyzed columns from dba_tab_columns. Finally, histogram helper should be automated to ensure our statistics are maintained. Any ideas or personal experiences with histograms? Any white papers on implementing and managing histograms? TIA! Steve Orr Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What does _allow_read_only_corruption do?
Hi Tom, If you have a media failure and for some reason (such as having lost an archived log file) you cannot perform a complete recovery on some datafiles, then you might need this parameter. It is new for 8i. Previously there was only '_allow_resetlogs_corruption' which allowed you to do a RESETLOGS open of the database in such situations. Of course, a database forced open in this way would be in a crazy state because the current SCN would reflect the extent of the incomplete recovery, but some datafiles would have blocks in the future, which would lead to lots of nasty ORA-00600 errors (although there is an ADJUST_SCN event that could be used for relief). Once in this position, the only thing to do would be to do a full database export, rebuild the database, import and then assess the damage. The new '_allow_read_only_corruption' provides a much cleaner solution to the same problem. You should only use it if all other recovery options have been exhausted, and you cannot open the database read/write. Once again, the intent is to export, rebuild and import. Not pleasant, but sometimes better than going back to an older usable backup and performing incomplete recovery to a consistent state. Also, the read only open allows you to assess better which recovery option you want to take without committing you to either. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 6 April 2001 8:32 To: Multiple recipients of list ORACLE-L Yes, I know it's a hidden parameter that should only be used at the advice of counsel and Oracle Support. But has anyone used it? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tom Pall (E-mail) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: tkprof for 10046 event
Hi Steve, I've not checked recently, but Guy Harrison used to have one on his web site. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 6 April 2001 1:52 To: Multiple recipients of list ORACLE-L Hi, When setting event 10046 at level 8 it produces a trace file with wait events. Does anyone have a utility like tkprof which summarises the trace file to show a summary of wait events and timings per sql statement? Thanks, Steve Wilkes ___ Oracle DBA npower email:[EMAIL PROTECTED] = This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify gpupower.co.uk or [EMAIL PROTECTED] This outgoing e-mail (and any attachments) has been checked (using Sophos Sweep 3.44 + patches) before leaving us (UK 08457 353637), and has been found to be clean from any virus infection. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wilkes, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Enqueue waits
Hi Pablo, The TM and TX waits are probably normal application tuning issues. You'll find some tips about reducing ST enqueue waits on the Ixora web site, MetaLink and elsewhere. The CU and SQ waits are relatively few and probably relatively brief, nevertheless there can be performance issues with these but I would focus on the others first. In general, the Anjo Kolk paper on "Oracle7 Wait Events and Enqueues" is the best starting point for information about different enqueue types. You can find it at http://www.evdbt.com/event.pdf @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 5 April 2001 0:26 To: Multiple recipients of list ORACLE-L Hi List. Enqueue waits is on the TOP of my wait list. Event time_waited average_wait - --- enqueue21685596396.93 Here are the details: TY GETS WAITS -- - - CF 104 0 CI 10788 0 CU 25388 7 DL 259 0 DR 149 0 DX 56457 0 IS 180 0 MR 130 0 RT 1 0 SQ 2660 3 ST 10078 221 TY GETS WAITS -- - - TM398703 5 TS 13669 0 TX288025 116 UL 4923 0 US 43960 0 WL16 0 This is a Oracle Financials application I'm tuning. Can anybody explain what these types of ENQUEUE mean? And how can I avoid them. TIA ___ Do You Yahoo!? Enva mensajes instantneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WAIT info in event10046 raw traces
Hi Danisment, It is after each wait. Whether each wait corresponds to a single system call depends on whether the file is multiplexed, and whether Oracle passes large multiblock reads to the operating system in a single call on that platform. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 3 April 2001 21:10 To: Multiple recipients of list ORACLE-L Hello, When is WAIT information written to event10046 raw trace file, after each WAIT or another mechanism such as cummulative writes after a specified count ? If it's after each WAIT,for example, we will be able to say, each IO related wait event in trace file is counterpart of one IO system call. and some P3 values will always be 1. thanks in advance... = --- Danisment Gazi Unal Web: http://www.geocities.com/danisment --- __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Qualifying columns to improve performance?
Hi Arn, There may be good reasons for explicitly qualifying all column references, but performance is not one of them, at least under Oracle 8i. I've just done some tests and there is absolutely no difference in the number of dictionary cache gets required during the parse, and no measurable difference in CPU usage. I too remember being taught this back in version 6 days, and it is in the Gurry and Corrigan "Oracle Performance Tuning" book (2nd edition, page 138) so there may have been some validity to it in the past. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 3 April 2001 12:30 To: Multiple recipients of list ORACLE-L At a course some years ago, we were told that in join statements, we should qualify ALL our column names with the appropriate table name, not just those that may be ambiguous. The reason was that the parser would not need to spend time checking multiple tables to determine the table to which each column belongs. Is this still a valid rule? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Metalink Response ....FYI
Hi All, I'd say that there must be something badly wrong with the application if it cannot cope with 1,500 concurrent users. I know of sites supporting more than 20 times that number of active users on a single server with consistent sub-second response times. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 4 April 2001 0:35 To: Multiple recipients of list ORACLE-L Hi Folks , I know lot of Metastink info floating around...so I thought I would post this for fun/info . I got this response from Metastink .. "Thank you for your comments on Metalink, the more feedback we get the better we here at Oracle Corporation will be able to service your needs. We have seen tremendous increase in customer usage of Metalink over the past 6 months. There are now about 240,000 registered users and at times we have over 1,500 active users. We know that the service does not meet our or customers expectations. With this in mind we have added capacity to our database servers and have also increased the number of middle tier application servers. Our experience is that the demand for online services has become so strong that as soon as we bring in more capacity, it is quickly consumed. We have also experience intermittent network and architecture problems that have caused several failovers from one server to the other resulting in slower performance than we expect. When these events occur, we strive to understand root cause and take corrective and preventive action. Sometime before summer, we exp!!ect to migrate the online services to Oracle's Customer Relationship Management (CRM) suite enabling us to deliver more to our customers.This is a global issue for us and it has great attention within Oracle management. We recognize how valuable our customers are to us and understand the immense value that Metalink can bring to them. We are working to meet your needs as fast as we can. Thank you for your patience as we continue to work to bring you the best in online support services." Interesting.won't you say..: ) RS __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Sakthi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Metalink Response ....FYI
Hi Eric, The one I know best is a chat site. The application is much more sophisticated than MetaLink. It has seen also seen "unprecedented demand" recently - about 500% growth in the last year. There have been growing pains, mostly caused by Oracle bugs, but they certainly seem to be able to make their Oracle stuff scale much better than MetaLink does. Maybe its because of the performance consultant they use. I understand that Oracle management politely declined an offer of help from the same consultant. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 4 April 2001 5:22 To: Multiple recipients of list ORACLE-L Steve, Thanks for the very interesting comments. Are those webified apps with similar requirements as metalink? On 3 Apr 2001, at 7:26, Steve Adams wrote: I'd say that there must be something badly wrong with the application if it cannot cope with 1,500 concurrent users. I know of sites supporting more than 20 times that number of active users on a single server with consistent sub-second response times. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Viewing current enabled roles
Hi Steve, There is no way to find out which roles are enabled in a session, other than your own. The reason is that this information is kept in each session's UGA, and for dedicated connections the UGA resides in the PGA which is in private memory, not visible to the rest of the instance. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 30 March 2001 19:20 To: Multiple recipients of list ORACLE-L Hi, I want to find out what roles are currently enabled for users logged on. The users are logging into Oracle from an application using a Web front end which issues 'set role ...' for them. I know about default_role in dba_role_privs which shows what roles are set by default at logon. Also about dbms_session.is_role_enabled which is only useful for your own session. Neither of these give me the information I am after. Any ideas? Thanks, Steve Wilkes ___ Oracle DBA npower email:[EMAIL PROTECTED] = This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify gpupower.co.uk or [EMAIL PROTECTED] This outgoing e-mail (and any attachments) has been checked (using Sophos Sweep 3.43 + patches) before leaving us (UK 08457 353637), and has been found to be clean from any virus infection. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wilkes, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Redo Log size used
Hi Glenn, Try 'log_file_usage.sql' at http://www.ixora.com.au/scripts/redo_log.htm#log_file_usage. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Tuesday, 27 March 2001 5:51 To: Multiple recipients of list ORACLE-L What can I query to see how much of the redo log file is currently in use? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Redo Log size used
Hi Glenn,
Yes, your query is fine, and does just what you think.
Please see http://www.ixora.com.au/q+a/0102/06135327.htm for the meaning
of cpodr_bno.
Please see http://www.ixora.com.au/tips/tuning/log_buffer_size.htm for
the relationship between log blocks and the log buffer.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Tuesday, 27 March 2001 7:27
To: Steve Adams
Cc: Oracledba
As always your script was exactly what I was looking for. I am assuming
the
column sys.x_$kccle.lesiz is number of blocks which make up the logfile,
and
sys.x_$kcccp.cpodr_bno is number of blocks in the log file currently in
use,
although I can't decipher the column name meaning. Using your
http://www.ixora.com.au/scripts/redo_log.htm#log_block_size.sql script,
I
was able to see the number of bytes in a redo log block. Again, I am
assuming it is bytes (why is the redo log block size different from the
log
buffer size (set in the init.ora file)?). What exactly is the
sys.x_$kccle.lebsz column?
I wrote this query to give me the number of MB currently in use by in
the
redo log file.
select
le.leseq log_sequence#,
le.lesiz * le.lebsz /1024 /1024 logmbtotal,
cp.cpodr_bno * le.lebsz /1024 /1024 logmbinuse
from
sys.x_$kcccp cp,
sys.x_$kccle le
where
le.inst_id = userenv('Instance') and
cp.inst_id = userenv('Instance') and
le.leseq = cp.cpodr_seq
Am I making the correct assumptions here?
-Original Message-
From: Steve Adams [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 26, 2001 3:26 PM
To: [EMAIL PROTECTED]
Cc: Oracledba
Subject: RE: Redo Log size used
Hi Glenn,
Try 'log_file_usage.sql' at
http://www.ixora.com.au/scripts/redo_log.htm#log_file_usage.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
From: Glenn Travis [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 27 March 2001 5:48
To: [EMAIL PROTECTED]
Subject: Redo Log size used
What can I query to see how much of the redo log file is currently in
use?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: APPEND hint (Was: Which is faster??)
Hi Yong, I don't think that's right. Try it on a table with no indexes, and dump the redo and undo blocks afterwards. My tests show that there is no row level redo (layer 11) except against the data dictionary tables for space management, regardless of whether the table or tablespace is defined as NOLOGGING. The undo is correspondingly trivial. However, if there are indexes then you get lots of index redo (layer 10) and corresponding undo. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Sunday, 25 March 2001 15:18 To: Multiple recipients of list ORACLE-L Hi, Connor, The append hint to insert does not disable generating rollback info. It does stop redo generation for a nologging table. Yong Huang [EMAIL PROTECTED] you wrote: If you're on 8.0 or higher, try insert /*+ APPEND */ into table select * from other_table; where "table" is defined as nologging. Then you won't hit either redo logs or rollback segments..Its the equivalent of a sqlldr direct load hth connor __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Max phy I/O size on Hp-UX
Hi Prasad, A new tuneable scsi_maxphys was introduced in patch PHKL_13552 in response to SR 4701376087. The patch notes say ... This value was previously fixed at 1MB (1048576). On V-Class systems, this value can be set as high as 32MB (33554432) for large raw SCSI disk transfers. Most systems won't need the value set this high, so the recommended value should be set to the SCSI tape max of 16M-1 (16777215) for V-Class. Non V-Class systems should remain with the system default of 1048576. To enable large record support for SCSI tape drives with the stape driver, the new st_large_recs tunables must also be set to 1. As you can see the issue here is tape I/O, not disk I/O, and although the driver limitation mentioned in param.h has been relaxed (for tape I/O) I think that it is effectively unchanged for disk I/O because of the LVM logical track group size restriction. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, 23 March 2001 7:17 To: Multiple recipients of list ORACLE-L This question regarding max physical I/O size for hp-ux v11. When we were talking to unix sys admin, she searched in HP knowledge base and found that 256k is the maximum physical I/O for hp-ux. When I was looking at QA on Steve Adams's web site, the answer is 1M for max physical i/o size for hp-ux. Steve, Could you please let me know if I am missing some thing here. From Steve Adam's Web site: Maximum I/O size 4 January 2000 What is the maximum I/O size (MAXPHYS) nowadays on HP-UX and Solaris? MAXPHYS has long been fixed at 64K on Solaris, and 256K on HP-UX. However, from Solaris 2.6 it is defined in /etc/system and defaults to 128K. On HP-UX 11 it now defaults to 1M. However, I think the LVM layer still constrains I/O operations to a single logical track group, which is 256K, so the higher MAXPHYS only applies if you are not using LVM, which is most unusual. Of course, these large physical I/O sizes are only possible if you are using raw or direct I/O. Perhaps more importantly, there is an internal Oracle kernel constant (SSTIOMAX) that limits I/O operations to 512K. From Knowledge base on HP Web site What is MAXPHYS for HP-UX systems? DocId: KBRC3216 Updated: 7/31/00 7:37:00 AM PROBLEM What is the the maximum size of a single I/O (or MAXPHYS) that can be issued to a device on an HP-UX system? RESOLUTION MAXPHYS is not a tunable on HP-UX systems but it defaults to 256KB. This can be seen in one of the system header files: 1. vi /usr/include/machine/param.h 2. Search for MAXPHYS This should return: #define MAXPHYS (256 * 1024)/* Maximum size of physical I/O transfer */ So MAXPHYS is 256KB. This is not tunable to larger value due to driver restrictions. Thanks in advance for all your comments. Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tuning Matter == Parameter PCT_USED in a Table
Hi Bambang, Analyze the table and you'll find the average row length in USER_TABLES.AVG_ROW_LEN. Subtract 90 bytes from the block size before expressing that as a percentage and round it up. Also, the formula should be PCT_USED = 100 - (PCT_FREE + 1 row). There are scripts on the Ixora web site that do this sort of stuff. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 23 March 2001 11:46 To: Multiple recipients of list ORACLE-L Dear Listers, I've download a presentation talking about Calculating PCT_USED . the formula is quite simple : PCT_USED + PCT_FREE 100 PCT_USED = PCT_FREE + 1 row size but I have no idea to calculate the size of 1 row in a table . is there anyone who knows to calculate it ? or any alternative formula to calculate PCT_USED ? Thanks a lot in advance : ) =bambang= Bambang Setiawan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bambang Setiawan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBA Job at a Chocolate Factory
Hi Steve, I once got to work on an oil rig in the middle of Bass Strait. The pay was based on how dangerous your work was, rather than the skill required. It is not often that being a DBA puts you at the bottom of the pay scale! @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Thursday, 22 March 2001 2:07 To: Multiple recipients of list ORACLE-L Seven years ago I was offered an Oracle DBA job at a chocolate factory and turned it down. (Please don't tell Rachel, she already thinks I'm crazy.) I had two simultaneous offers for the same salary and took the one closer to home. While on the interview they gave me the full tour of the factory starting with the bags of cocoa beans on the docks. The smell was intoxicating and even wafted into the office area where I would have worked. They had unlimited stashes of chocolate in every cubicle. I asked the employees if they ever got sick of chocolate and the answer was a resounding NO. Now my kids will never forgive me for not having taken the job at the chocolate factory. This begs the question... Where and what are some of the most interesting Oracle DBA jobs out there? I heard of one deep underground at a mining operation in the middle of the Nevada desert. Database is a basic technology that can be applied to just about any business anywhere. Any interesting stories? Personal experiences? Steve Orr -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Orr INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: instance resource allocation
Hi Grant, I suppose those 4 processors and 4G of RAM are all on one system board so that configuring the hardware as two distinct domains is not an option. Other than purchasing more hardware, you may want to consider Sun's "Solaris Resource Manager" which you can read about at http://www.sun.com/software/solaris/ds/ds-srm/. Unfortunately, I have no experience with it so I can add anything to the marketing information. Maybe others can. Meanwhile, make sure that you are using priority_paging so that intense demand for file system pages by one instance will not page out anonymous and executable pages from the other. @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, 1 March 2001 12:06 To: Multiple recipients of list ORACLE-L Group, being fairly new to Oracle (and this list) I have what I consider (and others may dare to differ) a valid question(running with 8i on Solaris 2.6) How do you manage the division of resources between 2 instances located on the same host? We have two oracle 8i instances (and a number of other applications) running on the same server. One in particular is drastically hogging the bulk of the server resources thus impeding the performance of the other instance and related applications As far as memory conflicts go, we're running an ad hoc query system on one instance, which means it can get resource hungry and can only be tuned/designed to an extent. The issue is that the resources it can grab should be limited, so they don't eat into a minimum resource allocation that the other instance should get. I am not aware of a way to use the DBMS_RESOURCE_MANAGER across two instances. Is there anything we can do here? I'm not sure how we specify how the two instances relate in terms of resourcesperhaps something in the init.ora file? Our Oracle environment resides on an exclusive E10K domain with 4 dedicated (400mhz) processors, 4GB RAM. TIA Grant -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: delayed block cleanout
Hi Russ, Yes, a bounce will not achieve anything, and neither will a full table scan if it is done in parallel or if 'delayed_logging_block_cleanouts' is TRUE (which is the default at 7.3 and 8.0). @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 28 February 2001 0:41 To: Multiple recipients of list ORACLE-L Hi, In order to avoid ORA-1555s due to delayed block cleanout during a particular conversion, we are running full table scans before the conversion. Someone has asked if bouncing the system prior to the conversion, which would be much faster, would suffice. I think the rollback segment entries are already flagged as commited, and the header of the data blocks would still contain the pointer to the RBS. Even if you bounce the system, I think you still get to wait until the next access of the block. Does anyone else have an opinion? Cheers, Russ Brooks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rolling Upgrade of Oracle on a 24*7 implementation
Hi Murali, There was a thread on this on Ixora Answers in January ... http://www.ixora.com.au/q+a/0101/06105002.htm @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Friday, 23 February 2001 4:41 To: Multiple recipients of list ORACLE-L Any body have ideas on how a Oracle Upgrade is done in a 24*7 installation for a very large database specifically using OPS. TIA Murali -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
