RE: tuning queries against sys views?
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'knecht.stefan@xxxxxxxxx'" <knecht.stefan@xxxxxxxxx>, "'SUzzell@xxxxxxxxxx'" <SUzzell@xxxxxxxxxx>
- Date: Thu, 2 Dec 2010 07:16:25 -0600
That is exactly what I was wondering. I know I've seen behavior like this
before.
Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>
CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and
may also be privileged. If you are not the named recipient, please notify the
sender immediately and delete the contents of this message without disclosing
the contents to anyone, using them for any purpose, or storing or copying the
information on any medium.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Stefan Knecht
Sent: Wednesday, December 01, 2010 4:33 PM
To: SUzzell@xxxxxxxxxx
Cc: oracle-l-freelists
Subject: Re: tuning queries against sys views?
Did you gather dictionary stats and fixed object stats ?
Stefan
=========================
Stefan P Knecht
CEO & Founder
s@xxxxxxxx<mailto: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<mailto:info@xxxxxxxx>
http://www.10046.ch
=========================
On Wed, Dec 1, 2010 at 10:01 PM, Uzzell, Stephan
<SUzzell@xxxxxxxxxx<mailto: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 | 7
443.285.6505
Other related posts:
