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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Lothar Flatz
*Sent:* Tuesday, May 31, 2016 2:14 PM
*To:* oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx]
*Sent:* Tuesday, May 31, 2016 12:35 PM
*To:* Deas, Scott <Scott.Deas@xxxxxxx> <mailto:Scott.Deas@xxxxxxx>
*Cc:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
<mailto:oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxx
<mailto:Scott.Deas@xxxxxxx>> 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@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Ashoke
Mandal
*Sent:* Sunday, May 29, 2016 5:42 PM
*To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx
<mailto:oracle-l@xxxxxxxxxxxxx>>
*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.**
