Oracle Database Discussions

We have a DSS type database (11G R2), which has all kinds of queries run against it. There is not one typical query that is generated by the app, and so, while we use bind variables (good practice), no two requests are alike.

Some of the queries may end up joining 10-20 tables (the same table actually gets used multiple times). While these are not difficult to generate, they do take time to parse. On a few queries I noticed that query itself might execute in 3-4 seconds, but because DB needs to do a hard parse, total time will be close to a minute.

Doing a query against V$SESS_TIME_MODEL shows "hard parse elapsed time" = 45,709,571.

Is there any way to help speed up the process? Our tables/indexes are mostly cached due to system using the same set over and over again, so it seems impractical that DB would spend so much time parsing...

Any help/suggestions would be much appreciated.

Thank you in advance.

--Alex