Tuesday Dec 03, 2013

Upgrading to 12.2.2? Do you want extra speed? Use a logon trigger for parallelism

When upgrading your Demantra application from an older version directly to 12.2.2, you can minimize the upgrade time.  You can force parallelism using a Logon trigger on the server.

   Creation of new Indexes on sales data can take long time.

   As part of the upgrade there is a script building a new set of indexes, one per engine profile, on each profile quantity_form expression.

   The creation of the index can take a long time on big data tables.

    - Remember to disable or drop that trigger when the upgrade has completed.

   The following trigger was created as SYS database user and tested successfully by creating DEMANTRA database user and logging in with it:

 CREATE OR REPLACE TRIGGER force_parallel_ddl_trg

AFTER LOGON ON database

BEGIN

  IF (USER='DEMANTRA') THEN

    EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DDL';

  END IF;

END force_parallel_ddl_trg;

/

 Make sure to:

  1. Modify DEMANTRA to be the database schema name.
  2. drop it after it is not needed anymore using the foillowing:

 DROP TRIGGER force_parallel_ddl_trg;

Saturday Oct 26, 2013

Demantra Partitioning and the First PK Column

  We have found that it is necessary in Demantra to have an index that matches the partition key, although it does not have to be the PK.  It is ok
  to create a new index instead of changing the PK.

  For example, if my PK on SALES_DATA is (ITEM_ID, LOCATION_ID, SALES_DATE) and I decide partition by SALES_DATE, then I should add an index starting
  with the partition key like this: (SALES_DATE, ITEM_ID, LOCATION_ID).

  * Note that the first column of the new index matches the partition key.

  It might also be helpful to create a 2nd index with the other PK columns reversed (SALES_DATE, LOCATION_ID, ITEM_ID). Again, the first column
  matches the partition key.

Wednesday Feb 06, 2013

Need Additional Details? Check out these latest MOS notes!

Demantra DATABASE PARAMETERS General, Organized Checklist. A Place to Begin for Maintenance and Proper Diagnostics, (Doc ID 1519789.1)

Demantra Index Advisor 7.3.0 and Upwards User Guide Addendum, Comment and Output for Performance (Doc ID 1520222.1)

Demantra DB Configuration and Application Maintenance Best Practices (Doc ID 1458751.1)

Demantra Process Management and Performance Summary Guide Parameters Logs Triggers Indexes Index and More! (Doc ID 1520581.1)

Demantra WORKSHEET PARAMETERS General, Organized Checklist. Begin for Maintenance and Proper Diagnostics, (Doc ID 1519805.1)

links not included for security

Wednesday Dec 19, 2012

Un-used Indexes on MDP_MATRIX Consuming Resources

Disable un-used Indexes:

As much as it is recommended to create relevant indexes, it is advised not to have too many indexes on the mdp_matrix table.  Too many indexes will cause long waits on the table as indexes needs to get updated every time the table is updated.  There are many seeded indexes on mdp_matrix, every out of the box data model level has an index on the matrix table.  If a level is unused in the specific data model of the implementation, it is advisable to disable that index.  If the customer is not sure if and how indexes are utilized, the DBA can monitor all indexes.  After a few cycles of operation, the DBA should go over that list and see which indexes have not been used.  Consider disabling them.

There are scripts on the net to monitor indexes or use the monitoring usage clause in the alter index statement.

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today