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>
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>