RE: How to find the last execution time of a Procedure.
Raj,
I am no X$ expert either, but X$KGLOB is exposed to us lowly DBAs as
V$DB_OBJECT_CACHE and KGLHDEXC is actually the EXECUTIONS column.
Prasada, you can check V$DB_OBJECT_CACHE for TYPE in ('PACKAGE','PACKAGE
BODY') and KEPT = 'NO' and keep pinning these using DBMS_POOL.KEEP via a
scheduled job. After a while, all those used packages will not only become
KEPT (and provide some side benefit of reducing reloads), you will not have
to store them back into the database... The KEPT = NO will avoid having to
revisit/manipulate those objects that were previously pinned. Of course,
this assumes that there is adeqauet Shared pool space and the Db is not
restarted in-between :)
YMMV!
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 11:00 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: How to find the last execution time of a Procedure.
But you better check with experts as my knowledge of x$ is
feather-weight ... also there is a column on x$kglob called
kglhdexc ... to me it seems the execution count (I feel like
Mr. Monk already). so if execution count is 0 then you
can say that it actually got executed.
But if this doesn't work, in the next CTOUG meeting, I'll try
to hide away from you.
YMMV
Raj
---
-
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Thursday, January 22, 2004 1:24 PM
To: Multiple recipients of list ORACLE-L
Thanks for input Raj.
I was also thinking on the same lines (Querying v$views
periodically and
store it in some metadata table) if there is no easier way to
figure out
from DBA_ views.
As far as changing the production code, as you know, It has
to go thru the
dev/test databases first and then go thru the release process
to implement
into the production. It is painful process.
I will use x$kglob instead of changing production code and all
that release
stuff. Thanks for your help, Raj.
Best Regards,
Prasad
860 843 8377
***
***
This e-mail message is confidential, intended only for the
named recipient(s) above and may contain information that is
privileged, attorney work product or exempt from disclosure
under applicable law. If you have received this message in
error, or are not the named recipient(s), please immediately
notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.
***
***4
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
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: Anyone using IBM's Flashcopy for hotbacks?
Rich, As I had indicated in a previous post on a similar topic, you will need to minimize writes to the SAN during a mirror split during FlashCopy (in IBM, BCV in EMC and ShadowImage in Hitachi). In my limited understanding, once the command to split is received by the SAN, it has to make sure that the write cache is *completely* written to disk. Taking on Tim G's excellent analogy of likening a SAN disk cache to a water tank with an inlet at one end and an outlet on the other, and the requirement of all writes to be written to disk during split, it becomes evident that the SAN has to very quickly bleed off the write cache as well as freeze or somehow delay writes during this time. An ALTER SYSTEM SUSPEND might help during the split. I have seen a 'runaway' Hash join very quickly fill up TEMP using direct writes and considerably delay splits. I really don't see any *read* related problems though at the time of split... YMMV! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Thursday, January 15, 2004 2:39 PM To: Multiple recipients of list ORACLE-L Subject: Anyone using IBM's Flashcopy for hotbacks? We're considering an IBM FAStT SAN for a 30GB Oracle9i DB on HP/UX 11i. One option with the FAStT is called FlashCopy. It's been six months since I've last looked at this, but our original idea was to smack all TSs into backup mode, FlashCopy, then smack all TSs out of backup mode. We'd also need to dump the copy to tape, then startup this copy as another instance, so the Tivoli plugin to have RMAN manage this probably wouldn't be worth the money for us. So, has anyone done this? Which FlashCopy options did you use? Any major gotchas to not do this? Does the Flash cause I/O problems during the backup due to the block reads from the original DB? TIA, Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Process consumes CPU and long time to finished
Hernawan, Is this a custom or standard Concurrent request? If this is standard, there may be a patch out for your module/level. If not, I would use a 10046 level 12 to look at the issue. As you can see from tkprof, you have a huge amount of LIO... Is your init.ora parameters kosher as per Oracle 11i recommendations? DO you see the explain plan for this particular SQL? Is this slowdown new or has existed previously? Can you process a smaller set (with Start/End invoice numbers)? These are some things to try, rather than wait on Oracle Support... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: hernawan [mailto:[EMAIL PROTECTED] Sent: Thursday, January 15, 2004 1:05 AM To: Multiple recipients of list ORACLE-L Subject: Process consumes CPU and long time to finished Hi all, I have process in Oracle apps 11.5.8 which need very lot CPUs and long time to complete. for about 17,000 invoices it takes 28 hours !! I have open TAR since month ago, and still get no solution. maybe here someone can share any idea ? im using 11.5.8, sparc. DB 9i rel2 here is from the tkprof : SELECT sum(nvl(entered_cr,0) - nvl(entered_dr,0)) , sum(nvl(accounted_cr,0) - nvl(accounted_dr,0)) FROM AP_AE_Lines AEL, AP_AE_Headers AEH, AP_Invoice_Payments AIP WHERE AIP.Invoice_ID = :b2 ANDAEL.Source_ID = AIP.Invoice_Payment_ID ANDAEL.Source_Table = 'AP_INVOICE_PAYMENTS' ANDAEL.AE_Line_type_code = 'LIABILITY' ANDAEL.AE_Header_ID = AEH.AE_Header_ID ANDAEH.Set_of_Books_ID = :b1 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.00 0.00 0 0 0 0 Execute 1539 0.23 0.31 0 0 0 0 Fetch 1539 16474.95 21810.67 24 46864854 0 1538 --- -- -- -- -- -- -- total 3079 16475.18 21810.99 24 46864854 0 1538 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 24 (recursive depth: 1) tq -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: hernawan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Shared Pool fragmentation
Rick, I think the best answer is 'know thy application'. And in this, knowledge of bind var vs hardcoded value usage, looking at V$SQL and V$SQLAREA, the ratio (!!) of 'parse count (hard)' to 'parse count (total)', pinning of packages/sequences, etc., can help... You cannot actually 'catch' a 4031 before it occurs, but you can always straighten things out before it occurs. I have found that a combination of pinning Packages/Sequences followed by judicious (once in a while) use of shared pool flush helps. Of course, the shared pool has to be correctly sized - too much and you waste time latching and memory, too little and you _might_ run into 4031. Sizing shared pool is an art that has a little science behind it - science that involves understanding and using values from X$KGLOB and X$KSMSP and your application OTOH, I have seen good results with a flush shared pool during quiet times for non-bind hungry 3rd party apps... See below (script courtersy Steve!) - the number of chunks has dropped dramatically freeing up largish globs of shared pool that would otherwise have to be freed up when a largish object (in this case 15456 bytes) has to load. As well, you will see that the number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down drastically as the system frees up 'freeable' chunks ahead of time, reducing the chance of 4031s My (very limited) understanding is that when a package/cursor has to load and a large-enough chunk of shared pool memory is not free, then the kernel will try and flush out the 'freeable' (not in use) memory and merge adjacent free chunks. If this still does not staisfy the memory requirements, then a 4031 is signalled/ The 'alter system flush shared pool' performs a manual flush instead, ahead of time and could (possibly) prevent a 4031 ... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** 08:35:00 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 01089784 23488 46 76 1 3941364656 84140 2 6812843678 185268 3 315504 875 360524 449019527300 671 1036 561588964099 1502 2060 655465161966 2821 4048 71125720 263 4280 7624 8 989584 101 9797 15456 9 rows selected. 08:35:29 SQL alter system flush shared_pool; System altered. 08:36:32 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 0 14364 330 43 76 1 6528 76 85140 6 3964 1 3964 3964 9 29580 129580 29580 105028636 10348821 65436 11 13860744 15092404 130872 12 32192980 173 186086 261016 13 64490864 172 374946 522764 14 83609184 112 7465101048432 15 79829220 57 14005122068384 16 38149220 14 27249443705320 11 rows selected. -Original Message- Sent: Tuesday, January 13, 2004 9:34 AM To: Multiple recipients of list ORACLE-L Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL
RE: Shared Pool fragmentation
Rick, I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. John -Original Message- From: John Kanagaraj [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Shared Pool fragmentation Rick, I think the best answer is 'know thy application'. And in this, knowledge of bind var vs hardcoded value usage, looking at V$SQL and V$SQLAREA, the ratio (!!) of 'parse count (hard)' to 'parse count (total)', pinning of packages/sequences, etc., can help... You cannot actually 'catch' a 4031 before it occurs, but you can always straighten things out before it occurs. I have found that a combination of pinning Packages/Sequences followed by judicious (once in a while) use of shared pool flush helps. Of course, the shared pool has to be correctly sized - too much and you waste time latching and memory, too little and you _might_ run into 4031. Sizing shared pool is an art that has a little science behind it - science that involves understanding and using values from X$KGLOB and X$KSMSP and your application OTOH, I have seen good results with a flush shared pool during quiet times for non-bind hungry 3rd party apps... See below (script courtersy Steve!) - the number of chunks has dropped dramatically freeing up largish globs of shared pool that would otherwise have to be freed up when a largish object (in this case 15456 bytes) has to load. As well, you will see that the number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down drastically as the system frees up 'freeable' chunks ahead of time, reducing the chance of 4031s My (very limited) understanding is that when a package/cursor has to load and a large-enough chunk of shared pool memory is not free, then the kernel will try and flush out the 'freeable' (not in use) memory and merge adjacent free chunks. If this still does not staisfy the memory requirements, then a 4031 is signalled/ The 'alter system flush shared pool' performs a manual flush instead, ahead of time and could (possibly) prevent a 4031 ... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** 08:35:00 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 01089784 23488 46 76 1 3941364656 84140 2 6812843678 185268 3 315504 875 360524 449019527300 671 1036 561588964099 1502 2060 655465161966 2821 4048 71125720 263 4280 7624 8 989584 101 9797 15456 9 rows selected. 08:35:29 SQL alter system flush shared_pool; System altered. 08:36:32 SQL @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 0 14364 330 43 76 1 6528 76 85140 6 3964 1 3964 3964 9 29580 129580 29580 105028636 10348821 65436 11 13860744 15092404 130872 12 32192980 173 186086 261016 13 64490864 172 374946 522764 14 83609184 112 7465101048432 15 79829220 57 14005122068384 16 38149220 14 27249443705320 11 rows selected. -Original Message- Sent: Tuesday, January 13, 2004 9:34 AM To: Multiple recipients of list ORACLE-L Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received
RE: re BCV / SnapShot / SnapClone and the ALTER SYSTEM
Mladen/Hemant,
I should have expressed myself more clearly. Suspend is not necessary,
it's only fast. Basically, with suspend, you don't put tablespaces into
backup mode. You
suspend, resync, split and start aonther instance as if it crashed. As no
I/O is
going to disk, datafiles aren't fuzzy, so no recovery is needed. Problem
with this approach
is that the original instance is not usable during this time. All sessions
are hanging.
Benefit is that no recovery is needed and if everything goes OK, you're
done very, very
quickly. It's either-or approach, not a combination.
I think there is some confusion here... AFAIU (As Far As I Understand!),
(a) A tablespace, and thus related datafiles, need to be in Hot backup
mode during an *OS* based backup to cater for split-block inconsistency
(i.e. to cater for the possibility of a generally shorter OS block read NOT
getting the generally larger whole block in a single read just when the DB
block was being updated). The Logwriter then writes *whole* blocks to redo
to avoid this split-block (aka fractured block) problem. This increased redo
logging becomes an issue when backing up a large database (such as an ERP
database). EMC's BCVs, Hitachi's ShadowImage (and other frozen disk copy
technologies) mitigate this problem by providing a snapshot copy of *almost
point in time* sets of disks that contain a hot backup copy of the database.
Both rely on the fact that the subsequent backup is an *OS* based copy (i.e.
outside of Oracle) and that the *whole* database was placed in Hot backup.
The split actually takes a few minutes (or seconds, depending on how it was
done and the amount of activity), and the whole database is in Hot backup
mode *only* at that time. A SUSPEND may possiblly only _reduce_ this split
time. Once the split completes, the Database is taken out of Hot backup mode
and the BCVs/Images are then presented back tp the OS via normal mount so
that a subsequent OS based backup utility (such as Legato or Netbackup) can
back it up to tape. Subsequent 'snapshots' will also require the DB to be
placed in Hot backup mode..
In essence, this technology provides for a slow backup of a large database
that is apparently in hot backup mode without having excessive redo being
generated during the physical backup. A positive side effect is that the
Backup I/O goes against currently non-production disks. As well, these
copies can also be mounted on a backup server connected to the same SAN to
even avoid using production CPU cycles... This concept has remained the same
since V7, going into V8/8.1. and 9i as well, and I daresay it is the same in
10g. The key point is that placing the complete DB in Hot backup mode is a
*requirement* before a BCV/Image split, regardless of the usage of SUSPEND
(and the assumption that I/O is not going to disk at this time).
(b) OTOH, RMAN reads a database file and the blocks therein directly, and
does not need the tablespace to be in backup mode since the DB block is
being read by an *Oracle* process. And since there is no need to place a
database in backup mode, one can use RMAN to backup a large database without
worrying about the excessive redo issue. *However*, since the Oracle process
can read only from a 'live' datafile, RMAN _cannot_ be used with
BCV/ShadowImage. And placing an RMAN backed-up DB in SUSPEND mode will only
aggravate users :)
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
From: Hemant K Chitale [mailto:[EMAIL PROTECTED]
Sent: Saturday, January 10, 2004 6:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: re BCV / SnapShot / SnapClone and the ALTER SYSTEM
Yes, I hadn't read the line
so the tablespaces had to be put into backup mode or (8i and
after) the
database had to be suspended
you _do_ have an OR between the backup mode and the database
.. suspended.
We hadn't heard of anyone using the SUSPEND and didn't want to
take the chance
of a database seeming to be frozen for a few seconds or upto
a minute
{weren't sure
how long the split would actually take to run before we
implemented it}.
We'll stick to putting the tablespaces in BACKUP mode.
Hemant
At 09:34 PM 09-01-04 -0800, you wrote:
I should have expressed myself more clearly. Suspend is not
necessary,
it's only fast. Basically,
with suspend, you don't put tablespaces into backup mode. You
suspend,
resync, split
and start aonther instance as if it crashed. As no I/O is
going to disk,
datafiles aren't
fuzzy, so no recovery is needed. Problem with this approach
is that the
original instance
is not usable during this time. All sessions are hanging.
Benefit is that
no recovery is
needed and if everything goes OK, you're done very, very
quickly. It's
either-or approach
RE: re BCV / SnapShot / SnapClone and the ALTER SYSTEM
Mladen, I apologize - I didn't want to imply that you were not aware of the way RMAN works. However, I am not sure I got my point across on the Hot backup issue, so here goes... You should not take a backup of a BCV mirror _without_ putting the whole database in Hot backup, even if you suspend all I/O using SUSPEND. AFAIK, the SUSPEND command was provided to enable an 'instance recoverable' database copy and NOT a day-to-day backup copy. In other words, a copy taken after a successful SUSPEND can be restored and started up, in which case an _instance_ recovery is done. The issue is that you cannot perform _media_ recovery to this copy to bring it up a particular point in time, which is the whole point of a backup... The way I see it, a DBA can use the SUSPEND command to backup a Development/Test database, which would not demand a point-in-time recovery requirement but require a end-of-day backup without having to shut it down. The other use of couse is to reduce or even eliminate IO activity to the BCV while the split occcurs. The split can take quite a while to complete if a session performs heavy writing - a Hash join writing to TEMP can very quickly overwhelme the Write cache of a SAN and delay the split. I found ML Note:91059.1 useful in understanding the SUSPEND command... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Monday, January 12, 2004 11:34 AM To: Multiple recipients of list ORACLE-L Subject: Re: re BCV / SnapShot / SnapClone and the ALTER SYSTEM John, I know that fro RMAN tablespaces need not be in hot backup mode. The trick with susspend is quick dirty way of achieving the same effect as with the cold backup, without bringing the database down. No RMAN involved. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Books on rac
Joe/Ron, Hope I am not beating anyone down, but a colleague has this particular book and said that much of it was a 'cut-and-paste' from the manual... I haven't read it yet, but I can verify this (offline) if you so need. OTOH, I do know that Murali Vallath has a book out on RAC, and I know for sure that he has worked on many RAC installations so you *might* get something from there... As ever, this is my $0.02 (which is not worth much against the Euro!), and carries my standard disclaimer. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED] Sent: Friday, January 09, 2004 12:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: Books on rac Joe, Last year at the midaltantic Oracle users group seminars there was a presentation by Mike Ault what was very informative on RAC with a budget. I believe that he has some decent information available. You might check www.rampant-books.com for his works. Ron [EMAIL PROTECTED] 01/09/2004 2:59:26 PM any recommendations? of course besides the oracle docs and technet, which i think i downloaded all that i need. joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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
Title: Message Tracy, This is a very cursory answer...If this is the 'library cache' latch, then there *should* be a number of entries in V$LATCH_CHILDREN.Are the figures therein skewed in some wayamong the child latches? If so, you *may* have an issue where a particular application or SQL is not using bind variables. A look at V$SQL will reveal a lot. I would look at applications without bind variables. Also, you may look for any Object stats (ANALZYE) that has spilled over and is currently running during the daytime (overly zealous DBA starts off ANALYZE because 'performance is bad'!) - this will invalidate SQLs resulting in parsing (and thus latching). Hth, John KanagarajDB Soft IncPhone: 408-970-7002 (W)Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: Tracy Rahmlow [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:40 PMTo: Multiple recipients of list ORACLE-LSubject: Suggestions Needed: Latch free - library cacheWe have experienced intermittent problems (slow response time) with our oltp database today. There appears to be a large number of latch free events and the p2 parameter is indicating an issue with the library cache. Any thoughts on where to go next? American Express made the followingannotations on 01/07/2004 03:36:25 PM--**"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."**==
RE: ora1652 question...
Chris, There are two options: 1. Easier, but requires a bounce : Add the following event into init.ora event=1652 trace name processstate level 10 This will dump the processstate for processing that encounter an ORA-01652. And you can even add the following to capture 1555 and 4031 errors event=1555 trace name errorstack level 3 event=4031 trace name errorstack level 3 **BUT**, keep _all_ 'event' lines together in the file (just as with utl_file_dir entries) 2. Harder (requires coding/testing), but better control and options: Create a System-level ON SERVERERROR trigger and check for 1652 (among others) and record all the details into either alert.log (via dbms_system.ksdwrt call), database table, utl_file etc. Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Chris Stephens [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:10 PM To: Multiple recipients of list ORACLE-L Subject: ora1652 question... Is there an event to set where I can identify any sql that receives a 1652 error message? There is some process running each night in a reporting database that has been generating this error for the past week. I figured someone would complain. That didn't happen so I went and asked the reporting people if any of the reports were blowing up. They said no. I just set up statspack and will run that every 10 minutes tonight. I also have a query that will capture the session info on sessions currently sorting that I will run every 10 minutes. Neither of the techniques are very direct. I would imagine there is an event to set so that I can generate a trace file. Any other suggestions of nailing this down would be appreciated. ..and so I don't have to ask about events anymore...where do I find what event means what? Thanks, Chris -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chris Stephens INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: stress testing
Henry, Sar is a better tool than vmstat/iostat as it collects a broad range of information. Specifically, sar -q should show up CPU queueing and swapping, and sar -v will show up file/process table overflow issues that may occur during stress testing. IMHO, sar is quite underutilized ( had a paper on this last IOUG, but couldn't go and present it :( John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Poras, Henry R. [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 10:54 AM To: Multiple recipients of list ORACLE-L Subject: stress testing We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris system starting next week (using LoadRunner). I have never gone through a formalized stress test before (most of my stress is brought about informally). So far I am planning to gather statspack information, and periodically get vmstat from the OS. Is there anything else that I should collect? Thanks for the help. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Poras, Henry R. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Hardware for RAC?
Title: Message Chinedu/Chandra, Although I agree that you can use different OEM vendors as long as the OS is the same, be aware of the increased chances for some cross-vendor problems. You necessarily don't want finger-pointing between vendors when problems occur (they will!) in a complex RAC environment. Chinedu, before you launch into using/testing RAC, you should (or ITS should) ask itself the question 'Do I need RAC?'. Mogens Nørgaard, a gurufrom this list has an excellent article in IOUG's SELECT magazine on this topic. If you don't have IOUG membership, maybe Shell ITS can get one. Alternately, you may ask Mogens for a copy. John KanagarajDB Soft IncPhone: 408-970-7002 (W)Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: Chandra Pabba [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 7:04 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Hardware for RAC? Yes, you can use nodes from different OEM vendors for RAC. You will for sure need a private network or interconnect between the nodes for maintaining the heart-beat. HTH Chandra -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ikediugwu, Chinedu SITI-ITPSIESent: Wednesday, December 31, 2003 7:00 AMTo: Multiple recipients of list ORACLE-LSubject: Hardware for RAC? Hello, I have been asked to setup a test environment forRAC.However, I don't know so much about hardware. My questions may appear dumb, please take no offence, I'm a beginning DBA and I really want to know. 1. Can I use 2 nodes of different makes (one IBM and one Compaq), but using same Oracleand OS versions (9.2.0.4 Linux respectively)? 2. Can I set RAC up, using only the public network? Thanks in advance Regards Chinedu
RE: Should we stop analyzing?
I am surprised no one raised the issue of invalidations in the shared pool caused by Stats gathering, and the parsing/reloading load that is caused _after_ the extra I/O and changed plans due to ANALYZEs I have this 250Gb Apps database that is analyzed once a month and we have not suffered due to incorrect or stale statistics. Projects in the new year include revisting the Stats gathering schedules of all our 90+ databases, some of which are analyzed daily :( Have a happy, blessed new year all! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: Should we stop analyzing? That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:09 PM I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Apps 11.5.9 Disater Recovery Site
Ron, I entirely agree with Stephen. Apps is a different beast when it comes to such stuff. Overall, the issues with incomplete recovery (i.e. recovery to a previous point in time), especially in a complex, integrated ERP system such as Oracle Apps 11i are many. You should only restore to a point in time as a very last resort. Depending on modules and interfaces that were active at the time of recovery, you would probably have system generated numbers (PO's ,Invoices, etc) that have been created and _already_ sent to customers and suppliers. As well, many ERP systems send out (and receive) EDI data from other external systems. Coordination of this could be a logistical nightmare when you perform an incomplete recovery. This needs to be understood and documented, otherwise you might end up having to fix complex data issues. The key words are Documentation/Understanding of Processes and Change Control Happy New Year all! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Karniotis, Stephen [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Apps 11.5.9 Disater Recovery Site Ron: I would definitely not suggest ignoring adpatch. Should you actually need to use this DR site and something went wrong, Oracle may not offer you any help. If rsync can offer you 100% replication than I would try it and validate it. However, applications is a much different beast than just the database. File system names, node names, database names, tns names, userids, passwords, etc. are embedded within the code and are very difficult to change. It would be best to take a complete image copy of the database environment, the apps environment and then use adpatch and adadmin to verify the environment once completed. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct:(313) 227-4350 Mobile:(248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- Ron Thomas Sent: Tuesday, December 30, 2003 6:35 PM To:Multiple recipients of list ORACLE-L Subject: Apps 11.5.9 Disater Recovery Site I've been charged with bringing up a disaster recovery site, so time to hit the books again as a lot has changed since the last time I did this. Looking for resource recommendations (FM to read, white papers, etc). Sticky part of this is it is an Applications 11.5.9 installation. The database end of it should not be too difficult (8.1.7.4, soon to be 9.2.0.4), but the applications file system is modified by the adpatch utility which adpatch requires a database connection to function. I can think of 2 ways to get around this requirement. 1. set the two_task to point to a live test system, and run adpatch force using the c and g drivers. The d driver would not need to be run since the changes will come over via the archive logs. 2. ignore adpatch utility completely and use rsync. Suggestion, comments? Thanks,. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen 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
RE: A performance problem
Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: A performance problem I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read15,978,336 5,809,277 57.28 SQL*Net message from dblink3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read 2,460,279 0 943,2524149.4 control file sequential read 907,1480 300,572355.1 pipe put 2,033 2,032 208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read5809277 60.16% 0.36 0.688.228762.11 SQL*Net message from dblink 196016820.30% 506.77 0.232.772956.51 db file scattered read 943252 9.77% 0.38 0.111.341422.70 control file sequential read 3005723.11% 0.33 0.040.43453.35 pipe put 208850 2.16% 102.73 0.020.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical Reads Reads/Execute Hashs Value % of Total INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 13 9737644 749049.54 1419451399 30.18 SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUC T
RE: A performance problem
Venu, You can work out the trace file name for Conc jobs. The OS process for a CM job is stored in the ORACLE_PROCESS_ID in FND_CONCURRENT_REQUESTS for that particular REQUEST_ID. You can then use this process number to generate the trace file in udump (normally $ORACLE_HOME/admin/DBSID/udump/*Os_proc*.trc in the case of a UNIX based 11i DB server). Although this would have been just a SQL_TRACE (10046 Level 1), you can *still* run a tkprof on it to determine which SQL consumed the most time Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Subject: RE: A performance problem John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: A performance problem I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait EventWaits Time (cs) % Total Wt Time --- db file sequential read 15,978,336 5,809,27757.28 SQL*Net message from dblink 3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - - cs - centisecond - 100th of a second - ms - millisecond - 1000th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read2,460,279 0 943,2524149.4
IT at Walmart and Kmart - keeping it On-Topic
Just trying to keep this On-topic! See extract of Wal-Mart's IT approach vs K-Mart http://searchcio.techtarget.com/originalContent/0,289142,sid19_gci938869,00. html?track=NL-35 Wal-Mart's step change approach to IT investment during the 1990s is a great example. First, the company installed software to manage the flow and storage of products through its far-flung network of suppliers, warehouses and distribution centers. Once it had automated product flow, it focused on using IT to coordinate its operations more tightly with those of its suppliers, leveraging its greater efficiency. With that smoother coordination, Wal-Mart could invest effectively in technology to plan the mix and replenishment of its goods. Finally, after integrating all these capabilities, the company built a data warehouse that uses information pulled from a range of sources to handle complex queries. Kmart, by contrast, made a misstep in its IT investments that undermined their effectiveness. It invested in systems to improve promotions management before it had installed the supply chain systems necessary to handle fluctuations in sales volume. As a result, it was unable to capitalize on the more precisely targeted promotions. Many retail banks also made errors in sequencing. They invested in popular customer relationship management systems before they had built repositories of consistent and reliable customer data. Not surprisingly, the CRM investments fell well short of expectations. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re[2]: http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT Mladen I'm pretty confident of one thing -- if they weren't selling, Wal-Mart would quickly stop selling them at that store. Most people don't think about it, but Sam Walton figured out a couple of things early on: 1. If you don't have the item on the shelf, people can't buy it. 2. Hire a smart computer systems manager. 3. Your control of your own data is a competitive weapon. The system they created was flexible enough to be expanded to many, many stores. In his book Sam Walton: Made in America, Sam lavishes praise on his systems people. The Kmart leadership, on the other hand, was often quoted in the press about how they were able to reduce their I.T. expense. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 12, 2003 9:54 AM To: Multiple recipients of list ORACLE-L http://www.wintercorp.com/vldb/2003_TopTen_Survey/TopT Jonathan is correct - WalMart uses Teradata. And they're selling gallon-sized Vlasic pickles. I always wondered who was buying such a monstrosity. It's a bi-annual pickles supply in a single package. Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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
RE: Performance tuning in complex environment
Reminds me of the day when a third-party developed app (main batch program) ran *very* slowly - the user department went out and bought this app and server on their own without IT's blessing or support (a different story). Dialogue below: Third-party Developer (TPD): This same batch program which runs 1hr 30 min on your box completes under 30 min at our Office with *your* data. We suggest obtaining an IBM S80 because it is 3 times faster than your current box (IBM On-site person: Yes! Yes!!!) User Department Manager (UDM): Ok - we have a $100,000 budget for this - lets go out and buy this h/w (We need to go through IT for this purchase) My Manager, when approached with this issue (MM): I know your TPD has this view, but can my Sr. DBA look at this problem? UDM: Ok, but I doubt anything can be done since my TPD says so... TPD: Hey, your DBA can't mess with our code! Sr.DBA (Me!): Ok - let's take a look at V$SYSTEM_EVENT, V$SESSION_EVENT and V$SESSION_WAIT when your program runs... Me: Hey - what's this session doing with 'SQL*Net Message from dblink'? This is the top wait (more than 99% of TIME_WAITED in V$SESSION_EVENT) TPD: Yeah - we have a view that makes a call to your employee table sitting on your prod box to fetch the Emp name, once for every row in the loop (1000s of rows, 3300 rows a pop) Me: Haven't you guys heard of Replicated Tables? TPD: What's that? Me: (after creating a local copy and replacing the view with an indexed table) Run your program now... TPD: Hey - it finished in 5 minutes!!! We don't need to buy any other box! UDM: I like that!!! Thanks!! MM: Well done - I knew my DBA could do it! (IBM On-site person: [EMAIL PROTECTED]@#*()+__@) Me: (Hitting myself on the head, and thinking to myself: I should have asked for just 1% of the $$ that would have otherwise been unnecessarily spent on that great big H/w box :( Moral of the story: (a) Never ass*u*me anything - ask for stats to prove any 'assumption' (b) Get the right tools to determine the problem area (and use it correctly) Afterthought (c) - Follow Gary Goodman's principle: Ask for 10% of the $$ allocated for the h/w that would have otherwise been spent on *trying* to solve the problem by throwing h/w at it! (Cary - correct me if I erred here!) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Performance tuning in complex environment Not really sure what happened and why we decided to that. I was involved in the beginning of project and remembered that PM was mentioning about talking to another Logician client who were facing same issues. -Original Message- Jamadagni, Rajendra Sent: Thursday, December 11, 2003 10:55 AM To: Multiple recipients of list ORACLE-L Ummm ... what was the problem that prompted you guys to replace citrix servers? Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- [mailto:[EMAIL PROTECTED] Sent: Thursday, December 11, 2003 1:35 PM To: Multiple recipients of list ORACLE-L Hello Everyone, I am trying to get some help/suggestions reg. how to troubleshoot performance issues. Little back ground about our environment. Its third party application (Logician) from GE. There are total 11 databases, all on oracle 8174 H-UX 11i in cluster environment. All the databases are on EMC Symmetrix using 6 disks. All the clients are connecting to database thru Citrix terminal servers. In last one year we spend lots of time/money in tuning databases, replacing Citrix servers but end result is same. I was wondering if anybody out there has ran into same kind of situation. Our (DBAs) guess is the disk layout is not optimal but we also dont have any data to prove that disks are the bottleneck. Is there any way to collect these kinds of stats in Oracle. We aren't getting much help from our SAN administrator. DISCLAIMER: This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email
RE: Verifying success of dbms_repcat.execute_ddl
Paul, How about an ON DDL system trigger on the source (and possibly target) databases? This trigger can log any and every detail of the who/what/when whenever *any* DDL is performed by whoever... Let me know if you need more details. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Paul Baumgartel [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Subject: Verifying success of dbms_repcat.execute_ddl I'm trying to determine when execution of DDL via DBMS_REPCAT.EXECUTE_DDL completes successfully on all master sites. I'm not sure how the DDL is propagated, but it doesn't appear to be via the replication administrator's scheduled jobs: I've removed the dbms_defer_sys.push job (via dbms_defer_sys.unschedule_push), and broken the dbms_repcat.do_deferred_repcat_admin job, then run dbms_repcat.execute_ddl. The results show up on the other master database within a couple of seconds. Anyway: If there is an error produced by the EXECUTE_DDL call, Oracle returns an error message immediately, and the error is also logged in dba_repcatlog (this led me to believe that execute_ddl calls were handled by dbms_repcat.do_deferred_repcat_admin, but that doesn't appear to be the case), so error detection is easy. What I need, though, is to be able to tell _when_ the DDL has been run on each master database (I am running a batch job that disables all FKs, then runs an import; the first attempt produced failures because the row insertions caused by the import arrived at the other master DB _before_ the FK disablement). Any help appreciated. TIA. = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RBO to CBO migration books/ material
Suhen, Any good books available to convert applications from RBO to CBO. References to Oracle 9i. I am not aware of any book, but there is a limited number of articles/papers (most well-known is Tim Gorman's 'Search for intelligent life in the CBO' at http://www.evdbt.com) that can point you in the right direction. My humble addition is the one at http://www.geocities.com/john_sharmila/links.htm - there are a number of ML articles on the CBO itself that will help: Doc ID: 35934.1 is a good one to start. I would personally do the following: * Trace all SQL coming into a live RBO-only system * Identify any code that uses the RULE Hint (in spite of being in a RULE based DB) * Create a clone of prod on a server of the same or similar capacity * Collect Statistics (COMPUTE if you can) * Set the OPTIMIZER_MODE to CHOOSE; review/reset other CBO related parameters (see my paper) * Let the Developers and UA testers loose on that Db * Use Cary's method to identity the top set of business processes and determine if the performance is Ok * If not Ok, then tune it... All the best! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Multi-threaded server - will it help in this case
Jared, I don't think that is what Tim meant. You can use something akin to the following: For an MTS connection/client: MYDB_MTS.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID))) For a dedicated connection/client: MYDB_DEDICATED.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID)(SERVER=DEDI CATED))) The only difference is in the TNS handles and the entry they point to which differs in content. The SERVER=DEDICATED will bypass the MTS configured default connection. You can do this via ONAMES too (and I know you use one!) - see Note:1036577.6. Btw, I am currently in the UK helping with a Name Server rollout.. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 7:29 AM To: Multiple recipients of list ORACLE-L Subject: Re: Multi-threaded server - will it help in this case Tim, This bit: accomodate this application. Please be aware that you can mix dedicated and MTS by setting up different TNS names on different ports for each, so it is not an all-or-nothing seems to imply that MTS and Dedicated will each require their own listener ( different ports). Been awhile since I messed with MTS, but I don't recall that as being necessary. Is that what you meant? Jared On Tue, 2003-11-11 at 07:04, Tim Gorman wrote: Peter, MTS (or SS in 9i onwards) is an excellent choice to accomodate this application. Please be aware that you can mix dedicated and MTS by setting up different TNS names on different ports for each, so it is not an all-or-nothing situation. Most connections to the database outside of this CAE app will likely be better served with dedicated connections, so just dole out TNS names accordingly. Also, please be sure to estimate the size of your UGA by tracking values (i.e. name like '%uga%') in V$SESSTAT at peak periods then sizing the Large Pool to accomodate, before you enable MTS. Unless you're really constrained for memory, don't be shy about this; double the highest value you sum from V$SESSSTAT to be safe. After enabling MTS, monitor the value of free memory where POOL = 'large pool' in V$SGASTAT. If you've oversized, you can start backing down on LARGE_POOL_SIZE gently, if you need the memory elsewhere... Hope this helps... -Tim Environment: AIX 4.3 Oracle 8.1.7 The application is a CAE tool which stores metadata for a hierarchy of 3D engineering design models. When a user opens a model at a given level in the design, the application retrieves data about that model and all of the models below it in the design try. This often involves as many as 100 or more models. Unfortunately, the way the application is written, it opens a new connection to the database for each model. Thus, in the process of retrieving metadata, it may open and close as many as 100 connections to the database. Obviously, this causes some performance problems, especially for remote users. The number of users when the system goes fully into production is going to be in the low 100's. The vendor is not interested in changing the way the software works. Will use of the mult-threaded server improve performance in this situation, for example, by eliminating the overhead of starting a dedicated server for each connection? Thanks, Peter Schauss -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
RE: Re[2]: Sequences in OPS/RAC
All, Just wanted to point out that 'missing' invoice numbers caused by a variety of causes (even if they were not cached), can cause problems for Accounting/Finance Depts in certain countries. Basically, the Govt looks on this as being used for 'tax avoidance', unless proved otherwise. You *can* miss uncached sequences under certain conditions when the Db restarts or a short burst of SQL causes pressure on the DD cache... Had this occur once in an Apps database and had to apply patches to undo and put back the sequence... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, November 03, 2003 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re[2]: Sequences in OPS/RAC The problem is that the ORDER clause comes at the expense of CACHE. You can use SQL tracing to verify that each use of the sequence causes an update of SYS.SEQ$ when ORDER is set, effectively rendering the CACHE setting a no-op. So, especially in an OPS/RAC environment, the use of ORDERED sequences, especially heavily used ORDERED sequences, comes at a steep price. Think about it: is ORDERED *really* necessary? In some situations (i.e. check numbers), the ORDERED clause would be necessary, but unless you are pumping out thousands of checks an hour, perhaps a cached sequence shouldn't be used. But for system-generated keys, surrogate keys, etc, I don't think the semantics of ORDERED are necessary at all. Hi, I have RAC and I always use ORDER when I create SEQUENCE. The following information is from Oracle Manual: ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order. Muqthar Ahmed -Original Message- Sent: Monday, November 03, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC However, the Builder.Com article quite explicity asserts HKC Sequence generator numbers are guaranteed to be unique only for a single HKC instance, which is unsuitable for use as a primary key in parallel or HKC remote environments, where a sequence in each environment might generate HKC the same number and result in conflicts Can you point us to the article? My guess is that the author is not familiar with Oracle, and is basing the above statement on his experience with some other database (DB2 perhaps?). There is no problem with using sequence numbers in a RAC. No conflicts will occur. I've never heard of a problem in that regard. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Muqthar Ahmed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -- --- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from
RE: xdb and xschema
M, There are a large number of articles on XML in SELECT - IOUG's technical journal (requires membership though). And I am sure that OTN has a ton of XML/XDB articles as well - they may a good starting point. J DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Friday, October 31, 2003 4:34 PM To: Multiple recipients of list ORACLE-L I've been tasked to create an xschema in the XDB repository. I'm new to xml. Is there a good place to start? I've run the catqm.sql script to create the XDB repository, but I'm searching for the next steps. Any help appreciated. TIA M. Do you Yahoo!? Exclusive Video Premiere - Britney Spears -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Moving projects from development/test to production
Dennis, You guessed correctly that this is ITIL based. I completed a Foundation certificate in ITIL way back in '96 when it was still UK based more than what it is now. The principles still stand, and the organization I worked for implemented some kind of CMDB. Unfortunately, they got into very low level details for the Cis and the project became too big to get off the ground One needs to find a balance. OTOH, the tools to help implement ITIL have come a long way since and I mentioned some good ones (at least ones that I have seen). Most of ITIL is just common-sense distilled into a framework for IT processes that define what an IT organization needs to get things done properly. However, in my limited understanding, the IT scene and organizations changes direction and in leadership so rapidly that one needs an evolving plan (to say the least). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2003 8:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: Moving projects from development/test to production Helmut I don't think this is off-topic, but something that many of us Oracle DBAs wrestle with. If you kept John Kanagaraj's posting yesterday on the DBA Support Database topic, it contains the big picture. If you didn't keep it, email me privately and I'll send it. I believe he is referring to the ITIL standards body. There is probably an ITIL user group near you or a company that gives talks from time to time. That is the easiest way to get an overview of ITIL. Our organization is evolving in that direction. Overall, this is a long-term commitment by your organization. You don't change everything overnight. One immediate change you can implement is a staging system. Most of us currently have a test or development system. Staging is an exact copy of production in all respects. From the database side, cloning the database or using RMAN DUPLICATE works very well. Then the development team makes a release to you. Ideally this is on a CD-ROM labeled 1.1 or whatever. You take the CD-ROM and instructions and apply the changes to the staging server. Then the staging system is tested. If it is deemed satisfactory, then you schedule a time and make the changes on production. If it fails the testing or you are unable to apply the changes, then it gets bounced back to the development group. The release may include application or web server changes that must be coordinated with database changes. This is a small change, but it has helped our releases. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, October 31, 2003 1:59 AM To: Multiple recipients of list ORACLE-L Hi! A somewhat off-topic question this time. I am put in charge of defining the procedure of moving projects from test/development into the production environment. This is to be seen from the entire IT-perspective (i.e. not just databases, but also Unix, Oracle and SAN). I.e. we should come up with check-lists and the like; although having an eye on quality assurance... We urgently need to set procedures up for that since the last time this was a nightmare... Did anybody out there work on a similar project? What are the procedures that you are following? Any input would be appreciated. This is 9.2 on HP-UX 11. Thanks, Helmut Helmut Daiminger WWK Lebensversicherung a.G. Marsstrasse 37 80292 München Telefon: (0 89) 51 14 - 3490 Fax: (0 89) 51 14 - 27 62 mailto:[EMAIL PROTECTED] http://www.wwk.de *** select 'bye for now' from sys.dual *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: DBA Support Database
Ron, As you may have seen already, some issues have been pointed out. However, there are deeper issues... :( Databases need to run on Servers - these servers in turn rely on other services below them, namely Disk infrastructure (such as SANs, NAS, Switch fabric, etc.) as well as other networking services such as DNS, Routers, switches, gateways, etc. As well, there are the Application and other middleware layers that take part in the equation. In addition, there are the people that manage them, the organization structure that holds it together and the IT processes that define how and who does what. As you can see, this quickly gets very complicated, and maintaining this data [assuming that an application exists to do this] becomes a priority. Miss capturing or maintaining any of this, and the data quickly gets out of date or is irrelevant so that it is no use. I say all of this to say what comes next: Certain IT standards bodies have recongnized this and have specified that IT creates a 'Configuration Management Database' [and an application around it]. Processes around this include * Incident Management [something breaks, a user calls the Helpdesk who record and route it, a technician fixes it] * Problem Management [a process to identify trends in Incidents and identify/fix root causes] * Change Management [a process to document, agree and implement changes to the IT components in a controlled fashion with adequate understanding of effects and impact] * Other processes such as Asset Management, etc. In addition to other things mentioned above, the Config database should be able to map Business processes [business-speak for what an IT user does to keep the business flowing] to IT components as well as maintain the relationships and dependencies of the IT components so that impact analysis can be done Add up all this, and you see both the need for this as well as the complexity of the issue. Prepackaged applications exist to do this all : Examples are HP's Service Desk [they have been at it a long time], Troux [www.troux.com], etc. I hope that I haven't quenched your enthusiam - just wanted to make you understand that your mini-database will be (has to be) a component in the big picture. As a start, you could always create a 'Control Database' that lists all your Databases so that you can use it as a reference to put together a periodic publishing of a List of databases and versions, Sizes allocated and used, and other good stuff such as 'Average BCHR in the last month' :-) These tools have the capability to reference such standalone repositories and update themselves, so you haven't lost anything Hope this helps! John Kanagaraj Oracle Applications DBA Hitach Data Systems, Santa Clara Work : (408) 970 7002 Fax: 408 327 3402 (Call/Email prior to fax) -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:09 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBA Support Database Ken, Ron I think the most important step is to ask some very hard questions about what data you really need. From what I've seen (and been involved in), you begin with a burst of enthusiasm and tend to collect far too much data. Then you can't keep it all updated, so the data tends to get obsolete and not trusted. Better to start with the minimum and add more data later. One thought is to collect data on the interdependencies between the databases. If one database has a link to another, it would be nice to know this before you take one of them down and accidentally shut down some other users. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, October 30, 2003 10:09 AM To: Multiple recipients of list ORACLE-L Ron: I have heard of this being done especially in large companies that have many, many databases. It is difficult to keep track of all the little details that are spread out all over the company. Having a central data mart for this information I thing would be very helpful. The only problem I see is keeping it up to date. Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:29 AM I was thinking about putting together a database that contains a list of DBAs, servers, databases, and applications. The database would be used by the Helpdesk and Management to see who is responsible for a given application or database when problems occur. I thought I would check first and see if anyone has already designed such a database and might be willing to share it. Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: 10046 level 8 trace - help required with 'direct path
Tim, As you have seen, this is due to writes to and reads from the TEMPORARY tablespace of that user. This could be due to both SORT segments (SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going to TEMP when they overflow HASH_AREA_SIZE. This can be seen from V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or Hashing, I am assuming that either there are triggers that are forcing this to occur, or this is a view and the INSTEAD OF is performing some inefficient joins... Andy - just curious how a WHERE clause on a DELETE would generate Sort usage (outside of that explained above)... John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: 10046 level 8 trace - help required with 'direct path Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Overhead Associated with Signon Audit in Financials 11.0
Vicki, As long as your *Purge* Audit signon data, I really do not see any significant overhead. We have a 200 Gb DB and see no issues. What level is your Profile set to? The advantages of Signon Audit far outweighs the load it places - for e.g. you have no other way of seeing which user is logged on (and depending on your audit level) what forms and what responsibility they are using at this time... On the other hand, ask your auditors *what* they would like to see. Oracle Apps already records Last-changed user and date/timestamp for rows, while Signon Audit tracks sessions only when it is switched on. Let us know if you need more info. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Thursday, October 30, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Does anyone have any statistics about overhead associated with using the Signon Audit in an 11.0.3/ 8.1.7.4/8.0.6.3 environment. We are using full installs of AP, GL, FA and CE. Size of the production database is 100G. Can't tell you exactly what we'd be auditing; we are under siege by Internal Audit at the moment - they've raised the database audit flag, but have not started dictating what they want audited. I am trying to get some real-world statistics to arm myself with when the day comes . I have heard that the overhead is significant - is this true, in your experience? Vicki Pierce Database Administration x2401 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: DBA Support Database
Paul, Kevin Loney was the original author of the CC Db - way back in the Oracle7 handbook. I used to have a schema built on that basis in a previous job, and it served the purpose well. However, the problem does remain that 'linking' it to other parts of the IT infrastructure will not work on account of *everyones* inability (dare I mention 'apathy') to keep it up to date. And yes - Greg's expensive free comment is well taken. An enterprise IT repository is well worth it, but it can and will be a bear to get off the ground unless there is some serious Management committment behind it. If executed and maintained well, it can relieve a lot of pressure and work and add value to the 'business' [Hope I don't sound like damagement :) ] John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com http://www.klove.com/ ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Thursday, October 30, 2003 3:24 PM To: Multiple recipients of list ORACLE-L I recall seeing a command center database in the book Oracle 8i DBA Handbook by Loney, Theriault. chapter 6 - Managing multiple databases. its a start. I haven't read the 9i version. Rachel, were there any improvements to it? Paul Loughmiller, Greg [EMAIL PROTECTED] wrote: I'll throw in my *very expensive free* comments... expensive free comments This begins to create the corporate metadata and architecture as Peter mentions. We are on this road, and there are several tools that can do *auto discovery*. There are some very nice tools on the market for asset discovery. They have *exit points* where one could write some basic code to access those assets(sql getting v$ info and store in your metadata?). We have built an enterprise repository to maintain infrastructure data, application, servers, network devices, and transport layers. And now we are going down the *yellow brick road* to begin the data acquisition process. We too, will also define and assign accountability to those elements within our repository. /expensive free comments greg -Original Message- Sent: Thursday, October 30, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Just a quick reply to this. You are, in fact, formulating the sort of request which would be input to a corporate data architecture. We have built such a thing, and it includes the issues you refer to. More importantly, we have identified who is responsible for every single piece of data in the system. The management of an attribute in a table can in fact be traced right back up to that level of senior management where they don't even know how to spell 'Oracle'... peter edinburgh -Original Message- From: Smith, Ron L. [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: Thursday, October 30, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: DBA Support Database I was thinking about putting together a database that contains a list of DBAs, servers, databases, and applications. The database would be used by the Helpdesk and Management to see who is responsible for a given application or database when problems occur. I thought I would check first and see if anyone has already designed such a database and might be willing to share it. Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net/ -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com/ San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing l! ist, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result! of the use of this medium to transmit messages from or to the BGS. .http
RE: Overhead Associated with Signon Audit in Financials 11.0
For all the non-APPS DBAs out there... Oracle Applications 10.4 onwards (lowest version I have seen) provides for a feature called 'Signon Auditing'. This is NOT Oracle's Auditing (which goes into SYS.AUD$). It is a parameter driven auditing that records all Users that logged in when set to USER, Application Responsibilities that they chose (upon login as well as subsequently switched to) when set to RESPONSIBILITY, in addition to recording the USER level, and the Forms that they chose to run when set to FORMS, in addition to that recorded at RESPONSIBILITY and USER levels. Thus, when set to FORMS, a user login would at best produce a minimum of three rows, etc. These rows are updated when the user logged out, so all sorts of reports about who is/was logged on, forms currently being used, etc. can be determined. In fact, for an Apps DBA to tie back a session to an actual user, at least USER level signon auditing should be turned on. The problem with Apps is that all users would login in the APPS schema using the encrypted password which is obtained using a dummy connection... Forms and further Access is then determined by 'Responsbilities' that are in turn tied to 'Organizations' and 'Datasets'. By default, almost all Applications tables record the last updated user and timestamp, so there is some inbuilt auditing, albeit not a trail. Oracle provides an additional Audit function that performs an audit trail for such datasets, and this can produce significant overhead for data storage. Thus all discussions about SYS.AUD$ are not really relevant in this particular thread, although some good ideas have been aired. Switching on Auditing without understanding what is ultimately required would be very counterproductive, whether this is on an APPS database or not, in any case. [As an aside, most of this is enabled via the AOL - Applications Object Layer (aka FND - Foundation Layer) and is a solid example of providing 'Application' infrastructure. And don't get me started on the Concurrent Processing - that's an excellent one too] I am going to stop now and let Apps gurus such as Andy R, Tanel and Tim G comment. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: Overhead Associated with Signon Audit in Financials 11.0 It is true, auditing adds significant overhead, but not session auditing. Significant overhead is added by DML auditing because you ad significant amount of modified blocks to every transaction you audit, you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-4031 error help.
Avnish, 4031 (as well as 0155 and 1652) are considered 'user' errors and will NOT be logged in the alert.log by default. You could add the following into your init.ora to capture them: (Make sure that you keep *all* event lines together, including previous ones in the init file, otherwise only the last set is considered): event=1555 trace name errorstack level 3 event=4031 trace name errorstack level 3 event=1652 trace name processstate level 10 I also see that you are at 9202 and I do know that there are *lots* of shared pool related errors below 9204. I would suggest an upgrade first... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) God's word wrapped in great music - 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jeremiah Wilton [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: Re: ORA-4031 error help. Well, you neet to check the full error, because otherwise there's no way to tell if you are running low on shared or large pool. The view that shows space usage in both places in v$sgastat. I suggest you start looking there. Maybe your third-party application doesn't use bind variables and is bloating the shared pool. You could verify this by observing that the sqlarea component of the shared pool is very large as seen in v$sgastat. If this is the case then you might consider testing with cursor_sharing=force. You could also count different versions of similar SQL from the application by grouping sql_text in v$sqlarea by the first 30 characters or so. This assumes your problem is shared pool sqlarea bloat. You could just be runnning out of space for MTS session heaps in the large pool. You have to look at v$sgastat first. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Wed, 29 Oct 2003, [EMAIL PROTECTED] wrote: Hello List, Need some help in resolving ORA-4031 error message. We are using Lawson and for last few days users are getting ORA-4031 error 2-3 times a day in LAWSON log files but there is no error message in alert log file or any trace file. Both shared pool and large pool is set to 1GB. Below is the current init.ora file. We are on Oracle 9202 and AIX 5.1, using MTS. # Miscellaneous COMPATIBLE=9.2.0 DB_NAME=LAWSON DB_FILES=1500 GLOBAL_NAMES=TRUE DB_BLOCK_SIZE=8192 DB_CACHE_SIZE=1792M DB_KEEP_CACHE_SIZE=16M LARGE_POOL_SIZE=1024M SHARED_POOL_SIZE=1024M SGA_MAX_SIZE = 5G DB_FILE_MULTIBLOCK_READ_COUNT=8 CONTROL_FILE_RECORD_KEEP_TIME=45 CURSOR_SHARING=SIMILAR OPEN_CURSORS=750 # From Lawson--Raised from 500 to 750 10/24/03 BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump TIMED_STATISTICS=TRUE CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWS ON_01.ctl, /appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl, /appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl, /appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl, /appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl) # Archive LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/ LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/ LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T LOG_ARCHIVE_START=TRUE # LOG_ARCHIVE_TRACE = 1 # Distributed, Replication and Snapshot DB_DOMAIN=PHSOR.ORG # Pools JAVA_POOL_SIZE=0 # Processes and Sessions # PROCESSES=800 Increased value per vendor JMK 6/09/03 PROCESSES=1000 SESSIONS=1140 ENQUEUE_RESOURCES=8000 TRANSACTION_AUDITING=FALSE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE FAST_START_MTTR_TARGET=1200 SORT_AREA_SIZE=0 HASH_AREA_SIZE=0 UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=undo UNDO_RETENTION = 10800 PGA_AGGREGATE_TARGET=1G WORKAREA_SIZE_POLICY = AUTO JOB_QUEUE_PROCESSES = 10 LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event in v$system_event CURSOR_SPACE_FOR_TIME = TRUE SERVICE_NAMES=lawson_ax3202a LOCAL_LISTENER=lawson_ax3202a # Network Registration INSTANCE_NAME=LAWSON DISK_ASYNCH_IO = FALSE BACKUP_TAPE_IO_SLAVES=TRUE PARALLEL_THREADS_PER_CPU = 6 PARALLEL_MAX_SERVERS = 6 PARALLEL_MIN_SERVERS = 1 DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000)) (DISPATCHERS=1) MAX_DISPATCHERS = 3 SHARED_SERVERS = 10 MAX_SHARED_SERVERS = 50 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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
RE: Cache a table
My understanding is that the KEEP and RECYCLE Pools are just 'names' in the
sense that they are placeholders for assigning an object to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to one of
these named pools segregates objects by retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers - rather it makes sure
that objects that you would like to 'keep' around are specifically directed
to a common pool and vice versa
Does anyone have additional information that can verify this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning training Class.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 6:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Cache a table
Good points, Arup.
Actually, I would argue that there is better reason to
consider using the
RECYCLE pool than to consider how to cache tables or use the
KEEP pool.
The advantage of effective use of the RECYCLE pool is better
behavior in the
rest of the Buffer Cache...
When you think of it, the default DEFAULT buffer pool and the
KEEP pool have
essentially the same purpose: long-term caching of blocks.
What keeps them
from accomplishing that mission but objects whose blocks waste
space and
energy cycling into and out from the Buffer Cache?
It's kind of like a school teacher admonishing his/her class that a
troublesome few have ruined things for everybody. When I was
in school,
troublemakers were segregated from the rest of the class, sometimes
cumulatively into a separate classroom (we called ourselves
the mentals
and read Mad magazines all the time, which accounts for a lot, then and
now). Nowadays, I'm sure that such a measure isn't considered
for fear of
lawsuit for hurting the self-esteem of the poor dears.
Never mind the
confusion between the useless feel-good phrase self-esteem
and the more
useful and thought-provoking phrase self-respect. Oh well,
better stop
now...
Anyway, marking a table as CACHE and placing it in a KEEP
buffer pool which
is large enough to accommodate all of the used blocks is the
closest thing
to pinning a table into the Buffer Cache as you'll get, as
Arup described.
Of course, there is little benefit from such a move, as Arup
also mentioned.
Just yesterday, I visited a customer who had a series of SQL
statements that
were executing some 10 million times _each_ per day, averaging
about 20-1500
LIOs per execution. They each had a 99.999% buffer cache hit
ratio, yet strangely enough the performance on the server is
absolute crap
because the eight brand-new 2Ghz CPUs on the server are busy
as hell with no
time to spare for anything.
Well, you know and I know that they simply need more CPUs,
which is what HP
is busy telling them, today right as we speak. Moreover, Oracle
Consulting is shoulder to shoulder with them, nodding their
heads. No way
does the crap custom-built application need to be altered in
any minor way,
so that it doesn't keep performing the same useless validation
query on the
same set of static lookup tables over and over again for each
row inserted,
when the JDBC thin client can easily query these tables only
once and store
the results. Nope. No sirree...
Cliff-Clavin-voice
It's a little-known fact that Java code actually has the consistency of
concrete, once in production. There are so many interdependencies from
shared modules and RPCs that people are terrified of modifying
anything,
probably for good reason. Far easier to shift blame or say
hear hear when
the vendor proposes another 4-8 CPUs.
Ah, I believe I'll have another beer when you're ready, Sammy...
/Cliff-Clavin-voice
Anyway, first tune the SQL. Then, tune to the application to
get rid of
unnecessary SQL. Then and only then, consider tuning the
Buffer Cache to
segregate bad tables to the RECYCLE pool or pinning tables
to the KEEP
pool. Reversing the order is a great way to convert a happy
application
capable of running on a small server to an unhappy application
demanding a
huge server...
on 10/21/03 5:21 AM, Arup Nanda at [EMAIL PROTECTED] wrote:
Never. Altering the table to cache does not gurantee that it
will be always
be available in the cache. It simply means the table will be
placed in the
Least recently used end of the LRU list and it will age away
as time goes
by, just like any other table.
A better approach is to use KEEP pool and place teh table
(and all other
tables that are accessed frequently) there. This is
particualrly true for
datawarehouses wherethe lookup tables or small dimension
RE: Oracle on Sun vs Tru64
Jake, I have heard horror stories from both Sun *and* Tru64 customers (we were Sequent, and moved to Sun), but your CIO is probably right in moving out since support (people, skills, patch and product/application availability) for Tru64 is slowly vanishing. As well maintenance costs are higher for 'exotic' brands, so the move might make sen$e. For sure, the Sun platform base is larger... The stickler is the time that will be taken for migration, and that depends on 'how much is the business willing to wait' while the database is down, as this *has* to be done via an export/import. Questions you might want to ask (and follow up for alternate solutions) are: * Would a read-only copy of the database suffice when you migrate (keeps users somewhat happy, but need to make sure that there are NO updates) * Is it possible to migrate a set of connected pieces at a time? (reduce a big-bang approach to a series of smaller bangs with interfaces via DB links, etc.) * Is it possible to create an archive copy and purge the older data? (reduce the amount of data to be exported/imported) * Is it possible to parallelize export/import (use all available CPU/IO capacity) and use piping rather than perform an export-ftp-import cycle? * Do you have additional (and equivalent) h/w so that you can test approaches and fine tune the whole thing? These are some of the questions that you might want to start asking of your CIO!!! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, October 15, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Our CIO is pushing a platform change from Tru64 to Sun. We are currently running a VLDB (~1.5 TB) on Tru64. Does anyone have feedback on the following: 1) Experiences with either/both, preferred platform? 2) Experiences with platform changes? (Time required for migration?) 3) Any other thoughts? Thanks for your feedback. I am just starting to research the issue so I am interested in any input. Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Financials and APPS password
April, Mike is right, but this also allows users to use the 'Examine' feature without knowing the APPS password - they can then make *data* changes *directly* to the database - a very strict Oracle Support no-no and a bigger problem. The issue with using a Non-APPS user is that APPS uses a ton of stored procs/pkgs which work only when used as APPS - this includes packages that set and use the ORG Id. I developed an alternative of allowing a set of users and developers to get to traces without the APPS password by setting the Utilities:Diagnostics to Yes at Responsibility or User level rather than at the Database level. This way, you can both *limit* the number of people that can damage the system while still not giving out the APPS password. For the Senior Developers/Team leads/Support folks that *do* need the APPS password for sure, you can still build in a Database level DDL trigger that detects and records *any* DDL changes made. Use this to rap any knuckles connected to fingers that stray! Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 8:14 AM To: Multiple recipients of list ORACLE-L Subject: RE: Financials and APPS password I could be missing something here. If you set the profile option Utilities:Diagnostics to YES users are allowed to enable trace on a session without having to provied the APPS password. Cheers, Mike Hately -Original Message- Sent: 15 October 2003 14:29 To: Multiple recipients of list ORACLE-L April, We lost this battle with our developers - they have the password, along with strict instructions to behave. Nobody else should have the password to any of the schemas (APPS, GL, INV, etc.). We create logins for users that need them and grant the necessary rights to objects. As you know, APPS can do just about anything in the database, so you're asking for trouble if you let the whole company in there. Chances are you already have some objects in that schema like MICROSOFTDTPROPERTIES. Jay [EMAIL PROTECTED] 10/15/03 08:39AM Okay, anyone using Financials... E-Business suite... Oracle 11i... whatever you want to call it... I am trying to apply SOME kind of security to my databases. It appears that it is critical for everyone to be able to access production using the APPS id Finance and accounting people, developers, everyone. What does everyone else do in their setups? The newest reason is the need to run the new Mass Additions Trace which apparently requires that you use the apps id. We have found a way to set up any user with a read only version of what APPS has (since they have to be able to compile reports in production and access production data live rather than a month old clone), but Oracle says that you need to run Mass Additions Trace as apps. Does anyone let the entire company have the production apps user's password? April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas /\ / \ / \ \ / \/ \ \ \ \ Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 *** * E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 *** * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: Problem with undo tablespace and snapshot too old
Helmut, The SELECT article 'Understanding ORA-0155' by Tim Gorman is a must-read. Get a subscription to IOUG, or ask Tim nicely and he might give a copy of the article to you... (Probably better to get an IOUG subs - there is a ton of excellent articles and tech stuff out there) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Daiminger, Helmut [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: Problem with undo tablespace and snapshot too old Hi! We are experiencing a weird problem here... We have automatic undo management enabled and the undo tablespace is 6 GB in size. undo_retention is set to 30 minutes. when a certain transaction runs, it fails with ORA-1555 Snapshot too old, although the undo tablespace only uses 700 MB (out of 6 GB possible). That loos weird to me... Then our other DBA suggested to cut the size of the buffer cache in half and let the transaction run again. We have done that and it worked flawlessly... WHY??? What is the relation between the buffer cache size und rollback (i.e. undo retention)? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Cary's Book - new topic
Ron, It is really simple - Just recreate the *larger* TRCA tables to use GTT as shown below: CREATE GLOBAL TEMPORARY TABLE trca$trace ( trace_id NUMBER, trace_filename VARCHAR2(64), trace_size NUMBER, trca_date DATE, parsed_percent NUMBER, host_name VARCHAR2(64), platform VARCHAR2(40), rdbms_release VARCHAR2(17), instance_name VARCHAR2(16), same_instance VARCHAR2(13), tim_factor NUMBER, start_date VARCHAR2(23), trace_date DATE, start_tim NUMBER, completion_tim NUMBER, duration_secs NUMBER, total_gaps NUMBER, total_cNUMBER, total_eNUMBER, wait_non_idle NUMBER, wait_idle NUMBER, truncated VARCHAR2(9), num_lines NUMBER, cursors_sysNUMBER, cursors_user NUMBER, unique_sql_sys NUMBER, unique_sql_userNUMBER ) ON COMMIT PRESERVE ROWS; CREATE GLOBAL TEMPORARY TABLE trca$parsing_in_cursor ( trace_id NUMBER, line_idNUMBER, cursor_# NUMBER, cursor_id NUMBER, lenNUMBER, depNUMBER, uid$ NUMBER, octNUMBER, lidNUMBER, timNUMBER, hv NUMBER, ad VARCHAR2(32), errNUMBER ) ON COMMIT PRESERVE ROWS; Etc... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Cary's Book - new topic John- Just got back from a vacation and saw this... Our jr DBA is in the process of doing this. Care to share your code??? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] s.comTo: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: RE: Cary's Book - new topic .com 10/08/2003 02:04 PM Please respond to ORACLE-L Raj (and all who use Oracle's Trace analyzer, I 'converted' the trace analzyer tables to GTTs, and no longer had the space issues with large trace files. This is because the data is stored 'temporarily' and is used for reporting in a subsequent SQL in the same session stream, and not reused elsewhere. Haven't really measured performance improvement, but this should ride on all the advantages that GTT provides
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
List, The %INTERFACE% tables (usually) consist of rows that are temporary in nature. The indexes supporting them are 'fragmented' (the term can be argued I suppose). I did test this out on the GL_INTERFACE_N2 index - ANALYZE/VALIDATE and record INDEX_STATS, Rebuild index, ANALYZE/VALIDATE and record INDEX_STATS again. The figures are below, but just to highlight a few: HEIGHT (Index depth) dropped from 3 to 2; BLKS_GETS_PER_ACCESS (expected number of CR reads to get to a row) dropped from 12 to 3; the PCT_USED (percentage of space allocated that is used) increased from 38% to 99%... HEIGHT 3 2 BLOCKS 44804432 LF_ROWS 362409 22552 LF_BLKS 423075 LF_ROWS_LEN 12531538578797 LF_BLK_LEN 79487780 BR_ROWS 422974 BR_BLKS 58 1 BR_ROWS_LEN 134043 1919 BR_BLK_LEN 80288028 DEL_LF_ROWS 339857 0 DEL_LF_ROWS_LEN 119527410 DISTINCT_KEYS 20869 9548 MOST_REPEATED_KEY 38594 8430 BTREE_SPACE 34085664591528 USED_SPACE 12665581580716 PCT_USED38 99 ROWS_PER_KEY17.3659016 2.36196062 BLKS_GETS_PER_ACCESS12.1829508 3.68098031 PRE_ROWS0 0 PRE_ROWS_LEN0 0 For a detailed explanation, look at the definition of INDEX_STATS. YMMV, but you will probably get the most from Non-unique indexes... (as in this case). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, October 15, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Rafiq, John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Indeed the interface tables suffer as well. I would suggest a TRUNCate of these tables after processing monthend (or at an agreed time with the users), so the index will be chopped as well John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Separate Indexes and Data
Title: Message Jared, Any indexes supporting a "In-Today; Gone-Tomorrow" status table will require index rebuilds. Most of them have monotonically increasing numbers which lends itself to a 'holey' index... (I have a bunch of them with Oracle Apps Concurrent Manager and Workflow tables) John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 13, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: Separate Indexes and Datahmmm... fodder for an article I've been contemplating. "Indexes: to rebuild or not to rebuild - that is the question" There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: Separate Indexes and DataI assume that what Rachel is referring to is the fact that indexes willgenerally not release much space when the underlying rows are deleted. Theyjust keep growing, so if you have a large indexed table that frequentlydeletes and inserts the indexes can grow to fairly ridiculous sizes over aperiod of time. We just went through the exercise of rebuilding indexes ona db supporting a 3rd party app and reclaimed about 70% of the allocatedindex space.Jay MillerSr. Oracle DBAx68355-Original Message-Sent: Sunday, October 12, 2003 7:39 AMTo: Multiple recipients of list ORACLE-LHi Rachael,You have me a little confused here.What do you mean by "We over allocate space" ? To the index segments or tothe tablespace ?Why the need to rebuild the indexes ? How are they using more space thanrequired ?What do you mean that you adjust the pctfree so you can determine "how smallyou can resize them to" ?You seem to go to a lot of trouble, I'm just failing to see what it allachieves ???CheersRichard- Original Message -To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to
RE: CBO and cartesian product
Tim/Dilip, Unfortunately, as this is an 'Apps' instance, the parameters DB_FILE_MULTIBLOCK_READ_COUNT should be set to 8 and the OPTIMIZER_INDEX_CACHING parameter should *not* be set (letting it default)... This is as per ML Note 216205.1 - non compliance = not supported. Since this is a customized report though, you *may* be able to get away with setting them within the program (or reverting to RULE as a quick fix) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED] Sent: Saturday, October 11, 2003 11:14 AM To: Multiple recipients of list ORACLE-L Subject: Re: CBO and cartesian product Here is the short answer: = * Set OPTIMIZER_INDEX_CACHING to 90 * Make sure that DB_FILE_MULTIBLOCK_READ_COUNT is not overly high * Also, consider gathering column-level statistics on some of the indexed columns involved, especially if the query in question uses literal data values on them Here is the long answer: Starting in the 8i timeframe, the CBO started borrowing some techniques from data warehouse STAR joins when confronted with any type of query that traversed two different entity-relationship heirarchies starting from the same table. Say you have three tables (to keep it simple). One table is a child entity to the other two tables, which are both parent entities in ERD terms. The CBO detects that both parent tables are much smaller than the child table. OK, so there is no relationship between the two parent tables -- they are both related only through the large child table. Now, think about what traditional join methods are possible: 1) start with one of the parent tables as the driving table, do a indexed nested-loop range-scan during the join to the child table, and then perform indexed nested-loop unique-scan during the final join to the other parent table 2) reverse the order of option #1. Start with the other parent table, join to the child, and then join up to the remaining parent 3) start with the child table and join up (via indexed unique-scans) to the two parent tables The weak point of both of these options is probably the access of the child table. Plain and simple, it is difficult to efficiently get rows from it. It is likely that the index supporting the foreign-key relationship from either parent table is not very efficient by itself, resulting in a very expensive range-scan, requiring a massive number of logical I/Os and cost calculated by the CBO. So, the CBO in 8i started utilizing another option, which initially blew my mind first time I saw it happen. It was the point which I realized that the CBO was _way_ smarter than humans... This additional option is to perform a cartesian join between the two parent tables, to come up with one result set. Then, using the filtered cartesian result set from that join, the CBO probes into the large child table using the _combined_ keys from both parent tables! Rather brilliant choice, in most cases. The cartesian join, despite everybody's visceral fear of it, is actually rather insignificant if the two parent tables are small. And it is even smaller if there are good filtering predicates on those tables in the WHERE clause. So, instead of having to retrieve rows from the large child table using one or the other of the relatively ineffective indexes supporting each foreign key, the CBO merges and uses both keys, resulting in a far more effective access method into the child table. So, chances are good that this is the situation you are facing. Is this correct? Can you verify the basic relationships between the tables involved? So, now the question is: why did the CBO make the wrong choice? First, the default setting of the OPTIMIZER_INDEX_CACHING parameter (i.e. 0) represents a flaw in the basic costing algorithm used by the CBO. Setting the parameter to 90 or so fixes this flaw. For a more detailed explanation, please feel free to view my paper Search for Intelligent Life in the CBO, available online at http://www.EvDBT.com/papers.htm;. Changing that alone may cause the CBO to rethink its decision to go with the derived STAR-join scheme involving a cartesian join, and instead choose the indexed nested-loops scheme which is the __only__ possible choice by the RBO. By discounting the cost of index-based access methods, the CBO (which considers _all_ possible access methods and chooses the one with the lowest cost) may now choose the index-based plan. Once again, the RBO only considered the one plan, which in this case turned into a bit of luck for the RBO, making it look good. You can experiment
RE: USERENV('SESSIONID') on RAC
Rich,
Is there a way to get your own executing program from a
9.2.0.4 RAC node?
USERENV('SESSIONID') and SYS_CONTEXT('USERENV','SESSIONID')
each return a
big fat zero on RAC.
Were you logged in as SYS on the RAC node? I believe the SESSIONID (which is
actually AUDSID) will be 0 for SYS/SYSDBA Internal connects...
04:45:52 SQL show user
USER is SYS (JK - Connected as SYSDBA)
04:45:55 SQL select SYS_CONTEXT('USERENV','SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
0
04:46:04 SQL select USERENV('SESSIONID') from dual;
USERENV('SESSIONID')
0
04:46:17 SQL connect gl
Enter password:
Connected.
04:46:26 SQL select USERENV('SESSIONID') from dual;
USERENV('SESSIONID')
7077637
04:46:32 SQL select SYS_CONTEXT('USERENV','SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
7077637
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
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: Cary's Book - new topic
Title: Message Raj (and all who use Oracle's Trace analyzer, I 'converted' the trace analzyer tables to GTTs, and no longer had the space issues with large trace files. This is because the data is stored 'temporarily' and is used for reporting in a subsequent SQL in the same session stream, and not reused elsewhere. Haven't really measured performance improvement, but this should ride on all the advantages that GTT provides. FWIW! John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 7:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: Cary's Book - new topic Thanks, I have been using that tool for a long time now, it needs a big tablespace (cause everything is loaded in tables) and puts a load on the server. It is good for smaller files, but takes too long on larger files. Nevertheless it is a great utility. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 08, 2003 9:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Cary's Book - new topic go to metalink and check out trace analyzer. ITs a new tool for analyzing 10046 traces. Has ALOT more detail than tkprof. Major improvement. Its on metalink.
RE: how to keep statistics up to date for CBO
Bob, How does one keep CBO statistics for an applications base tables up to date? We are about to implement the CBO any must read documents. I wouldn't call it 'must-read', but you can browse my RBO-to-CBO paper at 'http://www.geocities.com/john_sharmila/links.htm' (click on the paper link). It deals with a few things that you can trip up on. I should probably update it with 9iR2 specific stuff, but haven't yet had the time :( And of course, Tim's CBO paper at http://www.evdbt.com' *is* a must-read! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Please help me tune this io done wait event
Hans, AFAIK, kproc is the slave process for AIO. How many do you see on your system? Maybe you have too many of them and are choking on CPU scheduling... Is your CPU stats Ok at this time? You need to have only as many AIO proces as there are *simultaneous* requests 300 maybe overkill. Long time since I even touched AIX (4.2 last) so I may be way off-course here... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Hans de Git [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: Please help me tune this io done wait event Hi All, We suffer buffer busy waits and io done waits during batch processing. The batch does direct-path inserts (via Sqlloader and insert-append) in a 16k blocksize database (JFS, AIX). Async io servers = 300, maxreqs = 16384 What is the general approach to tune the io done' wait event? The explanation in the Oracle manual is -of course- not clear to me: The session waits for an I/O to complete or it waits for a slave process to become available to submit the I/O request. This event occurs on platforms that do not support asynchronous I/O. AIX does support async IOWhat is the slave process? Which io has to complete? Which write has to complete? Thanks. Regards, Hans de Git _ Hotmail en Messenger on the move http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: sid/serial# vs. audsid: why both?
Jacques, In what cases does the SERIAL# need to be used? Can someone give an example where a session-level command would be applied to an incorrect session object if SERIAL# were not available? For backward compatibility reasons :) Looks like AUDSID wasn't generated 7.2 and prior unless AUDIT_TRAIL was TRUE (even if you don't use auditing). A lot of scripts use SERIAL# and the ALTER SYSTEM KILL SESSION uses the serial#, which should always be available. Why not use AUDSID all the time? Is there a reason why the database keeps track of two session identifying numbers? AUDSID is 0 if connecting as internal. Notes 123128.1 and 122230.1 may help! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Multiple block sizes
Title: Message Mladen/Avinish, I would just be a little careful about keeping 'hot' indexes in 32K blocks. The chances of encountering buffer busy waits during multiple, simultaneous INSERTs and DELETEs would be higher as root blocks and branch blocks that need to be updated would now hold a larger number of entries and thus be more likely candidates for block contention Same is the case with more chances of running out of ITL space/entries in hot data blocks. IMHO, DSS type applications benefit most from larger block sizes and support for multiple block sizes in 9i was provided so that 'large-blocksize' tablespaces that contain transaction history can be transported from a 'otherwise-small blocksize' based OLTP database into DSS databases that traditionally have large block sizes. For e.g. in a 9i Db, you might have the OLTP tables based on 8K blocksized tablespaces, create monthly history from these transaction tablesinto a 32K blocksize based tablespace in the same 9i database so that you can Tablespace-Transport it to a 9i, 32k blocksized DSS database. Back to imbibing a thick, black brew after writing this complicated note :) John KanagarajDB Soft IncPhone: 408-970-7002 (W)Grace - Getting something we do NOT deserveMercy - NOT getting something we DO deserveClick on 'http://www.needhim.org' for Grace and Mercy that is freely available!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 12:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Multiple block sizes Great! You're exactly the guy that I was looking for. Any problems encountered/advice to give? --Mladen GogalaOracle DBA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Wednesday, September 24, 2003 1:55 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Multiple block sizes I have little bit experience on that. I am keeping indexes in 32K block 'cause Oracle access indexes sequentially and placing indexes in large block would help in reducing IO. All the tables are in 8K block size but youcanthink about putting small tables in 2K or 4KB block size to better utilize your RAM. We are on AIX 5.1 , Oracle 9202. -Original Message-From: Mladen Gogala [mailto:[EMAIL PROTECTED]Sent: Wednesday, September 24, 2003 9:05 AMTo: Multiple recipients of list ORACLE-LSubject: Multiple block sizes Does anybody have any experience with the multiple block sizes in the database? I'm about to reconfigure my database to have a tablespace with blocksize 16k in addition to the existing 8k tablespaces. Tables in this tablespace will be loaded weekly and read daily, frequently using full table scan (DW style reporting. I'm planning to have bitmap indexes and the rest of the DW arsenal). Does anybody have any negative experiences with that kind of stuff? It's 9.2.0.4 on RH 7.3. Am I running into ora-7445 and ora-0600 type errors? --Mladen GogalaOracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error,please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient.Wang Trading LLCand any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. DISCLAIMER:This message is intended for the sole use of the individual to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the addressee you are hereby notified that you may not use, copy, disclose, or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email and delete this message. Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged
RE: CLOB
Tanel/Original poster, Let me add one more issue about LOBs - My understanding is that rollback information for LOB changes is allocated from the LOB segment itself, and not from system UNDO or RBS. This is controlled by the PCTVERSION parameter - read more about it in the Concepts and SQL Reference manuals... The offshoot is that the space consumed by LOBs will increase when there are updates, depending on PCTVERSION. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Tanel Poder [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:25 PM To: Multiple recipients of list ORACLE-L Subject: Re: CLOB Ah, I forgot one important issue about LOB performance - if you select a varchar or long, the data in those is returned to client instantly, but if you select a LOB, then only a pointer (locator) is returned and it's up to client whether it sends a reading request to server to get actual contents of LOB. This means additional sqlnet roundtrips for each LOB item. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 5:04 AM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Offshore protests + Idle events (to keep it On-track!)
Dan, You said that very well (as usual). The problem is that Management doesn't usually see the downside of their downsizing actions until the stuff hits the fan. And Larry's statements about self-tuning databases are not helping as well - they help apply pressure on a dwindling market, whether real or apparent. The ability to communicate complex problems in simple, everyday language will _always_ stand you in good stead - whether to the business, to management or your fellow workers. I recently replied patiently without sarcasm to a question on an internal Java user group about why compiling procedures/packages when users are online is a bad idea - I could have been rude and told the largerly development audience off. The VP of Development was on that list (I didn't know about this) and wrote me a nice complimentary note. I hope to stand well when there is a shake-up later on :) On Idle events - I had a situation previously when I was remotely diagnosing a performance issue and noticed a large number of PQ related idle events. Turns out that the DBA had switched on PQ to make the queries 'run faster' - the two CPU server was just choked to death after this. These 'idle' events from a Statspack report helped me solve the issue (turn off PQ on all tables - it was an OLTP system). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Daniel Fink [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 8:05 AM To: Multiple recipients of list ORACLE-L Subject: Re: Offshore protests I realize that it may be terrifying, but there is a great deal of value in becoming indistinguishable. How many accounting/hr/finance jobs are going overseas? None that I have heard of. There are a ton of managers I wish we could offshore (but only about 1 mile off shore...). Bear with me for a moment, as I see the issue of 'self tuning' databases and the 'death' of the dba as all of the same larger issue. Consider Cary's quote Work first to reduce the biggest response time component of a business' most important user action. To optimize we must understand the business and the user, not just look at raw statistics from Enterprise Manager. This requires constant interaction with all levels of business, knowledge of the application architecture and awareness of the business goals. None of these can be done by a canned application nor isolated geek. If you solve problems and make yourself valuable to the business, you are in a better position to stay when others are let go. I'm not advocating any of the sneaky, unethical, self-promoting activities that some of our most despised coworkers are good at, but it is important to manage your own personal, technical and business reputations in a favorable light. Before I step down from my soapbox, I'd like to address the issue of 'idle' events. In my opinion, there are no 'idle' events. Each event can have meaning within the context of the system. I have seen SQL*Net messages indicate a chatty java program and poor file configuration. I have also seen cases where these messages consumed hours of time, but were safe to ignore. The only way you can understand when to discount these events is to have a solid understanding of the application/process. Which is something those canned apps can't do. The only way to understand properly is communication. ducking for cover... Dan Mladen Gogala wrote: No problem with beating people up. Violence is, contrary to Chris Lawson's book, an integral part of DBA job. BTW, Chris Lawsons' Art and Science of Tuning Oracle reads like Dale Carnegie for database administrators. Basically, we should give up our cynical attitude, become indistinguishable from the HR/finance drones and get rid of our geekish culture. Really terrifying stuff. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Migrating from ONAMES to OID
Ponnu, Looks like our resident OiD expert - Rich Jesse - didn't pick this up :) My thoughts are: * My understanding is that although Oracle threatened to yank Onames, they have still allowed it to reside in 9.2.0.x. I haven't heard anything about this in 10g... * OiD is a lot more than just a replacement for Onames - it is the basis for Single-signon and a starting point for Content management. As such, my reading is that it is still not kosher enough... (although I heard a BDE person say that the OiD in iAS 9.0.4 was much improved) * OiD is supposed to be LDAP (v3?) compliant - that would mean that M$ products should work seamlessly against a Corporate LDAP server served out of Oracle OiD. Try getting M$ to bless this ;-) OTOH, OiD is supposed to be able to replicate well from M$ ADS. I haven't had the opportunity to try that out yet... Although I wouldn't advice against progress (Onames - OiD), my question is 'Why fix something that is currently not broken'? Until the whole LDAP space settles down, I would advice waiting. (You still have V7 and that will crimp your style) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ponnusamy Rangasamy [mailto:[EMAIL PROTECTED] Sent: Friday, September 19, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Subject: Migrating from ONAMES to OID Folks, We are in the process of evaluating - migration from Oracle Names to 9i OID and will be glad to know your experiences.. I understand that clients should be 8i. we have Oracle Ver. 7 thru 9i. Listed below is an overview of the procedures.. a) Installing 9i OID on the database server b) Directory Services on LDAP c) Installing ldap clients for all existing oracle clients. d) Schema for OID in a database. Regards, ..Ponnu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ponnusamy Rangasamy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Apps 11.5.8 and 9i
Ron, We are 'in the process' of moving from 11.5.7/8.1.7.4 to 11.5.8/9.2.0.4 - planning/testing starts after approval. We went ahead and installed a test db in any case. Make sure that you start out with 9.2.0.4 - the older 9.2.0.3 is buggy and Oracle seems to have ratified .4 recently. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Apps 11.5.8 and 9i Has anyone upgraded Oracle Applications 11.5.8 from database version 8.1.7 to 9i? Was it good, bad, indifferent in regards to performance? I'd like to because of some of the database enhancements, but the CIO asked the performance question. Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
'Test' Note on Metalink...
Have a laff! See Note 240863.1 (esp the first sentence). This seems to have been around since 11-Jun-2003 (if the Modified date can be believed) John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
Thomas, What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 1:55 PM To: Multiple recipients of list ORACLE-L Subject: Confuzzled on OPTIMiZER_INDEX_COST_ADJ We recently upgraded a production DB to 9.2.0.2The equivalent test tier was upgraded last month. After the production upgrade, one application immediately began experiencing performance issues for a given package where they did not encounter such problems in test. The problem was with one simple SQL statement within the package: SELECT * FROM PARTS WHERE PART_NO = :b1 In production, we are seeing full table scans for this statement while in test it's using an index. We checked stats, indexes, etc, and they are all the same. So I then compared the optimizer parameters and it turns out that in test, optimizer_index_cost_adj is set to 100, but in production it's set to 80.If I do an alter session set optimizer_index_cost_adj to 100 in prod, the statement runs exactly as in test, i.e, with index access. My understanding is that LOWER values of optimizer_index_cost_adj will bias the CBO towards index probes. So, this situation has me confused. What am I missing here? Thanks! Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Confuzzled on OPTIMiZER_INDEX_COST_ADJ
Jeff (sorry - called you Thomas before!) The 9.x optimizer 'peeks' at values in bind variables when generating plans. Maybe that has something to do with it... I really wouldn't know. It might have to do with Oracle versions as well - 9.2.0.4 sorted out a _lot_ of bugs/issues as compared to 9.2.0.2. To get the 10053 trace, you will need to reparse the query - you might have to flush the shared pool or perform other shared sql invalidations (such as generating stats on one of the objects involved). Cut and paste from a 9.2.0.4 10053 trace for a reparsed SQL (see the 'this is a reparse' string) John QUERY alter session set events '10053 trace name context forever, level 1' *** 2003-09-17 13:04:07.750 QUERY select 'this is a reparse' from dual *** PARAMETERS USED BY THE OPTIMIZER OPTIMIZER_FEATURES_ENABLE = 9.2.0 OPTIMIZER_MODE/GOAL = Choose _OPTIMIZER_PERCENT_PARALLEL = 101 HASH_AREA_SIZE = 512000 HASH_JOIN_ENABLED = FALSE HASH_MULTIBLOCK_IO_COUNT = 0 SORT_AREA_SIZE = 256000 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = TRUE STAR_TRANSFORMATION_ENABLED = FALSE _COMPLEX_VIEW_MERGING = TRUE _PUSH_JOIN_PREDICATE = TRUE PARALLEL_BROADCAST_ENABLED = TRUE OPTIMIZER_MAX_PERMUTATIONS = 2000 OPTIMIZER_INDEX_CACHING = 0 _SYSTEM_INDEX_CACHING = 0 OPTIMIZER_INDEX_COST_ADJ = 100 OPTIMIZER_DYNAMIC_SAMPLING = 1 _OPTIMIZER_DYN_SMP_BLKS = 32 QUERY_REWRITE_ENABLED = TRUE QUERY_REWRITE_INTEGRITY = ENFORCED _INDEX_JOIN_ENABLED = TRUE _SORT_ELIMINATION_COST_RATIO = 5 _OR_EXPAND_NVL_PREDICATE = TRUE _NEW_INITIAL_JOIN_ORDERS = TRUE ALWAYS_ANTI_JOIN = CHOOSE ALWAYS_SEMI_JOIN = CHOOSE _OPTIMIZER_MODE_FORCE = TRUE _OPTIMIZER_UNDO_CHANGES = FALSE _UNNEST_SUBQUERY = TRUE _PUSH_JOIN_UNION_VIEW = TRUE _FAST_FULL_SCAN_ENABLED = FALSE _OPTIM_ENHANCE_NNULL_DETECTION = TRUE _ORDERED_NESTED_LOOP = TRUE _NESTED_LOOP_FUDGE = 100 _NO_OR_EXPANSION = FALSE _QUERY_COST_REWRITE = TRUE QUERY_REWRITE_EXPRESSION = TRUE _IMPROVED_ROW_LENGTH_ENABLED = TRUE _USE_NOSEGMENT_INDEXES = FALSE _ENABLE_TYPE_DEP_SELECTIVITY = TRUE _IMPROVED_OUTERJOIN_CARD = TRUE _OPTIMIZER_ADJUST_FOR_NULLS = TRUE _OPTIMIZER_CHOOSE_PERMUTATION = 0 _USE_COLUMN_STATS_FOR_FUNCTION = TRUE _SUBQUERY_PRUNING_ENABLED = TRUE _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50 _SUBQUERY_PRUNING_COST_FACTOR = 20 _LIKE_WITH_BIND_AS_EQUALITY = TRUE _TABLE_SCAN_COST_PLUS_ONE = TRUE _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE _OPTIMIZER_COST_MODEL = CHOOSE _GSETS_ALWAYS_USE_TEMPTABLES = FALSE DB_FILE_MULTIBLOCK_READ_COUNT = 8 _NEW_SORT_COST_ESTIMATE = TRUE _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE _CPU_TO_IO = 0 _PRED_MOVE_AROUND = TRUE *** BASE STATISTICAL INFORMATION *** Table statsTable: DUAL Alias: DUAL TOTAL :: CDN: 1 NBLKS: 1 AVG_ROW_LEN: 2 _OPTIMIZER_PERCENT_PARALLEL = 0 *** SINGLE TABLE ACCESS PATH TABLE: DUAL ORIG CDN: 1 ROUNDED CDN: 1 CMPTD CDN: 1 Access path: tsc Resc: 2 Resp: 2 BEST_CST: 2.00 PATH: 2 Degree: 1 *** OPTIMIZER STATISTICS AND COMPUTATIONS *** GENERAL PLANS *** Join order[1]: DUAL [DUAL] Best so far: TABLE#: 0 CST: 2 CDN: 1 BYTES: 0 Final: CST: 2 CDN: 1 RSC: 2 RSP: 2 BYTES: 0 IO-RSC: 2 IO-RSP: 2 CPU-RSC: 0 CPU-RSP: 0 -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Confuzzled on OPTIMiZER_INDEX_COST_ADJ John, OPTIMIZER_INDEX_CACHING is set to 0 in both databases, all other parameters are also the same. We also kept OPTIMIZER_FEATURES_ENABLE at 8.1.7 in both databases.I tried the 10053 trace but I'm not getting any results in the trace file -- just the query? Thanks. -Original Message- Sent: Wednesday, September 17, 2003 5:10 PM To: Multiple recipients of list ORACLE-L Thomas, What is OPTIMIZER_INDEX_CACHING set to? This one also influences the CBO as well as a host of other parameters (including SORT_AREA_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, .. etc). As well, Histograms and other stats can influence FTS vs Indexed reads. For a complete list of parameters that influence the CBO, you can look up my paper at http://www.geocities.com/john_sharmila/links.htm or look at a 10053 trace... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Thomas Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2003 1:55 PM To: Multiple
RE: Session Stat interpretation - total runtime
Ildefonso, SQL*Net event is a null event, and should not be considered. Hence, taking only meaningful I/O wait times, CPU Time (service time) = 182141 ms ; Conv to secs = 1821 secs Wait Time = 657.21 + 48.00 + 2.60 ~= 708 secs Response time = service time + Wait time = 1821 + 708 = 2529 secs Total Elapsed time = 2622 secs, which ties in neatly (nearly) to 2529 secs... The DB cannot measure time for context switches and wait times less than a msec (I think). Hope this explains! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Erroba, Ildefonso N [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Subject: Session Stat interpretation - total runtime I believe that a number of threads have already discussed how to equate the total runtime of a given session to its individual stats as shown in v$sesstat, but I could not find on my collection of threads relating to this subject. The stats below shows that the individual session stat is more than the total runtime. I calculated session stat using the wait events (1268 secs) + CPU used by this session (182141/100) = 3089 secs, whereas the total runtime is only 2622 secs (sysdate - logon_time). Could somebody help in accounting for the discrepancy? Appreciate any input on this. DB SESSION EVENTS REPORT == SessionStat IDST# Name VALUE === === 3112 CPU used by this session 182141 3111 CPU used when call started 182141 3190 CR blocks created 318 31 238 SQL*Net roundtrips to/from client 2168273 31 177 active txn count during cleanout 12876 31 197 branch node splits 20 31 223 buffer is not pinned count 3240239 31 222 buffer is pinned count 2172655 31 237 bytes received via SQL*Net from client 106496601 31 236 bytes sent via SQL*Net to client 353672034 31 110 calls to get snapshot scn: kcmgss 1955374 31 107 calls to kcmgas 7836 31 105 calls to kcmgcs 6384 3171 change write time 2694 31 178 cleanout - number of ktugct calls 31196 31 165 cleanouts only - consistent read gets 1638 31 193 cluster key scan block gets 636 31 192 cluster key scans 318 3181 commit cleanout failures: block lost 2704 3184 commit cleanout failures: buffer being written 8 3185 commit cleanout failures: callback failure 2 3186 commit cleanouts 103823 3187 commit cleanouts successfully completed 101109 31 176 commit txn count during cleanout 25135 3144 consistent changes 318 3141 consistent gets 8913902 31 102 consistent gets - examination 6640509 31 207 cursor authentications 13 31 163 data blocks consistent reads - undo records applied 318 3143 db block changes 3610861 3140 db block gets 4591125 31 175 deferred (CURRENT) block cleanout applications 88288 3176 dirty buffers inspected 9386 3126 enqueue conversions 122 3127 enqueue releases 12117 3125 enqueue requests 12117 31 235 execute count 1939464 3179 free buffer inspected 9386 3175 free buffer requested 711023 3178 hot buffers moved to head of LRU 29338 31 174 immediate (CR) block cleanout applications 1638 31 173 immediate (CURRENT) block cleanout applications 18011 31 203 index fetch by key 1971817 31 204 index scans kdiixs1 235827 31 196 leaf node 90-10 splits 3234 31 195 leaf node splits 6992 31 0 logons cumulative 1 31 1 logons current 1 3117 messages sent 6363 31 164 no work - consistent read gets 2027606 31 2 opened cursors cumulative 733 31 3 opened cursors current 10 31 233 parse count (hard) 13 31 232 parse count (total) 1103 31 230 parse time cpu 18 31 231 parse time elapsed 20 3142 physical reads 662867 3195 prefetched blocks 320225 3196 prefetched blocks aged out before use 1227 3114 process last non-idle time 1063669608 31 7 recursive calls 8236 31 8 recursive cpu usage 112 31 116 redo buffer allocation retries 14 31 114 redo entries 1808956 31 122 redo log space requests 14 31 123 redo log space wait time 36 31 115 redo size 623135736 3173 redo synch time 276 3172 redo synch writes 138 31 171 rollback changes - undo records applied 29 31
RE: offshoring article
All (in the US at least ;-) - maybe this explains it all... http://www.nytimes.com/2003/09/12/business/12NORR.html?ex=1064030400amp;en= 5e8977089c9764eeamp;ei=5062amp John Sorry Jared for posting this - I think we do need to understand what may be affecting our professional lives.. -Original Message- Sent: Tuesday, September 09, 2003 2:40 PM To: Multiple recipients of list ORACLE-L Makes sense. Peter Barnett wrote: Let's see if I have this straight, the US is nearly a half trillion dollars in debt. It is going to add at least another 87 billion to that number. It has just reduced taxes on its citizens. And, now it is good for the country to send its best paying jobs overseas. Looks to me like the US is determined to become a third world country at warp speed. Can't blame anyone overseas since the decisions are made in the US. --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: They're hired by the people who came out of those very same universities. Most often McKinsey et al are hired to OK decisions that management have a hard time OK'ing themselves for various reasons. To be fair, of all the consulting companies that make money out of telling people that water runs downhill, McKinsey are among the very best. Ryan wrote: Here is a link to an article from McKinsey Co. My favorite positive is that offshoring IT jobs frees Americans up to do other jobs. Now they dont say 'what' jobs, but we are free to do them. If you dont know these are the guys who payed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
Oracle World - Listers get together (proposed Tuesday Sep 9)
Hi all, We talked about this earlier and I wanted to get this mail out before everyone participating departs for OOW. I would propose a Lister's get-together on the evening of Tuesday Sep 9. Monday is the first day, Wed has the 'OracleWorld Appreciation day' in the evening and I am assuming there will be felicatations for Arup and Mogens at this time... (and Thu ends it all). I have the following that have responded (in no particular order): Arup Nanda, Jonathan Gennick, Matthew Adams, Brian McGraw, Ari Kaplan, Cary Millsap (+ other Gurus - Cary brought along Tom Kyte and Kyle Hailey last time?), Connor McDonald (all the way from Down under!), Greg Loughmiller, Matthew Zito, Molina Gerardo and self. We will meet over Dinner at a restaurant across the street from Moscone Center - probably from about 6:30PM? The address is: Chevy's 201 3rd Street (corner of 3rd and Howard) San Francisco, CA 94105 415-543-8060 I will send out a reminder email closer to that time (like Monday :) Let me know if there are additional numbers... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Permissions error
Title: Message Was 'root.sh' runsuccessfully? This should set the SUID/GID (set-UID and GID) on the 'oracle' executable... $ ls -l $ORACLE_HOME/bin/oracle -rwxr-x--x 1 oracle dba 33734776 Jul 9 13:19 /u01/c4prdb/8.1.7/bin/oracle If this is what you see (i.e. rwxr-x---x), then login as 'oracle' and add the SUID bit using the command $ chmod ug+s oracle You should see that the permission changes to 'rws-r-s--x'. You may also have to restart the instances after this change... (not sure about DEC ALPHA). John KanagarajDB Soft IncPhone: 408-970-7002 (W)Grace - Getting something we do NOT deserveMercy - NOT getting something we DO deserveClick on 'http://www.needhim.org' for Grace and Mercy that is freely available!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: Bartolo, David [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 5:19 PMTo: Multiple recipients of list ORACLE-LSubject: Permissions error Hi All I get this error when I try to log into sqlplus from my DEC ALPHA machine through a telnet session. Enter user-name: testid Enter password: ERROR: ORA-01034: ORACLE not available ORA-07320: smsget: shmat error when trying to attach sga. DEC OSF/1 (AXP) Error: 13: Permission denied Did some permissions get corrupted. I am able to connect using sqlplus from my oracle account. This happens when I try to connect from a different account. Thanks David
RE: get sid (session id) and serial#?
Title: Message Need to install @?/rdbms/admin/dbmssupp while connected as SYS. Available on all platforms 8.0.6+ I understand. John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Murali_Pavuloori/[EMAIL PROTECTED] [mailto:Murali_Pavuloori/[EMAIL PROTECTED]] Sent: Friday, August 29, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: get sid (session id) and serial#? Raj, Which version of db are you on? This is not available on 9.2.0.3 Murali.
RE: using developer under Unix versus on PC and then ftp code to
Title: Message NT - uppercase/lowercase no problems. UNIX - got problem! NT - Bill wants to use "\" just to spite UNIX; UNIX - Used "/" right from the beginning NT - Has a lot of GUI goodies that developers may want to use/call. UNIX - Likes the command line, so doesn't support that many GUI calls. I have been bitten enough by this - I would suggest Developing on the platform that you would deploy on... (not only Developer, but any other tool as well) - too many niggling issues that can cost more than then the equivalent Dev system. John KanagarajDB Soft IncPhone: 408-970-7002 (W)Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 1:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: using developer under Unix versus on PC and then ftp code to Pros/Cons/Ideas/Experience?
RE: Congratulations Arup (DBA of the Year)
kidding aside, this does sound like a good idea. an independent group of professionals. And if you are looking for an independent group of 'Oracle' professionals, you always have the IOUG. Granted, we have our share of Developers (!) but then all good DBAs probably were Developers at some point of time (potty training themselves). In spite of all negative comments about association to Oracle, IOUG is still a force to be reckoned with and Oracle does listen and participate. And a large number of Oracle-L members do also hold (and have held) voluntary positions within IOUG, and serve the Oracle community in general. (Arup is one of them btw and so are Jared, Stephen K, Ari Kaplan, Tony Jambu, self, etc). Some of the best papers at IOUG (and the University faculty as well) are from Oracle-L listers. And anyone can belong to IOUG for a yearly sum of $125 or 10 members of an organization for $595. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to find the session holding the library cache pin.
Murali, Running this should help in identifying the object being locked and the SID holding that pin. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** column waiter format a15 column holder format a15 column held_object format a47 column lock_or_pin format a15 column address format a15 column mode_requested format a15 set feedback off set echo off select /*+ ORDERED */ w1.sid || '/' || w1.username waiter, h1.sid || '/' || h1.username holder, o.to_owner || '.' || o.to_name held_object, w.kgllktype lock_or_pin, w.kgllkhdl address, decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1, v$object_dependen cy o where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and (((w.kgllkmod = 0) or (w.kgllkmod= 1)) and ((w.kgllkreq != 0) and (w.kgllkreq != 1 and w.kgllktype= h.kgllktype and w.kgllkhdl = h.kgllkhdl and w.kgllkuse = w1.saddr and h.kgllkuse = h1.saddr and w.kgllkhdl = o.to_address ; -Original Message- [mailto:Murali_Pavuloori/[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 1:14 PM To: Multiple recipients of list ORACLE-L Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: International Language Support Experiences?
Title: Message David, I can share this from an Oracle Apps perspective - we upgraded to UTF8 (a multi byte char set) from WE8ISO5599-1 (single byte Western Eur charset). Some of the biggest problems that we faced are: 1. Cut-and-paste produces incorrect characters which were acceptable in WE8 but failed conversion. I.e. UTF8 is stricter in what it can display as compared to WE8. This was pronounced in the umlaut and other Eur specific characters. 2. Quite a number of third-party applications do not support UTF8 - when asked about Unicode support, many vendors didn't even know what it would mean to support a MBCS such as UTF-8. This may also be the case with your own applications. 3. Middle-ware layers such as ODBC/JDBC don't work very well with UTF8 in the sense that the rules have become stricter and so programs that used to work previously will now fail mysteriously with vague messages (or worse still silently!). 4. A column whichsupports text elements that may now handle MBCswill require more storage width than previously designed for. Thus you may have to look at schema changes to increase VARCHAR2/CHAR columns.. 5. Oracle products themselves may need some patches - you mention iAS - and have functional restrictions. 6. The You won't hit 1 because you are moving from US7ASCII (7 bit) but watch out for the rest! ML Note 158577.1 is a good starting point. I would read this one (and the related links) before the 450 pages - you seem to like reading :) John KanagarajDB Soft IncPhone: 408-970-7002 (W)Disappointment is inevitable, but Discouragement is optional!** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 11:35 AMTo: Multiple recipients of list ORACLE-LSubject: International Language Support Experiences? We have a new requirement to support multiple languages in at least one of our databases. I'm reading the Oracle 9iR2 Globalization Support Guide (450 pages), but wonder if any of you can share real-life experiences regarding: 1. the conversion of existing DBs to broader character sets 2. using Unicode 3. implementing this with 9iAS Our databases currently use US7ASCII with the American character set, but we will likely need to support European, Southeast Asian, and South American languages. Thanks. Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions
RE: perl/shell script for alert log
Welcome back Ethan!
An alternative is using the following lines in init.ora:
event=1555 trace name errorstack level 3
event=4031 trace name errorstack level 3
event=1652 trace name processstate level 10
This catches the dreaded 01555, out of TEMP, and shared pool allocation
errors *along* with the SQL/Stack from the offending process (and thus time
of occurrence).
And keep *all* the event lines together ;-)
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Wednesday, August 27, 2003 1:14 PM
To: Multiple recipients of list ORACLE-L
If you use Connor's script you can modify it to send you the entire chunk of
file it is currently checking in the body of the email. Ideally you are
checking at a frequent interval so the time of the alert is usually about
the same time you get the error message.
One enhancement I suggest to every script is to configure the a SERVERERROR
trigger to throw certain errors out to the alert log. ORA-1555 is one that
will show up at the session level but not at the database level. Out of
TEMP space is another that is frequent at the session level but not the
database level. This way you know who is causing some important errors.
Overtime I find more and more session errors that are really critical
database errors. Try DDL on a table with an unusable index. Pretty big
deal on most production databases but this is a session error and would not
typically show up in the alert log. The risk of course is some huge loop
throwing 1000's of lines into the alert log. I would suggest a governor of
some sort in your servererror trigger.
- Ethan
-Original Message-
Sent: Wednesday, August 27, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L
When I grep something from the alert log,
it never tell me the date and time of the error.
Is there a setting for appending a timestamp on each error?
-Original Message-
Sent: Wednesday, August 27, 2003 1:35 PM
To: Multiple recipients of list ORACLE-L
HTH
#!/bin/sh
# This Script search for Oracle error messages in last
100 lines in the alert log file ,
# keep log to a file.
# You should pass name of ORACLE_SID as a parameter.
#!/usr/bin/sh
#
# Comments: Script checks last 100 lines of
# the alert log for specific
# Oracle errors, e-mails depending on the error.
# Parameter: ORACLE_SID
# ---
#
#
DIR=/u01/app/oracle/admin
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_HOME
ALERT_DEST=/u01/app/oracle/admin/${ORACLE_SID}/bdump
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH
COLLECTOR=`tail -100
$ALERT_DEST/alert_${ORACLE_SID}.log |grep ORA-`
if [ $COLLECTOR ]
then
echo
echo Errors found in:
$ALERT_DEST/alert_${ORACLE_SID}.log
echo
echo $COLLECTOR
echo
fi;
--- AK [EMAIL PROTECTED] wrote:
I am sure you guys might have some nice perl/shell
script to analyze alert log for errors or potential
problem . Can you share it with me /list .
Ohh thanks in advance guys .
-ak
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jose Luis Delgado
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).
For technical support please email [EMAIL PROTECTED] or you can
call (972)721-8257.
This email has been scanned for all viruses by the MessageLabs Email
Security System.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Roger Xu
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
RE: Query results to .csv
One caveat strikes my mind when considering Direct Load... Due to the fact that the buffer is constructed and written directly, the kernel can perform INSERTs only *above* the HWM. If the rate at which you perform Direct INSERTs is high (i.e. multiple runs in a day), then you may have an artificially large segment, most of which is empty. And your FTS will be reaching farther and farther... The situation can be compounded by parallel INSERT where you might acquire different 'start-to-insert' points in parallel. All this is from memory - I think it is mentioned in the Concepts/Admin manual for Direct INSERT - and I might be wrong. Just cross-check this out before implementing... John Kanagaraj -Original Message- Sent: Monday, August 25, 2003 5:09 PM To: Multiple recipients of list ORACLE-L Hi! What about several insert /*+ APPEND NOLOGGING */ commands over database link run in parallel? (Possibly over dedicated network). This is fast and is easier (in case you don't hit any compatibility problems). If you happen to be running on Windows for some reason, you could try to use named pipes network protocol instead of TCP as well. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 25, 2003 11:04 PM Thanks Tanel We will undoubtedly use export/import for the many small tables. We are looking for alternatives that will perform even faster. The insert phase seems to be the slowest part, and that is where SQL*Loader in direct path really shines. Now the next issue is how to produce a CSV file as fast as possible, and so far it looks like Jared's Perl program is the clear winner. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Hi! Spooling from sqlplus is VERY slow. Is the source database Oracle? Then use export/import If not, is there an ODBC driver for source database? Then use Oracle heterogenous services and do your transfer directly, without any intermediate files. Or use some very expensive software for doing this simple job... Tanel. P.S. if you definitely want to spool to textfile fast, Sparky could be what you want... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 25, 2003 7:24 PM Jared - Thanks for posting this. At the moment, we are preparing to move large database to a new server. Based on the advice you posted several months ago, we have been testing SQL*Loader and as you predicted, it is indeed fast. But also as you predicted, using SQL*Plus to create a CSV isn't very fast. Am I correct in assuming the dump.sql will not be the best choice for large tables? We are installing perl since you mentioned that would probably be much faster. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, August 25, 2003 9:40 AM To: Multiple recipients of list ORACLE-L http://www.cybcon.com/~jkstill/util/dump/dump.html On Mon, 2003-08-25 at 05:39, Imran Ashraf wrote: Hi, Whats the best way to write the results of a SQL query to a CSV file? Thanks. _ Hotmail messages direct to your mobile phone http://www.msn.co.uk/msnmobile -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City
RE: optimizer_max_permutations
Just fyi - it seems that Oracle had realized this in advance and has specifically instructed the Oracle Applications 11i installations to set this to 2000. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, August 26, 2003 8:55 AM To: Multiple recipients of list ORACLE-L One thing that the docs don't mention is that '8' (the default in 8) is also a special boundary value. Anything less than 80,000 changes some of ways the optimizer does it work, ie, its not just a reduction in permutations. Can't remember the specifics - join orders spring to mind but there is a metalink note about it. Because of this, there's a school of thought that even on 8i, adopting the (9i default) value of 2000 will improve the general optimizer performance (ie the quality of the decisions it makes). Cheers Connor --- Boivin, Patrice J [EMAIL PROTECTED] wrote: Has anyone worked with this one? http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch11 23.htm#81357 http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch1 123.htm#81357 Patrice. = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Onames and various connections scenarios
Bob, I understand what you are saying (and your pain). My suggestion would be to use the 'namesctl dump_tnsnames' command to dump out the current Onames repository to the PC's $TNS_ADMIN dir via a login script or SMS. You might want o rename the current TNSNAMES.ORA file just prior to that as dump_tnsnames adds to the end of the current one and doesn't handle changes very well. Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, August 26, 2003 6:44 AM To: Multiple recipients of list ORACLE-L Hello All Im back from the trenches to post a quick request for helpg Ive setup onames on 2 servers and standard applications, (our app and sqlplus) connect just fine, a problem situation manifests itself when: 1 users need to connect (add a database) to dba studio. They get a error to the effect cant resolve host name And the other 2. Users connect to remote databases (via vpn) that are in our onames but many of these vpn connections once made do not allow access to *our* network resources. So basically almost all of oour users have one or more of these secenarios which means they will need to maintain a tnsnames file as well. I'm managing about 70+ connect discriptors And I was hoping onames could be a centralised answer Is this common? Or is there a workaround? Thanks! bob -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Metelsky INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: About trcanlzr
Stephane, I have used the trcanlzr on occasion when required by an iTAR. Be careful when the trace file is big - it can blow out the tablespace holding the TRCANLYZR tables. Since the data in these tables are temporary in nature - they are used only for reporting - I converted the bigger tables into GTTs with no issues. Can't answer the dbms_system.set_ev though I do remember using them on 7.3.4 (was a while ago). John -Original Message- From: Stephane Paquette [mailto:[EMAIL PROTECTED] Sent: Monday, August 25, 2003 11:25 AM To: Multiple recipients of list ORACLE-L Subject: About trcanlzr Hi, I juststart testing the trcanlzr scripts (metalink 224270.1) I did not remember seeing any traffic on that utility onoracle-l , that's why I went on orafaq where there is only a post by Jamadagni Rajendra . I just open a tar to get the dbms_support script to enable 10046 tracing in other session as dbms_system.set_ev is not working here (8172 aix) but Oracle says it is only legitimate to use it on 9i. So, any feedback on trcanlzr scripts andis it normal thatdbms_system.set_ev is not working on 8172 ? Any workaround ? I've 9i relaease 2on my pc, I guessI can copy the dbmssup.sql and prvtsupp.plb over the 8172/aix databases, yes/no ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED]
RE: Performance Problem
Laura, Keep in mind that analyzing tables/indexes will invalidate related SQL in the shared pool. If you have Statspack snapshots at that time, you will see that both latching (for shared pool/library cache) as well as waits for 'library cache pin/locks/loads' was high at that time. You may have observed that logins freeze up, SQL processing literally stops and nothing gets done. This is why you should *always* analyze during off hours (or at least light load times). The CBO in 8.0.5 (lower than 8.1.7.3 anyway) had a number of issues so I would wait until 8.1.7 in any case. Also I wouldn't roll back the Stats... CBO _is_ the way to go, but the path from Rule to Cost is strewn with hidden mines. Tim Gorman's paper at 'http://www.evdbt.com' and mine at 'http://www.geocities.com/john_sharmila/links.htm' will help you avoiding these mines! For a more in-depth look, you can look at Jonathan Lewis and Wolfgang Breitling's sites. Also keep in mind that when the CBO processes an SQL where at least _one_ object is analyzed and some aren't, it assumes ridiculously low default values for these objects and that will result in horrendous performance as it will make wrong decisions. The key is this: Analyze all or Analyze none. Another caveat is that the CBO will default when certain type of objects or operations are attempted (even if the mode is RULE). I would also suggest using DBMS_STATS rather than the ANALYZE command in 8.1.7+. You can read all about it in the paper... John Kanagaraj -Original Message- Sent: Monday, August 25, 2003 1:49 PM To: Multiple recipients of list ORACLE-L We currently have an application we are trying to speed up. In researching rule/cost based optimizers, I read that the cost based optimizer was the way to go (although rule had its moments) because that is where Oracle would be focusing any upgrades, enhancements, etc. So I analyzed all tables and indexes. It brought our application to a stand still!! I then deleted the statistics and the application ran like before...slow. I know that I must have missed something although it seemed so straight forward. I verified that all tables were analyzed because I read that this would cause an extra step if all the tables were not analyzed. The database is Oracle 8.0.5. This weekend I will be upgrading to 8.1.7. The operating system is NT 4.0. Does anyone know something that could point me in the right direction? Thank you for your help. Laura -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Burton, Laura INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle World anyone?
All, I having taken on co-ordinating the Oracle-l listers get-together at OOW this year (Sep 7-11). So far, I have Jonathan Gennick, Matt Adams, Brian McGraw, Gerardo Molina and self. If any of you are considering a visit to the Bay area at that time - OOW or otherwise - you are welcome to attend. I will send out another invite closer to that time. John Kanagaraj -Original Message- Sent: Thursday, August 14, 2003 8:14 PM To: Multiple recipients of list ORACLE-L And there will be a bunch of us 'Silicon Valley' types who can arrange a get-together for ORACLE-L members. The rowdy bunch that got together last year nearly tore up the Restaurant, btw John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jonathan Gennick [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 7:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle World anyone? Thursday, August 14, 2003, 9:24:29 PM, you wrote: SM Just completed the registration, and was wondering how much company I was SM going to have there. SM Who else has plans to attend? I'll be there. I'm even presenting this year. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: URGENT!!! My 9i databases are not shutting down
Title: Message Tanel, It is more like 'two is company, three is a crowd'. The 'sync; sync; sync' was used in the days of yore when the disk controller was flaky and you had a few seconds of 'think time' on your hand in between commands. As Hemant says, it became a habit which still dogs me after 19 years - I have my (younger) colleagues here asking 'what is sync'? This was far better than my initial days when we used 8" AC Floppy drives whose speed varied with the electricity supply frequency and we had to use a frequency meter to note down the frequency during a floppy recording. [Btw - the term 'floppy' came from this media which was a flexible plastic based magnetic disk enclosed in a flexible - floppy - cover. And there was a 5 1/4" version before it finally became the 3 1/2" 'firm' disk]. I also worked on card punch IBM machines with 64 Kb RAM and 2 Mb (that right 2 MB!) system drives. Now back to Oracle stuff before Jared bears down on us :) John -Original Message-From: Hemant K Chitale [mailto:[EMAIL PROTECTED] Sent: Thursday, August 21, 2003 9:19 AMTo: Multiple recipients of list ORACLE-LSubject: Re: URGENT!!! My 9i databases are not shutting down Habit. The "sync" command is picked up by the scheduler "sched" and may not be executed immediately.With three "sync" calls, at least one goes through by the time you are ready to type in "shutdown" or "init 0"HemantAt 08:04 AM 21-08-03 -0800, you wrote: Hi!I've always wondered why 3 syncs. Is it quaranteed, that after *exactly 3* syncs everything has been written to disk? Or it more like that after that number of syncs, most of the changes should be on disk? (sounds stupid)Tanel. - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Thursday, August 21, 2003 12:04 AM Subject: Re: URGENT!!! My 9i databases are not shutting down cleanly/cons I'd suggest throwing a couple ALTER SYSTEM CHECKPOINT commands just prior to the SHUTDOWN ABORT, to help speed up the subsequent STARTUP (and just to make me feel better)... Does anyone remember the UNIX mantra of entering "sync; sync; sync" before "halt"? :-) on 8/20/03 11:09 AM, April Wells at [EMAIL PROTECTED] wrote: We have started waiting 90 min then do shutdown abort, startup, shutdown immediate April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: URGENT!!! My 9i databases are not shutting down cleanly/cons Which brings up that thread of - if they are hard to shutdown and possibly going to crash anyway then . why not just wait some period of time and do the old shutdown abort. -Original Message- From: April Wells [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: URGENT!!! My 9i databases are not shutting down cleanly/cons We do, Paula... both in 9.0.1.3 and 9.2.0.2 we have trouble getting them to shutdown elegantly... and they sometimes crash April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas Few people really enjoy the simple pleasure of flying a kite Adam Wells age 11 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 11:50 AM To: Multiple recipients of list ORACLE-L Subject: RE: URGENT!!! My 9i databases are not shutting down cleanly/cons BTW, nothing happening in terms of processes that would hang-up the system. Also, I have check alert and trace files and there are no obvious errors. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 20, 2003 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: URGENT!!! My 9i databases are not shutting down cleanly/cons There seems to be a problem with consistent shutdowns in 9i Oracle 9i 64bit 9.0.1.3.0 - infrastructure database that comes with the application server 9.2.0.1.0 - that is the version of RDBMS we are running. Does anyone have problems shutting down their databases consistently with 9i? The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive,
RE: library cahce pin wait on drop user
The user probably has code objects (stored proc/pkgs) as well owns tables which are being replicated elsewhere? The lib cache pins can be explained by the need to lock/pin affected objects in the shared pool for invalidation. These objects are code owned by the user or are referring the objects owned by the user Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Friday, August 15, 2003 7:10 AM To: Multiple recipients of list ORACLE-L I'm doing some departed user cleanup and the 'drop user cascade' hangs for 2 -3 minutes before completeing. I ran a 10046 trace and it does a lot of waiting for 'library cache pin' - 100 times for 308 centiseconds each time. Dropped two different users, 100 library cache pin waits per user. It hits the wait in dbms_repcat_utl.drop_user_repschema(:myuser). While I was amazed at all the things a drop user has to do, what the heck is the drop_user_repschema doing that is causing all this library cache pin issues? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle World anyone?
And there will be a bunch of us 'Silicon Valley' types who can arrange a get-together for ORACLE-L members. The rowdy bunch that got together last year nearly tore up the Restaurant, btw John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jonathan Gennick [mailto:[EMAIL PROTECTED] Sent: Thursday, August 14, 2003 7:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Oracle World anyone? Thursday, August 14, 2003, 9:24:29 PM, you wrote: SM Just completed the registration, and was wondering how much company I was SM going to have there. SM Who else has plans to attend? I'll be there. I'm even presenting this year. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Interesting Locking Problem
Interesting indeed... I didn't see a version number in your note, but I am assuming that it is 8.1.7.x. We have also had some _very_ strange occurrences of 'library cache lock' / 'library cache pin' problems - sessions en-masse went into this state during high activity times on a Solaris based 8.1.7.3 Apps 11i DB supporting about 300 users. [One would have thought that lib cache pins/locks arose from voluntary/involuntary compiles of stored proc/packages caused by Duhlevelopers changing code on the sly]. We tightened up code changes, but this still occurred. We finally traced it down to a bug - one of the sessions obtains a 'row cache lock' for manipulating the dd cache for a specific table, and then 'hangs' and many other session queue behind this one with lib cache pins/locks on that same table. Oracle of course told us to upgrade to 8.1.7.4 - and we did after a long round of testing. And this occurred again yesterday - and Oracle is telling us to go 9.2.x as it was _really_ fixed in 9... Apparently this occurs when either a MV refresh on that object takes place, or a combination of TRUNCATEs (possibly on Partitioned tables) and INSERTs take place at the same time, during heavy load. Both these situations were likely in our case. I think a ML search of certain keywords would generate a bug list of sufficient size! John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Subject: Interesting Locking Problem List, Below is the description of a locking problem I ran into today. Any help to figure out what was happening here would be appreciated. Maybe I was overlooking something obvious, but I hope not. :) An MS Word version is available at: http://www.cybcon.com/~jkstill/CIM_enqueue_wait_2003_07_31.doc as it may be a little easier to read. -- I ran into a rather interesting problem today, and I am hoping that someone can help me determine what was happening here. The app owner informed me that there were some problems with our CIM app this morning. As this app sometimes has issues with locking, I first checked snip -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: If you replied... Optimizer Mode question with regard v$sqlar
Mike, This means that the same query was executed at least twice with a slightly different executing environment which caused the optimizer to choose a different execution path. See ML Note 1013747.102. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve The opinions and advice expressed above are solely mine and not those of my employer or client! -Original Message- Sent: Thursday, July 31, 2003 11:14 AM To: Multiple recipients of list ORACLE-L All my e-mail was sys$hosed last night so could you please resend any and all responses to this since last night. Anybody run into a situation where you see optimizer_mode equal multiple_children_present when the optimizer_mode is set to choose in the init*.ora file ? If so, did you follow up on it and try and determine why this was the case and your results ? There is very little information on Metalink regarding this issue. Thank you for your time in advance. Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: direct path write waits, please help
Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Managing Archived Redo Logs
Just to add that you _can_ be informed of this if you monitor the alert log. The following errors are logged (from a live system, and way back from 7.3.4!) Thread 1 advanced to log sequence 69529 Current log# 1 seq# 69529 mem# 0: /u090/oradata//REDO0101.DBF Current log# 1 seq# 69529 mem# 1: /u091/oradata//REDO0102.DBF ...skipping... ORA-00255: error archiving log 1 of thread 1, sequence # 69616 ORA-00312: online log 1 thread 1: '/u090/oradata//REDO0101.DBF' ORA-00312: online log 1 thread 1: '/u091/oradata//REDO0102.DBF' ORA-00272: error writing archive log ORA-00334: archived log: '/u091/oradata//arch/arch1_69616.log' ARCH: ORA-00255: error archiving log 1 of thread 1, sequence # 69616 ORA-00312: online log 1 thread 1: '/u090/oradata//REDO0101.DBF' ORA-00312: online log 1 thread 1: '/u091/oradata//REDO0102.DBF' ORA-00272: error writing archive log ORA-00334: archived log: '/u091/oradata//arch/arch1_69616.log' Mon May 13 05:36:57 2002 Thread 1 cannot allocate new log, sequence 69618 Checkpoint not complete John Kanagaraj -Original Message- Sent: Wednesday, July 23, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Yes you have to move them to tape and either compress or delete them once you have backed up the logs and datafiles Check out the Backup and Recovery Handbook by Velpuri, there are some pretty good examples there. If you don't clean out the archive directory on occasion, the database will hang when that filesystem is filled. you won't get a message that it's hung but it will hang. --- Farnsworth, Dave [EMAIL PROTECTED] wrote: I finally get to put our 8.1.7 on NT databases into archivelog mode. We have a third party app vendor that would not support us if I did this but I finally convinced them that is the way to go and it should not effect the app. Anyway, I am reading chapter 7 from the Administrators Guide, Managing Archived Redo Logs. I know I have to set the parameters in the init.ora to achieve automatic archiving; log_archive_start=true log_archive_dest_1 = location=my\disk\drive log_archive_format=%%ORACLE_SID%%T%T%S.ARC -or somthing like that One thing I don't see in TFM is, do these archived redo logs just keep accumulating in the destination directory set in the log_archive_dest_1 parameter? Do I need to create a process to get them to tape and then once on tape, delete these old archive redo logs through my process? I'm just excited to be able to finally go to archivelog mode. Once I get the basics down then I want to investigate using RMAN. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: MicroSlop DTC
Jesse, You could always get around this problem by obtaining an upto date TNSNAMES.ORA on any Names-aware client using the 'namesctl dump_tnsnames' command that will create/update the tnsnames.ora with the entries from Names servers. I would suggest renaming the original tnsnames.ora _just_ before doing this, and having some script check the sizes of the created file (just in case). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2003 9:25 AM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Unfortunately, it's not just a problem with this product. Some Oracle products like OEM can incorrectly populate TNSNAMES.ORA for you because of course that's always the best thing to do (and isn't there another Oracle product that requires it or am I confusing that with the semi-Intelligent Agent's requirement of a LISTENER.ORA?). And Quest's QCO will largely not work correctly in v2.4 (and to some extent in 2.5) without a correctly populated TNSNAMES.ORA (sorry Jacques!). I went around and around with Quest Support as to why this is incorrect and why I refuse to manually populate a TNSNAMES.ORA on some or all clients. Supposedly, it'll be fixed in v3. Desperately trying to get rid of all TNSNAMES.ORAs on all non-DBA boxes... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Subject: RE: MicroSlop DTC Yes, as well as SQL*Plus and ODBCTST. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 2:59 PM To: Multiple recipients of list ORACLE-L Hum, does tnsping resolve the service correctly? Ron Thomas 9.2.0.1.0 On Win 2K. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, July 14, 2003 1:54 PM To: Multiple recipients of list ORACLE-L I've seen this happen with older versions of the sqlnet client (Different application, same symptom). What version of the client are you using? Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To All, especially any WEB developers out there. We've a WEB based application that uses MS DTC. OK, so we turned on XA in the database, but the web servers do not want to play with our normal ONmase setup. Instead they only want to work with a TNSNAMES.ORA file in the appriopriate place. I've been all over MicroSlop Technet and Metalink as well as several other IIS sites with no results. Therefore anyone know why this is?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Estimating User Load on the system
Raj, Assuming that these users will logoff normally (for the most cases), you could have a database level LOGOFF trigger that captures the following 'stats' from these sessions (out of v$MYSTAT): CPU used by this session (Cpu time used) user commits (Number of transactions - figuratively!) physical reads + physical reads (direct, lob, etc.) + db block changes (former is reads, latter is the number of changes that would cause writes on behalf of that process for Log, Undo and DBFile) session pga/uga memory max (Memory usage) SQL*Net roundtrips to/from client and dblink (Network usage) Collect and summarize system wide (via STATSPACK) and for individual users via LOGOFF just to compare. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Monday, July 14, 2003 10:14 AM To: Multiple recipients of list ORACLE-L Thanks Dennis, I have periodic (10 mins) snapshots taken of following views ... 1. gv$session 2. gv$sesstat 3. gv$transaction 4. gv$sess_io 5. gv$sysstat Now, I really _can't_ use Statspack, because it tells me overall score. I need to compute the load put on system by a set list of users ... it is kind of computation of possible charge back. So, I have the underlying system stats. but I am having tough time to put them in perspective for management types. I'd like to show them %CPU usage, %IO load, %Memory being used ... any ideas? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, July 14, 2003 12:49 PM To: Multiple recipients of list ORACLE-L Raj I'll second Jared's idea. Don Burleson in his book Oracle9i High-Performance Tuning with STATSPACK makes a strong point that looking at system load must start with the underlying system. Any look at Oracle performance must begin with an understanding of what the system load was at that time. As Jared points out, the three components are CPU, I/O, and memory. For example, you may find that one of these target users is the high CPU consumer from an Oracle perspective at a point in time. Now, if you discovered the system CPUs weren't being taxed at that time your conclusions might be different than if you discovered the system CPUs were pegged at 100% at that time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, July 14, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Load on a system is in general comprised of three components: CPU, memory and IO. If you have something in place to track those on a per user basis, then you're doing well. You might consider also tracking other users, so that you have a basis for comparison. Jared On Monday 14 July 2003 07:04, Jamadagni, Rajendra wrote: I have been asked to compute the load put on the system by a 'select group of userids'. I know these users and have put something in place where I sample periodically following 1. session stats 2. session io 3. system stats 4. number of sessions 5. v$transaction Am I missing something? Has anyone done this before? If so, what have you computed ? TIA Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail
RE: tunning an index built
How about Sort_area_size? This will matter - check for 'sorts to disk' and 'sort rows' from that session's v$sesstat (joined to v$statname). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve; Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Gurelei [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 08, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Subject: tunning an index built Hi. I'm trying to tune an index build. The table currently has about 65mil rows and I'm building a unique index, which takes about 55min to finish. The table size is about 3.4G, index is about the same size. I have tried different degrees of parallelism (up to 32), nologging is set in the create index script as well as on the tablespace. I noticed a lot of i/o waits during the buid and a lot of paging to and from filesystem, the paging area however appears to be unused. when I do lsps -a, it only shows 1% usage. What should be my next move? What should I look at? i have increased db_cache to 800M, sort area to 50M thanks Gene __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: wierd wait event - library cache load lock
Henry, These locks should normally occur when dependent objects needs to be compiled (internally by the kernel or externally by scripts) on account of invalidations. This problem is magnified in large ERP packages which exhibits lots of dependencies and objects. Since you mention that this is just moving into Production, you are probably experiencing a lot of last-minute changes that are causing this. The only way of controlling this is by scheduling changes to off-hours (even in Dev/UA), and most certainly in Production. Sort DBA_OBJECTS by LAST_DDL_TIME to check which objects changed. You could also write an ON DDL trigger to log details of who modified which object from where and at what time. You could look at ML Note 62143.1 'Understanding and Tuning the Shared Pool' to understand the intricacies of the Shared pool. If this is not due to invalidations, then it could be on account of a number of shared pool related bugs (don't know what version you are on). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Henry Poras [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 9:06 AM To: Multiple recipients of list ORACLE-L Subject: wierd wait event - library cache load lock We have a PeopleSoft system (Finanacials) that is just moving into production. Some of the conversion and data entry stuff was running slowly so I peaked at our system and session wait events (as well as looking at some recent history with statspack). There was a huge amount of time_waited for 'library cache load lock'. Never came across this one before. According to the Docs, it is a lock used to load an object into the library cache (you don't want the same object loaded more than once). I am trying to find more information so I can debug this. Of course the problem isn't there today (other modules are being worked on. Maybe that is why). Just trying to be ready when/if this happens again. I'm thinking a 10046 trace might give me some object information about what is being loaded/locked. Metalink suggests taking a systemstate dump (though they seem to mix up 'library cache lock' with 'library cache load lock'. Don't know if the same tracking techniques work on both.). Anybody else see this before? Suggestions on what to do if it shows up again? Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: CACHE/NOCACHE
Ravi, The DB Buffer cache 'aging' algorithm has changed in 8.1.x - it is now based on a touch count algorithm. If the table is not updated and is frequently accessed, it will continue to remain in the Buffer cache, whether it is NOCACHE or not. Figuring out performance problems should usually start with looking at wait events, rather than the buffer cache. the 'Oracle Performance Tuning 101' book by Gaja Vaidyanatha and Kirti Deshpande of this list should be you first stop! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 I don't know what the future holds for me, but I do know who holds my future! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Ravi Kulkarni [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 4:41 PM To: Multiple recipients of list ORACLE-L Subject: CACHE/NOCACHE Gurus, Reposting.. since this might have got lost in emails. Any thoughts...? Thanks, -Ravi. .. Help me figure this one out. Was helping a colleague diagnose slow response time (8.1.7/Solaris running Peoplesoft ). x$bh showed 102,248 out of 170,000 buffers belonged to a single table, which he said he cached explicitly. He did NOCACHE (on my suggestion) on the large table. I still find that the table is in buffer cache (even Buff# haven't changed - starts with buf#=1 - not sure if this means LRU end) even after a week. DB cannot be bounced since it is production. Do you know of any reason why it is not flushed out of cache when table is altered to NoCache? Thanks, Ravi. .. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Microsoft VS Oracle (again)
Rich, So, what's the case for code changes? TAF (Transparent Application Failover) will provide both SESSION failover as well as SELECT failover. In the former case, the session aborts on the now-failed server and starts from the beginning on the new node, while the latter enables user with open cursors to continue fetching on them after failure by re-executing the cursors on the new node. The former does not require code changes, while the latter does, and requires code changes done on a limited number of executable environments (JDBC Thin and OCI come to mind) that support 'TAF Callback'. TAF *with RAC* will provide the environment for a clustered environment where the user can failover from one node to another node accessing the same data (as compared to TAF in a replicated environment). Hope this answers your question. Murali Vallath [are you listening in, Murali?] may be able to add some details [Hint!] John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Microsoft VS Oracle (again) Has anyone read the articles? One point states that failover for RAC requires coding changes to take advantage of it. Not from the demo I saw. HPaq (or whoever they are these days) took a circa '99 Oracle test GUI called Oracle Workload Generator and got failover to work with only changes to the sqlnet.ora. I've seen the demo twice, once with Unix servers and once with Windohs servers (since the app is Windohs, the client had to be Windohs), and while the Unix did the failover much faster (1-2 secs vs. 20-30 secs), both worked seamlessly. As an aside, the load balancing queries worked flawlessly, too. So, what's the case for code changes? Makes me want to read the articles further... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, July 01, 2003 1:00 PM To: Multiple recipients of list ORACLE-L FYI One of my friends at Microsoft, (yes I must to confess, I have friends at MS) gave me a present, it's a 4 cd's kit called SQL Server 2000 for the Oracle Customer, the kit consist in 4 cd's with demos, docs, presentations, videos and a lot of stuff showing why sql server is a better option as a DB instead oracle, contains price lists, performance evaluation and many other information, maybe you'd like to spend some of your time giving Billy a chance to defend his product. The 4 cd's are available (almost completely) as links in: http://www.microsoft.com/sql/oraclekit Any comments? Gabriel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 10046 Trace file questions
Dan, 4) A subsequent statement has several space management (activity on fet$ and uet$) activities. The tablespace/file that is passed in as a bind variable are associated with a 'temp' tablespace. However, the tablespace is set up as dictionary managed. This indicates that sorting is being done by this operation and that the sort segment space management is being tracked in the data dictionary. Jared already commented on 1-3. Actually, 4 also seems pretty reasonable. If the TEMP tablespace is dictionary managed, then you should see deletes on FET$ and inserts on UET$ during allocation of extents. If the CONTENTS were TEMPORARY, and the instance was bounced recently and this was the initial set of sorts, then I would expect to see the same - these should taper off as the whole of TEMP is 'consumed' - this is never de-allocated. I would expect, though, to see 'direct path writes' and 'direct path reads' events in the trace John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve; Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: nfile parameter problem
Madhu, I wanted to reduce the open connections by each oracle processes As was clearly explained (nay! demonstrated) by Tanel, you _cannot_ reduce the open connections as each Db file that contains the requested block will be opened by the server process. MTS is one option, but the basic problem could be that you have way too many Datafiles... Consolidating this files (via CTAS, Export/Import, etc.) will reduce the number of open files, as there will be lesser number of files to open. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: SORT_AREA_SIZE question
Bart, Another consideration is the effect of S_A_S on the CBO. Setting this (and letting other dependent parameters such as HASH_AREA_SIZE which is 2 x S_A_S unless set) may adversely affect the decisions that the CBO would take for otherwise 'sane' plans. As for the original problem, an ON LOGON system trigger should allow specific connections to set a larger SAS. And no one has mentioned SORT_AREA_RETAINED_SIZE yet... I wonder why! John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, June 25, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Hi! At the least the wording is incorrect, a user who *connects* to instance, doesn't allocate any sort memory unless there is a need for sort. And if there's need for 100k, only that much will be allocated. And when the sort is over (and rows returned), the sort memory is freed. On the other hand, if all of the 10 users do huge sorts with sort_area_size 100M, they could have 1GB allocated altogether, even more than that, check my other mail. So yeah, this could introduce paging if having not enough memory. Btw, in mixed environments you could set sort_area_size to small value and create logon trigger which sets s_a_s big for reporting users.. just a thought about swapping issues... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, June 25, 2003 6:39 PM Bart Each user which connects to the DB will consume that amount of RAM on top of any memory used by the application. A lot depends on how much memory your server has i.e. 10 users will use at least 1Gb of RAM. It could induce swapping which is a killer. Our DSS systems use 20Mb for sort area mind you we have 18Gb temp tablespace Regards Nigel Bishop Snr. Oracle DBA ioko Tel DDI: +44 (0) 1904 435 458 Mobile: +44 (0) 7881 624 386 Fax: +44 (0) 1904 435 450 Email:[EMAIL PROTECTED] www.ioko.com -Original Message- Sent: 25 June 2003 15:39 To: Multiple recipients of list ORACLE-L Hello, I joined this list last week, so I apologize in advance if I'm asking a question that has previously been answered. I am responsible for a reporting database/data mart that is approximately 175 GB. Our main fact table ranges from 1-14 GB depending upon how far along we are into our financial year. I have large reports that run full table scans on this table daily. In an effort to keep as much of the sorting in memory as possible I have specified SORT_AREA_SIZE to be 100MB. Some of the tuning books I am reading now are making me second-guess myself and I am wondering if this is overkill. Can anyone provide some advice on how large they are setting their SORT_AREA_SIZE values for their DSS systems? Thanks in advance, Bart Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: nfile parameter problem
Dennis, I don't think there is an underlying _architectural_ problem (although there could be an OS bug that does not reuse File pointer slots, thus leading to an ever increasing slot usage). MTS would have reduced the _total_ number of open files across the OS by reducing the number of processes that opens files (since they are now shared). Monitoring 'sar -v' on a regular basis (or even writing some scripts that chop up the columns under the 'ov' columns and alert when this is 0) is a good proactive idea. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 3:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: nfile parameter problem John - Is there some underlying problem with the O.S.? To me MTS doesn't make sense on top of an application server that is doing connection pooling. I would tend to first suggest reducing the number of connections the A.S. is configured for. But I suspect they have performed some study that tells them that they need that many connections. I thing Madhu hinted at that when he said they weren't handling the current number of connections well. Maybe the server is too small or too little memory. Okay, I'm running pretty thin on my O.S. expertise here, but something just isn't making sense. I have a big server with hundreds of dedicated connections and hundreds of files, and don't have this issue. Hopefully I won't encounter it anytime soon. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Madhu, I wanted to reduce the open connections by each oracle processes As was clearly explained (nay! demonstrated) by Tanel, you _cannot_ reduce the open connections as each Db file that contains the requested block will be opened by the server process. MTS is one option, but the basic problem could be that you have way too many Datafiles... Consolidating this files (via CTAS, Export/Import, etc.) will reduce the number of open files, as there will be lesser number of files to open. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: SMON taking 50% of CPU and certain queries hanging
Sam, Can you check if PQ is enabled (via PARALLEL_* ) and you have PDML/PQ in use because of DB objects or queries? If so, the you might be overwhelming the number of CPUs in the system via too many PQs/PQ servers. If the key word is 'upgraded a test server' as in an OS upgrade on AIX, then you should check if you have lost previous values of AIO (configured via SMIT AIO). John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve; Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 4:49 AM To: Multiple recipients of list ORACLE-L Subject: SMON taking 50% of CPU and certain queries hanging I recently upgraded a test server and found out today that it was hanging. SQLPLUS etc... I checked alert log and found following errors WARNING: aiowait timed out 1 times WARNING: aiowait timed out 2 times I shutdown and restarted and since then Ive noticed that only certain queries hang and the SMON process is running at 50% CPU. This Test service has been running for ages, allthough I did do an upgrade to the Application a week ago. Its the end of day so I will leave it running and see if SMON finishes. Ive checked space etc..checked metalink briefly but if any of you can give me some suggestions I will try them. thanks Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: oracle authentication from windows
All, Oracle has rounded all this discussion up in Note:207959.1 'All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS' which is a jump off point to *lots* of other Notes. John -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Friday, June 20, 2003 12:16 PM To: Multiple recipients of list ORACLE-L Subject: Re: oracle authentication from windows Pete, Apprciate your comments. You are right in stating that if the OPS$ accounts have special privs they might be abused. But how it is any different than any other user id with special privileges whose password is not guarded well? The security hole does not come from the fact that remote_os_authent is true, but due to lax security management. Removing OPS$ accounts will not help increase the security any more than simply evaluating who has what privileges. Instead of fighting the introduction of ops$ accounts, what I suggested was to have a safe practice of setting a prefix. Of course, the privileges of such accounts should be carefully monitored and accesses should be provided to the bare minimum; dba accounts are certainly a big no. In your example you specified, this is rather ridiculous to have a form for a dba user. Why not use OEM, for free? In my book I have addressed some of these issues and common misconceptions and tried to separate myths from facts. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, June 20, 2003 6:19 AM Hi Arup, Remote OS authentication whether with OPS$ or not is still a risk. You are intimating that SYSTEM is the only risky account involved here. What if any of the newly created OPS$ accounts have useful privileges. I have seen a similar application to the one described recently. There were forms within the application for administration and user management (in oracle, not the application) and the users who had access to these were assigned the DBA role and were of course external accounts. I think what you should add to your comment is that the issue is overrated is that any OPS$ / external accounts should not have any dangerous privileges granted and certainly not DBA. If you can guess the name of an admin account even if its OPS$ then the issue is still severe. cheers Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Anyone using IBM's FastT900 SAN for Oracle DBs?
Rich, BTW, are you RAID 0ing along with your RAID 1 or are you relying on the SAN cache for performance? Whole DB or just data? We are pure RAID 1 for the whole DB (along with the Apps). It was a long fight, but worth it,and I had a savvy SA on my side early on. John -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: Anyone using IBM's FastT900 SAN for Oracle DBs?
Rich, There are two big issues with backup of an OLTP VLDB such as an ERP database: Backup window, and redo generation during hot backup mode. Disk I/O and network degradation during backup are also issues, albeit to a lesser extent. While RMAN would obviate the redo generation part, your backup window can still stretch out quite a bit depending on the backup architecture(dedicated backup server, tape libraries, drive speed and streaming, network segregation, disk staging capability, etc.) One of the best ways of performing backup of an OLTP VLDB is by the use of mirroring technologies - Hitachi ShadowImage, EMC BCV, IBMs whatever-it-is - is by breaking off a mirror copy when the *whole* database is in Hot backup mode. The backup can then be read off the mirror copy using a path that is different from the production path in the network fabric. Another option is presenting this copy to the server that performs the backup so the production box never suffers. This implies of course that you have a large enough SAN, and have configured the mirrors, _and_ the backup server is connected to the same SAN as the ERP and is thus able to mount the now-broken mirror. The issue with this is the resync that takes place when the mirror is brought back for resilvering. If the mirror copy is placed in the same server (i.e. not broken out) then resync takes much lesser time - and hour or two depending on the amount of writes as well as the efficiency of the SAN software. Some of the SANs are also able to perform a lazy catch-up where busy disks We have used this technique successfully [ not on IBM or EMC though, although there is no reason why this cannot be done ], but it costs $$. And I have _all_ RAID 1 volumes on the ERP SAN, after having successfully fought off a RAID5 'initiative' when we moved from a previous box :) [Mogens - Does this qualify me for an elevated status in the BAARF party?] -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: Anyone using IBM's FastT900 SAN for Oracle DBs? We're testing a FastT900 (http://www.storage.ibm.com/disk/fastt/fast900/index.html) to see how it will handle our IO thruput, but we've got one question as to how we're going to do our daily snapshot of our production DB. With our current RAID set (HP's AutoRAID -- that's why we're looking for a new solution. BAARF -- Battle Against Auto Raid Filesystems?), we do a simple hot backup and copy the production DB's datafiles tablespace-by-tablespace to a JBOD. Once that JBOD copy is put to tape, we recover it as a new database for our users to do what-if ERP scenarios. We're wondering how to accomplish this with the FastT900. Yes, we could do it the same way, but that seems to be a waste of Big SAN Power, doesn't it? The big difference between our current layout and the FastT900 layout is that the former-JBOD copy of the production DB will now reside on the FastT900 along with the production DB. I guess what I was thinking was to put all TSs into hot backup mode, perform some FastT900 magic in less than X minutes to copy the DB, then end backup mode on the TSs. No, I don't know what the X threshold is yet -- for the sake of argument, let's say 10. So, does anyone with experience on the FastTs have any ideas? Some of the terms thrown out are Business Continuous Volumes and Third Mirrors, although it doesn't seem like the FastT900 supports a third mirror, and I'm a bit skeptical (a DBA trait?) about the time it would take to resync the mirrors before we could do our snapshot. I'm contacting the Sales guys, too, but thought I'd see if anyone from an SA/DBA standpoint (hey, some of us do BOTH, OK?) would have any pertinent thoughts. Thanks! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA p.s. No disks were subjected to RAIDs outside of 10 and/or 0+1 in these tests. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Resend: Anyone using IBM's FastT900 SAN for Oracle DBs?
/** Resending, as I fat-fingered the previous one :( **/ Rich, There are two big issues with backup of an OLTP VLDB such as an ERP database: Backup window, and redo generation during hot backup mode. Disk I/O and network degradation during backup are also issues, albeit to a lesser extent. While RMAN would obviate the redo generation part, your backup window when using RMAN can still stretch out quite a bit depending on the backup architecture and capacity (dedicated backup server, tape libraries, drive speed and streaming, network segregation, disk staging capability, etc.) One of the best ways of performing backup of an OLTP VLDB is by the use of mirroring technologies - Hitachi ShadowImage, EMC BCV, IBMs whatever-it-is - and breaking off a mirror copy when the *whole* database is in Hot backup mode. The backup can then be read off the mirror copy using a path that is different from the production path in the network fabric. Another option is presenting this copy to the server that performs the backup so the production box never suffers. This implies of course that you have a large enough SAN, and have configured the mirrors, _and_ the backup server is connected to the same SAN as the ERP and is thus able to mount the now-broken mirror in the latter option. The issue with this approach is the resync that takes place when the mirror is brought back for resilvering. If the mirror copy is placed in the same server (i.e. not broken out) then resync takes much lesser time - and hour or two depending on the amount of writes as well as the efficiency of the SAN software. Some of the SANs are also able to perform a lazy catch-up where busy disks are left for later catchup, etc. We have used this technique successfully [ not on IBM or EMC though, although there is no reason why this cannot be done ], but it costs $$. And I have an _all_ RAID 1 volumes on the ERP SAN, after having successfully fought off a RAID5 'initiative' when we moved from a previous box :) [Mogens - Does this qualify me for an elevated status in the BAARF party?] John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, positive uplifting Christian music - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: Anyone using IBM's FastT900 SAN for Oracle DBs? We're testing a FastT900 (http://www.storage.ibm.com/disk/fastt/fast900/index.html) to see how it will handle our IO thruput, but we've got one question as to how we're going to do our daily snapshot of our production DB. With our current RAID set (HP's AutoRAID -- that's why we're looking for a new solution. BAARF -- Battle Against Auto Raid Filesystems?), we do a simple hot backup and copy the production DB's datafiles tablespace-by-tablespace to a JBOD. Once that JBOD copy is put to tape, we recover it as a new database for our users to do what-if ERP scenarios. We're wondering how to accomplish this with the FastT900. Yes, we could do it the same way, but that seems to be a waste of Big SAN Power, doesn't it? The big difference between our current layout and the FastT900 layout is that the former-JBOD copy of the production DB will now reside on the FastT900 along with the production DB. I guess what I was thinking was to put all TSs into hot backup mode, perform some FastT900 magic in less than X minutes to copy the DB, then end backup mode on the TSs. No, I don't know what the X threshold is yet -- for the sake of argument, let's say 10. So, does anyone with experience on the FastTs have any ideas? Some of the terms thrown out are Business Continuous Volumes and Third Mirrors, although it doesn't seem like the FastT900 supports a third mirror, and I'm a bit skeptical (a DBA trait?) about the time it would take to resync the mirrors before we could do our snapshot. I'm contacting the Sales guys, too, but thought I'd see if anyone from an SA/DBA standpoint (hey, some of us do BOTH, OK?) would have any pertinent thoughts. Thanks! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA p.s. No disks were subjected to RAIDs outside of 10 and/or 0+1 in these tests. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line
RE: Fragmentation ?
Aaah - now I understand. The LRU rule does not hold good once the Goddess applies her personal touch and 'accesses' these blocks (sorry - books) :) So they need to stay in the DB B(l)ock buffer cache as they now migrate to the MRU end of the cache chain... The blocks that do need to go out of the (book) cache are actually those that have been updated! Couldn't resist the rambling - it is Friday! John -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: Fragmentation ? your wife's rule wouldn't work in my case... every few years (usually less than 10 but on occasion 10 works too) I go on a re-reading spree. back to old friends, comfort food of books. I'd have to buy all new copies if I threw books out. I do, on rare occasion, get rid of books. My oracle books that tell me how to tune Version 7 are one example :) --- Niall Litchfield [EMAIL PROTECTED] wrote: I worked with a really smart guy once whom I won't name for obvious reasons. He had previously worked for a software co that said Our product includes an archive routine. It didn't, they never had to write one because hey disks held 3 times the storage for half the price before anyone wanted to archive anything - at which point you just bought some more storage. I also probably ought to include the ongoing marital dispute that I am having regarding books, my wife maintains that anything I haven't accessed for a decade could be disposed of (think Tolkien, Donaldson, Asimov, Shakespeare, Auden). *I* maintain well we could always buy another bookcase. Logic tends to dictate my wifes approach, management I feel confident would say ah well doesn't cost much lets buy another bookcase. In summary Niall's 2nd rule states that data always goes in but never comes out. It's parkinsons law for databases Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Lee Sent: 13 June 2003 18:45 To: Multiple recipients of list ORACLE-L Subject: RE: Fragmentation ? That's one thing good about the databases here. Tablespace fragmentation is rarely a problem. Most of the database here are a Database Roach Motel: Data checks in. It doesn't check out. Somehow, the data purge part of the application -- that they intended to put in one of these days -- never got written. (For non-USA dwellers, Roach Motel is a trap for roaches. It has a sticky floor, and the sales motto is Roaches check in. They don't check out.) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL
RE: Oracle Names
Dennis, 1. Are any of you using the Oracle Names? Of course! Used Names in 7.3 (since '97 or so? Was buggy at that time, but the 8i Names is quite Ok). Used to manage about 4000 static desktops (7.3) and now have about 2000+ Laptops and 100-odd NT/UNIX servers being served off an 8i Names service. 2. Is it as easy to configure as Oracle makes it sound, or is it difficult? The setup is quite easy, but make sure that you have a primary and secondary ONS servers. The notes on ML about ONAMES is sparse (to say the least), and not many people have set it up (that really surprises me). You _should_ use a Repository (a small DB schema) if you use a primary and secondary, although the notes imply that you don't need to. The 8i version allows you to load the repository via a TNSNAMES.ORA file (using LOAD_TNSNAMES) and generate a new TNSNAMES.ORA via DUMP_TNSNAMES - Cool! (I had to do this by hand for 7.3). Also make sure that you have standardized on the DEFAULT_DOMAIN - if you have multiple values for this across the organization, you may have some challenges... Oracle makes it sound difficult, as the doco tries to explain the details of root and sub-domains similar to an extrenal DNS. Just treat this as something within the organization, and it becomes failrly simple. If your users have had their own quirks in their local TNSNAMES.ORA (such as MYDB pointing to PROD and XYZ pointing to ABC), you will have problems. In other words, the difficult part is the *data* part, not the setup. We used SMS to get a cross-section of TNSNAMES.ORA files from laptops and used Perl (thanks Jared!) to massage them and consolidate the entries, and still missed a few. 3. Is Names reasonably robust? I can see this as yet another single point of failure. It is as much a single point of failure as a central SAN (and I am not talking the [EMAIL PROTECTED] here :) or a Data centre. You can have multiple ONS servers (not sure if you can go above four or five - I have two and it seems adequate) It is *very* crucial that you use DNS aliases to point to the primary and secondary, rather than the actual hostnames. This way, you can quickly setup another server and changes the aliases on the DNS servers if one of them goes down, or has an extended downtime. It is also very helpful for maintenance... As for reliability, our Primary Name server has been continually up since Nov 16 and has collected 664 CPU minutes, which works out to an average of about 4 CPU minutes per day, about the same as that of the DNS Daemon. $ ps -ef | grep name oracle 4625 1 0 Nov 16 ? 664:28 /oracle/onames_home/bin/names names.ctlstart=yes root 4667 1 1 Apr 19 ? 121:51 /usr/local/sbin/named At this time, the number of Name server 'Requests received' (and serviced) was 18,920,223. The repository does NOT need to be highly available. The Names servers cache required information and will load off these if restarted during the repository outage. The secondaries can be setup to resync from Primary every 'x' minutes as well. 4. Oracle hinted that Oracle Names is going away in favor of LDAP. Is this imminent, or just a scare tactic? I had held off using Names because of this, but the company has made a commitment to MS Active Directory, which I gather from the list postings isn't very compatible with Oracle's LDAP. I wouldn't worry about it - Names is available in 9i, and OiD is flaky from what I have heard so far. In summary: GO for it! Hth, John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve; Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris, The default statspack snapshot is at level 5, which collects Top SQL (by buffer and Phys reads, etc.) from the Shared pool, and that would cause significant latching for a large shared pool which in turn results in a high CPU usage. You could try a level 0 snapshot and look at the CPU utilization at that time... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Disappointment is inevitable, but Discouragement is optional! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Boris Dali [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Subject: statspack snapshots cause 3-4 sec of 100% CPU utilization As subject line indicates standard (level 5) snapshots make vmstat 1 or sar -u 1 100 show 100% CPU utilization (75% system mode) for about 3 seconds. Is this normal? Is statspack that brutal on CPU? And why would that be a system mode primarily? Environment: Oracle 9.2.0.2 on Mandrake 9.0 (2.4.19-16mdkenterprise) 2.4GHz uniprocessor P4 box, 1GB SDRAM TIA, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris,
Nice to discuss this with someone who understands the numerous (and various)
options of 'sar' :) I use 'sar -r' to cross verify the 'rate of need for
swap' - a sudden increase may mean either bursts of I/O (eating up File
buffer space), memory leaks or a sudden rush of programs Could you take
a quick snapshot of the top 20 CPU consumers using the script below when the
snapshot runs? It takes the SID as a parameter to grep out only Oracle
processes for that SID. The interesting part is that the CPUTIME *and*
ELAPSED time is shown - you should run the snapshot as a script (as in
sqlplus perfstat/ @snapshot.sql) where snapshot.sql has an execute,
followed by an exit. This way, one has a crude set of CPU and Elapsed time
for that process as it runs...
I use this to quickly point out processes that are heavy and consistent CPU
consumers, allowing me to rap some knuckles ;-)
#!/bin/ksh
#
# Name: top20.ksh
# Purpose: Display the top 20 CPU consumers. Specify a SID to collect
# only those top procs related to that SID in a multi-db system
# Author:John Kanagaraj, DBSoft Inc/ Aug 2001
# Notes: Tested and works on Solaris - may need adjustment for other OS
#
uptime
echo PID %CPURUSER CPUTIME ELAPSED COMMAND
if [ $# == 1 ]; then
ps -eo pid,pcpu,ruser,time,etime,args | grep $1 | sort -nr +1 |
head -20 | awk '{print substr($0,1,80)}'
else
ps -eo pid,pcpu,ruser,time,etime,args | sort -nr +1 | head -20 |
awk '{print substr($0,1,80)}'
fi
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Great, uplifting music - http://www.klove.com
** The opinions and statements above are entirely my own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John.
No there's no paging/swapping going on (1GB real
memory for a single 200MB SGA and just a couple of
users).
Out of curiousity, John. I usually measure paging via
vmstat (si/so columns on Linux and pi/po everywhere
else - everywhere else being HP-UX, Solaris and
AIX), as well as via sar -w (swpin/s, swpot/s) on
HP-UX/Solaris and sar -W on Linux (pswpin/s,
pswpot/s).
Is sar -r a better way?
Quick check shows that on Linux it seems to report
memory and swap utilization (but not in terms of
rates, rather absolute numbers). On HP-UX it doesn't
seem to be covered by man pages, but effectively the
output is the same as -w. On Solaris it shows unused
memory pages and disk blocks. And I don't currently
have any IBM boxes around
As for the wrong bucket... well, I'll be able to
verify it in the next couple of weeks on Solaris and
for sure on HP-UX. One thing I know is that both
vmstat and sar -u agree here on Mandrake that it is
the kernel-mode that chews up most of the CPU for this
3-4 sec snapshot time.
Thanks,
Boris Dali.
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Boris,
I missed the second part of your question...
apologies. If your SGA/Shared
pool was partly swapped out, I would assume that you
might see an increased
'system' utilization. Did you check 'sar -q' and
'sar -r' at the same time
to check? I haven't used mandrake - just wondering
if the CPU cycles used
for memory access are being counted against the
wrong pigeonhole..
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Grace - Getting something we don't deserve; Mercy -
NOT getting something we
deserve
Click on 'http://www.needhim.org' for Grace and
Mercy that is freely
available!
** The opinions and statements above are entirely my
own and not those of my
employer or clients **
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Boris,
I missed the second part of your question... apologies. If your SGA/Shared
pool was partly swapped out, I would assume that you might see an increased
'system' utilization. Did you check 'sar -q' and 'sar -r' at the same time
to check? I haven't used mandrake - just wondering if the CPU cycles used
for memory access are being counted against the wrong pigeonhole..
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Grace - Getting something we don't deserve; Mercy - NOT getting something we
deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and statements above are entirely my own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 7:45 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: statspack snapshots cause 3-4 sec of 100% CPU utilization
Thanks, John.
Any insight as to why is it primarily the system mode
CPU? I've seen high system mode CPU utilization in
non-Oracle stuff (like NFS), but for Oracle I thought
it should be primarily user mode?
Does Oracle's CPU used by this session represents
user-, kernel-mode or both? And what about c in the
raw traces?
Thanks John,
Boris Dali.
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Boris,
The default statspack snapshot is at level 5, which
collects Top SQL (by
buffer and Phys reads, etc.) from the Shared pool,
and that would cause
significant latching for a large shared pool which
in turn results in a high
CPU usage. You could try a level 0 snapshot and look
at the CPU utilization
at that time...
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Disappointment is inevitable, but Discouragement is
optional!
** The opinions and statements above are entirely my
own and not those of my
employer or clients **
-Original Message-
From: Boris Dali [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L
Subject: statspack snapshots cause 3-4 sec of 100%
CPU utilization
As subject line indicates standard (level 5)
snapshots
make vmstat 1 or sar -u 1 100 show 100% CPU
utilization (75% system mode) for about 3 seconds.
Is this normal? Is statspack that brutal on CPU?
And
why would that be a system mode primarily?
Environment:
Oracle 9.2.0.2 on Mandrake 9.0
(2.4.19-16mdkenterprise)
2.4GHz uniprocessor P4 box, 1GB SDRAM
TIA,
Boris Dali.
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Boris Dali
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and
web hosting services
-
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: John Kanagaraj
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).
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see
RE: performance questions
Sai, To quote part of an excellent article from the 'Goddess' on SLAs: (titled: Managing User Expectations with Service Level Agreements) When people talk about availability, the discussion almost always begins with hardware. Numbers and sizes of servers, disk arrays, communication lines and, on occasion, additional physical data center sites. Once the hardware is out of the way, the talk turns to software. Do we have a backup of our data? What about the programs that we use to manipulate and/or access the information? She then goes ahead to bust a number of myths and give you the low-down. The article is a 'protected' one at IOUG's SELECT Online, so you can either join IOUG (a good idea IMHO not only for many more such articles, but also be part of a group that can make a difference) or request the Goddess for a copy. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Monday, June 02, 2003 10:05 PM To: Multiple recipients of list ORACLE-L hi gurus this is a kind of query i have faced a few times in the recent past and which has really forced me to start this thread. as everyone knows, there is always what we call a SLA or in other words a service level agreement (may be called differently in different places) which infact means defining a time for any transaction to go thru in the database. This is very important in emvironments which handle transactions affecting sales or just normal queries against huge databases which helps a sales force or a front office customer support force.. Defining this is always a difficult task and i believe will keep changing as time goes on - factors like number of records,the number of databases running on a box(probably SLA was defined initially on a single box-single db kind of env and now the same box has more databases),memory,network,disk performance,number of transactions or can i say the load profile et al. there have been cases where i have been asked questions like why this query took more time than SLA when it was running ok sometime back. i find it very difficult to convince saying that ther! e are factors affecting this and not just explain plan et al(correct me if i am wrong) or in other words a scenario that says my test environment is running faster than prod (everything on the db side are the same except the way the disks are configured or the load profile on both dbs). here is my question? is there a way to determine this SLA. since it keeps changing how do we really determine it. there is a soltuion that comes right out saying abenchmark can help u do this but how do we extrapolate or assume that there was no benchmark done at the beginning how do we validate/dtermine this magic number. i have some ideas on this but nothing is very concrete. can someone give me some feedback on this..if u feel that this is not a right question to be put in this forum i apologize but i would like to take this up with someone who is interested and i wouldnt use this mailing list for the same. thanks for ur time sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 11i new features
Nope! - Oracle 10i will be the end of the world (as Oracle knows it at least :) since we already have an Oracle 11i (aka Oracle Applications 11i - but generally known in the ERP world as Oracle 11i or Apps 11i). Fyi - it mutated from Apps 10.7 to Apps 11.0.x and now to Apps 11.5.x - the 'i' replacing the 5 here. So when 11.5.9 is released later this year and they run out of numbers there, I believe it will mutate to Oracle 12i or Apps 12i The life of the person who is in charge of numbering at Oracle is gonna become quite complicated for sure. John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com http://www.klove.com/ ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Friday, May 30, 2003 9:20 PM To: Multiple recipients of list ORACLE-L UNLESS Oracle decides to skip Oracle 10i and go directly to Oracle 11i. They did something similar to bring the numbers for Oracle Database and Oracle Designer and Oracle Application Servers all up to 9i (9iDB, 9iAS, Designer 9i. . . .) After all, they must have introduced enough new features and bugs to skip a number or two?!?! - Babette -Original Message- Sent: Wednesday, May 28, 2003 8:05 AM To: Multiple recipients of list ORACLE-L Oracle 11/11i: http://www.vapourware.com http://www.vapourware.com Oracle Apps 11/11i http://www.oracle.com/appsnet/content.html http://www.oracle.com/appsnet/content.html http://www.oaug.org/ http://www.oaug.org/ http://www.appsdba.com http://www.appsdba.com HTH Mark -Original Message- Sent: 28 May 2003 11:08 To: Multiple recipients of list ORACLE-L Hi Can anybody tell me a website where I can know about new features in Oracle 11/11i? Thanks in Advance Ajay K. Garg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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: dbms_stats broken
Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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).
