DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Reorganization of Table

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dictionary managed? hmmmm

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Pando,
    I did the test both on LMT and DMT.

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    tamilselvan,

    I asked you why you cutted out the timing from your previous post - well, now you have included them. But timings for what?!!?? For creating/truncating the tables! You must be kidding. The whole discussion was about the performance of full table scans, your test with latches included FTS queries and those were the timings I was asking about. So I'm repeating myself agin, and just to make things very clear - ve are talking about the performance of queries, not DDLs:
    Originally posted by jmodic
    We vere talking about performance, yet you cutted the dbms_output of the timing results of both runs. Any particular reason? Maybe the fact that the timing shows no difference between the two? (And if you are claiming that run1 was noticably slower than run2 - or vice versa - then you have a problem elsevere, not directly in the number of extents!)
    It is perfectly clear that we are not talking about any DDL operations here, the performance in any normal context relates to queries (and DML operations). We are more or less all avare of the isues regarding very high number of extents and DDL operations, so the timings in your last post hardly show anything surprising. So you still haven't showed anything that was not allready said in the last paragraph of my previous post (or anything that would proove it wrong for that matter):

    Originally posted by jmodic
    .... doesn't say that extremely high nuber of extents is a good thing, but he also explain what about it is not good: It is only because of the maintainance work that has to be done in dictionary because of that. No impact on queries! None. Zero.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =====================================
    Jmodic wrote:
    So I'm repeating myself agin, and just to make things very clear - ve are talking about the performance of queries, not DDLs:
    =====================================

    Here is the query and execution time:

    set serveroutput on size 50000
    declare
    l_time_1 number ;
    l_time_2 number ;
    l_time_3 number ;
    l_cnt Number ;
    begin

    execute immediate 'truncate table stat_info' ;

    insert into stat_info select 'Before', a.* from stat_info_vw a ;
    l_time_1 := dbms_utility.get_time ;

    execute immediate 'select count(*) from test1' into l_cnt ;

    insert into stat_info select 'Run 1', a.* from stat_info_vw a ;
    l_time_2 := dbms_utility.get_time ;

    execute immediate 'select count(*) from test2' into l_cnt ;

    insert into stat_info select 'Run 2', a.* from stat_info_vw a ;
    l_time_3 := dbms_utility.get_time ;

    commit;
    dbms_output.put_line('Time to complete for Run 1 '||to_char(l_time_2 - l_time_1));
    dbms_output.put_line('Time to complete for Run 2 '||to_char(l_time_3 - l_time_2));
    end;
    /

    Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - Production

    SQL> @rec_call
    Time to complete for Run 1 852
    Time to complete for Run 2 571

    PL/SQL procedure successfully completed.

    SQL>
    Unlike Chris, I hope JMODIC would understand the diff between 852 and 571.

    The next question JMODIC would like to ask is:
    WHY DON'T YOU ADD A WHERE CLAUSE IN THE SELECT STATEMENT?

    Let me put my points very clear.

    1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
    even the disks are stripped.

    2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.

  5. #15
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    SQL> @rec_call
    Time to complete for Run 1 852
    Time to complete for Run 2 571

    PL/SQL procedure successfully completed.

    SQL>
    Unlike Chris, I hope JMODIC would understand the diff between 852 and 571.
    I've written this two time allready, I'll repeat myself for the third time:
    Originally posted by jmodic
    And if you are claiming that run1 was noticably slower than run2 - or vice versa - then you have a problem elsevere, not directly in the number of extents!
    Observe this:
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
    With the Partitioning option
    JServer Release 9.0.1.1.1 - Production
    
    SQL> drop table test1;
    
    Table dropped.
    
    SQL> drop table test2;
    
    Table dropped.
    
    SQL> alter tablespace test coalesce;
    
    Tablespace altered.
    
    SQL> create table test1
      2  storage (initial 16K next 16K pctincrease 0 maxextents 10000)
      3  tablespace test
      4  nologging
      5  as select * from test_sample;
    
    Table created.
    
    SQL> create table test2 storage (initial 10M next 10M pctincrease 0)
      2  tablespace test
      3  nologging
      4  as select * from test_sample;
    
    Table created.
    
    SQL> select count(*) from test1
      2  union all
      3  select count(*) from test2;
    
      COUNT(*)
    ----------
        224088
        224088
    
    SQL> column segment_name  format a15
    SQL> select segment_name, extents, bytes
      2  from user_segments where segment_name like 'TEST_';
    
    SEGMENT_NAME       EXTENTS      BYTES
    --------------- ---------- ----------
    TEST1                 1546   25329664
    TEST2                    3   31457280
    
    SQL> create or replace procedure test_fts as
      2    l_time_1 number ;
      3    l_time_2 number ;
      4    l_time_3 number ;
      5    l_cnt number;
      6  begin
      7    l_time_1 := dbms_utility.get_time ;
      8    execute immediate 'select count(*) from test1' into l_cnt ;
      9    l_time_2 := dbms_utility.get_time ;
     10    execute immediate 'select count(*) from test2' into l_cnt ;
     11    l_time_3 := dbms_utility.get_time ;
     12    dbms_output.put_line('Time to complete for Run 1 (about 1500 extents):'
     13                          ||to_char(l_time_2 - l_time_1));
     14    dbms_output.put_line('Time to complete for Run 2          (3 extents):'
     15                          ||to_char(l_time_3 - l_time_2));
     16  end;
     17  /
    
    Procedure created.
    
    SQL> set serveroutput on
    SQL> exec test_fts
    Time to complete for Run 1 (about 1500 extents):345
    Time to complete for Run 2          (3 extents):343
    
    PL/SQL procedure successfully completed.
    
    SQL> exec test_fts
    Time to complete for Run 1 (about 1500 extents):349
    Time to complete for Run 2          (3 extents):343
    
    PL/SQL procedure successfully completed.
    
    SQL> exec test_fts
    Time to complete for Run 1 (about 1500 extents):339
    Time to complete for Run 2          (3 extents):336
    
    PL/SQL procedure successfully completed.
    
    SQL> exec test_fts
    Time to complete for Run 1 (about 1500 extents):341
    Time to complete for Run 2          (3 extents):336
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Now what? The proof that "many extents mean bad performance" suddenly becomes vapour, IMHO. Note that I deliberately performed the above mesurments on tables with HUGE difference in number of extents (1500 versus 3), just to make it more obvious.

    Oh, yes, Run1 consistently outperformed the Run2, but I'm sure TAMISELVAN (no to mention Chris ) will notice what was the difference between the two. Btw, I was able to perform tests where Run1 (on 1500 extents) consistently outperformed Run2 (on 3 extents). But I don't drive any conclusons from that fact, because I happen to understand the reason for this. And just as a side note, tablespace TEST contains only one single file, residing on my home single-disk PC, so there is no mistery with some RAID systems or alike.

    Originally posted by tamilselvan
    The next question JMODIC would like to ask is:
    WHY DON'T YOU ADD A WHERE CLAUSE IN THE SELECT STATEMENT?

    Let me put my points very clear.

    1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
    even the disks are stripped.

    2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.
    Oh come on, don't you think you are making up childish arguments here? We've covered those topics many times on this forums. Do you realy think I don't know what full table scan is? And BTW, the explanation for point 2 that you have provided in your last sentence is amusingly absurd!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #16
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    Thanks to me for initiating such a gr8 discussion.

    Can I conclude that Many Extents is not a problem.

    So I m not reorganizing this table at present and better I concentrate on tuning aspects other than just extents.

    Thanks to both Jmodic and tamilselvan.

    Vijay
    --------------------------
    The Time has come ....

  7. #17
    Join Date
    Oct 2002
    Posts
    391
    Originally posted by jmodic
    liked the following question in vijay's post:
    "Then for what reasons reorganization is done?" I'd say in 95% of cases it is done only because DBBSs must do something to show to their bosses to justify their job possitions and their sallaries. In 99% of those 95% cases unfortunately those same DBBSs don't have a clue that they are just wasting time and their computer resources. They actually think that those rorgs are the prime reason why their databases are in such an excelent shape .

    hmmmm, so what are the compelling conditions that will make the reorganizations necessary and its not just another "thats why i am gettign paid for".....

  8. #18
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you had something like 10,000 extents, then a reorg ( = moving the table to a locally maaged TS with lareger uniform extent size) would be justifield, but I'm happy myself to go up to a few thousand extents.

    Good responses Jurij.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #19
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Let me put my points very clear.

    1 If the application performs full table scan (fast full index scans), then keep the number of extents to minimum,
    even the disks are stripped.

    2 On the otherside, if the segment is accessed via index look up, then you need not worry about the number of extents. B/c the amount of data fetched via index look up is very minimum.
    your reference to ixora contradicts your advice quoted above.

    If you are performing a full table scan then the overhead of having to read multiple segment header blocks to get all the extent locations is relatively very small.

    If you are performing an index lookup of a few rows, then the overhead of the extra segment header read is relatively large.

    So based on that, full table scans are not as sensitive to high numbers of extents as index lookups.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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