Re: tuning queries against sys views?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- Date: Wed, 01 Dec 2010 23:08:40 +0100
Stephan,
If I had all freedom about the query, I would directly query the SYS
tables. What you can do is create materialized views that
1) only contain the columns that are referenced in your query
2) Optionally, directly get them from the SYS tables (this may require
creating the materialized views in a schema that can query the SYS
tables, and grants to your user the right to query these materialized
views).
This should be fast. That said, Tim's quite judicious remark about the
fact that your query checks that tables aren't dropped has somewhat
alarmed me - I'm uncomfortable with applications that feel some urge to
check that their tables aren't dropped. I hope it doesn't create and
drop tables on the fly.
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:57 PM, Uzzell, Stephan wrote:
>
> 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:
