7 Replies Latest reply: Apr 1, 2013 11:09 AM by user6233945 RSS

    SQL Trace is not generating file nor it is showing explain plan

    user6233945
      This is really strange. I have set the trace in database level and then also in session level. Even after I run a select statement no file is generated in USER_DUMP_DEST! Nor the sql plan is showing up!!!
      SQL> show parameter trace

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_trace integer 0
      sec_protocol_error_trace_action string TRACE
      sql_trace boolean TRUE
      trace_enabled boolean TRUE
      tracefile_identifier string

      SQL> ALTER session SET SQL_TRACE = true;

      Session altered.

      SQL> select instance_name from v$instance;

      INSTANCE_NAME
      ----------------
      lawson9t

      SQL>
      This neither created a file in the USER_DUMP_DEST nor it showed up the explain plan on the screen!

      Same thing when I tried in a different machine I got this

      SQL> select instance_name from v$instance;

      INSTANCE_NAME
      ----------------
      lawson9p


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1936013931

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

      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
      |

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

      | 0 | SELECT STATEMENT | | 100 | 5700 | 0 (0)| 00:00
      :01 |

      | 1 | MERGE JOIN CARTESIAN | | 100 | 5700 | 0 (0)| 00:00
      :01 |

      | 2 | MERGE JOIN CARTESIAN| | 1 | 57 | 0 (0)| 00:00
      :01 |

      |* 3 | FIXED TABLE FULL | X$KSUXSINST | 1 | 23 | 0 (0)| 00:00
      :01 |

      | 4 | BUFFER SORT | | 1 | 34 | 0 (0)| 00:00
      :01 |

      |* 5 | FIXED TABLE FULL | X$KVIT | 1 | 34 | 0 (0)| 00:00
      :01 |

      | 6 | BUFFER SORT | | 100 | | 0 (0)| 00:00
      :01 |

      | 7 | FIXED TABLE FULL | X$QUIESCE | 100 | | 0 (0)| 00:00
      :01 |

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


      Predicate Information (identified by operation id):
      ---------------------------------------------------

      3 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
      5 - filter("KVITTAG"='kcbwst')


      Statistics
      ----------------------------------------------------------
      6 recursive calls
      2 db block gets
      0 consistent gets
      0 physical reads
      0 redo size
      537 bytes sent via SQL*Net to client
      524 bytes received via SQL*Net from client
      2 SQL*Net roundtrips to/from client
      2 sorts (memory)
      0 sorts (disk)
      1 rows processed

      So what I am doing wrong in the first server?
        • 1. Re: SQL Trace is not generating file nor it is showing explain plan
          Dom Brooks
          Are you getting confused between setting sql_trace on and setting [url http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm]autotrace on (or 'set autotrace trace explain' judging by the output/behavour described?
          • 2. Re: SQL Trace is not generating file nor it is showing explain plan
            user6233945
            You are absolutely right.
            In one server I had autotrace and in other sql_trace!!!!My bad.
            But this is how it started.

            I have an Oracle9i database where user wanted to put sql_trace. I did that. But it is not creating the file. Let me give you the parameters.
            SQL> show parameter trace

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            log_archive_trace integer 0
            oracle_trace_collection_name string
            oracle_trace_collection_path string ?/otrace/admin/cdf
            oracle_trace_collection_size integer 5242880
            oracle_trace_enable boolean FALSE
            oracle_trace_facility_name string oracled
            oracle_trace_facility_path string ?/otrace/admin/fdf
            sql_trace boolean TRUE
            trace_enabled boolean TRUE
            tracefile_identifier string KYOUNG

            SQL> show parameter user_dump_dest

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            user_dump_dest string /oracle/admin/itst50/udump
            SQL> show parameter timed_

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            timed_os_statistics integer 0
            timed_statistics boolean TRUE

            SQL> show parameter max_dump_file_size

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            max_dump_file_size string UNLIMITED



            Now even if I do a delete or select no file is being created in /oracle/admin/itst50/udump.
            • 3. Re: SQL Trace is not generating file nor it is showing explain plan
              sb92075
              post results from following OS command

              ls -ltr /oracle/admin/itst50/udump/ | tail -20
              • 4. Re: SQL Trace is not generating file nor it is showing explain plan
                user6233945
                itst50--/oracle/admin/itst50/udump>> ls -ltr /oracle/admin/itst50/udump/ | tail -20
                -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_17844.trc
                -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_18835.trc
                -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_5956.trc
                -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_3090.trc
                -rw-r----- 1 oracle dba 460 Aug 3 2009 itst50_ora_15931.trc
                -rw-r----- 1 oracle dba 458 Aug 3 2009 itst50_ora_5593.trc
                -rw-r----- 1 oracle dba 458 Oct 12 2009 itst50_ora_9069.trc
                -rw-r----- 1 oracle dba 460 Oct 12 2009 itst50_ora_10698.trc
                -rw-r----- 1 oracle dba 460 Oct 15 2009 itst50_ora_15232.trc
                -rw-r----- 1 oracle dba 458 Oct 15 2009 itst50_ora_3598.trc
                -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_6636.trc
                -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_3555.trc
                -rw-r----- 1 oracle dba 458 Jun 15 2011 itst50_ora_5305.trc
                -rw-r----- 1 oracle dba 460 Nov 19 2011 itst50_ora_10247.trc
                -rw-r----- 1 oracle dba 458 Nov 20 2011 itst50_ora_2474.trc
                -rw-r----- 1 oracle dba 460 Feb 16 2012 itst50_ora_15321.trc
                -rw-r----- 1 oracle dba 458 Feb 16 2012 itst50_ora_3510.trc
                -rw-rw-rw- 1 oracle dba 1488 Feb 19 2012 sbtio.log
                -rw-r----- 1 oracle dba 3781 Feb 28 08:31 itst50_ora_12298.trc
                -rw-r----- 1 oracle dba 3779 Feb 28 09:33 itst50_ora_7258.trc
                • 5. Re: SQL Trace is not generating file nor it is showing explain plan
                  sb92075
                  user6233945 wrote:
                  itst50--/oracle/admin/itst50/udump>> ls -ltr /oracle/admin/itst50/udump/ | tail -20
                  -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_17844.trc
                  -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_18835.trc
                  -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_5956.trc
                  -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_3090.trc
                  -rw-r----- 1 oracle dba 460 Aug 3 2009 itst50_ora_15931.trc
                  -rw-r----- 1 oracle dba 458 Aug 3 2009 itst50_ora_5593.trc
                  -rw-r----- 1 oracle dba 458 Oct 12 2009 itst50_ora_9069.trc
                  -rw-r----- 1 oracle dba 460 Oct 12 2009 itst50_ora_10698.trc
                  -rw-r----- 1 oracle dba 460 Oct 15 2009 itst50_ora_15232.trc
                  -rw-r----- 1 oracle dba 458 Oct 15 2009 itst50_ora_3598.trc
                  -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_6636.trc
                  -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_3555.trc
                  -rw-r----- 1 oracle dba 458 Jun 15 2011 itst50_ora_5305.trc
                  -rw-r----- 1 oracle dba 460 Nov 19 2011 itst50_ora_10247.trc
                  -rw-r----- 1 oracle dba 458 Nov 20 2011 itst50_ora_2474.trc
                  -rw-r----- 1 oracle dba 460 Feb 16 2012 itst50_ora_15321.trc
                  -rw-r----- 1 oracle dba 458 Feb 16 2012 itst50_ora_3510.trc
                  -rw-rw-rw- 1 oracle dba 1488 Feb 19 2012 sbtio.log
                  -rw-r----- 1 oracle dba 3781 Feb 28 08:31 itst50_ora_12298.trc
                  -rw-r----- 1 oracle dba 3779 Feb 28 09:33 itst50_ora_7258.trc
                  >
                  itst50--/oracle/admin/itst50/udump>> ls -ltr /oracle/admin/itst50/udump/ | tail -20
                  -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_17844.trc
                  -rw-r----- 1 oracle dba 460 Jan 15 2009 itst50_ora_18835.trc
                  -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_5956.trc
                  -rw-r----- 1 oracle dba 458 Jan 20 2009 itst50_ora_3090.trc
                  -rw-r----- 1 oracle dba 460 Aug 3 2009 itst50_ora_15931.trc
                  -rw-r----- 1 oracle dba 458 Aug 3 2009 itst50_ora_5593.trc
                  -rw-r----- 1 oracle dba 458 Oct 12 2009 itst50_ora_9069.trc
                  -rw-r----- 1 oracle dba 460 Oct 12 2009 itst50_ora_10698.trc
                  -rw-r----- 1 oracle dba 460 Oct 15 2009 itst50_ora_15232.trc
                  -rw-r----- 1 oracle dba 458 Oct 15 2009 itst50_ora_3598.trc
                  -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_6636.trc
                  -rw-r----- 1 oracle dba 458 Oct 26 2010 itst50_ora_3555.trc
                  -rw-r----- 1 oracle dba 458 Jun 15 2011 itst50_ora_5305.trc
                  -rw-r----- 1 oracle dba 460 Nov 19 2011 itst50_ora_10247.trc
                  -rw-r----- 1 oracle dba 458 Nov 20 2011 itst50_ora_2474.trc
                  -rw-r----- 1 oracle dba 460 Feb 16 2012 itst50_ora_15321.trc
                  -rw-r----- 1 oracle dba 458 Feb 16 2012 itst50_ora_3510.trc
                  -rw-rw-rw- 1 oracle dba 1488 Feb 19 2012 sbtio.log
                  -rw-r----- 1 oracle dba 3781 Feb 28 08:31 itst50_ora_12298.trc
                  -rw-r----- 1 oracle dba 3779 Feb 28 09:33 itst50_ora_7258.trc

                  since no new file has been created in this directory for more than 1 MONTH,
                  I suspect you are looking in the wrong folder.

                  consider using the find command to locate recent, new *trc files owned by OS user oracle.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                  • 6. Re: SQL Trace is not generating file nor it is showing explain plan
                    user6233945
                    This database was rarely used.
                    I saw that the alert.log for this server was screwed up too. I have fixed that. I wonder if there is any problem in the database itself.
                    itst50--/oracle/admin/itst50/bdump>> sqlplus /nolog

                    SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 1 10:07:03 2013

                    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

                    SQL> connect / as sysdba
                    Connected.
                    SQL> set autotrace on
                    ERROR:
                    ORA-00980: synonym translation is no longer valid


                    SP2-0611: Error enabling EXPLAIN report

                    I have checked bdump, cdump and udump and there is no new trc file.
                    • 7. Re: SQL Trace is not generating file nor it is showing explain plan
                      sb92075
                      user6233945 wrote:
                      This database was rarely used.
                      I saw that the alert.log for this server was screwed up too. I have fixed that. I wonder if there is any problem in the database itself.
                      itst50--/oracle/admin/itst50/bdump>> sqlplus /nolog

                      SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 1 10:07:03 2013

                      Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

                      SQL> connect / as sysdba
                      Connected.
                      SQL> set autotrace on
                      ERROR:
                      ORA-00980: synonym translation is no longer valid


                      SP2-0611: Error enabling EXPLAIN report

                      I have checked bdump, cdump and udump and there is no new trc file.
                      [oracle@localhost ~]$ oerr ora 980
                      00980, 00000, "synonym translation is no longer valid"
                      // *Cause: A synonym did not translate to a legal target object. This 
                      //         could happen for one of the following reasons:
                      //         1. The target schema does not exist.
                      //         2. The target object does not exist.
                      //         3. The synonym specifies an incorrect database link.
                      //         4. The synonym is not versioned but specifies a versioned
                      //            target object.
                      // *Action: Change the synonym definition so that the synonym points at
                      //          a legal target object.
                      [oracle@localhost ~]$ oerr sp2 613
                      00613, 0, "Unable to verify PLAN_TABLE format or existence\n"
                      // *Cause:  An AUTOTRACE command was issued by a user with insufficient
                      //          privileges, or who did not have a PLAN_TABLE.
                      // *Action: Make sure the user has been granted the PLUSTRACE role,
                      //          and that a PLAN_TABLE has been created for the user.