Prev: HA Problem with ORA-02019
Next: CPU and memory metrics for databases in Oracle RAC
From: joel garry on 24 Mar 2010 16:05
On Mar 24, 11:39 am, John Hurley <hurleyjo...(a)yahoo.com> wrote:
> On Mar 24, 12:27 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
>
> snip
>
>
>
> > Well if you stick a " create view stupid as " on top of the SQL you
> > can then see it is trying to create the following:
>
> > SQL> desc stupid
> >  Name                                      Null?    Type
> >  ----------------------------------------- --------
> > ----------------------------
> >  TABLE_CATALOG                                      VARCHAR2
> >  TABLE_SCHEMA                                       VARCHAR2(30)
> >  TABLE_NAME                                         VARCHAR2(30)
> >  TABLE_TYPE                                         VARCHAR2(18)
> >  TABLE_GUID                                         VARCHAR2
> >  DESCRIPTION                                        VARCHAR2
> >  TABLE_PROPID                                       VARCHAR2
> >  DATE_CREATED                                       DATE
> >  DATE_MODIFIED                                      DATE
>
> Ahhh ... very nice companion to the I'm with stupid t shirt approach
> maybe?

LOL! Maybe we need a stupid view contest for an oraclenerd t-
shirt :-)

jg
--
@home.com is bogus. See http://oraclenerd.spreadshirt.com/ if anyone
isn't familiar with it. Always wanted a cdos t-shirt, never could
decide exactly what to put on it. "Why Be Denormal?" almost made it
out of my brain and into the site.


From: Tim X on 25 Mar 2010 03:12
raja <dextersunil(a)gmail.com> writes:

> Hi,
>
> I cant understand what they are trying to fetch in the below query.
>
> Can anyone please explain me ?
>
> Also, Can anyone help me to re-write the following query, to
> understand better and get good performance :
>
>
> SELECT *
> FROM
> (SELECT
> NULL table_catalog,
> decode(owner, 'PUBLIC', NULL, owner) TABLE_SCHEMA,
> object_name TABLE_NAME,
> decode
> (
> owner, 'SYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'SYSTEM',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'DMSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'ORDSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'EXFSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'WMSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'MDSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'CTXSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'OLAPSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), 'WKSYS',
> decode(object_type, 'TABLE', 'SYSTEM TABLE', 'VIEW',
> 'SYSTEM VIEW', object_type), object_type
> ) table_type,
> NULL table_guid,
> NULL description,
> NULL table_propid,
> created date_created,
> last_ddl_time date_modified
> FROM
> all_objects
> WHERE
> object_type IN('TABLE', 'VIEW')
> UNION
> SELECT
> NULL table_catalog,
> decode(o2.owner, 'PUBLIC', NULL, o2.owner) TABLE_SCHEMA,
> o2.object_name TABLE_NAME,
> o2.object_type table_type,
> NULL table_guid,
> NULL description,
> NULL table_propid,
> o2.created date_created,
> o2.last_ddl_time date_modified
> FROM
> all_objects o2,
> all_objects o3,
> all_synonyms s
> WHERE
> o2.object_type = 'SYNONYM'
> AND(o3.object_type = 'TABLE' OR o3.object_type = 'VIEW')
> AND o2.owner = s.owner
> AND o2.object_name = s.synonym_name
> AND s.table_owner = o3.owner
> AND s.TABLE_NAME = o3.object_name
> )
> dbschema_tables
> WHERE table_type = 'TABLE';
>
>
>
> Thanks in Advance.
>

My question would be if you don't know what this does or how it works,
why do you

a) believe it has a performance problem and
b) think that re-writing it will improve anything?

answers to these two questions would go a long way to answering your
original question.

The first thing I would do is

1. re-format the query so that you can see clearly how all the statement
components relate to each other
2. Understand what each component does
3. Clearly define/identify what the query is supposed to do
4. Gather stats on how it is actually performing
5. Define what are acceptable performance stats
6. Perform analysis of the full query using the various provided oracle
tools to identify possible problem areas
7. Investigate how to modify things to address any performance issues.
Note that this may or may not involve re-writing the query. It could
also involve other actions, such as adding/removing/modifying indexes,
writing functions, updating stats, using temporary tables or other
built-in facilities or possibly totally changing the approach bieng used
to get the information desired.

Then iterate through steps 6 to 7 until you get the desired outcome.
Until you understand the query and the desired results, anything anyone
else does will mean little for you as it won't have the necessary
context. Note that you will likely get much more specific assistance
from this group if you also show what you have done to try and address
your grasp of the issue, especially if you can show what you understand
and what is still confusing. Including details of oracle version and
platform is also very useful as different versions/platforms can provide
alternative features and solutions.

--
tcross (at) rapttech dot com dot au
From: raja on 25 Mar 2010 03:43
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.
From: John Hurley on 25 Mar 2010 08:45
On Mar 25, 3:43 am, raja <dextersu...(a)gmail.com> wrote:

snip

> 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.

Find out who is running the query and ask them why they are running it
and what they think it is giving them.

You don't tune everything that shows up in an AWR report you tune
things that make a difference to your business.
From: Shakespeare on 27 Mar 2010 09:59
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
First  |  Prev  | 
Pages: 1 2
Prev: HA Problem with ORA-02019
Next: CPU and memory metrics for databases in Oracle RAC