OBI Apps Informatica Performance Tuning - Optimizing SDE Read Throughputs - Oracle DB Network Optimization

Oracle DB Network Optimization

After exhausting query performance with viable indices, consider modifying Session Data Unit (SDU) and Transport Data Unit (TDU) parameters to increase session throughput. The default value of SDU and TDU is 2048 and the maximum is 32767. These can be set as global parameters in sqlnet.ora or for a particular descriptor in tnsnames.ora. They need to be set on both server and client. Consider setting these parameters in tnsnames.ora so that it affects only a particular connection descriptor that is used for Oracle DB server to Informatica server communication. The SDU and TDU parameters can be set higher depending on the network and memory. Get help from your Infrastructure team in determining the MSS (Maximum Segment Size) of the network. Ideally they should be in the multiple of MSS. In addition, the SDU should not be greater than TDU. Ideally, they should be the same. For slower networks, consider a lower value.

Client Side Configuration (Informatica Server):

tnsnames.ora:

CRMOLTP =

  (DESCRIPTION =

(SDU=32768)

  (TDU=32768)

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = crmoltp.xyzcompany.com)(PORT = 1521))

Server Side Configuration:

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU=32768)

  (TDU=32768)

(GLOBAL_DBNAME = CRMOLTP)

(ORACLE_HOME = /u01crmoltp/oracle/product/10.2.0)

(SID_NAME = CRMOLTP)

)

)

tnsnames.ora:

CRMOLTP =

(SDU=32768)

(TDU=32768)

(DESCRIPTION =

(ADDRESS =

(PROTOCOL= TCP)

(Host= CRMOLTP)

(Port= 1521))

(CONNECT_DATA =

(SID = CRMOLTP)

)

)

BI Apps Performance Optimization and Consideration - Part 3

In this last part we will look at other options of optimizing the performance of OBIEE



Aggregation Strategy

• Multi-level (pyramid) aggregation strategy: level 1 to level 2, level 2 built on level 1


• Incremental aggregation for level 1 aggregates to reduce ETL time


• Level 1 aggregates can be at the lowest leaf of each dimension to avoid bulk re-aggregation when dimension hierarchies change (e.g. sales force re-organization, product hierarchy changes)


• Bulk aggregations for higher levels for simplicity


• Data compression drives aggregation (at least 10 x, preferably closer to 100x). This could be implemented for Service Request tables


• Try aggregation based on dimensions instead of factsAggregation to the ratio of 50:1

Caching Strategy

• Proper caching strategy needs to be implemented as this is among the best practice of optimization . Caching must be implemented based upon users by groups


• Set the physical table cache property on Mater table not aliases


• Set cache persistence time based of refresh schedule


• Ideally do not opt Cache never expires in the physical table properties


• Caching logs may be monitored for user sessions and queries may be analyzed across various users to optimize


• Use ODBC extension functions to purge cache which has the following advantages


• Analytics Cache Purge ODBC Extension Functions


• ETL routines can proactively call ODBC extension functions to manage cache. This ensures that obsolete cache is purged as soon as possible


• Purging Cache by Logical Query is a new capability. This will be particularly helpful in purging cache associated with logical queries that are used in Cache Seeding.


• Purging the entire cache set in one command is a new capability that can be used in various scenarios including development and testing environments.


• SAPurgeCacheByQuery will purge a cache entry that exactly matches the logical query plan derived from a specified query. This function takes one, and only one, parameter representing query text. USE_ADVANCED_HIT_DETECTION. The default value is NO which will have the pre-existing cache hit detection behavior. The value YES will invoke the two pass algorithm which will examine a greater search space and is potentially more expensive than the default behavior. you should set the value to YES when you wish to maximize the number of cache hits they obtain
  
  Check the following Cache for scalability

Browser Cache
Scripts like Java or HTML
High resolution images
Data Access
Query Plan cache
Multithreaded Database Pools

BI Server Cache
Result Set
Aggregations
Summaries



Database Optimization

1. Database archive strategy

• The data in the databse needs to be archived from time to time in order to manage the data size. The idea is to keep only a certain amount of data in the warehouse and archive the rest of the data. For example, keep only the last 12 months of data in the warehouse, and archive older data from the warehouse.


• This database archive strategy will help limit the number of rows across tables with huge data
  

2. Database Partition strategy

• A good database partition strategy will help query performance since the query will only need to look at a specific partition to obtain the result. For example: The Organization table can be partition based on Active Flag ; The Service Request table can be partitioned based on X_CALC_FLG = 'Y’

3. Database Indexes

• A database index should be added for the most queried columns. The database index should also be rebuild from time to time to ensure its effectiveness. ALTER INDEX MONITORING USAGE command that collects statistics over a period of time.


• Increase Parallel Query by configuring the database server for an optimal degree of parallel processing.


• Given sufficient resources, this can greatly improve query


• Make sure that cost-based optimization is enabled on databases


• Set the tablespace to at least the same as the transactional database size. Make sure the temporary tablespace has adequate space


• Create histogram statistics for these indices to enable the optimizer to better perform queries on critical tasks


• Spread data across multiple disks. RAID Redundant Array of Independent Disks configuration if possible is recommended


• Partition Large fact tables like Asset, SR is recommended


• Set the number of log file groups to 4 on Warehouse schema


• Set the size of each log file to 10 MB. on warehouse db


• Set the sga_max_size to 700 MB or more


• On the client side, edit the tnsnames.ora file. Modify the TNS alias by adding SDU= and TDU=as follows:
  myhost_orcl.world=DESCRIPTION=(SDU=16384)(TDU=16384)
  ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521)) CONNECT_DATA=(SID=ORCL))


• On the Oracle client Set the NLS_SORT parameter to BINARY


• The ETL implementation team should use analyze table commands specific to their database environment, that can be executed after the respective session has completed. This can be achieved by using the post-session command property of each session
  Try to break up ETL processing so data is extracted and staged remotely then compressed for further local processing


• Remote ETL servers extract and stage to local flat file, compress and send the flat file to central ETL server


• Central ETL Server performs the ‘stage to data warehouse’ process


• Manage I/O Traffic --> Manage the input and output accesses to disk storage by striping the disk storage. The best and simplest action is to install disk storage arrays (RAID), the second best is to stripe volumes using a Logical Volume Manager.


• De fragment tables to which the data is extracted


• See how much memory is on the available server and set the values for SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE rather high. 20, 30 or 40 MB per session is not uncommon in a data warehouse situation. Also set HASH_AREA_SIZE rather large

Infrastructure Options

• Consider deployment options on separate hardware for various OBI components like BI Publisher, OBI Server, OBI Presentation Services, Oracle Delivers Server


• Hardware must be available based of the recommendations from Oracle. The hardware for database must be must also be setup based of the size of the database

BI Apps Performance Optimization and Consideration - Part 2

This is in continuation to the earlier posting. We would now look at some recommendadtions for OBIEE Repository(Metadata), Reports and Dashboards and Presentation Catalogs

Repository

· Analyze the driving tables and nested loop joins
· Analyze the SQL if using any view object definitions in the physical layer
· Consider the possibility of complex joins in the physical layer
· Analyze the possible usage of Inner, Outer, left inner
· Consider usage of aggregate tables and model the rollups in the logical layer thus avoid detail tables as and when required
· Analyze the aggregate tables so that they do not combine multiple levels of aggregation instrad put each level of summarization within its own table
· Consider subset large physical dimension tables creating mini dimension tables but this must be done by identifying the family of queries that fall within the subset table
· Consider use of filters than CASE statements in rpd
· Manage cache and analyze if any stale queries are generated
· Evaluate possible cache purging methods
· Evaluate design
· Create additional aliases to avoid circular joins for dimension tables that are joined to more than one or other table
· Use canonical time dimension
· Use multiple time dimensions for each level of granularity
· A single logical time dimension may be modeled having many LTS’s of physical time dimension aliases

· Increase the max number of connections per connection pool based on need only
· Possibly do not use opaque views
· Validate proper logical level settings at the content tab for confirmed model
· Validate the metrics aggregation rules as when the metrics are created using logical columns then the aggregation rule need to be applied before and similarly when the metrics are created using the physical columns then the aggregation rule must be applied after creating the metric
· Use estimate levels to set the number of elements for each dimensional hierarchy level

Reports and Dashboards

· Recommended to split their reports based out of data level visibility. Now that a dashboard is with all data across all users. splitting with data level security model enhances performance as it restricts users with limited data at query level
· Must implement data level filters across dashboards to restrict data on dashboards that require significant scrolling and navigation
· To help users to locate their data in which they are interested organize dashboards by user role
· Home dashboard page might have only summary level reports and create a guided user navigation linked reports to facilitate users to drill to details
· Also you may create data driven drill through reports on summary to navigate to detailed reports of hierarchy
· Use conditional formatting to focus attention on data that is outside given parameters
· If the columns that are included in a report use web variables or carry only filter criteria, then hide these columns
· Direct users to other analysis based on how their dashboard appears at that moment
· Search and destroy all excluded column in pivot views across reports
· It is observed that pivot table views are being used across many reports. It is recommended to avoid using pivot views as far as possible
· Try to increase values for and parameter to override maximum number of records that can be processed by a pivot table
· Prune repository and web catalog to requirements
· For a table view just in case you want to increase Rows of data beyond 65000 then change parameter in instanceconfig fileVIRTUAL_TABLE_PAGE_SIZE in NQSConfig.ini file can be increased to 256

Presentation Catalogs

· Try to stay to the rule of 7, i.e. 7 top level folders and 7 columns per folders
· Create subfolders under relevant folder section
· When there are many fact tables within a presentation catalog then assign an implicit fact table

In the next part we will see the techniques for aggregation, caching, database optimization

BI Apps Performance Optimization and Consideration - Part1

This topic may be used at a high level but covers most of the common aspects of the performance tuning and optimization considerations for OBIEE deployments. This topic does not detail much about handling ETL performance tuning. Couple of points are picked from Oracle's recommendations

At a high level the following needs to be reviewed and analyzed

• Data model and custom star schema's
• Physical Layer (including Joins and Keys configured)
• Business Model and Mappings Layer (including Joins and measures defined)
• Presentation Layer Layout
• Application Performance (including Joins, and aggregate tables configured)
• Caching options configured
• Security and Personalization
• Initialization Blocks and Variables configured
• Investigate the use of aggregate tables and mini dimension tables to increase performance
• Define data archive strategy and table partition strategy to manage data sizes in the database
• Database optimization
• Hardware setup

Partitioning

• Consider partitioning large Fact tables having more than 20 million rows
• Identify eligible columns of type DATE for implementing range partitioning
• Connect to the Oracle BI Server repository and check the usage or dependencies on each column in the logical and presentation layers
• Analyze the summarized data distribution in the target table by each potential partitioning key candidate and data volumes per time range, month, quarter or year
• Basing on the compiled data, decide on the appropriate partitioning key and partitioning range for your future partitioned table
• The recommended partitioning range for most implementations is a month, though you can consider a quarter or a year for your partitioning ranges

The following columns may be considered as partitioning keys

W_AP_XACT_F on POSTED_ON_DT_WID
W_GL_ACCOUNT_D may be a Date WID
W_AR_XACT_F on POSTED_ON_DT_WID
W_GL_REVN_F on POSTED_ON_DT_WID
W_GL_COGS_F on POSTED_ON_DT_WID
W_TAX_XACT_F on POSTED_ON_DT_WID
W_GL_OTHER_F on ACCT_PERIOD_END_DT_WID

Storage Considerations for Oracle Business Analytics Warehouse

• Setting excessive parallel query processes
• Running multiple I/O intensive applications, such as databases, on a shared storage
  choosing sub-optimal storage for running BI Applications tiers
• Make sure you carefully plan for storage deployment, configuration and usage in Oracle BI Applications environment
• Avoid sharing the same RAID controller(s) across multiple databases
• Set up periodic monitoring of your I/O system during both ETL and end user queries load for any potential bottlenecks
• Update optimizer statistics
• Consider implementing Oracle RAC with multiple nodes to accommodate large numbers of concurrent users accessing web reports and dashboards
• Check for the database configuration parameters as per the recommendations specified in the install guide

Managing Slow Running Reports

• Analyze the session SQL
• Run Trace or Explain plan on query
• Analyze the logical joins leading to the slow performance
• Analyze the database
• Increase the log level to 5 and analyze NQLQuery.log file and look at Query string,
  Logical request, Physical SQL query, Query outcome status, Physical query response time, Rows returned to client
• Key approach should be to address performance issues as close to the data as is possible, moving processing down the stack to the data

The next part will be about handling performance at metadata, reports & dashboards, presentation catalogs