Re: Table update is taking too much time

why do you want to produce a truck oad of trace files, while sql_monitor is a click away? ;-)

Regards

Lothar

On 31.05.2016 18:50, Deas, Scott wrote:
>
> Ashoke,
>
> Use dbms_metadata.get_ddl to generate the full ddl for the tables,
> maintaining the partitioning. You can even modify the metadata if you
> wanted to create quarterly partitions for your older years.
>
> Regarding the time it takes to do the count, a count from a table this
> large is going to take some time, although 3 hours is excessive. I
> would recommend a 10046 trace to see what is actually happening. Do
> these tables have a large number of updates/deletes that would cause
> the blocks to be fragmented? You may find that inserting into a new
> table would shrink the size of the tables down, and may improve your
> response time. You really wouldn’t know what’s happening until you
> look at a trace and see what the system is doing as you run your query.
>
> Since you mention that these are partitioned tables, you could
> consider using partition exchange to update one partition at a time,
> however that could complicate the parent/child updates.
>
> Finally, if you have enough room, the method that Lothar and I have
> described is totally recoverable, just by the fact that you don’t
> rename the tables until you are sure everything looks good. The
> original tables will be available alongside the new tables, so only
> once you’re ready, do you issue the renames, and even then, the old
> tables can be exported and saved.
>
> Thanks,
>
> Scott
>
> *From:*Ashoke Mandal [mailto:ramukam1983_at_gmail.com]
> *Sent:* Tuesday, May 31, 2016 12:35 PM
> *To:* Deas, Scott <Scott.Deas_at_lfg.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Re: Table update is taking too much time
>
> Hi Scott, Lohar and Brent,
>
> 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
> <mailto: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>
> [mailto: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
> <mailto: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.**
>
> 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