Re: New diagnostic tool
From: Matthias Hoys <matthias.hoys_at_gmail.com>
Date: Fri, 9 Mar 2012 05:32:51 -0800 (PST)
Message-ID: <21572649.340.1331299971870.JavaMail.geo-discussion-forums_at_vbcu2>
On Friday, March 9, 2012 2:27:16 PM UTC+1, Matthias Hoys wrote:
> On Tuesday, February 28, 2012 4:53:06 AM UTC+1, Mladen Gogala wrote:
> > Oracle has just made available a new diagnostic tool which can help with
> > examining a SQL statement. Takes a statement, by the SQL_ID, and examines
> > it thoroughly. It goes through the optimizer environment, plan and plan
> > statistics, if available. It can also produce the execution history from
> > AWR, if the user is licensed for the performance tuning pack. Here is the
> > document:
> >
> > FAQ: SQL Health Check (SQLHC) (Doc ID 1417774.1)
> >
> > The tool is very interesting and it is free. It's very easy to use. It's
> > so easy that even Kim Kardashian could use it, to paraphrase one nice
> > GEICO commercial.
> >
> > --
> > http://mgogala.byethost5.com
>
> Although, I don't understand this recommendation:
>
> APEX_040100.WWV_FLOW_DATA Table has 1 index(es) with DOP different than its table. Table has a degree of parallelism of "1".
> Review index properties and fix degree of parallelism of table and/or its index(es).
>
> So, I check the APEX data dictionary:
>
> select table_name as obj_name,degree from all_tables where table_name = 'WWV_FLOW_DATA' and owner = 'APEX_040100'
> union all
> select index_name,degree from all_indexes where table_name = 'WWV_FLOW_DATA' and owner = 'APEX_040100'
>
> OBJ_NAME,DEGREE
> WWV_FLOW_DATA,1
> WWV_FLOW_DATA_IDX1,1
> WWV_FLOW_DATA_IDX2,1
>
> ??? I don't see any differences in the degree of parallelism between the table and its indexes, do you? I'm not sure where the SQLHC script gets this from? This is 10.2.0.5, btw.
>
> Matthias Hoys
Date: Fri, 9 Mar 2012 05:32:51 -0800 (PST)
Message-ID: <21572649.340.1331299971870.JavaMail.geo-discussion-forums_at_vbcu2>
On Friday, March 9, 2012 2:27:16 PM UTC+1, Matthias Hoys wrote:
> On Tuesday, February 28, 2012 4:53:06 AM UTC+1, Mladen Gogala wrote:
> > Oracle has just made available a new diagnostic tool which can help with
> > examining a SQL statement. Takes a statement, by the SQL_ID, and examines
> > it thoroughly. It goes through the optimizer environment, plan and plan
> > statistics, if available. It can also produce the execution history from
> > AWR, if the user is licensed for the performance tuning pack. Here is the
> > document:
> >
> > FAQ: SQL Health Check (SQLHC) (Doc ID 1417774.1)
> >
> > The tool is very interesting and it is free. It's very easy to use. It's
> > so easy that even Kim Kardashian could use it, to paraphrase one nice
> > GEICO commercial.
> >
> > --
> > http://mgogala.byethost5.com
>
> Although, I don't understand this recommendation:
>
> APEX_040100.WWV_FLOW_DATA Table has 1 index(es) with DOP different than its table. Table has a degree of parallelism of "1".
> Review index properties and fix degree of parallelism of table and/or its index(es).
>
> So, I check the APEX data dictionary:
>
> select table_name as obj_name,degree from all_tables where table_name = 'WWV_FLOW_DATA' and owner = 'APEX_040100'
> union all
> select index_name,degree from all_indexes where table_name = 'WWV_FLOW_DATA' and owner = 'APEX_040100'
>
> OBJ_NAME,DEGREE
> WWV_FLOW_DATA,1
> WWV_FLOW_DATA_IDX1,1
> WWV_FLOW_DATA_IDX2,1
>
> ??? I don't see any differences in the degree of parallelism between the table and its indexes, do you? I'm not sure where the SQLHC script gets this from? This is 10.2.0.5, btw.
>
> Matthias Hoys
Would it be so stupid to not take care of the white spaces in the "DEGREE" column? Received on Fri Mar 09 2012 - 07:32:51 CST