tuning queries against sys views?
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Dec 2010 16:01:56 -0500
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: