cancel
Showing results for 
Search instead for 
Did you mean: 

Linking a Tablespace issue and DB STATS job

Former Member
0 Kudos

PLatform: WIndows/Oracle 10g

I just started at a new site that has had a high turn over of staff and i;m trying to bring some stability to the landscape. In some of my investigations, our Production server has not ran a sucessful weekly scheduled Stats/optimizer job in 4 years. (no joke)

i'm wprking to correct the issue. found oss# 1165319 and #830576 that appears to point to the solution.

But this past week, our PSDAT Tablespace is run out of space 4 times. We have a huge LOBSEGMENT file that is growing at an extreame rate in the PSDAT tablesapce.

Could I assume to make the link here between no DB stats/optimizer job ever ran in the system and a LOBSEGMENT file growing out of control???

ALso to my knowledge, you will not be versy successful at doing a system export or EHP upgrade is your stats jobs is not able to run. True or False?

Accepted Solutions (0)

Answers (1)

Answers (1)

Reagan
Advisor
Advisor
0 Kudos

In some of my investigations, our Production server has not ran a sucessful weekly scheduled Stats/optimizer job in 4 years. (no joke)

Check why the statistics collection job is not running. If it is scheduled then check the reason why it fails.

Could I assume to make the link here between no DB stats/optimizer job ever ran in the system and a LOBSEGMENT file growing out of control???

Outdated statistics cannot be the reason for the growth of the LOBSEGMENT

ALso to my knowledge, you will not be versy successful at doing a system export or EHP upgrade is your stats jobs is not able to run. True or False?

False

What made you say this ? Is this mentioned somewhere (reliable) ?

system export - You basically take the copy of the SAP system to the OS level in a dump file.

EHP upgrade - Outdated statistics cannot prevent an EHP upgrade. If the statistics are updated it will smoothen the execution of the process.

Regards

RB

Former Member
0 Kudos

Thanks RB.

So I'm trying t track down what is eating all the space.

I found that the Logsegment file in my PSAPDAT tablesapace is the TST03 table.

I'm sorta dry on the oracle DBA skills, what more details can I find and how should I do it once I have this info.

WHats my next step?

Or should I go see an ABAPEr and have them explain to me what is being written to this table.

Former Member
0 Kudos

Some digging and its leading me to TEMSE..

OK im good from here

Reagan
Advisor
Advisor
0 Kudos

Table TST03 is a temse table

Note 10551 - Table TST03 grows

First check the house keeping job for the spool requests is running.

If not delete them

Note 48400 - Reorganization of TemSe and Spool

Note 130978 - RSPO1041 - Replacement for RSPO0041

Regards

RB

Reagan
Advisor
Advisor
0 Kudos

I would consider doing a reorg of the table TST03 using the export import method after deleting the obsolete data.

Note 646681 - Reorganizing tables with BRSPACE

Note 500340 - FAQ: LOBS

Regards

RB

Former Member
0 Kudos

Would like to add to to first check the oldest spool req present and then slowly start deleting say 10k through RSPO1041 and then also the reorg of Temse .

Later u reorg the table

and also schedule the program RSPO1041 and create a variant to leave only selected days of spool requests in the system.

Innitially u will have to monitor the system after reorg that TST03 table growth.

Thanks

Former Member
0 Kudos

Thanks

Very helpful links.

We have been trying to run the TemSe consistency check for a day now. At first I just used the dialog process. It timed out. Then I up the time of the dialog process run time, that timed out.

Then we ran RSTS0020 in the background and its gotten much further but still failing with ABAP dumps.

I'm getting a TSV_TNEW_OCCURS_NO_ROLL_MEMORY and a Spool_I/O_Error.

I deleted all the old spool jobs, actually wasn't that many.

So i'm still researching my issue.

Reagan
Advisor
Advisor
0 Kudos

I faced this issue couple of years ago and I did a truncate for the tables TST01 and TST03

Then I rebuilt all the indexes for both the tables.

Regards

RB

Former Member
0 Kudos

OK so where am I?

This link has helped allot

http://help.sap.com/saphelp_nw04/helpdata/en/fc/04ca3bb6f8c21de10000000a114084/content.htm

I've ran report RSTS0020 in the background.

Took along time but it ran.

Ran report RSTS0030 in dialog.

It times out when i try and run for both TST01 and TST03.

I can run against TST01. Actually did and cleaned lots of entries.

So now When i run report RSTS0030 against TST03, it times out.

the help link says not to run it in the background, must be dialogue.

I guess I can change my RDISP/ WPruntime to longer but any other tricks? I blieve my TST03 table is 148 gigs.

Any tips of how I can get my report done on TST03 so I can clean it?

Reagan
Advisor
Advisor
0 Kudos

I would first try a reorg of the tables TST01 and TST03 and rebuild the indexes.

They try to delete data with the reports from the above notes.

If that doesn't help then I will issue a truncate against both the tables and rebuild the indexes.

Regards

RB