Re: tuning queries against sys views?
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: SUzzell@xxxxxxxxxx
- Date: Wed, 1 Dec 2010 23:32:49 +0100
Did you gather dictionary stats and fixed object stats ?
Stefan
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Wed, Dec 1, 2010 at 10:01 PM, Uzzell, Stephan <SUzzell@xxxxxxxxxx> 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 |
> 7443.285.6505
>
>
>
Other related posts:
