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

Thread: Cache large table !!

  1. #11
    Join Date
    Nov 2002
    Posts
    170
    Originally posted by marist89
    What's the point? If you are continually scanning this table it's probably in cache anyway provided you have a big enough cache. Your management is clueless.

    The point is I am not pushing this table or any other table/data out of the default memory pool. If I keep this table in it's own memory it is not going to interfere with any other process. Other side of it is if I have this huge memory pool will there be any oracle back ground process/resource used up to maintain sync up the table. Also how many latches etc I need to assign for this pool...I guess that's just an trail/error method since there is no thumb rule regarding how many latches to use based on the size of the memory pool.

  2. #12
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Originally posted by dbasupuser
    The point is I am not pushing this table or any other table/data out of the default memory pool. If I keep this table in it's own memory it is not going to interfere with any other process. Other side of it is if I have this huge memory pool will there be any oracle back ground process/resource used up to maintain sync up the table. Also how many latches etc I need to assign for this pool...I guess that's just an trail/error method since there is no thumb rule regarding how many latches to use based on the size of the memory pool.
    Do you realize that this query is going to run for each row in your main cursor? At best you have a range scan, otherwise you are doing a full table scan with this query for each record returned from your main query, not to mention the other inefficiencies that exist in you query, like doing a select count(*) when you don't need to.

    Caching and latches in this case will not help inefficient code.

    Code:
    select cd,hd,sd,st
    into vConDist,vStHseDist,vStSenDist,vState
    from cx_zip_district
    where zip5 = substr(S_ADDR_PER_REC.ZIPCODE,1,5) and
    zip4start <= SUBSTR(S_ADDR_PER_REC.ZIPCODE,-4,4) and
    zip4end >= SUBSTR(S_ADDR_PER_REC.ZIPCODE,-4,4);
    this space intentionally left blank

  3. #13
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yeah ... this makes no sense

    Code:
     select count(*) into numrecs from temp_lds_addr_ids;
    
    if numrecs = 0 then
    
    execute immediate 'truncate table temp_lds_addr_ids';
    commit;
    ...
    insert /*+ append */ into temp_lds_addr_ids
    select /*+ parallel (a, 4) */ zipcode, per_id, row_id
    from s_addr_per a
    where addr_type_cd = 'Home' AND LENGTH(zipcode) >= 9 AND
    ((substr(zipcode,1,5) BETWEEN '00000' AND '99999') AND
    (substr(zipcode, -4,4) BETWEEN '0000' AND '9999'));
    commit;
    ...
    
    end if;
    Why truncate an empty table?
    Why commit after a truncate (which implicitly commits)?
    Why count(*) the entire table to see if it's empty?

    How about ...
    Code:
     select count(*) into numrecs from dual
     where exists (select 1 from temp_lds_addr_ids);
    
    if numrecs != 0 then
    
    execute immediate 'truncate table temp_lds_addr_ids';
    ...
    end if;
    Are you using true temporary tables? I suspect not.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #14
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Long story short, the problem is not going to go away with a bit of table caching. The PL/SQL is just very bad quality.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #15
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Oh, and a little code documentation would be a good practice.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #16
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Why truncate an empty table?
    To reset the HWM. Though I'm not saying it's needed here.

  7. #17
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Originally posted by slimdave
    Oh, and a little code documentation would be a good practice.
    Now you are really asking alot!!!
    this space intentionally left blank

  8. #18
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Originally posted by slimdave
    Long story short, the problem is not going to go away with a bit of table caching. The PL/SQL is just very bad quality.
    To emphasize what Dave is saying, fixing the PL/SQL code
    will yield better results than any other tuning that you can do.
    But with all of the advice you have been given, you should have
    everything you need to fix the code.
    this space intentionally left blank

  9. #19
    Join Date
    Mar 2002
    Posts
    534
    If you update more then 5% of your data it will probably be faster to recreate the entire table instead of updating it. So if it's not a problem for your application and if you updating more then 5% of your data I would make a test using CTS and check how long it takes.

  10. #20
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This is a Siebel CRM application.
    Finally, Don't say it is delivered code, you can't change it.
    You can change the code b/c I am also working with Siebel. I change the code quite often in Peoplesoft and Siebel.

    Tamil

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