Query on Dual
Hi all, Can anyone tell me the performance issues related to the excessive usage of the DUAL table in pl/sql procedures? I remember reading an article about this ( I dont remember if it was in ASKTOM site ) wherein it was told that excessive usage of DUAL comes with a cost and there was a suggestion in that to create a view on the system table X$DUAL.. If anybody has a link to that please send it to me. Thanks to one and all. Regards Kesh This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
RE: dbms_stats via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Deleting Statspack tables.
Tim Gorman has produced an excellent package sppurpkg.sql which certainly does the business for me It can be found at http://www.evdbt.com/sppurpkg.sql John -Original Message- Sent: 11 June 2003 00:35 To: Multiple recipients of list ORACLE-L Version : 8.1.7.3. I am not sure how it is working for you just by deleting from stats$snapshot and we do not have 8.1.6 here. Thanks -Original Message- Sent: Tuesday, June 10, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Reddy - You didn't mention your Oracle version. I am on 8.1.6 and I don't think those scripts are available there (sorry, busy day, no time to research). So I just delete from stats$snapshot. It seems to remove data from the associated tables just fine. First I select the snap_id and snap_time from stats$snapshot to determine which snapshots to remove. It runs slowly (because of all the child tables?), so I just remove a hundred or so at a time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH50 STATS$SYSSTAT 51 TABLE Size in MB -- -- STATS$PARAMETER56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my
worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end
of hassle.
So all the answers that I said were incorrect were not, they had all replicated my
original typo.
But one thing I proved was that you can mix and match positional and naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
RE: Deleting Statspack tables.
Hi John, Tim Gorman's site has been moved to http://www.sagelogix.com. So one can find all the UNIX and SQL scripts in sagelogix.com rather than evbdt.com. Regards, Ranganath -Original Message- Sent: Wednesday, June 11, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Tim Gorman has produced an excellent package sppurpkg.sql which certainly does the business for me It can be found at http://www.evdbt.com/sppurpkg.sql John -Original Message- Sent: 11 June 2003 00:35 To: Multiple recipients of list ORACLE-L Version : 8.1.7.3. I am not sure how it is working for you just by deleting from stats$snapshot and we do not have 8.1.6 here. Thanks -Original Message- Sent: Tuesday, June 10, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Reddy - You didn't mention your Oracle version. I am on 8.1.6 and I don't think those scripts are available there (sorry, busy day, no time to research). So I just delete from stats$snapshot. It seems to remove data from the associated tables just fine. First I select the snap_id and snap_time from stats$snapshot to determine which snapshots to remove. It runs slowly (because of all the child tables?), so I just remove a hundred or so at a time. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 2:15 PM To: Multiple recipients of list ORACLE-L Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH50 STATS$SYSSTAT 51 TABLE Size in MB -- -- STATS$PARAMETER56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hallas, John, Tech Dev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an
Re: Query on Dual
Find it as attachmentWith Regards,Manoj Kumar JhaC-56 , Phase-2NOIDA -201305, UP(INDIA)Tata Consultancy ServicesPh No: (+91-120) 4461001 ext : 1037 (Off.)Mobile No : 9810090974A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil.Jayaram Keshava Murthy (Cognizant) [EMAIL PROTECTED]Sent by: [EMAIL PROTECTED]06/10/2003 11:24 PM PSTPlease respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: bcc: Subject: Query on Dual Hi all, Can anyone tell me the performance issues related to the excessive usage of the DUAL table in pl/sql procedures?I remember reading an article about this ( I dont remember if it was in ASKTOM site ) wherein it was told that excessive usage of DUAL comes with a cost and there was a suggestion in that to create a view on the system table X$DUAL..If anybody has a link to that please send it to me.Thanks to one and all.RegardsKesh-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Upgradation Question on APPS 11i
Dear Group, One of our customer is using apps 11.5.4 on WIN2K. For a demo, they wanted to upgrade to 11.5.5, then 11.5.8. How do I do that in a short way. In the metalink doc's they are giving a big procedure. Any help??? TIA Senthil -Original Message- Gogala Sent: Saturday, May 31, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Oracle 13? Probably nicknamed something like Jason is back version? It would be appropriate to have such a presentation on Friday the 13th. On 2003.05.30 23:09 Babette Turner-Underwood wrote: Don't worry about the lid being off of 10i. We will be doing an overview presentation of Oracle 13 this fall at the local user group. :-) - Babette -Original Message- Thomas F Sent: Friday, May 30, 2003 9:05 AM To: Multiple recipients of list ORACLE-L who promoted you to traffic cop? this is the only way I'm able to hear about the new green gui button. -Original Message- Sent: Thursday, May 29, 2003 5:10 PM To: Multiple recipients of list ORACLE-L OK everyone. Traffic cop time. Everyone that is a beta partner for 10i MUST KEEP THEIR MOUTH SHUT. Don't get anyone in trouble, please. We as partners pushed Oracle for these beta programs. Conversations like this only hurt the process. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Thursday, May 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: 10i Got my first look at the 10i beta last night. I can't tell you much about it except to say that looking at some of the new stuff h ;-) While I'm certain many of the new features will not work perfectly for several releases afterwards, they look very cool! RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official
Re: Re: Rule Based Optimizer
what did oracle use before the RBO? From: Mogens Nørgaard [EMAIL PROTECTED] Date: 2003/06/11 Wed AM 12:39:20 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Rule Based Optimizer Yep. Here's the official statement from Oracle about RBO in 10i: The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has continued to be available for backwards compatibility. Oracle Oracle10i will support only one optimizer, and all applications running on that release should use that optimizer. What's behind this wording? I don't really know, but I can guess. There's a myth that the RBO hasn't been enhanced since the introduction of CBO in 7.0. That, I learned one day in Steve Adams' car, is not true. It has been enhanced to know about new segment types, etc. A fun (well, fun...) exception to this is partitions. If you're using partitions you're using CBO. So I read it as: Yeah, we haven't really enhanced the RBO since 7.0 except to allow it to recognize IOT's and other new segment types. In 10i we won't even do that. Mogens Freeman Robert - IL wrote: RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Yep. Here's the official statement from Oracle about RBO in 10i: The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an
Re: Rule Based Optimizer
Paper and pen? On Wednesday 11 of June 2003 13:39, you wrote: what did oracle use before the RBO? From: Mogens Nørgaard [EMAIL PROTECTED] Date: 2003/06/11 Wed AM 12:39:20 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Rule Based Optimizer Yep. Here's the official statement from Oracle about RBO in 10i: The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has continued to be available for backwards compatibility. Oracle Oracle10i will support only one optimizer, and all applications running on that release should use that optimizer. What's behind this wording? I don't really know, but I can guess. There's a myth that the RBO hasn't been enhanced since the introduction of CBO in 7.0. That, I learned one day in Steve Adams' car, is not true. It has been enhanced to know about new segment types, etc. A fun (well, fun...) exception to this is partitions. If you're using partitions you're using CBO. So I read it as: Yeah, we haven't really enhanced the RBO since 7.0 except to allow it to recognize IOT's and other new segment types. In 10i we won't even do that. Mogens Freeman Robert - IL wrote: RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - On a quiet Night, you
RE: How to pass string with a ' to PL/SQL
Title: RE: How to pass string with a ' to PL/SQL Exactly ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 10, 2003 7:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to pass string with a ' to PL/SQL Sorry about that. Looking at your example more closely it seems like you may be doing it right. In your package body I see this: IF ( in_whereclause IS NOT NULL ) THEN v_sqlbeg := v_sqlbeg || in_orderbyclause ||' '; END IF; shouldn't that be in_whereclause in the second line? *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: How to pass string with a ' to PL/SQL
Lizz,
Remove
the 'execute' from your PL/SQL block. it is not needed - in fact - it is
causing the error.
"Execute" is only needed within sqlplus when not using
a PL/SQL block. You are using an anonymous PL/SQL block to call your
procedure.
Good
Luck!
Tom Mercadante Oracle Certified Professional
-Original Message-From: laura pena
[mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 5:15
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
How to pass string with a ' to PL/SQL
Here you go... So far I have not gotten it to work... I have tried all
suggestions so far.
Seems to work if I just do it to a variable but once I call in the stored
proc. It fails to work.
Thanks
-Lizz
"Jamadagni, Rajendra"
[EMAIL PROTECTED] wrote:
Show us the package body ... not the spec.
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this
email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-From: laura pena
[mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003
2:44 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: How to pass string with a ' to
PL/SQL
This does not work when executing my stored procedure:
SQL var a refcursorSQL var b varchar2(100);SQL
begin 2 :b :='and sub_account_no=' ||
|| '864240103' || ; 3 execute
:a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21
00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate
desc',:b); 4 end; 5 /
execute :a :=
pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-2100:00:00','2003-06-02
00:00:00','20','864240103','order by calldate
desc',:b);
*ERROR at line 3:ORA-06550: line 3, column 12:PLS-00103:
Encountered the symbol "" when expecting one of the following::= . ( @
% ; immediateThe symbol ";" was substituted for "" to
continue.
The package body is defined as:
CREATE OR REPLACE PACKAGE pkg_reports_dynamiccti AS--
Function fu_sales_analysis. Return summary information -- about
sales analysis for period of time for NetOne Reports from --
customerinfo table. -- Input: begin Date, end date, center_id,
account-- Output: reference cursorFUNCTION
fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN VARCHAR2,
in_center IN VARCHAR2, in_acct IN
VARCHAR2,
in_orderbyClause IN VARCHAR2,
in_whereclause IN VARCHAR2)RETURN
pkg_cursors.ref_cursor;FUNCTION fu_sales_analysis_address
(in_customerinfoid IN NUMBER, in_addressType IN
NUMBER)RETURN pkg_cursors.ref_cursor;END
pkg_reports_dynamiccti;/
this works:
SQL var a refcursorSQL var b varchar2(100);SQL
begin 2 :b :='and sub_account_no=' ||
|| '864240103' || ; 3 end; 4
/
PL/SQL procedure successfully completed.
SQL print b
B-
and sub_account_no='864240103'
*This
e-mail message is confidential, intended only for the named recipient(s)
above and may contain information that is privileged, attorney work product
or exempt from disclosure under applicable law. If you have received this
message in error, or are not the named recipient(s), please immediately
notify corporate MIS at (860) 766-2000 and delete this e-mail message from
your computer, Thank
you.*1
Do you Yahoo!?Free online
calendar with sync to Outlook(TM).
Re: Re: Rule Based Optimizer
Perhaps MBO (Mood Based Optimizer)!
- Original Message -
From:
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, June 11, 2003 7:39
AM
Subject: Re: Re: Rule Based
Optimizer
what did oracle use before the RBO? From:
Mogens Nørgaard [EMAIL PROTECTED] Date:
2003/06/11 Wed AM 12:39:20 EDT To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Subject: Re: Rule Based Optimizer Yep. Here's the official
statement from Oracle about RBO in 10i: "The RBO is Oracle's
legacy optimizer originating from Oracle Version 6 and earlier. The
RBO is being desupported in Oracle10i. The RBO will still exist in
Oracle10i, but will be an unsupported feature and will be removed in
a future release. No code changes will be made to the RBO code and no
bug fixes will be provided. The RBO was superseded in Oracle7 by the
CBO and has continued to be available for backwards compatibility.
Oracle Oracle10i will support only one optimizer, and all
applications running on that release should use that optimizer. "
What's behind this wording? I don't really know, but I can guess.
There's a myth that the RBO hasn't been enhanced since the
introduction of CBO in 7.0. That, I learned one day in Steve Adams'
car, is not true. It has been enhanced to know about new segment
types, etc. A fun (well, fun...) exception to this is partitions. If
you're using partitions you're using CBO. So I read
it as: "Yeah, we haven't really enhanced the RBO since 7.0 except to
allow it to recognize IOT's and other new segment types. In 10i we
won't even do that." Mogens Freeman Robert -
IL wrote: RWB, I'll tell you, if
KG says it about Oracle, I pretty much would go to the bank with
it. RF -Original
Message- To: Multiple recipients of list ORACLE-L
Sent: 6/10/2003 4:24 PM Mr.
Gopalakrishnan: How do you know this? Can anyone
confirm (or deny) this? I hope it is true!
Oracle should just quit cold turkey and not support the RULE
optimizer at all in any future releases.
RWB
Reginald W.
Bailey IBM Global Services - ETS SW GDSD - Database
Management Your Friendly Neighborhood DBA 713-216-7703
(Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
[EMAIL PROTECTED]
.com
To: [EMAIL PROTECTED]
Sent
by:
cc:
[EMAIL PROTECTED]
Subject: RE: Rule Based
Optimizer
ity.com
06/10/2003
12:59 PM
Please respond
to ORACLE-L
RULE
optimizer will not be supported in next version. But you can still
use the RULE optimizer and it is just a matter of official support
from Oracle. Best Regards, K
Gopalakrishnan
-Original Message- Goulet, Dick Sent:
Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list
ORACLE-L There appears to be a number of
folks on the list who have some insight into the next
version of the database. I'd like to ask those folks a simple
question. What is the future of the Rule Based Optimizer looking
like? Does it die in 10i?? Dick
Goulet Senior Oracle DBA Oracle Certified 8i
DBA -- Please see the official ORACLE-L FAQ:
http://www.orafaq.net -- Author: Goulet, Dick
INET: [EMAIL PROTECTED] Fat City Network
Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing
list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in the message BODY, include a line containing: UNSUB
ORACLE-L (or the name of mailing list you want to be removed
from). You may also send the HELP command for other
information (like subscribing).
-- Please see the official ORACLE-L FAQ:
http://www.orafaq.net -- Author: K
Gopalakrishnan INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051
http://www.fatcity.com San Diego,
California -- Mailing list and web
hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in the message BODY, include a line containing: UNSUB
ORACLE-L (or the name of mailing list you want to be removed
from). You may also send the HELP command for other
information (like subscribing).
Yep. Here's the official statement from Oracle about RBO in
10i:The RBO is Oracle's legacy optimizer originating from
RE: dbms_stats via dbms_job - syntax question
John,
My code doesn't work probably because CASCADE = TRUE should actually
be: CASCADE = TRUE.
So, this should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 3:24 AM
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list
Fragmentation ?
Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = UNIFORM OVER dba_tablespaces.ALLOCATION_TYPE = USER ? With ALLOCATION_TYPE = UNIFORM , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script storage (NEXT Value) which is allowed when having allocation_type=USER . This allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = UNIFORM automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = USER by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Named Pipes Protocol Adapter
And you would really allow your pipes to go nameless? I have both names and nicknames for the integral parts of my plumbing. Leaky Jack and wet Jill are my favorites. Windoze has no concept of SYSV IPC, in particular it doesn't know what shared memory is. Oracle on Windoze is not running processes, it's running threads. That means that you cannot attach (shmat) shared memory as on a civilized OS but you have to use Oracle*Net. The protocol that you are using to communicate with a database on your local machine is called named pipes. Just read the fine manuals, everything is in there. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 6:25 PM To: Multiple recipients of list ORACLE-L List, Why would someone want to use Named Pipes Protocol Adapter? I have never used this, could someone educated me on this? David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question SOLVED
But one thing I proved was that you can mix and match positional and
naming parameters
Hmm, I'd still rather use one OR another: you never know if mixing them
would work in the next version...
Why trying your luck?-:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the
syntax. In my worked example I had CASCADE=TRUE whereas it should have
been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and
naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD
E=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
RE: Database Auditing
Beware that user SYS is not AUDITable in 8i... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 1:14 AM To: Multiple recipients of list ORACLE-L Subject: Re: Database Auditing Ravindra, I'm not sure I understand the question correctly. Statement level auditing? Are you referring to recording that a particular statement was executed on a specific table? If so, the plain vanilla AUDIT statement is for you. There is no need for any triggers to be set up. If you want more elaborate, which rows the user updated, etc. you have a challenge. In Oracle 8i, you could go to log miner to mine all statements that modified data. Thi swill capture all but the SELECT statements. In Oralce 9i, there is a new feature called Fine Grained Auditing that can capture the exact statement the user issued and can even audit on based on the predicate of the select query. If youwant some moreinformation on the FGA, let me know. If you want to capture the DDL changes, the best option is to use system level triggers. I am not sure what you mean by OS level auditing of statements. HTH. Arup Nanda Author of the forthcoming book HIPAA Security and Auditing for Oracle from Rampant TechPress (www.Rampant.cc) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 12:04 AM Hi All, How do we turn on statement level auditing (DB and OS Level) to audit the DML/DDL's on the database.Is this possible with out triggers? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_stats via dbms_job - syntax question SOLVED
John
Then be sure to set the init.ora parameter
_do_what_I_mean=true
It solved most of my problems.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the syntax.
In my worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and naming
parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=T
RUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot get
the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official
RE: Re: Rule Based Optimizer
No it was just BO - and it stunk -Original Message- Sent: 11 June 2003 14:20 To: Multiple recipients of list ORACLE-L Perhaps MBO (Mood Based Optimizer)! - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Wednesday, June 11, 2003 7:39 AM what did oracle use before the RBO? From: Mogens Nørgaard [EMAIL PROTECTED] Date: 2003/06/11 Wed AM 12:39:20 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Rule Based Optimizer Yep. Here's the official statement from Oracle about RBO in 10i: The RBO is Oracle's legacy optimizer originating from Oracle Version 6 and earlier. The RBO is being desupported in Oracle10i. The RBO will still exist in Oracle10i, but will be an unsupported feature and will be removed in a future release. No code changes will be made to the RBO code and no bug fixes will be provided. The RBO was superseded in Oracle7 by the CBO and has continued to be available for backwards compatibility. Oracle Oracle10i will support only one optimizer, and all applications running on that release should use that optimizer. What's behind this wording? I don't really know, but I can guess. There's a myth that the RBO hasn't been enhanced since the introduction of CBO in 7.0. That, I learned one day in Steve Adams' car, is not true. It has been enhanced to know about new segment types, etc. A fun (well, fun...) exception to this is partitions. If you're using partitions you're using CBO. So I read it as: Yeah, we haven't really enhanced the RBO since 7.0 except to allow it to recognize IOT's and other new segment types. In 10i we won't even do that. Mogens Freeman Robert - IL wrote: RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for
Urgent: Need Oradim.exe for version 8.1.6
I need the Oradim.exe for version 8.1.6 as its got corrupted. Can someone please zip it and send it to me? Regards Naveen -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: sql query optimization Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Deleting Statspack tables.
Tim's script is not much of different except deleting from the stats$sqltext from original SPPURGE.sql . But if you see my mail below I do have a lot of other tables which needs to be purged periodically. Thank You, Madhu Reddy -Original Message- Sent: Tuesday, June 10, 2003 9:50 PM To: Multiple recipients of list ORACLE-L Um, if I understand correctly, you're just trying to keep the volume down in your statspack tables. I use Tim Gorman's sppurpkg.sql package (on several different versions across several different operating systems). (www.evdbt.com) I have it set up to keep 14 days of data, but you can change that. It schedules the dbms_job for you. I just set up his job when I configure statspack on a new database, and then I never need to think about it again. You might take a look and see if it'll work for you. Barb --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH50 STATS$SYSSTAT 51 TABLE Size in MB -- -- STATS$PARAMETER56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation ?
Vivek Make sure you've read How to Stop Defragmenting and Start Living http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 The authors point out that uniform extents stop fragmentation at the tablespace level. However they point out that there are other forms of fragmentation. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = UNIFORM OVER dba_tablespaces.ALLOCATION_TYPE = USER ? With ALLOCATION_TYPE = UNIFORM , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script storage (NEXT Value) which is allowed when having allocation_type=USER . This allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = UNIFORM automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = USER by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
New Oracle security tool?
Anyone know what the forthcoming security tools are called? Part of OEM IV??? http://www.eweek.com/article2/0,3959,1121278,00.asp Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to pass string with a ' to PL/SQL - Solved -Thanks.
Everyone:
Yes this worked.
This is what I originally ... originally had !!! The problem seem to be with my code.
Pointed out by Jacques. My orderby clause was where the whereclause statement should be..
Thanks for the code review and feed back everyone. Learned a lot with this exercise.
"Gorbounov,Vadim" [EMAIL PROTECTED] wrote:
Laura,
Would you try this?
var a refcursorvar b varchar2(100);begin :b :='and sub_account_no=''864240103'''; :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);end;/
Regards
Vadim
-Original Message-From: laura pena [mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003 1:25 PMTo: Multiple recipients of list ORACLE-LSubject: How to pass string with a ' to PL/SQL
I have procedure that needs to pass a string with a ' .
Anyone have any idea how to do this in PL/SQL
Here is my example:
var a refcursorvar b varchar2(100);begin :b :='and sub_account_no=''864240103'''; execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00:00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b);end;/
This is giving me an error.
ERROR at line 3:ORA-06550: line 3, column 12:PLS-00103: Encountered the symbol "" when expecting one of the following::= . ( @ % ; immediateThe symbol ";" was substituted for "" to continue.
Thanks in advance.
-Lizz
Do you Yahoo!?Free online calendar with sync to Outlook(TM).
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
Re: Named Pipes Protocol Adapter
Mladen, I think what is being referred to here is that named pipes can be set up as a protocol in the listener on Windoze. As to why one would want to do that, I don't have an answer. Jared On Wednesday 11 June 2003 07:00, Gogala, Mladen wrote: And you would really allow your pipes to go nameless? I have both names and nicknames for the integral parts of my plumbing. Leaky Jack and wet Jill are my favorites. Windoze has no concept of SYSV IPC, in particular it doesn't know what shared memory is. Oracle on Windoze is not running processes, it's running threads. That means that you cannot attach (shmat) shared memory as on a civilized OS but you have to use Oracle*Net. The protocol that you are using to communicate with a database on your local machine is called named pipes. Just read the fine manuals, everything is in there. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 6:25 PM To: Multiple recipients of list ORACLE-L List, Why would someone want to use Named Pipes Protocol Adapter? I have never used this, could someone educated me on this? David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Urgent: Need Oradim.exe for version 8.1.6
O/S ? 32 bit or 64 bit?? John -Original Message- Sent: 11 June 2003 15:25 To: Multiple recipients of list ORACLE-L I need the Oradim.exe for version 8.1.6 as its got corrupted. Can someone please zip it and send it to me? Regards Naveen -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: sql query optimization Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L
RE: How to pass string with a ' to PL/SQL
You rock !!!
Should have reviews my code closer!!
Thanks.Jacques Kilchoer [EMAIL PROTECTED] wrote:
Sorry about that. Looking at your example more closely it seems like you maybe doing it right.In your package body I see this:IF ( in_whereclause IS NOT NULL ) THEN v_sqlbeg := v_sqlbeg || in_orderbyclause ||' '; END IF;shouldn't that be "in_whereclause" in the second line? -Original Message- From: Jacques Kilchoer Sent: mardi, 10. juin 2003 14:55 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: How to pass string with a ' to PL/SQL I think the problem is that once you pass the string to a PL/SQL procedure, and you want to use it in dynamic SQL, you have to "escape" the single quote again. Perhaps the example below will help. SQL select * from emp ; ID LAST_NAME FIRST_NAME -
-- -- 1 MILLER ARTHUR 2 O'NEILL EUGENE SQL create function get_id (in_where_clause varchar2) return number 2 is 3 the_id number ; 4 begin 5 execute immediate 'select id from emp where ' || in_where_clause 6 into the_id ; 7 return the_id ; 8 end ; 9 / Fonction créée. SQL variable return_id number SQL -- in this example, I want the function to receive the string SQL -- (last_name = 'MILLER') so I surround it with single quotes SQL -- and change each of the single quotes to two single quotes SQL execute :return_id := get_id ('last_name = ''MILLER''') Procédure PL/SQL terminée avec succès. SQL print RETURN_ID - 1 SQL -- in this example, I want the function to
receive the string SQL -- (last_name = 'O''NEILL') so I surround it with single quotes SQL -- and change each of the single quotes to twoo single quotes SQL execute :return_id := get_id ('last_name = ''ONEILL''') Procédure PL/SQL terminée avec succès. SQL print RETURN_ID - 2 SQL -Original Message- From: laura pena [mailto:[EMAIL PROTECTED] Here you go... So far I have not gotten it to work... I have tried all suggestions so far. Seems to work if I just do it to a variable but once I call in the stored proc. It fails to work. -Original Message- "Jamadagni, Rajendra" <[EMAIL PROTECTED]>wrote: Show us the package body ... not the spec. -Original Message- From: laura pena
[mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: How to pass string with a ' to PL/SQL This does not work when executing my stored procedure: SQL var a refcursor SQL var b varchar2(100); SQL begin 2 :b :='and sub_account_no=' || || '864240103' || ; 3 execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-21 00:00 :00','2003-06-02 00:00:00','20','864240103','order by calldate desc',:b); 4 end; 5 / execute :a := pkg_reports_Dynamiccti.fu_sales_analysis('2003-05-2100:00:00', '2003-06-02 00:00:00','20','864240103','order by calldate desc',:b); * ERROR at line 3: ORA-06550: line 3, column 12: PLS-00103: Encountered the symbol "" when expecting one of the following: := . (
@ % ; immediate The symbol ";" was substituted for "" to continue. The package body is defined as: CREATE OR REPLACE PACKAGE pkg_reports_dynamiccti AS -- Function fu_sales_analysis. Return summary information -- about sales analysis for period of time for NetOne Reporrts from -- customerinfo table. -- Input: begin Date, end date, center_id, account -- Output: reference cursor FUNCTION fu_sales_analysis (in_begdate IN VARCHAR2, in_enddate IN VARCHAR2, in_center IN VARCHAR2, in_acct IN VARCHAR2, in_orderbyClause IN VARCHAR2, in_whereclause IN VARCHAR2) RETURN pkg_cursors.ref_cursor; FUNCTION fu_sales_analysis_address (in_customerinfoid IN NUMBER, in_addressType IN NUMBER) RETURN pkg_cursors.ref_cursor; END pkg_reports_dynamiccti; / this works: SQL var a refcursor
SQL var b varchar2(100); SQL begin 2 :b :='and sub_account_no=' || || '864240103' || ; 3 end; 4 / PL/SQL procedure successfully completed. SQL print b B -- --- and sub_account_no='864240103'& -- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jacques KilchoerINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You
mayalso send the HELP command for other information (like subscribing).
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
Re: Query on Dual
Kesh, I have some information on my website (http://www.optimaldba.com/internals/oraint_dual.html). It is a pretty terse discussion of the topic. I know that Cary Millsap has tested and written a paper on this as well. IIRC, there is mention of DUAL in his paper on reducing Logical I/Os. You can find out more on his website at www.hotsos.com. -- Daniel W. Fink http://www.optimaldba.com Jayaram Keshava Murthy (Cognizant) wrote: Hi all, Can anyone tell me the performance issues related to the excessive usage of the DUAL table in pl/sql procedures? I remember reading an article about this ( I dont remember if it was in ASKTOM site ) wherein it was told that excessive usage of DUAL comes with a cost and there was a suggestion in that to create a view on the system table X$DUAL.. If anybody has a link to that please send it to me. Thanks to one and all. Regards Kesh This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Hash Tables and PL/SQL (Ora 9.2)
I've been trying to create a hash table using PL/SQL but I seem to be running into some trouble. Hoping someone can point me in the right direction. I've been using PL/SQL Users Guide and Reference Ch 5 as a guide. I have the following piece of code: declare cursor c1_cur is select * from load_tab; type rdt_rec_type is table of varchar2(30) index by varchar2(30); rdt_type rdt_rec_type; begin open c1_cur; loop fetch c1_cur into c1_rec; exit when c1_cur%notfound; if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; end loop; end; / My goal is to have only those values in the hash table (rdt values) that are not dups. I was hoping that object.exists(value) would work, but apparently I seem to be getting everything in my hash. Any ideas how I can code this? thanks mohammed __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Hash Tables and PL/SQL (Ora 9.2)
Ok, I found the bug. Just re-read ch 5 page 5-4 Understanding Associative Arrays (Index-By Tables). The problem was here: if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; This should have been: if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(c1_rec.rdt) := c1_rec.rdt; end if; Now my hash works and has unique values. mohammed --- mkb [EMAIL PROTECTED] wrote: I've been trying to create a hash table using PL/SQL but I seem to be running into some trouble. Hoping someone can point me in the right direction. I've been using PL/SQL Users Guide and Reference Ch 5 as a guide. I have the following piece of code: declare cursor c1_cur is select * from load_tab; type rdt_rec_type is table of varchar2(30) index by varchar2(30); rdt_type rdt_rec_type; begin open c1_cur; loop fetch c1_cur into c1_rec; exit when c1_cur%notfound; if rdt_type.exists(c1_rec.rdt) then null; else rdt_type(ctr) := c1_rec.rdt; end if; end loop; end; / My goal is to have only those values in the hash table (rdt values) that are not dups. I was hoping that object.exists(value) would work, but apparently I seem to be getting everything in my hash. Any ideas how I can code this? thanks mohammed __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to:
[no subject]
SET ORACLE-L NOMAIL -- - Software Engineering Vattmannstraße 7, 33100 Paderborn Phone: (+49) 52 51 / 30 1 6333 Fax: (+49) 52 51 / 30 16 399 eMail: [EMAIL PROTECTED] http://www.unitrade.com -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joerg Jost INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Performance Problems Solaris Vs Windows
Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj Blank Bkgrd.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. sql.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. blueLineL.GIFThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. sort.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. empty.GIFThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for
RE: Named Pipes Protocol Adapter
Jared, I use to use Named Pipes MANY years ago before TCP/IP became the major player in networking. It runs on top of NBP or NetBeui. While one can use it with WinDoze that's about all. None of the Unix vendors that I know of support it. I believe that even MicroSlop has deprecated it's use. So, Why would one want to use it? Probably because they don't know any better. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 11:29 AM To: Multiple recipients of list ORACLE-L Mladen, I think what is being referred to here is that named pipes can be set up as a protocol in the listener on Windoze. As to why one would want to do that, I don't have an answer. Jared On Wednesday 11 June 2003 07:00, Gogala, Mladen wrote: And you would really allow your pipes to go nameless? I have both names and nicknames for the integral parts of my plumbing. Leaky Jack and wet Jill are my favorites. Windoze has no concept of SYSV IPC, in particular it doesn't know what shared memory is. Oracle on Windoze is not running processes, it's running threads. That means that you cannot attach (shmat) shared memory as on a civilized OS but you have to use Oracle*Net. The protocol that you are using to communicate with a database on your local machine is called named pipes. Just read the fine manuals, everything is in there. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 10, 2003 6:25 PM To: Multiple recipients of list ORACLE-L List, Why would someone want to use Named Pipes Protocol Adapter? I have never used this, could someone educated me on this? David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: New Oracle security tool?
Oracle Security Auditing Tool for Analyzing Networks Does anybody have any good suggestions for abbreviations?Mladen GogalaOracle DBAPhone:(203) 459-6855Email:[EMAIL PROTECTED]-Original Message-From: Jesse, Rich [mailto:[EMAIL PROTECTED]]Sent: Wednesday, June 11, 2003 11:20 AMTo: Multiple recipients of list ORACLE-LSubject: New Oracle security tool?Anyone know what the forthcoming security tools are called? Part of OEMIV???http://www.eweek.com/article2/0,3959,1121278,00.aspRichRich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Jesse, Rich INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Sun to Linux on Dell
Title: Message I'd go Windows on the Dell box. There are some quite nice tools like perfmon that can give you a lot of info about what is going on. My preferred Windows box though would be the Proliant, it's earned it's reputation over time for performance andreliability. Problem with a big system though is the cost of the port. I'd tend to stick with a SUN box with a SAN but do the sums. If you want sheer performance dump the disk drives except a couple mirrored for the operating system then go something like an HP SAN. We've seen thirty fold improvement on some databases going SAN keeping the same server. Cheerio John -Original Message-From: Michael Kline [mailto:[EMAIL PROTECTED] Sent: June 9, 2003 1:14 PMTo: Multiple recipients of list ORACLE-LSubject: Sun to Linux on Dell I have a cust pounding a Sun E450 I believe they said it was with a large RAID with fiber channels. They pound this thing at 2,037 I/O per seconds and end up having about 3,868 I/O per data block wait. This is averaged over 3-5 days, 24 hr/day, so there are times it's way over that. While sometimes slow, performance is acceptable most of the time. They are contemplating moving it to Linux and a very high end Dell system, perhaps 4-8 CPU, etc. I've always heard Sun was pretty much the best on heavy I/O and if it were not for the fiber channels, they would probably have been hurting big time some time ago. They are only in RD right now, but has anyone done a move of this nature? The database is Siebel with many mods and sub systems, average tuned, about 76gb. It is about 139 million records. They are around 200 users or so. Maks
RE: Deleting Statspack tables.
I'm having difficulty following all of the referential integrity on perfstat tables, but there are several on delete cascade constraints on the tables. It looks to me like all of my perfstat tables are being cleaned up. I've been accumulating data on 1 database for more than 1 year every hour at level 5, and my largest table is 10 megs. It's very odd that your STATS$LATCH_CHILDREN table is nearly 3 gigs. I have to wonder if something else is wrong. You might check your perfstat user to insure that the user has adequate privs; you might even consider removing and re-installing statspack. Good luck! Barb --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Tim's script is not much of different except deleting from the stats$sqltext from original SPPURGE.sql . But if you see my mail below I do have a lot of other tables which needs to be purged periodically. Thank You, Madhu Reddy -Original Message- Sent: Tuesday, June 10, 2003 9:50 PM To: Multiple recipients of list ORACLE-L Um, if I understand correctly, you're just trying to keep the volume down in your statspack tables. I use Tim Gorman's sppurpkg.sql package (on several different versions across several different operating systems). (www.evdbt.com) I have it set up to keep 14 days of data, but you can change that. It schedules the dbms_job for you. I just set up his job when I configure statspack on a new database, and then I never need to think about it again. You might take a look and see if it'll work for you. Barb --- Reddy, Madhusudana [EMAIL PROTECTED] wrote: Hello All, We have a job ( shell Script ) which deletes from the statspack tables every Sunday, but uses SPPURGE.sql ( $ORACLE_HOME/rdbms/admin). Seems like it is not deleting all tables and hence causing the tablespace to grow. Does anybody has a different approach which deletes all table without any referential integrity issues ?? I do not want to use SPTRUNC.sql please. Pl find below some of the statspack tables are growing so big ( also Indexes /PKs ). STATS$BG_EVENT_SUMMARY 7 STATS$ROWCACHE_SUMMARY 8 STATS$SGASTAT 8 STATS$LATCH_PARENT 10 STATS$SYSTEM_EVENT 19 STATS$ROLLSTAT 37 STATS$LATCH50 STATS$SYSSTAT 51 TABLE Size in MB -- -- STATS$PARAMETER56 STATS$LATCH_MISSES_SUMMARY 84 STATS$SQLTEXT 122 STATS$FILESTATXS 234 STATS$SQL_SUMMARY 886 STATS$LATCH_CHILDREN 2872 ( These are just tables , didnt show indexes here) Thank You , Madhu Reddy -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of
RE: Upgradation Question on APPS 11i
It is a Big Procedure ;) Sorry ... Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Senthil Kumar D Sent: Wednesday, June 11, 2003 7:00 PM To: Multiple recipients of list ORACLE-L Dear Group, One of our customer is using apps 11.5.4 on WIN2K. For a demo, they wanted to upgrade to 11.5.5, then 11.5.8. How do I do that in a short way. In the metalink doc's they are giving a big procedure. Any help??? TIA Senthil -Original Message- Gogala Sent: Saturday, May 31, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Oracle 13? Probably nicknamed something like Jason is back version? It would be appropriate to have such a presentation on Friday the 13th. On 2003.05.30 23:09 Babette Turner-Underwood wrote: Don't worry about the lid being off of 10i. We will be doing an overview presentation of Oracle 13 this fall at the local user group. :-) - Babette -Original Message- Thomas F Sent: Friday, May 30, 2003 9:05 AM To: Multiple recipients of list ORACLE-L who promoted you to traffic cop? this is the only way I'm able to hear about the new green gui button. -Original Message- Sent: Thursday, May 29, 2003 5:10 PM To: Multiple recipients of list ORACLE-L OK everyone. Traffic cop time. Everyone that is a beta partner for 10i MUST KEEP THEIR MOUTH SHUT. Don't get anyone in trouble, please. We as partners pushed Oracle for these beta programs. Conversations like this only hurt the process. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email:[EMAIL PROTECTED] Web: www.compuware.com -Original Message- Sent: Thursday, May 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: 10i Got my first look at the 10i beta last night. I can't tell you much about it except to say that looking at some of the new stuff h ;-) While I'm certain many of the new features will not work perfectly for several releases afterwards, they look very cool! RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
RE: Rule Based Optimizer
Tom, I think you meant 'RBO' in 5.x and 6.0 rather than CBO... There were the 15 rules. John -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Rule Based Optimizer CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB == == Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) == == [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: Rule Based Optimizer
I know that the CBO existed on Oracle 7.0.15 for HP-UX. I remember it well because we would frequently get incorrect and inconsistent results. Installing the 7.0.16 patch _mostly_ fixed these issues. We still had a large number of queries that we hinted to use the RBO as we couldn't get the desired query performance with the CBO Kevin -Original Message- Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Not true. IIRC the first Oracle version with dual optimizer mode was 7.1.x. Best Regards, K Gopalakrishnan -Original Message- Mercadante, Thomas F Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
RE: Rule Based Optimizer
Title: RE: Rule Based Optimizer Tom, I thought the rules of precedence were for RBO (the R in RBO). Whereas CBO was _intelligent_ enough to choose a path based on information available on hand. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Mercadante, Thomas F Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
selectivity of predicates with LIKE - diff between 8i and 9i
Dear List, Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO? We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS. After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i: [EMAIL PROTECTED] @target COUNT(1) -- 291 [EMAIL PROTECTED] l 1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%' -- 8i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- - 0 3 1 SELECT STATEMENT (choose) Cost (3,1,20) 10 1 SORT (aggregate) 21 3 2 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40) -- 9i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- --- 0 39 1 SELECT STATEMENT (choose) Cost (39,1,19) 10 1 SORT (aggregate) 21 39 8415 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885) -- 8i: Access path: index (index-only) INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE (obj_id=307169 - DIS_IND_ALBUM_TITRE_1) CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06 ... BEST_CST: 3.00 PATH: 4 Degree: 1 -- 9i: Access path: index (index-only) Index: DIS_IND_ALBUM_TITRE_1 TABLE: DIS_TAB_ALBUM_TITRE RSC_CPU: 0 RSC_IO: 39 IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02 ... BEST_CST: 39.00 PATH: 4 Degree: 1 In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below): INDEX#: 307169 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 161254NULLS: 0 DENS: 6.2014e-06 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 - 3 But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39: INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 157906NULLS: 0 DENS: 6.3329e-06 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions: 1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39? 2) Is there a simple way to get it back on track to CST=2 without hints or stored outlines - some spfile parameter would be ideal? 3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291). Would histograms be the right way to get CMPTD CDN closer to the reality in this case? Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M) Thanks for any help, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
well beat me with a stick and call me black and blue. of *course* I meant RBO! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 12:30 PM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
RE: Rule Based Optimizer
Same here. Hopefully 10i's CBO has matured. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 1:16 PM To: Multiple recipients of list ORACLE-L I know that the CBO existed on Oracle 7.0.15 for HP-UX. I remember it well because we would frequently get incorrect and inconsistent results. Installing the 7.0.16 patch _mostly_ fixed these issues. We still had a large number of queries that we hinted to use the RBO as we couldn't get the desired query performance with the CBO Kevin -Original Message- Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Not true. IIRC the first Oracle version with dual optimizer mode was 7.1.x. Best Regards, K Gopalakrishnan -Original Message- Mercadante, Thomas F Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
OT: uuencode and mail
Hi all, Platform: AIX 4.3.3 I realize this off topic but it is somewhat related. Each morning a cron job creates an SQL*Plus output in csv format which gets e-mailed out as an attachment: $ uuencode somefile.csv somefile.csv |mail -s Here is your file [EMAIL PROTECTED] I still want to continue receiving the attachment but I cannot figure out how to write an e-mail message in the body of that e-mail which gives an explanation of what the report is and contains a disclaimer type clause. If I do this: uuencode somefile.csv somefile.csv |mail -s TEST [EMAIL PROTECTED] testmsg I only receive what is in testmsg as the body of the e-mail and not the csv attachment. If anyone can contribute to a solution, I'd be grateful. Thanks, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: selectivity of predicates with LIKE - diff between 8i and 9i
By any chance are you using CURSOR_SHARING parameter in your 8i version? I think the default selectivity of 5% is used while costing the like operator and with the binds (and with an underscore parameter which I think defaults TRUE) it is treated as equality . If not you can set the underscore parameter _like_with_bind_as_equality to get the index costing. KG Best Regards, K Gopalakrishnan -Original Message- Boris Dali Sent: Wednesday, June 11, 2003 10:21 AM To: Multiple recipients of list ORACLE-L Dear List, Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO? We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS. After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i: [EMAIL PROTECTED] @target COUNT(1) -- 291 [EMAIL PROTECTED] l 1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%' -- 8i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- - 0 3 1 SELECT STATEMENT (choose) Cost (3,1,20) 10 1 SORT (aggregate) 21 3 2 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40) -- 9i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- --- 0 39 1 SELECT STATEMENT (choose) Cost (39,1,19) 10 1 SORT (aggregate) 21 39 8415 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885) -- 8i: Access path: index (index-only) INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE (obj_id=307169 - DIS_IND_ALBUM_TITRE_1) CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06 ... BEST_CST: 3.00 PATH: 4 Degree: 1 -- 9i: Access path: index (index-only) Index: DIS_IND_ALBUM_TITRE_1 TABLE: DIS_TAB_ALBUM_TITRE RSC_CPU: 0 RSC_IO: 39 IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02 ... BEST_CST: 39.00 PATH: 4 Degree: 1 In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below): INDEX#: 307169 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 161254NULLS: 0 DENS: 6.2014e-06 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 - 3 But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39: INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 157906NULLS: 0 DENS: 6.3329e-06 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions: 1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39? 2) Is there a simple way to get it back on track to CST=2 without hints or stored outlines - some spfile parameter would be ideal? 3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291). Would histograms be the right way to get CMPTD CDN closer to the reality in this case? Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M) Thanks for any help, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ:
wait event puzzler
Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Rule Based Optimizer
did oracle always use the RBO? or did it have some extremely simplified optimizer prior to 6.0? i know some of you haev been around a while... hopefully this doesnt make you feel old. no real need to know. just curious how the technology has progressed over time. From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/06/11 Wed PM 01:25:07 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer well beat me with a stick and call me black and blue. of *course* I meant RBO! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 12:30 PM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: wait event puzzler
Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan -Original Message- Meng, Dennis Sent: Wednesday, June 11, 2003 11:02 AM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: selectivity of predicates with LIKE - diff between 8i and 9i
Thanks, KG No, CURSOR_SHARING is not set (meaning it defaults to EXACT on both 8i and 9i) No bind variables. The query given with ... LIKE 'LOVE%' is the real one Yes, I can try setting _LIKE_WITH_BIND_AS_EQUALITY. One additional piece of info - we don't have OPTIMIZER_INDEX_* parameter set on eigher one (I should re-read Tim's excellent paper). It would probably be a safer bet to change supported parameters first (after understanding what is different in 9i that is), but I am not sure what effect it might have on the rest of the app. Cheers, Boris. --- K Gopalakrishnan [EMAIL PROTECTED] wrote: By any chance are you using CURSOR_SHARING parameter in your 8i version? I think the default selectivity of 5% is used while costing the like operator and with the binds (and with an underscore parameter which I think defaults TRUE) it is treated as equality . If not you can set the underscore parameter _like_with_bind_as_equality to get the index costing. KG __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: wait event puzzler
It's reads the block using the *ROWID*...that would explain his concerns... Regis -Original Message- Sent: Wednesday, June 11, 2003 7:19 PM To: Multiple recipients of list ORACLE-L Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan -Original Message- Meng, Dennis Sent: Wednesday, June 11, 2003 11:02 AM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: wait event puzzler
A SINGLE block? I was convinced that it was = DB_FILE_MULTIBLOCK_READ_COUNT blocks. Are you sure? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan -Original Message- Meng, Dennis Sent: Wednesday, June 11, 2003 11:02 AM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: wait event puzzler
The db file sequential read means that oracle is trying to read a sequence of consecutive blocks, shorter then DB_FILE_MULTIBLOCK_READ_COUNT. That usually happens when reading indexes, but it's not inconceivable to happen during table access as well. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 2:02 PM To: Multiple recipients of list ORACLE-L Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? TIA Dennis - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to put a TOP 1 in a select
I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a INNER JOIN ( select item, location, sum(quantity) as can_vta from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 05700185/29/2003 12:00:00 AMTJU02-405700185/31/2003 12:00:00 AMTJU02-305700185/30/2003 12:00:00 AMTJU02-205700186/2/2003 12:00:00 AM TJU02-205700186/3/2003 12:00:00 AMTJU02-205700186/1/2003 12:00:00 AM TJU02-105700185/30/2003 12:00:00 AMTJU24-605700186/1/2003 12:00:00 AM TJU24-605700186/2/2003 12:00:00 AM TJU24-505700186/3/2003 12:00:00 AM TJU24-305700185/31/2003 12:00:00 AMTJU24-205700186/9/2003 12:00:00 AM TJU31-4 Results: Item Date Location Quantity == 05700185/29/2003 12:00:00 AM TJU02 -14 05700186/1/2003 12:00:00 AM TJU24 -22 05700185/30/2003 12:00:00 AM TJU24 -22 05700186/9/2003 12:00:00 AMTJU31 -4 The problem is that when we have an item that sold two or more days the same quantity and it is the biggestquantityit returns more that one row per item. This query is execute in 28 seconds [EMAIL PROTECTED] 06/10/03 09:24PM There may be different ways to write this query - otherwise you need tolook at tuning this query. Four minutes for a single product in a 5million row table doesn't sound really good but I guess it depends onhardware.Have a look at the explain plan for the query - you have a lot of selectioncriteria on the ICTRANS table. If one (or a group) of those criteria isvery restrictive then index (and analyze) that column(s) to see theperformance gain.I've had a go at writing this as an analytical query. The syntax may beincorrect since I don't have anything convenient to test it against. Theremay even be syntax errors - it was simply typed in without being executed.Good luck!!!select a.item, a.location, a.trans_date, sum(b.quantity) from (select item, location, trans_date rank() over (partition by item, location order by quantity desc)the_rankfrom ictranswhere company = 2000 and trans_date between (current_date-14) andcurrent_dateand doc_type = 'IS' and reason_code = 'VTCL'and substr(item,2,2) = '57') a,ictrans bwhere a.the_rank = 1and a.item = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) = '57') "Teresita Castro" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] martmx.com cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] om 11/06/2003 11:59 Please respond to ORACLE-L This is an example of the information.I forgot to mention that in each company we have supermarkets called in thesystem Locations. So when I do this query I have to return per item thetotal of sales in the las two week, the day that we sale more persupermarketItem trans_date Quantity Location0570004 5/29/2003 12:00:00 AM -1 TJU020570004 6/3/2003 12:00:00 AM -1 TJU240570004 6/9/2003 12:00:00 AM -1 TJU310570006 5/28/2003 12:00:00 AM -1 TJU240570010 6/3/2003 12:00:00 AM -1 TJU020570010 5/30/2003 12:00:00 AM -1 TJU240570017 6/3/2003 12:00:00 AM -1 TJU240570018 5/29/2003 12:00:00 AM -4 TJU020570018 5/31/2003 12:00:00 AM -3 TJU020570018 5/28/2003 12:00:00 AM -2 TJU020570018 5/30/2003 12:00:00 AM -2 TJU020570018 6/3/2003 12:00:00 AM -2 TJU020570018 6/2/2003 12:00:00 AM -2 TJU020570018 6/1/2003 12:00:00 AM -1 TJU020570018 5/30/2003 12:00:00 AM -6 TJU240570018 6/1/2003 12:00:00 AM -6 TJU240570018 6/2/2003 12:00:00 AM -5 TJU240570018 6/3/2003 12:00:00 AM -3 TJU240570018 5/31/2003 12:00:00 AM -2 TJU240570018 5/28/2003 12:00:00 AM -1 TJU240570018 6/9/2003 12:00:00 AM -4 TJU310570019 6/2/2003 12:00:00 AM -3 TJU240570019 5/28/2003 12:00:00 AM -1 TJU240570019 6/9/2003 12:00:00 AM -1 TJU310570020 6/3/2003 12:00:00 AM -2 TJU020570020 5/31/2003 12:00:00 AM -1 TJU020570020 6/2/2003 12:00:00 AM -1 TJU020570020 6/1/2003 12:00:00 AM -1 TJU24And this should be the resultItem trans_date sum( Quantity ) Location0570004 5/29/2003 12:00:00 AM -1 TJU020570004 6/3/2003 12:00:00 AM -1 TJU240570004 6/9/2003 12:00:00 AM -1 TJU310570006 5/28/2003 12:00:00 AM -1 TJU240570010 6/3/2003 12:00:00 AM -1 TJU020570010 5/30/2003 12:00:00 AM -1 TJU240570017 6/3/2003 12:00:00
more on Oracle/PeopleSoft
From SearchDatabase.com today Despite Craig Conway's indignation over Larry Ellison's $5.1 billion hostile takeover bid, the PeopleSoft chief executive officer had approached Oracle just one year ago to discuss a possible deal between the two firms. The two companies went as far as signing a nondisclosure agreement on June 5, 2002, and had introductory discussions about a combination that lasted two days, according to paperwork Oracle filed with the Securities and Exchange Commission on Monday. For the full details, click: http://www.sfgate.com/cgi-bin/article.cgi?file=/chronicle/archive/2003/06/11/BU288684.DTLtype=business Sounds like a policical campaign... Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: wait event puzzler
db file sequential read means single block read, which can be either from an index or from a table. Likewise, db file scattered read means multiblock read, which again can be either from a table or from an index. At 10:01 AM 6/11/2003 -0800, you wrote: Good day all, We have a bunch of queries in our data warehouse environment that are taking a long time to complete. I did a select on v$session_wait on one of the queries when it is running and it shows the wait event to be 'db file sequential read', which means it is scanning index blocks, correct? But when I put the p1 p2 p3 values into another query to see which segment it belongs to, it shows me a table, not an index. How can this be? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: uuencode and mail
Just append the file to the bottom of the message you want to send. S...: echo This is my message about this report testmsg uuencode somefile.csv somefile.csv testmsg mail -s Candygram! [EMAIL PROTECTED] testmsg Something like that. Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Saira Somani Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Subject: OT: uuencode and mail Hi all, Platform: AIX 4.3.3 I realize this off topic but it is somewhat related. Each morning a cron job creates an SQL*Plus output in csv format which gets e-mailed out as an attachment: $ uuencode somefile.csv somefile.csv |mail -s Here is your file [EMAIL PROTECTED] I still want to continue receiving the attachment but I cannot figure out how to write an e-mail message in the body of that e-mail which gives an explanation of what the report is and contains a disclaimer type clause. If I do this: uuencode somefile.csv somefile.csv |mail -s TEST [EMAIL PROTECTED] testmsg I only receive what is in testmsg as the body of the e-mail and not the csv attachment. If anyone can contribute to a solution, I'd be grateful. Thanks, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Problems Solaris Vs Windows
Do u analyze both sets of tables on the different platforms at the same regular intervals? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish BajajSent: Wednesday, June 11, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj
RE: RE: Rule Based Optimizer
RBO was the optimizer as far back as I can remember and that was V3. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 2:10 PM To: Multiple recipients of list ORACLE-L did oracle always use the RBO? or did it have some extremely simplified optimizer prior to 6.0? i know some of you haev been around a while... hopefully this doesnt make you feel old. no real need to know. just curious how the technology has progressed over time. From: Mercadante, Thomas F [EMAIL PROTECTED] Date: 2003/06/11 Wed PM 01:25:07 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer well beat me with a stick and call me black and blue. of *course* I meant RBO! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 12:30 PM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next version of the database. I'd like to ask those folks a simple question. What is the future of the Rule Based Optimizer looking like? Does it die in 10i?? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the
RE: Query on Dual
http://www.ixora.com.au/scripts/cache.htm replace_dual.sql In some cases the data block for SYS.DUAL can be a hot block in cache. This script replaces the SYS.DUAL table with a view onto X$DUAL, thereby eliminating the cache access entirely. Please note: This is not a normal, supported procedure. So do not do it on a production system, unless you have discussed the matter fully with Oracle Support beforehand. -Original Message- Can anyone tell me the performance issues related to the excessive usage of the DUAL table in pl/sql procedures? I remember reading an article about this ( I dont remember if it was in ASKTOM site ) wherein it was told that excessive usage of DUAL comes with a cost and there was a suggestion in that to create a view on the system table X$DUAL.. If anybody has a link to that please send it to me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
[Q] RMAN and backup tool?
We are thinking about use RMAN to backup our databases. The database is ORACLE 9ir2. My questions are: 1. due to we will use Veritas netbackup with RMAN, but UNIX server is Netbackup client (server is win 2K). Can RMAN understand and control the remote tape driver? 2. any other software I need to make RMAN and Veritras (client) work? Thanks. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mike mon INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to put a TOP 1 in a select
Title: Message That's always going to happen with something like a rank (or min/max). You'll need to add some additional criteria to tell Oracle which of the multiple records that you want to see returned. And that will depend on the business needs. For example, you might want the earlier date, so you could make the top line select a.item, a.location, min(a.trans_date), b.can_vta and add the appropriate group by. Or you may want the latest date - only you can decide that. HTH, Carol Bristow DPRA Inc. 1300 N 17th St Suite 950 Rosslyn, VA 22209 Work: 703-841-8025 Fax: 703-524-9415 -Original Message-From: Teresita Castro [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 2:50 PMTo: Multiple recipients of list ORACLE-LSubject: RE: How to put a TOP 1 in a select I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a INNER JOIN ( select item, location, sum(quantity) as can_vta from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 05700185/29/2003 12:00:00 AMTJU02-405700185/31/2003 12:00:00 AMTJU02-305700185/30/2003 12:00:00 AMTJU02-205700186/2/2003 12:00:00 AM TJU02-205700186/3/2003 12:00:00 AMTJU02-205700186/1/2003 12:00:00 AM TJU02-105700185/30/2003 12:00:00 AMTJU24-605700186/1/2003 12:00:00 AM TJU24-605700186/2/2003 12:00:00 AM TJU24-505700186/3/2003 12:00:00 AM TJU24-305700185/31/2003 12:00:00 AMTJU24-205700186/9/2003 12:00:00 AM TJU31-4 Results: Item Date Location Quantity == 05700185/29/2003 12:00:00 AM TJU02 -14 05700186/1/2003 12:00:00 AM TJU24 -22 05700185/30/2003 12:00:00 AM TJU24 -22 05700186/9/2003 12:00:00 AMTJU31 -4 The problem is that when we have an item that sold two or more days the same quantity and it is the biggestquantityit returns more that one row per item. This query is execute in 28 seconds [EMAIL PROTECTED] 06/10/03 09:24PM There may be different ways to write this query - otherwise you need tolook at tuning this query. Four minutes for a single product in a 5million row table doesn't sound really good but I guess it depends onhardware.Have a look at the explain plan for the query - you have a lot of selectioncriteria on the ICTRANS table. If one (or a group) of those criteria isvery restrictive then index (and analyze) that column(s) to see theperformance gain.I've had a go at writing this as an analytical query. The syntax may beincorrect since I don't have anything convenient to test it against. Theremay even be syntax errors - it was simply typed in without being executed.Good luck!!!select a.item, a.location, a.trans_date, sum(b.quantity) from (select item, location, trans_date rank() over (partition by item, location order by quantity desc)the_rankfrom ictranswhere company = 2000 and trans_date between (current_date-14) andcurrent_dateand doc_type = 'IS' and reason_code = 'VTCL'and substr(item,2,2) = '57') a,ictrans bwhere a.the_rank = 1and a.item = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) = '57') "Teresita Castro" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] martmx.com cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] om 11/06/2003 11:59 Please respond to ORACLE-L This is an example of the information.I forgot to mention that in each company we have supermarkets called in thesystem Locations. So when I do this query I have to return per item thetotal of sales in the las two week, the day that we sale more persupermarketItem trans_date Quantity Location0570004 5/29/2003 12:00:00 AM -1 TJU020570004 6/3/2003 12:00:00 AM -1 TJU240570004 6/9/2003 12:00:00 AM -1 TJU310570006 5/28/2003 12:00:00 AM -1 TJU240570010 6/3/2003 12:00:00 AM -1 TJU020570010 5/30/2003 12:00:00 AM -1 TJU240570017 6/3/2003 12:00:00 AM -1 TJU240570018 5/29/2003 12:00:00 AM -4 TJU020570018 5/31/2003 12:00:00 AM -3 TJU020570018
Preparing for Performance test
Tru64 5.1A 8.1.7.3 I have a very short window of time tomorrow to try and determine why the runtime of one 2-table join takes ~4 time as long on a (SAN/Snapshot) database copy as the same statement on the source database. All other test selects have similar runtimes on each database. Both database are using copies of the same init.ora file with only directory specific parameters changed, CBO statistics are, of course, the same and one of these 2 database are the only thing running on the box during test time. I plan to run the SQL test scripts with event 10046 lvl 8 set. Should I have a look at anything else during the test to try and shed more light on this discrepency. Thanks, Mike Hand Polaroid Corp. This transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: uuencode and mail
Hey that worked. Thanks! Saira -Original Message- Matthew Zito Sent: June 11, 2003 3:00 PM To: Multiple recipients of list ORACLE-L Just append the file to the bottom of the message you want to send. S...: echo This is my message about this report testmsg uuencode somefile.csv somefile.csv testmsg mail -s Candygram! [EMAIL PROTECTED] testmsg Something like that. Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Saira Somani Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Subject: OT: uuencode and mail Hi all, Platform: AIX 4.3.3 I realize this off topic but it is somewhat related. Each morning a cron job creates an SQL*Plus output in csv format which gets e-mailed out as an attachment: $ uuencode somefile.csv somefile.csv |mail -s Here is your file [EMAIL PROTECTED] I still want to continue receiving the attachment but I cannot figure out how to write an e-mail message in the body of that e-mail which gives an explanation of what the report is and contains a disclaimer type clause. If I do this: uuencode somefile.csv somefile.csv |mail -s TEST [EMAIL PROTECTED] testmsg I only receive what is in testmsg as the body of the e-mail and not the csv attachment. If anyone can contribute to a solution, I'd be grateful. Thanks, Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Preparing for Performance test
Title: RE: Preparing for Performance test after you are done wint 10046^8, you may want to try with event 10053^1 ... and if you are looking for information to interpret the output look no further than http://www.centrexcc.com/papers.html Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Hand, Michael T [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 11, 2003 3:26 PM To: Multiple recipients of list ORACLE-L Subject: Preparing for Performance test Tru64 5.1A 8.1.7.3 I have a very short window of time tomorrow to try and determine why the runtime of one 2-table join takes ~4 time as long on a (SAN/Snapshot) database copy as the same statement on the source database. All other test selects have similar runtimes on each database. Both database are using copies of the same init.ora file with only directory specific parameters changed, CBO statistics are, of course, the same and one of these 2 database are the only thing running on the box during test time. I plan to run the SQL test scripts with event 10046 lvl 8 set. Should I have a look at anything else during the test to try and shed more light on this discrepency. Thanks, Mike Hand Polaroid Corp. This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: more on Oracle/PeopleSoft
This is getting to be entertaining. I started re-reading The New Art of War by Sun Bin last night... Patrice. -Original Message- Sent: Wednesday, June 11, 2003 3:55 PM To: Multiple recipients of list ORACLE-L From SearchDatabase.com today Despite Craig Conway's indignation over Larry Ellison's $5.1 billion hostile takeover bid, the PeopleSoft chief executive officer had approached Oracle just one year ago to discuss a possible deal between the two firms. The two companies went as far as signing a nondisclosure agreement on June 5, 2002, and had introductory discussions about a combination that lasted two days, according to paperwork Oracle filed with the Securities and Exchange Commission on Monday. For the full details, click: http://www.sfgate.com/cgi-bin/article.cgi?file=/chronicle/archive/2003/06/11 /BU288684.DTLtype=business Sounds like a policical campaign... Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Performance Problems Solaris Vs Windows
Do you have anything set in the configuration that would (hints, multiblock read count...) that would favor a different execution path? -Original Message-From: Ishwar Tewari [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 11, 2003 3:00 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Performance Problems Solaris Vs Windows Do u analyze both sets of tables on the different platforms at the same regular intervals? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish BajajSent: Wednesday, June 11, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj
Is this thing on?
Title: Is this thing on? Testing... Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax: 954-935-3639 Cell: 954-683-4459 "The sender believes that this E-Mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking proactive and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments." -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: selectivity of predicates with LIKE - diff between 8i and
So far I have not observed a difference in selectivity of predicates between 8i and 9i. Is there something special about the table or index? I tried to create a mock table with your statistics and the 9i optimizer came up with the same cardinality and cost estimates as 8i. Can you post (or e-mail me) more of the 10053 trace? In 9i the optimizer used a FF of 3.4877e-02 (see IXSEL or TBSEL in the 10053 trace) giving the high cardinality estimate : 241286 * 3.4877e-02 = 8414.85, rounded to 8415. And the explanation of the cost of 39 is as follows: It is an index-only access path, so the cost is LVLS + FF * #LB = 2 + 8414.85 * 1035 = 2 + 36.0977, rounded to 39. At 09:21 AM 6/11/2003 -0800, you wrote: Dear List, Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO? We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS. After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i: [EMAIL PROTECTED] @target COUNT(1) -- 291 [EMAIL PROTECTED] l 1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%' -- 8i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- - 0 3 1 SELECT STATEMENT (choose) Cost (3,1,20) 10 1 SORT (aggregate) 21 3 2 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40) -- 9i: [EMAIL PROTECTED] @explain8 Id ParCSTCDN Plan -- -- --- 0 39 1 SELECT STATEMENT (choose) Cost (39,1,19) 10 1 SORT (aggregate) 21 39 8415 INDEX (analyzed) NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885) -- 8i: Access path: index (index-only) INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE (obj_id=307169 - DIS_IND_ALBUM_TITRE_1) CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06 ... BEST_CST: 3.00 PATH: 4 Degree: 1 -- 9i: Access path: index (index-only) Index: DIS_IND_ALBUM_TITRE_1 TABLE: DIS_TAB_ALBUM_TITRE RSC_CPU: 0 RSC_IO: 39 IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02 ... BEST_CST: 39.00 PATH: 4 Degree: 1 In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below): INDEX#: 307169 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 161254NULLS: 0 DENS: 6.2014e-06 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 - 3 But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39: INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3 TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table: DIS_TAB_ALBUM_TITRE Alias: ALT NDV: 157906NULLS: 0 DENS: 6.3329e-06 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions: 1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39? 2) Is there a simple way to get it back on track to CST=2 without hints or stored outlines - some spfile parameter would be ideal? 3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291). Would histograms be the right way to get CMPTD CDN closer to the reality in this case? Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M) Thanks for any help, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
Re: Rule Based Optimizer
Dick I personally think the CBO is a great optimizer even if the first versions 7.0.x,7.1.x, and 7.2 was a bit shaky When somebody say that the CBO has to get better/mature then they typically have worked with the RBO and forgot that humans has this fantastic skill of adapting to known behaviour. That is we unconscious worked as preprocessors to the RBO, by using small tricks like rearranging the from clause, adding a empty sting to the left side of a join, rearranging the order of the predicates in the where clause. Since the CBO is able to take into account so many new things (hash, direct read for sort, parallel ...) when creating a plan for a statement and most of the time do et descent job. When the CBO really F...up a simple plan for at statement we humans get annoyed and surprised over how stupid the CBO is. My experience is that if you take a 1000 Sql statement the and feed them through the CBO and the RBO (with out preprocessing) the CBO will always win or you take a 1000 Sql statement and preprocess then for the RBO and preprocess then via hints and statistics for the CBO the CBO will win :-) That my to cent's d, Dick wrote: Same here. Hopefully 10i's CBO has matured. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 1:16 PM To: Multiple recipients of list ORACLE-L I know that the CBO existed on Oracle 7.0.15 for HP-UX. I remember it well because we would frequently get incorrect and inconsistent results. Installing the 7.0.16 patch _mostly_ fixed these issues. We still had a large number of queries that we hinted to use the RBO as we couldn't get the desired query performance with the CBO Kevin -Original Message- Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Not true. IIRC the first Oracle version with dual optimizer mode was 7.1.x. Best Regards, K Gopalakrishnan -Original Message- Mercadante, Thomas F Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager) [EMAIL PROTECTED] .com To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Rule Based Optimizer ity.com 06/10/2003 12:59 PM Please respond to ORACLE-L RULE optimizer will not be supported in next version. But you can still use the RULE optimizer and it is just a matter of official support from Oracle. Best Regards, K Gopalakrishnan -Original Message- Goulet, Dick Sent: Tuesday, June 10, 2003 10:16 AM To: Multiple recipients of list ORACLE-L There appears to be a number of folks on the list who have some insight into the next
Re: Database Auditing
That's correct. The poster didn't specify which version. If it's 9i the options available are plenty. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:19 AM Beware that user SYS is not AUDITable in 8i... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 1:14 AM To: Multiple recipients of list ORACLE-L Subject: Re: Database Auditing Ravindra, I'm not sure I understand the question correctly. Statement level auditing? Are you referring to recording that a particular statement was executed on a specific table? If so, the plain vanilla AUDIT statement is for you. There is no need for any triggers to be set up. If you want more elaborate, which rows the user updated, etc. you have a challenge. In Oracle 8i, you could go to log miner to mine all statements that modified data. Thi swill capture all but the SELECT statements. In Oralce 9i, there is a new feature called Fine Grained Auditing that can capture the exact statement the user issued and can even audit on based on the predicate of the select query. If youwant some moreinformation on the FGA, let me know. If you want to capture the DDL changes, the best option is to use system level triggers. I am not sure what you mean by OS level auditing of statements. HTH. Arup Nanda Author of the forthcoming book HIPAA Security and Auditing for Oracle from Rampant TechPress (www.Rampant.cc) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 12:04 AM Hi All, How do we turn on statement level auditing (DB and OS Level) to audit the DML/DDL's on the database.Is this possible with out triggers? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: wait event puzzler
Yes, 'db file sequential read' is always a single block read. It is 'db file scattered read' which is = DB_FILE_MULTIBLOCK_READ_COUNT At 10:35 AM 6/11/2003 -0800, you wrote: A SINGLE block? I was convinced that it was = DB_FILE_MULTIBLOCK_READ_COUNT blocks. Are you sure? Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Dennis: THe event 'db file sequential read' is NOT always an INDEX Scan. It is just a single block read which TYPICALLY happens during an INDEX scan. The word 'TYPICALLY' is the key here. Best Regards, K Gopalakrishnan Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: selectivity of predicates with LIKE - diff between 8i and 9i
Aha, just come accross note 94051.1 explaining _LIKE_WITH_BIND_AS_EQUALITY... but it's probably not relevant in my case as my query doesn't make use of bind variables. And yes I tried it (at the session level) just for kicks with no effect on the execution plan Thanks anyway, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Named Pipes Protocol Adapter
I remember asking on the Metalink SQL*Net forum a couple of years ago why one would use Named Pipes vs. TCP/IP. The Oracle support person on Metalink answered Hardly anybody uses Named Pipes. You should use TCP/IP. -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] I think what is being referred to here is that named pipes can be set up as a protocol in the listener on Windoze. As to why one would want to do that, I don't have an answer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
Peter, OK, It seems you got my message wrong. Truly the CBO has made matters a whole lot better, especially for someone like Me who has a lot of semi educated users creating ad-hoc queries into the database. On the other hand, CBO has on occasion done some pretty dumb things that have not added up and taken way too long. At first I thought it was a statistics thing, like stale stats, but that has turned out false. If, as Oracle should, they abandon the RBO for CBO then it needs to be more consistent in making it's choices. I have a hard time trying to explain to duhvelopers why the CBO decided on an full table scan today when it used a unique index scan yesterday. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 3:38 PM To: [EMAIL PROTECTED]; Goulet, Dick Dick I personally think the CBO is a great optimizer even if the first versions 7.0.x,7.1.x, and 7.2 was a bit shaky When somebody say that the CBO has to get better/mature then they typically have worked with the RBO and forgot that humans has this fantastic skill of adapting to known behaviour. That is we unconscious worked as preprocessors to the RBO, by using small tricks like rearranging the from clause, adding a empty sting to the left side of a join, rearranging the order of the predicates in the where clause. Since the CBO is able to take into account so many new things (hash, direct read for sort, parallel ...) when creating a plan for a statement and most of the time do et descent job. When the CBO really F...up a simple plan for at statement we humans get annoyed and surprised over how stupid the CBO is. My experience is that if you take a 1000 Sql statement the and feed them through the CBO and the RBO (with out preprocessing) the CBO will always win or you take a 1000 Sql statement and preprocess then for the RBO and preprocess then via hints and statistics for the CBO the CBO will win :-) That my to cent's d, Dick wrote: Same here. Hopefully 10i's CBO has matured. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 1:16 PM To: Multiple recipients of list ORACLE-L I know that the CBO existed on Oracle 7.0.15 for HP-UX. I remember it well because we would frequently get incorrect and inconsistent results. Installing the 7.0.16 patch _mostly_ fixed these issues. We still had a large number of queries that we hinted to use the RBO as we couldn't get the desired query performance with the CBO Kevin -Original Message- Sent: Wednesday, June 11, 2003 12:55 PM To: Multiple recipients of list ORACLE-L Not true. IIRC the first Oracle version with dual optimizer mode was 7.1.x. Best Regards, K Gopalakrishnan -Original Message- Mercadante, Thomas F Sent: Wednesday, June 11, 2003 9:30 AM To: Multiple recipients of list ORACLE-L CBO was what I learned on in late version of 5.x and 6.0. It was what Oracle taught developers back then - with the chart showing the precedence of how indexes were selected for a spcific query. And how the order of the tables in the FROM clause was *very* important in telling the CBO how to operate. Of course, that was when the developers had to know everything about the data within all the tables to get things to work right. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 11, 2003 11:50 AM To: Multiple recipients of list ORACLE-L I remember CBO in 7.1.6 on AIX... RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 6:45 PM I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) Jared Ryan [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/10/2003 04:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Rule Based Optimizer there not really quitting cold turkey its been phased out. wasnt the CBO introduced in 1997? Or was it earlier than that. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 6:44 PM RWB, I'll tell you, if KG says it about Oracle, I pretty much would go to the bank with it. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/10/2003 4:24 PM Mr. Gopalakrishnan: How do you know this? Can anyone confirm (or deny) this? I hope it is true! Oracle should just quit cold turkey and not support the RULE optimizer at all in any future releases. RWB Reginald W. Bailey IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA 713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)
Re: World premier performance of the BAARF party logo
Our storage team won't even respond to me anymore when I ask for the manufacturer's rating for non-cached I/Os per second number of controllers, RAID level, striping, etc... All I get is 'why do you want to know that' and 'what application is this for'... BARRF will stop the debilitating headaches since I will just refuse to talk about it anymore. Happy Day!! Mogens Nørgaard [EMAIL PROTECTED] T To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: bcc: Subject: World 06/10/03 06:19 PMpremier performance of the BAARF party logo Please respond to ORACLE-L Friends, James Morle has done it again. Nobody does it better. For the first public showing of his BAARF animation, please GoTo www.MiracleAS.dk . Then get back to your work or mailing list fast :-). Let me know if you want to become a BAARF party member, and I'll assign you a BAARF party membership number right away. You can reach Bold Membership Status if you can argue that you've been fighting RAID-F for a long time, a medium time, a short time or an extremely short time. Best regards, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: question about DynaLoader::dl_load_file from perl profiler output
Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then dprofpp -u to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is DynaLoader::dl_load_file corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: World premier performance of the BAARF party logo
I quit talking about RAID over a year ago. I just put the data on the disks the Unix folks present to the server. If there are performance problems that we can identify to the storage system then we pass them along for an explanation. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 11, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Our storage team won't even respond to me anymore when I ask for the manufacturer's rating for non-cached I/Os per second number of controllers, RAID level, striping, etc... All I get is 'why do you want to know that' and 'what application is this for'... BARRF will stop the debilitating headaches since I will just refuse to talk about it anymore. Happy Day!! Mogens Nørgaard [EMAIL PROTECTED] T To: Multiple recipients of list ORACLE-L Sent by: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: bcc: Subject: World 06/10/03 06:19 PMpremier performance of the BAARF party logo Please respond to ORACLE-L Friends, James Morle has done it again. Nobody does it better. For the first public showing of his BAARF animation, please GoTo www.MiracleAS.dk . Then get back to your work or mailing list fast :-). Let me know if you want to become a BAARF party member, and I'll assign you a BAARF party membership number right away. You can reach Bold Membership Status if you can argue that you've been fighting RAID-F for a long time, a medium time, a short time or an extremely short time. Best regards, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
high cpu
OS :hp-ux oracle rdbms 8.1.7.4 there are certain processes taking lot of cpu ( some thing went wrong with stastics last nite). which is causing almost a halt in production database.these processes are killed from oracle but i can still see them on OS .Cpu is still high . I though after killing them mpon might be doing some clean up work .cpu is still high . question : If I recycle this instance ,is it going to take long time to come back . ( smon will do the job now ?) or is "fast warm start" will help here to bring db up quickly? thanks, -Ak
RE: question about DynaLoader::dl_load_file from perl profiler output
IIRC, DynaLoader::dl_load_file looks for and physically loads an object that the perl program is dynamically linked against or calling in some fashion. Then the dl_ calls you see at the bottom handle symbol resolution, etc. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gmei Sent: Wednesday, June 11, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Subject: OT: question about DynaLoader::dl_load_file from perl profiler output Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then dprofpp -u to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is DynaLoader::dl_load_file corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
java.sql.SQLException: ORA-21243896
DBA's, Has anyone ever seen this: 15:37:19 06/11 [DBUG] [ConnectionPoolC] SvcsConnectionFinished Reading properties horizon 15:37:20 06/11 [EROR] [ConnectionPoolC] SvcsConnection SQLException occured while while creating pool for horizon 15:37:20 06/11 [EROR] [ConnectionPoolC] SvcsConnection java.sql.SQLException: ORA-21243896: Message 21243896 not found; product=RDBMS; facility=ORA IRIX64 Error: 67108868: Unknown system error This is from the connection pool log in BEA, it show up when re-deploying the application from BEA??? TAI ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: high cpu
Rebounce the database... After shutting down database , check for any oracle process is still alive by using ipcs -mb If it is still their kill those process by yourself (using ipcem -m ) or by Unix admin. Sometime it does not go and then you have to reboot. If shutdown immediate takes longer then use shutdown abort. Recovery will not take longer at startup. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 11 Jun 2003 14:04:21 -0800 OS :hp-ux oracle rdbms 8.1.7.4 there are certain processes taking lot of cpu ( some thing went wrong with stastics last nite). which is causing almost a halt in production database.these processes are killed from oracle but i can still see them on OS .Cpu is still high . I though after killing them mpon might be doing some clean up work .cpu is still high . question : If I recycle this instance ,is it going to take long time to come back . ( smon will do the job now ?) or is fast warm start will help here to bring db up quickly? thanks, -Ak _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: high cpu
Ak - Were the processes you killed doing updates? If so the work is probably rolling back changes. Then it won't make much difference whether the process rolls it back or smon does. You could take a look at the wait statistics to get an idea of what is going on. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 5:04 PM To: Multiple recipients of list ORACLE-L OS :hp-ux oracle rdbms 8.1.7.4 there are certain processes taking lot of cpu ( some thing went wrong with stastics last nite). which is causing almost a halt in production database.these processes are killed from oracle but i can still see them on OS .Cpu is still high . I though after killing them mpon might be doing some clean up work .cpu is still high . question : If I recycle this instance ,is it going to take long time to come back . ( smon will do the job now ?) or is fast warm start will help here to bring db up quickly? thanks, -Ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RMAN FILESPERSET
I'm a bit confused by the default for filesperset. I ran the following yesterday
run
{
allocate channel c1 device type sbt format 'df_%t_%s_%p' maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
backup database;
backup current controlfile;
release channel c1;
}
There were 245 datafiles in the database. RMAN made the following backup sets
BS_KEY COUNT(B.FILE#)
-- --
9623 63
9727 64
9810 64
9892 35
9893 4
9894 4
9895 6
9896 5
--
sum 245
As I understand it the default for filesperset is the lesser of 64 or the number of
input files / the number of channels.
As there was only one channel I would have expected 3 backup sets of 64 files and one
of 53 channels.
Today I ran against the same target database
run
{
allocate channel c1 device type sbt format 'df_%t_%s_%p' maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
allocate channel c2 device type sbt format 'df_%t_%s_%p' maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
backup database skip readonly;
backup current controlfile;
release channel c1;
release channel c2;
}
There are 92 read write datafiles. I would have expected the job to be divided into
two backup sets with 46 files each.
Instead I got
BS_KEY COUNT(B.FILE#)
-- --
10704 2
10705 2
10721 2
10722 2
10723 2
10724 2
10725 2
10726 2
10727 2
10728 2
10765 33
10766 4
10767 2
10768 1
10769 1
10770 1
10771 1
10772 1
10773 1
10774 1
10775 1
10776 1
10777 1
10778 1
10779 1
10780 2
10781 1
10782 18
--
sum92
-
Any guesses as to why so many backup sets are being created.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: high cpu
I don't think they were updates . buth they were long queries .which went crazy due to some statistics changes last nite. I did got one temp tablespace shortage error as well. -ak - Original Message - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 2:25 PM Ak - Were the processes you killed doing updates? If so the work is probably rolling back changes. Then it won't make much difference whether the process rolls it back or smon does. You could take a look at the wait statistics to get an idea of what is going on. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- From: AK [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:04 PM To: Multiple recipients of list ORACLE-L Subject: high cpu OS :hp-ux oracle rdbms 8.1.7.4 there are certain processes taking lot of cpu ( some thing went wrong with stastics last nite). which is causing almost a halt in production database.these processes are killed from oracle but i can still see them on OS .Cpu is still high . I though after killing them mpon might be doing some clean up work .cpu is still high . question : If I recycle this instance ,is it going to take long time to come back . ( smon will do the job now ?) or is fast warm start will help here to bring db up quickly? thanks, -Ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
applying transactions
I've got a project where I get daily log files with inserts and deletes to keep a table current. I've set up an external table which contains the logs and a stored procedure reads from it and inserts or deletes from the table accordingly. Note one insert or delete per iteration. They're not bulked. The problem is it is running way too slowly. I'm running about 300 transactions a second and believe the slow time has to do with context switching. Merge won't work because it can't handle a record being changed multiple times in the transaction log/external table. When I run inserts only I'm inserting about 5000 rows a second, but understand the deletes would slow it down considerably. Keep in mind all the records have to be executed sequentially because we're just applying a log file. Right now I'm trying to figure out a scheme to perform all the inserts that don't exist in the destination table, then all deletes, and then the remaining inserts but thought I should just send an email to see if someone had a better way of getting me the transaction rate I need, about 1000 rec/s. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to put a TOP 1 in a select
That's a good approach to dealing with duplicates - wrap it up in another query with a group by clause to remove the duplicates. It looks like we've got your query down from never to around 30 seconds. Also since the analytical function is doing a window sort and you will then be doing another sort to remove duplicates the sort_area_size will have an impact on performance - althought 30 seconds is probably good enough to not worry about fiddling with this. Just out of curiousity - did my query also return the duplicates? To be honest, I'm not sure if RANK returns the same value for duplicates or not. I guess that's something I should look into although I barely use this type of query. Regards, Mark. Carol Bristow [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ra.com cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] .com 12/06/2003 05:25 Please respond to ORACLE-L That's always going to happen with something like a rank (or min/max). You'll need to add some additional criteria to tell Oracle which of the multiple records that you want to see returned. And that will depend on the business needs. For example, you might want the earlier date, so you could make the top line select a.item, a.location, min(a.trans_date), b.can_vta and add the appropriate group by. Or you may want the latest date - only you can decide that. HTH, Carol Bristow DPRA Inc. 1300 N 17th St Suite 950 Rosslyn, VA 22209 Work: 703-841-8025 Fax: 703-524-9415 -Original Message- Sent: Wednesday, June 11, 2003 2:50 PM To: Multiple recipients of list ORACLE-L I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a INNER JOIN ( select item, location, sum(quantity) as can_vta from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 0570018 5/29/2003 12:00:00 AM TJU02 -4 0570018 5/31/2003 12:00:00 AM TJU02 -3 0570018 5/30/2003 12:00:00 AM TJU02 -2 0570018 6/2/2003 12:00:00 AM TJU02 -2 0570018 6/3/2003 12:00:00 AM TJU02 -2 0570018 6/1/2003 12:00:00 AM TJU02 -1 0570018 5/30/2003 12:00:00 AM TJU24 -6 0570018 6/1/2003 12:00:00 AM TJU24 -6 0570018 6/2/2003 12:00:00 AM TJU24 -5 0570018 6/3/2003 12:00:00 AM TJU24 -3 0570018 5/31/2003 12:00:00 AM TJU24 -2 0570018 6/9/2003 12:00:00 AM TJU31 -4 Results: Item Date Location Quantity == 05700185/29/2003 12:00:00 AM TJU02 -14 0570018 6/1/2003 12:00:00 AM
RE: question about DynaLoader::dl_load_file from perl profiler output
Does this mean that there is nothing I can do for that part in terms of code optimization? My objective is to find which part the perl code I could try to optimize for speed. Guang On Wed, 11 Jun 2003, Matthew Zito wrote: IIRC, DynaLoader::dl_load_file looks for and physically loads an object that the perl program is dynamically linked against or calling in some fashion. Then the dl_ calls you see at the bottom handle symbol resolution, etc. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gmei Sent: Wednesday, June 11, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Subject: OT: question about DynaLoader::dl_load_file from perl profiler output Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then dprofpp -u to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is DynaLoader::dl_load_file corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: question about DynaLoader::dl_load_file from perl profiler output
First off, you really need to join a Perl and/or DBI users list, found at lists.perl.org. DynaLoader is used to load modules at runtime. It's part of Perl, you don't need to be too concerned with it unless it's taking a lot of time. Jared gmei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 02:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:OT: question about DynaLoader::dl_load_file from perl profiler output Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then dprofpp -u to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is DynaLoader::dl_load_file corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Urgent: Need Oradim.exe for version 8.1.6
Here si oradim for 8.1..6.3 if you still need it. Jared Naveen Nahata [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 07:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Urgent: Need Oradim.exe for version 8.1.6 I need the Oradim.exe for version 8.1.6 as its got corrupted. Can someone please zip it and send it to me? Regards Naveen -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: sql query optimization Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or
RE: question about DynaLoader::dl_load_file from perl profiler output
Sure you could link Perl statically. Probably not a good idea. I suggest you move this to Perl mailing list. Jared Guang Mei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 04:44 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: question about DynaLoader::dl_load_file from perl profiler output Does this mean that there is nothing I can do for that part in terms of code optimization? My objective is to find which part the perl code I could try to optimize for speed. Guang On Wed, 11 Jun 2003, Matthew Zito wrote: IIRC, DynaLoader::dl_load_file looks for and physically loads an object that the perl program is dynamically linked against or calling in some fashion. Then the dl_ calls you see at the bottom handle symbol resolution, etc. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gmei Sent: Wednesday, June 11, 2003 4:40 PM To: Multiple recipients of list ORACLE-L Subject: OT: question about DynaLoader::dl_load_file from perl profiler output Hi: I am using this command to profile a perl program on our unix box /tools/bin/perl -d:DProf my_program.pl. and then dprofpp -u to get the following: Total Elapsed Time = 0.477847 Seconds User Time = 0.319582 Seconds Exclusive Times %Time ExclSec CumulS #Calls sec/call Csec/c Name 49.7 0.159 0.295 6 0.0265 0.0491 main::BEGIN 12.5 0.040 0.040 2 0.0200 0.0200 DynaLoader::dl_load_file 6.26 0.020 0.020 1 0.0200 0.0200 DBD::Oracle::db::_login 6.26 0.020 0.020 8 0.0025 0.0025 Exporter::export 3.13 0.010 0.010 1 0.0100 0.0100 AutoLoader::AUTOLOAD 3.13 0.010 0.010 94 0.0001 0.0001 DBI::_install_method 3.13 0.010 0.017 46 0.0002 0.0004 DBI::db::prepare 3.13 0.010 0.020 3 0.0033 0.0066 File::Spec::Unix::BEGIN 3.13 0.010 0.009 48 0.0002 0.0002 DBI::_new_handle 3.13 0.010 0.050 1 0.0100 0.0498 DBI::install_driver 3.13 0.010 0.099 1 0.0099 0.0989 obsData::BEGIN 0.00 0.000 -0.000 2 0. - DynaLoader::dl_load_flags 0.00 0.000 -0.000 2 0. - DynaLoader::dl_undef_symbols 0.00 0.000 -0.000 2 0. - DynaLoader::dl_find_symbol 0.00 0.000 -0.000 2 0. - DynaLoader::dl_install_xsub What is DynaLoader::dl_load_file corresponding to in my perl code? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: How to put a TOP 1 in a select
Yes,rank return duplicates, that why I have some items with two or more line per item. [EMAIL PROTECTED] 06/11/03 05:39PM That's a good approach to dealing with duplicates - wrap it up in anotherquery with a group by clause to remove the duplicates. It looks like we'vegot your query down from "never" to around 30 seconds. Also since theanalytical function is doing a window sort and you will then be doinganother sort to remove duplicates the sort_area_size will have an impact onperformance - althought 30 seconds is probably good enough to not worryabout fiddling with this.Just out of curiousity - did my query also return the duplicates? To behonest, I'm not sure if RANK returns the same value for duplicates or not.I guess that's something I should look into although I barely use this typeof query.Regards, Mark. "Carol Bristow" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ra.com cc: Sent by: Subject: RE: How to put a TOP 1 in a select [EMAIL PROTECTED] .com 12/06/2003 05:25 Please respond to ORACLE-L That's always going to happen with something like a rank (or min/max).You'll need to add some additional criteria to tell Oracle which of themultiple records that you want to see returned. And that will depend onthe business needs. For example, you might want the earlier date, so youcould make the top line select a.item, a.location, min(a.trans_date), b.can_vtaand add the appropriate group by. Or you may want the latest date - onlyyou can decide that.HTH,Carol BristowDPRA Inc.1300 N 17th St Suite 950Rosslyn, VA 22209Work: 703-841-8025Fax: 703-524-9415-Original Message-Sent: Wednesday, June 11, 2003 2:50 PMTo: Multiple recipients of list ORACLE-L I run the next instruction: select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date, rank() over (partition by item, location order by quantity) the_rank from ictrans a where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a INNER JOIN ( select item, location, sum(quantity) as can_vta from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1 Information: item Date Location Quantity === 0570018 5/29/2003 12:00:00 AM TJU02 -4 0570018 5/31/2003 12:00:00 AM TJU02 -3 0570018 5/30/2003 12:00:00 AM TJU02 -2 0570018 6/2/2003 12:00:00 AM TJU02 -2 0570018 6/3/2003 12:00:00 AM TJU02 -2 0570018 6/1/2003 12:00:00 AM TJU02 -1 0570018 5/30/2003 12:00:00 AM TJU24 -6 0570018 6/1/2003 12:00:00 AM TJU24 -6 0570018 6/2/2003 12:00:00 AM TJU24 -5 0570018 6/3/2003 12:00:00 AM TJU24 -3 0570018 5/31/2003 12:00:00 AM TJU24 -2 0570018 6/9/2003 12:00:00 AM TJU31 -4 Results: Item Date Location Quantity == 0570018 5/29/2003 12:00:00 AM TJU02 -14 0570018 6/1/2003 12:00:00 AM TJU24 -22 0570018 5/30/2003 12:00:00 AM TJU24 -22 0570018 6/9/2003 12:00:00 AM TJU31 -4 The problem is that when we have an item that sold two or more days the same quantity and it is the biggest quantity it returns more that one row per item. This query is execute in 28 seconds [EMAIL PROTECTED] 06/10/03 09:24PM There may be different ways to write this query - otherwise you need to look at tuning this query. Four minutes for a single product in a 5 million row table doesn't sound really good but I guess it depends on hardware. Have a look at the explain plan for the query - you have a lot of selection criteria on the ICTRANS table. If one (or a group) of those criteria is very restrictive then index (and analyze) that column(s) to see the performance gain. I've had a go at writing this as an analytical query. The syntax may be incorrect since I don't have anything convenient to test it against. There may even be syntax errors - it was simply typed in without being executed. Good luck!!! select a.item, a.location, a.trans_date, sum(b.quantity) from ( select item, location, trans_date rank() over (partition by item, location order by quantity desc) the_rank from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a, ictrans b where a.the_rank = 1 and a.item = b.item and a.location = b.location and b.company = 2000 and b.trans_date between (current_date-14) and current_date and b.doc_type = 'IS' and b.reason_code = 'VTCL' and substr(b.item,2,2) = '57') "Teresita Castro" [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] martmx.com cc: Sent by: Subject: RE: How to put a TOP 1 in a
Re: Urgent: Need Oradim.exe for version 8.1.6
Oops, here it is again, zipped this time. I realized after sending the last one your email may filter out exe files. And then I realized I sent it to the list instead of you. Jared Naveen Nahata [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 07:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Urgent: Need Oradim.exe for version 8.1.6 I need the Oradim.exe for version 8.1.6 as its got corrupted. Can someone please zip it and send it to me? Regards Naveen -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: sql query optimization Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this
Re: applying transactions
If you're on 8i+ you can use bulk loading. It could save you a lot of time on large loads such as this. Jared David Turner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 04:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:applying transactions I've got a project where I get daily log files with inserts and deletes to keep a table current. I've set up an external table which contains the logs and a stored procedure reads from it and inserts or deletes from the table accordingly. Note one insert or delete per iteration. They're not bulked. The problem is it is running way too slowly. I'm running about 300 transactions a second and believe the slow time has to do with context switching. Merge won't work because it can't handle a record being changed multiple times in the transaction log/external table. When I run inserts only I'm inserting about 5000 rows a second, but understand the deletes would slow it down considerably. Keep in mind all the records have to be executed sequentially because we're just applying a log file. Right now I'm trying to figure out a scheme to perform all the inserts that don't exist in the destination table, then all deletes, and then the remaining inserts but thought I should just send an email to see if someone had a better way of getting me the transaction rate I need, about 1000 rec/s. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Rule Based Optimizer
Much as I hate to say that Jared's confidence in Mogens is misplaced, we must all remember Mogens has now reached the grand old age of 42 and the brain cells are starting to slip. :) To be absolutely 100% picky and correct, there was an event setting introduced in (I believe) 6.0.27-ish that you could use to turn on the CBO. However, Mogens is correct in that it was first officially released and supported in the first Production release of Oracle7, 7.0.13. Sorry, Mogens, couldn't resist! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long term Oracle DBA. -Original Message- Mogens Nørgaard Sent: Tuesday, June 10, 2003 7:55 PM To: Multiple recipients of list ORACLE-L Thanks for the confidence, Jared. It was introduced in 7.0 and the world hasn't been the same since. Mogens Jacques Kilchoer wrote: I am 99% confident that I remember the CBO existing on VAX/VMS in 7.1.5, and for sure in 7.1.6 (on VAX/VMS) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I first saw the CBO in 1994 if IIRC. Seems to me it was introduced in 7.2. I'm sure Mogens knows for sure. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Urgent: Need Oradim.exe for version 8.1.6
Crud! Sorry, replied twice to the list when I shouldn't have. D'oh! Jared Naveen Nahata [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 07:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Urgent: Need Oradim.exe for version 8.1.6 I need the Oradim.exe for version 8.1.6 as its got corrupted. Can someone please zip it and send it to me? Regards Naveen -Original Message- From: Mark Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: sql query optimization Hi, From what you have said the cost of distinct and the function call shouldn't be a big deal. I did wonder if you can use to_number with an appropriate mask to avoid the function call but it's probably not even worth bothering. Simplifying the connect by sub-query will hopefully provide the boost you need. The concatenated index relates to my uncertainty about how Oracle can use them for recursive SQL. I did a simple test - creating the following indexes: 1) Unique index on child 2) Non-unique index on parent 3) Unique index on parent, child 4) Unique index on child, parent The table only had a handful of rows but Oracle chose to use index 1 and index 3 for the query instead of index 2. On a table of significant volume (I used to work on very large recursive SQL statements at one point) I would suggest testing the indexing combinations to see what Oracle likes - then remove the rest. Also, the requirements are different if you are traversing the tree in both directions - you seem to only be going down the tree. Good luck. Guang Mei [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: sql query optimization .com 11/06/2003 12:34 Please respond to ORACLE-L I just looked: [EMAIL PROTECTED] select count(*) from arc where arctype in (299,300); COUNT(*) -- 56932 This is about 27% of the total rows, so I will test to move them into a new table tomorrow and this should help. I did test each part separatley and timed them and I found that the sub-query is probably the bottle-neck because start ... connect by ... requires walk the whole index to get all possible nodes (expensive). I can create this new table. 2) Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying) I don't know why concatenated index would help here, for which part in where clause it would? Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any
Re: applying transactions
Thx Jared, But I don't see how I could use loader to perform a delete. Sad to say but this isn't the typical load that I'm used to.. The load files contain inserts and deletes. I could set up a temporary table to load into which has a trigger fire on delete to remove rows from the destination table and on an insert inserts the row into the destination. But, I would think this would be about as slow because the trigger would have to find each record for deleting. I also needed to add that each insert and delete was an entire record with a flag showing the transaction type (I= insert O=out/delete). I think I have a cool solution though. Here's an example : This is the table that contains the transactions create table test1(id number(4),trans_no number(4), trans char(1), field varchar2(8)); Here are some example transactions: insert into test1 values(1,101,'I','A'); insert into test1 values(2,102,'I','A'); insert into test1 values(3,103,'I','A'); insert into test1 values(4,104,'I','A'); insert into test1 values(5,105,'I','A'); insert into test1 values(1,106,'0','X'); insert into test1 values(2,107,'O','X'); insert into test1 values(3,108,'0','X'); insert into test1 values(4,109,'I','B'); insert into test1 values(5,110,'I','B'); insert into test1 values(5,115,'0','X'); insert into test1 values(4,114,'I','C'); insert into test1 values(3,113,'I','C'); insert into test1 values(2,112,'I','C'); insert into test1 values(1,111,'I','C'); Here is the select to get the last change performed on a row select a.id,a.trans_no, a.trans, a.field from test1 a, (select id,max(trans_no) trans_no from test1 group by id) b where a.trans_no=b.trans_no; Here would be the result: IDLINE_NO T FIELD -- -- - 1111 I C 2112 I C 3113 I C 4114 I C 5115 0 X I then merge this result set with the destination table. I haven't found any problems yet and I'm fairly certain I'll hit around 900 trans per sec. Thanks, Dave On Wed, Jun 11, 2003 at 04:43:32PM -0700, [EMAIL PROTECTED] wrote: If you're on 8i+ you can use bulk loading. It could save you a lot of time on large loads such as this. Jared David Turner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 04:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:applying transactions I've got a project where I get daily log files with inserts and deletes to keep a table current. I've set up an external table which contains the logs and a stored procedure reads from it and inserts or deletes from the table accordingly. Note one insert or delete per iteration. They're not bulked. The problem is it is running way too slowly. I'm running about 300 transactions a second and believe the slow time has to do with context switching. Merge won't work because it can't handle a record being changed multiple times in the transaction log/external table. When I run inserts only I'm inserting about 5000 rows a second, but understand the deletes would slow it down considerably. Keep in mind all the records have to be executed sequentially because we're just applying a log file. Right now I'm trying to figure out a scheme to perform all the inserts that don't exist in the destination table, then all deletes, and then the remaining inserts but thought I should just send an email to see if someone had a better way of getting me the transaction rate I need, about 1000 rec/s. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: applying transactions
Sorry, guess I wasn't clear. Bulk loading is done with PL/SQL, not sql loader. I should have referred to 'bulk binds'. Here are some URL's that may help. http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#37506 http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#20419 http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#28332 Jared David Turner [EMAIL PROTECTED] 06/11/2003 05:02 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: applying transactions Thx Jared, But I don't see how I could use loader to perform a delete. Sad to say but this isn't the typical load that I'm used to.. The load files contain inserts and deletes. I could set up a temporary table to load into which has a trigger fire on delete to remove rows from the destination table and on an insert inserts the row into the destination. But, I would think this would be about as slow because the trigger would have to find each record for deleting. I also needed to add that each insert and delete was an entire record with a flag showing the transaction type (I= insert O=out/delete). I think I have a cool solution though. Here's an example : This is the table that contains the transactions create table test1(id number(4),trans_no number(4), trans char(1), field varchar2(8)); Here are some example transactions: insert into test1 values(1,101,'I','A'); insert into test1 values(2,102,'I','A'); insert into test1 values(3,103,'I','A'); insert into test1 values(4,104,'I','A'); insert into test1 values(5,105,'I','A'); insert into test1 values(1,106,'0','X'); insert into test1 values(2,107,'O','X'); insert into test1 values(3,108,'0','X'); insert into test1 values(4,109,'I','B'); insert into test1 values(5,110,'I','B'); insert into test1 values(5,115,'0','X'); insert into test1 values(4,114,'I','C'); insert into test1 values(3,113,'I','C'); insert into test1 values(2,112,'I','C'); insert into test1 values(1,111,'I','C'); Here is the select to get the last change performed on a row select a.id,a.trans_no, a.trans, a.field from test1 a, (select id,max(trans_no) trans_no from test1 group by id) b where a.trans_no=b.trans_no; Here would be the result: IDLINE_NO T FIELD -- -- - 1111 I C 2112 I C 3113 I C 4114 I C 5115 0 X I then merge this result set with the destination table. I haven't found any problems yet and I'm fairly certain I'll hit around 900 trans per sec. Thanks, Dave On Wed, Jun 11, 2003 at 04:43:32PM -0700, [EMAIL PROTECTED] wrote: If you're on 8i+ you can use bulk loading. It could save you a lot of time on large loads such as this. Jared David Turner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 04:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:applying transactions I've got a project where I get daily log files with inserts and deletes to keep a table current. I've set up an external table which contains the logs and a stored procedure reads from it and inserts or deletes from the table accordingly. Note one insert or delete per iteration. They're not bulked. The problem is it is running way too slowly. I'm running about 300 transactions a second and believe the slow time has to do with context switching. Merge won't work because it can't handle a record being changed multiple times in the transaction log/external table. When I run inserts only I'm inserting about 5000 rows a second, but understand the deletes would slow it down considerably. Keep in mind all the records have to be executed sequentially because we're just applying a log file. Right now I'm trying to figure out a scheme to perform all the inserts that don't exist in the destination table, then all deletes, and then the remaining inserts but thought I should just send an email to see if someone had a better way of getting me the transaction rate I need, about 1000 rec/s. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official
Re: RMAN FILESPERSET
How did you join to get BS_KEY and FILE# together?
I don't believe the BS_KEY from a backup database relate directly to a
FILE# from the rc_views.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 10:25 AM
I'm a bit confused by the default for filesperset. I ran the following
yesterday
run
{
allocate channel c1 device type sbt format 'df_%t_%s_%p'
maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
backup database;
backup current controlfile;
release channel c1;
}
There were 245 datafiles in the database. RMAN made the following backup
sets
BS_KEY COUNT(B.FILE#)
-- --
9623 63
9727 64
9810 64
9892 35
9893 4
9894 4
9895 6
9896 5
--
sum 245
As I understand it the default for filesperset is the lesser of 64 or the
number of input files / the number of channels.
As there was only one channel I would have expected 3 backup sets of 64
files and one of 53 channels.
Today I ran against the same target database
run
{
allocate channel c1 device type sbt format 'df_%t_%s_%p'
maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
allocate channel c2 device type sbt format 'df_%t_%s_%p'
maxpiecesize=2048M
PARMS=SBT_LIBRARY=/opt/oracle/dbserver/9.0.1/lib/libobk.so,
ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt);
backup database skip readonly;
backup current controlfile;
release channel c1;
release channel c2;
}
There are 92 read write datafiles. I would have expected the job to be
divided into two backup sets with 46 files each.
Instead I got
BS_KEY COUNT(B.FILE#)
-- --
10704 2
10705 2
10721 2
10722 2
10723 2
10724 2
10725 2
10726 2
10727 2
10728 2
10765 33
10766 4
10767 2
10768 1
10769 1
10770 1
10771 1
10772 1
10773 1
10774 1
10775 1
10776 1
10777 1
10778 1
10779 1
10780 2
10781 1
10782 18
--
sum92
--
---
Any guesses as to why so many backup sets are being created.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Binley Lim
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: applying transactions
Depending on the volume of the changes it may be worthwhile to drop and then recreate indexes on the destination table. Indexes are going to slow the transaction rate down considerably. One way to partly achieve this goal would be to perform all inserts into a temporary table which has no indexes then use a single statement to copy the contents of this table into the main table. Applying deletes and updates (if any) could be more difficult though since a given record could be inserted, then deleted, and perhaps even re-inserted during a single log file. Dropping indexes on the main tablemay be easier to implement. The best approach probably depends on a few factors including: what is the size of the destination table, what is the size of the typical log file, what are the availability requirements of the table, can a single record appear multiple times in the one log file, etc. [EMAIL PROTECTED] ys.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Re: applying transactions .com 12/06/2003 10:45 Please respond to ORACLE-L If you're on 8i+ you can use bulk loading. It could save you a lot of time on large loads such as this. Jared David Turner [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/11/2003 04:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:applying transactions I've got a project where I get daily log files with inserts and deletes to keep a table current. I've set up an external table which contains the logs and a stored procedure reads from it and inserts or deletes from the table accordingly. Note one insert or delete per iteration. They're not bulked. The problem is it is running way too slowly. I'm running about 300 transactions a second and believe the slow time has to do with context switching. Merge won't work because it can't handle a record being changed multiple times in the transaction log/external table. When I run inserts only I'm inserting about 5000 rows a second, but understand the deletes would slow it down considerably. Keep in mind all the records have to be executed sequentially because we're just applying a log file. Right now I'm trying to figure out a scheme to perform all the inserts that don't exist in the destination table, then all deletes, and then the remaining inserts but thought I should just send an email to see if someone had a better way of getting me the transaction rate I need, about 1000 rec/s. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Turner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California--
