RE: Trace output
Title: RE: Trace output Satheesh, Row source operation is the run time execution plan. The execution plan is added when you run tkprof and you use parameter explain=user/password Connect to ORACLE and issue EXPLAIN PLAN and this is theoretical plan. In version 9.2 those two plans can differ. The reason for that is how CBO deals with bind variables in explain plan and when statement is optimized for real execution. For explain plan you don't need to supply any values for bind variables. But when CBO prepares the runtime execution plan (here comes the quote from Database Performance Tuning Guide and Reference 9.2) quote it peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values. /quote So in Row source operation you see the actual plan while in explain plan you see only theoretical plan that doesn't take into account the values for bind variables. That's the reason why those two plans can differ. There are many cases when this can cause problems because the existing plan is not optimal for subsequent execution. Regards, Joze -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, January 23, 2004 5:59 AM To: Multiple recipients of list ORACLE-L Subject: Trace output Hi, I am generating the trace using the event 10046, level 12. In the trace file I am seeing Row source operation following by execution plan. What is the different between these 2, as I am seeing diffent execution plan for both of them? Thanks and Regards, Satheesh Babu.S DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: HOTSOS Conference
Title: RE: HOTSOS Conference I will be there as well primarily listening and also speaking. It is a great idea for a list dinner. Joze -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 06, 2004 2:59 AM To: Multiple recipients of list ORACLE-L Subject: HOTSOS Conference While perusing the HOTSOS site, I noticed that the deadline for the discounted registration for the HOTSOS conferences ends after tomorrow. If you're thinking of going, you may want to check it out. Along those same lines, how many listers will be there? We could get together on Tuesday evening for dinner/drinks if any are interested. Possibly some of you with Dallas connection can recommend a suitable location. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: STATSPACK interpretation
Title: STATSPACK interpretation
Using
dbms_application_info package also causes that there are several versions of
same statement - but theyshare same execution plan. You cancheck if
this is the case by queryingv$sqlarea (module and action
columns).
Regards,
Joze
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003
9:39 PMTo: Multiple recipients of list ORACLE-LSubject:
STATSPACK interpretation
We recently experienced a crash on our prod
datewarehouse running 9.2.0.2 on AIX 4.3.3. The cause of the crash was 4031 errors
generated by background processes
(Oracle support has confirmed there is a bug involved), however,
since that crash occurred, a certain
nightly batch job has slowed to a crawl.
Trying to recreate what has happened, I
came across this in the STATSPACK report. The interval for this report is 30
minutes.
Is it telling me that I have 746 versions
of this call eating up 400+ mb at the time of the snapshot? Why would that be?
The procedure in question uses bind
variables.
SQL ordered by Sharable Memory for DB: DSSP
Instance: DSSP Snaps: 3309 -3310 - End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions %
Total Hash Value ---
483,580,268
57 411.8 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS
(:1,:2,:3,:4) ; END;
-
SQL ordered by Version Count for DB: DSSP
Instance: DSSP Snaps: 3309 -3310 - End Version Count
Threshold: 20
Version Count Executions Hash
Value
746
57 539672786 Module: [EMAIL PROTECTED] (TNS V1-V3) BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4)
; END;
Jeffery D Thomas DBA Thomson Information
Services Thomson, Inc.
Email: [EMAIL PROTECTED]
Indy DBA Master Documentation available
at: http://gkmqp.tce.com/tis_dba
RE: for security patches - going to 9.2.0.4
Title: RE: for security patches - going to 9.2.0.4 Paula, I had also bad experience when we upgraded one of our applications from 8.1.7 to 9.2.0.1 There were some queries that executed significantly slower then on 8.1.7. What we have done was: 1. We gathered system statistics with dbms_system.gather_system_stats. This helps CBO to know on what kind of HW configuration it is running on and also turns on the new cpu costing model. The result were better execution plans. Please read Metalink Note: 153761.1 about system stats. I had also replied to several questions regarding system stats on Metalink and I don't know for a case when gathering system stats caused performance degradation. 2. We have installed the latest patch 9.2.0.4 which among other bugs fixes also some optimizer bugs - some of them are still there ;) . This also solved other performance problems and things were again under control. 3. With manual setting system statistics (see the procedures in dbms_stats package to do that) one can achieve the same execution plans as on 8.1.7 version. If the mreadtim is 1.2 times higher than the sreadtim with a multiblockreadcount (mbrc) set to 8, then the I/O cost part will be equivalent to the Oracle8i I/O cost. I haven't tested this yet (there was no need to do that and besides I currently don't have access to proper environment to do that) but it sounds like setting the OPTIMIZER_INDEX_COST_ADJ in 8i to lower values then default. 4. There are several settings related to CBO's behavior that have changed the default value from false to true in 9iR2. Those are: _b_tree_bitmap_plans = true _complex_view_merging = true _index_join_enabled = true _new_initial_join_orders = true _ordered_nested_loop = true _push_join_predicate = true _push_join_union_view = true _table_scan_cost_plus_one = true _unnest_subquery = true We had several times problems with bitmap conversions for b-tree indexes, so we changed _b_tree_bitmap_plans back to false as it was in 8i. The changed plans without this feature were executing faster. Regards, Joze -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 12, 2003 10:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: for security patches - going to 9.2.0.4 I was just wondering if setting compatible back to 8.1.7 for the CBO reasons was still necessary? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul Drake Sent: Friday, December 12, 2003 4:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: for security patches - going to 9.2.0.4 Paula, since you use the export utility - patch to 9.2.0.4. a full export will throw an error in 9.2.0.3, on W2K and on RH 8.0. regarding security, I haven't yet applied the 8.1.7.4.13 patchset (its not yet available) or the SSL-related issues in Oracle Security Alert #62. but the patchset 9.2.0.4.0 is still a pre-requisite for this, as the one-off patchset for 9.2.0.3.0 is not yet available. As far as setting compatible back to 8.1.7, the key thing is not whether you have locally managed tablespaces, but a locally managed _SYSTEM_ tablespace. just wanted to make that clear. Paul [EMAIL PROTECTED] wrote: That is the case. I do have tablespaces that are locally managed - I did the migrate with compatible set to 8.1.7. I did startup the database. However, my system tablespace is dictionary managed - all others are not. The migration worked. I have large databases and have been using Oracle's migration utility from 7 - 8 and 8 - 9. Yes, it is quirky and tricky but better than export/import on large databases. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Paul Drake Sent: Thursday, December 11, 2003 7:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: for security patches - going to 9.2.0.4 Paula, I hope that you are just confused. AFAIK, if you have created a database with a locally managed system tablespace, that you cannot set compatible to anything lower than 9.0.1. Ok, you can set it, but oracle will complain during instance startup and you won't have a database instance to attach to. But this might be a myth of mine, its awhile since I last read the upgrade/migration guide. I can see setting the init.ora parameter optimizer_features_enable = 8.1.7 if the 9.2 CBO acts quite differently from its older brother did, back in 8.1.7. But compatible? I seriously doubt it. If you migrated your db from 8.1.7 to 9.2 and the system tablespace is still dictionary managed - that is a completely different matter. I've been lucky enough that most dbs were small enough to just use exp/imp and move data into a clean, newly created db. Paul [EMAIL PROTECTED] wrote: Guys, I saved all of your writing including Todd Boxx, Richard Foote, Wolfgang... about issues with 9.2.0.4. We are currently on 9.2.0.3 and I understand (although have not hit it yet) that in this
RE: Finding SID of current session
Title: RE: Finding SID of current session
You can use also this approach:
select * from v$session_event
where sid = (select sid from v$session where audsid = userenv('SESSIONID'))
Regards, Joze
-Original Message-
From: George Leonard [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 02, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L
Subject: Finding SID of current session
Hi there all.
How can I find the sid of the current session,
I got a pl/sql procedure that needs to do a select from v$session_event
but only want to do it for it's own session so I need to know this for
the where clause ?
George
__
George Leonard
Oracle Database Administrator
Professional Services (Oracle Business Unit)
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Cell: (+27) 82 655 2466
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web: http://www.didata.co.za
You Have The Obligation to Inform One Honestly of the risk,
And As a Person You Are Committed to Educate Yourself to the Total Risk
In Any Activity!
Once Informed Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
This e-mail is sent on the Terms and Conditions that can be accessed by Clicking on this link http://www.vodacom.net/legal/email.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: George Leonard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: ** find whether table or index being accessed
Daniel, it will work but indexes are present in buffer cache also because of updates. The only possibility is to store each index in questionin separate tablespace and monitor the i/o. If number of reads will be equal or little bit greaterthan thenumber of writes than this is a candidate. If number of reads is significantly bigger from number of writes then this index is used for speeding up the access. The answer is: you will never be 100% sure. Regards, Joze -Original Message-From: Daniel Fink [mailto:[EMAIL PROTECTED]Sent: Tuesday, November 18, 2003 10:34 PMTo: Multiple recipients of list ORACLE-LSubject: Re: ** find whether table or index being accessedThis is just an idea, so please test it thoroughly (and then test it again!) Any and all comments (including "Are you brain-dead, Dan?") are welcome. How about periodically sampling v$bh for index segment headers? This assumes that any index access reads the header (true/false?) for the statement using the index. I'd set the sample frequency fairly high (several times a day sounds reasonable) and monitor any impact. This will not show every index that is used, as one could be used and flushed from the cache between samples. However, I think it would be fairly likely to catch the ones really in use. Of course, under no circumstances remove indexes on primary keys, unique constraints or foreign keys, even if they don't show up. Daniel Fink A Joshi wrote: Looking to see if any statement has accessed the index in say 30 days. So basically : "how often index blocks are being read". So I can decide to drop unused indexes. TThanks Daniel for your help. Daniel Fink [EMAIL PROTECTED] wrote: Are you looking to see if statements are using indexes or how often index blocks are being read? Daniel Fink A Joshi wrote: Hi, I had sent this some time back but got no answer for version 8.1.7. For table I understand auditing is an option. What about for index? Thank You A Joshi [EMAIL PROTECTED] wrote: Hi, Is there an easy way to find out if a table or an index is being used. I mean short of going thru all code or keeping looking at v$sqlarea. I mean even if code is covered there are always ad hoc SQL queries etc. Same for other objects like views etc. Is there a place where oracle stores objects accessed and any other related info. Thanks Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
RE: LF and CR (chr(10)||chr(13)) problem
The solution is simple. The correct sequence of characters is not chr(10)||chr(13) but rather chr(13)||chr(10). This is obviously changed by notepad. regards, Joze -Original Message- Sent: Friday, November 14, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Hi, Currently I am working on a project that requires me to transfer Customer Master Record data into SAP R/3 system from my oracle database (9.2.0.4 running on Solaris 5.9). The text file (contains LF and CR) generated by PLSQL or Oracle reporter builder could not be loaded into the SAP database (running on the same version of oracle database on W2K). It breaks on the CR. However, when I open the text file by notepad, do nothing, just simply save the file, re-run the data load, then everything works fine. Does anyone know anything about this? Please help. Thanks in advance. Kitty Luo Oracle DBA, OCP Onlane Inc. www.onlane.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kitty Luo INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Joze Senegacnik INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: LF and CR (chr(10)||chr(13)) problem
Title: RE: LF and CR (chr(10)||chr(13)) problem Then you should use an editor that is capable to show file contents in hex. Or you can use a program that compares two files - the original and the one transformed by notepad and see the difference. Regards, Joze -Original Message- From: Kitty Luo [mailto:[EMAIL PROTECTED]] Sent: Friday, November 14, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: LF and CR (chr(10)||chr(13)) problem Joze, It's not that simply. I already tried all the possible combination: chr(13)||chr(10) , chr(10)||chr(13), chr(13) , chr(10)||chr(10)... Thanks for your input anyway. Regards, Kitty -Original Message- Sent: Friday, November 14, 2003 6:09 AM To: Multiple recipients of list ORACLE-L The solution is simple. The correct sequence of characters is not chr(10)||chr(13) but rather chr(13)||chr(10). This is obviously changed by notepad. regards, Joze -Original Message- Sent: Friday, November 14, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Hi, Currently I am working on a project that requires me to transfer Customer Master Record data into SAP R/3 system from my oracle database (9.2.0.4 running on Solaris 5.9). The text file (contains LF and CR) generated by PLSQL or Oracle reporter builder could not be loaded into the SAP database (running on the same version of oracle database on W2K). It breaks on the CR. However, when I open the text file by notepad, do nothing, just simply save the file, re-run the data load, then everything works fine. Does anyone know anything about this? Please help. Thanks in advance. Kitty Luo Oracle DBA, OCP Onlane Inc. www.onlane.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kitty Luo INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Joze Senegacnik INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Kitty Luo INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
