1 2 Previous Next 20 Replies Latest reply: Jan 18, 2013 1:01 PM by jgarry Go to original post RSS
      • 15. Re: What is my query doing??
        Dom Brooks
        Now it runs the normal 90 minutes and sits idle another 90 minutes before finishing.
        You're almost certainly not idle.
        You're likely either working or waiting.

        As other have posted, you need to trace to see what you're waiting on.

        Also see template tuning threads:
        [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
        [url https://forums.oracle.com/forums/thread.jspa?messageID=1812597]When your query takes too long

        Both threads walk you through how to find out what your session is doing, etc.

        Alternatives to sql trace include real time sql monitoring and ASH, if licensed for diagnostic pack.

        Simply look at V$ACTIVE_SESSION_HISTORY for your SESSION_ID and look at what this session is working on or waiting on.
        • 16. Re: What is my query doing??
          jgarry
          964470 wrote:
          From alert.log after I bounced the instance:
          ...
          Thu Jan 17 11:12:33 2013
          Errors in file /usr/local/oracle/diag/rdbms/ora7/ORA7/trace/ORA7_m000_5898.trc:
          ORA-19815: WARNING: db_recovery_file_dest_size of 4194304000 bytes is 99.43% used, and has 23871488 remaining bytes available.
          ************************************************************************
          You have following choices to free up space from recovery area:
          1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
          then consider changing RMAN ARCHIVELOG DELETION POLICY.
          2. Back up files to tertiary device such as tape using RMAN
          BACKUP RECOVERY AREA command.
          3. Add disk space and increase db_recovery_file_dest_size parameter to
          reflect the new space.
          4. Delete unnecessary files using RMAN DELETE command. If an operating
          system command was used to delete files, then use RMAN CROSSCHECK and
          DELETE EXPIRED commands.
          ************************************************************************
          Thu Jan 17 11:18:16 2013
          Starting background process SMCO
          Thu Jan 17 11:18:16 2013
          SMCO started with pid=39, OS id=6978
          Thu Jan 17 12:57:29 2013
          Thu Jan 17 12:57:29 2013
          Thread 1 advanced to log sequence 33272 (LGWR switch)
          Current log# 2 seq# 33272 mem# 0: /usr/local/oracle/oradata/ORA7/redo02.log
          Thread 1 advanced to log sequence 33273 (LGWR switch)
          Current log# 3 seq# 33273 mem# 0: /usr/local/oracle/oradata/ORA7/redo03.log
          Thread 1 cannot allocate new log, sequence 33274
          Checkpoint not complete
          Current log# 3 seq# 33273 mem# 0: /usr/local/oracle/oradata/ORA7/redo03.log
          Thread 1 advanced to log sequence 33274 (LGWR switch)
          Current log# 1 seq# 33274 mem# 0: /usr/local/oracle/oradata/ORA7/redo01.log
          Thread 1 cannot allocate new log, sequence 33275
          Checkpoint not complete
          Current log# 1 seq# 33274 mem# 0: /usr/local/oracle/oradata/ORA7/redo01.log
          Thread 1 advanced to log sequence 33275 (LGWR switch)
          Current log# 2 seq# 33275 mem# 0: /usr/local/oracle/oradata/ORA7/redo02.log
          Thu Jan 17 12:57:41 2013
          Thread 1 cannot allocate new log, sequence 33276
          Checkpoint not complete
          Current log# 2 seq# 33275 mem# 0: /usr/local/oracle/oradata/ORA7/redo02.log
          Thread 1 advanced to log sequence 33276 (LGWR switch)
          Current log# 3 seq# 33276 mem# 0: /usr/local/oracle/oradata/ORA7/redo03.log
          Thread 1 cannot allocate new log, sequence 33277
          Checkpoint not complete
          Current log# 3 seq# 33276 mem# 0: /usr/local/oracle/oradata/ORA7/redo03.log
          Thread 1 advanced to log sequence 33277 (LGWR switch)
          Current log# 1 seq# 33277 mem# 0: /usr/local/oracle/oradata/ORA7/redo01.log
          Thread 1 cannot allocate new log, sequence 33278
          Checkpoint not complete
          Current log# 1 seq# 33277 mem# 0: /usr/local/oracle/oradata/ORA7/redo01.log
          Thread 1 advanced to log sequence 33278 (LGWR switch)
          Current log# 2 seq# 33278 mem# 0: /usr/local/oracle/oradata/ORA7/redo02.log
          Thread 1 cannot allocate new log, sequence 33279
          Checkpoint not complete

          Trace files were not filling it up too much. The db was running in archivelogmode at one point. I cleaned up old trace files and rman backups.

          Will setting the db_recovery_file_dest_size to something larger than 4000M help?
          How did you clean up the old rman backups? As #4 in the warning above said, you need to tell Oracle about it with RMAN commands if you simply deleted the files with OS commands.

          If you are normally switching logs 6+ times a minute and getting checkpoint not complete errors, you have some redo sizing to do. I would expect you would have some waits showing about that.
          • 17. Re: What is my query doing??
            jgarry
            964470 wrote:
            I've done some tracing on a questionable query. I've found the following errors after using tkprof to examine the trace file:

            error during execute of EXPLAIN PLAN statement
            ORA-00904: "SDO_GEOR_DEF"."GETSQLTEXT": invalid identifier

            error during execute of EXPLAIN PLAN statement
            ORA-00942: table or view does not exist

            error during execute of EXPLAIN PLAN statement
            ORA-00907: missing right parenthesis

            Does it seem odd that I would be getting explain plan errors while running an insert?
            I think that means you don't have privileges? Try SELECT COUNT(*) FROM V$SQL_PLAN; and see if you get that 942. Maybe a spatial person knows about that SDO_GEOR_DEF error.
            • 18. Re: What is my query doing??
              967473
              Thank you for the feedback. I did remove the back files with UNIX commands (rm). I will see what I can find to tell RMAN about my manual deletes.

              I have three redo logs, each one is 51MB. I will be churning this much data through the dabatase each day. Is there a 'best-practice' for redo resizing?

              Thanks again.
              • 19. Re: What is my query doing??
                sb92075
                964470 wrote:
                Thank you for the feedback. I did remove the back files with UNIX commands (rm). I will see what I can find to tell RMAN about my manual deletes.

                I have three redo logs, each one is 51MB. I will be churning this much data through the dabatase each day. Is there a 'best-practice' for redo resizing?
                Yes, the "optimal" goal is for the REDO log file switch about 4 times an hour.
                Two alternative ways can be implemented.
                One is to size the REDO files appropriate based upon type DML activity.
                The other is to "oversize" the REDO files & programtically switch every 15 minutes.
                • 20. Re: What is my query doing??
                  jgarry
                  I'm one of those who considers "best practice" an oxymoron - it incorrectly assumes too much about how similar your system is to others.

                  That said, see the docs about v$instance_recovery, and search for that term here, there was a discussion recently.

                  That said, size them to switch every 15 minutes during heavy load (one rule of thumb used when no SLA is made). Watch what waits happen before and after (statspack or AWR). You can get close by figuring out how often your redo switches (either from the alert log or search for scripts to figure it out), and multiplying their size by the amount to make it 15 minutes. Some systems are so heavily loaded that 15 minutes isn't really attainable. Some systems change radically when bad sql is fixed or requirements change. Some systems have the max load in the middle of the night so they don't care if things are waiting. It depends.
                  1 2 Previous Next