Optimization for Oracle

This Attachment shows how to import data to dimensions and cubes in an optimized way for SGDB Oracle.

Benefits

The benefit acquired is the import speed. See comparative table (records/sec):

 

Object

Method

Speed

Gain

Dimension

Traditional

2,800 to 3,300

50 to 95

 

Optimized

140,000 to 320,000

Cube

Traditional

1,500 to 2,500

7 to 32

Optimized

11,000 to 80,000

Requirements

1. Required privileges and configurations, according to the case and access option.
2. Access to origin database from SIGADW database.
3. Through db link, to access DB in other server(s).

Example: select * from SA1001@LNK_ERP where MSEXP <> ‘ ‘

4. Use of @db link

 http://download-west.oracle.com/ docs/ cd/ B10501_01/ server.920/ a96540/ statements_49a.htm#2060142

5. Naming db link

 http://download-west.oracle.com/ docs/ cd/ B10501_01/ server.920/ a96540/ sql_elements10a.htm#27775

6. Creating db link

 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_56a.htm

7. Via \"schema\", to access DB on the same server, but in different schema.

Example: select * from DADOSADV.SA1001 where MSEXP <> ‘ ‘

See details in:

• Access to tables in other schema.

 

Pay attention to the size of fields.

Note:

Do not use Varchar2 , only CHAR, for fields of Character type.

This optimization procedure may not be effective or even worsen the performance because it depends on specific configurations of Oracle server. You should discuss the adoption of this procedure or not together with DBA of database.

 

The structure of origin table must follow the structure of destination table, that is, fields with the same name (alias can be used in the field name) and same format (functions of conversion or formatting can be used). Fields that have date must be of character type, with size of 08 and in the format YYYYMMDD. Logical fields must be of character type, with size of 01 and containing T or F to represent their state.

Restrictions:

Do not use the optimized routine when you need to run scripts made in AdvPL.

Note: Soon the version that enables scripts in PL/SQL to be run will be released.

 

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_108a.htm#2067873

Setup:

1. Create the db link with the required access privileges.

2. Define the privileges of access in the origin for the user that owns SIGADW.

3. Implement stored procedures and auxiliary tables required.

Operation:

The optimized import process uses specific resources of SGDB Oracle to handle data warehouses. The basic mechanism of operation is: data extraction is made by select or stored procedure that feeds the transfer table (see Attachments-> Stored Procedures), by accessing the origin through one of the methods described. Result-set obtained in the extraction passes through transformation, through functions or stored procedures. The load is made from this result-set, by using specific commands to handle large amounts of data.

Access is always made from SIGADW schema.

Procedures step by step:

1. In DB of SIGADW, create support tables, views, stored procedures, synonyms, db links, and define privileges as well as other items that are necessary.
2. In the origin DB, create support tables, views, stored procedures, synonyms, define privileges as well as other items that are necessary.
3. In SIGADW, define a connection for DB itself of SIGADW.
4. In SIGADW, add a data source for dimension or cube, just like occurred in the traditional process, and link the optimization (Optimize).
5. On the next screen:

The field Limit of Records does not have function. If you want to limit the amount of rows to be processed, you must do it through SQL, entering it in the filed SQL (data) of the screen Parameters and send data to the server.

Access the screen Scripts to ensure that origin table is available for access and that all destination fields have their pair in the origin. If some field is wrong or if it does not exist in the source table, return to the screen Parameters and adjust the field SQL (data) and SQL (structure).

 

6. Activate import.

 

Note:

If there is an error during the optimized import process, SIGADW tries to run the traditional process.