OraFAQ Forum: Performance Tuning » Interpreting "Row Source Operation" Output of TKPROF

Home » RDBMS Server » Performance Tuning » Interpreting "Row Source Operation" Output of TKPROF (Oracle 9iR2)

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

Interpreting "Row Source Operation" Output of TKPROF [message #358799]

Wed, 12 November 2008 09:19

tsteinmaurer
Messages: 12
Registered: October 2008

Junior Member

Hello,

can someone help me to interpret the following "row source operation" output of TKPROF, possibly giving a hint on how to improve the query (although generated via an O/R mapper).

Query:

SELECT "tbl0"."KONTAKTAUFNAHME_ART" "f1", "tbl0"."VORGAENGER_BBZ_ID" "f2", 
  "tbl0"."LOCK_AM" "f3", "tbl0"."FACHBEREICH_ART" "f4", "tbl0"."KON_ID" "f5", 
  "tbl0"."DATUMSWUNSCHVON" "f6", "tbl0"."ANTRAG_ART" "f7", 
  "tbl0"."ZUSTAENDIGER_BBZ_ID" "f8", "tbl0"."BETREUUNG_ART" "f9", 
  "tbl0"."SYNC_AM" "f10", "tbl0"."LOCK_VON" "f11", 
  "tbl0"."AUSFUEHRENDEDIENSTSTELLE" "f12", "tbl0"."GEAENDERT_VON" "f13", 
  "tbl0"."AKTUELLE_BAZ_ID" "f14", "tbl0"."DRINGEND" "f15", 
  "tbl0"."ANTRAGANNAHMEDATUM" "f16", "tbl0"."LOCK_ART" "f17", "tbl0"."ASNR" 
  "f18", "tbl0"."AKT_ID" "f19", "tbl0"."ERSTELLT_VON" "f20", 
  "tbl0"."ERSTELLT_AM" "f21", "tbl0"."BESCHREIBUNG" "f22", 
  "tbl0"."GEAENDERT_AM" "f23", "tbl0"."DATUMSWUNSCHBIS" "f24", 
  "tbl0"."POSTEINGANGSNUMMER" "f25" 
FROM
 "AK" "tbl0" LEFT OUTER JOIN "AR" "tbl1" ON ("tbl0"."ASNR")=("tbl1"."ASNR") 
  LEFT OUTER JOIN "BZ" "tbl2" ON ("tbl0"."AKTUELLE_BAZ_ID")=("tbl2"."BAZ_ID") 
  LEFT OUTER JOIN "BZU" "tbl3" ON ("tbl2"."BZS_ID")=("tbl3"."BZS_ID") WHERE 
  ((("tbl0"."ZUSTAENDIGER_BBZ_ID") IN ((:p0))) AND 
  ((((1-abs((("tbl3"."IST_ABGESCHLOSSEN"))-((:p1)))))=(:p2)))) AND 
  ((("tbl0"."ANTRAG_ART")=(:p3)) AND (NOT (("tbl2"."BZS_ID")=(:p4)))) ORDER 
  BY "tbl1"."NAME" ASC, "tbl0"."ANTRAGANNAHMEDATUM" ASC

Execution details:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      3.15      61.97      79590      80758          0         116
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      3.20      62.02      79590      80758          0         116

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 62  (EPOS)

Rows     Row Source Operation
-------  ---------------------------------------------------
    116  SORT ORDER BY 
    116   NESTED LOOPS OUTER 
    116    HASH JOIN  
    327     TABLE ACCESS BY INDEX ROWID AKTIVITAET 
    356      INDEX RANGE SCAN AKT_BBZ_ZUSTAENDIGER_FK_I (object id 30672)
 449440     TABLE ACCESS BY INDEX ROWID BAM_AKTIVITAET_ZUSTAND 
 449473      NESTED LOOPS  
     32       TABLE ACCESS FULL BAM_ZUSTAND 
 449440       INDEX RANGE SCAN BAZ_BZS_FK_I (object id 30719)
    116    VIEW PUSHED PREDICATE  
    116     NESTED LOOPS OUTER 
    116      TABLE ACCESS BY INDEX ROWID ARBEITSSTAETTEN 
    116       INDEX UNIQUE SCAN ARB_PK (object id 30404)
      0      INDEX UNIQUE SCAN ARBEITSSTAETTEN_LOCK_PK (object id 31332)

There is quite some work involved to fetch 116 records.

Any hints?

Thanks,
Thomas

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #358836 is a reply to message #358799]

Wed, 12 November 2008 10:58

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

Senior Member

Well considering the table names in your select statement don't appear to match the table names in your tkprof output I don't really see how we can give you any meaningfull help.
Only thing I can possibly suggest without a select and tkprof that actually match up is do you really need to use outer joins?

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #358894 is a reply to message #358836]

Wed, 12 November 2008 20:26

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i19260

Ross Leishman

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #358976 is a reply to message #358799]

Thu, 13 November 2008 02:23

tsteinmaurer
Messages: 12
Registered: October 2008

Junior Member

Hi,

yeah, right. I'm sorry. Synonyms are used by the SELECT statement.

AK => AKTIVITAET
AR => ARBEITSSTAETTEN
BZ => BAM_AKTIVITAET_ZUSTAND
BZU => BAM_ZUSTAND

The LEFT OUTER JOINs are generated by the O/R Mapper. Need to check if the O/R Mapper would be clever enough to use an INNER JOIN in case the foreign key field is declared as NOT NULL.

I've followed Ross' link, although I'm still not sure how the output of TKPROF can be interpreted in my example. I understand that parsing and executing the statement was a no-brainer, but fetching the result set is quite some work.

Am I right, that with

 449440       INDEX RANGE SCAN BAZ_BZS_FK_I (object id 30719)

449440 rows have been accessed via the index BAZ_BZS_FK_I? If so, I "guess":

NOT (("tbl2"."BZS_ID")=(:p4))

is the culprit in that case?

There are about 500.000 records in BAM_AKTIVITAET_ZUSTAND, aka BZ (using tbl2 as table alias)

Thanks for any hints!

Thomas

[Updated on: Thu, 13 November 2008 02:24]

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #359004 is a reply to message #358799]

Thu, 13 November 2008 05:05

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

Senior Member

Isn't the index BAZ_BZS_FK_I on BAM_ZUSTAND (tbl13)?

That aside, yes the NOT isn't exactly going to help.
Thing is, because you're using outer joins it's almost certainly evaluating the NOT clause before it evaluates the joins between the tables.
If it did that the other way round it'd almost certainly read a lot less rows from BAM_AKTIVITAET_ZUSTAND and BAM_ZUSTAND.
You really need to see if those outer joins can be made inner joins.

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #359022 is a reply to message #358799]

Thu, 13 November 2008 06:05

tsteinmaurer
Messages: 12
Registered: October 2008

Junior Member

Hi,

no, the index BAZ_BZS_FK_I is on BAM_AKTIVITAET_ZUSTAND(BZS_ID).

Are you aware of an article which discusses the optimizer evaluation order? Your example with OUTER JOINs and NOT would be one candidate to check out in poor statements. Some kind of list of "don't do that" bullets would be very helpful. Wink

Thanks a lot for your input. Much appreciated.

Regards,
Thomas

Report message to a moderator

Re: Interpreting "Row Source Operation" Output of TKPROF [message #359142 is a reply to message #359022]

Thu, 13 November 2008 19:24

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Heres an article on Explain Plan: http://www.orafaq.com/node/1420

And another on tuning high volume SQL: http://www.orafaq.com/node/1981

Since you want to access such a large proportion of your table, you want to avoid indexes. Make sure you have fresh statistics gathered with DBMS_STATS.GATHER_TABLE_STATS(), or try using some FULL or NO_INDEX hints.

Ross Leishman

Report message to a moderator

Previous Topic:	High Logical Reads
Next Topic:	package performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 02:41:09 CST 2024