demonstrated how bad HWM would be for FTS
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: demonstrated how bad HWM would be for FTS

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    demonstrated how bad HWM would be for FTS

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Set events 10046.

    Use tkprof to get report from the trace file.

    Tamil

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: demonstrated how bad HWM would be for FTS

    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
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Oct 2003
    Posts
    312
    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?????

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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).

  8. #8
    Join Date
    Oct 2003
    Posts
    312
    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.
    Last edited by learning_bee; 01-05-2005 at 10:28 AM.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  10. #10
    Join Date
    Oct 2003
    Posts
    312
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width