tuning queries against sys views?

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: