Re: Query : Understand and Re-write
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 27 Mar 2010 14:59:13 +0100
Message-ID: <4bae0f38$0$22934$e4fe514c_at_news.xs4all.nl>
Op 25-3-2010 8:43, raja schreef:
> Thanks for all your immediate responses.
>
> AWR Report showed this query in one of the top list.
> I was not able to understand this query. So though posting here would
> give me immediate ideas to move forward.
>
> ok. from my observation.
> there is a UNION ( with 2 queries ), then there are having filter
> condition as TABLE.
> 1st query : trying to take list of table and view using ALL_OBJECTS
> 2nd query : trying to take list of SYNONYMS, TABLES, VIEWS using
> ALL_OBJECTS, ALL_SYNONYMS ( with unnecessary joining ALL_OBJECTS twice
> and then with ALL_SYNONYMS )
>
> Finally taking the output, filtering to take table names alone.
>
> To rewrite the query : I felt why shouldnt we just use ALL_OBJECTS to
> take the list of table_names alone !!!
> Comments : Collecting gather stats is waste, as the tables/views are
> all related to METADATA ( system related )
>
> Am i right ?
>
> Please help.
>
> With Regards,
> Raja.
Date: Sat, 27 Mar 2010 14:59:13 +0100
Message-ID: <4bae0f38$0$22934$e4fe514c_at_news.xs4all.nl>
Op 25-3-2010 8:43, raja schreef:
> Thanks for all your immediate responses.
>
> AWR Report showed this query in one of the top list.
> I was not able to understand this query. So though posting here would
> give me immediate ideas to move forward.
>
> ok. from my observation.
> there is a UNION ( with 2 queries ), then there are having filter
> condition as TABLE.
> 1st query : trying to take list of table and view using ALL_OBJECTS
> 2nd query : trying to take list of SYNONYMS, TABLES, VIEWS using
> ALL_OBJECTS, ALL_SYNONYMS ( with unnecessary joining ALL_OBJECTS twice
> and then with ALL_SYNONYMS )
>
> Finally taking the output, filtering to take table names alone.
>
> To rewrite the query : I felt why shouldnt we just use ALL_OBJECTS to
> take the list of table_names alone !!!
> Comments : Collecting gather stats is waste, as the tables/views are
> all related to METADATA ( system related )
>
> Am i right ?
>
> Please help.
>
> With Regards,
> Raja.
The first part selects all tables and defines tables of certain users as
'SYSTEM TABLE' which is filtered out by the surrounding query
(object_type is TABLE)
The second part of the union is obsolete, it's filtered out by the
surrounding query, because it filters tables where synonyms where returned.
So finaly, some data about 'non-system' tables is collected. The query may perform badly because of all the needless decodes.
In my DB, the largest part of the cost is caused by a lot of table access full over sys.obj$.
Shakespeare Received on Sat Mar 27 2010 - 08:59:13 CDT