Re: tuning queries against sys views?

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 01 Dec 2010 14:26:43 -0700
Message-ID: <4CF6BD93.8000002_at_evdbt.com>

Stephan,

Only interesting thing I can see is that the only reason the ALL_ALL_TABLES view is being referenced is to determine if the table is DROPPED (i.e. in the recyclebin) or not.  If you have the parameter RECYCLEBIN set to OFF, then joining to that view would be unnecessary.  Obviously, the parameter can be changed, so having that check makes good sense, but just an observation...

Otherwise, I don't see any mistakes in the join logic.  You're stuck with it.  There can't be any good reason for running this query frequently -- after all, how often are primary key definitions modified?

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => 13087 West Cedar Drive #225, Lakewood CO 80228
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

On 12/1/2010 2:01 PM, Uzzell, Stephan wrote:

I’ve had a nasty query dropped in my lap, and I’m not sure anything can be done with it – but if anyone can help, it is you guys! :-)

 

SELECT 'PKLEN', SUM(TC.DATA_LENGTH)

                FROM ALL_CONSTRAINTS AC,

                        ALL_TAB_COLUMNS TC,

                        ALL_CONS_COLUMNS CC ,

                        ALL_ALL_TABLES T

WHERE AC.OWNER = TC.OWNER

AND AC.TABLE_NAME = TC.TABLE_NAME

AND AC.OWNER = CC.OWNER

AND AC.TABLE_NAME = CC.TABLE_NAME

AND CC.COLUMN_NAME = TC.COLUMN_NAME

AND AC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

AND AC.CONSTRAINT_TYPE = 'P'

AND AC.VALIDATED = 'VALIDATED'

AND AC.STATUS = 'ENABLED'

AND AC.TABLE_NAME = 'RN_NAME'

AND AC.OWNER = 'DLQPTR'

AND AC.OWNER = T.OWNER

AND AC.TABLE_NAME = T.TABLE_NAME

AND T.DROPPED = 'NO'

 

From what I’ve been told, this is coming from a third-party app, so cannot be easily modified. And it is a brute of a query – takes 15 minutes or so to run, and the plan is hundreds of lines long.

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.21       0.21          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2    927.76     927.84          0  108313204          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        4    927.98     928.06          0  108313204          0           1

 

Oracle 10.2.0.3 and 10.2.0.4 on Windows.

 

Is anything possible?

 

Thanks,

stephan

 

_____________________________________________________________________________

Stephan Uzzell | MICROS Systems, Inc. 

 

Database Administrator - OPERA Global Technical Services

7031 Columbia Gateway Dr,  Columbia, MD  21046 | ( 443.285.8000x2760 | 7 443.285.6505

 

-- http://www.freelists.org/webpage/oracle-l Received on Wed Dec 01 2010 - 15:26:43 CST