OraFAQ Forum: Performance Tuning » need help to understand this execution plan

Home » RDBMS Server » Performance Tuning » need help to understand this execution plan (oracle 10.2.0.5)

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

need help to understand this execution plan [message #543249]

Mon, 13 February 2012 13:36

n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA

Junior Member

Hi,

I got this query:

SELECT
    c.id DTVContentId,
    c.Channel,
    c.SiteID,
    c.OutputURL,
    ea.name  EncryptionAppliance,
    k.Key1,
    k.Key2,
    k.KeyStartTime,
    k.KeyEndTime,
    k.Expired,
    k.EncryptionFlag,
    k.Imported,
    k.RemoteKey2EndTime
FROM   iptv.DTVContent c
JOIN   iptv.DTVKey k
ON     k.DTVContentId = c.Id
JOIN   iptv.ContentAppliance ca
ON     ca.ContentId = k.DTVContentId
JOIN   iptv.EncryptionAppliance ea
ON     ea.id = ca.EncryptionApplianceId
WHERE  k.Expired = 0
and    c.active = 1
and    c.channel is not null;

and then I got this execution plan:

Can someone help me understand where is the cartesian operation in that query, and why?

-----------------------------------------------------------------
| Id  | Operation                 | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                             |  2867 |   347K|    33   (7)| 00:00:01 |
|   1 |  NESTED LOOPS             |                             |  2867 |   347K|    33   (7)| 00:00:01 |
|*  2 |   HASH JOIN               |                             |  2000 |   228K|    32   (4)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL      | DTVCONTENT                  |  1999 | 75962 |     9   (0)| 00:00:01 |
|   4 |    MERGE JOIN CARTESIAN   |                             |  2000 |   154K|    22   (0)| 00:00:01 |
|   5 |     VIEW                  | index$_join$_006            |     1 |     9 |     3   (0)| 00:00:01 |
|*  6 |      HASH JOIN            |                             |       |       |            |          |
|   7 |       INDEX FAST FULL SCAN| IX_ENCRYPTIONAPPLIANCE_NAME |     1 |     9 |     1   (0)| 00:00:01 |
|   8 |       INDEX FAST FULL SCAN| PK_ENCRYPTIONAPPLIANCE      |     1 |     9 |     1   (0)| 00:00:01 |
|   9 |     BUFFER SORT           |                             |  2000 |   136K|    19   (0)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL    | DTVKEY                      |  2000 |   136K|    19   (0)| 00:00:01 |
|* 11 |   INDEX UNIQUE SCAN       | PK_CONTENTAPPLIANCE         |     1 |     7 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------

[Updated on: Mon, 13 February 2012 13:43]

Report message to a moderator

Re: need help to understand this execution plan [message #543250 is a reply to message #543249]

Mon, 13 February 2012 13:40

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 13 February 2012 13:40]

Report message to a moderator

Re: need help to understand this execution plan [message #543251 is a reply to message #543250]

Mon, 13 February 2012 13:43

n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA

Junior Member

Thanks. It does look a lot better.

Report message to a moderator

Re: need help to understand this execution plan [message #543252 is a reply to message #543249]

Mon, 13 February 2012 14:12

n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA

Junior Member

I've performed the same query with just a select * and I have a completely different execution plan (and no cartesian product).

I'm even more confused.

SELECT *
FROM   iptv.DTVContent c
JOIN   iptv.DTVKey k
ON     k.DTVContentId = c.Id
JOIN   iptv.ContentAppliance ca
ON     ca.ContentId = k.DTVContentId
JOIN   iptv.EncryptionAppliance ea
ON     ea.id = ca.EncryptionApplianceId
WHERE  k.Expired = 0
and    c.active = 1
and    c.channel is not null;
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |  2000 |   425K|    36  (12)| 00:00:01 |
|   1 |  NESTED LOOPS                |                        |  2000 |   425K|    36  (12)| 00:00:01 |
|*  2 |   HASH JOIN                  |                        |  2000 |   392K|    33   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | CONTENTAPPLIANCE       |  2000 | 16000 |     3   (0)| 00:00:01 |
|*  4 |    HASH JOIN                 |                        |  2000 |   376K|    29   (4)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL        | DTVKEY                 |  2000 |   156K|    19   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | DTVCONTENT             |  1999 |   220K|     9   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| ENCRYPTIONAPPLIANCE    |     1 |    17 |     1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN         | PK_ENCRYPTIONAPPLIANCE |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Report message to a moderator

Re: need help to understand this execution plan [message #543266 is a reply to message #543252]

Mon, 13 February 2012 16:25

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

The first query is joining two indexes: IX_ENCRYPTIONAPPLIANCE_NAME and PK_ENCRYPTIONAPPLIANCE. It thinks that join will give 1 row, so it then does a merge join cartesian between the result of that join and DTVKEY.
Merge Join cartesian isn't always a problem.

The 2nd query is different because the extra columns in the select list means it has to access all four tables where previously it could avoid hitting CONTENTAPPLIANCE and ENCRYPTIONAPPLIANCE as all the columns it needed were covered by indexes.

Report message to a moderator

Re: need help to understand this execution plan [message #543738 is a reply to message #543249]

Thu, 16 February 2012 10:51

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

You have missed the most important point in your plan which is the predicate section. Sometimes predicate section will enlighten you why oracle is not using an Index and why it has chosen a particular path.

Firstly I would check whether my stats are upto date (the most boring step).
Second, I would compare my row source estimates between the actual and the estimated. If they are way apart need to dig in further to find out why CBO cannot come up with the estimate and how can I help it to come up with it.

But either way compare the execution plan between different sql and also remember to post the predicate section of your output.

Thanks

Raj

Report message to a moderator

Previous Topic:	Help - dropping partitions(2 Merged)
Next Topic:	application is slow? what to check immediately

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 21:14:30 CDT 2024