Hi,

I have a session with a query very long (more than 10 hour)
the execution plan seems correct through TOAD :

SELECT SUM (glaa) "AA"
FROM f0911, f55126a
WHERE glco = tdco
AND glpost = 'P'
AND gllt = 'AA'
AND glaa > 0
AND globj >= 100000
AND globj < 800000
AND tdac24 = :b3
AND glco = :b2
AND globj = :b1
AND ((globj = tdobj AND tdobj <> ' ') OR (tdobj = ' '))
AND gldgj >= tddfyj
AND gldgj < tddgj
GROUP BY tdac24, glco, globj

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 53
SORT GROUP BY NOSORT 1 45 53
NESTED LOOPS 1 45
53
TABLE ACCESS BY INDEX ROWID PRDDTA.F55126A 1 22 1
INDEX UNIQUE SCAN PRDDTA.F55126A_PK 48
TABLE ACCESS BY INDEX ROWID PRDDTA.F0911 1 23 52
INDEX RANGE SCAN PRDDTA.F0911_40 22 39


I can see the query there are bind variables
how I can see the value of the bind variables
during the query is running ?

10046 level 12 only give me db file sequential read wait event

How I can find bind variable actually used ?


Thanks in advance

On Jul 11, 1:54 pm, "astalavista" <nob...@nowhere.com> wrote:
> Hi,
>
> I have a session with a query very long (more than 10 hour)
> the execution plan seems correct through TOAD :
>
> SELECT SUM (glaa) "AA"
> FROM f0911, f55126a
> WHERE glco = tdco
> AND glpost = 'P'
> AND gllt = 'AA'
> AND glaa > 0
> AND globj >= 100000
> AND globj < 800000
> AND tdac24 = :b3
> AND glco = :b2
> AND globj = :b1
> AND ((globj = tdobj AND tdobj <> ' ') OR (tdobj = ' '))
> AND gldgj >= tddfyj
> AND gldgj < tddgj
> GROUP BY tdac24, glco, globj
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 53
> SORT GROUP BY NOSORT 1 45 53
> NESTED LOOPS 1 45
> 53
> TABLE ACCESS BY INDEX ROWID PRDDTA.F55126A 1 22 1
> INDEX UNIQUE SCAN PRDDTA.F55126A_PK 48
> TABLE ACCESS BY INDEX ROWID PRDDTA.F0911 1 23 52
> INDEX RANGE SCAN PRDDTA.F0911_40 22 39
>
> I can see the query there are bind variables
> how I can see the value of the bind variables
> during the query is running ?
>
> 10046 level 12 only give me db file sequential read wait event
>
> How I can find bind variable actually used ?
>
> Thanks in advance

Which release of Oracle is this? Report that information to 4
numbers. Also, is the PLAN_TABLE created with the current
definition? We need more information than you've provided in order to
even begin answering your question.


David Fitzjarrell

>
> Which release of Oracle is this? Report that information to 4
> numbers. Also, is the PLAN_TABLE created with the current
> definition? We need more information than you've provided in order to
> even begin answering your question.

release is 9.2.0.6 on AIX
for PLAN_TABLE, I will try tomorrow

On Jul 11, 11:54 am, "astalavista" <nob...@nowhere.com> wrote:
> Hi,
>
> I have a session with a query very long (more than 10 hour)
> the execution plan seems correct through TOAD :
>
> SELECT SUM (glaa) "AA"
> FROM f0911, f55126a
> WHERE glco = tdco
> AND glpost = 'P'
> AND gllt = 'AA'
> AND glaa > 0
> AND globj >= 100000
> AND globj < 800000
> AND tdac24 = :b3
> AND glco = :b2
> AND globj = :b1
> AND ((globj = tdobj AND tdobj <> ' ') OR (tdobj = ' '))
> AND gldgj >= tddfyj
> AND gldgj < tddgj
> GROUP BY tdac24, glco, globj
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 53
> SORT GROUP BY NOSORT 1 45 53
> NESTED LOOPS 1 45
> 53
> TABLE ACCESS BY INDEX ROWID PRDDTA.F55126A 1 22 1
> INDEX UNIQUE SCAN PRDDTA.F55126A_PK 48
> TABLE ACCESS BY INDEX ROWID PRDDTA.F0911 1 23 52
> INDEX RANGE SCAN PRDDTA.F0911_40 22 39
>
> I can see the query there are bind variables
> how I can see the value of the bind variables
> during the query is running ?
>
> 10046 level 12 only give me db file sequential read wait event
>
> How I can find bind variable actually used ?
>
> Thanks in advance

See metalink Note:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw
SQL Traces with Binds and/or Waits generated by EVENT 10046

The level 12 should have the bind info.

http://www.oracle.com/technology/pub...agher_tfm.html looks
kind of interesting.

jg
--
@home.com is bogus.
http://lauren.vortex.com/archive/000256.html

On Wed, 11 Jul 2007 12:10:43 -0700, fitzjarrell@cox.net wrote:

> Which release of Oracle is this? Report that information to 4 numbers.
> Also, is the PLAN_TABLE created with the current definition? We need
> more information than you've provided in order to even begin answering
> your question.

No, we do not need more information. The answer to his question is that
with 10046, level 12, bind variables will be in the trace file. This is
so ever since Oracle7. Unfortunately, tkprof doesn't format bind
variables, so he will have to read the raw trace file. The version is
important only if he's running Oracle 6 or lower versions.

--
http://www.mladen-gogala.com

astalavista wrote:
>
> Hi,
>
> I have a session with a query very long (more than 10 hour)
> the execution plan seems correct through TOAD :
>
> SELECT SUM (glaa) "AA"
> FROM f0911, f55126a
> WHERE glco = tdco
> AND glpost = 'P'
> AND gllt = 'AA'
> AND glaa > 0
> AND globj >= 100000
> AND globj < 800000
> AND tdac24 = :b3
> AND glco = :b2
> AND globj = :b1
> AND ((globj = tdobj AND tdobj <> ' ') OR (tdobj = ' '))
> AND gldgj >= tddfyj
> AND gldgj < tddgj
> GROUP BY tdac24, glco, globj
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 53
> SORT GROUP BY NOSORT 1 45 53
> NESTED LOOPS 1 45
> 53
> TABLE ACCESS BY INDEX ROWID PRDDTA.F55126A 1 22 1
> INDEX UNIQUE SCAN PRDDTA.F55126A_PK 48
> TABLE ACCESS BY INDEX ROWID PRDDTA.F0911 1 23 52
> INDEX RANGE SCAN PRDDTA.F0911_40 22 39
>
> I can see the query there are bind variables
> how I can see the value of the bind variables
> during the query is running ?
>
> 10046 level 12 only give me db file sequential read wait event
>
> How I can find bind variable actually used ?
>
> Thanks in advance

Level 4 is sufficient for bind vars, or v$sql_bind_capture in 10g and
above.
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------

>
> No, we do not need more information. The answer to his question is that
> with 10046, level 12, bind variables will be in the trace file. This is
> so ever since Oracle7. Unfortunately, tkprof doesn't format bind
> variables, so he will have to read the raw trace file. The version is
> important only if he's running Oracle 6 or lower versions.

OK thanks I think I don't see bind variable
because the query is running and
I don't set the event before the query start
am i right ?
I only see wait event: db file sequential read

My question was :
is it possible to see bind variables of a running query ??

Thanks

On Thu, 12 Jul 2007 21:28:27 +0200, "astalavista" <nobody@nowhere.com>
wrote:

>>
>> No, we do not need more information. The answer to his question is that
>> with 10046, level 12, bind variables will be in the trace file. This is
>> so ever since Oracle7. Unfortunately, tkprof doesn't format bind
>> variables, so he will have to read the raw trace file. The version is
>> important only if he's running Oracle 6 or lower versions.
>
>OK thanks I think I don't see bind variable
>because the query is running and
>I don't set the event before the query start
>am i right ?
>I only see wait event: db file sequential read
>
>My question was :
>is it possible to see bind variables of a running query ??
>
>Thanks
>
>
select * from dict where table_name like '%BIND%'
some people will NEVER use the tools presented to them over and over
again.

--
Sybrand Bakker
Senior Oracle DBA

On Jul 12, 12:28 pm, "astalavista" <nob...@nowhere.com> wrote:
> > No, we do not need more information. The answer to his question is that
> > with 10046, level 12, bind variables will be in the trace file. This is
> > so ever since Oracle7. Unfortunately, tkprof doesn't format bind
> > variables, so he will have to read the raw trace file. The version is
> > important only if he's running Oracle 6 or lower versions.
>
> OK thanks I think I don't see bind variable
> because the query is running and
> I don't set the event before the query start
> am i right ?
> I only see wait event: db file sequential read
>
> My question was :
> is it possible to see bind variables of a running query ??
>
> Thanks

Oh, a _running_ query :-)

See http://www.oracleutilities.com/SQLPLus/oradebug.html
http://www.evdbt.com/tools.htm#trclvl12

I believe what you will have to do is enable the trace from another
session, then end the trace to actually have the variables written out
to a file where you can look at them.

See metalink Note:45114.1 for an example of what Sybrand is talking
about.

jg
--
@home.com is bogus.
Someone will always have something better: http://www.thelocal.se/7869/20070712/

On Thu, 12 Jul 2007 21:28:27 +0200, astalavista wrote:


> My question was :
> is it possible to see bind variables of a running query ??

Only in V10, in V$SQL_BIND_CAPTURE. Trace file is written when Oracle
stops executing and starts waiting. Bind variables are written down after
parse and bind phases but before execute phase. If the query is already
executing, it's too late. You might try with process dump from oradebug,
that will probably contain bind variables.

--
http://www.mladen-gogala.com