ORA-26028: index initially in unusable state

peternolan9 · Post by **peternolan9** » Wed Jan 19, 2005 2:12 am

Hi All,
just wondering if anyone has ever seen this message before. I haven't.

Situation is we are loading a couple of years worth of history for a transaction file. We have split the work into pieces because daily updates will be incremental so we only allocated the disk for incremental updates and didn't allocate disk to run a one off load of 2 years history. (No real need for it)

I have a job to gunzip each file in turn and then load it. The first gunzip->load->gzip worked fine but for some reason left an index on the table in an unusable state. Easy to fix, drop/re-create the index. But we lost 14 hours elapsed processing time overnight and I'd prefer not to lose it again tonight...

Does anyone know where I might look to find out what might have happened? Like why did the index become unusable and was it anything I did? Is there a detailed Oracle log somewhere that might tell me? (I know this is possibly the wrong place to ask but I thought someone here might have seen this problem specifically....)

Thanks

mtl_unit_transactions_06a..Oracle_OCI_Load_141: Internal Error: Unable to initialize the Direct Path Context
ORA-26028: index ORAPRDSTAGE.MTL_UNIT_TRANSACTIONS_IX1 initially in unusable state

ray.wurlod · Post by **ray.wurlod** » Wed Jan 19, 2005 2:20 am

Surely it's the Oracle DBA's job to know about these things? That's the person whom you should ask. Did you try Ask Tom?

And, no, I've never seen that message. Here's output from oerr.

Code: Select all

$ oerr ORA 26028
26028, 0000, "index %s.%s initially in unusable state"
//* Cause:  An index is in IU state prior to the beginning of a direct
//*         path load, it cannot be maintained by the loader.
//* Action: Either rebuild the index, re-create the index, or use either
//*         SKIP_UNUSABLE_INDEXES or SKIP_INDEX_MAINTENANCE (Sql*Loader only).

Presumably "IU" stands for "initially unusable".

peternolan9 · Post by **peternolan9** » Wed Jan 19, 2005 7:34 am

ray.wurlod wrote:Surely it's the Oracle DBA's job to know about these things? That's the person whom you should ask. Did you try Ask Tom?

And, no, I've never seen that message. Here's output from oerr.
Code: Select all
$ oerr ORA 26028
26028, 0000, "index %s.%s initially in unusable state"
//* Cause:  An index is in IU state prior to the beginning of a direct
//*         path load, it cannot be maintained by the loader.
//* Action: Either rebuild the index, re-create the index, or use either
//*         SKIP_UNUSABLE_INDEXES or SKIP_INDEX_MAINTENANCE (Sql*Loader only).
Presumably "IU" stands for "initially unusable".

Hi Ray,
and you would be absolutely correct, and if we had an Oracle DBA I would ask him, but we don't, and we still have to get the job done as best we can......

...

We went to the message manual etc and I understand the index is in an unusable state, but I was trying to find out how it got that way.....I suspect all I will get from an Oracle person is 'your index is not usable, re-build it'....

Anyway,
I think I found why the index was in an unusable state. And I think we still have a problem...

We are using ORAOCIBL Stage on AIX/DS7.5. The messages in the log look different to my last DS install (sun/DS7.1). I am seeing messages that list the value that the parameters to the stage are set to like:

mtl_unit_transactions_06a..Oracle_OCI_Load_141: SERVICENAME='IWS'.

I don't recall seeing these types of messages before.

Also, the message:

mtl_unit_transactions_06a..Oracle_OCI_Load_141: Internal Error: Unable to initialize the Direct Path Context
ORA-26028: index ORAPRDSTAGE.MTL_UNIT_TRANSACTIONS_IX1 initially in unusable state

seems to indicate it is trying to perform Direct Path load.

Now direct path loading does not perform index maintenance at the end of the load. And if there are duplicates in the table the index build fails.

My last site (Tom Nel + Others) did a lot of testing around direct path loading and decided against it. They stayed with sqlldr.

Further, the document oraocibl.pdf says :
Ascential DataStage provides an Orabulk stage which generates control and data files for bulk loading into a single table in an Oracle target database. This stage then loads the files into the database by using the Oracle command sqlldr. However, the Orabulk stage cannot populate a table in different loading modes. Use Oracle OCI Load to do this.

Orabulk seems to be gone from 7.5....???

Now I've looked at some jobs from my last site (7.1) and I can see they were using ORAOCIBL and I know for sure that they ran with sqlldr and not direct path. At least I'm as certain and my old brain cells let me be...

So what I'm thinking is that at this site for some reason we are using direct path for loading rather than sqlldr.

I've asked my DS tech support here to check and he has come back and said he can find no option that allows ORAOCIBL to use sqlldr rather than direct path....But I believe such an option exists since Tom told me he tested both....

My problem is I need to load about 480M rows for back loading history and ORAOCIBL is running at about 1,800 rows per second on a shiny new 5 CPU AIX machine. And that is no faster than performing inserts via ODBC.

So, I'm trying to find out what might be wrong with the DS/ORA environment here such that we are getting such terrible performance for bulk loading....and I think the unusable index was just a pointer to what the real problem might be...

Can anyone shed some light on where we might look to solve our problem? I know on our previous site we were able to consistently get 12,000 rows per second on a similar sun machine and we got up to 90,000 rows per second using PX to load data....1,800 just isn't a load rate I want to contemplate for 480M rows....

Hopefully Tom will also be listening, but I know it is 'friday afternoon' where he is.....

chulett · Post by **chulett** » Wed Jan 19, 2005 7:58 am

Just some quick points...

"Direct path" *is* sqlldr, it is one of two load options - Conventional or Direct Path. Conventional is the slower of the two, obeys constraints and indexes as it goes. Direct Path is faster as it (in essence) defers the constraints during the load which makes it possible to load duplicates into a unique index. As you noted, after the load finishes it attempts to reestablish the constraints. If that fails, because you've done something like load duplicates into a unique index, that index will be marked 'UNUSABLE' and will need to be rebuilt.

kcbland · Post by **kcbland** » Wed Jan 19, 2005 9:21 am

Direct path load is the optimal loading method for high volume warehouse tables. You are going to have to use a careful indexing strategy, as indexing becomes trickier. For instance, bitmap indexes become fun, as they deal with low cardinality columns and sqlldr does not play nice. As a preference, I always using direct path, as it can be 100's X faster than conventional.

Craig precisely describes what sqlldr is barfing on. Direct path disables the index update and defers it until the load is finished. When you put data in that violates the nature of the index, you have to remove the offending data in order to build the index. Look over at asktom.oracle.com. If you corrupted a unique primary key index, it will take forever to delete the offending data because you have to drop the index because it will prevent queries against the primary key. In this case, you may opt to build a non-unique primary key index. What it will do is allow you to load and never go into an unusable state in the case of duplicates. You will have to inspect the data to insure duplicates are not loading.

Large tables like this must be partitioned, so building a locally managed non-unique primary key index should not affect performance. Since you're loading transactional data, any querying by primary key is usually not done, except during ETL or auditing. This index is not required for these purposes.

peternolan9 · Post by **peternolan9** » Wed Jan 19, 2005 9:32 am

chulett wrote:Just some quick points...

"Direct path" *is* sqlldr, it is one of two load options - Conventional or Direct Path. Conventional is the slower of the two, obeys constraints and indexes as it goes. Direct Path is faster as it (in essence) defers the constraints during the load which makes it possible to load duplicates into a unique index. As you noted, after the load finishes it attempts to reestablish the constraints. If that fails, because you've done something like load duplicates into a unique index, that index will be marked 'UNUSABLE' and will need to be rebuilt.

Hi Craig,
and how do I tell it direct path or conventional? I can't see a parameter anywhere on this....is there any documentation on this in the manuals?? Happy to RTM....

kcbland · Post by **kcbland** » Wed Jan 19, 2005 9:42 am

direct=FALSE on the sqlldr command invocation

chulett · Post by **chulett** » Wed Jan 19, 2005 10:02 am

Or omit it all together as it is the default. You have to explicitly set DIRECT to TRUE to get a Direct Path load.

AFAIK, the 'Ora Bulk' stage only does a Conventional load.

peternolan9 · Post by **peternolan9** » Wed Jan 19, 2005 10:24 am

kcbland wrote:direct=FALSE on the sqlldr command invocation

Hi Kenneth,
I must be missing something....

I have an ORAOCIBL icon in a DS job and I do not see where I can set direct=FALSE in any of the settings for the icon. I see properties like service name, user name, password etc but I do not see 'direct'???

And I have done these kinds of loads many times in many places....?????

I cannot for the life of me figure out why it is so slow......and the real problem is 'why is it so slow?'...I was thinking it was direct path but I am told direct path should even be faster than conventional...???

Just getting frustrated that it has taken the last 8 hours to load just 48M rows and I'm just sitting here at 6:30 waiting for the load to finish so I can start the next job....

....I must be the worlds most expensive 'scheduler'...

chulett · Post by **chulett** » Wed Jan 19, 2005 10:33 am

Like I said, I'm pretty sure there is no option to do a Direct Path load in that stage. And I certainly hope you're not running the thing in 'Automatic' mode, that's even slower.

You need to look into getting your control files generated once and then you can add the DIRECT=TRUE keyword to the control file. Or you can include it in the invocation of the sqlldr command itself. We use DataStage to generate the flat file and then scripts to do the actual load so we can get a direct path load.

kcbland · Post by **kcbland** » Wed Jan 19, 2005 10:44 am

Since you're not using PX, you may need to consider doing manually partitioning of the load files. By creating a load-ready file for each partition, you can absolutely name the partition to load in the control file. Then, launch sqlldr for each file against each partition simultaneously. That is, of course, if you have no global indexes. If you do, then that can explain what is taking so long to load.

Without knowing more about indexing on your tables, I couldn't tell you where sqlldr is bottlenecking. You could post your .log file here and let us take a look at it. Chances are you are getting thru the data portion fine, it's the index updating that's taking all of the time.

peternolan9 · Post by **peternolan9** » Thu Jan 20, 2005 7:44 am

Hi Guys,
next step in our diagnosis.....we generated a million rows of data and tried to load on a small AIX server in our office here. Same result, 1,400 rows per second even when loaded directly via oracle loader....

We took the same 1M rows and loaded them on a 2 CPU PC we have running win2003 and we get 10,000 rows per second!!! From my last site I am used to seeing 10-12,000 rows per second on a 4 CPU sun box.

It seems to me an oracle setting in the default install of AIX.

Q.

So, question, is anyone else out there using AIX 5.1/DS 7.5? And if so, can you share with me load rates and machine size? And did your Oracle DBA have to do anything you know about to achieve those rates?

Thanks in hope of a miracle answer..

chulett · Post by **chulett** » Thu Jan 20, 2005 7:54 am

peternolan9 wrote:we generated a million rows of data and tried to load on a small AIX server in our office here. Same result, 1,400 rows per second even when loaded directly via oracle loader....

When you say 'loaded directly via Oracle loader', you mean sqlldr and not the Ora Bulk stage in automatic mode, yes?

And when you run these, are you specifying 'DIRECT=TRUE' on both boxes? I'd also be curious if the output generated by the two jobs was different in any way, other than the rows/second of course. Anything of interest when comparing the two log files?

kcbland · Post by **kcbland** » Thu Jan 20, 2005 9:55 am

Apples and toaster ovens. A 1.4 GHz chip vs a 3.0 Ghz chip is unfair, not to mention the underlying disk subsystems are radically different. I can make a 1 cpu Windoze solution fly on a straight sqlldr direct path into a table and beat the pants off a multi-TB unix instance. But, I can't get that multi-TB onto that PC and match performance.

Can you share anything with us regarding your table, like DDL?

peternolan9 · Post by **peternolan9** » Fri Jan 21, 2005 10:00 am

chulett wrote:
peternolan9 wrote:we generated a million rows of data and tried to load on a small AIX server in our office here. Same result, 1,400 rows per second even when loaded directly via oracle loader....
When you say 'loaded directly via Oracle loader', you mean sqlldr and not the Ora Bulk stage in automatic mode, yes?

And when you run these, are you specifying 'DIRECT=TRUE' on both boxes? I'd also be curious if the output generated by the two jobs was different in any way, other than the rows/second of course. Anything of interest when comparing the two log files?

Hi Craig,
on this one I mean we used SQL LOADER outside the DS environment just to test.....on the3 test outside DS we wrote the SQL LDR statement by hand and had no direct statement as far as I am aware.....(But I'm 5 miles from that box and didn't see the command executed...my stand in ORA DBA said he tried everything he could and could not get AIX above 1,400 on our small old box and the windoze machine ran the same statements at 10,000/second...)

There must be a problem because other areas of performance (lookups etc) are running as expected, ODBC access is running as expected....I'm sure you see my problem...no tools, no DBA, nothing to compare to....trying to get the job done...

Hi Kenneth,
yep, I'm aware of the performance differences in the underlying CPUs/OS etc.

My thoughts here are that the sqlloader is running at the same pace as an ODBC insert. And on windoze/solaris the loader runs 5x faster than ODBC inserts to oracle. So I was rather expecting a similar ratio on AIX/Oracle.

Since I was able to re-create the problem on 2 AIX machines I am thinking along the lines that there is a setting when ORA is installed on AIX that causes this problem and that someone else might have seen it. Which is why I'm asking if someone else out there has ORA/AIX and what load speeds are they achieving when using SQLLDR?

I can share table DDL with you, but it's every table we try so I cannot see that the table DDL itself is a problem/issue.....