Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2008
    Posts
    4

    Unanswered: Oracle SQL trace problem

    Hi,
    Ive set up a sql trace following a particular session and ran tkprof (no options when run, just file1 + file2) on the trace file given.
    The output file I am now looking at has several sql insert commands that look something like this:

    INSERT into LineInfo (line_ID, cd_ID, p_Type) VALUES (:L1, :L2, :L3)

    Im assuming the L1 L2 L3 etc are some kind of passed in parameters, possibly taken from a cursor (?) but I dont really know.

    Id like to know the actual values behind these placeholders; does anyone know how I might do this?

    Thanks!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL_TRACE needs to be enabled at LEVEL=12

    http://www.petefinnigan.com/weblog/a...s/00000999.htm
    I know this site documents how to do it or use GOOGLE

    The substituted values will be in the raw trace file after LEVEL=12 has been enabled.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2008
    Posts
    4
    I have tried running the trace with the following commands:

    exec dbms_system.set_bool_param_in_session(145,107,'tim ed_statistics',true);
    exec dbms_system.set_int_param_in_session(145,107,'max_ dump_file_size',2147483647);
    exec dbms_system.set_ev(145,107,10046,12,'');

    --did stuff here

    exec dbms_system.set_ev(145,107,10046,0,'');

    But still getting the same problem.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >--did stuff here
    If you say so.

    >But still getting the same problem.
    If you say so.

    Step back & put yourself in any readers position.
    You are requesting/expecting folks to debug what they can NOT see.

    One can only assume that the code you posted was running in session SID=145,SERIAL#=107; then again maybe not.

    I am not be paid enough to solve this mystery. It does work if you do it correctly.
    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2008
    Posts
    4
    Well, thanks for you first post!
    Im sorry my reply was not very clear.
    Ill try to explain things a little better even if I am now on my own

    I start up the application I am debugging.
    Next open up Oracle SQL developer, tools -> Monitor Sessions -> write down SID and SERIAL of the session the app is using.

    Then ran the below commands in SQL+

    exec dbms_system.set_bool_param_in_session(145,107,'tim ed_statistics',true);
    exec dbms_system.set_int_param_in_session(145,107,'max_ dump_file_size',2147483647);
    exec dbms_system.set_ev(145,107,10046,12,'');

    145 is the SID and 107 the Serial.

    Then I went to the application I am debugging and performed some tasks.
    After had finished I ran the below line in SQL+

    exec dbms_system.set_ev(145,107,10046,0,'');

    I ran the trc file through tkprof (tkprof filename1 output.txt)
    The output.txt file has a few INSERT commands that look like the below.

    INSERT INTO theTable (column1, column2, column3, column4, column5, column6, column7, column8)
    VALUES
    (:1,:2,:3,:4,:5,:6,:7,:8)

    Id like to know the actual values inserted!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I did not state the values would be in the TKPROF results.
    I thought I clearly stated where you could obtain the desired value.
    What part of The substituted values will be in the raw trace file after LEVEL=12 has been enabled. do you NOT understand?

    While some folks are clue repellent; others are clue resistant!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2008
    Posts
    4
    I repeated the steps I listed above but did not run the trace file through tkprof. Inside the .trc file the insert statements still have the placeholders (:1, :2, :3 etc)

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    from the raw trace file including the INSERT line post 50 - 100 lines above it where the substitution is done & recorded.
    It has been about 18 months since I last did this & I do not recall what specifically to look for.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    /*  Log in as SYS                                  */
    /* To trace a remote session just paste the output */
    /* into your sqlplus session.                      */

    select
    'execute sys.dbms_system.set_ev('|| sid ||','|| serial# ||',10046,12, '''');'
    from v$session
    where username 
    =  '&&username';

    /* to turn off: */
    /* paste the output into your sqlplus session */

    select
    'execute sys.dbms_system.set_sql_trace_in_session('|| sid ||'  ,'|| serial# ||',FALSE);'
    from gv$session
    where  username 
    =  '&&username'
    now goto your raw trace file and the variable values will be there (trust me).

    Also, in 10g there are views which show you the bound variables
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    "Also, in 10g there are views which show you the bound variables."

    Really? Does anyone have the names for these? --=cf

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    one of these i suspect
    Code:
      1* SELECT table_name , COLUMN_NAME FROM DBA_COL_COMMENTS WHERE column_NAME LIKE '%BIND%'
    SQL> /
    
    TABLE_NAME                       COLUMN_NAME
    -------------------------------- --------------------------------
    OPERATOR$                        NEXTBINDNUM
    OPERATOR$                        NUMBIND
    OPBINDING$                       BIND#
    OPANCILLARY$                     PRIMBIND#
    OPANCILLARY$                     BIND#
    OPARG$                           BIND#
    INDOP$                           BIND#
    AUD$                             SQLBIND
    FGA_LOG$                         LSQLBIND
    FGA_LOG$                         SQLBIND
    V_$SESSION                       SQL_TRACE_BINDS
    V_$SQLAREA                       BIND_DATA
    V_$SQLAREA_PLAN_HASH             BIND_DATA
    V_$SQL                           BIND_DATA
    V_$SQL_SHARED_CURSOR             BIND_PEEKED_PQ_MISMATCH
    V_$SQL_SHARED_CURSOR             BIND_UACS_DIFF
    V_$SQL_SHARED_CURSOR             USER_BIND_PEEK_MISMATCH
    V_$SQL_SHARED_CURSOR             BIND_MISMATCH
    V_$ARCHIVE_DEST                  BINDING
    V_$SQL_CURSOR                    BIND_MEM_LOC
    V_$SQL_CURSOR                    BIND_VARS
    V_$SQL_BIND_METADATA             BIND_NAME
    V_$XML_AUDIT_TRAIL               SQL_BIND
    GV_$SQLSTATS                     BIND_DATA
    GV_$SESSION                      SQL_TRACE_BINDS
    GV_$SQLAREA                      BIND_DATA
    GV_$SQLAREA_PLAN_HASH            BIND_DATA
    GV_$SQL                          BIND_DATA
    GV_$SQL_SHARED_CURSOR            BIND_PEEKED_PQ_MISMATCH
    GV_$SQL_SHARED_CURSOR            BIND_UACS_DIFF
    GV_$SQL_SHARED_CURSOR            USER_BIND_PEEK_MISMATCH
    GV_$SQL_SHARED_CURSOR            BIND_MISMATCH
    GV_$ARCHIVE_DEST                 BINDING
    GV_$SQL_CURSOR                   BIND_MEM_LOC
    GV_$SQL_CURSOR                   BIND_VARS
    GV_$SQL_BIND_METADATA            BIND_NAME
    GV_$XML_AUDIT_TRAIL              SQL_BIND
    DBA_OPERATORS                    NUMBER_OF_BINDS
    ALL_OPERATORS                    NUMBER_OF_BINDS
    USER_OPERATORS                   NUMBER_OF_BINDS
    DBA_OPBINDINGS                   BINDING#
    USER_OPBINDINGS                  BINDING#
    ALL_OPBINDINGS                   BINDING#
    DBA_OPANCILLARY                  PRIMOP_BIND#
    DBA_OPANCILLARY                  BINDING#
    USER_OPANCILLARY                 PRIMOP_BIND#
    USER_OPANCILLARY                 BINDING#
    ALL_OPANCILLARY                  PRIMOP_BIND#
    ALL_OPANCILLARY                  BINDING#
    DBA_OPARGUMENTS                  BINDING#
    USER_OPARGUMENTS                 BINDING#
    ALL_OPARGUMENTS                  BINDING#
    DBA_INDEXTYPE_OPERATORS          BINDING#
    USER_INDEXTYPE_OPERATORS         BINDING#
    ALL_INDEXTYPE_OPERATORS          BINDING#
    DBA_AUDIT_TRAIL                  SQL_BIND
    USER_AUDIT_TRAIL                 SQL_BIND
    DBA_AUDIT_STATEMENT              SQL_BIND
    USER_AUDIT_STATEMENT             SQL_BIND
    DBA_AUDIT_OBJECT                 SQL_BIND
    USER_AUDIT_OBJECT                SQL_BIND
    DBA_AUDIT_EXISTS                 SQL_BIND
    DBA_FGA_AUDIT_TRAIL              SQL_BIND
    DBA_COMMON_AUDIT_TRAIL           SQL_BIND
    KU$_LOB_VIEW                     LOBINDEX
    KU$_LOBFRAG_VIEW                 LOBINDEX
    KU$_PIOTLOBFRAG_VIEW             LOBINDEX
    KU$_SUBLOBFRAG_VIEW              LOBINDEX
    KU$_OPANCILLARY_VIEW             BIND_NUM
    KU$_OPBINDING_VIEW               BIND_NUM
    KU$_OPERATOR_VIEW                BINDINGS
    KU$_INDEXOP_VIEW                 BIND_NUM
    DBA_ENABLED_TRACES               BINDS
    WRH$_SQLSTAT                     BIND_DATA
    WRH$_SQLSTAT_BL                  BIND_DATA
    WRI$_SQLSET_PLANS                BINDS_CAPTURED
    WRI$_SQLSET_PLANS                BIND_DATA
    DBA_SQLSET_STATEMENTS            BINDS_CAPTURED
    DBA_SQLSET_STATEMENTS            BIND_DATA
    USER_SQLSET_STATEMENTS           BINDS_CAPTURED
    USER_SQLSET_STATEMENTS           BIND_DATA
    ALL_SQLSET_STATEMENTS            BINDS_CAPTURED
    ALL_SQLSET_STATEMENTS            BIND_DATA
    _ALL_SQLSET_STATISTICS_ONLY      BINDS_CAPTURED
    _ALL_SQLSET_STATEMENTS_PHV       BINDS_CAPTURED
    DBA_HIST_SQLSTAT                 BIND_DATA
    
    86 rows selected.
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •