-
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.
-
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);
-
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.
-
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.
-
Oh, and a little code documentation would be a good practice.
-
Originally posted by slimdave
Why truncate an empty table?
To reset the HWM. Though I'm not saying it's needed here.
-
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|