OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Performance tuning

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance tuning [message #56716]

Fri, 18 April 2003 14:38

Victoria
Messages: 152
Registered: July 2002

Senior Member

Hi,
Please help me with the following...
1) qUERY EXECUTION PLAN WITHOUT HINT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=33)
1 0 TABLE ACCESS (FULL) OF 'TCY' (Cost=28 Card=1 Bytes=33)

Statistics
----------------------------------------------------------
0 recursive calls
11 db block gets
1158 consistent gets
635 physical reads
0 redo size
19146 bytes sent via SQL*Net to client
2160 bytes received via SQL*Net from client
30 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
425 rows processed
2).qUERY EXCUTION PLAN WITH /*+ INDEX(TABLE_NAME INDEX1) */
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=389 Card=1 Bytes=33)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TCY' (Cost=389 Card=1 By
tes=33)

2 1 INDEX (FULL SCAN) OF 'TCY_INDEX' (NON-UNIQUE) (Cost=385
Card=1)

Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
868 consistent gets
246 physical reads
16884 redo size
19355 bytes sent via SQL*Net to client
2160 bytes received via SQL*Net from client
30 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
425 rows processed

Can anyone tell me which one is the best among the above two and
how it(the best one) is decided?
Thanks
~V~

Report message to a moderator

Re: Performance tuning [message #56722 is a reply to message #56716]

Fri, 18 April 2003 17:22

Maaher
Messages: 7065
Registered: December 2001

Senior Member

IMHO the cost is mostly a fair indicator of the effort a query (an execution path) demands from the DB.
Tom Kyte uses a similar example to show the world that the CBO (cost based optimizer) deliberately chooses to go for a full table scan and that this is not by default a bad thing. I strongly advise to search his website with keywords from the explain plan because he sure gives a lot of useful info.

HTH,
MHE

Report message to a moderator

Re: Performance tuning [message #56728 is a reply to message #56716]

Sun, 20 April 2003 13:50

sai sreenivas jeedigunta
Messages: 370
Registered: November 2001

Senior Member

Looking at the Physical reads, I feel 2nd query is better as it directly affects I/O..

sai

Report message to a moderator

Previous Topic:	Nested for loop cursors or Joins????
Next Topic:	Insert scripts in flat file to be executed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:28:38 CDT 2024