Help to solve sql tuning

[manoj]

Dear Sir

I wanted to tune the below query.Any help would be of great help

Below is the query

--Find Year to Generate Risk Score Starts here
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
from statement
where borrid= &var_borrId and user_id= &var_user_id and coaid =
&var_mdlid
and to_char(stmtdt, 'yyyy') in
(
select os.year from
(
select borrid, year, count(*) as cntActuals
from subjective_prm_trans
where borrid= &var_borrId and user_id= &var_user_id and mdlid =
&var_mdlid and endnodeflag = 'E'
group by year, borrid
) os,
(
select j.borrid, j.year, j.cntVariable + k.cntFixed as
cntMdlTotals
from
(
select a.borrid, a.year , count(*) as cntVariable
from subjective_prm_trans a, mdl_Parameter_Tree m
where
a.prmid = m.parentid and a.mdlid = m.mdlid and
a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
a.prmid not in (select r.prmid from mdl_parameter_tree r where
trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id=
&var_user_id
group by a.borrid , a.year
) j,
(
select count(*) as cntFixed
from mdl_parameter_tree u
where u.prmid not in
(
select t.prmid
from mdl_parameter_tree t
where t.rootnode in
(
select b.rootnode
from subjective_prm_trans a, mdl_parameter_tree b
where
a.mdlid = b.mdlid and a.prmid = b.prmid and
a.endnodeflag = 'N' and a.value between 0.0001 and 1
and
a.prmid not in (select r.prmid from mdl_parameter_tree
r where trim(lower(r.prmname)) = 'project risk' and r.mdlid=
&var_mdlid ) and
a.borrid= &var_borrId and a.mdlid= &var_mdlid and
a.user_id= &var_user_id
) and
t.endnodeflag = 'E' and parametertype = 'S' and mdlid=
&var_mdlid
) and
u.endnodeflag ='E' and
u.parametertype = 'S' and
u.mdlid= &var_mdlid
) k
) om
where
os.borrid = om.borrid and
os.year = om.year and
os.cntActuals = om.cntMdlTotals
)
order by year desc


Well below is the explain plan


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=1 Bytes=61
)

1 0 SORT (ORDER BY) (Cost=28 Card=1 Bytes=61)
2 1 HASH JOIN (SEMI) (Cost=26 Card=1 Bytes=61)
3 2 TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=2 Card=1 Bytes=48)

4 2 VIEW OF 'VW_NSO_1' (Cost=23 Card=1 Bytes=13)
5 4 NESTED LOOPS (Cost=23 Card=1 Bytes=91)
6 5 HASH JOIN (Cost=13 Card=1 Bytes=78)
7 6 VIEW (Cost=4 Card=1 Bytes=39)
8 7 SORT (GROUP BY) (Cost=4 Card=1 Bytes=55)
9 8 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1
Bytes=55)

10 6 VIEW (Cost=9 Card=1 Bytes=39)
11 10 SORT (GROUP BY) (Cost=7 Card=1 Bytes=107)
12 11 FILTER
13 12 HASH JOIN (Cost=5 Card=1 Bytes=107)
14 13 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_T
RANS' (Cost=2 Card=1 Bytes=81)

15 13 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (Cost=2 Card=142 Bytes=3692)

16 12 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
' (Cost=2 Card=1 Bytes=155)

17 5 VIEW
18 17 SORT (AGGREGATE)
19 18 FILTER
20 19 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE'
(Cost=2 Card=1 Bytes=284)

21 19 FILTER
22 21 NESTED LOOPS (Cost=6 Card=1 Bytes=636)
23 22 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 By
tes=481)

24 23 TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM
_TRANS' (Cost=2 Card=1 Bytes=68)

25 23 BUFFER (SORT) (Cost=2 Card=1 Bytes=413
)

26 25 TABLE ACCESS (FULL) OF 'MDL_PARAMETE
R_TREE' (Cost=2 Card=1 Bytes=413)

27 22 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (Cost=2 Card=18 Bytes=2790)

28 21 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
' (Cost=2 Card=1 Bytes=155)





Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
387229 consistent gets
306954 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Any help would help to resolve the issue

[ora...@msn.com]

Where to begin ...

You're grinding away through 694183 data blocks (306954 physical reads
and 387229 consistent gets) to return ONE row -- that's a bit
excessive, I think.

Your accsss of STATEMENT is impaired (as to index usage) by the
to_char() function calls. I expect this is 10.2.0 so I would be
considering the creation of a function-based index on that column.

Possibly you should think of creating one or more materialized views
based upon the various subqueries you're using. A materialized view
may be better than the in-line views as an MV can be indexed.

I would also ensure that the statistics on the tables in question are
current.

Of course given the fact you haven't provided any DDL for the tables
involved, nor any sample data upon which to test no one could actually
do much more than what I've already stated.


David Fitzjarrell

[manoj]

Dear sir

But let me tell you that I am going to use this query in the
procedure.Also would like to tell you that the query is taking a long
time to execute(i.e 1 ros selected in elapsed time 28.781 seconds).I
would like to know what should be the steps to make the query optimal.

Regards

> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

[ora...@msn.com]

Comments embedded.

On May 23, 10:25 am, manoj <manojwastha...@gmail.com> wrote:
> Dear sir
>
> But let me tell you that I am going to use this query in the
> procedure.

That does NOT change my initial advice.

> Also would like to tell you that the query is taking a long
> time to execute(i.e 1 ros selected in elapsed time 28.781 seconds).

Certainly it will; reading 5.3 GIGABYTES of data to return ONE row
isn't a fast operation.

> I
> would like to know what should be the steps to make the query optimal.
>

Are you experiencing difficulty reading the response given you? Can
you not see the suggestion to create at least one function-based index
on the stmtdt column in the STATEMENT table? Can you not understand
that using materialized views IN PLACE OF your in-line views MAY
improve performance dramatically as MATERIALIZED VIEWS can be INDEXED
and in-line views cannot?
Suggestions to improve the performance have been provided you; that
you choose to ignore them in hopes of finding a 'silver bullet' is not
my fault nor is it any omission of mine.

> > - Show quoted text -- Hide quoted text -

>
> - Show quoted text -

David Fitzjarrell

[manoj]

Dear sir

But Let me tell you that I am going to create a procedure and I am
going to use this in a procedure in the begin section.How would
creating a materialised view will help to improve the performance of
the query.Also the version i am going to use is 9.2.0.1.0
Is the advice going to be the same as the previous.But I dont in this
case creation of materialised view is going to help

Regards

[ora...@msn.com]

On May 23, 10:54 am, manoj <manojwastha...@gmail.com> wrote:
> Dear sir
>

> But Let me tell you that I am going to create a procedure and I am
> going to use this in a procedure in the begin section.How would
> creating a materialised view will help to improve the performance of
> the query.

You replace some, or ALL, of this:

with a materialized view BASED on that query or parts thereof, then
INDEX that materialized view and use IT in PLACE of the in-line views
you've created.

> Also the version i am going to use is 9.2.0.1.0
> Is the advice going to be the same as the previous.

Yes.

> But I dont in this
> case creation of materialised view is going to help
>

Clearly because you don't understand the concept. Please read this
post CAREFULLY.

> Regards
>



David Fitzjarrell

[Charles Hooper]

On May 23, 10:25 am, manoj <manojwastha...@gmail.com> wrote:

> Dear Sir
>
> I wanted to tune the below query.Any help would be of great help
>
> Below is the query
>
> --Find Year to Generate Risk Score Starts here

(<snip)

> Statistics
> ----------------------------------------------------------
> 18 recursive calls
> 0 db block gets
> 387229 consistent gets
> 306954 physical reads
> 0 redo size
> 432 bytes sent via SQL*Net to client
> 584 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 114 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Any help would help to resolve the issue

David has already mentioned that your use of "and to_char(stmtdt,
'yyyy')" makes it impossible to use a regular index on the stmtdt
column, if one exists.

Looking at the plan, you may notice that there is not a single index
access, the cardinality numbers are low (possibily indicating that it
has been a while since statistics were gathered), there is a merge
join Cartesian which can kill performance with large numbers of rows,
the formatting without spaces makes it impossible to see how the data
was retrieved in order, and it appears that you are using a version of
Oracle prior to 10g R1 based on the presence of "SORT (GROUP BY)".
That may be an indication that Oracle is converting or is not
converting some/all of the NOT IN subqueries as necessary.

Looking at the query, slightly reformatted:
SELECT
TO_CHAR(STMTDT, 'YYYY') AS ID, TO_CHAR(STMTDT, 'YYYY') AS YEAR
FROM
STATEMENT
WHERE
BORRID= &VAR_BORRID
AND USER_ID= &VAR_USER_ID
AND COAID = &VAR_MDLID
AND TO_CHAR(STMTDT, 'YYYY') IN
(
SELECT
OS.YEAR
FROM
(
SELECT
BORRID,
YEAR,
COUNT(*) AS CNTACTUALS
FROM
SUBJECTIVE_PRM_TRANS
WHERE
BORRID= &VAR_BORRID
AND USER_ID= &VAR_USER_ID
AND MDLID = &VAR_MDLID
AND ENDNODEFLAG = 'E'
GROUP BY
YEAR,
BORRID) OS,
(
SELECT
J.BORRID,
J.YEAR,
J.CNTVARIABLE + K.CNTFIXED AS CNTMDLTOTALS
FROM
(
SELECT
A.BORRID,
A.YEAR ,
COUNT(*) AS CNTVARIABLE
FROM
SUBJECTIVE_PRM_TRANS A,
MDL_PARAMETER_TREE M
WHERE
A.PRMID = M.PARENTID
AND A.MDLID = M.MDLID
AND A.ENDNODEFLAG = 'N'
AND A.VALUE BETWEEN 0.0001 AND 1
AND A.PRMID NOT IN
(SELECT
R.PRMID
FROM
MDL_PARAMETER_TREE R
WHERE
TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
AND R.MDLID= &VAR_MDLID )
AND A.BORRID= &VAR_BORRID
AND A.MDLID= &VAR_MDLID
AND A.USER_ID= &VAR_USER_ID
GROUP BY
A.BORRID ,
A.YEAR ) J,
(
SELECT
COUNT(*) AS CNTFIXED
FROM
MDL_PARAMETER_TREE U
WHERE
U.PRMID NOT IN
(SELECT
T.PRMID
FROM
MDL_PARAMETER_TREE T
WHERE
T.ROOTNODE IN
(SELECT
B.ROOTNODE
FROM
SUBJECTIVE_PRM_TRANS A,
MDL_PARAMETER_TREE B
WHERE
A.MDLID = B.MDLID
AND A.PRMID = B.PRMID
AND A.ENDNODEFLAG = 'N' AND A.VALUE BETWEEN 0.0001
AND 1
AND A.PRMID NOT IN
(SELECT
R.PRMID
FROM
MDL_PARAMETER_TREE R
WHERE
TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
AND R.MDLID= &VAR_MDLID )
AND A.BORRID= &VAR_BORRID
AND A.MDLID= &VAR_MDLID
AND A.USER_ID= &VAR_USER_ID
)
AND T.ENDNODEFLAG = 'E'
AND PARAMETERTYPE = 'S'
AND MDLID= &VAR_MDLID
)
AND U.ENDNODEFLAG ='E'
AND U.PARAMETERTYPE = 'S'
AND U.MDLID= &VAR_MDLID
) K
) OM
WHERE
OS.BORRID = OM.BORRID
AND OS.YEAR = OM.YEAR
AND OS.CNTACTUALS = OM.CNTMDLTOTALS
)
ORDER BY
YEAR DESC

You seem to be repeating the same inline view SQL in a couple places,
you seem to be performing a COUNT in two inline views so that you can
perform "AND OS.CNTACTUALS = OM.CNTMDLTOTALS" - there might be a
better way. Where you have "SELECT OS.YEAR" - you may want to convert
this to 2 date columns with Jan 1 of the year in the first date column
and Dec 31 of the year in the second - doing that would allow you to
change "TO_CHAR(STMTDT, 'YYYY') IN" into a statement that does not
require TO_CHAR.

It will take someone familiar with the data to tell you if some of the
extra work in the inline views may be eliminated.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[manoj]

Dear Sir
Thank you very much for giving me a very good advice sir
Please correct me If i am wrong sir

As you said that I have to create materialised view with replacing
some or all of this
So Please correct me sir If i am wrong

create or replace materialized view view1_name enable query rewrite
as

As you also said that we need to create a function based index on the
below view

create index ind1 on view1_name(To_char(stmtdt,'YYYY'));

Now the rest of the query I should write like this

select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
from statement
where borrid= &var_borrId and user_id= &var_user_id and coaid =
&var_mdlid
and to_char(stmtdt, 'yyyy') in
(
select os.year from
(
select borrid, year, count(*) as cntActuals
from subjective_prm_trans
where borrid= &var_borrId and user_id= &var_user_id and mdlid =
&var_mdlid and endnodeflag = 'E'
group by year, borrid
) os,
(
select j.borrid, j.year, j.cntVariable + k.cntFixed as
cntMdlTotals

from (select * from view_name1);



Please correct me If I am wrong sir
Thank you.

> K&M Machine-Fabricating, Inc.- Hide quoted text -

[manoj]

Dear Sir
Thank you very much for giving me a very good advice sir
Please correct me If i am wrong sir

As you said that I have to create materialised view with replacing
some or all of this
So Please correct me sir If i am wrong

create or replace materialized view view1_name enable query rewrite
as

As you also said that we need to create a function based index on the
below view

create index ind1 on view1_name(To_char(stmtdt,'YYYY'));

Now the rest of the query I should write like this

select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
from statement
where borrid= &var_borrId and user_id= &var_user_id and coaid =
&var_mdlid
and to_char(stmtdt, 'yyyy') in
(
select os.year from
(
select borrid, year, count(*) as cntActuals
from subjective_prm_trans
where borrid= &var_borrId and user_id= &var_user_id and mdlid =
&var_mdlid and endnodeflag = 'E'
group by year, borrid
) os,
(
select j.borrid, j.year, j.cntVariable + k.cntFixed as
cntMdlTotals

from (select * from view_name1);



Please correct me If I am wrong sir
Thank you.


On May 24, 2:23 am, Charles Hooper <hooperc2...@yahoo.com> wrote:

[manoj]

Dear Sir

Please correct me if I am wrong sir

sql>create or replace materialized view VIEW_NAME1 enable query
rewrite as

select borrid, year, count(*) as cntActuals
from subjective_prm_trans
where borrid= &var_borrId and user_id= &var_user_id and mdlid =

&var_mdlid and endnodeflag= 'E'

) group by year desc

prompt FUNCTION BASED index creation

create index ind1 on statement(to_char(stmtdt,'YYYY'));

PROMPT
PROMPT REST OF THE QUERY

select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
from statement
where borrid= &var_borrId and user_id= &var_user_id and coaid =
&var_mdlid
and to_char(stmtdt, 'yyyy') in
(
select os.year from

order by year desc

pLEASE CORRECT ME IF I AM WRONG SIR

[manoj]

Dear Sir

Any update on the below

Regards

On May 24, 2:23 am, Charles Hooper <hooperc2...@yahoo.com> wrote:

[manoj]

Dear Sir

Please check the sample data for testing also sir of the three tables

Sample data of subjective_prm_trans

ID BORRID MDLID PARENTID PRMID GDDID GDSBMSID SCORESETID LVL WEIGHT
PARAMETERTYPE ENDNODEFLAG YEAR VALUE REMARKS USER_ID TRANS_DATETIME
120183 5120 2 1 8 205 52 2 2 1.00000 S E 2008 1.0000 284 5/20/2008
10:44:16 AM



Sample data of mdl_parameter_tree

MDLID PRMID PRMNAME PRMFLAG STATUSFLAG PARENTID LVL SEQ ROOTNODE
PRECELINK ENDNODE CHAIN ENDNODEFLAG GDSID SCORESETID BMARKID RSSID
TOPLVL_WTCOEF FACILITYCATID RATIOID PARAMETERTYPE THRESHOLDVALUE
REMARKS
2 84 IR - Technology dependence S A 80 3 4 4 /4/8 /4/8/4 E 75 2 0 0 0
0 0 S 0

Sample data of statement table

BORRID STMTID STMTDT STMTPERIOD AUDIT_METHOD_ID STMTORDER RECONCILETO
COAID USER_ID TRANS_DATETIME
5120 1 3/31/2006 12 2 1 3/31/2005 2 284 5/20/2008 10:23:59 AM

Regards
On May 24, 2:23 am, Charles Hooper <hooperc2...@yahoo.com> wrote:

[manoj]

Please check below the statistics of the above query after applying
the indexes


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6468 consistent gets
0 physical reads

0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

and the explain plan
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156 Card=1 Bytes=28)
1 0 SORT (ORDER BY) (Cost=156 Card=1 Bytes=28)
2 1 MERGE JOIN (SEMI) (Cost=154 Card=1 Bytes=28)
3 2 SORT (JOIN) (Cost=4 Card=1 Bytes=15)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'STATEMENT' (Cost=2 Card=1
Bytes=15)

5 4 INDEX (RANGE SCAN) OF 'IND3' (NON-UNIQUE) (Cost=1 Card=1)

6 2 SORT (UNIQUE) (Cost=151 Card=1 Bytes=13)
7 6 VIEW OF 'VW_NSO_1' (Cost=149 Card=1 Bytes=13)
8 7 MERGE JOIN (Cost=149 Card=1 Bytes=91)
9 8 SORT (JOIN) (Cost=140 Card=1 Bytes=52)
10 9 MERGE JOIN (CARTESIAN) (Cost=139 Card=1 Bytes=52)

11 10 VIEW (Cost=4 Card=1 Bytes=39)
12 11 SORT (GROUP BY) (Cost=4 Card=1 Bytes=17)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'SUbjectTIVE_PRM_TRANS' (Cost=2
Card=1 Bytes=17)

14 13 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

15 10 BUFFER (SORT) (Cost=139 Card=1 Bytes=13)
16 15 VIEW
17 16 SORT (AGGREGATE)
18 17 FILTER
19 18 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1
Bytes=11)

20 18 FILTER
21 20 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=2
Card=7 Bytes=70)

22 21 NESTED LOOPS (Cost=68 Card=1 Bytes=44)

23 22 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=34)

24 23 TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2
Card=1 Bytes=20)

25 24 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

26 23 BUFFER (SORT) (Cost=64 Card=1 Bytes=14)

27 26 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64
Card=1 Bytes=14)

28 27 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

29 22 INDEX (RANGE SCAN) OF 'IND2' (NON-UNIQUE) (Cost=1 Card=92)

30 20 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64
Card=1 Bytes=41)

31 30 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

32 8 FILTER
33 32 SORT (JOIN)
34 33 VIEW (Cost=72 Card=1 Bytes=39)
35 34 SORT (GROUP BY) (Cost=8 Card=1 Bytes=31)
36 35 FILTER
37 36 HASH JOIN (Cost=6 Card=1 Bytes=31)
38 37 TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2
Card=1 Bytes=24)

39 38 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

40 37 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=85
Bytes=595)

41 36 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64
Card=1 Bytes=41)

42 41 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

Dear Sir

I have created indexes on three tables

***********************************STATEMENT******
**************************************************
-- Create/Recreate indexes
prompt:

prompt composite index on borrid,user_id:

create index IND3 on STATEMENT (BORRID,USER_ID);

*****************************SUBJECTIVE_PRM_TRANS*
************************************************** *
prompt:
prompt composite index on borrid,mdlid,user_id column:

create index IND4 on SUBJECTIVE_PRM_TRANS (BORRID,MDLID,USER_ID);

prompt:

*****************************MDL_PARAMETER_TREE***
************************************************** ***************
PROMPT:
PROMPT INDEX ON ROOTNODE COLUMN:
- Create/Recreate indexes
create index IND2 on MDL_PARAMETER_TREE (ROOTNODE);

prompt:
prompt index on MDLID COLUMN:

create index IND5 on MDL_PARAMETER_TREE (MDLID);

prompt:

After creating the indexes the output was executed in 1
milliseconds.But I would like to ask you one thing.Its seems like
after applying the indexes on the three tables the query became
faster.As well as the cost of the query is now after applying the
index is 156.Initially the cost was 28.How come there is a increase in
the cost after applying the indexes in the three table?Please help to
resolve the issue.?

[ora...@msn.com]

You never provided any of the statistics for these tables, so any
response is nothing more than a guess, however I expect that your
statistics were not current prior to the index creations. Again, it's
merely a guess.



David Fitzjarrell

[manoj]

Dear Sir

Please check the statistics after creating the indexes.

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6468 consistent gets
0 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Regards

> David Fitzjarrell- Hide quoted text -

[ora...@msn.com]

Comments embedded.

On Jun 10, 12:10 am, manoj <manojwastha...@gmail.com> wrote:
> Dear Sir
>
>         Please check the statistics after creating the indexes.
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 6468 consistent gets
> 0 physical reads
> 0 redo size
> 432 bytes sent via SQL*Net to client
> 584 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 114 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Regards
>

Sorry, no, these are not the statistics you should be concerned with
when computing the 'cost' of a query. You need to look at the
LAST_ANALYZED column of USER_TABLES and USER_INDEXES to determine when
the last OPTIMIZER statistics were computed. I know for a fact in
10.2.0 that creating an index updates the index statistics
automatically; the 'cost' is calculated from the various bits and
pieces of data found in USER_TABLES and USER_INDEXES which
DBMS_STATS.GATHER_TABLE_STATS, DBMS_STATS.GATHER_INDEX_STATS or
DBMS_STATS.GATHER_SCHEMA_STATS will compute. The 'statistics' from
the query run have NOTHING to do with how the query plan is generated.

Check the LAST_ANALYZED column of USER_TABLES for the tables involved
in this query and you may be surprised that the optimizer statistics
are not up-to-date.

> > - Show quoted text -- Hide quoted text -

>
> - Show quoted text -

David Fitzjarrell