1 Reply Latest reply: Dec 24, 2008 5:19 AM by mdecker RSS

    oem explain plan produced doesn't correspond to explain plan with tkprof

    256603
      Hello all,

      I am running OEM on a 10.2.0.3 database and I have a very slow query with cognos 8 BI on my data warehouse.

      I went to the dbconsole through OEM and connected to target database I went to look at the query execution and then got an explain plan.

      Then I did a trace file and ran it throught tkprof.

      When I look at both produced explain plan, I can see the tree looks the same exept the corresponding values. In OEM, it says I am going throug 18000 rows and in the tkprof result it says more like millions of records.

      As anybody had these kind of results?

      Shall I have more confidence in TKprof then OEM?

      It is very important to me since I am being chalanged by an external DBA.
        • 1. Re: oem explain plan produced doesn't correspond to explain plan with tkprof
          mdecker
          I would recommend you to get Christian Antogini´s book "Troublshooting Oracle Performance", (http://www.antognini.ch/top/) which explains everything you need to know when analyzing Oracle SQL Performance and Explain Plans.

          If you properly trace your SQL Statement, you will get "STAT" lines in the trace file. These stat lines show you the actual number of rows processed per row source operation. Explain plan per default does only show you the "estimated" row counts for the row source operations no matter whether you use "explain=" in the tkprof call or OEM to get the explain plan. Tkprof reads the STAT lines from the trace and outputs a section which is similar to an execution plan but contains the "real" number of rows.

          However, if you want to troubleshoot Oracle Performance, I would recommend you to run the statement with the hint /*+ GATHER_PLAN_STATISTICS */ or set statistics_level=ALL in your session (not database-wide!).

          If you do, you can get an excellent execution plan with DBMS_XPLAN.DISPLAY_CURSOR containing both estimated rows as well as actual rows combined with the "number of starts" for e.g. nested loop joins.

          Get the book, read it, and you will be able to discuss this issue with your external dba in a professional way. ;-)

          Regards,
          Martin
          www.ora-solutions.net