Loading ...
Sorry, an error occurred while loading the content.

Re: rebuild Indexes & analyze Tables takes to long suddenly

Expand Messages
  • Henry Poras <hporas@attbi.com>
    Bert, First, I would try and find where the time is going by looking at v$session_event, v$session_wait tables. There are a bunch of good articles on how to
    Message 1 of 6 , Mar 4, 2003
    • 0 Attachment
      Bert,

      First, I would try and find where the time is going by looking at
      v$session_event, v$session_wait tables. There are a bunch of good
      articles on how to use these tables. One is at www.orapub.com
      (Response Time Analysis for Oracle Based Systems). If you need more
      information try slapping on a 10046 trace (see www.hotsos.com). These
      should tell you where the system is spending its time.

      If you need some more detail on these techniques, please post.

      Henry


      --- In psftdba@yahoogroups.com, BL Barske <BL.Barske@D...> wrote:
      >
      > We had a script to rebuild all the indexes and afterwards to
      reanalyse the tables on our (several) PS databases (HR/FIN/SA).
      > (SA 7.60 / PT 7.58 / Oracle 8.05 & HR 7.51 / PT 7.57 / Oracle
      8.05 & FS 7.52 / PT 7.55 / Oracle 8.05)
      >
      > We ran it every two weeks.
      >
      > See scripts below.
      >
      > Normally the both SQL scripts (after setting the VERYBIGROLLBACK
      segment ON-LINE) took about 30 min/SID.
      >
      >
      > Now, since ... after 12 hours the rebuild indexes is still busy.
      (Even on a test database as copy of production on a standalone server
      with one user)
      > When I run it in a older copy of the database (same number of
      indexes and tables, about 2% less data) it still takes only an hour
      for both scripts.
      >
      > Now I have build from ApplicationDesigner a script for all the
      records to drop en build the indexes.
      > After that I gave the command "execute DBMS_UTILITY.ANALYZE_SCHEMA
      ('SYSADMPS', 'COMPUTE');"
      > and totaly it costs about 4 hours to complete
      >
      > Why takes it suddenly so long to perform the tasks ?
      >
      > Bert Barskè
      > Drenthe College (NL)
      >
      >
      ======================================================================
      > REM An AT command starts the next single row SID.bat to optimize
      >
      > RUN_DB_OPTIMIZE.BAT SYSTEM <pssword> <SID>
      >
      >
      ======================================================================
      > REM The batch RUN_DB_OPTIMIZE.BAT that starts the 4 SQL
      >
      > D:\ORANT\BIN\SQLPLUS %1/%2@%3
      @D:\DBA_BEHEER\UTILS\RBS_SWITCH_TO_BIG.SQL
      > D:\ORANT\BIN\SQLPLUS %1/%2@%3
      @D:\DBA_BEHEER\HERINDEXEER\805_REBUILD_ALL_INDEX.SQL
      > D:\ORANT\BIN\SQLPLUS %1/%2@%3
      @D:\DBA_BEHEER\HERINDEXEER\805_ANALYZE_ALL_TABLES.SQL
      > D:\ORANT\BIN\SQLPLUS %1/%2@%3
      @D:\DBA_BEHEER\UTILS\RBS_SWITCH_TO_SMALL.SQL
      >
      >
      ======================================================================
      > -- REBUILD ALL INDEXES -- 805_REBUILD_ALL_INDEX.SQL --
      >
      > DECLARE
      > CURSOR CurIndex IS SELECT TABLE_OWNER, INDEX_NAME
      > FROM ALL_INDEXES
      > WHERE NOT (TABLE_OWNER='SYSTEM' OR
      TABLE_OWNER='SYS') AND
      > NOT
      (TABLESPACE_NAME='SYSTEM') AND
      > TEMPORARY='N';
      > SqlStatement VARCHAR2(200);
      > Teller INTEGER;
      > PROCEDURE ExecSql (strSQL IN VARCHAR2) IS
      > CursorName INTEGER;
      > NumRows INTEGER;
      > BEGIN
      > CursorName := dbms_sql.open_cursor;
      > dbms_sql.parse (CursorName, strSQL, dbms_sql.v6);
      > NumRows := dbms_sql.Execute (CursorName);
      > dbms_sql.Close_Cursor (CursorName);
      > END;
      > BEGIN
      > Teller := 0;
      > For Row IN CurIndex Loop
      > Teller := Teller + 1;
      > SqlStatement := '/* INDEX: '||Teller||' */ ALTER
      INDEX '||Row.TABLE_OWNER||'.'||Row.INDEX_NAME||' REBUILD NOLOGGING';
      > ExecSql (SqlStatement);
      > End Loop;
      > END;
      > /
      > EXIT;
      >
      >
      >
      ======================================================================
      =================
      > -- ANALYZE ALL TABLES --805_ANALYZE_ALL_TABLES.SQL --
      >
      > DECLARE
      > CURSOR CurTable IS SELECT OWNER, OBJECT_NAME
      > FROM ALL_OBJECTS
      > WHERE OBJECT_TYPE='TABLE' AND
      > NOT (OWNER='SYS' OR
      OWNER='SYSTEM') AND
      > TEMPORARY='N';
      > SqlStatement VARCHAR2(200);
      > Teller INTEGER;
      >
      > PROCEDURE ExecSql (strSQL IN VARCHAR2) IS
      > CursorName INTEGER;
      > NumRows INTEGER;
      > BEGIN
      > CursorName := dbms_sql.open_cursor;
      > dbms_sql.parse (CursorName, strSQL, dbms_sql.v6);
      > NumRows := dbms_sql.Execute (CursorName);
      > dbms_sql.Close_Cursor (CursorName);
      > END;
      >
      > BEGIN
      > Teller := 0;
      > For Row IN CurTable Loop
      > Teller := Teller + 1;
      > SqlStatement := '/* ANALYZE: '||Teller||' */ ANALYZE
      TABLE '||Row.OWNER||'.'||Row.OBJECT_NAME||' COMPUTE STATISTICS';
      > ExecSql (SqlStatement);
      > End Loop;
      > END;
      > /
      > EXIT;
      >
      >
      ======================================================================
      =================
      >
      >
      >
      >
      >
      > ------------------------------->
      > Website van het Drenthe College:
      > www.drenthecollege.nl
      >
      > Vrijwaring/Disclaimer
      > De gegevens in dit elektronisch document en de eventuele bijlagen
      zijn
      > uitsluitend bedoeld voor de geadresseerde(n). De informatie wordt
      > zorgvuldig samengesteld. Het Drenthe College kan echter niet
      garanderen
      > dat de informatie absoluut juist, volledig en tijdig overkomt via
      het
      > Internet. Voor belangrijke beslissingen aangaande de verstrekte
      > informatie dient u een en ander rechtstreeks bij het Drenthe
      College te
      > controleren.
    • litbighor
      Hi Bert, Did you check the file init .ora ? Is there any differences between the 2 databases ? Check SORT_AREA_SIZE. Regards, Litbighor
      Message 2 of 6 , Mar 7, 2003
      • 0 Attachment
        Hi Bert,

        Did you check the file init<SID>.ora ?
        Is there any differences between the 2 databases ?
        Check SORT_AREA_SIZE.

        Regards,

        Litbighor
      • litbighor
        What s the end of the story, please Bert ? ... reanalyse the tables on our (several) PS databases (HR/FIN/SA). ... 8.05 & FS 7.52 / PT 7.55 / Oracle 8.05) ...
        Message 3 of 6 , Apr 7 3:57 AM
        • 0 Attachment
          What's the end of the story, please Bert ?


          --- In psftdba@yahoogroups.com, BL Barske <BL.Barske@D...> wrote:
          >
          > We had a script to rebuild all the indexes and afterwards to
          reanalyse the tables on our (several) PS databases (HR/FIN/SA).
          > (SA 7.60 / PT 7.58 / Oracle 8.05 & HR 7.51 / PT 7.57 / Oracle
          8.05 & FS 7.52 / PT 7.55 / Oracle 8.05)
          >
          > We ran it every two weeks.
          >
          > See scripts below.
          >
          > Normally the both SQL scripts (after setting the VERYBIGROLLBACK
          segment ON-LINE) took about 30 min/SID.
          >
          >
          > Now, since ... after 12 hours the rebuild indexes is still busy.
          (Even on a test database as copy of production on a standalone server
          with one user)
          > When I run it in a older copy of the database (same number of
          indexes and tables, about 2% less data) it still takes only an hour
          for both scripts.
          >
          > Now I have build from ApplicationDesigner a script for all the
          records to drop en build the indexes.
          > After that I gave the command "execute DBMS_UTILITY.ANALYZE_SCHEMA
          ('SYSADMPS', 'COMPUTE');"
          > and totaly it costs about 4 hours to complete
          >
          > Why takes it suddenly so long to perform the tasks ?
          >
          > Bert Barskè
          > Drenthe College (NL)
          >
        • David Kurtz
          Two suggestions: i) take a statspack snapshot before and after the analyze command. If you want a really detail report and analysis submit it to Anjo Kolk s
          Message 4 of 6 , Apr 7 9:49 AM
          • 0 Attachment
            Two suggestions:

            i) take a statspack snapshot before and after the analyze command. If you
            want a really detail report and analysis submit it to Anjo Kolk's YAPP
            report on www.oraperf.com



            ii) enable SQL trace, or perhaps event 10046,8 (waits)

            alter session set events '10046 trace name context forever, level 8';

            1=sql trace
            4=binds
            8=waits
            12=binds&waits

            and process with TKPROF including recursive SQL and see what you get.

            _________________________
            David Kurtz
            Go-Faster Consultancy Ltd.
            phone: +44 (0)7771 760660
            fax: +44 (0)7092 348865
            mailto:david.kurtz@...
            web: www.go-faster.co.uk
            PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
          • Henry Poras
            David, tkprof won t report on the wait events which are in the raw trace file. I believe there is a tool at www.hotsos.com which you can use. Otherwise, I
            Message 5 of 6 , Apr 7 11:15 AM
            • 0 Attachment
              David,

              tkprof won't report on the wait events which are in the raw trace
              file. I believe there is a tool at www.hotsos.com which you can use.
              Otherwise, I wrote an awk script a while ago.

              Henry

              # Script for analyzing Oracle Trace files with WAIT statistics
              # Usage: wait_scan.awk <filename>
              # Written: Henry Poras
              # 5/16/00
              # Modified: 12/3/01 Initially assumes all wait states for a
              cursor are between
              # parse statements.
              #
              #
              nawk ' # need
              nawk, not awk
              BEGIN {N=""
              PARSE_FLAG=0 #
              PARSE_FLAG = 0 (normal state)
              printf("\n\n%-35s %-12s %-18s\n\n", #
              PARSE_FLAG = 1 (previous line PARSING)
              "WAIT EVENT", "# OF TIMES", "ELAPSED TIME (sec)") #
              print column headers
              }

              {if (PARSE_FLAG==1) # if
              previous line started
              {SQL[N]=$0 # with
              PARSING, print
              PARSE_FLAG=0 # the
              SQL.
              N=""
              }
              }
              /^PARSING/ {FS=" "
              N=$4
              sub("#","",N)
              if (N in SQL)
              prinfo(N)
              PARSE_FLAG=1
              }
              /^WAIT/ {FS="#| nam=|ela=|p1="
              N=$2
              sub(":","",N)
              PARSE_FLAG=2
              n_wait[N,$3] += 1
              ela_wait[N,$3] += $4
              }
              END {for (N in SQL) { #
              Print Wait statistics for final
              printf "\n\n\n%s\n\n", SQL[N] # SQL
              statement in file
              for (k in n_wait) {
              split(k,arg,SUBSEP)
              if (arg[1]==N && n_wait[k]!=0) {
              printf "%-35s %-12s %12.2f\n",
              arg[2],n_wait[k],ela_wait[k]/100
              n_wait[k]=0
              ela_wait[k]=0
              }
              }
              printf "\n\n"
              }
              for (k in n_wait) {
              split(k,arg,SUBSEP)
              if (n_wait[k] != 0) {
              printf "%-35s %-12s %12.2f\n",
              arg[2],n_wait[k],ela_wait[k]/100
              n_wait[k]=0
              ela_wait[k]=0
              }
              }
              }

              function prinfo(N, k) {
              printf "\n\n\n%s\n\n", SQL[N]
              for (k in n_wait){
              split(k,arg,SUBSEP)
              if (arg[1]==N && n_wait[k]!=0) {
              printf "%-35s %-12s %12.2f\n",
              arg[2],n_wait[k],ela_wait[k]/100
              n_wait[k]=0
              ela_wait[k]=0
              }
              }
              }
              ' $1

              --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@g...>
              wrote:
              > Two suggestions:
              >
              > i) take a statspack snapshot before and after the analyze command.
              If you
              > want a really detail report and analysis submit it to Anjo Kolk's
              YAPP
              > report on www.oraperf.com
              >
              >
              >
              > ii) enable SQL trace, or perhaps event 10046,8 (waits)
              >
              > alter session set events '10046 trace name context forever, level
              8';
              >
              > 1=sql trace
              > 4=binds
              > 8=waits
              > 12=binds&waits
              >
              > and process with TKPROF including recursive SQL and see what you
              get.
              >
              > _________________________
              > David Kurtz
              > Go-Faster Consultancy Ltd.
              > phone: +44 (0)7771 760660
              > fax: +44 (0)7092 348865
              > mailto:david.kurtz@g...
              > web: www.go-faster.co.uk
              > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
            Your message has been successfully submitted and would be delivered to recipients shortly.