Date conversion
Hi all One of the developers came to me. They are using a Genesys call logging system. It has apparently stored a date time in a number field as the number of seconds since 1970 1 Jan 0:00 They need to know exactly what time this is. I can not off hand remember that oracle got a conversion routine for this. Anyone know of one, they prefer something already there compared to something that I write. I was thinking figure out how many days the seconds represent, add this to the date of 1 Jan 1970. then figure out what time of day the remainder seconds are to determine the time of day ? Ideas. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: (+27) 82 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Database tracking
Title: RE: Database tracking Are you willing to share the solution with us ? George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: (+27) 82 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Orr, Steve [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 18:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: Database tracking I concur with the recommendation to use STATSPACK but you might want to augment it. I take STATSPACK snapshots every 15 minutes and if there's a performance problem caused by a few bad queries I can usually isolate the offenders. But constant fined-grained STATSPACK snapshots can be a lot of overhead so you may want something more lightweight. I've developed a DBA web app which queries V$SYSSTAT and V$SYSTEM_EVENT every minute. I assume regular queries on these tables do not impact system performance enough to worry about. I record the result sets from these queries outside of Oracle in a very light weight RRDTool "round robin database." (RRDTool is free, http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/.) >From this I can produce 55 graphs on demand for 5 different time spans: daily; weekly; monthly; quarterly; and yearly. Of course damagement loves graphs/pictures. The storage needed for one plus year's worth of minute to minute V$SYSSTAT/V$SYSTEM_EVENT query data only comes to 3.2MB for each database instance being monitored. A cool thing to do is produce a graph with a visually obvious spike in some V$SYSTEM_EVENT wait statistic at say 3:15PM yesterday then correlate that graphic spike to a specific problem query as recorded in STATSPACK. It provides nice "smoking gun" incriminating evidence to be used for putting duhvelopers on trial. Steve Orr Bozeman, MT -Original Message- From: Terrian, Tom (Contractor) (DAASC) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 12:52 PM To: Multiple recipients of list ORACLE-L Subject: RE: Database tracking Ok, thanks -Original Message- Sent: Wednesday, January 15, 2003 2:10 PM To: Multiple recipients of list ORACLE-L Tom - I'll provide an example of what we do and maybe it will give you some ideas. On one database, the users have identified a certain process that has marginal performance at best, and when anything gets out of whack it gets bad real fast. So the developers have added a logging feature in the application. When the user hits "submit", that is logged, along with the username and other relevant data. When the results are returned to the user, that is also logged. Now we have a measurement from the user's perspective. This has allowed us to detect problems a number of times before they were serious. When the users have complained about intolerable performance it has given us some actual numbers to review (rather than opinions or impressions). I think you have a good idea, but if possible you should go end-to-end, rather than just the database. I would go with STATSPACK snapshots if you just want to look at the server. That gives you an overall server status, while a single query may not detect a lot of severe problems. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, January 15, 2003 11:54 AM To: Multiple recipients of list ORACLE-L All, I would like to track the performance of my production databases by running the same SQL statement against each database every 5 minutes or so and recording the results. For example: sql> set timing on; sql> select count(*) from dba_tables; That was I would know if they are getting faster or slower over time. As anyone already done this? Would there be a good SQL statement to use? Thanks, Tom Terrian
RE: Installer does not run on Win 2k Service Pack 3
Hi there If this is being run a P4 have a look on metalink for Pentium 4. There is a workaround you need to follow. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: (+27) 82 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 16 January 2003 14:09 PM To: Multiple recipients of list ORACLE-L Hi DBAs, Has anyone had trouble installing Oracle 8.x on Win 2k Service Pack 3?. When I tried installing 8.1.6 and 8.1.7 the installer does not start. Oracle 9.x installs fine. I do not know if it related to SP3 or not as I was able to install on similar server with SP 2. Thanks Rick -- 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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: email out of oracle
-- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hately, Mike (NESL-IT) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stefan Jahnke > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (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: Leonard, George > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (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: 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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
email out of oracle
rself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Hately, Mike (NESL-IT) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Viewer
http://www.geocities.com/alexdabr/ George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: 082 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 06 January 2003 14:59 PM To: Multiple recipients of list ORACLE-L What is the URL for this product? RWB -- 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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Viewer
Hi there Been using it on and off for 2 yrs now since the original standard edition. Very good and def worth while, Alexey also normally releases a new/updated version every 1-2 months. There is some great features planed for the future from discussions with him. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Cell: 082 655 2466 Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 02 January 2003 22:35 PM To: Multiple recipients of list ORACLE-L Anybody using this product? What are your thoughts about it? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tracy Rahmlow INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: import and rollback segments
I am going with the commit=y it seems the best option since I am already using buffer size. This way I don't need to do anything with my current rollbacks. Thx for the feedback PS: can not remember now who it is but someone on this group wrote a new rman backup recover manual, can someone pls forward me the title of the book, someone asked me for it, know if I go to borders will find it just can not remember at the moment, Thx all, have a good weekend. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 20 December 2002 14:54 PM To: Multiple recipients of list ORACLE-L You can not specify RB segment for import to use. Consider using commit=y option of the import command. - Kirti -Original Message- Sent: Friday, December 20, 2002 7:09 AM To: Multiple recipients of list ORACLE-L Hi all Can a person somehow force a import process to use a specific rollback segment. I got a live system with many smallish segments, the dump file is about 20 GB and take just over 12 hours to process but it requires a big rollback segment. I don't want to offline the small once so that only a big one is online since this causes problems with the day to day operation of the client systems. Ideas. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
import and rollback segments
Hi all Can a person somehow force a import process to use a specific rollback segment. I got a live system with many smallish segments, the dump file is about 20 GB and take just over 12 hours to process but it requires a big rollback segment. I don't want to offline the small once so that only a big one is online since this causes problems with the day to day operation of the client systems. Ideas. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 & SAN Experiences?
Title: The Sys Hi there What are the thoughts about the Xiotech - Magnitude. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Babette Turner-Underwood [mailto:[EMAIL PROTECTED]] Sent: 11 November 2002 15:29 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oracle & SAN Experiences? A client site that I was supporting a while ago had big problems with their NAS. While doing Oracle backups to tape, the application would drop connections. In a SAN environment, there might also be similar problems. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David Wagoner Sent: Friday, November 08, 2002 9:59 AM To: Multiple recipients of list ORACLE-L Subject: Oracle & SAN Experiences? The Sys. Admin. team wants to consolidate storage (and probably get a new toy too) on all of our servers, so they are evaluating a SAN (LSI Logic E4600). The DBA team is doing some research to determine the pros and cons of doing this, and I'd like to hear any of your experiences (good and bad) using SAN with Oracle. My understanding is that all of our database servers would remain intact, but the attached disk storage would move into the SAN. So, we still have the Production, Test, and App. servers with their processors and memory, Oracle homes, etc. The SAN will hold database files from Production, Test, Apps., staging, ODS,data warehouse, etc. Their arguments: -the SAN is very scalable (500 GB - 40 TB) -easy to manage disks in one central location -fancy statistics collection on all SAN disks -much higher throughput on the fiber SAN connections than with locally attached disk arrays -capable of using mixed RAID levels (0, 1, 1+0, 5, etc.) -can partition sets of disks in the SAN for specific server access -Snapshot backup capability is very fast in the SAN (much faster than traditional Oracle backups) DBA arguments: -How will this affect database performance? -What are the drawbacks, if any, with the pre-fetch of data performed by the SAN (i.e., SAN cache) -How tunable is the SAN -Fast, small disks are better for performance and less wasted space than the typical huge disks in a SAN (it's possible to use smaller disks in the SAN) -Prove it! After reading the "Sane SAN" article and a case study about Volvo implementing a SAN, I believe it's possible to have a great Oracle/SAN implementation if it's setup correctly and tuned. Other resources that you can Google are "Using SVA SnapShot with Oracle", "Performance Benchmark LSI Logic E4600 (STK D178)", "SAN Storage for Open Systems Environments", and of course check the OraFaq. Thanks for sharing, David Wagoner Oracle DBA
RE: LOCALLY MANAGED TABLESPACE
the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
why is this happening
tem 2 5 7 0 folder 3 item 1 2 1 1 2nd subfolder 6 2 3 folder 2 item 3 3 2 2 folder 2 item 2 4 2 1 folder 2 item 1 - Desired SQL statement results: ID PARENTID NODEORDER DESCRIPTION -- -- -- 1 0 0 top folder 9 1 0 1st subfolder 2 1 1 2nd subfolder 4 2 1 folder 2 item 1 3 2 2 folder 2 item 2 6 2 3 folder 2 item 3 7 1 2 3rd subfolder 5 7 0 folder 3 item 1 8 7 1 folder 3 item 2 - Kudos to anyone who can figure out how to do this via SQL. Steve Orr Bozeman, Montana -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index Partitioning
Title: Adhoc queries and limiting the amount of records queried... Hi all System is Siebel Ok I got a couple of tables about 6 GB big, up to 10 million rows. Some of the tables come out of the box with 30+ indexes, now for those not aware Siebel does not support the dropping of any indexes. I do though know what my indexes is that are hit the most and was thinking of partitioning them and or maybe the tables. Firstly If I was to partition only the table, Would I have to make any changes to the currently indexes other than rebuilding them. Second. Is it possible for to only partition a selected index. Here I keep on seeing local and global partitions - indexes. From what I can determine Global is bad news. How do I do local, what consideration are there. Some of the tables/indexes considered is orders, orderliness, shipments, shipments lines, all with well over 5 million records each. The queries is not date specific but more account or contact specific for the order if that's helps. I was considering partitioning on order_id but again it looks best to use hash partitioning since there is not real way of saying the queries will always go this way. Basically trying to reduce the work for Oracle to get to data. All my queries is already using the best possible index. Comment, suggestions thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
RE: DB monitoring using SNMP MIBs
pproaches for you. >1. You write your own tool that will issue SNMP alerts. Perhaps this > would be a Unix daemon process that executes database queries, and then > based on what it finds, issues SNMP alerts. >2. Use an existing tool to accomplish what you want. > > If your desire is to create a database monitoring tool that you can give > away for free, then sell to CA for a lot of money, take path #1. If your > goal is to become a better DBA, then I would go with #2. > > > Dennis Williams > DBA, 40%OCP > Lifetouch, Inc. > [EMAIL PROTECTED] < mailto:dwilliams@;lifetouch.com > <mailto:dwilliams@;lifetouch.com> > > > -Original Message- > Sent: Wednesday, October 23, 2002 4:39 PM > To: Multiple recipients of list ORACLE-L > > > > Has anyone implemented basic DB monitoring using snmp MIB information > rather > > than running queries against the db? > > I am looking into this and have no clue or available docs on how to do this > (esp on AIX). If someone can point me to the right direction, I would > really > > appreciate that. > > TIA > Raj > __ > Rajendra Jamadagni MIS, ESPN Inc. > Rajendra dot Jamadagni at ESPN dot com > Any opinion expressed here is personal and doesn't reflect that of ESPN > Inc. > > > QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Siebel + Oracle on Sun ?
Hi all Just a friendly enquiry Who else is running Siebel + Oracle on Sun, Would you mind sharing your system configuration. What spec hardware, HD layout. Table distribution I will start: Oracle 8.1.6.3 EE (32 bit) + Solaris 2.6 fully patched. E10K Domain #1, OLTP Siebel DB Domain 8 CPU (400Mhz, Sparc2) 8 GB Ram 2 System boards, 4 GB + 4 CPU each Each connected via Fibre Channel to a A5200 with 11 x 18GB @ 15K RPM disks 11 disk striped and then mirror to other A5200 connected to 2nd System Board. Each A5200 second Fibre link to sister System board so that both system boards got connectivity to both A5200. Tablespace: 3 Small Data64k 2.5 mb, 5 mb 3 Medium Data 10 mb, 25mb, 50 mb 3 Large Data100mb, 100mb, 100mb 3 Small Indexes as above 3 Mediam Indexes 3 Large Indexes Tools Temp Rollback System Users All LMT except System and Rollback Current foot print of DB: 110 GB Site#1 UK (1st E10K): Users 200+ Site#2 USA (2n E10K): User 800+ Domains #2 and #3 for other db's George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: LOCALLY MANAGED TABLESPACE
Title: RE: LOCALLY MANAGED TABLESPACE Or am I simply lucky to not have that level of bureaucracy - You are lucky We are busy going into production for a big project, during the rollout and data take on the managers wanted to know all these things, it comes down to them not just accepting it when you tell them how long something takes. Then you have the manager that managed Oracle some time ago that remember DMT and have worked with LMT so converting them is difficult. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Gary Weber [mailto:[EMAIL PROTECTED]] Sent: 25 October 2002 15:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: LOCALLY MANAGED TABLESPACE Pardon the ignorance, I'm simply trying to understand... What is meant by "management" in this context? I'm can't imagine a circumstance under which ANY business manager would have a say on what goes on in the black box called Oracle. Downtime? Cost of hardware/software? Vendor selection? I can see the input on those issues. But, all the way down to extent management?? Or am I simply lucky to not have that level of bureaucracy? Gary Weber Senior DBA Charles Jones, LLC||Superior Information Services, LLC -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Leonard, George Sent: Friday, October 25, 2002 10:29 AM To: Multiple recipients of list ORACLE-L Subject: RE: LOCALLY MANAGED TABLESPACE Same here Getting management to first understand the extent issue on Dictionary managed was a interesting exercise. Now trying to break that understanding down when wanting to use LMT is like double the work, painful. Difficult thing trying to educate them enough to understand something but not leaving at the same time halve way where you start getting these interesting architecture decisions or ideas. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: 25 October 2002 13:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: LOCALLY MANAGED TABLESPACE The only issue we faced was convincing the management that in LMT having 150 extents is not really a problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, October 24, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Hi I am thinking to change our few dictinary manages tablespace to locally managed tablespace.Can any one experienced any issues with locally managed tablespace? Do any one experience what gain after changing to locally managed tablespace? Thx -Seema
RE: LOCALLY MANAGED TABLESPACE
Title: RE: LOCALLY MANAGED TABLESPACE Same here Getting management to first understand the extent issue on Dictionary managed was a interesting exercise. Now trying to break that understanding down when wanting to use LMT is like double the work, painful. Difficult thing trying to educate them enough to understand something but not leaving at the same time halve way where you start getting these interesting architecture decisions or ideas. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: 25 October 2002 13:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: LOCALLY MANAGED TABLESPACE The only issue we faced was convincing the management that in LMT having 150 extents is not really a problem. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, October 24, 2002 1:49 PM To: Multiple recipients of list ORACLE-L Hi I am thinking to change our few dictinary manages tablespace to locally managed tablespace.Can any one experienced any issues with locally managed tablespace? Do any one experience what gain after changing to locally managed tablespace? Thx -Seema
RE: Rollback segment space usage
Hi Kevin, Fantastic, thx, just what I was looking for, will event tell the oracle support person that could not even assist. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- Sent: 23 October 2002 17:41 PM To: Multiple recipients of list ORACLE-L SELECT r.NAME, -- rbs name s.sid, s.serial#, s.username, s.machine, t.status, t.cr_get, -- consistent gets t.phy_io, -- physical IO t.used_ublk, -- Undo blocks used t.noundo, -- Is a noundo transaction SUBSTR (s.program, 1, 78) "COMMAND", s.username "DB User", t.start_time, s.sql_address "Address", s.sql_hash_value "Sql Hash" FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r WHERE t.addr = s.taddr AND t.xidusn = r.usn ORDER BY t.start_time Try this. It shows the undo blocks used in the rollback segment. Plus some other variables for query usage. -Original Message- Sent: Wednesday, October 23, 2002 12:02 PM To: Multiple recipients of list ORACLE-L Hi all, Trying here now since have tried to find it online and build the query myself. I need to figure out how much space a user/session is using of a rollback segment. I got the queries to show which segment the user is attached to but need to know how much is being used (something like a fuel gauge). Any takers. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Rollback segment space usage
Hi all, Trying here now since have tried to find it online and build the query myself. I need to figure out how much space a user/session is using of a rollback segment. I got the queries to show which segment the user is attached to but need to know how much is being used (something like a fuel gauge). Any takers. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: login trigger
Title: ROWID question? Problem solved You can get the sid from v$mystat, having this we can then query v$session for the rest. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Leonard, George Sent: 22 October 2002 18:44 PM To: Multiple recipients of list ORACLE-L Subject: login trigger Hi all I need to record the time, sun process id and oracle proc id when a user connects. This needs to be done via a trigger, does someone have something like this handy, I am battling trying to find out in a trigger what the user's information is. thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
login trigger
Title: ROWID question? Hi all I need to record the time, sun process id and oracle proc id when a user connects. This needs to be done via a trigger, does someone have something like this handy, I am battling trying to find out in a trigger what the user's information is. thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
FW: Siebel NOTE number for approving CBO for EIM
Title: Message Hi guys. That Siebel note about CBO that was asked for. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -Original Message- From: Charles Hirst [mailto:[EMAIL PROTECTED]] Sent: 08 October 2002 05:26 AM To: Leonard, George Subject: RE: Siebel NOTE number for approving CBO for EIM George The only 'NOTE' I have regarding this is the following SR from SupportWeb. This is for Siebel 6.3 running against Oracle and shows Support suggesting to the client that the use of CBO is acceptable, providing statistics are kept up to date. Why are you interested in this? I thought we had now loaded our bulk data and only had deltas to do, or are you now looking at Newark data? If you want to go down this route you may want to open your own SR to Support, referencing the one below and asking for clarification that the use of CBO is OK. The only thing Siebel normally has against CBO is that it can perform poorly compared to RBO, given that the SQL Siebel generates is tuned for the use of the RBO. However if you test both approaches and find that CBO is faster for you I cannot see why it will cause you any problems, it is simply a performance change, not a functional one. Regards Chuck Service Request #: 38-240228854 Area: EIM S2000 Product Release: V6 (Siebel 2000-Enterprise) Version: 6.3 [10169] Database: Oracle Database Version: 8.1.6 Status: Resolved GMT Date Opened: 02/12/2001 22:06:29 Abstract: EIM Performance on EIM_COMM Messages: 1 2 3 Description: We are doing mass imports to the S_COMMUNICATION table and have noticed slow EIM performance. I've set the the SQL PROFILE and have determined that one particular query has resulted in 95.4% of the total processing time. I've done the explain plan. It showed 3 non-unique range scans for the update statement. The first index choice seems to be a bad choice, but there doesn't seem to be one that's better. The production is targeted to contain 5MM records loaded into this table on a tight conversion schedule. Any performance robbers must be removed to prevent a lengthy conversion time. Activity Type: Resolution GMT Created: 05/09/2001 05:12:27 Messages: 1 2 3 Comment: For the benefit of other readers, the performance degradation is owning to the fact that the database optimizer picked an inefficient index. Analyzing s_org_ext resolved the problem. The slow query is: UPDATESIEBEL.EIM_COMM IT SETT_COMMUNICA_PRCONI = (SELECT/*+ INDEX(BT S_CONTACT_U3)EIM Index Hint */ MIN(BT.ROW_ID) FROMSIEBEL.S_CONTACT BT WHERE (BT.BU_ID = IT.COMM_CON_BI AND BT.FST_NAME = IT.COMM_CON_FST_NAME AND BT.LAST_NAME = IT.COMM_CON_LAST_NAME AND BT.PRIV_FLG = IT.COMM_CON_PRIV_FLG AND (BT.MID_NAME = IT.COMM_CON_MID_NAME OR (BT.MID_NAME IS NULL AND IT.COMM_CON_MID_NAME IS NULL)) AND BT.PR_DEPT_OU_ID = (SELECTMIN(BT2.ROW_ID) FROM SIEBEL.S_ORG_EXT BT2 WHERE (BT2.BU_ID = IT.COMM_CON_ACC_BI AND (BT2.LOC = IT.COMM_CON_ACC_LOC OR (BT2.LOC IS NULL AND IT.COMM_CON_ACC_LOC IS NULL)) AND BT2.NAME = IT.COMM_CON_ACC_NAME WHERE (IF_ROW_BATCH_NUM = 4036 AND IF_ROW_STAT_NUM = 0 AND T_COMMUNICA__STA = 0) According to the execution plan, s_org_ext_m17 instead of s_org_ext_u2 was picked originally for the subquery on s_org_ext. Although Oracle optimization mode is set to Rule for the application, cost-based optimizer is used to generate the execution plan for the above query because of the existence of the index hint. This is the behavior of the database. The cost-based optimizer relied on statistics to generate an efficient execution plan, while the rule-based optimizer generates plans based only on SQL statements. The fact that there was no statistics available for s_org_ext is the reason why s_org_ext_m17 was selected. In this case, there are actually two ways to have the correct index, s_org_ext_u2, to be picked in the plan. One is to add the parameter "USE INDEX HINTS = FALSE" into the ifb file, which eliminates all the index hints during the EIM job. The other is to gather statistics for s_org_ext. Considering index hints might be useful for other queries, we decided to adopt the latter. Please note that it is necessary to update the statistics periodically
Cost of joins
Hi there Can someone please give me in order of preference/cost the relevant costs for the different joins. IE: This join is cheap, This is very expensive, This is bad and always avoid. George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel: (+27 11) 575 0573 Fax: (+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leonard, George INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
