Re: Reverse Key Index Performance
Larry, |control all other factors. And I will not have the chance to do so. As far |as they are concerned, the production problem is resolved. So, there's no |need to more thoroughly investigate this -- let's move on to other pressing |matters. I'd like to have more details, but it's hard to justify spending |more time on it. Sometimes it's a pity that a problem can be resolved without being understood, but that's the real world. A couple of thoughts (for next time). It would be useful to see the execution plans (particularly to see the plan dumped in the trace files just in case the theoretical plan was not the same as the actual plan). Also the full EXPLAIN PLAN output to see if the estimated index access costs on the subquery varied. One thought that could explain the discrepancy, which would be controlled by the type of query and the size of the table. If Oracle optiimises the query by doing the DISTINCT before doing the subquery (and this is nominally a valid optimisation, depending on scale and statistics) then the EMPNOs being checked would be in empno order. With a standard index, you would get 100% buffering of index blocks when doing the subquery - with the reverse key, you COULD get 0% buffering on the leaf blocks. It tallies with the timing - does it tally with the execution path ? Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to 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] Date: 04 May 2002 23:52 |Jonathan, | |Absolutely, the index was being used whether reverse key or not. Surrogate |key defined as RKI using direct inserts and a sequence for populating the |key (no caching on the sequence). Environment, 64 bit 8.1.7.3 Solaris 2.7 |EMC Symmetrix (raw). | |The query was a correlated NOT IN generated by Oracle's replication process |for a primary key fast refresh, just like the following except with real |table names instead of EMP: | |SELECT | DISTINCT LOG$.EMPNO |FROM | (SELECT MLOG$.EMPNO | FROM SCOTT.MLOG$_EMP MLOG$ | WHERE SNAPTIME$$ :1 AND | (DMLTYPE$$ != 'I')) LOG$ |WHERE (LOG$.EMPNO) NOT IN (SELECT MAS_TAB$.EMPNO | FROM EMP MAS_TAB$ | WHERE LOG$.EMPNO = MAS_TAB$.EMPNO) | |So, for every row in the MLOG$ table, a unique index lookup would be |performed on the PK of the table being replicated, EMP_PK in the example |above. And this was verified by tracing the session and examining the plan. |And in my testing of just the query, I would also verify the plan. In the |real world case, MLOG$ will vary between 500,000 and 5 million rows a day, |just depends on the loads done that day. The table on which the snapshot is |created is around 250 - 275 million rows, I'm thinking 30-40 gig total size |(I'm not at work, can't verify) with the reverse key PK a few gig. | |So, when doing a 10046 trace with waits, saw big time waits on db file |sequential reads. Ok, so possible I/O contention, maybe a hot disk, |saturated switch, whatever. But, they don't have the tools to dig into the |black box called EMC to see if we had hot disks. And the SA's don't have |anything (they are working on it) that map things out. With the striping |that was done, who knows what else might reside on those same disks that |could be causing contention. But from a fiber and switch standpoint, they |have never seen any saturation issues with everything working well below |peak capacity. | |But, I did note that of all the tables being replicated, and many pushing 1 |to 5 million rows a day, sometimes much more, the only two that were |experiencing performance issues in the past were those with reverse key |PK's. So, decided to test RKI's against regular B-Trees. I created a copy of |the 250 million row table, and created the snapshot log. And it's hard to |say exactly how it ends up getting laid out on the disks -- working with the |SA's and production DBA's on that. I then created the reverse key index. I |generated 1 million inserts and 1 million updates, giving me 2 million rows |in my personal MLOG$ table. | |Ran the query, it ran for a while (killed it after 1.5 hours). Once again |seeing severe waits on db file sequential reads. Dropped the RKI and |created, using the same TS, as a B-TREE. Query finished in 6 minutes. And |this is what I was talking about earlier and having an un-controlled |environment. Just because I create the RKI, and then the b-tree, in the same |TS, the way the data actually got placed on the physical disks could vary |greatly and at this time they don't have the tools to investigate it. So I |repeated the process, going back to a RKI, ran the query, killed it after |1.5 hours, once again seeing waits on sequential reads. Dropped the RKI and
Re: Response time analysis and TKPROF
Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg I do think so. My point was that the SQL statement level is the wrong granularity. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Response time analysis and TKPROF
Well , Depending on what you want: 1) The only way to see 'response times' on SQL statements is to use 10046, but that adds overhead. There are third party tools out there that will do for you without the 10046 trace. 2) v$sesstat will show the service component (cpu) on the session level, not on the SQL statement level. 3) v$session_wait is close to useless ;-) It will tell you what a session is waiting on right now or what the last wait of a session was. That is not enough. You need v$session_event Again that is on the session level and not on the SQL statement level. See my comments at 1) about that. If you response times on the SQL statement level, you need to use 10046 or a third party tool. On session level use v$sesstat and v$session_event. On instance level use v$sysstat and v$system_event. Read more on how to do that in the YAPP paper. Anjo. Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: I need to change the instance name
Actualy, I am confused, because this is my production environment and I need to get sucess in this operation. It is necessary, and i would like to keep the old name of instance, because i will change the BD version. I think the best way is : 1.) backup the software and datafiles 2.) Export all the database 3.) Deinstanll the old software 4.) Install the new version 5.) Create the instance with the old name 6.) Import the database but it will take a while. I am looking for a fast way , Understand ? Thanks Regards Eriovaldo - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, May 04, 2002 8:03 PM Its actually the easiest part. However, I don't want to miss a step so you best running off to Metalink. They actually have a note out there on how to do it. Not quite up to date for 9i but close. You need to shutdown your database, change your oracle sid, rename your init, if using an i version change the instance name and if desired the service name. Hum, me thinks that's it. I may be missing something though. I feel like I am missing something. However, the moral of the story is that its quite easy and I have done it a couple of times. You need to make a decision on what to do with your directories structures if you are using the $ORACLE_BASE/admin/sid structure. There is no technical reason to change it except for your own sanity. -Original Message- Andrietta Sent: Saturday, May 04, 2002 12:23 PM To: Multiple recipients of list ORACLE-L Friends : I need to change the instance name. For example it is : DEVELOP and I need to put DEVELOPER. I know how to change the database name , but not the instance name .. Any idea ? Regards Eriovaldo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eriovaldo Andrietta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eriovaldo Andrietta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
where to find Oracle Installation guide for HP-UX ?
Hi ! As weird as it sounds - i could not locate the Oracle Installation guide for HP-UX neither on Metalink nor on Google. The only one i was able to find was the Quick start guide , which is not a replacement for the full one. Could u please refer me to a URL where i can get one ? I'm especially interested in a pre-/post-installation steps needed to be taken as root. Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Response time analysis and TKPROF
Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, Stephane Faroult wrote: Greg Moore wrote: But this is all on the server side, so in Oracle .. to get what I apparently want it would be most accurate to use v$sesstat and v$session_wait ? - Greg I do think so. My point was that the SQL statement level is the wrong granularity. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Datawarehousing help
Hello Dennis SAS has progressed a little in the last years and now offer a complete DW solution, including ETL tools. You can use their tools also to populate and query oracle. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, May 04, 2002 2:48 AM Rachel - I always find it helpful to understand something if I know the origins. I worked with SAS several years ago. At that time it was a statistical analysis package. A scientist or engineer could load a set of test data into it and perform various arithmetic and statistical analyses. Today most of that can be done with Oracle or MS Excel. My point is that I would expect it to be heavily biased toward mathematical capabilities. Like Data Mining, which is all statistics. Learn what that term means. To learn Data Warehousing, I would encourage you to just do some Googling and find good tutorials. An excellent newslist is dwlist. Instructions: For help with list commands, send a message to mailto:[EMAIL PROTECTED] with the word help in the body of the message. The magazine http://www.intelligententerprise.com/ has some excellent information. I would search for Ralph Kimball. He is one of the leading figures in the DW arena. Look for some of his earliest columns on the magazine site. He also answers questions on dwlist from time to time. The main change you need yourself is to forget normalization. DBAs that can't get past that point don't last long in the DW field. In the early days the DW people would patiently explain the reasons to a DBA, but today there are enough DBAs that have made the leap that a hard-headed normalization bigot just isn't tolerated. It is much easier to just ask for a replacement DBA. The reason normalization isn't adhered to in DW is that users will be creating their own queries and they can't understand 10-table joins with outer joins, etc. A DW is usually loaded and then queried. Our DW is loaded each weekend and then queried all week. So a DW is deliberately denormalized and contains redundant data for ease of use. OLTP databases have no concept of time. A DW is all about time. To reconstruct what the situation is at various points of time, the DW has loads of historical data. For example, marketing people need to be able to reconstruct the amount of business they did with a customer over a period of time last year and compare it with the same period this year. So between denormalization and tons of detailed historical data, DWs are normally BIG! Fortunately they are usually read-only. For Oracle, you want Enterprise Edition with the partitioning option. And study Oracle Materialized Views. In schema, a DW is usually a central fact table and 4-6 dimension tables. Less than 4 dimensions and you don't need a DW. More than 6 and marketing people can't understand the model. Normally the fact table is much larger than the others, but not always. One of Wal-Mart's dimension tables is each person in the U.S. Just size each of those tables, and you've got your size. Growth is easy to predict. Ralph Kimball warns that often people will get the grain wrong. They will size it for data summarized at the weekly level, then after it is built they will realize that isn't going to cut it and need a daily level. You must start almost from scratch and get 7 times the disk capacity. That is the fun side of being a DW DBA. Your cynical instincts will still serve you well, just get them away from normalization and worry about getting the grain right. Okay, I've rambled along here too long. Hope that gets you off on the right foot. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Okay, my background is OLTP, but we are looking at a data warehousing project here any and all help appreciated! Specifically: 1) does anyone have any experience with a product called SAS Datawarehousing Administrator (or SAS)? 2) how do I go about doing rough estimates of sizing needs, assuming I will get rough numbers of information being collected, growth rates, length of history to keep, etc. help? Rachel -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS
Re: ERD generation tool - Active SCM
Well , just to keep things jumping. Last week I deviated from our rule and gave a responsible user that needed truncate on tables the password for the owner of the schema. Guess what? Today he comes to me to recreate 2 tables that he dropped. Go figure. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 5:53 PM Yechiel, Yes, I have been there, done that, over and over... But then, there is a Toyota Corolla solution and maybe a Ferrari Testarosa solution. If we can control Dom Phoc without tieing his hands behind the back, wouldn't that would be the best: white paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Keith Date: Thu, 02 May 2002 11:48:38 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Well Keith Our solution to the Doom Phoc (and their siblings) is: Do not grant they rights to do any DDL either in test nor in prod. The dab stuff does all the DDL work. Sure it is an added chore, but after tracking down, a few times, tables that were dropped inadvertently by users (their tool did it by itself) we now use the following policy: Every application has two user id's: Owner, with password known only to the DBA group. User with rights for select, insert, update, delete ONLY. It works. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 7:54 PM Lisa, There is only so much you can control via a model, since it remains a process away from the DB, and cannot be enforced via privileges, etc. So, we are always in the hands of Dom Phoc (and their siblings), who can do stuff even in the production database with SQLPLus/TOAD/... Under this schenario, do you sleep well at night? So, we said lets work with our Dom Phoc's. On production databases, we will STRIP them off of the Oracle database passwords. No password, no change. ENFORCED! Now, I can sleep well at night. How? Not via models. Via a solution involving the following, and it seems to be working for us well: ActiveDesigner/ActiveChangeManager/ActiveCompare/A+ White Paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Take charge of the Dom Phocs in your org! Keith To: '[EMAIL PROTECTED]' [EMAIL PROTECTED], '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Wed, 1 May 2002 16:06:00 -0500 Well, for one thing, if your developer, Dom Phoc, starts changing crap in your database (as has happened to me in the past) a compare to the dev model would be great because my development changes would be in the model, not in the test or production databases. In that specific case I had to TRUST him (what? trust him after what he just did?) to change everything back, or restore from a backup, which would have been very time consuming. I was one large ball of raging hormones that day and I took it all out on him. We don't work on the same projects anymore. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Keith Peterson [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: ERD generation tool - Active Comparisons Am I speaking to the wind For Compares, why would you compare the MODEL with the DATABASE...like going from US to London via Tokyo... ... and you get to pay more, like... you pay not for distance, but for time in the air... If a tool takes longer to do something, makes more mistakes, is bumpy and complex... you get to pay more. For compares, someone tell me what beats ActiveCompare: http://www.iraje.com/compare-diff.htm http://www.iraje.com/ActiveCompare_viewlet.html ...and I will switch my tool. Keith __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Keith Peterson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). --
Re: IN() question
Hello Lisa
I think that using :
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND (pay_METHOD NOT IN ('C','P')
5 or pay_method is null)
will give you the correct result.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, May 04, 2002 1:43 AM
Slap me if this is a dumb question.
Here's my pay methods
SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*)
2 FROM LEISURE_PLAN_MASTER_TEMP
3 WHERE MEMBERSHIP_CLASS = 'D'
4 GROUP BY PAY_METHOD;
ASCII(PAY_METHOD) P COUNT(*)
- - --
67 C 42955
80 P 34373
11786
I expected this statement to return the 11,786 records that have null
values. However, it doesn't:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD NOT IN ('C','P');
COUNT(*)
--
0
But when I do this, I get the answer I expect.
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND PAY_METHOD IS NULL;
COUNT(*)
--
11786
This isn't exactly correct. There may be other values in this field, and
if
they show up I need to include them, not just records where this field is
null. What am I missing? Is it because the value is NULL that Oracle
excludes it from the IN() statement, because of the classic definition of
NULL (can't be defined, therefore can't be sure it's not a C or a P)?
This is easy enough to fix, I'll change my data load to populate the null
values with my own code. But still? Have I got the WHY correct?
Thanks for any light someone can shed on this stupid question.
Lisa Koivu
Oracle Database Monkey Mama
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yechiel Adar
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: ERD generation tool - Active SCM
Sorry Yeichel... I had to laugh! I've been on both sides of this situation. I hope you showed him where his a$$ is for the next time he can't find it with his own two hands. :) and developers wonder why they have been termed duh-veloper and Dom Phoc. That's why every time someone gets upset with me and wants the schema password, I say something to the effect of It's my job to recover it if something goes wrong. I don't have 24 hours to complete the recovery, and neither do you. No Freaking Way, Mr. CIO. (this has actually happened, it turned into quite an unpleasant discussion). Fire me if you want to. It's a lovely spring morning in Florida and there is a mockingbird serenading me.. Wow. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Yechiel Adar [SMTP:[EMAIL PROTECTED]] Sent: Sunday, May 05, 2002 7:48 AM To: Multiple recipients of list ORACLE-L Subject: Re: ERD generation tool - Active SCM Well , just to keep things jumping. Last week I deviated from our rule and gave a responsible user that needed truncate on tables the password for the owner of the schema. Guess what? Today he comes to me to recreate 2 tables that he dropped. Go figure. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 03, 2002 5:53 PM Yechiel, Yes, I have been there, done that, over and over... But then, there is a Toyota Corolla solution and maybe a Ferrari Testarosa solution. If we can control Dom Phoc without tieing his hands behind the back, wouldn't that would be the best: white paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Keith Date: Thu, 02 May 2002 11:48:38 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Reply-to: [EMAIL PROTECTED] Organization: Fat City Network Services, San Diego, California Well Keith Our solution to the Doom Phoc (and their siblings) is: Do not grant they rights to do any DDL either in test nor in prod. The dab stuff does all the DDL work. Sure it is an added chore, but after tracking down, a few times, tables that were dropped inadvertently by users (their tool did it by itself) we now use the following policy: Every application has two user id's: Owner, with password known only to the DBA group. User with rights for select, insert, update, delete ONLY. It works. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 7:54 PM Lisa, There is only so much you can control via a model, since it remains a process away from the DB, and cannot be enforced via privileges, etc. So, we are always in the hands of Dom Phoc (and their siblings), who can do stuff even in the production database with SQLPLus/TOAD/... Under this schenario, do you sleep well at night? So, we said lets work with our Dom Phoc's. On production databases, we will STRIP them off of the Oracle database passwords. No password, no change. ENFORCED! Now, I can sleep well at night. How? Not via models. Via a solution involving the following, and it seems to be working for us well: ActiveDesigner/ActiveChangeManager/ActiveCompare/A+ White Paper: http://www.iraje.com/docs/ActiveSecureDesigner.htm Take charge of the Dom Phocs in your org! Keith To: '[EMAIL PROTECTED]' [EMAIL PROTECTED], '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Wed, 1 May 2002 16:06:00 -0500 Well, for one thing, if your developer, Dom Phoc, starts changing crap in your database (as has happened to me in the past) a compare to the dev model would be great because my development changes would be in the model, not in the test or production databases. In that specific case I had to TRUST him (what? trust him after what he just did?) to change everything back, or restore from a backup, which would have been very time consuming. I was one large ball of raging hormones that day and I took it all out on him. We don't work on the same projects anymore. Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Keith Peterson [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: ERD generation tool - Active Comparisons Am I speaking to the wind For Compares, why would you compare the MODEL with the DATABASE...like going from US to London via Tokyo... ... and you get to pay more, like... you pay
RE: IN() question
Hi Yeichel,
It will, thanks for your reply. I ended up taking the route of cleaning up
the data. (I'm so anal)
Have a great day.
Lisa
-Original Message-
From: Yechiel Adar [SMTP:[EMAIL PROTECTED]]
Sent: Sunday, May 05, 2002 8:43 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: IN() question
Hello Lisa
I think that using :
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND (pay_METHOD NOT IN ('C','P')
5 or pay_method is null)
will give you the correct result.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, May 04, 2002 1:43 AM
Slap me if this is a dumb question.
Here's my pay methods
SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*)
2 FROM LEISURE_PLAN_MASTER_TEMP
3 WHERE MEMBERSHIP_CLASS = 'D'
4 GROUP BY PAY_METHOD;
ASCII(PAY_METHOD) P COUNT(*)
- - --
67 C 42955
80 P 34373
11786
I expected this statement to return the 11,786 records that have null
values. However, it doesn't:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD NOT IN ('C','P');
COUNT(*)
--
0
But when I do this, I get the answer I expect.
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND PAY_METHOD IS NULL;
COUNT(*)
--
11786
This isn't exactly correct. There may be other values in this field,
and
if
they show up I need to include them, not just records where this field
is
null. What am I missing? Is it because the value is NULL that Oracle
excludes it from the IN() statement, because of the classic definition
of
NULL (can't be defined, therefore can't be sure it's not a C or a P)?
This is easy enough to fix, I'll change my data load to populate the
null
values with my own code. But still? Have I got the WHY correct?
Thanks for any light someone can shed on this stupid question.
Lisa Koivu
Oracle Database Monkey Mama
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yechiel Adar
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: where to find Oracle Installation guide for HP-UX ?
go to http://technet.oracle.com/docs/products/oracle9i/content.html and scroll down on the page to HPUX... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, May 05, 2002 5:03 AM Hi ! As weird as it sounds - i could not locate the Oracle Installation guide for HP-UX neither on Metalink nor on Google. The only one i was able to find was the Quick start guide , which is not a replacement for the full one. Could u please refer me to a URL where i can get one ? I'm especially interested in a pre-/post-installation steps needed to be taken as root. Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
DB Version upgrade with CreateDB/Import Risky???
Hello fellow Oracle DBAs, I really need your opinion on this matter: I routinely do upgrades of Oracle software and databases on all types of systems: Compaq Unix, Netware, Linux, etc. (as do we all from time to time). On one Windows/NT 4 system where I am presently doing an upgrade from a lower version of Oracle 8 to 8.1.7, I am dealing with a particular System Administrator for the NT box who knows a little about Oracle, but that is obviously laboring under some misconceptions. He strongly believes that the ONLY way one should upgrade a database (once the software has been upgraded) is to do a migration (presumably as detailed in the Oracle Migration manual) and he is URGING me to do it this way. On small databases (i.e. less than 10G) where downtime is not an issue, the way I have always done it and the way I intend to do it this case is: (1) Take a full database export under the old version (2) Install the Version 8.1.7 software (3) Recreate from scratch the database under the new software (4) Do a full database import to the new database. For our systems, I as the DBA believe and have found this to be a fully reliable, quick, and clean method and the preferable way to do it, rather than go through the migration procedure. He on the other hand believes that NOT doing it via the migration route is very risky. Without going into a long spiel with him about what an upgrade IS as far as the database itself is concerned (i.e. the data dictionary objects being brought up to the new version), and why the way I intend to do it with a full import is perfectly acceptable to accomplish this, I'd like to just offer this person the opinions of some of you out there in ORACLE-L List Land where the Oracle DBA expertise is highly respected for its stature, I.E. so he doesn't have to believe me. In other words, I don't want to try to convince him against his will..I'd rather have impartial competent experts give him unbiased testimony. Thus I humbly solicit your opinion on this matter. I believe that my above procedure is a simple and fully reliable way of bringing the database up to the new software level..or perhaps it is ***I*** that am laboring under the misconception??? May I please have your take on this. Much appreciate, JDamiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: where to find Oracle Installation guide for HP-UX ?
http://docs.oracle.com/ I will let you do your own searching to find the proper one:-) -Original Message- Sent: Sunday, May 05, 2002 4:03 AM To: Multiple recipients of list ORACLE-L Hi ! As weird as it sounds - i could not locate the Oracle Installation guide for HP-UX neither on Metalink nor on Google. The only one i was able to find was the Quick start guide , which is not a replacement for the full one. Could u please refer me to a URL where i can get one ? I'm especially interested in a pre-/post-installation steps needed to be taken as root. Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Version upgrade with CreateDB/Import Risky???
Um, why not just install the software and then run that silly little upgrade script they give you. Oh yeah, and change the oracle_home parameter in the listener and change the compatible parameter in the init. Not that your way is wrong but seems to be a tad more then what you need to do. Unless of course you need to rearrange stuff for some reason. -Original Message- Damiano Sent: Sunday, May 05, 2002 9:23 AM To: Multiple recipients of list ORACLE-L Hello fellow Oracle DBAs, I really need your opinion on this matter: I routinely do upgrades of Oracle software and databases on all types of systems: Compaq Unix, Netware, Linux, etc. (as do we all from time to time). On one Windows/NT 4 system where I am presently doing an upgrade from a lower version of Oracle 8 to 8.1.7, I am dealing with a particular System Administrator for the NT box who knows a little about Oracle, but that is obviously laboring under some misconceptions. He strongly believes that the ONLY way one should upgrade a database (once the software has been upgraded) is to do a migration (presumably as detailed in the Oracle Migration manual) and he is URGING me to do it this way. On small databases (i.e. less than 10G) where downtime is not an issue, the way I have always done it and the way I intend to do it this case is: (1) Take a full database export under the old version (2) Install the Version 8.1.7 software (3) Recreate from scratch the database under the new software (4) Do a full database import to the new database. For our systems, I as the DBA believe and have found this to be a fully reliable, quick, and clean method and the preferable way to do it, rather than go through the migration procedure. He on the other hand believes that NOT doing it via the migration route is very risky. Without going into a long spiel with him about what an upgrade IS as far as the database itself is concerned (i.e. the data dictionary objects being brought up to the new version), and why the way I intend to do it with a full import is perfectly acceptable to accomplish this, I'd like to just offer this person the opinions of some of you out there in ORACLE-L List Land where the Oracle DBA expertise is highly respected for its stature, I.E. so he doesn't have to believe me. In other words, I don't want to try to convince him against his will..I'd rather have impartial competent experts give him unbiased testimony. Thus I humbly solicit your opinion on this matter. I believe that my above procedure is a simple and fully reliable way of bringing the database up to the new software level..or perhaps it is ***I*** that am laboring under the misconception??? May I please have your take on this. Much appreciate, JDamiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DB Version upgrade with CreateDB/Import Risky???
Hello James In the last 2 years I did about 5-6 migrations from 7.3.4 to 8.1.6.0 and 10-12 from 8.1.6.0 to 8.1.6.3.4. All done on NT with upgrades. No scratch and rebuild. No problems at all. Sorry to disappoint you. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, May 05, 2002 6:23 PM Hello fellow Oracle DBAs, I really need your opinion on this matter: I routinely do upgrades of Oracle software and databases on all types of systems: Compaq Unix, Netware, Linux, etc. (as do we all from time to time). On one Windows/NT 4 system where I am presently doing an upgrade from a lower version of Oracle 8 to 8.1.7, I am dealing with a particular System Administrator for the NT box who knows a little about Oracle, but that is obviously laboring under some misconceptions. He strongly believes that the ONLY way one should upgrade a database (once the software has been upgraded) is to do a migration (presumably as detailed in the Oracle Migration manual) and he is URGING me to do it this way. On small databases (i.e. less than 10G) where downtime is not an issue, the way I have always done it and the way I intend to do it this case is: (1) Take a full database export under the old version (2) Install the Version 8.1.7 software (3) Recreate from scratch the database under the new software (4) Do a full database import to the new database. For our systems, I as the DBA believe and have found this to be a fully reliable, quick, and clean method and the preferable way to do it, rather than go through the migration procedure. He on the other hand believes that NOT doing it via the migration route is very risky. Without going into a long spiel with him about what an upgrade IS as far as the database itself is concerned (i.e. the data dictionary objects being brought up to the new version), and why the way I intend to do it with a full import is perfectly acceptable to accomplish this, I'd like to just offer this person the opinions of some of you out there in ORACLE-L List Land where the Oracle DBA expertise is highly respected for its stature, I.E. so he doesn't have to believe me. In other words, I don't want to try to convince him against his will..I'd rather have impartial competent experts give him unbiased testimony. Thus I humbly solicit your opinion on this matter. I believe that my above procedure is a simple and fully reliable way of bringing the database up to the new software level..or perhaps it is ***I*** that am laboring under the misconception??? May I please have your take on this. Much appreciate, JDamiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DB Version upgrade with CreateDB/Import Risky???
James, Your plan is perfectly acceptable. In fact, if time allows, it may be preferable. If there are structural issues in the database (i.e. migrated rows, tables in index tablespace (and vice versa)), this is one method of killing two birds with one stone. It does require that there is sufficient space on the system to store the export file(s). I've also done migrations using the Oracle supplied processes and utilities...no problems there. If the database is structurally sound, it may be 'easier' to use the migration method. One advantage is that no additional space is required to hold an export file. All in all, there are valid reasons to use both methods. Export/Create/Import offers a chance to 'repair' structural issues. In place migration may require less time. Daniel W. Fink James Damiano wrote: Hello fellow Oracle DBAs, I really need your opinion on this matter: I routinely do upgrades of Oracle software and databases on all types of systems: Compaq Unix, Netware, Linux, etc. (as do we all from time to time). On one Windows/NT 4 system where I am presently doing an upgrade from a lower version of Oracle 8 to 8.1.7, I am dealing with a particular System Administrator for the NT box who knows a little about Oracle, but that is obviously laboring under some misconceptions. He strongly believes that the ONLY way one should upgrade a database (once the software has been upgraded) is to do a migration (presumably as detailed in the Oracle Migration manual) and he is URGING me to do it this way. On small databases (i.e. less than 10G) where downtime is not an issue, the way I have always done it and the way I intend to do it this case is: (1) Take a full database export under the old version (2) Install the Version 8.1.7 software (3) Recreate from scratch the database under the new software (4) Do a full database import to the new database. For our systems, I as the DBA believe and have found this to be a fully reliable, quick, and clean method and the preferable way to do it, rather than go through the migration procedure. He on the other hand believes that NOT doing it via the migration route is very risky. Without going into a long spiel with him about what an upgrade IS as far as the database itself is concerned (i.e. the data dictionary objects being brought up to the new version), and why the way I intend to do it with a full import is perfectly acceptable to accomplish this, I'd like to just offer this person the opinions of some of you out there in ORACLE-L List Land where the Oracle DBA expertise is highly respected for its stature, I.E. so he doesn't have to believe me. In other words, I don't want to try to convince him against his will..I'd rather have impartial competent experts give him unbiased testimony. Thus I humbly solicit your opinion on this matter. I believe that my above procedure is a simple and fully reliable way of bringing the database up to the new software level..or perhaps it is ***I*** that am laboring under the misconception??? May I please have your take on this. Much appreciate, JDamiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Response time analysis and TKPROF
Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I meet more and more (business) processes in which, without being top-notch, SQL statements do not look terribly bad. Rewrite everything, and it roars. I am not sure that digging deep in this case inside trace files is the most effective. Having a talk round the coffee-machine with end-users also helps. And you always have that terrible SQL statement which runs at 2 am and about which nobody cares as long as the maintenance window is large enough. What I question is the need to abuse queue theory when, let's put it clearly, the problem is awful code written by beginners under the leadership of people too often unable to reread what has been written by their 'subordinates'. And I have strong doubts about how easily you will 'sell' it to a management who better understands that a faster processor (or an additional processor) may make things run faster - even if we all know that it is far from being always true. How much simpler for a 'decision taker' than purchasing days of consulting for a result which may, and usually will, be much more efficient, but for which quantifying (even wrongly) results is much more delicate. End of rant ;-). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DB Version upgrade with CreateDB/Import Risky???
James - I think that your system administrator is too much of a Microsoft head. I can admit that to someone that knows nothing about Oracle, the official method might sound safer. I have done the upgrades both ways for many years, and I have never had a problem with the CreateDB/Import route. I have often had problems with the migrate method. When I went from Oracle 8 to 8i, finally had to give up on getting the migrate method to work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, May 05, 2002 11:23 AM To: Multiple recipients of list ORACLE-L Hello fellow Oracle DBAs, I really need your opinion on this matter: I routinely do upgrades of Oracle software and databases on all types of systems: Compaq Unix, Netware, Linux, etc. (as do we all from time to time). On one Windows/NT 4 system where I am presently doing an upgrade from a lower version of Oracle 8 to 8.1.7, I am dealing with a particular System Administrator for the NT box who knows a little about Oracle, but that is obviously laboring under some misconceptions. He strongly believes that the ONLY way one should upgrade a database (once the software has been upgraded) is to do a migration (presumably as detailed in the Oracle Migration manual) and he is URGING me to do it this way. On small databases (i.e. less than 10G) where downtime is not an issue, the way I have always done it and the way I intend to do it this case is: (1) Take a full database export under the old version (2) Install the Version 8.1.7 software (3) Recreate from scratch the database under the new software (4) Do a full database import to the new database. For our systems, I as the DBA believe and have found this to be a fully reliable, quick, and clean method and the preferable way to do it, rather than go through the migration procedure. He on the other hand believes that NOT doing it via the migration route is very risky. Without going into a long spiel with him about what an upgrade IS as far as the database itself is concerned (i.e. the data dictionary objects being brought up to the new version), and why the way I intend to do it with a full import is perfectly acceptable to accomplish this, I'd like to just offer this person the opinions of some of you out there in ORACLE-L List Land where the Oracle DBA expertise is highly respected for its stature, I.E. so he doesn't have to believe me. In other words, I don't want to try to convince him against his will..I'd rather have impartial competent experts give him unbiased testimony. Thus I humbly solicit your opinion on this matter. I believe that my above procedure is a simple and fully reliable way of bringing the database up to the new software level..or perhaps it is ***I*** that am laboring under the misconception??? May I please have your take on this. Much appreciate, JDamiano -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Damiano INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Datawarehousing help
Yechiel - I did not mean to imply that SAS had not improved since the '80s. They would be out of business otherwise. And of course every DW vendor is full solution vendor. Just read their brochures if you don't believe me. My point was that if you understand a company's roots, then often a lot of their quirks start to make sense. My point was that SAS has a VERY strong mathematical foundation, which may help set your understandings. The features you have listed Rachel can get off their brochures. Can you provide any more ideas? My guess is that they might be very strongly positioned to perform data mining. Can you confirm that? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, May 05, 2002 6:23 AM To: Multiple recipients of list ORACLE-L Hello Dennis SAS has progressed a little in the last years and now offer a complete DW solution, including ETL tools. You can use their tools also to populate and query oracle. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, May 04, 2002 2:48 AM Rachel - I always find it helpful to understand something if I know the origins. I worked with SAS several years ago. At that time it was a statistical analysis package. A scientist or engineer could load a set of test data into it and perform various arithmetic and statistical analyses. Today most of that can be done with Oracle or MS Excel. My point is that I would expect it to be heavily biased toward mathematical capabilities. Like Data Mining, which is all statistics. Learn what that term means. To learn Data Warehousing, I would encourage you to just do some Googling and find good tutorials. An excellent newslist is dwlist. Instructions: For help with list commands, send a message to mailto:[EMAIL PROTECTED] with the word help in the body of the message. The magazine http://www.intelligententerprise.com/ has some excellent information. I would search for Ralph Kimball. He is one of the leading figures in the DW arena. Look for some of his earliest columns on the magazine site. He also answers questions on dwlist from time to time. The main change you need yourself is to forget normalization. DBAs that can't get past that point don't last long in the DW field. In the early days the DW people would patiently explain the reasons to a DBA, but today there are enough DBAs that have made the leap that a hard-headed normalization bigot just isn't tolerated. It is much easier to just ask for a replacement DBA. The reason normalization isn't adhered to in DW is that users will be creating their own queries and they can't understand 10-table joins with outer joins, etc. A DW is usually loaded and then queried. Our DW is loaded each weekend and then queried all week. So a DW is deliberately denormalized and contains redundant data for ease of use. OLTP databases have no concept of time. A DW is all about time. To reconstruct what the situation is at various points of time, the DW has loads of historical data. For example, marketing people need to be able to reconstruct the amount of business they did with a customer over a period of time last year and compare it with the same period this year. So between denormalization and tons of detailed historical data, DWs are normally BIG! Fortunately they are usually read-only. For Oracle, you want Enterprise Edition with the partitioning option. And study Oracle Materialized Views. In schema, a DW is usually a central fact table and 4-6 dimension tables. Less than 4 dimensions and you don't need a DW. More than 6 and marketing people can't understand the model. Normally the fact table is much larger than the others, but not always. One of Wal-Mart's dimension tables is each person in the U.S. Just size each of those tables, and you've got your size. Growth is easy to predict. Ralph Kimball warns that often people will get the grain wrong. They will size it for data summarized at the weekly level, then after it is built they will realize that isn't going to cut it and need a daily level. You must start almost from scratch and get 7 times the disk capacity. That is the fun side of being a DW DBA. Your cynical instincts will still serve you well, just get them away from normalization and worry about getting the grain right. Okay, I've rambled along here too long. Hope that gets you off on the right foot. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Okay, my background is OLTP, but we are looking at a data warehousing project here any and all help appreciated! Specifically: 1) does anyone have any experience with a product called SAS Datawarehousing Administrator (or SAS)? 2) how do I go about doing rough estimates of sizing needs, assuming I will get rough numbers of information being collected,
Re: Paging problem in SunOS
What are your paging statistics? run 'vmstat 10 10' during a period of poor performace and post the output back to the list. Jared On Friday 03 May 2002 05:38, Pradyut Mitra wrote: Hi,I am experiencing a massive performance problem due tohigh page-in operation In Sun OS 5.6/Oracle 8.1.7.The RAM is 512 M and SGA is around is 50M and no otherprocess is running on that m/c. Any suggestions. Thanks in advance. Pradyut - Do You Yahoo!? Yahoo! Health - your guide to health and wellness Content-Type: text/html; charset=us-ascii; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql is INTERPRETED?
Na, I probably would have got a bonus or something for that. Jared On Thursday 02 May 2002 12:13, Khedr, Waleed wrote: Hope it's not the program that triggered the whole Enron thing :) Regards, Waleed -Original Message- Sent: Thursday, May 02, 2002 2:14 PM To: Multiple recipients of list ORACLE-L It ain't that tough. We're not talking about taking a programming class without any experience, I've done a bit of it before. Learning all the API's, etc.: that would take some time. The language? It isn't that difficult, though I would be hard put to write any at the moment. The job I was going to use Java on was at Enron, and we all know what happened to that. It's been a year since I took the class, and I *much* prefer Perl. It can run circles around Java for most stuff. Jared Alex [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/02/2002 08:23 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql is INTERPRETED? It took you a week to learn it? Then you obviously do not know it. Syntax is one thing design is another. I would love to know what you learned in that week. On Thu, 2 May 2002, Jared Still wrote: Hold on Lisa! Java is not complex. It's a very simple language actually. It took me a week to learn it, though I'm not using it now: I much prefer Perl. Getting a handle on all of the libraries and API's is another story, but Java as a language is pretty simple. Jared On Tuesday 30 April 2002 11:14, Koivu, Lisa wrote: You have a point Chris, but pl/sql is nowhere near as complex as an OO language like java or C++, IMHO. I agree with Tom that pl/sql can be learned fairly easily in comparison to the many other choices out there. However, it takes a bit of database savvy to do it correctly. (Not much tho) I was amazed in my database class in college that the same people failing the simple entity-relationship modeling portion of the class that had aced the Op Systems and networking classes we took. I nearly failed both classes, they were so complex. I was the teacher's pet in the db class because I asked him questions that made him think, and he sometimes couldn't answer. (And I had to wear a skirt - night student, straight from work.) What's easy for who is dependent on the person's strengths. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 1:14 PM To:Multiple recipients of list ORACLE-L Subject: RE: pl/sql is INTERPRETED? IMHO, I don't believe that you can properly learn PL/SQL in a very short period of time, or for that matter, any other language. I attended Steve Feuerstein's presentation at MAOP-AOTC conference, and he tore into many real-life examples of PL/SQL. Supposedly, these were written by developers that knew what they were doing. Granted, if a smart developer sits down and reads Feuerstein's Learning PL/SQL and Best Practices books, then perhaps they will be good. But who the hell has free time? There is no free time on any project or effort that I know of!! I'm struggling with trying to improve my Oracle DBA skills, plus some developers skills so I can speak their language when they blow out OPEN_CURSORS or something. My head is swimming in the stupid technical alphabet soup, XML, XDK, XSQL, XSLT, XPath, SOAP, ASP, ADO, EJB, BC4J, JDBC, SQLJ, PSP, JVM, JSP, J2EE, EAD, RMI, CORBA, IIOP...and don't ask me what all those mean, because I can't keep them straight. But I do keep hearing that XML is going to put me out of a job, so I guess I should learn that...whatever that is. Isn't XML an add-on, or extension, or something to DML??? Now where the heck did I hide that bottle... -Original Message- Sent: Tuesday, April 30, 2002 12:15 PM To: Multiple recipients of list ORACLE-L Lisa, You are right about the debate between PL/SQL Java (or anything else outside of the db). In my mind, the deciding factor (and something that is *never* mentioned) is what programming langauage the organization is satisfied with/settled upon. In my little opinion, *any* programmer can learn PL/SQL in a very short period of time. This means that development and maintenance costs are relatively low. If an IT shop is stronger in Java, then they should probably program in Java, or Cobol, or Ada, or whatever the flavor of the decade happens to be (lets bring
RE: Reverse Key Index Performance
Sometimes it's a pity that a problem can be resolved without being understood, but that's the real world. No kidding -- if some things appear to work, it would help to understand the details to make sure a valid conclusion is being drawn. The test of reverse vs. b-tree was simply performed because of the situation described earlier where the only two issues where on those with RKI's. I had the test going on in the background while focusing on more critical work that needed to be done. I didn't expect the big difference or else I would have tried to be a little more controlled to really pinpoint all possible factors. And so now I will still be wondering what other factors were involved. And it's hard to duplicate on my Win2k box, and not sure that you could draw a correlation anyway due to such massive differences in size, HW, and OS's. A couple of thoughts (for next time). It would be useful to see the execution plans (particularly to see the plan dumped in the trace files just in case the theoretical plan was not the same as the actual plan). The plan in the raw trace file was the same as what explain plan was giving. Also the full EXPLAIN PLAN output to see if the estimated index access costs on the subquery varied. No can do -- was lucky to be able to temporarily borrow the space. I hardly ever pay attention to the calculated cost when dealing with problem queries, but, it would have been a good idea here to note the differences in the calculated costs when using the reverse key vs. b-tree. Had this been my primary focus, I would have taken the time to do a 10053 trace as well just to see inside the CBO's head and how it might calculate things differently between the RKI and the b-tree. Maybe there would have been differences, but the bottom line is the same access path was used either way, so I don't know how much we would gain from seeing the numbers. One thought that could explain the discrepancy, which would be controlled by the type of query and the size of the table. If Oracle optiimises the query by doing the DISTINCT before doing the subquery (and this is nominally a valid optimisation, depending on scale and statistics) then the EMPNOs being checked would be in empno order. Would this show up differently in the plan? I don't guess that I have seen that. Typically I have seen the sort phase for the distinct operation as the last step. Or, are you implying that even if the sort phase shows up last, that internally it could have selected the distinct values before doing the correlation? I could see where that could be a valid optimization -- reduce the number of correlated UK index lookups. On the other hand, if most were unique, and the correlated sub-query eliminated many rows, the cost of sorting could be much less when done *after* the correlation, at the expense of more unique index lookups. And which one benefits the most? With a standard index, you would get 100% buffering of index blocks when doing the subquery - with the reverse key, you COULD get 0% buffering on the leaf blocks. It tallies with the timing - does it tally with the execution path ? And especially in my test case. My update was simply a where rownum 101 -- yeah, I should have done something random. So, with this being a fresh table just inserted into, I know there's a high probability that I was updating physically adjacent rows that would correspond very nicely with the index, minimizing the number of blocks to visit and then benefiting from the buffering. And with the FTS on the MLOGS$ table, and the way I did the updates, and the MLOG$ table being new, I would stand a good chance of reading those updated values in order. So this was like a best case scenario. That's why I then used the MLOG$ table from the real table for additional testing. Those updates would have been random, and not updating 1 million rows residing in the fewest blocks possible, thus being a little bit better test than using my MLOG$ table and it's built in advantage due to the way I did the updates. On a table of this size, and if the updates were really random, though, you could conceivably see cases where the difference in the number of index blocks visited could be nearly the same between the two types of indexes. But things could flush out with the RKI requiring physically reading a block again. So, buffer size could play a role. And I also wonder what type of overhead is needed when reversing the value to do the index lookup. Similar to compressed indexes -- we know there can be a big benefit, but we also hear from some people where, in specific cases, the decompression overhead offset any advantage of a smaller index. Anyway, thanks for throwing some things out there to think about. At some point in time, I would like to dig deeper, but just can't justify it now. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar -
Re: Response time analysis and TKPROF
If someone were to ask me whether a TKPROF report tells you anything about wait events, my first response would be No. Yet what, if anything, can I learn from a quick glance at the totals for CPU time and elapsed time? If... response time = service time + wait time then is it correct to say: * response time is: TKPROF total elapsed time * service time is: TKPROF total CPU time * wait time is: TKPROF total elapsed time - total CPU time If this is true, then from a TKPROF report, I could glance at these two totals and see whether wait events are significantly affecting the SQL statement. Case #1: The two totals are very close. Waits are not affecting this SQL statement. Case #2: Total CPU time is significantly less than total elapsed time. Waits are significantly affecting the statement. Consider running the SQL in a stand alone session, and use before and after snapshots of v$session_event to see what it's waiting on. Are my assumptions true? And as Stephane points out, even if they are true, are they of any practical value? Personally, I think it would be kind of cool to be able to look at a TKPROF report and get a quick read on whether wait events are significantly affecting this particular SQL statement. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Response time analysis and TKPROF
Yes, I think that we are talking about different things: 1) I don't feel that we are abusing the queue theory by borrowing terms like service time and wait time. Actually when I did the YAPP method, it was back in 1996 on a project that involved Tuxedo and the programmers on the project wanted more processes. I had to convince them to do it with less and I could do that with the service and wait time model (calculating the response time in the Oracle server for sessions). And it really works well. 2) The problem with tuning by hit ratios and tuning by counting (like number of buffer gets and number of physical I/O from v$sqlarea) is that we ignore the cost or the time they take. We assume that each Logical I/O or buffer get is the same cost. Which is not true. So the statement with a 1000 LIO could be more expensive than the statement with a 1200 LIO. The same is true for the Physical I/O. Not each physical I/O has the same cost or response time. Again the statement with 1000 I/Os may be more expensive than the 1200 I/Os, because one is going after different disks. I have run tests that show LIO for the same statement to be all most twice as slow (depending on some settings, but the SQL is the same, same plan) 3) If you now take the service time (which is CPU) and that part is 80 percent of the total response time, we can tell management that a 50 percent faster CPU will make roughly a 40 percent difference. That is not to say that is the right approach, because the opposite may also happen (20 percent CPU and 80 percent wait, 50 percent faster will only make a 10 percent improvement). I have seen customers with response time problems that consisted for over 80 percent of I/O problems (I/O too slow). They needed a 50 percent improvement but couldn't fix the I/O. So they wanted to find 50 percent some where else. That didn't happen ofcourse and they had to fix the I/O problem. Now that really helped management to understand where the priorities were: Yeeh, this is not a database problem but a disk array problem. 4) I believe that 80-90 percent of all Oracle applications out there in the field are highly inefficient. And that doesn't mean that they don't use bind variables or that they do many logical I/Os. And that the only way to fix them is faster CPU's (open to flames here ;-)) or do some serious redesigning. 5) Oracle provides many interesting statistics, but most of them only count. Now in Oracle 9i they have added some long overdue response time or timing statistics. But still it is lacking very important information. For example, how can we tell what a SQL statement waited for a particular session between 2 AM and 3 AM (without 10046 tracing) or for all sessions ? If the session performs a business function, what resources did the session use in that period for that business function ? That information is hard to come by, or with very high overhead with the traditional Oracle tools. 6) probably the most important point. The database doesn't decide WHAT SQL to execute. The database decides HOW SQL should be executed. The application decides WHAT to execute. Many fast SQL statements can still result in a slow business function, because do we need all those functions ? I have another favorite formula for that: Amount * Cost = total cost. So either reduce the cost or reduce the amount of SQL statements. Showed this formula to bunch of people at an Oracle user group in the netherlands. 2 days later, I got an email from someone saying that they concentrated on the amount instead of cost They reduced the batch job time from 2 hours to 10 minutes without tuning the SQL statement, but tuned the function. 7) The response time model gives the end-user perspective (without actually having to go to the coffee machine, unless you want coffee ;-)), but talking to them is very valuable. That doesn't mean they are right ;-) Anjo. Stephane Faroult wrote: Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I meet more and more (business) processes in which, without being top-notch, SQL statements do not look terribly bad. Rewrite everything, and it roars. I am not sure that digging deep in this case inside trace files is the most effective. Having a talk round the coffee-machine with end-users also helps. And you always have that terrible SQL statement which runs at 2 am and about which nobody cares as long as the maintenance window is large enough. What I question is the need to abuse queue theory
Re: where to find Oracle Installation guide for HP-UX ?
Next time include the release/version you're looking for, ya know!!! As for what needs to be done as root, here's the doc's, now it's your turn to dig. HP-UX, 7.3.3 http://uisnt1.humboldt.edu/otn/library/doc/server73x/AR73/toc.htm http://uisnt1.humboldt.edu/otn/library/doc/hp/server.733/a52813_1/doc.html http://uisnt1.humboldt.edu/otn/library/doc/hp/server.733/a43837_2/toc.htm HP-UX, 8.0.5 http://otn.oracle.com/doc/hp/server.805/a64527.pdf http://otn.oracle.com/doc/hp/server.805/a64525.pdf http://otn.oracle.com/doc/hp/server.805/a64526.pdf HP-UX, 8.1.5 http://otn.oracle.com/doc/hp/server.815/a67162/toc.htm http://otn.oracle.com/doc/hp/server.815/a67163/toc.htm HP-UX, 8.1.6 http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_installguide_816.pdf http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_adminguide_816.pdf http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_relnotes_816.pdf HP-UX, 8.1.7 http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_installguide_817.pdf http://otn.oracle.com/docs/products/oracle8i/pdf/hp-ux_adminguide_817.pdf http://otn.oracle.com/docs/products/oracle8i/pdf/linux_relnotes_817.pd HP-UX, 9.0.1 http://otn.oracle.com/docs/products/oracle9i/pdf/9i_hp_relnotes.pdf http://otn.oracle.com/docs/products/oracle9i/pdf/9i_hp_install.pdf http://otn.oracle.com/docs/products/oracle9i/pdf/9i_hp_installcl.pdf http://otn.oracle.com/docs/products/oracle9i/pdf/9i_unix_admin.pdf http://otn.oracle.com/docs/products/oracle9i/pdf/9i_unix_install.pdf http://uisnt1.humboldt.edu/otn/library/doc/hp.htm http://otn.oracle.com/doc/hp/server.805/a64527.pdf http://otn.oracle.com/docs/products/oracle8i/content.html http://otn.oracle.com/docs/products/oracle9i/content.html Brian P. MacLean Oracle DBA, OCP8i Andrey Bronfin andreyb@elrontelTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] esoft.com cc: Sent by: Subject: where to find Oracle Installation guide for HP-UX ? [EMAIL PROTECTED] 05/05/02 04:03 AM Please respond to ORACLE-L Hi ! As weird as it sounds - i could not locate the Oracle Installation guide for HP-UX neither on Metalink nor on Google. The only one i was able to find was the Quick start guide , which is not a replacement for the full one. Could u please refer me to a URL where i can get one ? I'm especially interested in a pre-/post-installation steps needed to be taken as root. Thanks a lot ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this
slow SQL query, diagnosis using 10046 trace event
Tuning Gurus, I am tuning one of our dayend batch jobs using the 10046 wait event (level 8). I see a whole lot of direct path read/ write events (1000's) in the trace file. Also many SQL*NET messages from/ to client waits. Not sure if this is the way PRO*C works, connects fetches and disconnects multiple time. Therefore there are also many FETCHES. Not sure what these events relate to. I am not sure if it may be related to sorting. However the session stats show only 3 sorts. (2 in memory and 1 to disk). This is a PROC*C program. Snippet from trace WAIT #5: nam='direct path read' ela= 0 p1=101 p2=167180 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=167183 p3=1 WAIT #5: nam='direct path read' ela= 3 p1=101 p2=167892 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=160653 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=160295 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=165431 p3=1 WAIT #5: nam='direct path read' ela= 4 p1=101 p2=166769 p3=1 WAIT #5: nam='direct path read' ela= 2 p1=101 p2=166770 p3=2 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166772 p3=1 WAIT #5: nam='direct path read' ela= 3 p1=101 p2=166773 p3=2 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=165382 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=161977 p3=1 WAIT #5: nam='direct path read' ela= 5 p1=101 p2=162178 p3=1 WAIT #5: nam='direct path read' ela= 3 p1=101 p2=166148 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=165788 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166562 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166565 p3=1 WAIT #5: nam='direct path read' ela= 4 p1=101 p2=166566 p3=2 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166352 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166355 p3=1 WAIT #5: nam='direct path read' ela= 0 p1=101 p2=166201 p3=1 WAIT #5: nam='direct path read' ela= 8 p1=101 p2=166204 p3=1 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=838976 p2=1 p3=0 FETCH #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=2,tim=28537720 WAIT #5: nam='SQL*Net message from client' ela= 0 p1=838976 p2=1 p3=0 Query Plan select orgplvee.org_lvl_parent ,prdplvee.prd_lvl_parent , (NVL(invbalee.on_hand_qty,0)+NVL(to_intrn_qty,0)) , (NVL(invbalee.on_hand_retl,0)+NVL(to_intrn_retl,0)) , (NVL(invbalee.on_hand_cost,0)+NVL(to_intrn_cost,0)) from invbalee ,orgplvee ,prdplvee where (orgplvee.org_lvl_child= invbalee.org_lvl_child and prdplvee.prd_lvl_child=invbalee.prd_lvl_child) order by orgplvee.org_lvl_parent,prdplvee.prd_lvl_parent call count cpu elapsed disk query current rows --- -- -- -- -- -- -- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 1073155 383.55 401.00 5501 0 0 2146310 --- -- -- -- -- -- -- total 1073155 383.55 401.00 5501 0 0 2146310 Misses in library cache during parse: 0 Parsing user id: 20 Execution Plan Id Par Pos Ins Plan --- 0 SELECT STATEMENT (choose) Cost (48836,5333714,170678848) 1 0 1 SORT (order by) Cost (48836,5333714,170678848) 2 1 1 HASH JOIN Cost (1705,5333714,170678848) 3 2 1 INDEX (analyzed) UNIQUE JDAPROD ORGPLVEEP1 (fast full scan) Cost (1,1073,5365) 4 2 2 HASH JOIN
Re: Cronjob
Dear Tom, Would you send me example of the script. Because I'm new with VMS environment. thanks in advance, Ahmadsyah - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 11:43 PM Ahmadsyah, If I remember correctly (my Vax is a couple of years old), we set up two jobs to accomplish this. One job ran every day. All this job did was to start the real job schedule job. the job schedule job interrogated what day of the week it was and ran other jobs. For example, if today is Thursday, it would start all jobs that were supposed to run on a thursday. the final thing that this job did was to re-schedule the first job for tommorrow at the same time. Kind of a circular job scheduling process. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, April 25, 2002 10:34 AM To: Multiple recipients of list ORACLE-L I have the same problem but with our vax/vms machine.. could I submit a job queue on every week or maybe every month... $Submi/Noprin/Notif/Que=Parm_Deplan/log=[diga.digu]dige.log - _$/After = tomorrow+06:00 - _$sys$geology:[diga.digu]dige.com I usually submit que every day.. but I want to submit every week and month or every saturday. Could I do it on our vax machine? regards, Ahmadsyah Alghozi Nugroho Certified Oracle DBA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 25, 2002 7:38 PM 0 06 * * 6 d31/appl/konto/bat/laddabilbo.sh /d31/appl/konto/log/laddabilbo.log 21 Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 25 April 2002 12:43 To: Multiple recipients of list ORACLE-L Hallo, I would like to have this cronjob run only in saturday mornings at 6 am. How could I easy change this script? 0 18 * * * /d31/appl/konto/bat/laddabilbo.sh /d31/appl/konto/log/laddabilbo.log 21 Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SARKAR, Samir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
Re: Cronjob
-- [EMAIL PROTECTED] Dear Tom, Would you send me example of the script. Because I'm new with VMS environment. thanks in advance, This is gonna be living hell to deal with in DCL. Suggestion: grab a copy of perl5 and use Schedule::Cron to cycle the jobs with the vms-ish file system to handle lookups. Main advantage is giving you a single point to track the results -- instead of having to deal with all of the /after options and separate jobs. With Schedule::Cron you can feed in a crontab file or use $cron-add( $schedule ) to generate the jobs w/in your code. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Lembark INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: stored procedure status
Hello kranti, What about error handling? Wednesday, May 01, 2002, 2:43:23 PM, you wrote: kp Hi list, kp I have writeen a stored procedure to execute a set of stored kp procedures. I want to know is there any way to stop the procedure after any kp of stoed procedure( from the set) returns an error. kp In simple words, is it possible to get a status whether the called procedure kp was successful or not. kp TIA kp Kranti -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Response time analysis and TKPROF
Anjo, you mentioned third party tool using instead of 10046 event trace files. What is the tool and how it works - if you have this info of cource. Alex Hillman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anjo Kolk Sent: Sunday, May 05, 2002 8:08 PM To: Multiple recipients of list ORACLE-L Subject: Re: Response time analysis and TKPROF Yes, I think that we are talking about different things: 1) I don't feel that we are abusing the queue theory by borrowing terms like service time and wait time. Actually when I did the YAPP method, it was back in 1996 on a project that involved Tuxedo and the programmers on the project wanted more processes. I had to convince them to do it with less and I could do that with the service and wait time model (calculating the response time in the Oracle server for sessions). And it really works well. 2) The problem with tuning by hit ratios and tuning by counting (like number of buffer gets and number of physical I/O from v$sqlarea) is that we ignore the cost or the time they take. We assume that each Logical I/O or buffer get is the same cost. Which is not true. So the statement with a 1000 LIO could be more expensive than the statement with a 1200 LIO. The same is true for the Physical I/O. Not each physical I/O has the same cost or response time. Again the statement with 1000 I/Os may be more expensive than the 1200 I/Os, because one is going after different disks. I have run tests that show LIO for the same statement to be all most twice as slow (depending on some settings, but the SQL is the same, same plan) 3) If you now take the service time (which is CPU) and that part is 80 percent of the total response time, we can tell management that a 50 percent faster CPU will make roughly a 40 percent difference. That is not to say that is the right approach, because the opposite may also happen (20 percent CPU and 80 percent wait, 50 percent faster will only make a 10 percent improvement). I have seen customers with response time problems that consisted for over 80 percent of I/O problems (I/O too slow). They needed a 50 percent improvement but couldn't fix the I/O. So they wanted to find 50 percent some where else. That didn't happen ofcourse and they had to fix the I/O problem. Now that really helped management to understand where the priorities were: Yeeh, this is not a database problem but a disk array problem. 4) I believe that 80-90 percent of all Oracle applications out there in the field are highly inefficient. And that doesn't mean that they don't use bind variables or that they do many logical I/Os. And that the only way to fix them is faster CPU's (open to flames here ;-)) or do some serious redesigning. 5) Oracle provides many interesting statistics, but most of them only count. Now in Oracle 9i they have added some long overdue response time or timing statistics. But still it is lacking very important information. For example, how can we tell what a SQL statement waited for a particular session between 2 AM and 3 AM (without 10046 tracing) or for all sessions ? If the session performs a business function, what resources did the session use in that period for that business function ? That information is hard to come by, or with very high overhead with the traditional Oracle tools. 6) probably the most important point. The database doesn't decide WHAT SQL to execute. The database decides HOW SQL should be executed. The application decides WHAT to execute. Many fast SQL statements can still result in a slow business function, because do we need all those functions ? I have another favorite formula for that: Amount * Cost = total cost. So either reduce the cost or reduce the amount of SQL statements. Showed this formula to bunch of people at an Oracle user group in the netherlands. 2 days later, I got an email from someone saying that they concentrated on the amount instead of cost They reduced the batch job time from 2 hours to 10 minutes without tuning the SQL statement, but tuned the function. 7) The response time model gives the end-user perspective (without actually having to go to the coffee machine, unless you want coffee ;-)), but talking to them is very valuable. That doesn't mean they are right ;-) Anjo. Stephane Faroult wrote: Anjo Kolk wrote: Stephane. The SQL statement is the right level, believe it or not. Basically the most expensive SQL statements (resource wise) will float to the top that way. Anji, I disagree, with a strong feeling of not talking about the same thing. My favorite method for finding the most expensive SQL statements is rather to check buffer gets at regular intervals, but here of course is a question of personal taste. But I
Re[2]: pl/sql is INTERPRETED?
Hello Alex, There are several books like Java in 21 days... You need to learn just basics. You don't need to remember all classes (I think it's not simple task). Therefore I think it's quite possible to learn java even in week or two. Thursday, May 02, 2002, 10:23:33 PM, you wrote: A It took you a week to learn it? Then you obviously do not know it. A Syntax is one thing design is another. I would love to know what you A learned in that week. A On Thu, 2 May 2002, Jared Still wrote: Hold on Lisa! Java is not complex. It's a very simple language actually. It took me a week to learn it, though I'm not using it now: I much prefer Perl. Getting a handle on all of the libraries and API's is another story, but Java as a language is pretty simple. Jared On Tuesday 30 April 2002 11:14, Koivu, Lisa wrote: You have a point Chris, but pl/sql is nowhere near as complex as an OO language like java or C++, IMHO. I agree with Tom that pl/sql can be learned fairly easily in comparison to the many other choices out there. However, it takes a bit of database savvy to do it correctly. (Not much tho) I was amazed in my database class in college that the same people failing the simple entity-relationship modeling portion of the class that had aced the Op Systems and networking classes we took. I nearly failed both classes, they were so complex. I was the teacher's pet in the db class because I asked him questions that made him think, and he sometimes couldn't answer. (And I had to wear a skirt - night student, straight from work.) What's easy for who is dependent on the person's strengths. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Grabowy, Chris [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Subject: RE: pl/sql is INTERPRETED? IMHO, I don't believe that you can properly learn PL/SQL in a very short period of time, or for that matter, any other language. I attended Steve Feuerstein's presentation at MAOP-AOTC conference, and he tore into many real-life examples of PL/SQL. Supposedly, these were written by developers that knew what they were doing. Granted, if a smart developer sits down and reads Feuerstein's Learning PL/SQL and Best Practices books, then perhaps they will be good. But who the hell has free time? There is no free time on any project or effort that I know of!! I'm struggling with trying to improve my Oracle DBA skills, plus some developers skills so I can speak their language when they blow out OPEN_CURSORS or something. My head is swimming in the stupid technical alphabet soup, XML, XDK, XSQL, XSLT, XPath, SOAP, ASP, ADO, EJB, BC4J, JDBC, SQLJ, PSP, JVM, JSP, J2EE, EAD, RMI, CORBA, IIOP...and don't ask me what all those mean, because I can't keep them straight. But I do keep hearing that XML is going to put me out of a job, so I guess I should learn that...whatever that is. Isn't XML an add-on, or extension, or something to DML??? Now where the heck did I hide that bottle... -Original Message- Sent: Tuesday, April 30, 2002 12:15 PM To: Multiple recipients of list ORACLE-L Lisa, You are right about the debate between PL/SQL Java (or anything else outside of the db). In my mind, the deciding factor (and something that is *never* mentioned) is what programming langauage the organization is satisfied with/settled upon. In my little opinion, *any* programmer can learn PL/SQL in a very short period of time. This means that development and maintenance costs are relatively low. If an IT shop is stronger in Java, then they should probably program in Java, or Cobol, or Ada, or whatever the flavor of the decade happens to be (lets bring back APL!). IT tool selection/standards should be the deciding factor. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, April 30, 2002 11:34 AM To: Multiple recipients of list ORACLE-L This is something that's been debated on the list in the past. The general consensus was: For manipulating data in the database, nothing beats pl/sql. It is well suited for this purpose. For everything else, java could beat it. I am sure fellow list members will post links describing studies. I remember seeing these last year. Stefan, have you tried running your own test? There's a sure fire way to convince yourself. Even a small test (no fancy code) would suffice. Wish I had more time to play... Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063
Oracle 9 Agent not starting
Hi All, I was wondering if anyone has had problems with Oracle 9i Agent starting up on Windows 2000 server. The following entries are written in the log files. I would really appreciate if anyone can shed some light on my problem so I can get the Agent started :-( Thanks Ian Agntsrvc.log -- [OracleOraHome90Agent] Sending initial service status.. Commandline is D:\oracle\ora90\bin\dbsnmpwd.bat -agent_name OracleOraHome90Agent 2. WAIT_OBJECT_0: Agent exited with retCode 0. Agent failed to startup. Check D:\oracle\ora90\network\log\OracleOraHome90Agent.nohup for details OracleOraHome90Agent.nohup Service name is OracleOraHome90Agent nmiumini_initializeUM: Unable to initialize UQFailed while initializing user subsystem Error initializing subsystems Agent exited on 06/05/2002 2:23p with return value 55 Could not start agent. Initialization failure Agent startup failed. Check D:\oracle\ora90\network\log\OracleOraHome90Agent.nohup for details Dbsnmp.log DBSNMP for 32-bit Windows: Version 9.0.1.0.1 - Production on 06-MAY-2002 14:23:03 Copyright (c) 2001 Oracle Corporation. All rights reserved. System parameter file is D:\oracle\ora90\network\admin\snmp_ro.ora Log messages written to D:\oracle\ora90\network\log\dbsnmp.log Trace information written to D:\oracle\ora90\network\trace\dbsnmp.trc Trace level is currently 0 NMS-1: Warning: dbsnmp unable to connect to SNMP master agent
Re: In an Analyze Necessary?
Hello Rajesh, Since you use rule-based optimisation there is no need for analyzing. Other benefits are: -you can view several statistics like number of rows in table etc. -with analyze command you can determine chained rows. Friday, May 03, 2002, 10:48:34 PM, you wrote: RRjc Hello Folks, RRjc A datawarehouse. There are only a few selected SQL's run against this RRjc database. And all this SQL's are tuned to optimum during design with hints RRjc embedded to take the least execution time. The SQL's currently execute in RRjc times, much better than what the expected response time was by the users. RRjc Would it still be required to analyze the tables? What other benefits would RRjc one reap from an analyze? Assume that there are no other SQL's running RRjc against the database other than this select few, and the data in the RRjc underlying tables has been accounted for a period of 2 years during design. RRjc Any new SQL's which would need to be added would go thru the drawing board. RRjc Absolutely Nothing adhoc. RRjc Regards RRjc Raj -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
