On 01/19/2006 08:08:48 AM, Thomas Jeff wrote:
> We have this purchased app that is generating the following query. It
> for 8 hours then crashes due to ORA-1555
. I'm reluctant to tinker with
> the undo settings or increase the undo tablespace size, as it appears
> longops that this thing would likely run for another 7-8 hours before
Why are you reluctant? Did you check V$UNDOSTAT? It tells you how many undo
blocks are used at any single time and how many active transactions there
were since the database startup (the TXNCOUNT value is cumulative).
> I'm at a loss on how to tune this -- I can't touch the
> physical structure, and I don't know enough to see any hints that could
> possibly help.
> Any ideas?
> SELET (boatload of columns)
> TS_SALES_ORDLNS, /* 465,175 rows, 20,930 blocks */
> OD_SALES_ORDLNS, /* 18,733,125 rows, 1,247,303 blocks */
> IA_SALES_ORDLNS, /* 18,631,135 rows, 603,410 blocks */
> TS_SALES_ORDLNS_HD, /* 474,465 rows, 25,819 blocks */
> TS_SALES_ORDLNS_LN /* 472,645 rows, 14,043 blocks */
As for the query, to rewrite it properly, I'd have to have some business
knowledge. I also notice that you don't have system statistics (no cpu
costing). Do you have histograms for all involved columns?
What percentage of the data do you want to retrieve? If you want to report
on the better part of the tables, then the plan might be right and you
may want to use brute force and change parallel degrees of the tables read
by the full table scan. The query looks like a star schema but getting ORA-1555
indicates that you have an OLTP database. That means that bitmap
indexes are out of the question.