• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

RE: Jobs staying at status 'S'

epost

Active Member
RE: Jobs staying at status \'S\'

The number of records in a table is not the figure you need if you are
concerned with the possibility of full table scans. You need to look at the
high water mark, if I have a table with 1 million recs, delete all but one
and issue a query that requires a full table scan it will still scan the
space held by the million recs. If I was there I would monitor the session
running the job, trap the SQL using SQL Trace and figure out where it is
taking a lot of time. More than likely it is a full table scan somewhere.
I have seen one of the tables involved with invoicing which is a used to
temporarily place a record then delete it balloon to huge sizes because
someone picked the wrong data selection on a job. Nothing bad happens, but
the user never tells anyone and then performance suffers. We soon discover
that the table is huge in comparison to the total number of records it now
stores. I suggest having your DBA look for tables with lots of empty space
on a regular basis.

- Ethan
- www.FreeOCP.com <http://www.FreeOCP.com>
 

epost

Active Member
RE: Jobs staying at status \'S\'

If you have a DBA he/she should already be analyzing your tables on a
regular basis. If so then you can easily query the DBA_TABLES and
DBA_SEGMENTS tables to find any problems tables. Something like...

SELECT
a.owner,
a.table_name,
a.num_rows,
trunc(b.bytes/1024/1204) sz_mb,
trunc(a.num_rows * a.avg_row_len/1024/1204) expected_sz_mb,
trunc(b.bytes/1024/1024 - (a.num_rows * (a.avg_row_len/1024/1024)))
dif_mb,
trunc((1 - (a.num_rows * (a.avg_row_len/1024/1204)) /
(b.bytes/1024/1204))*100) per_dif
FROM
dba_tables a,
dba_segments b
WHERE
a.owner = b.owner and
a.table_name = b.segment_name and
b.segment_type = 'table' and
a.owner not in ('SYSTEM', 'SYS') and
trunc((1 - (a.num_rows * (a.avg_row_len/1024/1204)) /
(b.bytes/1024/1204))*100) > 25 and
b.bytes > 5000000
ORDER BY
trunc((1 - (a.num_rows * (a.avg_row_len/1024/1204)) /
(b.bytes/1024/1204))*100) desc;

The query above show objects over 5MB with "guestimated" freespace over 25%
sorted from worst to bad. Object that show up on my radar are F0414A,
F01131M, F0411A, F01131 and the ill fated F986110 which has a huge problem
with multiple processes performing full table scans on it as they check the
queue. I have cached this table which has solved the problem for me.

- Ethan
- www.FreeOCP.com
 

ijc

Well Known Member
RE: Jobs staying at status \'S\'

(no text)

Live:B7321, Oracle 7, NT
Test:Xe, Oracle 8, NT
 
Top