demonstrated how bad HWM would be for FTS

[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

[tamilselvan]

Set events 10046.

Use tkprof to get report from the trace file.

Tamil

[stmontgo]

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

set timing on

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

[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?????

[DaPi]

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?

[marist89]

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?????

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.

[DaPi]

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.

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).

[learning_bee]

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.

[DaPi]

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.

What "metadata" would that be? Nothing I can think of that anyone should be relying on.

[learning_bee]

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