Oracle Demantra Implementation Guide Supplement

Overview of Database Health Check

Demantra provides a database procedure that you can run at regular intervals that checks tables for missing primary keys, unoptimized ordering of null columns, and other conditions that may negatively impact database performance. You can set a threshold determining the minimum number of rows a table must have before it is checked. You can also set the frequency at which a reminder to run the thorough check will appear in the 'log_table_reorg' table after running the quick check. For each finding identified by the check, a corresponding message is logged in the 'log_table_reorg' table. The message specifies the table with the particular problem, In some cases a finding can be a recommendation to reorganize a table. For that you can run a sql script on the table that will reorganize and optimize it for better performance.

There are two types of database checks: 1) a quick check that normally runs in less than 20 seconds, and 2) a more thorough one that, depending on system data volume, can run up to several hours.

Both the quick check and the thorough checks can be launched manually using PLSQL by running the procedure TABLE_REORG.check_reorg, and specifying a parameter of 'T' to run the thorough check, or 'Q' to run the quick check. For example, enter CHECK_REORG('Q') to run the quick check.

This script is located in the Demantra installation directory, under \Demand Planner\Database Objects\Oracle Server\admin.

You can also run the script by defining a workflow that contains the ‘Stored Procedure’ workflow step. Specify ‘TABLE_REORG.check_reorg' as the procedure name and either 'T' or 'Q' as the parameter.

All activity of the checks and reorg operation are logged in detail in the log_table_reorg table. Please review this table when troubleshooting (order by log_time) for detailed error output and comments.

Health Check System Parameters

The system parameters listed below control the behavior of this database procedure. For details about each parameter, see the chapter "Non-Engine Parameters" in the Oracle Demantra Implementation Guide.

SYSTEM_PRIMARY_KEY

MIN_NUMROWS_FOR_REORG

QUICK_CHECK_TIMEOUT

THOROUGH _CHECK_INTERVAL

THOROUGH_CHECK_TIMEOUT

Thorough Database Health Check

The "Thorough Database Health Check" is used to launch the thorough health check procedure. It writes output to the log_table_reorg table. This procedure requires more time than the quick check but provides greater accuracy. At the end of the thorough check, purging is done to keep only 2 months of information in the log table. If it recommends reorganizing the database tables, then run the table reorganization utility described below. You should run the thorough check after major changes in data volume, such as large engine runs or changes to table metadata. An example of this would be creating or deleting a series that affects column modification in the table.

Note: In order to run the table reorg, additional privileges are required for the Demantra schema. These privileges can be obtained by running the grant_table_reorg.sql script. This script is located in the Demantra installation directory, under \Demand Planner\Database Objects\Oracle Server\admin.To revoke these privileges, run revoke_table_reorg.sql (same location as the ‘grant’ script).

For information regarding database privileges that may impact the Table Reorganization Utility, see the sys_grants.sql section of the Demantra Installation Guide.

Running the Table Reorganization Utility

There are several ways to run this utility, including:

Running Table Reorganization SQL Script
Calling the TABLE_REORG.REORG stored procedure
Running a workflow

You can use SQL*Plus to run the SQL script or to run the TABLE_REORG.REORG stored procedure. In both cases you need to log into the Demantra schema as described below.

Be sure you have a complete and valid backup of the database.
Login with SQL*Plus as SYS (sqlplus sys/demantra@orcl where 'sys' is the system username, 'demantra' is the schema name and 'orcl' is the database name)
Grant Demantra schema the required privileges to run the reorg. For example, run grant_table_reorg.sql in the Demantra installation directory under Demantra_installation\Demand Planner\Database Objects\Oracle Server\admin.
Login with SQL*Plus as the Demantra database schema name (for example: sqlplus demantra/dem1@orcl)

Note: Shutting down the application server is not required. However, running this process during a heavy load will slow down production operations.

Running the Table Reorganization SQL Script

After logging in with SQL*Plus, perform the following steps:

Call the run_table_reorg.sql script (for example: SQL> @run_table_reorg.sql)
Enter the name of the table you want to reorganize.
Enter reorg type 'R' or 'C'. Entering 'R' causes a row reorg. Entering 'C' also causes a row reorg, but additionally re-sequences columns so that null columns come after columns with data.
Enter the value for PCTFREE (see note below). The default value is 20%.
Enter the degree of parallelism. This determines the number of worker threads running. The default value is 4.
Hit any key to start the reorg process (or Ctrl C to abort).

Note: The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, setting PCTFREE to 10 causes 10% of each data block in the table's data segment to be kept free and available for possible updates to existing rows. However, this value is considered low and could cause "row chaining." Row chaining occurs when data for a single row must span more than one data block, which slows database performance. At the other end of the spectrum, a value such as 50 would likely prevent row chaining, but also consumes more disk space.

Calling the TABLE_REORG.REORG Stored Procedure

This is another way of performing the reorg process. After logging in with SQL*Plus, execute the table_reorg.reorg procedure, passing in arguments for the REORG procedure. For details refer to the Database Procedures chapter in the Oracle Demantra Implementation Guide.

For example:

BEGIN

table_reorg.reorg('DEMANTRA','SALES_DATA','C',10,4);

END;

This example executes a column reorganization as defined by the following parameters:


Schema:	DEMANTRA
Table:	SALES_DATA
Row or Column Reorganization	Column
Min % reserved free space (PCTFREE	10
Degree of parallelism (Num of parallel slaves)	4

After Running the Script or Stored Procedure

After the process completes successfully, verify that the reorganized table is valid by running a worksheet with many series that are related to that table. For example, if the process ran on the PROMOTION_DATA table, you could open the Promotion Comparison worksheet.
After confirming the table is valid, you can drop the original table which the process renames to RDFDDHHMISS$-original table name (where DDHHMISS is date format Day,Month,Hour. The table name is truncated to 30 characters, and the hour format is 24).
If grants were given by running the grant_table_reorg.sql script, revoke the privileges you granted earlier by connecting to SQL*Plus again as SYS and running revoke_table_reorg.sql located in the same directory.

Running the Table Reorganization Procedure Using a Workflow

If desired, the table reorganization procedure can be run using a workflow. If you are implementing the Oracle In-Memory Consumption Driven Planning (CDP) module, the table reorganization procedure TABLE_REORG.REORG can be set up to run periodically using the predefined workflow “CDP Weekly Data Tables Maintenance.” The workflow is set up to reorganize the T_EP_CDP_DATA and SALES_DATA tables by default. Each run of the REORG procedure is logged in a dedicated log table named LOG_TABLE_REORG. For details about this workflow, refer to the Oracle In-Memory Consumption-Driven Planning User’s Guide.

To enable this workflow to run periodically, select the ‘Enable automated table reorganization’ checkbox in the “DBA Details” screen when installing or upgrading Demantra. This check box appears in the ‘DBA Details’ Installer screen and can be selected even if you are not implementing CDP. However, the CDP Weekly Data Tables Maintenance workflow will not be enabled nor will it be set up to run automatically unless CDP is installed.

If you are not installing CDP, you can either define a workflow to automate the process of running the TABLE_REORG procedure or you can run it manually. When defining a workflow to run the procedure, use the "Stored Procedure" workflow step and specify the table_reorg.reorg procedure, and then enter the Demantra schema name, the table name, reorg level, PCTFREE and degree of parallelism in the Parameters. These parameters are listed in "Calling the TABLE_REORG.REORG stored procedure.”

For details about running the procedure manually, see “Running the Table Reorganization Utility.”

Note: If this option is selected, then a new database role will be created (DEM_SECURE) with additional privileges that are required to reorganize a table. This role is not enabled by default and is password protected. When the REORG procedure is executed, it enables the role, reorganizes the table and then revokes the role so the session has the extra privileges only during the reorganization process.

Database Procedures

CHECK_REORG

This procedure accepts the level of the check (quick or thorough) and then runs a check of the database tables (see Overview of Database Performance Enhancement).

This procedure is included in the package TABLE_REORG.

CHECK_LEVEL


Description	This determines whether a quick or thorough database check will be performed. The quick check usually takes less than 20 seconds (depending on data size), whereas the thorough check can take several hours.
Values	"Q" (for quick) or "T" (for thorough)
Default Value	none

REORG

This procedure reorganizes a table for optimum performance. Oracle recommends running it by using the wrapping script (see Running the Table Reorganization Utility), but it can be manually run by providing the arguments listed below. The reorg procedure will reorganize the table’s data structure (and columns if ‘C’ is specified for is_reorg_level) to improve database performance.

This procedure is included in the package TABLE_REORG.

is_dem_user


Description	Demantra’s database schema name. This parameter can be null and in that case will automatically replace the null with the DB user who is executing the procedure.
Values	Must be a valid schema name.
Default Value	(None)

is_table_name


Description	This identifies the table that will be reorganized.
Values	A valid table name
Default Value	(None)

is_reorg_level


Description	This determines whether row or column reorganization will be done (see Overview of Database Health Check).
Values	"R" (for row reorganization) or "C" (for column reorganization).
Default Value	(None)

ii_pct_free


Description	The minimum percentage of a data block to be reserved as free space (see Running the Table Reorganization Utility).
Values	0 - 100
Default Value	20

ii_parallel_degree


Description	Determines the degree of parallelism used by the reorg process.
Values	(number of database server CPUs)
Default Value	4

EP_LOAD_SALES

The EP_LOAD_SALES procedure loads lowest level time-dependant information into the Demantra SALES_DATA table. This information includes historical data as well as other time-varying information such as price, booking, and shipments.

Ep_Load_Sales_LoadNullActualQty
Description	If set to FALSE, then NULL values in the ACTUAL_QTY field of the T_SRC_SALES table will not be loaded (ex. UPDATE db_params set pval = 'FALSE' WHERE pname = Ep_Load_Sales_LoadNullActualQty; ). Note: When set to FALSE, EP_LOAD considers any records with ACTUAL_QTY = NULL as errors and writes them to the _ERR table. Whether this parameter is set to TRUE or FALSE, combinations with ACTUAL_QTY = NULL will still be added to MDP_MATRIX. If you want to load both sales with ACTUAL_QTY = NULL and sales with ACTUAL_QTY not NULL, use the following statement: UPDATE db_params set pval = 'TRUE' WHERE pname = 'Ep_Load_Sales_LoadNullActualQty'; COMMIT;
Values	TRUE or FALSE
Default Value	FALSE

Ep_Load_Sales_LoadFromStagingTableDirectly
Description	When set to FALSE, EP_LOAD_SALES copies the staging table to a loading table. When set to TRUE, the EP_LOAD_SALES procedure will load sales directly from the sales staging table. This setting improves performance since the staging table is not copied to a loading table.
Values	TRUE or FALSE
Default Value	TRUE

Ep_Load_Sales_DisableEnableTriggers
Description	When set to TRUE, the EP_LOAD_SALES procedure will identify enabled seeded triggers on SALES_DATA and disable them. When sales are loaded, the triggers will then be re-enabled. These triggers are used to synchronize specific series between SALES_DATA and PROMOTION_DATA. Preventing these triggers from being invoked can improve performance of the EP_LOAD_SALES procedure. If these seeded triggers have been customized, you may want to set this parameter to FALSE.
Values	TRUE or FALSE
Default Value	TRUE

Ep_Load_Sales_ReportNullActualQty
Description	Report error sales when Ep_Load_Sales_LoadNullActualQty = FALSE. This parameter allows you to load ACTUAL_QTY = NULL into SALES_DATA. When Ep_Load_Sales_LoadNullActualQty is FALSE, it treats ACTUAL_QTY = NULL as error sales , reporting them via the T_SRC_SALE_TMPL_ERR table. Having the default setting (Ep_Load_Sales_LoadNullActualQty = FALSE, Ep_Load_Sales_ReportNullActualQty = FALSE) allows ACTUAL_QTY = NULL sales to be loaded to MDP_MATRIX and not reported as errors.
Values	TRUE or FALSE
Default Value	FALSE

Ep_Load_Sales_SALES_DATA_Merge_LoopControl
Description	The cursor control override column used in the SALES_DATA MERGE in EP_LOAD_SALES. Default NULL. Can be any T_SRC_SALES_.. or SALES_DATA PK column. Effective when Ep_Load_Sales_SALES_DATA_Merge_InOneMerge=FALSE.
Values	Can be any T_SRC_SALES_.. or SALES_DATA PK column.
Default Value	NULL

Non-Engine parameters Related to Database Performance


Parameter	Location	Default	Details
SYSTEM_PRIMARY_KEY	Business Modeler > System Parameters > Database	SALES_DATE,ITEM_ID,LOCATION_ID	A comma-delimited list of columns specifying the optimal primary key order. This parameter is used by the Table Reorganization Procedure for optimizing the order of columns.
MIN_NUMROWS_FOR_REORG	Business Modeler > System Parameters > Database	1000000	Only tables with at least this many rows of data will be considered by the quick or thorough check processes.
QUICK_CHECK_TIMEOUT	Business Modeler > System Parameters > Database	20	This sets a maximum running time for the quick check process, in seconds. The parameter is numeric, cannot be null, and ranges from 20 through 600.
THOROUGH _CHECK_INTERVAL	Business Modeler > System Parameters > Database	30	Sets the interval, in days, that determines when a reminder to run the thorough check will appear in the log_table_reorg table after running the quick check. The parameter is numeric, cannot be null, and ranges from 7 through 60. Any value outside this range is treated as a 1.
THOROUGH_CHECK_TIMEOUT	Business Modeler > System Parameters > Database	18000 (5 hours)	This sets a maximum running time for the thorough check process, in seconds. The parameter is numeric, cannot be null, and ranges from 3600 through 36000 (1 to 10 hours).