This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: Export

HowTo: Using DBMS_STATS to Restore Stats

Scenario: You’re about to implement some major changes to the Oracle database that will adjust the database statistics.
Oracle provide the DBMS_STATS package to help administer stats.  The package includes some procedures that can be used to export/import stats, plus restore them to a previous point in time.

When statistics are updated using DBMS_STATS.GATHER_*_STATS, it saves the previous version in the database (can be changed with DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure).  Also, see table DBA_TAB_STATS_HISTORY.

These versions are retained for a specific retention period, which you can check using the GET_STATS_HISTORY_RETENTION procedure:

SQL> set serveroutput on
SQL> DECLARE
v number;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE(‘Stats history retention: ‘ || v || ‘ days.’);
END;
/

Stats history retention x days.

PL/SQL procedure successfully completed.

You can also check the date of the oldest stats history:

SQL> set serveroutput on
SQL> DECLARE
v timestamp;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
DBMS_OUTPUT.PUT_LINE(‘Oldest stats history: ‘ || v);
END;
/

Oldest stats history: 15-DEC-13 11.29.32.143688 PM

PL/SQL procedure successfully completed

To restore the statistics you can use one of the relevant procedures:

DBMS_STATS.RESTORE_DICTIONARY_STATS
DBMS_STATS.RESTORE_FIXED_OBJECT_STATS
DBMS_STATS.RESTORE_SCHEMA_STATS
DBMS_STATS.RESTORE_SYSTEM_STATS
DBMS_STATS.RESTORE_TABLE_STATS

See here for parameters:
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#insertedID2
As an example, the RESTORE_SCHEMA_STATS procedure takes the following parameters:

ownname   Schema owner,
timestamp   Timestamp,
force   TRUE|FALSE   Restore even if stats are locked, default TRUE,
no_invalidate   TRUE|FALSE   Invalidate cursors, default get_param(‘NO_INVALIDATE’).

If the stats are restored to a specific timestamp, it means that whatever statistics values were applicable to a specific table at a specific point in time, are applied to the tables.  If the table’s statistics are not changed then there will be gaps in the history.
You can imagine this being like a roll-forward through the DBA_TAB_STATS_HISTORY table, until the timestamp specified.

WARNING: If the table’s statistics are not changed then there will be gaps in the history.  In which case, you may not be able to restore previous statistics if the table stats have not changed within the last history window (default 31 days).

Some great examples are here: https://www.morganslibrary.org/reference/pkgs/dbms_stats.html

You should also note, that under an SAP system, the Oracle stats gatherer is called by BR*Connect, and note that it calls the GATHER_TABLE_STATS procedure for each table that is mentioned in table DBSTATC for tables that have stats enabled in DBSTATC.
If the table is not enabled to collect stats, then it may have stats delivered by SAP (see SAP note 1020260), in which case, there may not be any history.

Also see my blog post on SAP statistics and DBSTATC.

SAP Unicode Conversion MAXDOP Parallelism on MS SQL Server

When performing a Unicode conversion of an SAP system running on MS SQL Server database, you get pointed to the Unicode Collection Note 1319517.
This note points to a MS SQL Server specific note (Note 1054852 – Recommendations for migrations to MS SQL Server)  which covers a couple of different scenarios, such as moving to SQL Server from another database provider, or just performing an R3load export/import (like me) to the same platform.

Now from experience, I know that the R3load import is actually pretty quick (especially on Oracle with DIRECTPATH).  What takes the time is the index creation afterwards.  Lots of scanning and I/O needs to be done.  This is where you waste time, and where you could save time.
The note 1054852 mentions the use of the MAXDOP (Maximum Degree of Parallelism) SQL Server parameter that could benefit any subsequent index creation/rebuild tasks performed during an R3load import.
The recommendation in note 1054852 is to change the MAXDOP from the SAP recommended setting of 1, to 4 for the entire SQL Server instance.  NOTE: The maximum MAXDOP in 2008R2 is 1024 (see here and SAP note 1654613).
This is the “hammer” approach and should be used with caution.  There is a nice blog here on the use of MAXDOP with SQL Server.  The blog shows how setting this to a value greater than 1 can actually increase fragmentation.  This is completely understandable.  However, the reader must note that the fragmentation is only an issue if the index is specifically set with ALLOW_PAGE_LOCKS to “OFF” (the default in 2008R2/2012 is “ON” (see syntax here)! ).
There is another blog article that shows how this fragmentation problem is overcome by setting the ALLOW_PAGE_LOCKS option.  The article states that by default this is “ON”.  However, according to Microsoft KB 2292737, the default is “OFF” by design. 
So which is it?  In fact, the MS syntax for “ALTER INDEX” specifically states that it is not possible to reorganise an index with ALLOW_PAGE_LOCKS set to “OFF”.

Here’s how to check the value of the ALLOW_PAGE_LOCKS setting for an index (there is no global setting):


use <SAP DB>
go
select name,type,allow_page_locks from sys.indexes
where allow_page_locks != 1
order by 1;

And the results…  well, some of the largest SAP tables in my system have their indexes set with ALLOW_PAGE_LOCKS to “OFF“.  Great!

NAME
TYPE
ALLOW_PAGE_LOCKS
ARFCRDATA~0
1
 0
ARFCSDATA~0
1
0
COVREF~0
1
0
COVREF~001
2
0
COVREF~002
2
0
D010INC~0
1
0
D010INC~1
2
0
D010TAB~0
1
0
D010TAB~1
2
0
EDI40~0
1
0
EDIDC~0
1
0
EDIDC~1
2
0
EDIDC~2
2
0
EDIDC~3
2
0
EDIDC~4
2
0
EDIDS~0
1
0
EDIDS~1
2
0
EDIDS~2
2
0
REPOLOAD~0
1
0
REPOSRC~0
1
0
REPOSRC~SPM
2
0
TRFCQIN~0
1
0
TRFCQIN~1
2
0
TRFCQIN~2
2
0
TRFCQIN~3
2
0
TRFCQIN~4
2
0
TRFCQIN~5
2
0
TRFCQIN~6
2
0
TRFCQOUT~0
1
0
TRFCQOUT~1
2
0
TRFCQOUT~2
2
0
TRFCQOUT~3
2
0
TRFCQOUT~4
2
0
TRFCQOUT~5
2
0
TRFCQOUT~6
2
0
VBDATA~0
1
0
VBHDR~0
1
0
VBMOD~0
1
0

I can understand the VB* tables might not want index locking and this is also hinted at in the older SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0 web page.  However, where did the other tables come from?
I took a look on SAP Notes but I was unable to find anything definitive.  The system I was working on was recently copied and used SWDM (SAP Software Deployment Manager) to perform the post-copy steps, so it’s possible that the indexes were automatically adjusted (ALTER’d) to try and ensure a consistent approach.
What to do next?  Well, some of those tables can be ignored since they are supposed to have ALLOW_PAGE_LOCKS set to “OFF”, some, like REPOLOAD are re-populated only during a system-copy with R3 tools (e.g. Unicode conversion), so you could try adjusting the setting during the R3load import. 
The others, well in theory you would try to minimise data in those tables (like TRFC*) before you perform a Unicode conversion, so the indexes wouldn’t be massive anyway.

At the end of the day, all we are talking about here is a little fragmentation.  So let’s move on.

Let’s go back to the MAXDOP setting recommendation mentioned in SAP note 1054852. 
Now, my system happens to be a BW type system (it’s actually SEM, but this is just BW in disguise), so I found SAP Note 1654613 – SQL Server Parallelism for SAP BW which suggests that for SAP BW systems, you can now manage the MAXDOP parameter settings in table RSADMIN through report SAP_RSADMIN_MAINTAIN by setting parameters MSS_MAXDOP_QUERY, MSS_MAXDOP_<cubename> and MSS_MAXDOP_INDEXING.
The note 1654613 also goes on to say that by applying the note corrections (or the related support package stacks) the default MAXDOP for BW queries is set to 2, and for (process chain) index creations is set to 8.
Aha! 
So the Unicode collection note’s setting of 4, could actually contradict the setting of 8 in BW systems!
The note 1654613 also states that you may increase the MAXDOP for queries to more than 4, but this depends on the number of worker threads configured in your SQL Server instance.
The worker threads setting in my SQL Server 2008R2 instance was set to “0”, which means the number is dynamically calculated (see here).

You can use the sp_configure procedure to check your setting:

sp_configure @configname='max worker threads';

 You can query the current number of threads (thanks to https://blogs.msdn.com/b/boduff/archive/2008/05/17/configuring-max-worker-threads-in-sql-2005.aspx):

select count(*) from sys.dm_os_threads;

 My idle database was using 50 threads on a VMWare VM with 4 processors.  So I guess I could increase the MAXDOP for queries permanently in my BW (SEM) system.

You should also note the setting MSS_MAXDOP_INDEXING = 0 means that all available threads will be used during (process chain) index creation.

Summary:
We are advised to set MAXDOP to 4 when moving an SQL Server database or performing a system-copy or Unicode conversion. 
However, more detailed analysis has shown that for BW systems specifically, we can potentially adjust the MAXDOP setting even higher than 4 during our R3load import, to ensure that we make the best use of the available CPU. 
This is underlined by the fact that defaults within the BW system are sometimes higher than the recommended setting of 4.
Therefore, I will be trying out a value of 12 (default of 8 for index creation + 50%) in my Unicode conversion:

sp_configure 'max degree of parallelism', 12;
reconfigure with override

SAP R3load Error TOC is Not From Same Export

During an R3load import process, you are importing the data files generated from a successful R3load export.
However, you are seeing errors in the <PACKAGE>.log file along the lines of:
(DB) INFO: connected to DB
(DB) INFO: DbSlControl(DBSL_CMD_NLS_CHARACTERSET_GET): UTF16
(RFF) ERROR: <PACKAGE>.TOC is not from the same export as <PACKAGE>.001
This occurs when the TOC (table of contents) file is corrupt.
The TOC file is generated during the export process and contains the name(s) of the tables in the export package and the number of records inside the relevant data files (files ending with .nnn   e.g. .001).
The corruption can happen if you terminated an export, or an export failed (maybe because of disk space), and you then restarted the export (either through the SUM, SAPInst or by manually editing the .properties file).
If you failed to remove the already generated .TOC file for the failed package, before restarting the export, then the .TOC file will be confused and think that the subsequent export, is an append operation to the existing data file.
A normal .TOC file should have something like:

vn: R7.20/V1.6
id: de1a50a500000043
cp: 4102
data_with_checksum
tab: [HEADER]
fil: <PACKAGE>.001 1024
1 1
eot: #0 rows 20130902160509
tab: <TABLE>
fil: <PACKAGE>.001 1024
2 1024000
eot: #50212404 rows 20130902184553
eof: #20130902184553

A corrupt .TOC file for the same package, would look something like:

vn: R7.20/V1.6
id: de1a50a500000043
cp: 4102
data_with_checksum
tab: [HEADER]
fil: <PACKAGE>.001 1024
1 1
eot: #0 rows 20130902160509
tab: <TABLE>
fil: <PACKAGE>.001 1024
2 1024000
eot: #50212404 rows 20130902184553
tab: <TABLE>
fil: <PACKAGE>.001 1024
1024001 2048000
eot: #50212404 rows 20130903161923
eof: #20130903161923
Notice the additional four lines generated in the file during a second export attempt.
This would cause the import to fail.
It’s not possible to adjust the .TOC file manually, as it seems that the .TOC file and the data files are tied with a checksum somehow.
The only time you will find out that the export .TOC files are corrupt, is when you try to import them.  Maybe SAP could write a verification routine into the R3load program.

Checking R3load Export Progress

When running R3load to export an Oracle SAP database, it’s difficult to see the exact table or tables that is/are being exported.

You can log into the Oracle database during the R3load execution and use the following SQL to follow the progress:
SQL> select sess.process, sql.sql_text
       from v$session sess,
            v$sqltext sql
      where sess.type='USER'
        and sess.module like 'DBSL%'
        and sql.sql_text like '%FROM%'
      order by sql.part;
This will show the OS process ID of the R3load process, plus the table (from the FROM clause)  that is currently being exported.
For large tables, you may be able to see the progress in the V$SESSION_LONGOPS table by looking for rows where TOTALWORK != SOFAR.

Estimating Oracle Export Space Requirements

To estimate a full Oracle export space requirements, you can use DataPump.
The below command estimates for a full export of the database.
It makes use of the ESTIMATE_ONLY option on the expdp command line.

First you need to create your directory in the database:

> sqlplus / as sysdba

SQL> create directory dp_dump as '/your_path';

SQL> quit;

> expdp "/ as sysdba" full=y directory=DP_EXPORT logfile=full_exp_estimate.log estimate_only=yes
...

Total estimation using BLOCKS method: 2.418 GB

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 18:50:48

The method above runs through each object to be exported and calculates the number of blocks relevant to the block size of each object, that will be required on disk.

If you have a large database and your statistics are up-to-date, then you could use the additional “ESTIMATE=STATISTICS” option, which uses the data gathered from the Oracle statistics collections to estimate the space required. This is a lot quicker but needs accurate stats.

The example above took 1 min.

With the “ESTIMATE=STATISTICS” option, it took 46 seconds, but estimated only 991.3 MB would be required (half as much as the BLOCKS method).  There’s obviously some missing stats on objects in my DB.