Home » RDBMS Server » Performance Tuning » Performance1 (Oracle 9i)
Performance1 [message #326087] Tue, 10 June 2008 02:45 Go to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

I wanted to ask you one question.Now when I was tuning the query the cost of the above query was 28 and the total time taking to execute the query was 28.741 seconds.Now after applying the indexes on the columns which are there in filter condition the cost of the query have been increased to 156 and total time taking to execute the query was 1 milliseconds.How come this happen.I have heard this that if the cost of the query is low the query is much tunned.Please help to resolve the issue?.I think the reason must be of not analysing the tables for which indexes are created.?
Please check the statistics before applying the index
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


and please check the statistics after applying the index

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

Report message to a moderator

Re: Performance1 [message #326212 is a reply to message #326087] Tue, 10 June 2008 12:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Insufficient information.
Oracle version upto 4 digits is required.
Did you update the statistics on the tables and indexes after creating the indexes?
If not, use dbms_stats and try again and post the results.

EDIT:
Apparently, I missed the fact that you did not collect stats.
Collect the stats and please post.

>>I have heard this that if the cost of the query is low the query is much tunned.
Not exactly correct. The sql with the lower cost is preferred by CBO.
"COST" with respect to CBO is just a derived number based on available statistics. Without proper statistics, CBO may hallucinate.

[Updated on: Tue, 10 June 2008 12:53]

Report message to a moderator

Re: Performance1 [message #326308 is a reply to message #326212] Wed, 11 June 2008 01:04 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

let me tell you the version 9.2.0.1.0
But when I do
dbms_stats.gather_schema_stats(USER,CASCADE=>TRUE)
when i run this package there is no impact happening on the user

I only get this message as
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
Now could you please tell me how to collect stats?What do you mean by hallucinate

Regards

[Updated on: Wed, 11 June 2008 01:07]

Report message to a moderator

Re: Performance1 [message #326311 is a reply to message #326308] Wed, 11 June 2008 01:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You just need a few more options.
Now execute the sql and look into plan.
try again with something like this.
scott@gretel_gretel > EXEC DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size,method_opt=>'For all indexed columns');

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Performance1 [message #326327 is a reply to message #326311] Wed, 11 June 2008 01:54 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear Sir

Before runing this package the query was taking 24.941 seconds
And Now after runing this package provided by you it is taking 481.859 seconds to execute the query.Initially the query was taking 24.941 seconds.Now Please suggest?.Now Is the statistics up-to date?.Could you please tell me what happens when we run this package?

Regards

[Updated on: Wed, 11 June 2008 02:04]

Report message to a moderator

Re: Performance1 [message #326330 is a reply to message #326327] Wed, 11 June 2008 02:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
just collect the baseline stats.
Something like this.

exec dbms_stats.gather_schema_stats('SCOTT'),METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);


If it does not help, remove the stats, try again.
Compare and post the plans before and after.

Report message to a moderator

Re: Performance1 [message #326335 is a reply to message #326330] Wed, 11 June 2008 02:18 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Dear sir

Could you please tell me how to remove stats in sql?
Please check the explain plan after runing this package

exec dbms_stats.gather_schema_stats('SCOTT'),METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1080 Card=1 Bytes=61
          )

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

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

  10    6             VIEW (Cost=356 Card=1 Bytes=39)
  11   10               SORT (GROUP BY) (Cost=353 Card=1 Bytes=107)
  12   11                 FILTER
  13   12                   HASH JOIN (Cost=351 Card=1 Bytes=107)
  14   13                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=81)

  15   13                     TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=11 Bytes=286)

  16   12                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
          ' (Cost=3 Card=1 Bytes=100)

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

  21   19                 FILTER
  22   21                   NESTED LOOPS (Cost=353 Card=1 Bytes=268)
  23   22                     NESTED LOOPS (Cost=6 Card=1 Bytes=200)
  24   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  25   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  26   22                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=68)

  27   21                   TABLE ACCESS (FULL)OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)





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


Please check the explain plan after running the package
exec dbms_stats.gather_schema_stats('SCOTT'),METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1080 Card=1 Bytes=61
          )

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

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

  10    6             VIEW (Cost=356 Card=1 Bytes=39)
  11   10               SORT (GROUP BY) (Cost=353 Card=1 Bytes=107)
  12   11                 FILTER
  13   12                   HASH JOIN (Cost=351 Card=1 Bytes=107)
  14   13                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=81)

  15   13                     TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=11 Bytes=286)

  16   12                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

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

  21   19                 FILTER
  22   21                   NESTED LOOPS (Cost=353 Card=1 Bytes=268)
  23   22                     NESTED LOOPS (Cost=6 Card=1 Bytes=200)
  24   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  25   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  26   22                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=68)

  27   21                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)





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

Regards

[Updated on: Wed, 11 June 2008 02:59]

Report message to a moderator

Re: Performance1 [message #326342 is a reply to message #326335] Wed, 11 June 2008 02:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Delete schema stats.

http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php

or just remove the stats for the concerned table.

Report message to a moderator

Re: Performance1 [message #326353 is a reply to message #326335] Wed, 11 June 2008 03:13 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
I am sorry sir.
The first explain plan and the statistics was from this package
EXEC DBMS_STATS.gather_schema_stats (ownname => 'IRS2007_YBL_PROD', cascade =>true,estimate_percent => dbms_stats.auto_sample_size,method_opt=>'For all indexed columns');


Whereas the second explain plan and the statistics was from this package

exec dbms_stats.gather_schema_stats('IRS2007_YBL_PROD',METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);

Report message to a moderator

Re: Performance1 [message #326411 is a reply to message #326353] Wed, 11 June 2008 06:36 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
ould you please tell me how to remove stats in sql?
Please check the explain plan after runing this package

EXEC DBMS_STATS.gather_schema_stats (ownname => 'SCOTT', cascade =>true,estimate_percent => dbms_stats.auto_sample_size,method_opt=>'For all indexed columns');


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1080 Card=1 Bytes=61
          )

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

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

  10    6             VIEW (Cost=356 Card=1 Bytes=39)
  11   10               SORT (GROUP BY) (Cost=353 Card=1 Bytes=107)
  12   11                 FILTER
  13   12                   HASH JOIN (Cost=351 Card=1 Bytes=107)
  14   13                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=81)

  15   13                     TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=11 Bytes=286)

  16   12                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE
          ' (Cost=3 Card=1 Bytes=100)

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

  21   19                 FILTER
  22   21                   NESTED LOOPS (Cost=353 Card=1 Bytes=268)
  23   22                     NESTED LOOPS (Cost=6 Card=1 Bytes=200)
  24   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  25   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  26   22                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=68)

  27   21                   TABLE ACCESS (FULL)OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)





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


Please check the explain plan after running the package
exec dbms_stats.gather_schema_stats('SCOTT'),METHOD_OPT=>'For all indexed columns size 250',CASCADE=>true);


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1080 Card=1 Bytes=61
          )

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

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

  10    6             VIEW (Cost=356 Card=1 Bytes=39)
  11   10               SORT (GROUP BY) (Cost=353 Card=1 Bytes=107)
  12   11                 FILTER
  13   12                   HASH JOIN (Cost=351 Card=1 Bytes=107)
  14   13                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=81)

  15   13                     TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=11 Bytes=286)

  16   12                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

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

  21   19                 FILTER
  22   21                   NESTED LOOPS (Cost=353 Card=1 Bytes=268)
  23   22                     NESTED LOOPS (Cost=6 Card=1 Bytes=200)
  24   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  25   23                       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)

  26   22                     TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=347 Card=1 Bytes=68)

  27   21                   TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=100)





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


As I am looking at the plan I can find no comparision at all.Please help me ?
Regards

[Updated on: Wed, 11 June 2008 06:38]

Report message to a moderator

Re: Performance1 [message #327530 is a reply to message #326087] Mon, 16 June 2008 15:25 Go to previous message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
didn't see the query, but from stat, i was wondering why you have such high io for only one row, are you doing group?
387229 consistent gets 
306954 physical reads

for the gets#, i guess your query read most data from disk, then into buffer, then oracle read from buffer. of course, oracle will read data from buffer it it finds. that's why get# are high consistent gets is a little bigger than physical reads.
Both sort (memory & disk) are zero, so it shouldn't be sort area too small.

Report message to a moderator

Previous Topic: Performance improvement measures
Next Topic: Partitioning a table in Source
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 20:54:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.