Why does Optimizer not pick up indexes?

[waitecj]

Our database versioin is 9.2.0.6.

What could we do with 'library cache lock'?
It seems that's the reason for our database slow down. What's the reason for 'library chache lock?

Thanks!

Taken from the manuals:

Library Cache Locks
When a database object, such as a table, view, procedure, package, or index, is referenced during the parsing of a SQL, DML, DDL, PL/SQL, or Java statement, the process that parses the statement acquires a library cache lock. In Oracle9i, the lock is held only until the parse or compilation completes, or, for the duration of the parse call.

So my guess is you aren't using bind variables, so you're spending alot more time hard parsing than you should be.