Printable View
I asked to demonstrate what would affect performance if I have a high number of high water mark. Anyway, below is what I have in mind, but I need to get some inputs/advises.
1. let's say I have a table with 100k of rows.
2. delete the data
3. DBA_SEGMENTS is unchanges in bytes which is obvious.
how can I capture the time of the query and show that HWM would take a lot more time to scan the entire table up to the HWM even there is no records.
thanks
Set events 10046.
Use tkprof to get report from the trace file.
Tamil
set timing onQuote:
Originally posted by learning_bee
I asked to demonstrate what would affect performance if I have a high number of high water mark. Anyway, below is what I have in mind, but I need to get some inputs/advises.
1. let's say I have a table with 100k of rows.
2. delete the data
3. DBA_SEGMENTS is unchanges in bytes which is obvious.
how can I capture the time of the query and show that HWM would take a lot more time to scan the entire table up to the HWM even there is no records.
thanks
create big table
select count(*) from big table
delete from big table
select count(*) from big table
truncate big table
select count(*) from big table
...compare the timing results
thanks all,
If I have the staging table with high water mark, the way the application was desgined, it insert a bunch of data in those tables and at the end it delete the data out (TRUNCATE is not an option). beside moving the objects and rebuild index, is there any other tricky way you guys can recommend?????
Why is truncate not an option? Isn't there a "quiet" time when you can lock the table, check the record count is zero and then truncate?
So what's the point of truncating if you're going to fill it up again? Seems like a lot of work for very little (if any) benefit.Quote:
Originally posted by learning_bee
thanks all,
If I have the staging table with high water mark, the way the application was desgined, it insert a bunch of data in those tables and at the end it delete the data out (TRUNCATE is not an option). beside moving the objects and rebuild index, is there any other tricky way you guys can recommend?????
The problem can be that a work table like this is normally empty so the CBO will go for a FTS - OK if the HWM is not at 5'000 blocks. After you do a BIG job, you want a way to get the HWM down to a reasonable level (or persuade the CBO to use an index).Quote:
Originally posted by marist89
So what's the point of truncating if you're going to fill it up again? Seems like a lot of work for very little (if any) benefit.
Thanks Jeff and Dapi,
1. I already talked to the one of the top guys who build the application and mentioned about truncate data instead of delete, but he told me that's not the option b/c there were a still some metadata they want to preserved on the table.
2. I did try some test yesterday and I don't understand this. I set timing on and 11 sec for SELECT count(*) on this table (table had 4 million rows), when I deleted all data and query SELECT count(*) and it took 21 sec. the question is : a. why it took longer to query after the delete than the query with data still in there??? b. only 11 sec to query the select, the developer won't be convinced that HWM is the big issue here and he think it's just a minor issue.
What "metadata" would that be? Nothing I can think of that anyone should be relying on.Quote:
Originally posted by learning_bee
1. I already talked to the one of the top guys who build the application and mentioned about truncate data instead of delete, but he told me that's not the option b/c there were a still some metadata they want to preserved on the table.
I am not sure what metadata, I am just under impression that TRUNCATE is not an option according to him.
What do you think about my secdon question????
thanks
Data in buffers from a FTS is the first to be flushed when space is needed - so it's the most sensitive to any other activity on the machine that competes for the buffers. I'd repeat the selects a few times to see what the typical values are.
Dapi,
22 sec for FTS to HWM isn't bad at all, I thought it would take way more than 22 sec for 4 million rows.
Depends on row-length => number of blocks.
Depends on the hardware.
If you read the table once per day, OK.
If you are polling every second for work to do, not OK.
if we have HWM on the table and later on we insert a bunch of data into that table, will Oracle reuse to empty segment or it will use the new one and move the HWM????
The empty blocks below HWM will be reused. And the method you are following (delete, fill the table and rebuild index) is the only option you have as per your application design.Quote:
Originally posted by learning_bee
if we have HWM on the table and later on we insert a bunch of data into that table, will Oracle reuse to empty segment or it will use the new one and move the HWM????
Dont forget to analyze the table at the end of the rebuild of indexes. This is the additional thing you may have to keep in mind.
If there is a block which was "full" and is now less full than PCTUSED, new rows will be inserted in it.
http://download-west.oracle.com/docs...rec.htm#635139
unless you are doing insert /*+APPEND*/ ...Quote:
Originally posted by nagarjuna
The empty blocks below HWM will be reused. And the method you are following (delete, fill the table and rebuild index) is the only option you have as per your application design.
Dont forget to analyze the table at the end of the rebuild of indexes. This is the additional thing you may have to keep in mind.
AH YES! an excellent way to ensure the HWM gets higher and higher!Quote:
Originally posted by davey23uk
unless you are doing insert /*+APPEND*/ ...
The developer knows the application best. If he knows a certain amount of data will be inserted into this table, deleted, and the same amount is inserted again, then he is right. IMHO this sounds like a minor issue at best.Quote:
Originally posted by learning_bee
the developer won't be convinced that HWM is the big issue here and he think it's just a minor issue.
if this temporary table or no constraints just do
insert the metadata needed to a temporary table
drop the old table
rename the temp table
or migrate to 10g and shrink your segments
have you compared the time needed for delete vs truncate?
if these tables are the statging tables, would it be faster/improved performance with we use NOLOGGING option on those tables since they do a massive INSERT and DELETE at the end of teir process???
thanks,
nologging only works for DIRECT INSERT, RTFM