Re: Table update is taking too much time

Thanks for your tips.

I have some questions before I try the insert method.

1. All three tables are partitioned by quarter with test_date. We have data from 1992. The partitions were created from 1992 through 1996 by year meaning there was only one partition for each year. But from 1997 through 2016 we have one partition for every quarter. So there are 85 partitions on each of these tables. Both of the child tables contain multiple records corresponding to each record on the master table.
2. For some reason even a simple select statement is taking long time on each of these tables. 2.1 select count(*) from master_table; It takes 11 min => Records to be updated in master_table: 2437461 (total records in this table is 222,760,096) 2.2 select count(*) from child_table_1; Takes 1 hour => Records to be updated in child_table_1: 370569916 (total records in this table is 20,355,000,000) 2.3 select count(*) from child_table_2; Takes 3 hours => Records to be updated in child_table_2: 4594843 (total records in this table is 1,843,018,199)
3. If I create a temporary copy of each of these tables using CTAS it would create a regular table not the partitioned table. So I could generate create script for each of these tables using TOAD or SQL Developer, then expand 255 (85*3) tablespaces to it's double the size to host two copies of these tables. I am kind of worried to touch these partitioned tables. If something goes wrong then recovery of this 3TB database will be time consuming.

Do you think there is some issue with my tables which is causing to take the simple queries so long (as mentioned above)? Could I speed up the update process using my PL/SQL code since that seems safer as I don't need to touch the entire data and don't need to rebuild index on 1 TB size of the biggest table.

Ashoke

On Sun, May 29, 2016 at 5:17 PM, Deas, Scott <Scott.Deas_at_lfg.com> wrote:

> Ashoke,
>
>
>
> I assume you have some sort of maintenance window allocated for this work,
> so my recommendation would be to not do the update in place, but rather to
> do something like the following:
>
>
>
> 1. CTAS for each table you’re changing to create an empty copy of
> the table with the nologging option
>
> 2. Create a lookup table where you store original ut_id and new
> ut_id
>
> 3. Parallel, direct path insert from original table (joining to
> your control table to get the new ut_it) into new tables
>
> 4. Build all indexes/constraints
>
> 5. Verify data
>
> 6. Enable logging on tables/indexes
>
> 7. Rename objects so new tables now have the original table names
>
>
>
> All of this assumes you aren’t changing in these objects.
>
>
>
> You may also want to look at this whitepaper from Bryn Llewellyn that
> discusses using PL/SQL to transform one table into another, when it’s
> changing.
> https://blogs.oracle.com/plsql-and-ebr/resource/Transforming_One_Table_to_Another_Sql_or_Plsql_Whitepaper.pdf
>
>
>
> Thanks,
>
> Scott
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ashoke Mandal
> *Sent:* Sunday, May 29, 2016 5:42 PM
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Table update is taking too much time
>
>
>
> Dear All, Let me know if you have any suggestions to make this update
> faster as six days is too high.
>
> Background: Due to some unexpected reason we have same set of test_ids in
> the table for two different testing facilities. We are trying to change the
> ut_ids of one facility to resolve the duplication issue using the following
> code. It works but taking *6* days to complete the update and looking for
> tips to make the update faster. The ut_id is part of the primary key in all
> three tables (master_table, child_table_1 and child_table_2).
>
> Records to be updated in master_table: 2437461 (total records in this
> table is 222760096)
>
> Records to be updated in child_table_1: 370569916
>
> Records to be updated in child_table_2: 4594843
>
>
>
> Using following UNIX shell script and a stored procedure:
>
> *=> Calling shell script code:*
>
> export PATH=$PATH:/opt/bin:/usr/ccs/bin:/usr/sbin:/var/opt/oracle
>
> export DMAS_ROOT=/med_oracle/dmas/production
>
> export start_utid=$1
>
> export end_utid=$2
>
> export ORACLE_SID=$3
>
>
>
> # Set oracle environment for the specified database ($3)
>
> export ORAENV_ASK=NO
>
> . /usr/local/bin/oraenv
>
>
>
> sqlplus user/password <<EOF1
>
>
>
> -- Disable the Foreign key constraints on the child tables
>
> ALTER TABLE CHILD_TABLE_1 DISABLE CONSTRAINT
> CHILD_TABLE_1_FK_UT_ID_TEST_DATE;
>
> ALTER TABLE CHILD_TABLE_2 DISABLE CONSTRAINT TEST_NOTE_FK_UT_ID_TEST_DATE;
>
>
>
> TRUNCATE TABLE master_table_temp;
>
> INSERT INTO master_table_temp select * from master_table where (ut_id>=
> ${start_utid} and ut_id <= ${end_utid}) and test_facility_code <> 'E';
>
> -- Call the procedure to update the UT_Ids
>
> EXECUTE proc_update_utid ('$1','$2');
>
>
>
> -- Enable the foreign key constarints
>
> ALTER TABLE CHILD_TABLE_1 ENABLE NOVALIDATE CONSTRAINT
> child_table_1_FK_UT_ID_TEST_DATE;
>
> ALTER TABLE TEST_NOTE ENABLE NOVALIDATE CONSTRAINT
> child_table_2_FK_UT_ID_TEST_DATE;
>
>
>
> EXIT;
>
> EOF1
>
>
>
> *=> Called stored procedure*
>
> create or replace PROCEDURE proc_update_utid(start_utid number, end_utid
> number) AS
>
>
>
> lv_rec_count number :=0;
>
> lv_update_number number :=0;
>
> lv_total_rec_cnt number :=0;
>
> lv_last_utid number;
>
> lv_logfile varchar2(40);
>
> lv_db_name varchar2(8);
>
> lv_stmt varchar2(1000);
>
> output_file UTL_FILE.FILE_TYPE;
>
>
>
> -- Declare a cursor on the impacted records stored in the temporary
> working table
>
> cursor ut_cur is select * from master_table_temp order by ut_id;
>
>
>
> begin
>
> dbms_output.enable(10000000); -- setup of test output
>
> -- Get the database name
>
> select name into lv_db_name from v$database;
>
> lv_logfile := lv_db_name || '_' || 'update.log';
>
> output_file := UTL_FILE.FOPEN('/med_oracle/dmas/production/log',
> lv_logfile, 'A');
>
>
>
> utl_file.put_line(output_file,'Start Time: '|| to_char(sysdate,
> 'DD-MON-YYYY HH24:MI:SS'));
>
>
>
> -- Check the number of duplicate UT_IDs to be updated
>
> select count(*) into lv_total_rec_cnt from master_table
>
> where (ut_id >= start_utid and ut_id <= end_utid) and test_facility_code
> <> 'E';
>
> utl_file.put_line(output_file,'Total number of duplicate UT_ID before the
> update: '|| lv_total_rec_cnt);
>
> --
>
>
>
> for ur_rec in ut_cur
>
> loop
>
> -- dbms_output.put_line ('This is right before the update of unit_test
> table');
>
> update master_table set ut_id=ut_id+9000000000 where
> ut_id=ur_rec.ut_id and
>
> test_facility_code <> 'E';
>
> -- dbms_output.put_line ('This is right before the update of
> child_table_1 table');
>
> update child_table_1 set ut_id=ut_id+9000000000 where
> child_table_1.ut_id=ur_rec.ut_id and
>
> test_date=ur_rec.test_date;
>
> update child_table_2 set ut_id=ut_id+9000000000 where
> child_table_2.ut_id=ur_rec.ut_id and
>
> test_date=ur_rec.test_date;
>
> test_date=ur_rec.test_date;
>
> lv_rec_count := lv_rec_count+1;
>
> if lv_rec_count = 1000
>
> then
>
> lv_update_number := lv_update_number+1;
>
> -- insert a row to the update_status table
>
> insert into update_status values(lv_update_number, ur_rec.ut_id,
> sysdate);
>
> commit;
>
> lv_rec_count := 0;
>
> else
>
> lv_last_utid := ur_rec.ut_id;
>
> end if;
>
> end loop;
>
> -- Following code is to address the last set of rows, which are less
> than 1000.
>
> if lv_rec_count != 1000
>
> then
>
> lv_update_number := lv_update_number+1;
>
> -- insert a row to the update_status table
>
> insert into update_status values(lv_update_number, lv_last_utid,
> sysdate);
>
> -- dbms_output.put_line ('This is right before the second commit');
>
> commit;
>
> end if;
>
> -- Verify if the UT_ID between start_utid and end_utid range with
> facility_code <> 'E' are
>
> -- all assigned to a different UT_ID. If that is true then the following
> query shouldn't return any row.
>
> select count(*) into lv_total_rec_cnt from master_table
>
> where (ut_id >= start_utid and ut_id <= end_utid) and test_facility_code
> <> 'E';
>
> dbms_output.put_line ('The total duplicate UT_IDs in master_table is
> ' ||lv_total_rec_cnt);
>
> --
>
> utl_file.put_line(output_file,'Total number of duplicate UT_ID after the
> update: '|| lv_total_rec_cnt);
>
>
>
> utl_file.put_line(output_file,'End Time: '|| to_char(sysdate, 'DD-MON-YYYY
> HH24:MI:SS'));
>
> utl_file.fclose(output_file);
>
>
>
> end proc_update_utid;
>
>
>
> Thanks,
>
> Ashoke
>
> Notice of Confidentiality: **This E-mail and any of its attachments may
> contain
> Lincoln National Corporation proprietary information, which is privileged,
> confidential,
> or subject to copyright belonging to the Lincoln National Corporation
> family of
> companies. This E-mail is intended solely for the use of the individual or
> entity to
> which it is addressed. If you are not the intended recipient of this
> E-mail, you are
> hereby notified that any dissemination, distribution, copying, or action
> taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please
> notify the
> sender immediately and permanently delete the original and any copy of
> this E-mail
> and any printout. Thank You.**
>

--
http://www.freelists.org/webpage/oracle-l