We help IT Professionals succeed at work.

how to fix this ? -- rebuild and analyze indexes ( dbms_index_utl package )

vishali_vishu
on
3,797 Views
Last Modified: 2013-12-18
Hai !
       I am trying to do this way:

CREATE OR REPLACE PROCEDURE index_maintainance(table_name_h VARCHAR2)
IS
   table_count_p   NUMBER := 0;
BEGIN
   
   execute immediate 'alter session set db_file_multiblock_read_count=96';

   
   SELECT COUNT (table_name)
     INTO table_count_p
     FROM user_tables
    WHERE table_name = UPPER (TRIM (table_name_h));

   IF table_count_p = 1
   THEN
      dbms_index_utl.build_table_indexes (LIST    => UPPER  (TRIM (table_name_h) ),
                                                                 just_unusable       => FALSE,
                                                                 locality            => 'ALL',
                                                                 concurrent          => TRUE,
                                                                 cont_after_err      => TRUE,
                                                                 max_slaves          => 6
                                                                );
      DBMS_STATS.gather_table_stats ('My_schema', UPPER (TRIM (table_name_h)),
                                                               estimate_percent      => 20
                                                             );
   END IF;
END;
/

This is running really long ( around 2 hrs )

what's the problem with the code ?

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
how many rows are there on the table?
how many indexes are there on the table?
partitioned table?

Author

Commented:
dbms_index_utl.build_table_indexes (LIST    => UPPER  (TRIM (table_name_h) ),
                                                                 just_unusable       => FALSE,
                                                                 locality            => 'ALL',
                                                                 concurrent          => TRUE,
                                                                 cont_after_err      => TRUE,
                                                                 max_slaves          => 6
                                                                );

what does locality means ?
what does max_slaves mean ? --- parallel processes ( my guess )

Author

Commented:
Table is not partitioned....

Indexes -- 12

Rows : 12 million

Author

Commented:
on 9i database  we are using this  and it is taking 1 hr and we tried to deploy the same on 10g and this is running for 5 hrs. so i change to above code and it still runs for 2 hrs.  I am estimating the statistics even though running for 2 hrs. what could be the cause ?

CREATE OR REPLACE PROCEDURE index_maintainance(table_name_h varchar2)
   IS
col_count_p number := 0;
cur_count_p number := 0;  
BEGIN

 

select count(*) into col_count_p from all_tab_cols where table_name = upper(trim(table_name_h));
if col_count_p > 0 then  

for table_indexes in (select * from user_indexes
where lower(table_name) = lower(trim(table_name_h)))
loop


execute immediate ('alter index '|| table_indexes.index_name || ' rebuild compute statistics');

end loop;
execute immediate ('analyze table '|| table_name_h || ' compute statistics');

end if;    
   
END; -- Procedure
/
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the first statment will make the "inventory" of the indexes on that table. max_slaves will define that 6 processes will be started to the table stats gathering process.

now, with 12M rows, 12 indexes, it's eventually "normal" that it takes 2 hours...
but you have to check the I/O performance (of the disks).

question: are the indexes on the same tablespace than the table itself?

Author

Commented:
angelIII:
       But the same is getting done in 1 hr on 9i server. everything is replicated on to 10g server.

same structure / same tablespace names
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
are the hardware specs the same on the 10g server?
I suggest that you start by getting some specialist to confirm/compare the I/O performance/parameters/settings on the 2 servers.,
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
or. check the oracle init settings about the buffer, SGA, PGA ... can you show, side-by-side, those relevant settings

Author

Commented:
angelIII:
             i am a developer and i don't have privilages to do.
Is there anything with the code? anything messing up
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>i am a developer and i don't have privilages to do.
then this should not be your job to gather stats on tables/indexes...
get the right person to do the job instead.

Commented:
Why are you rebuilding your indexes?
Will you do this on a weekly basis or just for this special occasion?
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
This code worked for me...

i am rebuilding the indexes in parallel.
CREATE OR REPLACE PROCEDURE index_maintainance (
   table_name_h    VARCHAR2,
   schema_name_h   VARCHAR2 default 'SYS'
)
IS
   table_count_p          NUMBER          := 0;
 
   TYPE ind_tab IS TABLE OF all_indexes.index_name%TYPE;
 
   index_table            ind_tab;
   vjob_num1              job_num_tab     := job_num_tab ();
   vjob_num2              job_num_tab     := job_num_tab ();
   job_command            VARCHAR2 (200);
   n_jobs_running_count   NUMBER;
   job_list               VARCHAR2 (2000);
   break_signal           NUMBER          := 0;
BEGIN
-- CHECK IF THE TABLE EXISTS IN THE GIVEN SCHEMA
   SELECT COUNT (table_name)
     INTO table_count_p
     FROM all_tables
    WHERE table_name = UPPER (TRIM (table_name_h))
      AND owner = UPPER (TRIM (schema_name_h));
 
-- IF TABLE EXISTS THEN DO THE INDEX MAINTAINANCE JOB ON NOT VALID INDEXES ON THE SUPPLIED TABLE
   IF table_count_p = 1
   THEN
-- COLLECT THE INDEX NAMES ON THE SUPPLIED TABLES WHOSE STATUS IS NOT VALID
      SELECT index_name
      BULK COLLECT INTO index_table
        FROM all_indexes
       WHERE table_name = UPPER (TRIM (table_name_h))
         AND owner = UPPER (TRIM (schema_name_h));
 
    
 
      -- BUILD THE BACK GROUND INDEX REBUILD JOBS
      FOR i IN index_table.FIRST .. index_table.COUNT
      LOOP
         vjob_num1.EXTEND;
         job_command := 'ALTER INDEX ' || index_table (i) || ' REBUILD';
         DBMS_OUTPUT.put_line (job_command);
         DBMS_JOB.submit (job       => vjob_num1 (i),
                          what      =>    'EXECUTE IMMEDIATE '''
                                       || job_command
                                       || ''';'
                         );
      END LOOP;
 
      -- SUBMIT THE BUILT BACK GROUND JOBS -- THEY KEEP RUNNING IN THE BACKGROUND IN PARALLEL
      COMMIT;
 
      -- KEEP WAITING TILL ALL THE SUBMITTED BACKGROUND JOBS ARE DONE.
      LOOP
         dbms_output.put_line('wait loop');
         
         SELECT COUNT (job)
           INTO n_jobs_running_count
           FROM user_jobs
          WHERE job IN (SELECT column_value
                          FROM TABLE (CAST (vjob_num1 AS job_num_tab)));
 
         IF n_jobs_running_count = 0
         THEN
            -- EXIT THE LOOP ONCE ALL THE BACK GROUND JOBS ARE DONE
            break_signal := 1;
         ELSE
            DBMS_OUTPUT.put_line ('KEEP WAITING');
            -- CHECK AFTER EVERY ONE MINUTE FOR THE BACKGROUND JOB STATUS.
            DBMS_LOCK.sleep (60);
         END IF;
 
         EXIT WHEN break_signal = 1;
      END LOOP;
 
      break_signal := 0;
 
      -- BUILD THE ANALYZE INDEXES JOBS TO BE SUBMITTED IN THE BACKGROUND
      FOR i IN index_table.FIRST .. index_table.COUNT
      LOOP
         vjob_num2.EXTEND;
         -- ESTIMATE THE STATISTICS WITH 30 PERCENT OF IT'S DATA
         job_command :=
               'ANALYZE INDEX '
            || index_table (i)
            || ' ESTIMATE STATISTICS SAMPLE 30 PERCENT';
         DBMS_OUTPUT.put_line (job_command);
         DBMS_JOB.submit (job       => vjob_num2 (i),
                          what      =>    'EXECUTE IMMEDIATE '''
                                       || job_command
                                       || ''';'
                         );
      END LOOP;
 
      -- SUBMIT THE BUILT BACKGROUND JOBS TO RUN IN PRARALLEL .
      COMMIT;
      -- ANALYZE THE TABLE IN THE MEAN WHILE. THIS IS NOT DEPENDENT ON THE ABOVE BACKGROUND JOBS.
      DBMS_STATS.gather_table_stats (ownname               => UPPER
                                                                 (TRIM
                                                                     (schema_name_h
                                                                     )
                                                                 ),
                                     tabname               => UPPER
                                                                 (TRIM
                                                                     (table_name_h
                                                                     )
                                                                 ),
                                     estimate_percent      => 30
                                    );
 
      -- KEEP WAITING TILL ALLL THE BACKGROUND JOBS ARE DONE ( ANALYZE INDEXES )
      LOOP
         dbms_output.put_line('wait loop');
         SELECT COUNT (job)
           INTO n_jobs_running_count
           FROM user_jobs
          WHERE job IN (SELECT *
                          FROM TABLE (CAST (vjob_num2 AS job_num_tab)));
 
         IF n_jobs_running_count = 0
         THEN
            break_signal := 1;
         ELSE
            DBMS_OUTPUT.put_line ('KEEP WAITING');
            -- CHECK EVERY 1 MINUTE FOR THE STATUS OF THE BACKGROUND JOBS.
            DBMS_LOCK.sleep (60);
         END IF;
 
         EXIT WHEN break_signal = 1;
      END LOOP;
   END IF;
END;
/

Open in new window

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.