Home » SQL & PL/SQL » SQL & PL/SQL » truncating a empty table (11g)
truncating a empty table [message #647844] Tue, 09 February 2016 23:26 Go to next message
arudesai
Messages: 7
Registered: July 2015
Junior Member
Hi,

I am executing a package for a date range suppose for 1 month .. it has used table 'ABC' which truncats and loads every time. below is the secenerio
1) truncate table 'ABC' used run the package for date range 1 month, execution time is 1 min 17 sec. records in table 37000
2)truncate tables 'ABC used run the package for date range 1 day, execution time 5 sec. records in table 0
3)run the package for date range 1 month without truncat 'ABC (as table is empty), execution time 1 hr 56 min records in table 37000

can some one help me understand oracle behavior here.

Thanks,
Aru

[Updated on: Wed, 10 February 2016 00:32]

Report message to a moderator

Re: truncating a empty table [message #647851 is a reply to message #647844] Wed, 10 February 2016 02:27 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can ask Oracle itself what it is doing.

First do a session trace.
Then let Oracle explain what it is doing with TKProf

And see what statements take how long during the package execution.
Re: truncating a empty table [message #647865 is a reply to message #647844] Wed, 10 February 2016 07:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> used table 'ABC' which truncats and loads every time
Are the stats updated everytime?
Truncate will leave the stats as-is.
With stale stats, optimizer could get delirious.
Try again with the updated stats and observe.

Then try ThomasG's recommendation. The best way to check what Oracle does in terms of measurable units.

[Updated on: Wed, 10 February 2016 07:04]

Report message to a moderator

Re: truncating a empty table [message #647867 is a reply to message #647865] Wed, 10 February 2016 07:09 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,

You need to Analyze the table and gather statistics.Please read Below mentioned documents for the same:


https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4005.htm
Re: truncating a empty table [message #647870 is a reply to message #647867] Wed, 10 February 2016 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Bad answer, once more, NEVER use this command to gather statistics.
Hopefully, you are there to post bad answers then people can know what they must NOT do.

Re: truncating a empty table [message #647891 is a reply to message #647844] Thu, 11 February 2016 02:06 Go to previous messageGo to next message
arudesai
Messages: 7
Registered: July 2015
Junior Member
I dont have db access to check trace files.. it would take long to get the files from DBA for test env i have already raised a request for the same .
can any one let me know proper commands for gather stat and Analyze.
Re: truncating a empty table [message #647892 is a reply to message #647891] Thu, 11 February 2016 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at DBMS_STATS.GATHER_TABLE_STATS procedure.
Do NOT use ANALYZE command.

Re: truncating a empty table [message #647895 is a reply to message #647892] Thu, 11 February 2016 03:03 Go to previous messageGo to next message
arudesai
Messages: 7
Registered: July 2015
Junior Member
just for curiosity.. what would be impact of analyze command
Re: truncating a empty table [message #647896 is a reply to message #647895] Thu, 11 February 2016 03:21 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'll have badly analyzed data
Re: truncating a empty table [message #647897 is a reply to message #647895] Thu, 11 February 2016 03:29 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Ask Tom.
Re: truncating a empty table [message #647900 is a reply to message #647897] Thu, 11 February 2016 04:30 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Why do you think the truncate is taking this long? It's much more likely to be the load.
Re: truncating a empty table [message #647901 is a reply to message #647900] Thu, 11 February 2016 05:19 Go to previous messageGo to next message
arudesai
Messages: 7
Registered: July 2015
Junior Member
after truncate it is fast , but the question is if the table is empty and we dont truncate it is taking long
Re: truncating a empty table [message #647902 is a reply to message #647901] Thu, 11 February 2016 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the answer remains - trace the session to find out what the time is being spent on.
Re: truncating a empty table [message #647929 is a reply to message #647902] Thu, 11 February 2016 12:35 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Just curious, what are you loading the table for? How are you loading it (sqlldr, a procedure?) If it is the same query, have you thought about using a materialized view that is refreshed on a daily schedule?
Re: truncating a empty table [message #647931 is a reply to message #647929] Thu, 11 February 2016 22:22 Go to previous messageGo to next message
arudesai
Messages: 7
Registered: July 2015
Junior Member
i am loading it trough procedure, its monthly schedule with populates other table for reporting purpose materialized view wont be feasible for monthly scheduled job.
Re: truncating a empty table [message #647933 is a reply to message #647931] Thu, 11 February 2016 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


I refuse to guess & you refuse to actually SHOW us the code.
You have a mystery & we have no clues.

is data loaded using INSERT /*+ APPEND */?
Re: truncating a empty table [message #647950 is a reply to message #647933] Fri, 12 February 2016 07:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Setup a DBMS_JOB or a DBMS_SCHEDULER job to run once a month on the day and time you want. Middle of the night is cool and do something likee
delete from my_table;

insert into my_table
select col1,col2,col3...
from ...
where ...;

commit;


This way it doesn't matter how long it takes. Run it on the off shift time and the existing data isn't replaced until the commit is issued.
Re: truncating a empty table [message #647976 is a reply to message #647950] Fri, 12 February 2016 23:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
how does the table get "empty" if you do not truncate it?

a table that is empty is not necessarily a small table. There is a thing call the HIGH WATER MARK for a table. The HIGH WATER MARK represents the highest block under which data is or was at one time sitting in that block. Consider this sequence of events:

1. you create a table
2. you load it with several million rows causing it to allocate 1 million blocks to the table.
3. all of these 1 million blocks have at least one row.
4. you scan the table (this will require looking at 1 million blocks)
5. you then DELETE all the rows in the table.
6. you then scan the table (this will require looking at 1 million blocks (even though there are no rows))
7. you then TRUNCATE the "empty" table.
8. you then scan the table (this will require looking at only about 4 blocks even though all 1 million blocks still below to the table))

#8 above is because the TRUNCATE command moves the high water mark down to a minimum and a table scan only looks at blocks under the high water mark.

I am guessing here, but your "long run time" might be due to look at empty tables which still have high water marks above lots of blocks. This is at least one way to explain why "it runs fast after we truncate".

Good luck. Kevin
Re: truncating a empty table [message #647980 is a reply to message #647895] Sat, 13 February 2016 06:57 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
arudesai wrote on Thu, 11 February 2016 09:03
just for curiosity.. what would be impact of analyze command
My understanding of this (which may be incorrect - I haven't bothered to validate it) is that ANALYZE may destroy the more sophisticated statistics (particularly histograms) that DBMS_STATS may have gathered.
Re: truncating a empty table [message #648023 is a reply to message #647980] Mon, 15 February 2016 02:07 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
John Watson wrote on Sat, 13 February 2016 12:57
arudesai wrote on Thu, 11 February 2016 09:03
just for curiosity.. what would be impact of analyze command
My understanding of this (which may be incorrect - I haven't bothered to validate it) is that ANALYZE may destroy the more sophisticated statistics (particularly histograms) that DBMS_STATS may have gathered.



Yup.

Still the only way to get chained rows though, as far as I know. Which is a bummer.
Previous Topic: What is the switching context with FORALL?
Next Topic: Queries for User and Password Policy
Goto Forum:
  


Current Time: Thu Apr 18 19:24:52 CDT 2024