Re: Bind variable values
Hand, Michael T wrote: Can the value used in bind variables be retrieved from V$ tables? The reason I am interested is I'm trying to get use a subset of the SQL from V$SQLTEXT from one 8.1.7 instance to run a comparative test on another instance. However, most of the SQL contains bind variables. I checked out V$SQL_BIND_DATA but a records had VALUE as NULL. Thanks, Mike Mike, Yes and no. The bind variables are not stored in the SGA, but in the private memory of your server so you cannot see those from another session (I have not checked with MTS, but in practice if you can 'see' only what comes from sessions you cannot pick ...). However, I have managed to catch them in triggers (so, catching bind variables coming from _MY_ session). It requires a bit of juggling with the X$, because there is a join for which you need a cursor number which is left out of the V$ views. It's probably simpler to use event 10046 and extract the values from the trace file. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Bind variable values
you can get bind var data witha 10046 level 4 trace. Hand, Michael T [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/16/2003 11:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Bind variable values Can the value used in bind variables be retrieved from V$ tables? The reason I am interested is I'm trying to get use a subset of the SQL from V$SQLTEXT from one 8.1.7 instance to run a comparative test on another instance. However, most of the SQL contains bind variables. I checked out V$SQL_BIND_DATA but a records had VALUE as NULL. Thanks, Mike This transmission is intended only for use by the addressee(s) named herein and may contain information that is proprietary, confidential and/or legally privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Bind Variable values
Hi If you just want to see the explain plan of a querry with bind variables you don't need the values. just type explain plan for statement and you can read the explain from the plan table(should have been created beforehand) HTH jack Nalla Ravi vvnrk2001@yahoo.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.uk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Bind Variable values [EMAIL PROTECTED] 30-05-2002 13:53 Please respond to ORACLE-L Hi All, I want to get the individual sql_trace from my applicaion as they use loads of bind variables, how to get those values so that I can run explain plan and see them individually? for examploe like event 10046? Thanks for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does 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
RE: Bind Variable values
Ravi - According to Metalink Note:171647.1, There are four levels available when setting up trace with Event 10046 - · Level 1 is the default. This level traces all activities until the trace session is stopped. · Level 4 provides level 1 tracing and displays the entered value for all bind variables. Bind variables are the values that the user enters. The code displays these bind variables as: b1, etc. When level 4 is activated, the substituted value for the bind variable is displayed in the trace file. · Level 8 provides level 1 tracing and displays a list of all database wait events. Database wait events list the reasons if the elapsed time is greater than the CPU time in the tkprof report. · Level 12 provides level 1 tracing in addition to both bind variable substitution and database wait events. All trace modes will include timed statistics information in the trace file. Be warned that the increasing levels cause increasing trace file sizes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 30, 2002 6:53 AM To: Multiple recipients of list ORACLE-L Hi All, I want to get the individual sql_trace from my applicaion as they use loads of bind variables, how to get those values so that I can run explain plan and see them individually? for examploe like event 10046? Thanks for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Bind Variable values
Set the SQL trace event (10046) to level 4 to dump the values of the bind variables. If you can enable SQL trace from within the session, you can use... alter session set events '10046 trace name context forever, level 4'; If you cannot alter the source code or otherwise enable SQL trace from within the session, you can do it from a SYSDBA session: SQL*Plus ORADEBUG SQL oradebug setospid OS-pid-of-server-process SQL oradebug event 10046 trace name context forever, level 4 DBMS_SYSTEM package exec dbms_system.set_ev(sid,serial#, 10046,4,''); I have a PL/SQL procedure named TRCLVL12 posted on my website (http://www.EvDBT.com/tools.htm) which demonstrates the use of the SET_EV procedure. I also have UNIX korn-shell scripts named traceon.sh and traceoff.sh at the same website which demonstrate the use of ORADEBUG. However, be aware that both utilities are setting SQL trace level 12, which is a combination of level 4 (bind variables) and level 8 (display wait event info) and produces huge trace files... Hope this helps... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 7:08 AM Hi If you just want to see the explain plan of a querry with bind variables you don't need the values. just type explain plan for statement and you can read the explain from the plan table(should have been created beforehand) HTH jack Nalla Ravi vvnrk2001@yahoo.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.uk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Bind Variable values [EMAIL PROTECTED] 30-05-2002 13:53 Please respond to ORACLE-L Hi All, I want to get the individual sql_trace from my applicaion as they use loads of bind variables, how to get those values so that I can run explain plan and see them individually? for examploe like event 10046? Thanks for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does 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,
Re: Bind Variable values
Hi Jack, thanks for the response however what I was looking is in a sql query they substitue around 100 bind variables (through application) I can't just substitue them one by one in where condition and run the query rather what I wanted to see like puting sql_trace on if we put set events 10046 name context forever, leve4 , can we also get the values for bind variables? Thanks, ravi --- Jack van Zanen [EMAIL PROTECTED] wrote: Hi If you just want to see the explain plan of a querry with bind variables you don't need the values. just type explain plan for statement and you can read the explain from the plan table(should have been created beforehand) HTH jack Nalla Ravi vvnrk2001@yahoo.To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.uk cc: (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) Sent by: Subject: Bind Variable values [EMAIL PROTECTED] 30-05-2002 13:53 Please respond to ORACLE-L Hi All, I want to get the individual sql_trace from my applicaion as they use loads of bind variables, how to get those values so that I can run explain plan and see them individually? for examploe like event 10046? Thanks for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for
RE: Bind Variable values
Hi Dennis, Thanks for the answer, I just put event leve4, I could not get the bind variables values, rather gave me :b1,:b2 How do I get those values? And another thing is in tkprof values can please explain me about the query and current rows and cpu and elapsed times? my understanding is:query is in consistent mode i.e rollback is that right? if that is the case if there are not active entries in rollback segments, query should give zero right? and current is blocks in current state? am i right? Thank you so much for the clarification. Thanks you Ravi --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Ravi - According to Metalink Note:171647.1, There are four levels available when setting up trace with Event 10046 - · Level 1 is the default. This level traces all activities until the trace session is stopped. · Level 4 provides level 1 tracing and displays the entered value for all bind variables. Bind variables are the values that the user enters. The code displays these bind variables as: b1, etc. When level 4 is activated, the substituted value for the bind variable is displayed in the trace file. · Level 8 provides level 1 tracing and displays a list of all database wait events. Database wait events list the reasons if the elapsed time is greater than the CPU time in the tkprof report. · Level 12 provides level 1 tracing in addition to both bind variable substitution and database wait events. All trace modes will include timed statistics information in the trace file. Be warned that the increasing levels cause increasing trace file sizes. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 30, 2002 6:53 AM To: Multiple recipients of list ORACLE-L Hi All, I want to get the individual sql_trace from my applicaion as they use loads of bind variables, how to get those values so that I can run explain plan and see them individually? for examploe like event 10046? Thanks for your help. Ravi. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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). __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Nalla=20Ravi?= 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: Bind Variable values
Dennis (or anyone else) I remember these values of 1,4,8,12 but the SQLab tuner from quest insists on doing the 10046 trace at level 15, are there any more hidden levels that we don't know of? 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! *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
Re: Bind Variable values
Raj: Level 15 (or level 100 or 200!!) is level 12 only. Anything above level 12 is considered as level 12/ Best Regards, K Gopalakrishnan - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 10:13 PM Dennis (or anyone else) I remember these values of 1,4,8,12 but the SQLab tuner from quest insists on doing the 10046 trace at level 15, are there any more hidden levels that we don't know of? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: Bind Variable values
That explains it ... I thought quest guys knew something that we didn't know about. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 30, 2002 1:09 PM To: Multiple recipients of list ORACLE-L Raj: Level 15 (or level 100 or 200!!) is level 12 only. Anything above level 12 is considered as level 12/ Best Regards, K Gopalakrishnan *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
