Re: Table update is taking too much time
Date: Tue, 31 May 2016 20:30:44 +0200
Message-ID: <f757c990-22db-9500-2abb-d60afb1b92d0_at_bluewin.ch>
Gosh! I really hope they got a tuning pack license for a db of this size. :-D
On 31.05.2016 20:17, Deas, Scott wrote:
>
> That is true, but you wouldn’t need to run the 10046 for very long to
> start to see where your problem may lie, and it also doesn’t require a
> Tuning Pack license. J
>
> Thanks,
> Scott
>
> *From:*oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Lothar Flatz
> *Sent:* Tuesday, May 31, 2016 2:14 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Table update is taking too much time
>
> Hi Scott,
>
> 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> <mailto:Scott.Deas_at_lfg.com>
> *Cc:* ORACLE-L <oracle-l_at_freelists.org>
> <mailto: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.**
>
> --
>
> 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-lReceived on Tue May 31 2016 - 20:30:44 CEST