epost
Active Member
Oracle 8.0.4.4
B733.1
HPUX 11
All,
I have been perplexed for sometime with a problem of the F986110 table and
it's related job queue Oracle processes. I have written about this problem
in the past and even offered solutions. However upon more investigation I
have found other difficulties. I'm interested in knowing if this problem
has been fixed in XE or if you have come to a better resolution than I.
Each job queue repeatedly scans the F986110 table for new job submissions.
A new job is designated by it's status. After the job is completed the
information is retained in the same table but the status is changed. Over
time this table will grow very large unless you implement some sort of
cleanup job. However even deleting data from the table will not actually
shrink it in size because the high water mark will not be reduced. The
effect is that any operation needing to scan the entire table for data will
always scan up to the high water mark (most of the SQL on this table will
execute FTS regardless of index because WHERE statements are not selective
enough).
My initial solution was to cache the table (ALTER TABLE n CACHE). This
stopped the application from going to disk all the time for the data but it
has introduced a new dilemma. Since the table is now loaded into memory
almost constantly the CPU has no trouble reading the blocks in memory very
quickly. However with 5+ processes touching this table at all times getting
a consistent view of the data requires tremendously inflated numbers of
buffer gets on this table. The CPU say, "hey no trouble it's all right here
in memory, let me get it 100 times!", this results in a CPU use of about 10%
per process as it busily reads all the data over and over again. It also
results in a very healthy looking buffer cache hit ratio but this is not a
good way to obtain it.
I would like to know if this is still a problem in XE, to determine if you
are having a problem also. Here is what you need to know/look for.
1. How many job queues do you have configured?
2. How many records are in your F986110 table and what is it's current size?
3. What is the average CPU use for each of these processes (use top in NIX)?
4. What is the interval your queues are set up to query the F986110 table?
5. What is the high water mark on the F986110 table?
Please let me know if you also think this might be a problem. A potential
solution is to move the records to an job archive table as soon as possible
and build an JDE interface to show the records in the job archive table.
That way the table stays very small. In 8i you can configure a KEEP buffer
pool and stuff the table in that so that it doesn't effect standard buffer
pool hit ratios. In 9i you will be able to configure a completely separate
KEEP buffer pool just for this table.
Thanks,
Ethan
------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.
==============================================================================
B733.1
HPUX 11
All,
I have been perplexed for sometime with a problem of the F986110 table and
it's related job queue Oracle processes. I have written about this problem
in the past and even offered solutions. However upon more investigation I
have found other difficulties. I'm interested in knowing if this problem
has been fixed in XE or if you have come to a better resolution than I.
Each job queue repeatedly scans the F986110 table for new job submissions.
A new job is designated by it's status. After the job is completed the
information is retained in the same table but the status is changed. Over
time this table will grow very large unless you implement some sort of
cleanup job. However even deleting data from the table will not actually
shrink it in size because the high water mark will not be reduced. The
effect is that any operation needing to scan the entire table for data will
always scan up to the high water mark (most of the SQL on this table will
execute FTS regardless of index because WHERE statements are not selective
enough).
My initial solution was to cache the table (ALTER TABLE n CACHE). This
stopped the application from going to disk all the time for the data but it
has introduced a new dilemma. Since the table is now loaded into memory
almost constantly the CPU has no trouble reading the blocks in memory very
quickly. However with 5+ processes touching this table at all times getting
a consistent view of the data requires tremendously inflated numbers of
buffer gets on this table. The CPU say, "hey no trouble it's all right here
in memory, let me get it 100 times!", this results in a CPU use of about 10%
per process as it busily reads all the data over and over again. It also
results in a very healthy looking buffer cache hit ratio but this is not a
good way to obtain it.
I would like to know if this is still a problem in XE, to determine if you
are having a problem also. Here is what you need to know/look for.
1. How many job queues do you have configured?
2. How many records are in your F986110 table and what is it's current size?
3. What is the average CPU use for each of these processes (use top in NIX)?
4. What is the interval your queues are set up to query the F986110 table?
5. What is the high water mark on the F986110 table?
Please let me know if you also think this might be a problem. A potential
solution is to move the records to an job archive table as soon as possible
and build an JDE interface to show the records in the job archive table.
That way the table stays very small. In 8i you can configure a KEEP buffer
pool and stuff the table in that so that it doesn't effect standard buffer
pool hit ratios. In 9i you will be able to configure a completely separate
KEEP buffer pool just for this table.
Thanks,
Ethan
------------------------------------------------------------------------------
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.
==============================================================================
. Submit said script via cron every 30 minutes. This temporary solution worked well for around 12 months when we were on Oracle 8.0.5.
