Schema area extent

Posted by bremmeyr on 21-Jul-2016 16:15

What causes growth of the database schema area file extent? I expect there is not any data in this area so what will make it grow? 

With my case in the database all the tables and indexes are type 2. There is 1 application table in the schema area due to someones mistake. That 1 table has zero records. The only indexes that show in the Schema area are default indexes for a hand full of 1 record tables that have no indexes defined. 

Different customer's files have different sizes. The application schema are the same. 

DBanalys shows total of the size column is 2.6 MB for the records and 430 K for the indexes. 

Example database.d1 file sizes:

Customer A, 300 MB

Customer B, 1.38 GB

Customer C, 69 MB

Progress OpenEdge 11.5.1, Windows server

All Replies

Posted by ChUIMonster on 21-Jul-2016 16:26
Do you have any BLOB/CLOB data in the schematic area?

(I'd move the accidental and default stuff on principle.)

--
Tom Bascom
Posted by steve pittman on 21-Jul-2016 17:42
If the users use OE sql on their db, and if they create sql statistics (via UPDATE STATISTICS),  then the statistics go into schema tables in the schema area.
This could account for some of the growth, depending on the “if”  questions above.
 
Hope this helps,      …..steve pittman
 
 
Posted by Rob Fitzpatrick on 21-Jul-2016 18:03

Do you know the history of those DBs?  Is it possible the schema area contained many objects in the past, and they have been moved into Type II areas?

Those are some very large schema areas.  Mine area typically 10 MB or less, with over 1300 schema objects. Given the size totals for records and indexes you've given, the extends weren't caused buy the data that's there now.  What is the HWM of the area?

find dictdb._area no-lock where _area._area-number = 6.
find dictdb._areastatus no-lock where _areastatus._areastatus-areanum = _area._area-number.
display
  _area._area-blocksize
  _areastatus._areastatus-hiwater
  _areastatus._areastatus-totblocks
.
Posted by George Potemkin on 22-Jul-2016 00:43

Dbanalys reports LOB objects only since Progress V11.6 but their space is counted in the messages:

212 RM block(s) found in the storage area.
91.45% of the RM block space is used.
Posted by bremmeyr on 22-Jul-2016 10:34

Thanks to all for your input.

Tom

No clob, no blob

Good point that the objects need to be moved.

Steve:

About SQL, what if they use any ODBC connection for any SQL client?  I know they use ODBC connections.

Rob:

Yes the schema area contained objects. They were moved, perhaps months ago. That could account for the current size.

Yet with example customer B the area has grown after the objects have moved.

Example database.d1 file sizes:

Customer A, 300 MB; block size 4096, area hiwater 69,336; area total blocks 75,022

Customer B, 1.38 GB; block size 4096, area hiwater 362,991; area total block size 491,005

From DBanalysis

362991 block(s) found in the area.

Current high water mark: 362991

   361921 free block(s) found in the area

   753 record block(s) found in the area

   314 index block(s) found in the area

   0 empty block(s) found in the area

   1 object block(s) found in the area

Customer C, 69 MB; block size 4096, area hiwater 17,382; area total block size 17,391

Posted by George Potemkin on 22-Jul-2016 10:44

Use 'proutil db -C mvsch' and delete "Old Schema Area"

Posted by steve pittman on 22-Jul-2016 11:18

>>> Steve:

>>>  About SQL, what if they use any ODBC connection for any SQL client?  I know they use ODBC connections.

The db gets sql statistics, in the sql schema tables in the schema area,  only when some DBA does the UPDATE STATISTICS sql command for the db.
Simple use of ODBC, say for reporting, does not create and/or change sql statistics.
 
Hope this helps,     …..sjp
 
Posted by bremmeyr on 22-Jul-2016 12:47

FYI, no SQL statistics are involved.

systblstat has zero records.

Posted by bremmeyr on 22-Jul-2016 12:51

Hi All, Thank you for your replies and input.

I took a backup of one of the DB's, restored the DB, moved the default indexes and 0 record table out of the area. Then I did the move schema area, mvsch. The result is a schema area extent that is 4 MB.

I think this confirms the size of the extent was the result of the records for the tables that have been moved to a data area.

Thank you, again.

Posted by George Potemkin on 22-Jul-2016 13:11

RM blocks in SAT1 can not change their type to the Free blocks. All free blocks were the index blocks in past.

> 361921 free block(s) found in the area

Posted by bremmeyr on 22-Jul-2016 15:19

I am trying to find a way to see what objects are in schema area 6 from ABL. Should the following work?

I get more results than I expect. Tables that are not in the schema area are included in the result list. .

 for each _file no-lock

   WHERE _ianum = 6:

   disp  _file-name _ianum.

 end.

 for each _Index no-lock

     WHERE _ianum = 6

     AND  NOT  _index-name BEGINS '_':

   disp _Index-name _ianum.

 end.

Posted by Rob Fitzpatrick on 22-Jul-2016 15:22

Don't use _ianum, it isn't accurate.

Read _storageobject for _area-number = 6, join to _file and _index (based on __object-number and _object-type) to get the tables and indexes, and filter out anything that is a system object.

Posted by Rob Fitzpatrick on 22-Jul-2016 15:30

Simple example, assumes no LOBs:

/* application objects in schema area */
for each _storageobject no-lock where _area-number = 6: case _storageobject._object-type: when 1 then /* table */ do: find _file no-lock where _file-number = _object-number. if _tbl-type = 't' then display _file-number _file-name. end. when 2 then /* index */ do: find _index no-lock where _idx-num = _object-number. if not _index-name begins "_" then do: find _file no-lock of _index. display _file-number _file-name _idx-num _index-name. end. end. end case. end.
Posted by bremmeyr on 22-Jul-2016 15:33

Solid.  Thanks again.

This thread is closed