• Connection Pools
How to decide on Connection Pool Number
– Maximum number of connections in a connection pool : 20% of concurrent users
* number of reports per dashboard.
– How Connection Pool can improvise Performance
– Create dedicated connection pools for your Session Variables.
– Create dedicated connection pools for your report queries.
– Always use OCI Call Interface in the rpd if your backend db is Oracle
– Using ODBC for the Oracle RDBMS Connection can degrade performance
– DB tracing
– ODBC tracing is enabled to troubleshoot ODBC issues. This can cause huge
performance problems. This is not indicated in any OBI logs.
• Setting the NQSConfig.ini parameters
– Setting following NQSConfig.ini parameters that can affect OBI performance
– SORT_MEMORY_SIZE: specifies the maximum amount of memory to be used for each sort operation
– SORT_BUFFER_INCREMENT_SIZE: specifies the increment by which the sort memory is increased as more memory is needed
– VIRTUAL_TABLE_PAGE_SIZE: specifies the size of a memory page for OBI internal processing
– There are some more parameters which can help/effect the performance
– SERVER_THREAD_RANGE
– SERVER_THREAD_STACK_SIZE
– DB_GATEWAY_THREAD_STACK_SIZE
– MAX_SESSION_LIMIT
– MAX_REQUEST_PER_SESSION_LIMIT
– BUFFER_POOL_SIZE
– MAX_CACHE_ENTRY_SIZE
– DATA_STORAGE_PATHS
– INIT_BLOCK_CACHE_ENTRIES
• Periodcally check performance monitoring using..
- http://<host url>:<port number>/analytics/saw.dll?perfmon
• Use of “filter By” Option compared to “Case When”
• Always specify the number of elements per level. BI Server will use it to identify
aggregate tables and mini dimensions. It does not need to be accurate, a rough estimate is fine.
• Exposing materialized views in Physical layer
• Deploying SSL will have a level of overhead on the overall performance
• Formatting of reports has overhead on the system verse executing HTML based reports only (i.e. Dashboards)
• Turning off logging
• Setting query limits
• Pushing calculations to the database
• Using database hints
• Infrastructure sizing
• Using aggregates
• Constraining results using a WHERE clause
• Limiting the number of initialization blocks
• Caching (Last Resort)
• Depending on your configuration, enable some parameters in database feature:
• Always specify the content level in all logical table
• Delete Unused Views from Presentation Layer
• Do not overuse dashboard conditions, it has a cost on performance.
• Dashboard should be as interactive as possible:
– column selectors,
– Contextual drill-down,
– guided navigation
• Using aggregates
– Enabling OBI to generate queries against smaller, summarized tables
• Constraining results using a WHERE clause
– Limiting the rows returned from a data source
• Caching
– Fulfilling a query from a local cache as opposed to processing the query through a data source
• Limiting the number of initialization blocks
– Initialization block queries are executed when OBI is started and when users log in
• Turning off logging in production
– No overhead for OBI to generate log files
• Setting query limits
– Enabling OBI to track and cancel runaway queries
• Pushing calculations to the database
– Automatically pushing certain operations to the database based on database feature entries
• Exposing materialized views in Physical layer
– Guarantees to choose the most economical table source to satisfy a query
• Using database hints
– Forcing the database query optimizer to execute the statement in a more efficient way
• Network Issues
– Any network issues. It may be necessary to enable network tracing tools, if you suspect problems with slow network.
• Other Factors
– Sizing of the OBI servers is an important part in implementations when the number
of users and the complexity of the dashboards/reports is significant. It may be
necessary to involve infrastructure Admin to get a sizing review for large
implementations.
– The size of the RPD and webcatalog may affect start up times of the services. It is
recommended to clean up the RPD and webcatalog to remove redundant objects.
– Sometimes ODBC tracing is enabled to troubleshoot ODBC issues. This can cause
huge performance problems. This is not indicated in any OBI logs.
– When analyzing the NqQuery.log, if the Physical SQL is taken a lot of time,
performance tuning needs to be done on the database.
– Oracle Database features like proper indexing , partitioning and Star
Transformations can help performance to a great extent.
• RPD considerations
– The RPD should be designed according to best practices.
– De-normalize dimensional objects, combine several dimensional attributes into
one flat table. This will help reduce joins.
– Model your physical tables in a way that should help in creating simpler Business Models.
– Use Aggregate Tables when necessary to help better performance.
– Aggregate tables will need additional ETL, storage and complex mapping efforts.
– The size of the RPD may affect start up times of the services
• Complex Calculations
– It may be better to move complex calculations to ETL for performance reasons.
– If customers have complex logic that involves complex SQL, it is a good idea to
consider pushing this logic to ETL rather than handling it in the RPD at the run time
• Avoid Cross-database joins
– Cross-database joins or “federated queries” can be expensive in terms of
performance when compared to joins within the same database. For cross-database
joins, tables are normally loaded into memory, and then joined.
• Caching (THE LAST RESORT)
– Caching can be very helpful in alleviating performance issues for complex queries
that are reused many times.
– iBots can be used to seed queries during off peak hours to achieve performance.
• DBFeatures
– If one of the dbfeatures is turned off, improper SQL may be generated by OBI.
– Improper SQL may cause performance degradation
• Control long running queries
– Set certain timeout parameters in the RPD
• Use a default value for the prompts in dashboards.
– This will make sure that the reports will return a smaller result set
– If this is not possible to do due to the business requirements
• Consider adding some links on the dashboard page for reports.
• If the query ran quickly in the NqQuery.log, then Presentation services may be taking
more time to display the results.
– If you have a significant number of Charts or Pivot table views for the reports.
– Changing the existing reports to Table view could help performance
• Too many pivot tables and charts on a dashboard page can make the UI very slow
• Check for hidden sections and guided navigations that will run always.
• Make sure the logconfig.xml file is not modified to get enhanced logging. This is not
recommended in production systems.
• SQL request contains Non-cacheable element Current_Timestamp, Current_Time, Rand,
Populate, or a parameter marker then it is not added to the cache
• Result set is too big. MAX_ROWS_PER_CACHE_ENTRY and MAX_CACHE_ENTRY_SIZE
parameters in nqsconfig.ini limit the max rows and max size for cache
• Oracle BI Server is clustered.
• Caching cannot be used for constrained Prompts.
• If session variables are used, caching will not work since this results in the cache being
specific to a user.
• ORDER BY clause made up of columns in the select list. Queries that order by columns
not contained in the select list result in cache misses.
• Cache entries become specific to users depending on the way data-level security is
setup in the RPD.
• The logical query has an additional column as compared to the seeded query.
How to decide on Connection Pool Number
– Maximum number of connections in a connection pool : 20% of concurrent users
* number of reports per dashboard.
– How Connection Pool can improvise Performance
– Create dedicated connection pools for your Session Variables.
– Create dedicated connection pools for your report queries.
– Always use OCI Call Interface in the rpd if your backend db is Oracle
– Using ODBC for the Oracle RDBMS Connection can degrade performance
– DB tracing
– ODBC tracing is enabled to troubleshoot ODBC issues. This can cause huge
performance problems. This is not indicated in any OBI logs.
• Setting the NQSConfig.ini parameters
– Setting following NQSConfig.ini parameters that can affect OBI performance
– SORT_MEMORY_SIZE: specifies the maximum amount of memory to be used for each sort operation
– SORT_BUFFER_INCREMENT_SIZE: specifies the increment by which the sort memory is increased as more memory is needed
– VIRTUAL_TABLE_PAGE_SIZE: specifies the size of a memory page for OBI internal processing
– There are some more parameters which can help/effect the performance
– SERVER_THREAD_RANGE
– SERVER_THREAD_STACK_SIZE
– DB_GATEWAY_THREAD_STACK_SIZE
– MAX_SESSION_LIMIT
– MAX_REQUEST_PER_SESSION_LIMIT
– BUFFER_POOL_SIZE
– MAX_CACHE_ENTRY_SIZE
– DATA_STORAGE_PATHS
– INIT_BLOCK_CACHE_ENTRIES
• Periodcally check performance monitoring using..
- http://<host url>:<port number>/analytics/saw.dll?perfmon
• Use of “filter By” Option compared to “Case When”
• Always specify the number of elements per level. BI Server will use it to identify
aggregate tables and mini dimensions. It does not need to be accurate, a rough estimate is fine.
• Exposing materialized views in Physical layer
• Deploying SSL will have a level of overhead on the overall performance
• Formatting of reports has overhead on the system verse executing HTML based reports only (i.e. Dashboards)
• Turning off logging
• Setting query limits
• Pushing calculations to the database
• Using database hints
• Infrastructure sizing
• Using aggregates
• Constraining results using a WHERE clause
• Limiting the number of initialization blocks
• Caching (Last Resort)
• Depending on your configuration, enable some parameters in database feature:
• Always specify the content level in all logical table
• Delete Unused Views from Presentation Layer
• Do not overuse dashboard conditions, it has a cost on performance.
• Dashboard should be as interactive as possible:
– column selectors,
– Contextual drill-down,
– guided navigation
• Using aggregates
– Enabling OBI to generate queries against smaller, summarized tables
• Constraining results using a WHERE clause
– Limiting the rows returned from a data source
• Caching
– Fulfilling a query from a local cache as opposed to processing the query through a data source
• Limiting the number of initialization blocks
– Initialization block queries are executed when OBI is started and when users log in
• Turning off logging in production
– No overhead for OBI to generate log files
• Setting query limits
– Enabling OBI to track and cancel runaway queries
• Pushing calculations to the database
– Automatically pushing certain operations to the database based on database feature entries
• Exposing materialized views in Physical layer
– Guarantees to choose the most economical table source to satisfy a query
• Using database hints
– Forcing the database query optimizer to execute the statement in a more efficient way
• Network Issues
– Any network issues. It may be necessary to enable network tracing tools, if you suspect problems with slow network.
• Other Factors
– Sizing of the OBI servers is an important part in implementations when the number
of users and the complexity of the dashboards/reports is significant. It may be
necessary to involve infrastructure Admin to get a sizing review for large
implementations.
– The size of the RPD and webcatalog may affect start up times of the services. It is
recommended to clean up the RPD and webcatalog to remove redundant objects.
– Sometimes ODBC tracing is enabled to troubleshoot ODBC issues. This can cause
huge performance problems. This is not indicated in any OBI logs.
– When analyzing the NqQuery.log, if the Physical SQL is taken a lot of time,
performance tuning needs to be done on the database.
– Oracle Database features like proper indexing , partitioning and Star
Transformations can help performance to a great extent.
• RPD considerations
– The RPD should be designed according to best practices.
– De-normalize dimensional objects, combine several dimensional attributes into
one flat table. This will help reduce joins.
– Model your physical tables in a way that should help in creating simpler Business Models.
– Use Aggregate Tables when necessary to help better performance.
– Aggregate tables will need additional ETL, storage and complex mapping efforts.
– The size of the RPD may affect start up times of the services
• Complex Calculations
– It may be better to move complex calculations to ETL for performance reasons.
– If customers have complex logic that involves complex SQL, it is a good idea to
consider pushing this logic to ETL rather than handling it in the RPD at the run time
• Avoid Cross-database joins
– Cross-database joins or “federated queries” can be expensive in terms of
performance when compared to joins within the same database. For cross-database
joins, tables are normally loaded into memory, and then joined.
• Caching (THE LAST RESORT)
– Caching can be very helpful in alleviating performance issues for complex queries
that are reused many times.
– iBots can be used to seed queries during off peak hours to achieve performance.
• DBFeatures
– If one of the dbfeatures is turned off, improper SQL may be generated by OBI.
– Improper SQL may cause performance degradation
• Control long running queries
– Set certain timeout parameters in the RPD
• Use a default value for the prompts in dashboards.
– This will make sure that the reports will return a smaller result set
– If this is not possible to do due to the business requirements
• Consider adding some links on the dashboard page for reports.
• If the query ran quickly in the NqQuery.log, then Presentation services may be taking
more time to display the results.
– If you have a significant number of Charts or Pivot table views for the reports.
– Changing the existing reports to Table view could help performance
• Too many pivot tables and charts on a dashboard page can make the UI very slow
• Check for hidden sections and guided navigations that will run always.
• Make sure the logconfig.xml file is not modified to get enhanced logging. This is not
recommended in production systems.
• SQL request contains Non-cacheable element Current_Timestamp, Current_Time, Rand,
Populate, or a parameter marker then it is not added to the cache
• Result set is too big. MAX_ROWS_PER_CACHE_ENTRY and MAX_CACHE_ENTRY_SIZE
parameters in nqsconfig.ini limit the max rows and max size for cache
• Oracle BI Server is clustered.
• Caching cannot be used for constrained Prompts.
• If session variables are used, caching will not work since this results in the cache being
specific to a user.
• ORDER BY clause made up of columns in the select list. Queries that order by columns
not contained in the select list result in cache misses.
• Cache entries become specific to users depending on the way data-level security is
setup in the RPD.
• The logical query has an additional column as compared to the seeded query.
No comments:
Post a Comment