Home » RDBMS Server » Performance Tuning » need help to understand this execution plan (oracle 10.2.0.5)
need help to understand this execution plan [message #543249] Mon, 13 February 2012 13:36 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
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 Go to previous messageGo to next message
n2fontenay
Messages: 15
Registered: December 2011
Location: San Diego, CA
Junior Member
Thanks. It does look a lot better.
Re: need help to understand this execution plan [message #543252 is a reply to message #543249] Mon, 13 February 2012 14:12 Go to previous messageGo to next message
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 |
-------------------------------------------------------------------------------------------------------
Re: need help to understand this execution plan [message #543266 is a reply to message #543252] Mon, 13 February 2012 16:25 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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.
Re: need help to understand this execution plan [message #543738 is a reply to message #543249] Thu, 16 February 2012 10:51 Go to previous message
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
Previous Topic: Help - dropping partitions(2 Merged)
Next Topic: application is slow? what to check immediately
Goto Forum:
  


Current Time: Fri Sep 19 05:39:53 CDT 2014

Total time taken to generate the page: 0.23447 seconds