Indexes

nVision Performance Tuning: 3. Indexing of Ledger, Budget, and Summary Ledger Tables on Non-Engineered Oracle Systems

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

If you are on a conventional non-engineered Oracle system, then it is not going to be viable to full scan the ledger tables for every query.  You are going to have to use indexes to find your data.  Every customer's nVision reports are unique to that customer and the nature of their business.  Different customers will analyse their data by different combinations of attributes.  Indexes will be needed to match those analysis criteria.

Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace

Apex Interactive Grid and IOT’s

I love the interactive grid in Application Express.  And here’s why… (Warning: Ranting mode is now on Smile)

You can tell people

Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in 12.1.0.2

One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version.

To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are local, you shouldn’t be impacted by the bug.

The SQL statements I use to prepare the schema to reproduce it are the following:

Exadata Storage Index Min/Max Optimization

Before discussing the Exadata-specific feature, let’s review what the database engine can do independently of whether Exadata is used. To execute queries containing the min or max functions efficiently, two specific operations are available with B-tree indexes defined on the column referenced in the min or max function. The first, INDEX FULL SCAN (MIN/MAX), is used when a query doesn’t specify a range condition. In spite of its name, however, it performs no full index scan. It simply gets either the rightmost or the leftmost index key:

Who is using this index?

Or, to put it another way, I want to change or drop this index, who and what will I impact?

The Challenge 

The problem that I am going to outline is certainly not exclusive to PeopleSoft, but I am going to illustrate it with examples from PeopleSoft. I often find tables with far more indexes than are good for them.
  • The Application Designer tool makes it very easy for developers to add indexes to tables. Sometimes, too easy!
  • Sometimes, DBAs are too quick to unquestioningly follow the advice of the Oracle tuning advisor to add indexes.

Recently, I have been working on 3 different PeopleSoft Financials systems where I have found major tables with a host of indexes.

There are several concerns:

Who is using this index?

Or, to put it another way, I want to change or drop this index, who and what will I impact?

The Challenge 

The problem that I am going to outline is certainly not exclusive to PeopleSoft, but I am going to illustrate it with examples from PeopleSoft. I often find tables with far more indexes than are good for them.
  • The Application Designer tool makes it very easy for developers to add indexes to tables. Sometimes, too easy!
  • Sometimes, DBAs are too quick to unquestioningly follow the advice of the Oracle tuning advisor to add indexes.

Recently, I have been working on 3 different PeopleSoft Financials systems where I have found major tables with a host of indexes.

There are several concerns:

MOUS 2013 Presentations

Just a quick note/announcement, that we had our annual Michigan Oracle Users Summit yesterday at the VisTaTech Center on the campus of Schoolcraft College, in Livonia, MI.  It was a good conference, and I think everyone who made it out, enjoyed their time there and saw some excellent presentations.

I did two presentations, Understanding and Interpreting Deadlocks and All About Indexes.  The presentations will be available at the MOUS website, but I also wanted to make them available here.  (See the links above.)

P.S.  I finally, just today, got my storage for my GoldenGate test boxes, so, I’ll be proceeding with that testing soon.  Stay tuned for my next blog post in that series, in a week or so.

OIC(A) again

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.


Here’s a simple table T1 holding 1M rows with the indexed column X having 10 distinct values: