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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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).
