OBIEE 11g Architecture

A request is made by the users through Answers and send to presentation server. The Presentation Server converts the request into logical SQL and send to BI server. The BI server converts logical SQL into physical SQL and send it to database. The result get back to the user through the same path.

Above we found the basic understanding of OBIEE architecture and flow of a request from user.Now lets have the elaborative idea about the complete Architecture.

The entire system architecture is known as BI Domain. The BI domain consists of Java components and non-Java components. Note that Java components are Weblogic server domain components. Non-java components are Oracle BI system components.

• Weblogic Server Domain(WLS Domain)

It consists of Managed server module and Admin server module. These services have the Java modules to trigger the Java services.

• Admin Server

This module contains Java components for administering the system. It is responsible to trigger the start, stop kind of admin activity for the peer Manager server processes.

• Managed Server

This module provides the run-time environment for the Java-based services and applications within the system. The services include SOA, Security, Publisher, BI office services, BI plugin and more.

• Node Manager

It is a distinct and separate Java utility. The node manager is responsible for providing processes management services to the Admin server and Managed server. This module triggers the auto start, stop and restart activities.

• OPMN( Oracle Process Manager and Notification server).

It is used to start and stop all system components of BI.

• Oracle Weblogic Server

This is a Java EE application server which provides support for the deployment of Oracle BI Java components.

• Fusion Middleware Control

This is a browser based tool used to monitor, manage and configure Oracle Business Intelligence components.

• Java Components

They are deployed as Java EE applications.

• Administrative components

JMX MBeans and Enterprise management applications manage all configurations and run time settings for Oracle BI.

• Oracle BI Publisher

This helps author, manage and deliver all types of highly formatted documents to employees, customers and suppliers.

• Oracle BI Office

This helps integrate Oracle BI and MS Office products.

• Oracle BI Action Services

This helps the Action framework and aids an administrator to manually configure which Web service directories can be browsed by users upon their creation of actions.

• Oracle RTD(Real Time Decisions)

This component helps companies to make better decisions in real time with its enterprise analytics software solutions.

• Oracle BI security services

This helps the integration of the Oracle BI server with the Oracle fusion middleware security platform.

• Oracle BI SOA Services

These enable objects in the Oracle BI presentation catalog to invoke analysis, agents and conditions. Also facilitates the process to invoke Orcale BI functionality from BPEL(Business Process Execution Language) processes.

• Oracle BI Plugin

This routes HTTP and SOAP(Simple Object Access Protocol) requests to Oracle BI presentation services.

• System Components

These are deployed as non-JEE components such as processes and services created in C++ and J2SE.

• Oracle BI Server

It provides significant query and data access capabilities. Also provides support to access and manage the enterprise semantic model.

• Oracle BI Presentation Services

This is responsible to provide the framework and interface in order to present business intelligence data to web clients.

• Oracle BI Scheduler

It supports extensible scheduling for analysis to be delivered to users at specific times.

• Oracle BI JavaHost

It helps Oracle BI presentation services to support Java tasks for Oracle BI scheduler, Oracle BI publisher and graph generation.

• Oracle BI cluster controller

It distributes requests to the BI server which results in requests to be evenly load-balanced across all BI Server process instances in the BI domain.

SQL Processing In Oracle

SQL Processing:-

To review, Oracle hard parsing reads in statistics, utilizes index information and creates an execution plan. Soft parsing already has the execution plan and doesn’t need to revisit the statistics and so on. The illustration below (taken from the Oracle Performance Tuning Guide) shows the steps the CBO (cost-based optimizer) takes to arrive at an execution plan.

Figure 2. How Oracle Arrives at an Explain Plan

This is the hard parse process.  At the end of this discussion, you will see why Oracle always looks to see if the SQL is already in the library cache first! (soft parse).

The CBO runs three to four SQL statements of its own during the hard parse process.  The SQL submitted by Oracle are called Dictionary SQL or Recursive SQL.  I’ll go with dictionary SQL for the remainder of this series.

These dictionary SQL statements are used to:

1.      Check the syntax of the table names and column names

2.      Pull in statistics

3.      Check permissions to all the objects involved

4.      Save the where clause predicates for future statistic runs

1.      The future statistics runs will produce histograms on these where clause items…another useful statistic that helps the CBO when there is skewed data…

The part in the dashed lines happens for both soft parsing and hard parsing.  The semantic checking, the permissions, and the lookup in the library cache.

The first thing in the hard parse is to do the query transformation part.  This is where the CBO can do subtle and not so subtle rewrites of your SQL.  Query transformation came to be in Oracle9 but it really has been around like forever because Oracle has always added functions to where clause items where the data types don’t match (to make them match…).  This can show up with the SQL not using available indexes because indexes are not used when there are functions on where clause items.

Oracle SQL is parsed before execution, and checked for syntax (and parts of the semantic check) before the SQL is loaded into the library cache.  As opposed to a soft parse (which does not require loading into the shared pool), a hard parse includes these steps:

1. Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" parse step)
2. Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords.
3. Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data.
4. Query Transformation - Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate. In earlier releases of Oracle the query_rewrite=true parameter had to be set for materialized view rewriting. 
5. Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g).  Oracle build the decision tree of costs during this period, choosing the path with the lowest perceived cost.
6. Create executable - Oracle builds an executable file with native file calls to service the SQL query.
7. Fetch rows - Oracle then executes the native calls to the data files to retrieve the rows and passes them back to the calling program.

Anytime a session issues SQL statement that does not already exist in the shared pool, then Oracle has to do a hard parse. Essentially performing all of the above steps.  If the statement already exists, then a soft parse occurs, skipping step 1. 

In a soft parse, Oracle must still perform a syntax parse and semantic check because it is possible that a DDL change altered one of the target tables or views since the SQL statement was originally executed.  In the case of DDL, all related SQL is marked as invalidated.

A hard parse is when your SQL must be re-loaded into the shared pool.  A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management.  Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. 

Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.

The optimizer contains three main components, which are shown below

 A set of query blocks represents a parsed query, which is the input to the optimizer. The optimizer performs the following operations:

1. Query transformer

The optimizer determines whether it is helpful to change the form of the query so that the optimizer can generate a better execution plan. The query transformer determines whether it is advantageous to rewrite the original SQL statement into a semantically equivalent SQL statement with a lower cost. When a viable alternative exists, the database calculates the cost of the alternatives separately and chooses the lowest-cost alternative.

 1.      Estimator

The optimizer estimates the cost of each plan based on statistics in the data dictionary.

The estimator uses three different measures to determine cost:

i. Selectivity

ii. Cardinality

iii. Cost

i. Selectivity: The percentage of rows in the row set that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as WHERE last_name LIKE 'A%', or a combination of predicates. A predicate becomes more selective as the selectivity value approaches 0 and less selective (or more unselective) as the value approaches 1.Selectivity is an internal calculation that is not visible in the execution plans.

ii. Cardinality: The cardinality is the number of rows returned by each operation in an execution plan. This input, which is crucial to obtaining an optimal plan, is common to all cost functions. The estimator can derive cardinality from the table statistics collected by DBMS_STATS, or derive it after accounting for effects from predicates (filter, join, and so on), DISTINCT or GROUP BY operations, and so on. The Rows column in an execution plan shows the estimated cardinality.

iii. Cost: This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

1.      Plan Generator

The optimizer compares the costs of plans and chooses the lowest-cost plan, known as the execution plan, to pass to the row source generator. It explores various plans for a query block by trying out different access paths, join methods, and join orders. Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.