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.
The following is the third installment on the topic of High Availability within an OBIEE environment.As I mentioned in previous posts, much of what I’ll be discussing was covered in an Oracle eSeminar which I recently viewed on the subject.To quickly summarize, our basic goal within an HA implementation is to provide multiple instances of all components from the BI Server all the way to the end user, so if anything fails, we have another instance of the same component ready to go.The first two posts on the subject can be found in the blog archives if you’d like to rewind. This installment will feature the HA connections between the Presentation Servers and BI Scheduler Servers as well as between the Presentation Servers and BI Servers.We’ll also look closer at the BI Scheduler Cluster configuration.
First we’ll look at the connection between the BI Servers and the Presentation Servers.This again is handled by the Cluster Controller.In a Windows environment you’ll need to go into the Administrative Tools on each Presentation Server and set up the clustered ODBC data source.To do so, simply ensure that “Is this a clustered DSN?” is checked, then specify the primary and secondary cluster controllers and ports.On a Unix/Linux box, you’ll need to make the following changes to the odbc.ini file:
IsClusteredDSN=Yes
PrimaryCCS=<PrimaryCCS>
PrimaryCCSPort=9706
SecondaryCCS=<SecondaryCCS>
SecondaryCCSPort=9706
One important note to keep in mind is that if you have any clients from which you want to access your repository in online mode, you’ll need the clustered DSN set up on these as well.They’ll need the same connection to the BI Servers as the Presentation Servers will.
The BI Scheduler Cluster Controller assigns the active Scheduler server. In an HA environment, you would have two cluster controllers, a primary and a secondary, in an active/passive relationship. The secondary server will not be used unless the primary is unavailable. The client Controller ports are specified in the respective NQClusterConfig.INI files.The Scheduler configuration will be handled by the Cluster Controllers, so all we need to do in the instanceconfig.xml file is point to the Cluster Controllers, as shown below:
<Alerts>
<ScheduleServer>
ccsPrimary=”<Primary Cluster Controller>”
ccsPrimaryPort=”<Client ControllerPort>”
ccsSecondary=”<Secondary Cluster Controller>”
ccsSecondaryPort=”<Client Controller Port>”
</Alerts>
</ScheduleServer>
The other task you would need to complete is to add the BI Scheduler Administrator credentials to the credential store of each ps.The quickest and easiest way would probably be to copy the credential store file from one instance to all other presentation servers.
A few notes about what will occur when the Active Scheduler fails.The transition from one server to another is seamless to the user’s perspective.The users won’t receive any errors, the Cluster Controller will simply detect the failure on the active server and point to the secondary. Any jobs which didn’t complete will be picked up where they left off.One important note to remember is that once the primary server is back up, it will not automatically resume the primary role. Only after the services have been restarted will the primary Scheduler resume its proper role.If any Java, command line, or script jobs are being run during an interruption, they will be restarted when another server is activated and given a new job ID. Take a look at the diagram of the basic HA architecture for the Scheduler Servers.
To configure the Cluster Controller to talk with multiple Schedulers, you must make the following entry in the NQSClusterConfig.INI file on each Cluster Controller:
The first port number for each scheduler will be the rpc port, where the Scheduler will be listening for connections from the Scheduler.The second will be the monitor port, which simply listens for a “heartbeat” from the Scheduler, to confirm it’s still available. This is how the Cluster Controller determines that the primary server has gone down and it needs to look to the secondary server.The default ports for the rpc and monitor ports are 9705 and 9708, respectively.
Configuration of the BI Scheduler Servers themselves can be completed through the Job Manager or via command line using schconfig.All configuration settings are saved in the instanceconfig.xml file of the Scheduler folder, not to be confused with the file of the same name in the web\config folder.The Scheduler can be configured to talk with multiple Presentation Servers and Java Hosts, which will be necessary if you are setting up a true HA environment.If you are using the Scheduler to run script files, you must place them in a shared network file, so that multiple Scheduler Servers can access them.All Schedulers should have read/write access to these files.
That’s going to wrap it up for this post.I’ll finish things up next time with a closer look at the BI Server cluster and how it’s integrated into the HA environment.
This is the second installment in a series of posts in which I’ve been discussing the implementation of High Availability within an OBIEE environment.Much of what we’ll be discussing was in included in an Oracle eSeminar which I recently viewed on the topic.In my original post, I gave the broad strokes in regards to HA and provided the basic overall architecture of a High Availability deployment.This time, we’ll start to dive into some of the specifics regarding configuration which will be necessary to implement a true “shared nothing” HA environment.
Each Presentation Server can be configured to talk with multiple web servers,Java hosts, BI Servers, and BI Schedulers.In this installment we’ll cover the Presentation Catalog, web server, and Java host connections to the Presentation Servers as well as how the user is affected when a Presentation Server fails. The diagram below is a subset of the one shown in my original post on the subject.This figure shows only the components of the HA architecture which we’ll be looking at today.
First, let’s discuss the web client behavior in a High Availability environment.When a user begins a session, the web client is bound to a specific Presentation Service and subsequent requests will be sent to that same service. When a Presentation Service failure occurs, the error is relayed back to the browser and any unsaved data will be lost.Upon logging in again, the user will be bound to another available Presentation Service.Two exceptions to this rule would be if the user is using SSO or if the Presentation Services plug-in is configured to automatically reconnect to another server.In these cases, there may still be a loss of session state. There will also be a time lag to recognize the failed server. This lag will be dependent on plug-in ping settings which we’ll get to eventually.
Any iBots which fail to complete as a result of a Presentation Service failure will result in an error being passed to the BI Scheduler Server and will be included in the log file. When the next available Presentation Service becomes available, the job is rerun without impact and will start again at the step in which it originally failed.
Next, we’ll look at how we would like our Presentation Services to share the Presentation Catalog. There are two basic options which can be deployed.The first option is to use a shared file system. All presentation servers have access to the same shared files.This is the simplest approach and, as I mentioned in my last post, is recommended by Oracle.Alternatively, a more complex method of catalog replication can be deployed through the use of replication agents on each instance which will monitor a single instance for changes and sync other copies as necessary.Two-way replication, which involves making changes to multiple copies of the Presentation Catalog and attempting to keep them all in sync, is highly discouraged and should be avoided.As you can imagine, this method would make maintaining data integrity much more difficult and complicated.
If you’ll be using the shared file approach, the first step necessary will be to point each presentation server to the shared file path by editing the <Catalog> element of the instanceconfig.xml file.In addition, we should also make changes the Presentation Service cache settings.Keep in mind that each instance will have its own cache, which we’ll want to configure to ensure it won’t get stale.Oracle recommends adding the following settings to each configuration file:
Another piece of the puzzle will be to configure the presentation servers to work with multiple Java hosts. Once again, we must edit instanceconfig.xml to complete this task.This will involve listing the java host instances as shown below. The default Java Host port is 9810, but you can verify this by checking the OracleBI_Home\web\javahost\config\config.xml file.Simple load balancing will be performed in a round robin fashion between all instances listed in the config file.
<JavaHostProxy>
<Hosts>
<Host address=”<Javahost Machine1>” port=”9810”/>
<Host address=”<Javahost Machine2>” port=”9810”/>
</Hosts>
</JavaHostProxy>
You may also add an optional LoadBalance/Ping element. This element specifies the criteria for determining whether a Java Host is reachable.The ping element is not necessary if you wish to keep the default, which is 5 pings at 20 second intervals.
The final component we’ll look at today is the BI pres Services plug-in, which sits on the web servers.Here I’ll outline the changes necessary on each web server instance both for IIS and Java-based servers.IIS web servers will use the ISAPI plug-in, and the config file for this plug-in can be found in the OracleBIData_Home\web\config directory.The only element you must configure is the Hosts element, in which you will list the host and port of all Presentation Service instances.You may also optionally configure the LoadBalancer element which controls the autoroute feature. The default setting is false, which means that the user will receive an error if the current Presentation Server goes down.Setting this option to true would cause the server to attempt to connect to the next available Presentation Server without impact to the user.You also have the option of adding the ping element, which is the same element we just discussed when examining the Java host configuration.
The Java Servlet changes necessary for Java-based web server configuration are very similar to the ISAPI configuration mentioned above.You’ll need to edit the config file found in the OracleBI_Home\web\app\WEB-INF directory to include all Presentation Server host and port pairs. The <oracle.bi.presentation.sawconnect.loadbalance.AlwaysKeepSessionAffiliation> element is equivalent to the <LoadBalancer> element with the ISAPI plug-in and should be set to “Y” or “N”.
Next time we’ll continue to discuss the BI Presentation Server and how it will be configured to talk with the BI Server and Scheduler…
Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.
On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.
Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query not cached? The reason why the queries are not cache can be of many reasons. Some of the reasons are:
Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers. Non-cacheable Table: Physical tables in the OBIEE repository can be marked 'non-cacheable'. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.
Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate "roll-up" hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes. Caching is not configured: Caching is not enabled in NQSConfig.ini file.
Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes "bigger" when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.
Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.
Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.
OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2
I recently viewed an eSeminar on Oracle BI High Availability given by Oracle and thought I'd discuss the fundamentals of High Availability for anyone who's unfamiliar with the concept.High Availability, in the broadest possible terms, is a protocol for system design which will ensure a system is running acceptably for a certain percentage of a given time period.
Within OBIEE, availability can be defined basically as the ability to log into the system and perform normal operations at an acceptable and consistent level.This can be accomplished by employing system fault tolerance, which means that SPOF (single points of failure) must be eliminated. The goal with HA is to create a “shared nothing” environment in which any single box can temporarily fail without a major impact to users.During the eSeminar, it was explained that a general goal for a High Availability implementation might be 99.9% availability for a 24/7 system, although I'm sure service level agreements vary greatly from case to case.Using the "three nines" availability percentage, this would calculate to only 8.76 hours of downtime for an entire year, or about 43 minutes a month.
The above diagram should look familiar to most, this is a very simple representation of the OBIEE architecture showing the major components.A high availability deployment would have multiple instances of each of these objects in the case of an instance failing. An HA implementation will also use a clustered configuration for the BI server, including both a primary and secondary cluster controller. See the figure below which is another simplified look at the architecture with the redundant nodes added.
Notice that each of the objects or nodes is connected to multiple instances of every object it must talk with. I’ve left the catalog, repository, and scheduler database out of this diagram for simplicity’s sake, but each of these will be shared by its respective servers. You also may have noticed that the secondary cluster controller isn’t depicted here either, but should be included in any clustered HA setup. It is also possible for each presentation server to have its own copy of the presentation catalog, but due to the complicated setup and the difficulty with keeping the files in sync, the easier (and Oracle recommended) approach is to use a shared file system. Although the redundant web servers and their load balancer fall outside of the OBIEE scope, they are necessary to complete a true “shared nothing” environment all the way back to the user.
In future posts, I plan to drill into some of the details surrounding the configuration of the separate components of an HA deployment.We’ll be looking at some of the configuration file changes which will be necessary as well as exactly what types of impacts will be seen when specific failures do occur in an HA environment.Stay tuned for the next installment which will highlight the Presentation Services component….
I recently picked up a book titled "Troubleshooting Oracle Performance", which covers methods for tuning SQL and the Oracle DB versions 9iR2 through 11gR1. I have only read a few chapters to this point, but it appears to contain some useful information. One interesting point it makes in these early chapters is that many IT projects (BI implementations included) fail to define specific performance requirements (or SLA's) in addition to the functional requirements. In some instances, performance does not ever become an issue after implementation, but far too often, performance is not adequately tested during the test phases, and performance issues are not encountered until the application is already in production. This point is easy to ignore (until it occurs in production and it's too late), but poor performance can have the same damaging effect as functional defects on the overall user adoption of an application, and therefore performance testing should be given equal consideration as functional testing - this point is just something to think about which bares repeating every now and then.
Part of any OBIEE Implementation should be spent defining the proper caching strategy specific to a given project. This post is a basic look at what cache means to OBIEE.
WHAT IS CACHE? Pronounced "cash", it is generally an easily accessible place to store frequently used information. The most recognizable use of cache relates to the manner in which a personal computer utilizes this information. As it relates to PC, there are two basic types of cache: memory caching and disk caching.
Both types follow the same principle of storing recent or frequently used information in a memory buffer. Memory cache uses a portion of static RAM memory. Disk caching uses conventional main memory. When a piece of information is required, the system (or application) first checks the cache to see if the data is there.
The main purpose of cache is to improve performance. Accessing information stored in a memory buffer can be thousands of times faster than accessing a byte on a hard disk.
OBIEE CACHE Caching as it relates to OBIEE is a bit different, but the same general purpose applies. Rather than utilizing memory buffer to quickly access recent information, OBIEE will access a file containing a stored record set of information rather than spend processing time querying the database upon each request.
When a request, or query, is made for a set of records, OBIEE can store this as cached information in a file to be used later. When a similar request is made for this same information, OBIEE will retrieve this information from the cache rather than spending processing time negotiating with the database. This can greatly improve the performance of the application.
In the OBIEE/Siebel Analytics world, this is referred to this as ‘query cache’. By utilizing query cache, the cost of database processing only needs to be paid for the one time the query is run against the database. Subsequent identical queries will run against the cache.
Opinions expressed are entirely our own and do not reflect the position of BI Consulting Group, Oracle or any other corporation. Do NOT take anything written here, unless explicitly mentioned otherwise, to be BICG policy.
Posts
