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
|
|
|
Re: Interpreting "Row Source Operation" Output of TKPROF [message #358836 is a reply to message #358799] |
Wed, 12 November 2008 10:58 |
cookiemonster
Messages: 13961 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?
|
|
|
|
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: 13961 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.
|
|
|
|
|