Re: SQL Tuning -- large, long-running outer join query

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: jeff.thomas@xxxxxxxxxxx
  • Date: Thu, 19 Jan 2006 09:59:41 -0500


On 01/19/2006 08:08:48 AM, Thomas Jeff wrote: 

We have this purchased app that is generating the following query.   It
runs
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
from
longops that this thing would likely run for another 7-8 hours before
completing.

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)
FROM
 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    */
WHERE


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.


--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2006