Home » RDBMS Server » Performance Tuning » What the statistics in Explain plan suggests? (merged)
What the statistics in Explain plan suggests? (merged) [message #319893] Tue, 13 May 2008 06:21 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I have got the below explain plan statistics for the below query:
SELECT  
	COUNT(CASE WHEN(  A.TMS1> SYSTIMESTAMP - INTERVAL '30' MINUTE)  THEN 1  END),
	COUNT(CASE WHEN  A.TMS1>= SYSTIMESTAMP - INTERVAL '24' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
	
        COUNT(*) AS CNT 
FROM MQ A,CODE B
WHERE SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=680 Card=34430 Bytes
=4303750)

1 0 SORT (GROUP BY) (Cost=680 Card=34430 Bytes=4303750)
2 1 HASH JOIN (Cost=211 Card=34430 Bytes=4303750)
3 2 INDEX (FAST FULL SCAN) OF 'PK_MQ1' (UNIQUE) (
Cost=7 Card=2469 Bytes=34566)

4 2 MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
4)

5 4 TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
tes=528)

6 4 BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
7 6 TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
130 Bytes=13000)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
92 consistent gets
68 physical reads
0 redo size
50934 bytes sent via SQL*Net to client
1017 bytes received via SQL*Net from client
108 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1598 rows processed

And making some modification to the above query I got the following below statistics:

[ Modified last line in the above uqery: A.PROCESS_NME LIKE B.CODE_VALUE_TXT || '%' ]


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16913 Card=770744 By
tes=96343000)

1 0 SORT (GROUP BY) (Cost=16913 Card=770744 Bytes=96343000)
2 1 NESTED LOOPS (Cost=6438 Card=770744 Bytes=96343000)
3 2 MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
4)

4 3 TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
tes=528)

5 3 BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
6 5 TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
130 Bytes=13000)

7 2 INDEX (RANGE SCAN) OF 'PK_MQ1' (UNIQUE) (Cost
=1 Card=123 Bytes=1722)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7333 consistent gets
0 physical reads
0 redo size
50934 bytes sent via SQL*Net to client
1017 bytes received via SQL*Net from client
108 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1598 rows processed



What the statistics suggests? Where I can read more about all those parameters mentioned in statistics?

Need help!
Re: What the statistics in Explain plan suggests? [message #319895 is a reply to message #319893] Tue, 13 May 2008 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A non formatted explain plan is unreadable.

In addition, keep your lines in 80 characters width.

Regards
Michel
Re: What the statistics in Explain plan suggests? [message #319922 is a reply to message #319893] Tue, 13 May 2008 07:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
after keeping lines in 80 character width.

OK, sorry for this, I was thinking about your query, for explain plan it is hard (and not wise) to keep it in 80 characters.

It is hard to say as your "tables" seems to be views.

Regards
Michel

[Updated on: Tue, 13 May 2008 07:17]

Report message to a moderator

Re: What the statistics in Explain plan suggests? [message #319945 is a reply to message #319922] Tue, 13 May 2008 07:59 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
[mod-edit]

User requested his latest message be removed (possibly due to names of tables/indexes). I have removed the message. The original message is still here as it was not requested to be removed.
Re: What the statistics in Explain plan suggests? [message #319951 is a reply to message #319945] Tue, 13 May 2008 08:10 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thank you very much for removing the message.
Which one is feasible? [message #320099 is a reply to message #319893] Wed, 14 May 2008 01:42 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I have written a query and got the below statistics.



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=680 Card=34430 Bytes
          =4303750)

   1    0   SORT (GROUP BY) (Cost=680 Card=34430 Bytes=4303750)
   2    1     HASH JOIN (Cost=211 Card=34430 Bytes=4303750)
   3    2       INDEX (FAST FULL SCAN) OF 'PK_MQ1' (UNIQUE) (
          Cost=7 Card=2469 Bytes=34566)

   4    2       MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
          4)

   5    4         TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
          tes=528)

   6    4         BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
   7    6           TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
          130 Bytes=13000)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         92  consistent gets
         68  physical reads
          0  redo size
      50934  bytes sent via SQL*Net to client
       1017  bytes received via SQL*Net from client
        108  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1598  rows processed

after modifying the conditions in where clause I got the following:
Can anyone tell me what the statistics suggests? Or how can I understand which one is feasible?

	   
	   

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16913 Card=770744 By
          tes=96343000)

   1    0   SORT (GROUP BY) (Cost=16913 Card=770744 Bytes=96343000)
   2    1     NESTED LOOPS (Cost=6438 Card=770744 Bytes=96343000)
   3    2       MERGE JOIN (CARTESIAN) (Cost=194 Card=6244 Bytes=69308
          4)

   4    3         TABLE ACCESS (FULL) OF 'LOCATION' (Cost=2 Card=48 By
          tes=528)

   5    3         BUFFER (SORT) (Cost=192 Card=130 Bytes=13000)
   6    5           TABLE ACCESS (FULL) OF 'CODE' (Cost=4 Card=
          130 Bytes=13000)

   7    2       INDEX (RANGE SCAN) OF 'PK_MQ1' (UNIQUE) (Cost
          =1 Card=123 Bytes=1722)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7333  consistent gets
          0  physical reads
          0  redo size
      50934  bytes sent via SQL*Net to client
       1017  bytes received via SQL*Net from client
        108  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1598  rows processed
	   
Re: Which one is feasible? [message #320100 is a reply to message #320099] Wed, 14 May 2008 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost the same question.
Don't create a new topic for the same question, continue on the previous one.

Regards
Michel
Re: Which one is feasible? [message #320101 is a reply to message #320099] Wed, 14 May 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
[quote title=Michel Cadot wrote on Tue, 13 May 2008 14:11]
Quote:
It is hard to say as your "tables" seems to be views.

You asked to remove the same post that you repost now.
Can you explain us your behaviour?

Regards
Michel

Re: Which one is feasible? [message #320159 is a reply to message #320101] Wed, 14 May 2008 03:36 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
The reason for removing was that there was something wrong in my message (which I asked to remove)

Regards,
Veddeta
Re: What the statistics in Explain plan suggests? (merged) [message #320160 is a reply to message #319893] Wed, 14 May 2008 03:38 Go to previous message
VEDDETA
Messages: 54
Registered: May 2008
Member
To my understanding, the 1st one will be feasible as the cost is low..is it correct?
Need your help on this issue...
Previous Topic: optimization of a query
Next Topic: can any one help to tune the query
Goto Forum:
  


Current Time: Tue Dec 06 08:40:45 CST 2016

Total time taken to generate the page: 0.11195 seconds