RE: tuning queries against sys views?
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: 'Stephane Faroult' <sfaroult@xxxxxxxxxxxx>
- Date: Wed, 1 Dec 2010 16:57:02 -0500
Hi Stephane,
Have to admit materialized views are not something I've used before - so now of
course I want to try it! :-)
What do you think would be a better approach: materialized views for each of
those sys views? Or one materialized view that already contains all the join
logic?
Thanks,
stephan
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Wednesday, 01 December, 2010 16:17
To: Uzzell, Stephan
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: tuning queries against sys views?
Quite in line with the preceding thread about synonyms ...
I would create materialized views bearing the same name as the dictionary views
in the schema that run the query for every view that is queried with a constant
criterion. They will mask the public synonyms. I assume that you are on a
production database on which people don't spend their time running DDL
statements and that therefore it is possible to have a low refreshing rate and
yet always being in sync with the data dictionary.
Then you will be able to index them at will, and the application will suspect
nothing.
Stephane Faroult
RoughSea Ltd<http://www.roughsea.com>
Konagora<http://www.konagora.com>
RoughSea Channel on Youtube<http://www.youtube.com/user/roughsealtd>
On 12/01/2010 10: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
Other related posts:
