5 Replies Latest reply on Jul 24, 2008 7:58 AM by Dom Brooks

    Obtain bind vars from a session (oracle 10.2)

    531871
      Hi

      Repost from SQL forum, wher noone answered - so now i'm trying here:

      I am trying to extract BIND vars from the current or last SQL a session has produced:

      The reasin we try to do in in pure sql is that we are outsourced and the DBAs sits in another country. We dont have access to the actual servers (AIX) - thus we cannon run TKRPOF, 10046 traces etc. So we are kind of stuck with pure SQL, and maybe a proxy
      select distinct name, value_string, datatype_string, last_captured, sql_text
      from (
      select rownum rn, DECODE (s.sql_id, NULL, s.prev_sql_id, s.sql_id) sql_id, sql_text
      from v$session s, v$sqltext sq
      where s.sid = :no and
      DECODE (s.sql_hash_value, 0, s.prev_hash_value, s.sql_hash_value) = sq.hash_value and
      DECODE (s.sql_id, NULL, s.prev_sql_id, s.sql_id) = sq.sql_id
      union
      select rownum rn, o.sql_id, sql_text
      FROM v$open_cursor o, v$session s
      WHERE o.saddr = s.saddr AND 
      o.SID = s.SID AND 
      s.sid = :no
      ) a, v$sql_bind_capture b
      where a.sql_id = b.sql_id
      order by last_captured desc nulls last;
      This gives me a "better" result, since it takes the past executions from open cursors along, but I cant figure out if it takes too much if other sessions have executed the same SQL (same hash).

      And since we have only sampled values it seems that it only takes the first execution of a statmets and its binds?

      So ideas appreciated :-)

      Best regards
      Mette

      PS rownum stuff is there, because it rewrites the query when we run it from the report in sql developer otherwise giving us terrible performance (2-3 minutes contra less 1 sec).
        • 1. Re: Obtain bind vars from a session (oracle 10.2)
          636927
          try that http://jokach.com/blog/?p=19
          • 2. Re: Obtain bind vars from a session (oracle 10.2)
            531871
            Hi Smok.

            I dont think the link is about the same . I AM using BINDS - but we have a problem with an application, so we need to see that VALUES of the bind vars in order to troubleshoot. So I'm actially truing to get the content of the calls to Oracle out :-)

            Since the app. is Java - and the code autogenrated through a layer in between, we cannot just display or change the SQL's like that. So we need to take them out another way.

            My second best try is now to use FGA on the involved tables (OK - but lots of data remains there)

            My thrid try is using SQLMonitor as a proxy (OK, but requires that I can trap the process on the machine running the app.)

            best regards
            Mette
            • 3. Re: Obtain bind vars from a session (oracle 10.2)
              601585
              I don't understand why you can't enable 10046 trace and ask the remote DBA to send the trace file.

              Anyway, i don't think you can get Orcacle to capture every bind variables.
              It's too harsh and bad for performance.

              Oracle provides 2 parameters to control bind capture.
              UKJA@ukja102> @para capture
              old   8: and i.ksppinm like '%&1%'
              new   8: and i.ksppinm like '%capture%'
               
              NAME                           VALUE                SES_MODIFIABLE  SYS_MODIFIABLE
              ------------------------------ -------------------- --------------- ------------------------
              DESCRIPTION
              --------------------------------------------------------------------------------------------
              --------------------------------------------------
              _cursor_bind_capture_area_size 2000                 false           immediate
              maximum size of the cursor bind capture area
               
              _cursor_bind_capture_interval  900                  false           immediate
              interval (in seconds) between two bind capture for a cursor
              You can shorten the interval of capture with the risk of performance degradation.
              But even in that case, it still doesn't guarantee that captured bind is what you actually wanted.

              Dion Cho
              • 4. Re: Obtain bind vars from a session (oracle 10.2)
                26741
                If you know the database session, you can do a 10046 level 4 trace on it.

                Just noticed your :
                "we are outsourced and the DBAs sits in another country. We dont have access to the actual servers (AIX) - thus we cannon run TKRPOF, 10046 traces etc"

                Then you are in a bad shape. Can't you get the DBA to co-operate ?

                Message was edited by:
                Hemant K Chitale
                • 5. Re: Obtain bind vars from a session (oracle 10.2)
                  Dom Brooks
                  Are there log levels in the java app, the autogeneration layer or the driver that could include this information?