Re: dropping materialized view
Please try this select table_name, constraint_name from all_constraints where r_constraint_name in ( select constraint_name from all_constraints where table_name = 'MEDIUMS') it should give you the table_name constraint names that are not allowing you to drop your table. You will have to disable these constraints before dropping your table mediums. Mohammed Shakir --- Jeroen van Sluisdam [EMAIL PROTECTED] wrote: Hi, I have a serious problem in dropping a snapshot/mview I cannot find whatever constraint is blocking this. Any advice is appreciated SQL drop snapshot deca.mediums; drop snapshot deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL drop materialized view deca.mediums; drop materialized view deca.mediums * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL SELECT * FROM dba_constraints where table_name = 'MEDIUMS'; no rows selected I get the samen results when dropping as owner and as sys Details: Oracle 9.2.0.4 HP-UX11.11 Tnx, Jeroen = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: End of communication channel
Can you check on Metalink? Last time I had a similar problem, it was with export or import and there was a solution available on Metalink. Try it if you can. Best of luck --- Wartiak Rastislav [EMAIL PROTECTED] wrote: what is he doing? eg. when i start endless recursion in pl/sql, serving oracle process dies and end of comm channel occurs. you can check what happens with his process. can you see any core dumps? rw Hi list I'm looking for further hints on where to look for problems which may lead to an end of communication channel error. Any input (especially networking or operating system problems) appreciated. The server runs 9.0.1.0.1 on Win2k. Not my box, a coworker has trouble with the network/sys admins stating that it can't be a network/system problem. I highly doubt that, since the Oracle service and the listener are both constantly available. No memory problems either. It's just that in the middle of the session, the connection goes down the drain. Any ideas ? TIA, Stefan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wartiak Rastislav INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 9i - Dynamic SGA - SGA_MAX_SIZE
Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: 9i - Dynamic SGA - SGA_MAX_SIZE
I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti --- Hately, Mike (LogicaCMG) [EMAIL PROTECTED] wrote: Stephen, The documentation is pretty wooly regarding this issue but the way it seems to be intended to work is this: At startup Oracle will allocate an SGA sized as specified in the sga_max_size parameter. This is to ensure that the system has enough memory accomodate what you see as a maximum requirement for the SGA. After it's allocated this and started the database it should deallocate any memory it holds over and above that required to store the components of the SGA. In some platforms/versions this deallocation doesn't occur. Solaris for example behaves like this unless you move to version 8. It's possible that your version of Tru64 has a similar limitation or that you're seeing a bug. To my mind though, Oracle Support's claim that this is expected behaviour is a bit of a cop out. This is certainly not the way it was supposed to work. The concept guide states the following: The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification. and Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE Both of these statements imply that the unused memory is supposed to be released back to the operating system. The way that this feature operates on your system it allows you to juggle storage backwards and forwards between caches which is still useful but not 'what it says on the box'. I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation. In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. Hope this helps, Mike Hately __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Unix root account remote access
Why do not you login as you and then login as root? It is for your protection that nobody should be able to remote login from outside and any one who is login as root can be monitored. HTH --- M.Godlewski [EMAIL PROTECTED] wrote: List, I want to install Oracle on a UNIX system no sys admin on board yet, so I get to set up the system with the Oracle account etc. Unfortunately, I can not log into the root account remotely. I get a non console message. Is there a way to allow remote root connections? - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: passing ref cursor on a procedure
I used the following program to learn bind variables and ref cursor long time ago. This might help. Put this code into a file called getstaff.sql and then run it. Bottom lines show how to declare a cursor and pass it to a procedure using SQL. HTH Mohammed Shakir -- rem filename getstaff.sql rem uses employee table(emp ??) from scott/tiger schema on oracle rem you can use bind variables, then cursor can not have the return type??. rem You can return the ref cursor, see the multir~2.sql rem This program works rem USAGE: sqlplus scott/tiger @getstaff.sql rem Originally this was multir~1.sql drop package emp_data; CREATE PACKAGE emp_data AS TYPE EmpRecTyp IS RECORD ( emp_idNUMBER(4), emp_name CHAR(10), job_title CHAR(9), dept_name CHAR(14), dept_loc CHAR(13)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; PROCEDURE get_staff ( dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp); END; / CREATE PACKAGE BODY emp_data AS PROCEDURE get_staff ( dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT empno, ename, job, dname, loc FROM emp, dept WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno ORDER BY empno; END; END; / COLUMN EMPNO HEADING Number COLUMN ENAME HEADING Name COLUMN JOB HEADING JobTitle COLUMN DNAME HEADING Department COLUMN LOC HEADING Location SET AUTOPRINT ON VARIABLE cv REFCURSOR EXECUTE emp_data.get_staff(20, :cv); --- Igor Neyman [EMAIL PROTECTED] wrote: Oops. It's supposed to be: Variable lCursor REFCURSOR; Not: declare lCursor REFCURSOR; Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Neyman Sent: Wednesday, June 04, 2003 9:40 AM To: Multiple recipients of list ORACLE-L If in SQL*Plus: Just declare variable of REFCURSOR type and pass it to stored procedure: DECLARE lCursor REFCURSOR; Begin procedure_name(par1, par2, ., :lCursor); end; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Hatzistavrou John Sent: Wednesday, June 04, 2003 8:40 AM To: Multiple recipients of list ORACLE-L Dear All, I have a vendor procedure which I wish to run . This procedure takes as input variables a varchar and a ref_cursor. I know the query that is related to the ref_cursor and I wish to implicitly pass it to the procedure . How can this be done? Kind Regards, Hatzistavrou Yannis = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ 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: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: query run time vs IN list elements
This is what the book (page 196 Oracle SQL High-Performance Tuning - Second Edition, Guy Harrison.) says about your situation. INLIST ITERATOR step indicates that each subsequent step was executed once for each value in an IN list. A large number of elements in the IN list can be time consuming, because the cost-based optimizer is required to calculate costs for each of the concatenated subqueries. Only thing I would suggest is to remove first Order by Clause if you have any to remove sort step and see if it helps. HTH Mohammed Shakir --- gmei [EMAIL PROTECTED] wrote: RE: query run time vs IN list elementsHi: 1. I should have mentioned that I analyzed the index after creating the index, also I looked at the explain table in both situations: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=81 Card=267 Bytes=29 37) 10 SORT (GROUP BY) (Cost=81 Card=267 Bytes=2937) 21 TABLE ACCESS (FULL) OF 'GENE2DISEASE2H' (Cost=28 Card=26 7 Bytes=2937) Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=267 Bytes=29 37) 10 SORT (GROUP BY) (Cost=57 Card=267 Bytes=2937) 21 INLIST ITERATOR 32 TABLE ACCESS (BY INDEX ROWID) OF 'GENE2DISEASE2H' (Cos t=4 Card=267 Bytes=2937) 43 INDEX (RANGE SCAN) OF 'GENE2DISEASE2H_GENEID' (NON-U NIQUE) (Cost=2 Card=267) 2. I have not tried using exists. I was wondering why IN had such problem (I have not seen this situation before)? Guang -Original Message- From: Whittle Jerome Contr NCI [mailto:[EMAIL PROTECTED] Sent: Friday, June 06, 2003 2:14 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: query run time vs IN list elements Guang, 1. Just because you created an index doesn't necessarily mean Oracle is using it especially if using CBO and you haven't analyzed the table and index after the index creation. Try using a hint. 2. If IN isn't meeting your needs, try converting it to an EXISTS statement. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: gmei [SMTP:[EMAIL PROTECTED] Hi: Today I have something I don't fully understand. I have oracle 8173 on Sun Solaris. I have the following query that runs pretty fast when the number of elements in the IN list is small. But if I kept adding more geneids in the IN list, my query time increased dramatically. Now there is no index on any columns on the table. I got very similar results even if I created index on gene2disease2H.geneid. So this seem to suggest this situation has not to do with index. So my question is: why did I see the sigificant time increase when I only add one more geneid? TIA. Guang cut = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ 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: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: StoredProc SQL statistics
It is nice to have good tools to find what and how want quickly. I simply use statspack to find out my most time consuming SQL. To me SQL is SQL. Whether I execute it using SQL prompt, or it comes from one of my PL/SQL procedures. What I look for it is, how many time I am executing each sql, how many time it is loaded, how many physical reads I have, and how does it compare to consistent reads. If I am executing a SQL 100K time and it takes 1 second, I look at it. If a SQL takes 5 seconds and I execute it only once, it is not worth my time. There are many scripts in the DBA books, Tim Gorman website, and Steve Adams website you could try. HTH Mohammed Shakir --- Cary Millsap [EMAIL PROTECTED] wrote: The information you need is all in the extended (10046 level 8) SQL trace file. You just have to know how to determine all the recursive SQL parent-child relationships. Tkprof and TFA don't even try. This is one of the problems we wrote our Hotsos Profiler to solve. DBMS_PROFILER will help if your problem is not SQL; that is, if your problem happens to be in the PL/SQL code lines that don't make database calls. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... - Hotsos Symposium 2004 / March 7-10 Dallas -Original Message- Sent: Wednesday, June 04, 2003 12:55 AM To: Multiple recipients of list ORACLE-L Hi People, Is there any way to find the statistics(like tkprof gives) of SQL's within storedproc , when storedrpoc is called. Basically I want to find culprit SQL within StoredProc. Any help is appreciated Regards Sam __ 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: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ 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: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Benchmarking of ASP.NET with Oracle v/s MSSQL
Last year in May I had a chance to develop back-end software using ASP.NET with SQL Server and Oracle. Microsoft did an excellent job integrating SQL Server with ASP.NET. However, not such a good job with Oracle. I had to create stored procedure manually if I wanted to use Oracle. My main problem was the connection time. It took a while to connect to Oracle. And almost no time lag when connecting to SQL Server. I was using webservices to connect to the Oracle database and it took few seconds to make connection. I heard that Microsoft was going to bring in a new version of ASP.NET this year that would put Oracle on the same footing as SQL Server. I have not had a chance to follow up on that. Mohammed Shakir --- Niall Litchfield [EMAIL PROTECTED] wrote: What do you wish to discover? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Satya Prakash Viswanath Sent: 03 June 2003 05:40 To: Multiple recipients of list ORACLE-L Subject: Benchmarking of ASP.NET with Oracle v/s MSSQL Hi List, Anybody done performance benchmarking of a typical ASP.NET application with IIS(or any web server) with Oracle database(9.2) v/s MS SQL Server.(Latest version) Any pointers for the same is most appreciated. If there is any document on which POC has been done, plz mail to my id. FYI: 80% of the transactions are DB intensive. So, any guidelines for the same will be useful to me. Thanks ! Regards, Satya Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Satya Prakash Viswanath INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ 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: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Any tips to installing Designer 9i?
Check the readme file or installation notes. It will tell you where to install Designer to make it work. I installed a couple of years old version, and I was asked to install in another Oracle_home. Hope this helps --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm downloading Designer 9.0.2.4.0 to try it out. I don't want to #$%^@ up my current ORACLE_HOME, like I've done while testing every other Oracle product for Winders 2000. Any tips? Without reading the docs yet (still downloading), I plan to install it into another ORACLE_HOME. Other than that and some Registry scraping so I can actually remove Designer when I'm done, anything else I should be aware of? TIA, Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: enterprise manager console and sqlplus worksheet can't work
I installed Jdeveloper 9.x on oracle 9.0.1.3 and did not have a problem with OEM or SQL worksheet. I believe, you need to install it in a separate directory. Also it does not give you any startup Icon or put the Jdeveloper in Start and program menu, so you need to start from the directory. --- [EMAIL PROTECTED] wrote: If it is NT/2000 platform, Oracle installer normally screws up the registry entries, for example TNS_ADMIN in registry etc. Some product also screws up DLL ( specially microsoft DLL) files , if installed in the same oracle Home ( overwrite good DLLs with new but incompatible DLL) . 1. See, if OLD TNS_ADMIN is overridden by new TNS_ADMIN. Copy old stuff into the tnsnames.ora of new TNS_ADMIN. You will be lucky if this is the case. 2. Deinstall and reinstall all of the stuff in seperate home. Lots of work. In between install(s), you can export registry entries for backup and compare. 3. Some products have to be in same oracle home and will still screw up. In that case call Oracle :). Meanwhile, you can again deinstall everything and try, change the order of install of the product. I am assuming here that you have no other work, but do these over and over again. Pradip -Original Message- Sent: Thursday, March 27, 2003 12:09 AM To: Multiple recipients of list ORACLE-L afer installed the development tools I downloadd the developer suites from www.oracle.com such as jdeveloper. But after I installed it .my oracle enterprise manager console and sqlplus worksheet cant work which I installed before. Why ,and how to make them work . Thanks in advance! Regards Liujd -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: liujd INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Excessive SQL*Net message from client waits
I noticed a similar problem on my last contract assignment. I was lucky as the loading process took 4 hours but only 1 hour was spent by oracle. So we knew it was application that was taking the time. running the application on a faster processor cut the time to 1/3. We also could not find any problem with the network or sqlnet either. Hope this helps Mohammed Shakir --- Mark Richard [EMAIL PROTECTED] wrote: I think you can relatively safely argue that Oracle is spending 90% of it's time waiting for the client (by that a user pressing a button or the application processing some logic) - and therefore even if you make Oracle run infinitely fast you will only improve the application overall by 10%. Perhaps someone else can verify this. Jonathan explained, quite well, why the waits are so high... It the application spawns 10 sessions per user then each session will only be called once per approx. 10 SQL statements. Reducing the number of sessions will reduce the wait time on the report, but won't speed the application up. The stats indicate that the application fired ~3,000 queries in ~10 minutes (if I'm reading it right). That gives a stat of about 5 queries per second - it sounds like there is little you can do at the Oracle end of town. My guess is that the application is doing a lot of single row per query type statements when it should be working on a record set. It's a shame, but it looks like an application problem that Oracle can do very little to help out. Regards, Mark. Karen Morton [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lting.comcc: Sent by: Subject: RE: Excessive SQL*Net message from client waits [EMAIL PROTECTED] 13/03/2003 22:53 Please respond to ORACLE-L Not like this nor should it be the top event always as seems to be the case here I don't believe. And, I know for certain that the client did everything as quickly as possible during the trace. Minimal data entry done and OK buttons clicked without delay...no time out for getting a cup of coffee in between or anything. :) Karen -Original Message- Zanen Sent: Thursday, March 13, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Hi Isn't sql*net message from client always sort of on top, because it just means the rdbms is waiting for the client to send some query/command (user is not typing/clicking/reading fast enough) Jack -Original Message- Sent: donderdag 13 maart 2003 3:19 To: Multiple recipients of list ORACLE-L Hi All, I've got a situation where I've collected trace data and am seeing 90% of total response time is accounted for with the SQL*Net Message From Client event. Individual queries within the trace show minimal CPU time used and no obvious indications of bad SQL being the culprit. I used the Hotsos Profiler (way cool) and here's an example of what it shows: Response Time Component Duration # Calls AvgMin Max -- --- --- -- --- (i) SQL*Net message from client 500.98s 85.1% 2,757 0.181712s 0.00s 5.91s (i) unaccounted-for 23.03s3.9% (i) direct path write22.38s3.8% 1,373 0.016300s 0.00s 0.32s (i) log file sync20.70s3.5% 685 0.030219s 0.00s 0.52s (i) user-mode CPU
RE: slowish query causing problems...
the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be views of any such entity. _ _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal - For more information please visit http://www.marshalsoftware.com www.marshalsoftware.com _ _ DISCLAIMER This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. TFMC, its holding company, and any of its subsidiaries each reserve the right to monitor and manage all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. _ This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com _ ATTACHMENT part 2 application/octet-stream name=plan_table.csv = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Need to customize execution plan for a cross table.
I have a cross table that connects data in two base tables. If base table 1 is the driving table, query execution time is 0.02 seconds. If base table 2 is the driving table, query execution time is 0.5 seconds. But when this query is executed 10K times, it addes 1.5 hours to the processing time. Execution plan is simple when base table 1 is the driving table. It is based on nested loop. First select data from table 1. Based on data selected from table 1, select data from cross table using nested loop. Do the same for base table 2. Cross table allows very quick data selection from base table 2. Oracle does a good job at it. When base table 2 is the driving table, execution plan changes drastically. Now it uses sort-merge and merge join cartesian. Rather than cross table used to quickly get data from the next base table, it is used last. So first select the data from base table 2. Then get all the records from base table 1 and sort - merge it with table 1. Now do nested loop join between this data and cross table data. Since either of the two base tables can be a driving table, the only way I found is that I should create two separate queries, use ordered and nested_loop hints and this should work. However, the query is a view and therefore, I need to look into other possibilities. Any ideas? I am using oracle 8i(64 bits)(81.7.4) on Solaris(2.8). Also using cost based optimizer. = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Optimizer tuning parameter not working?
I changed the optimizer_search_limit value to 1 in init.ora to minimize the cartesian product in my execution plans. But I do not see any change in my execution plans. Am I missing something? I modified init.ora and bounced the database. I have also tried to run the query after manually set it in my sql session without any change in results. I tried it on NT/Oracle 8.1.7.4.1 I noticed that my execution plans do not change for the following tuning parameters either. 1. Optimizer_index_cost_adj=50 2. Optimizer_max_permutations=7 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! Mail Plus Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Table Scans
-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Setting Cursor Sharing = Force in 8.1.7.3
I recently used it on 8.1.7.4 and 9i. I gained 33% performance gain. It would be different depending on how much literal SQL is used in your code. My process ran fine, and I was able to load the data. However, I have not fully checked the data yet. Shakir --- Johnson, Michael [EMAIL PROTECTED] wrote: I went thru this recently ... The best thing to do is to FORCE the developers to use bind variables and then this is no longer an issue. If you do set it to FORCE , I believe there is a problem with it in versions 8.1.6 and before and it should not be used.Double Check with Metalink on this. FWIW ... Mike -Original Message- Sent: Friday, September 27, 2002 1:13 PM To: Multiple recipients of list ORACLE-L We are looking into setting up cursor_sharing parameter to FORCE. Has anyone seen any bad effects of setting Cursor_sharing=FORCE. Are there any real bad effects of setting it..? I was thinking of going back to my developers and make them use bind variables in their code. If I set the above parameter, they may continue to develop their code the way it is now. Let me know what you all think about it.. Thanks in advance, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Library object/cache locks?
This is from Steve Adam's Oracle 8i Internal Services(page 50-51) Library cache locks are held in shared mode during parse operation. A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed or executed. Pins are normally held in shared mode, but are also held in execlusive mode while the library cache information for the object is being changed. So my understanding is that, locks are applied during parse etc and pins during library cache information change. Hope this is correct. Shakir --- Walter K [EMAIL PROTECTED] wrote: Perhaps this is a rudimentary question but I haven't found any good info on this topic. Would someone please explain the difference between a library cache lock and a library cache pin? Is the first simply a dictionary lock on a physical object (i.e. table) whereas the second is a dictionary lock on a piece of code (i.e. procedure, function, trigger)? According to what I've read, I can query x$kgllk where kgllkreq=0 to find the library object locks in the database. How do I know if the lock is a library cache lock vs. a library cache pin, simply by inference due to the type of object? If I perform a systemstate dump and see 'mode=S' under a process, does that mean that the process has a 'share' lock on the object? If so, I'm confused as to why I don't see a corresponding lock in v$lock. Any info would be appreciated. Thanks VERY much in advance. -Walt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Best method to move Filesystems to RAW Devices.
of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] N to Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nat INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Sais INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Explain plan of a table you do not own
How about trying table_owner.table_name in your query where you specify the tablename in your from clause? Regards, --- Van der Sande Patrick [EMAIL PROTECTED] wrote: Dear, As system I want to generate an explain plan of an end user query. In this query a number of tables are defined which do not have a public synonym. The explain plan ... statement does not succeed because of a Table or View does not exist. Can somebody tell me how to bypass this without having to create public synonym ? Is it possible in 1 single SQL command ? I do not want to work with a third party tool ! Kind Regards, Ing. Patrick Van der Sande Managing Director ANDES Consulting BVBA www.andes-consulting.be = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: FW: Problem upgrading Oracle 8.1.7 on MS2000
not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Yahoo! - We Remember 9-11: A tribute to the more than 3,000 lives lost http://dir.remember.yahoo.com/tribute -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Recreate database script
ERwin, Visia Architect, Oracle Designer all can do it. There may be other packages that I do not know about. I have all three of them and they work. Shakir --- ltiu [EMAIL PROTECTED] wrote: Hello, Could anyone here suggest a software package that can create an ER diagram by simply connecting to an Oracle database, reverse engineer it to see the schema in an ER diagram - instead of in a file with ddl/dml statements. Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Loading tables from XML files
Yes, it is called XML-SQL(XSU) Utility. I ran a test this morning and it worked. It is much simpler. I did not have to write anycode. I needed to specify a different connect string to get it working. I loaded JDeveloper 3.2.3 to do it. I believe, I could have done it by downloading jdk 1.2 and using libraries that came with Oracle. My notes are shown below. You can see the document in 'Oracle Application Developer's Guide -XML Release 3(8.1.7). Regards, Shakir XML file load into oracle ?xml version='1.0'? ROWSET ROW num=1 EMPNO8000/EMPNO ENAMESmith/ENAME JOBCLERK/JOB MGR7902/MGR HIREDATE12/17/2002 0:0:0/HIREDATE SAL800/SAL DEPTNO20/DEPTNO /ROW /ROWSET Install JDeveloper for java.exe or download jdk1.2 from web Add to classpath.bat. Files shown here are from JDeveloper First three files are needed for XML load. The last three added to run java set CLASSPATH=c:\Program Files\Oracle\Jdeveloper 3.2.3\lib\xsu12.jar; c:\Program Files\Oracle\JDeveloper 3.2.3\lib\xmlparserv2.jar; C:\Program Files\Oracle\JDeveloper 3.2.3\jdbc\lib\oracle8.1.7\classes12.zip; c:\Program Files\Oracle\JDeveloper 3.2.3\java1.2\dt.jar; C:\Program Files\Oracle\JDeveloper 3.2.3\java1.2\lib\jvm.lib; C:\Program Files\Oracle\JDeveloper 3.2.3\java1.2\lib\jvm_g.lib; C:\Program Files\Oracle\JDeveloper 3.2.3\java1.2\lib\tools.jar run Classpath.bat Following command worked on amd1400 oracle 8.1.7 personal with patch 8.1.7.3 java OracleXML putXML -user 'scott/tiger -conn jdbc:oracle:thin:@amd1400:1521:amd1400 -user scott/tiger -filename c:\emp.xml emp java OracleXML putXML -user 'scott/tiger -conn jdbc:oracle:thin:scott/tiger@amd1400:1521:amd1400 -user scott/tiger -filename c:\emp.xml emp emp - table name -conn Url --- Schauss, Peter [EMAIL PROTECTED] wrote: Is there a utility similar to SQLLDR which will load tables from XML files? I found the Java and PL/SQL APIs for loading tables from XML files and wrote a trivial Java application to load data so my immediate problem is solved. I just wanted to make sure that I hadn't missed a simpler way to do it. thanks, Peter Schauss Northrop Grumman Corporation [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schauss, Peter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Install: 9i on 2000, TNS Listener service not installed
I have installed 9.0.1 many times on Win2K but with a CD and had no problem with the listener. Did you see any error messages during the installtion? Please check the install log. You could try to re-install Oracle with custom option. That would not hurt the database you have installed, except you will be adding missing components. I have tried this option in other cases and has worked. I am assuming that listner service is not showing up in the services window and it is not set on manual start. Regards, Shakir --- Jesse, Rich [EMAIL PROTECTED] wrote: I've got 9.0.1 on my WinTuke WS SP2 box and the listener service installed correctly. Not that it helps you any, but I thought you should know that it worked for someone. GL! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Robert Monical [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 08, 2002 5:33 PM To: Multiple recipients of list ORACLE-L Subject: Install: 9i on 2000, TNS Listener service not installed Starting to play around with 9i. Downloaded and installed on Windows 2000 Workstation. So far so good except no TNS Listener service lsnrctl start from the command line creates a listener. This may be a side effect of installing on Workstation instead of Server. Anyone have any insight? Anyone know how to create the listener service after the install? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: What block size are you using for your new 9i data warehouse?
: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson Poovathummoottil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Todd Carlson INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http
RE: CPU Pegged at 100%
Raghu I am also chasing a ghost like yours. Even though I have much larger number of cache Buffer chain waits, I look at the total picture. Looking at my statspack report, I noticed that application spends 30% of the time in buffer cache contention. How about the other 70%? Looking at the buffer gets reports, I found each of my processes having 2.2 Billion buffer gets. Looking at my application, I should not have more than 250Million buffer gets for each process. Off course the problem is with the SQL. So I am working first on my SQL and then if buffer cache chains problem still exist, I will work on it. So check the buffer gets and find out SQL that are doing it and work on them. Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: redo_size values in v$sysstat
I recently started getting these high numbers in my statspack
statistics. We have Oracle 8.1.6.0.0 on Solaris 2.6 platform.
Shakir
--- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED]
wrote:
Glenn,
Without commenting on the value of the hit ratio, I can comment on
the suggestion that the bug affects all platforms.
I am running 8.1.7.1.4 on NT4 and your query gives the following:
SQL col name for a20
SQL col value for 999,999,999,999,999,999,999
SQL select name,value from v$sysstat
2 where name in ('redo size', 'physical reads', 'db block gets')
3 /
NAMEVALUE
db block gets 872,439,682
physical reads 74,967,581
redo size 32,655,440,244
SQL select sysdate-startup_time from v$instance;
SYSDATE-STARTUP_TIME
107.677072
SQL
So, we have generated over 2Gb redo and our other counters aren't
wrapping.
This is consistent for another NT4 81714 database we have as well.
I don't have access to any other platforms besides Windows so can't
comment on the situation elsewhere.
Hope this helps,
Bruce Reardon
-Original Message-
Sent: Friday, 12 April 2002 0:59
Glenn,
The buffer cache hit ratio is meaning less, not only after startup
but any time you calculate it. I am pretty sure that I am not the
first one and probably not the last one saying that on this mailing
list.
Now about the claim of why you need to wait until 10i to get this
fixed, has probably something to do with the fact of how the SGA is
allocated on the HP platform. Any change in the layout of the fixed
SGA will mean a recompile of the code on HP.
Now it looks to me that the upper 4 bytes of the 8 bytes have been
set to -1:
18446744069434437169
012EEE31
18446744052688746229
FFFB1B0FF6F5
So you probably could adjust for that
Anjo.
Glenn Travis wrote:
I sent a message last week regarding our values in the v$sysstat
table being WAY too large;
physical_reads = 18,446,744,069,434,437,169
db_block_gets, physical_reads_direct, physical_writes_direct also.
This prevents us from running the db cache hit ratio queries.
I logged a tar with Oracle and they said it was a bug (#1713403).
It is caused by an overflow in v$sysstat when the amount of generated
redo grows over 2GB. They say this bug can't be fixed (at least not
until 10i!). I am running on 8.1.7 (HP-UX11).
If you are on 8i, could you query the v$sysstat table and let me
know if anyone else is seeing this problem?
col name for a20
col value for 999,999,999,999,999,999,999
select name,value from v$sysstat
where name in ('redo size', 'physical reads', 'db block gets')
/
NAMEVALUE
db block gets 18,446,743,996,920,309,855
physical reads 18,446,744,052,688,746,229
redo size 17,049,609,736
I find it unacceptable that Oracle would ignore this until 10i.
The only time I can get a cache hit ratio is when I first start up
the database (which doesn't mean anything). I know hit ratios are
overrated and we look at waits more for performance tuning (read all
the articles), but it is still frustrating nonetheless.
Author: Glenn Travis
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
=
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also
RE: ORACLE 9i doesn't start on Linux
try to start it manually and see what happens. Might give you clue of what is wrong. --- Gogala, Mladen [EMAIL PROTECTED] wrote: Well, for one thing, you should edit /etc/oratab. That might help. -Original Message- From: Zsolt Csillag [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 11, 2002 4:04 PM To: Multiple recipients of list ORACLE-L Subject: ORACLE 9i doesn't start on Linux Hi, I've installed Oracle 9i on Suse Linux 7.1. The lsnrctl starts nicely, however when I type dbstart then it does absolutelly nothing. No error message, but the database won't start. Any ideas? Thank you in advance Zsolt Csillag, Hungary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Zsolt Csillag INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert performance
No enough inserts to bog down the CPUs? --- Paul Baumgartel [EMAIL PROTECTED] wrote: Thanks, Mohammed and Anjo, for your replies. Now my question is this: given that the table structures (freelists, etc.) are identical on the two machines, the init.ora parameters are identical for the two instances, and the machines themselves are nearly identical (one has 6 CPUs, one 4, but in neither case are the CPUs anywhere near maxed out), what could be causing the discrepancy? PB --- Mohammed Shakir [EMAIL PROTECTED] wrote: data block waits is the indicator of freelists contention. I have never seen a freelists contention, even though I have been running 11 processing doing inserts using 11 CPUs. I have seen library cache, Shared pool and cache buffer chains waits. I took care of buffer busy waits and db file sequential reads by increasing freelists, initrans to 11 and by partitioning the disks. In my case most of the data was writting to the same block by as many as 9 processes. I am working on a Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 Library Shared Pool latch contention was found due to Literal or non-shared SQL. Check SQL_text in SQLarea to find literal SQL. Shared Pool contention seems to be due to a very large Shared pool. I found a bug report that indicates that cache buffer chain problem was fixed in 8.1.3.4 and 9.0.1.3 Cache buffer chain is also an indicator of high physical and logical I/O. You can check on that as well. Hope this helps. Shakir --- Paul Baumgartel [EMAIL PROTECTED] wrote: Greetings! I am trying to diagnose a performance difference between two databases running the same test. They are similarly configured (same SGA size, etc.), and the servers are identical except for the number of CPUs (server A has 4, server B has 6). On database A, INSERT performance is about 190 rows/second. On database B, INSERT performance is over 500 rows/second. I saw some cache buffers chains, buffer busy, and library cache latch waits on database A while the test was running, as well as redo log sync waits. The waits didn't seem excessive, though. I checked for checkpoint not complete redo allocation messages in database A's alert log and found none. The db_block_lru_latches parameter is set to one-half the number of CPUs in both machines. I'd much appreciate any suggestions as to what else to check. I know that freelists can be an issue (there are multiple sessions performing inserts); how can I check to see if there's freelist contention? Anything else I should investigate? Many TIA, = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services
Re: package in sqlarea
You need to check the SQL in the procedure. I have the same situation. I run Tim Gornman's temp_top_stmt2.sql script to identify the SQL within the procedure. Statspack also identifies the high resource consuming SQL. IXORA also provides scripts to identify them. Shakir --- Big Planet [EMAIL PROTECTED] wrote: Hi List , While searching for poor sqls , I am getting a stored procedure name in v$sqlarea with high value in disk_reads . What does it mean and how can I reduce the disk read . Yeah ..one more thing does the case of stored proc and name of bind varibales creats different entry in sqlarea . TIA Bp = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Insert append generating redo
This is code I use and it works. If you see I do not use 'AS' before select. I am not sure if you need it. insert /*+ parallel(1, 6) */ into 1 select /*+ parallel(2, 6) */ * from 2; Shakir --- paquette stephane [EMAIL PROTECTED] wrote: Hi, I'm trying the following insert /*+ append */ into t1 as select * from t2; t1 is created with nologging attribute. The insert is not using the hint at all. I can select on t1 (before any commit) which I should not be able to do if the append hint was used. Any ways to get the hing used ? (Oracle 817/NT) TIA = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: UWIN 3.0 and ORA-12560 error
I donot have UWIN so I can not test it. However on UNIX using ksh you not only you set the ORACLE_SID and ORACLE_HOME environment variable, but you need to export them as well. try it, Shakir --- Deshpande, Kirti [EMAIL PROTECTED] wrote: Hello, I am getting this ORA-12560: TNS Protocol Adapter error, while connecting to the local database on my Windoze NT Workstation. This happens when using the ksh environment in UWIN 3.0. I have done all the debugging I can think of. Everything works fine via DOS Command window. I have checked Registry entry and it looked okay to me (ORACLE_SID, ORACLE_HOME etc). My env variables (PATH, ORACLE_SID, ORACLE_HOME etc) are all fine. I can connect via SQL*Net, no problem there. It's just the regular connection under ksh environment that is getting this error. Has anyone seen this error with UWIN 3.0? I have UWIN 2.0 on my home PC and it works fine. I sure can use some help in resolving this ORA-12560 error. Thanks in advance. - Kirti -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to tune SQL and CPU usage
There are two types of background processes Oracle runs. One to manage the database and the other to process user requests or SQL. Which one are using up CPU is difficult to say without looking at the PS report. Even though it may seem Oracle processes are using lots of CPU, it may be misleading, because if Oracle is up and running for sometime PS results will show high numbers for Oracle background processes. However the background process to service your SQL should not show high number. It is possible that the SQL user is processing is not optimized or there is an index missing on the table and Oracle is performing full scan of a table. You can tune the SQL. But you have to spend time learning Oracle. Look at Oracle concept manual for understanding SQL. Look for High Performance SQL tuning book to help you understand what is involved for SQL tuning. Shakir --- Nguyen, David M [EMAIL PROTECTED] wrote: Someone uses SQL to query data, he complains it takes too long to get results back from oracle database. He also notices there are many oracle processes running on system that uses much CPU and causing system slow down. Is there a way to tune SQL to improve query and to set maximum number of running oracle processes? Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: WHICH UNIX FOR ORACLE?
Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Moving From Rule-Based to Cost-Based
It is hard to say what will happen to the execution plans if your SQL written for rule based are run with cost based optimizer. I would collect all my queries in one file, analyze all my tables, set for cost based optimization, run the queries and check my execution plans and compare. Some of the things you might notice is the table order in the execution plans might change. The driving table may change and that may make the world difference in execution speed. I once did this test. I went from Oracle 7.2 on OS2 to 8.1.5 onNT and my application ran twice as fast without any changes. I am sure some of the gain is coming from Oracle 8i but it was a pleasant surprise.Cost based optimizer has improved drastically since version 7 and does a decent job of creating proper execution plans. However, you have to keep an eye on it. I have found it more difficult to optimize code using Cost optimizer than rule based optimizer. There are too many variables in statistics etc that we do not know everything about and some time it is hard to get the execution plan you want. I still collect all the SQL in one file for all my applications. If I find that one of my process is taking very long time, I run this file and compare my execution plans. this provides me instant information on where I am having problems. Oracle is doing more work on cost based optimizer to support new features like partitioning and so on. So it would be to your advantage to move forward to cost based optimizer. There are lot more parameters that you can use to customize the optimizer to your liking and get the type of execution plans. You can use the same execution plans on test and production system by using Oracle new execution plan stability features. I would not simply give rule based SQL and let my customer run on cost based optimizer without me testing and making sure that it would not blow up my application. Even though I worked on over 100 gig DB, I would not consider a 25 gig DB a small database. --- Sam Bootsma [EMAIL PROTECTED] wrote: Hello All, At our site, we are use rule based optimization. We are careful to write SQL that performs well under this mode. Some of our SQL also contains hints. When we provide our application to clients, we default it to use Rule-based. However, some clients may choose to run our application in cost-based mode. This will be as simple as analyzing all tables and indexes in our schemas', and changing the mode to use cost based. My Questions: 1. How likely is it that our application will perform worse under cost-based than it does under rule-based. What has been the experience of others on the list who have moved from Rule-based to Cost-based? 2. What are some of the underlying reasons for performance to decline when making such a move? What are some gotchas to watch for? 3. Is there more that needs to be done to move from rule to cost? (Other than changing the optimization mode and analyzing tables and indexes)? Most clients run our database on NT, although some use UNIX. Assume Oracle 9i. Our database (at client sites) tends to be small, with the largest being about 25 GB. Thanks for any suggestions, Sam Bootsma Technical Support Analyst -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sam Bootsma INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
This is the code you submitted later yesterday and the error I am getting during compile is shown below. I am wondering how you got it to work. I tried it this on 8i/Solaris and 9i/NT. SQL CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS 2 -- 3 PROCEDURE eval_this (pi_string IN VARCHAR2) IS 4 BEGIN 5 RTRIM(pi_string,';') || '; end;'; 6 END eval_this; 7 -- 8 END My_Dynamic_Stuff; 9 / Warning: Package Body created with compilation errors. SQL show errors Errors for PACKAGE BODY MY_DYNAMIC_STUFF: LINE/COL ERROR - 5/22 PLS-00103: Encountered the symbol | when expecting one of the following: := . ( % ; The symbol := was inserted before | to continue. SQL --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: There was another email I sent to list, check that out, that has the right code. Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/SQL debug
You are missing the BORTTAGS_FLAGG in your INSERT STATEMENT at the end
of the code, where you are creating the string. The last variable is
vare_snr and not BORTTAGS_FLAGG
lvSQL := 'INSERT INTO PBK.PRISREGISTER(' ||
'BUTIKID,' ||
'EAN,' ||
'PERIOD,' ||
'LAGSTAPRIS,' ||
'PRIS,' ||
'UNDERLAGID,' ||
'SUBSTITUT,' ||
'KOMMENTAR,' ||
'BUTIKTYP,' ||
'VARUTYP,' ||
'REGICASTATUS,' ||
'VARENR,' ||
'LEVNR,' ||
'VARE_SNR) ' ||
'VALUES ('||
ButikID||','||-- BUTIKID
Ean||','''|| -- EAN
Period||''', ' || -- PERIOD
LagstaPris||','|| -- LAGSTAPRIS
strButikPris ||',' ||-- PRIS
inUnderlagid ||',' || -- UNDERLAGSID
strsubstitutean || ',''' || -- SUBSTITUT (null or
LPKORGEANREL.EANREL)
--''null', '|| -- KOMMENTAR
'null'', '|| -- KOMMENTAR
'0' || ' , ' ||-- BUTIKTYP
--'0' || ', ' ||-- BUTIKTYP
varutyp || ', ' ||-- VARUTYP (0 or according to LPKORGEANREL)
IcaStatus||', ' || -- REGICASTATUS
varenr || ', ' || -- VARUNR (original VARENR, not the
substitutes)
levnr || ', ' || -- LEVNR (original LEVNR, not the substitutes)
vare_snr || ')'; -- VARE_SNR (original VARE_SNR, not the
substitutes)
--- [EMAIL PROTECTED] wrote:
Hallo,
I am trying to insert things into the table PRISREGISTER_KOPIA_WED
but why doesnt the insert statement work.
It says Missing expression when the insertstatement runs and when i
look at the sql code which is trying to be run to insertinto the
table
it says just missing expression and it seems that I dont nclude
BORTTAGS_FLAGG by some reason. can anyone help me with this:
The sql which is to run is INSERT INTO
PBK.PRISREGISTER_kopia_wed(BUTIKID,EAN,PERIOD,LAGSTAPRIS,PRIS,UNDERLAGID,SUBSTITUT,KOMMENTAR,BUTIKTYP,VARUTYP,REGICASTATUS,VARENR,LEVNR,VARE_SNR,BORTTAGS_FLAGG)
VALUES (116,7312300342180,'200206',
0,9.95,616,NULL,'null', 0 , 0, 1, 290799, 13173, 10, )
As you see the last parameter is missing.
The whole pl/sql code looks like this included file.
(See attached file: newregicapriceroland.sql)
Hope any one can help me with this. Would appreciate very much.
Thanks in advance
Roland S
ATTACHMENT part 2 application/octet-stream
name=newregicapriceroland.sql
=
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
Thanks so much for your time and effort. Correct code was found in the attachement. It compiles and runs as you described. You found a very innovative way of converting the string to an expression. I will need to check on whether it creates the Shared or non-shared SQL. That will be the determining factor for us to use it. If it works, you could save my project at least a month of effort. So thanks for help. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Here you go Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mohammed Shakir [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 12:25 PM To: Jamadagni, Rajendra Subject: RE: Literal SQL and sys.dual Thanks for your help and sending the code three times. I know it can be a bother. However, Can you just tell me in a yes/no, if this is the code you are sending me? The code I have received it is shown below. Thanks Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: For the 3rd time I am sending this code, Mohammad, it wouldn't be a bad idea to try to resolve the code yourself. CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS v_result NUMBER; -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN RTRIM(pi_string,';') || '; end;'; END eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ ATTACHMENT part 2 application/octet-stream name=MY_DYNAMIC_STUFF.PKS *2 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 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
I ran the tkprof on the code and found that it is still generating the literal non-shared SQL. Because the code uses dynamic sql. However, you did convert the string to a workable expression that got summed and assigned to the result number variable. Our past programmer did the similar thing. He created the expression as a string, concatenated as 'select ' || my_string || ' from dual;' and passed it as an argument to a function that used a native dynamic SQL to execute it. my_string := 'select ' || '1 + 1 + 2' || ' from dual;'; execute immediate my_string into my_number_variable; So I am back to SQUARE ONE. Thanks however for a great try. Shakir --- Mohammed Shakir [EMAIL PROTECTED] wrote: Thanks so much for your time and effort. Correct code was found in the attachement. It compiles and runs as you described. You found a very innovative way of converting the string to an expression. I will need to check on whether it creates the Shared or non-shared SQL. That will be the determining factor for us to use it. If it works, you could save my project at least a month of effort. So thanks for help. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Here you go Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Mohammed Shakir [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 12:25 PM To: Jamadagni, Rajendra Subject: RE: Literal SQL and sys.dual Thanks for your help and sending the code three times. I know it can be a bother. However, Can you just tell me in a yes/no, if this is the code you are sending me? The code I have received it is shown below. Thanks Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: For the 3rd time I am sending this code, Mohammad, it wouldn't be a bad idea to try to resolve the code yourself. CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS v_result NUMBER; -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN RTRIM(pi_string,';') || '; end;'; END eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ ATTACHMENT part 2 application/octet-stream name=MY_DYNAMIC_STUFF.PKS *2 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 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list
RE: Literal SQL and sys.dual
Thanks for the help. You are right on the money. However, I am getting the data as strings and I do not know what I will get. I get hundreds of thousands of them and I have no idea what they would look like. I might get any string. couple of examples: select 1 + 1 + 1 + 1 from dual select 1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 + 0) So my requirement is different. When I use bind variables, either I use a bind variable for each literal and it will work, or use one bind variable for all literals and then all I will get back is one of the above string in the result set. The best answer is tht programmer should calculate the data as it comes in based on the token he gets and when he/she parses the data coming from the table. This might take place eventually. Right now I am trying to reduce huge library cache and shared pool contentions by elimating literal non-shared SQL. I come from C/C++/Pro*C development environment and I can not imagine ever using sys.dual to do arithmatic. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: The error is in your code, you are explicitly telling oracle that the bind variables are varchar2, so why would Oracle evaluate it? Try this ... DECLARE nnum1 NUMBER; nnum2 NUMBER; nresult NUMBER; BEGIN BEGIN nnum1 := 0; EXECUTE IMMEDIATE USING nnum1, OUT nresult; dbms_output.put_line(TO_CHAR(nresult)); END; BEGIN nnum1 := 1; nnum2 := 1; EXECUTE IMMEDIATE 'begin SELECT :1 + :2 INTO :0 FROM dual; END;' USING nnum1, nnum2, OUT nresult; dbms_output.put_line(TO_CHAR(nresult)); END; END; / Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
Thanks a million, I am going to try it and let you know how it works
out. I can see the result I am looking for.
Shakir
--- Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
TRy something like this ...
CREATE OR REPLACE PACKAGE My_Dynamic_Stuff IS
v_result NUMBER;
--
PROCEDURE eval_this (pi_string IN VARCHAR2);
END My_Dynamic_Stuff;
/
CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS
--
PROCEDURE eval_this (pi_string IN VARCHAR2) IS
BEGIN
RTRIM(pi_string,';') || '; end;';
END eval_this;
--
END My_Dynamic_Stuff;
/
Here is a test ...
SQL get raj_test
1 declare
2my_str varchar2(100);
3 begin
4 dbms_output.enable;
5 my_dynamic_stuff.eval_this('1 + 1 + 1 + 1');
6 dbms_output.put_line ('1 + 1 + 1 + 1 evaluates to : ' ||
to_char(my_dynamic_stuff.v_result));
7 --
8 my_dynamic_stuff.eval_this('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) /
( 5 +
5 + 0)');
9 dbms_output.put_line ('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5
+ 5 +
0) evaluates to : ' || to_char(my_dynamic_stuff.v_result));
10* end;
SQL @raj_test
1 + 1 + 1 + 1 evaluates to : 4
1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 + 0) evaluates to : 3.3
PL/SQL procedure successfully completed.
SQL
HTH some
Raj
__
Rajendra JamadagniMIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
***1
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 ESPN at (860) 766-2000 and
delete this e-mail message from your computer, Thank you.
***1
=
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
__
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
This code looks the same as what you submitted earlier. The package body does not compile either. I am not sure what you are trying to do with the RTRIM function. I see that you will get for the first example 1 + 1 + 1 +1; end; but then what? I have tried to compile the package body on 8.1.6/Solaris and 9i/NT and both bomb at concatenated string ( || ) on RTRIM code line. Is this the correct code you are submitting? --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Hmmm there is a small error The package body should be as follows CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN RTRIM(pi_string,';') || '; end;'; END eval_this; -- END My_Dynamic_Stuff; / this of course assumes that you are on 8i or better version, if you are on pre 8i version, change code appropriately to use dbms_sql package instead of NDS. HTH Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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 = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Lietral
= Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Literal SQL and sys.dual
I am working on a database optimization project. My Shared pool is filled with SQL like select 0 from dual; select 1/1 from dual; select 1 - 1/(2 + 2) from dual; I tried to use the bind variables to minimize the literal SQL. However I need a different SQL script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Literal SQL and sys.dual
As usual I have inherited the code. The code is not in front of me. Here is the psuedo code. sql_string := '1 + 1' sql_string := 'select ' || sql_string || ' from dual;' Above string is passed as a parameter to a function that executes a native dynamic sql: begin execute immediate sql_string; end; Oracle executes the above query in two steps. First step is the native dynamic sql and second part itself, and passes SQL to Oracle as: select 1 + 1 from dual; And the result is 2. As complete text statement is processed. Now comes 1 + 2 and the SQL is parsed again and that is non-shared SQL. The problem is how in this example Oracle sees 1 + 1 as numbers. If I use bind variable to pass 1 + 1, they are taken as text. I agree that Programmer did not have to use sys.dual. He has a table that indicates which record is a value and which record is a token If token is a + he can add the value. If a token is - (minus) he can subtract the value when he is getting the data from the table. He did not have to create the string and pass it to dynamic sql or sys.dual to process it. The programmer did not realize that he is executing this and other statements over a million times and he will be filling up shared pool with 11K unique (sys.dual) statements. There are 14000 statements (not sys.dual) use literal sql filling up the shared pool. I think they are easier to fix as they are part of either value or where clause. I am not sure if your approach will work as I am doing the same. Here is my test program which I believe should look similar to yours. I have a simpler version but it is not with me right now. The results of the programs are 0 and 1 + 1. whereas I should be getting 0 and 2. The problem is 1 + 1 is used as string rather than numbers. declare l_varchar2 varchar2(50); l_varchar_in varchar2(50); begin begin l_varchar_in := '0'; execute immediate 'begin select :l_varchar_str into :l_varchar2 from dual; end;' using l_varchar_in, OUT l_varchar2; dbms_output.put_line(l_varchar2); end; begin l_varchar_in := '1 + 1'; -- I will be getting them as string execute immediate 'begin select :l_varchar_str into :l_varchar2 from dual; end;' using l_varchar_in, OUT l_varchar2; dbms_output.put_line(l_varchar2); end; end; --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: One way to calculate select (1+1) from dual; is myvar := 1+1;. Trust me, it is more efficient. Most arithmetic functions can be used in pl/sql, without having to use dual. Question for you, why do you think you need to go to database, when you are NOT accessing anything from the database? Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1 __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Literal SQL and sys.dual
The programmer does not know what he has to calculate ahead of time. The formulas are stored in the table. He takes the data out from the table with the formula creates the string of data and operator. Then adds: create string: sql_string := '1 '; sql_string := sql_string || '+ '; sql_string := sql_string || '1'; sql_string := 'select ' || sql_string || ' from dual;' result := execute immediate sql_string; --- Igor Neyman [EMAIL PROTECTED] wrote: Why don't you do: variable1 := 1+ 1; instead of select (1+1) into variable1 from dual? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 02, 2002 2:03 PM I am working on a database optimization project. My Shared pool is filled with SQL like select 0 from dual; select 1/1 from dual; select 1 - 1/(2 + 2) from dual; I tried to use the bind variables to minimize the literal SQL. However I need a different SQL script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Literal SQL and sys.dual
You are right. There are at least twelve modules that I have identified so far. Some are easy and others are like speghatti. Learning the code, fixing and testing will require couple of months. I have to leave this project with in a month and I have SQL code that needs optimization. I have a huge buffer gets problem. So I am running against the time. So without messing up the logic and spend time learning fixing and testing the code, this one seems like a better option for me at this point. I am trying the project to do it right and do it where code needs the proper change even when I am gone. Thanks for yours and every one elses response. Your advices are supporting the ideas I have in the back of my mind to do it right. --- Stephane Faroult [EMAIL PROTECTED] wrote: Mohammed Shakir wrote: I am working on a database optimization project. My Shared pool is filled with SQL like select 0 from dual; select 1/1 from dual; select 1 - 1/(2 + 2) from dual; I tried to use the bind variables to minimize the literal SQL. However I need a different SQL script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) Mohammed, Don't you think, before starting with a PL/SQL function of death, that it *might* be easier to check the code and see where these statements, which are unlikely to be functional requirements, are used? Rewriting the PL/SQL code around them is probably the most efficient way to get rid of them. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: STATSPACK
First it seems like you want to understand the report. The report does have some comments on the top of the page, what to expect. However, if you can buy the book that Dennis suggested, you will get additional information on how to interpret the report. The book also provides many scripts that generate reports as well as comments in the report. I found the book and especially the scripts real worth while. As far as writing you own query, you could use the scripts provided with Statspack and the book as a baseline and get your own canned reports. I have used scripts that I have collected over many years. Statspack collects relevant data, organizes data in a nice way and stores the data for you. The scripts and information in the book give you additional stuff that is not covered in the book. I hope this helps you in making the decision. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Reddy - I assume that you mean that you ran the statsrep.sql and printed the report. Is your question about interpreting this report? There is a good series of articles at http://www.oracle.com/oramag/oracle/00-Mar/index.html?o20tun.html Or is your question how to write your own queries? If that is the question, I would recommend that you invest in the book Oracle High-Performance Tuning with STATSPACK by Don Burleson, available at your local bookstore. Don offers some articles on-line at http://www.dba-oracle.com/articles.htm Let me know if that is the information that you want. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 26, 2002 3:00 PM To: Multiple recipients of list ORACLE-L Hello ALL, I have set up the statspack on Oracle 8.1.7 and just now I also got the REPORT , but poor me unable to understand that . Can anybody help me out in this ... Well I know www.oraperf.com will do it for me by sending a report , but I want to do it myself by writing some queries ... would anybody help me with some white paper. Thanks In advance, Madhu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reddy, Madhusudana INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8.1.7 LMTs Autoallocate vs Uniform Extents
this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Sundeep Maini Consultant Currently on Assignement at Marshfield Clinic WI [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: sundeep maini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORACLE TUNING
Are the sizes of the tables same on both development and production machines? In our case, we used histograms on indexed columns and that brought execution plans same on both the database. Look for analyze command syntax to build histograms. --- [EMAIL PROTECTED] wrote: Both have current statistics. Data distribution is smaller on DEV. Both are using CBO. -Original Message- Sent: Tuesday, March 19, 2002 4:53 PM To: Multiple recipients of list ORACLE-L What about current statistics on both DBs? Is the distribution of the data reasonably the same? Are they both using the same optimizer? I would state that obiviously something is different between the two instances; otherwise you'd get exactly the same results. [EMAIL PROTECTED] wrote: Hello: I have an identical query that has similar explain plan on both Prod and Dev environment, only difference being that certain indexes are performing unique scan on Prod and Range scan on Dev. Can you cast some light on how this can be matched and under what conditions this discrepancy might occur. Thank you, Srini -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Lead, follow, or at least have the courtesy to get out of my way! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Need a delete trigger that tracks deletes.
copy the data to another audit table with the id of the user who deleted it with a timestamp when it was deleted. The data in this table can be deleted once a week or a month as you would like it. --- Khedr, Waleed [EMAIL PROTECTED] wrote: Another idea is to mark the record deleted instead of physically deleting it. Regards, Waleed -Original Message- Sent: Monday, March 18, 2002 5:18 PM To: Multiple recipients of list ORACLE-L We need to be able to track what records have been deleted from a table. Our current thinking is 1. A before delete trigger, that stores a unique ID of the all the data in the table. 2. A after delete trigger, which gets the records left in the table. 3. Subtract the two lists to find the records that have been deleted. This appears to be cumbersome, is there anyway to do this with one trigger ? Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tkprof plan missing rows
Do you have data in the tables that you are running the query againt? You do have row count zero, so tkprof seems to count the rows but the rows do not seem to be there or are not selected. --- Baker, Barbara [EMAIL PROTECTED] wrote: Oracle 7.3.4, OpenVMS 7.1 My tkprof report is missing the row count in the execution plan. Does anyone know why what I might be missing? If I autotrace the same query in the same database, I do get cardinality. The tables have been analyzed. If I tkprof another database using the same version and OS, that tkprof DOES have row counts. I believe I'm just missing a parameter somewhere, but I don't know where. Thanks for any ideas. Barb $ tkprof DRAX02_AMPROD_FG_SRV_041.TRC;1 sel.tkp explain=user/pwd sys=no * *** Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WO' 0INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PK_WO' (UNIQUE) here's a tkprof from a different database: Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 1440212 SORT (GROUP BY) 2785044HASH JOIN 3109095 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'WORK_ORDER_DETAILS' 3762491 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WORK_ORDER_DETAILS_IDX3' (NON-UNIQUE) 5727880 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SLS_OF_REC' -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ATTACHMENT part 2 application/ms-tnef name=winmail.dat = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SOFT PARSE RATIO?
Check the size of your shared pool. Do you have big enough shared pool to keep parsed SQL statements for re-use or they are flushed out because of a small library cache and are parsed when loaded again. --- Smith, Ron L. [EMAIL PROTECTED] wrote: Can anyone tell me more about this? I have a 7.3.4 database that appears to be doing an awful lot of parsing even though it uses bind variables. How do I tell for sure it is doing too much parsing and is there a way to control it assuming the app is already using bind variables? Ron Smith DBA Kerr-McGee Corp -Original Message- Sent: Tuesday, March 12, 2002 1:42 PM To: Multiple recipients of list ORACLE-L Thanks for the correction and for your paper Regards, Ed The parameter is really called session_cached_cursors and it doesn't actually reduce the soft parses - it just decreases the serialization overhead of repeated soft parses within the same session. It's all in my paper on OTN. Thanks, Bjørn. On Tuesday 12 March 2002 14:58, you wrote: Seema, I believe you can reduce that ratio by using session_cursors Regards, Ed Hi In our database I found SOFT PARSE RATION is 62% which is lower than normal.What could be problem and how to correct this problem?Please suggest. Thx -Seema -- Bjørn Engsig, Miracle A/S http://MiracleAS.dk -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: tns error
You may have tnsname.ora under network/admin directory and another in net8/admin directory. I think you will have only one listerner.ora file under network/admin directory. So make sure both directories have tnsname.ora and the service name defined --- art [EMAIL PROTECTED] wrote: hi all i installed personal oracle 8i on my pc and also jdeveloper in net8 configuration assistant i want to configure service name and also listener in this case for listener when i selecet IPC i recieve a message that said it has conflit with another software and also when i test my local new service name it is not succesful and therefor i cant connect help me what should i do to solve this problem and also what i must set up for my jdeveloper thanks artemis zarnegar = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Insert is very slow
We use ForAll for insert and it works fine. However, we do not use prepare_cached or execute. We use bind variables with our static SQL and insert millions of records and it is fast. --- Gavin D'mello [EMAIL PROTECTED] wrote: MessageI'm trying to do a bulk insert using DBI and Oracle for about 248 rows, this is proving to be pretty slow and expensive. Is there anyway where I can do a bulk insert ? I am using prepare_cached and execute with parameters . Thanks so much, Gavin - Original Message - From: Cunningham, Gerald To: Multiple recipients of list ORACLE-L Sent: Saturday, March 02, 2002 2:13 AM Subject: RE: Which FM??? http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/ch3.htm -Original Message- From: Rodd Holman [mailto:[EMAIL PROTECTED]] Sent: Friday, March 01, 2002 1:53 PM To: Multiple recipients of list ORACLE-L Subject: Which FM??? I'm looking for descriptions of the more cryptic columns in some of the V$'s. Anyone know which FM those are in? TIA Rodd = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
