One is All You Need

While being an incredibly powerful and efficient way of indexing certain types of data, it is now relatively well accepted that Oracle’s Bitmap Indexes still have some kinks and sometimes cause erratic behavior in queries, particularly when dealing with parallel queries.

Unfortunately, this erratic behavior can even crop up when parallelism has been set to zero both on a given table, and in a query itself, if you are trying to execute even simple queries that contain PARTITION BY clauses. Unfortunately, there often appears to be no rhyme or reason as to when queries will exhibit this behavior when querying one bitmap-indexed table versus another.

Nonetheless, you may have found yourself faced with the following situation, and simply need a workaround.

Consider:

A fact table, ORDER_ITEM_F of the following general structure:

This table has numerous records already in it:

> SELECT count(*) FROM order_item_f;

  COUNT(*)
----------
    657802

Additionally, among others, a bitmap index has been created on the location ID field (loc_id).

> CREATE BITMAP INDEX order_item_f_b_idx_11 ON order_item_f (loc_id)


Next, for some internal reporting you would like to query this table to find counts of order items per sales location, while attaching a total for the set to each row (perhaps you are using this query in an Oracle Report and doing some nice formatting).

A simple query could be as follows:

> SELECT  distinct loc_id,
          count(*) over (partition by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

What you expect to receive from such a query is a row for each LOC_ID value, which includes a count of records having that LOC_ID, and an extra column containing the total number of records in the table (this value will be the same for each row returned).

Unfortunately, what you receive instead falls into one of the two scenarios below:

Scenario 1: Inaccurate counts based on full table count query (example above):

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17         12         89
        18         12         89
        19          8         89
        20          9         89
        21          1         89
        22          3         89
        23          4         89
        24          6         89
        25          6         89
        26          1         89
        27         11         89
        28          6         89
        29          4         89
        30          3         89
        31          1         89
        44          1         89
        45          1         89

Scenario 2: ORA-0600 - The Dread Internal Error

ERROR at line 1:
ORA-00600: internal error code, arguments: [kxfqupp_bad_cvl], [7215], [4],
 [0], [], [], [], []

Enabling autotrace (>SET AUTOTRACE ON) and re-running the query reveals that the database attempted to retrieve your results using the bitmap index:

Execution Plan
----------------------------------------------------------
Plan hash value: 2270429775

-------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |    16
|   1 |  SORT UNIQUE                   |                       |    16
|   2 |   WINDOW SORT                  |                       |    16
|   3 |    WINDOW BUFFER               |                       |    16
|   4 |     BITMAP INDEX FAST FULL SCAN| ORDER_ITEM_F_B_IDX_11 |   657K
-------------------------------------------------------------------------

An inspection of the plan shows that ~657K records are in fact accounted for by the fast full scan of the bitmap index. Yet in the case of Scenario 1 above, the counts coming back clearly do not reflect this total (if they come back at all!).

The PARALLEL/BITMAP bug strikes!

While a much more in-depth discussion of the cause for this bug could follow, sometimes you just need a practical fix:

The Fix: Full Table Scan Hint

To quickly resolve your problem, a simple hint can be added to your SELECT query, to force a full table scan instead in place of the bitmap index scan:

> SELECT  /*+ FULL(order_item_f) */
          distinct loc_id,
          count(*) over (PARTITION by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17     127674     657802
        18     136815     657802
        19      49540     657802
        20      51782     657802
        21       3908     657802
        22      11463     657802
        23      33415     657802
        24      29190     657802
        25      34141     657802
        26       1901     657802
        27      97483     657802
        28      31405     657802
        29      32619     657802
        30      15248     657802
        31          1     657802
        44       1090     657802
        45        127     657802

17 rows selected.

	John Weicher

The /*+ PARALLEL(table degree) */ hint may actually be my favorite query tuning tool. Actually, my favorite tool is the one that makes whatever query I’m tuning run faster, but I pull this hint out of the tool chest all the time. The PARALLEL hint is powerful, but potentially dangerous if used inappropriately. With most hints, usually the worst you can do is make your own query run faster (or slower!), but with PARALLEL you can significantly and adversely affect an entire database if you’re not careful.

The PARALLEL hint is just one way to take advantage of Oracle parallel execution. During parallel processing, large database operations are split into multiple pieces for multi-threaded execution. Parallel execution can be used in queries as well as DML (i.e. INSERT and UPDATE), DDL (i.e. CREATE INDEX) and in other ways not covered here. The Oracle documentation does a good job of fully explaining the inner sanctum of parallel execution, so we will not spend to much time on that topic. Let’s take a closer look at PARALLEL as a hint (it can also be used as a clause in a SQL statement) and what it can do for you…and to you!

The once-named Parallel Query Option (PQO) was first introduced in Oracle 7 and is primarily concerned with multi-threading large SQL operations. For our purposes, a large SQL operation typically means full table scans, sorts and hash joins, although index range scans on partitioned tables can occur in parallel with the PARALLEL_INDEX hint. Nested loops (index look ups) are not by themselves executable in parallel but these operations can be done in conjunction with a full table scans in parallel. So only certain types of queries will benefit from a PARALLEL hint. Queries that already include one or more full table scans are the most likely candidates. But, as we will see, queries that exclusively use nested loops (index look ups) may also benefit with a PARALLEL hint if the query is also asked to substitute full table scans for the nested loops.

Assuming that the parallel execution is available in your database (the DBA can set the maximum and minimum size of the process pool as well as many other parameters that affect parallel execution), you can take advantage of parallel processing in a number of ways. If a table is created or altered to include a DEGREE of parallelism, queries will automatically take advantage of parallel execution when large SQL operations are involved. However, it has been my experience that tables are rarely created with a parallelism DEGREE, since the ramifications for the database can be significant. As an example, query execution plans that once used indexes might decide to use full table scans and hash joins if the underlying table provides an inherent DEGREE of parallelism. Actually, the query does not decide anything - the Cost Based Optimizer decides; but that is a topic for another day. DEGREE, most simply, is the number a parallel processes that the database will spawn to execute the large SQL operation.

Let’s take a look at a “slow” query and how the PARALLEL can help it along. In this example, the “really_big_table” table has 10M rows and the “lookup table” has 100 rows. Of special note is lookup_id = 1 which has 5M rows in the really_big_table (50% of the table) while the other 99 lookup_ids are evenly distributed (about 50K rows a piece).

SELECT lut.lookup_name, count(*)
FROM   really_big_table rbt,
          lookup_table  lut
WHERE rbt.lookup_id = lut.lookup_id
GROUP BY lookup_name
ORDER BY lookup_name;

In our Oracle 10G database, the query execution plan looks like this:

This is the expected result. We need to look at every row in the really_big_table and join this to the lookup_table to get the lookup name. After the query completes the table scan, it hash joins the results and sorts the output to get the count(*) and sorts again to get us the final order. Its an efficient plan, but its also a single-threaded approach and if this query is running on a 16 processor machine, we can do better. If we add the PARALLEL hint with the table name (if the table is aliased, you may use the alias instead of the name) and the number of parallel threads (DEGREE of 2 in this case) we want to use, we get the following query with hint:

SELECT /*+ PARALLEL(rbt 2) */ lut.lookup_name, count(*)
FROM   really_big_table rbt,
          lookup_table  lut
WHERE rbt.lookup_id = lut.lookup_id
GROUP BY lookup_name
ORDER BY lookup_name;

Please note that the arguments to the PARALLEL hint are separated by a space, not a comma as if so often mistaken. A comma will actually cause the hint to be ignored in 9i, but in 10g it works both ways. This query produces a dramatically different query execution plan:

For those of you not used to parallel processing, this execution plan may look a little strange. All the extra lines here represent the operations necessary to take a serialized query, perform parallel execution, and finally gather the results back into a final result set (we promise to discuss the intricacies of these plans in more detail in a future post). In this particular case, the new, parallelized query ran 40% faster than the original query although the execution plan really didn’t change much. If you look closely, you will see that this query still performed 2 table scans, 1 hash join and some sorting. Its magic! Without making a single change to the actual query (except adding a PARALLEL hint), we have conjured a 40% improvement. Of course its not magic, we just commandeered more of the hardware resources. I performed these tests on a small windows box with two processors. As the following CPU usage diagrams show, the first query used one processor only while the parallelized query used both processors in parallel.

No Parallel Hint	Parallel execution with a degree of 2

If two processes were, good 4, 8 or even 16 must be all that much better. Obviously, this will depend entirely on the amount of processing power and disk I/O your hardware can muster. The amount of time savings will degrade as system resources are exhausted. Ultimately, queries that use too many parallel slaves (or too many parallel queries running at the same time) will degrade system performance for all other users. So care must be taken in choosing the proper degree of parallelism. For maximum effectiveness, 2 X number of available processors is a good rule of thumb. Even if you have a dedicated server, at some point adding parallel query processors will not appreciably increase the speed of query execution. The following CPU diagram shows the same query (on my small windows box) executed with 4, 8 and 16 degrees of parallelism (see the three big CPU bumps).

As it turns out, the CPU usage and query execution time did not change at all! Since there was available excess CPU time, this query likely was limited by disk I/O. But for enterprise quality hardware configurations, the PARALLEL hint will gobble up whatever resources it can get away with. Of course, there are many ways the DBA can limit the available resources, but in environments where parallel execution is not used such governors may not be in place. The result: A greedy PARALLEL hint can bring a database server to its knees. However, it has been this author’s experience that the PARALLEL hint can significantly improve query performance using resources that may be underutilized anyway. The PARALLEL hint can make you a hero!

Part 2: Monitoring Parallel Query Execution –>

Click here to ask SingleQuery.com a question.

Greg Pike

Those who have been involved with developing and deploying Business Intelligence solutions at an organization have probably experienced something that is close to Sir Isaac Newton’s first law; BI users have their habits and tend to stick to their habits unless shown a better way. One of the most common user habits I have observed over the last decade is the immediate and sometimes aggressive need to export data from new reports that have been developed and work with the data in their beloved spreadsheet program. For the BI developer, this need is often met with a good mix of astonishment and disappointment, as the value of their very flashy and complexly organized report is seemingly diminished as the data is then viewed column by column, row by row. Consuming BI data in that way is sometimes counter-productive and could seriously undermine the efforts of a BI project. But, as this is SingleQuery.com, I will turn the focus of this post to ways of possibly heading off that movement of data by using a single SQL query to solve a common user request.

Users will quite often wish to compare one result of data to the entire set of data it belongs. This is usually as described as a “Percent of Total” calculation. While a very simple and straightforward concept that both business users and BI developers all understand, those calculations are often missing in reports. This omission then causes motivated users to download the data into a spreadsheet and develop the formulas themselves to then easily see how the sales of blue shirts compares to the sales of shirts in total. Ranking is also a common driver of moving data into a spreadsheet. In order to rank products by number sold, all results must first be evaluated and then correctly ordered.

I have seen this occur in BI tools that do not handle multiple passes through data very well. This lack of functionality then causes the developer to make serious choices as to whether the inclusion of the calculation in the report is worth the extra effort needed to develop a custom solution in the report to deliver the “Percent of Total” or ranking calculations. Using Oracle’s Analytic Functions can make this worry immediately go away. They allow for results in each row to use sub-totals and totals of data to then use in Percent of Total calculations. So, the step that users perform to create a spreadsheet and formulas that divide the value in each row by the total of all values in the column is rendered moot and can easily be accomplished using one single SQL query.

The beauty of the Analytic Function is their ability to use “PARTITION BY” and “ORDER BY” clauses to create windows of data to perform functions on. The PARTITION BY drives the window or sub-set of data that the query is looking to evaluate. The ORDER BY allows for the rows to be in the desired order within a PARTITION BY argument.

Let’s look at a set of data that tracks sales of different clothing products over years:

The first step is determining a Percent of Total Sales value within each year is for each row to contain the total for all products within the year. Using the PARTITION BY clause the query will create windows for each year and then a sum on (units) will result in desired total.

select
year,
product,
units,
sum(units) over(partition by year)
from sales

Once the total for all units sold in a year appears on every row in the data set, you can see you have all the components to perform a “percent of total” calculation.

select
year,
product,
units,
sum(units) over(partition by year),
(units/(sum(units) over(partition by year))) Percent_of_Year
from sales

So, there you have it. Using a single query, Oracle can evaluate a specified set of rows in a result set and perform sub-total type calculations that can then be used for analytical calculations. This is all done in “one pass” of the data by the SQL query and could possibly go a long way and heading off issues with Business Intelligence reports in the future. And you didn’t need to get hit on the head by an apple to figure this out.

Another PIOCON white paper for the upcoming Collaborate ‘07 in Las Vegas April 15-19.

Session 523, time TBA

Services, Adapters and/or Proprietary Solutions
Tom DeLise, Piocon Technologies, Inc.

From the Overview:

In today’s IT and SOA landscape is there really a need for adapters? The answer is a resounding “Yes”. Distributed computing has been around for a very long time, but SOA in its most current incarnation is still very young. Early on vendors jumped on the bandwagon and laid claims that they were SOA compliant, since most people didn’t understand what true compliancy meant there wasn’t really an outcry until people started to use the software, but even then there wasn’t a resounding “call to arms”. Integration can be a daunting task, when a company does a large scale integration, adding one more system that should have had native service calls to a list tends to get missed in the grand picture.

Let’s go back and re-write history and ask the same question. Even if every vendor out there had all their current software available as services, would there really be a need for adapters? Again, the answer is a resounding “Yes”. The reason why is due to legacy systems. All large companies and just about all mid-size companies have made large investments in time and money to get their legacy systems to run the way they need to function in day to day business. So what is the answer; to “rip and replace”, to throw away the current corporate assets and invest even more into new technology and deal with the inherent downtime and bugs? Even though there is a legacy replacement strategy within SOA, this type of project is not easy and by design is an iterative phased endeavor. Let’s ask the question again, but slightly differently. How can we get the advantages of a SOA environment and utilize our legacy and standalone software while we execute on a long-term plan of action for an overall SOA environment? The quick answer here is services, adapters and / or proprietary solutions.

In this document I will go over three different techniques used in SOA integration. I will discuss what each technique is and how you might use pieces of them. I will show you some architectural examples and demonstrated some of the decisions that take place when implementing these integration techniques. I also will show how simple requirement changes could have big effects in how you integrate systems. I will then discuss a simplified pro and cons discussion for each of the three techniques outlined, finishing in some general pointers to consider when taking on a SOA integration project

Services, Adapters and/or Proprietary Solutions

Another PIOCON article for the upcoming Collaborate ‘07 in Las Vegas April 15-19.

Session 445, time TBA

Integrating Customer Touch Points - An Oracle ADF Case Study
Rohit Badiyani, PIOCON Technologies, Inc.
Peeyush Shukla, Mesirow Financial Advanced Strategies

From the introduction:

Mesirow Financial Advanced Strategies (MFAS) is one of the leading hedge fund-of-fund managers in the business, managing over $11 billion in assets. MFAS decided to invest in a customized Oracle-based information tracking and reporting system. This would enable consolidation of multiple data sources into a single repository, and create an application that would act as a single version of truth for all the information.

MFAS chose an Oracle platform for this overall business process tracking and business intelligence reporting solution. The environment consists of an Oracle 10g database, Oracle Application Server, Oracle Warehouse Builder, Oracle Reports, Oracle Discoverer, and Oracle Business Intelligence (BI) Beans. In addition, to build a robust, scalable and secure multi-tier enterprise application, MFAS chose J2EE as the standard platform for application development. Seeking a framework that enables simplified coding of complex business logic, flexibility and re-usability to adapt to changing business needs, MFAS settled on a Model-View-Controller (MVC) based Oracle Application Development Framework (ADF).

PIOCON worked alongside MFAS leadership to lay the foundation of the application development framework for Oracle ADF. Then, using this framework, PIOCON developed and deployed a number of critical web based information tracking applications.

This case study starts with a brief overview of Oracle ADF and then provides a detailed look at the rapid application development approach used by PIOCON at Mesirow Financial Advanced Strategies, showing real-world examples of ADF code and a working ADF solution.

Integrating Customer Touch Points - An Oracle ADF Case Study

Here is the link and overview of the paper that I will be presenting at Collaborate ‘07 in Las Vegas April 15-19.

Session 530 on Wed. 18 at 11:00am

Oracle Applcation Server High-Availablity (zipped)

From the Overview:

Oracle Fusion Middleware and its centerpiece, the Oracle Application Server (OAS), lie at the heart of many corporate SOA, Portal and Identity Management initiatives. These mission-critical applications may require 24×7x365 high-availability while allowing for unplanned or planned downtime for individual system components. Oracle provides several methods for achieving OAS high-availability and it is important to understand and choose the optimal architecture to fit the business needs. For example, while configuring OAS in an Active-Passive mode provides fail-over, it does not include load-balancing. Full redundancy, high-availability and load-balancing can only be achieved with an Active-Active configuration. To leverage OAS as the core technology for any mission-critical initiative, each high-availability option must be carefully considered. This paper addresses the OAS alternative configurations and the associated benefits and risks of each.

Oracle Applcation Server High-Availablity (zipped)

Hope to see you there!

Hot off the presses is my white paper for Collaborate ‘07 in Las Vegas April 15-19. Although the contents of the paper is too large for this forum, the complete text can be obtained at the following link:

Session 407 on Monday, April 16 at 11:30am

Enablng Enterprise Provisioning and Security with Oracle Portal

From the Overview:

Today’s technology landscape provides a myriad of solutions for enabling enterprise-wide, access right provisioning. Provisioning strategies range from simple, synchronized LDAP repositories to comprehensive Identity Management packages and complex SOA/BPEL implementations with granular business processes. However, these robust provisioning solutions additionally require highly-specialized skill sets and can be costly to purchase, deploy and maintain. An alternative method for managing user metadata is available to organizations already invested in Oracle Portal technology.

Although Oracle Portal primarily provides its proprietary Group objects to simplify the management of user privileges internally (i.e. page group access), these constructs can be additionally leveraged to facilitate centralized, granular access to data and functionality throughout the organization. In effect, a user’s security protocol and even the graphical experience for external applications can be provisioned from Oracle Portal.

Enablng Enterprise Provisioning and Security with Oracle Portal

I will be at the PIOCON booth Monday and Tuesday so stop by and introduce yourself!

Although not a wide-spread problem, Oracle sometimes produces less than optimal execution plans when the Discoverer-generated queries are poorly conceived. I recently encountered this problem in an Apps 11i environment on Oracle 9i using Discoverer 4i. Even though the database server contained ample memory and hardware resources (20+ processors and lots o’ RAM), some queries simply refused to complete in a reasonable amount of time. What is a reasonable amount of time you ask? In this case, less than the 20-40 estimated hours as seen in V$SESSION_LONGOPS!

The problem manifests itself from a poorly-conceived EUL combined with the 9i Cost Based Optimizer’s (CBO) desire to use Cartesian Merge Joins when it feels this join method is appropriate. However, in an Oracle Apps environment, the tables may contain 100+ columns and often every one of these is exposed in the EUL. If the EUL developer added all of the flex fields from the base table into the EUL, watch out! A Cartesian Merge Join with this many columns consumes system resources quickly and forces the joins off to physical disk. All of a sudden, the Cartesian Merge Join is going to take 17000 seconds. If you are experiencing agonizingly slow queries from Discoverer and see a Cartesian Merge Join, this may be the culprit. Short of reworking the Discoverer EUL or the individual workbooks, forcing the CBO to change the execution plan may be solution.

In my situation, strategic placement of the /*+ no_merge */ hint in selected, complex folders of the EUL provided a workable, but hardly optimal, solution. The /*+ no_merge */ hint kindly asks the CBO to use an alternate method for joining intermediate result sets as the query is executed. While the new plan eliminates Cartesian Merge Joins and may result in a 99% time reduction, you still may be faced with a 24 minute query that should probably take a minute or less. Although there is most definitely a better solution, /*+ no_merge */ offers a quick and dirty way to get your Discoverer queries back under control until a more suitable long-term solution can be applied.

By the way, the real long-term solution is to eliminate all unused columns from the EUL (especially the VARCHAR2(150) flex fields) and then remove the /*+ no_merge */ hints.