| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> performance-problem with views
Hi!
I've a strange (for me at last) performance problem.
I've two very similar tables FORM_TROUBLETICKET_9 and FORM_SERVICETICKET_4.
Because of this similarity I have somtimes to query the data in one of
the two tables, somtimes I have to query the data in both tables. So I
use a "union all" view FORM_XTICKET.
The thing is, that the same query is don within one or two seconds if
performed on the table directly but lasts up to 10 minutes when
performed on the view.
I've done an explain plan for the query and was able to optimize it a
bit. But it till takes about 5 minutes.
Could you give me a hint where the performance is lost?
Here ist the select-statement:
SELECT *
FROM avw_stepinstance pi ,form_xticket xt
WHERE ((xt.oid in (select form from avw_forminstance where
process=pi.oid) AND
xt.ticket_begin >= TO_DATE('01-01-2005', 'DD-MM-YYYY
HH24:MI:SS') AND
(pi.status != 4) AND
((pi.id like '303')) AND
xt.ticket_state != 'Closed')) AND
(pi."TYPE"=22)
(Don't wonder about the brackets, there is a tool that acually generates
the statement...)
avw_stepinstance and avw_forminstance are about 3 times lage as
FORM_TROUBLETICKET_9 and FORM_SERVICETICKET_4 together.
PLAN_TABLE_OUTPUT
| Id | Operation | Name
| Rows | Bytes | Cost |
| 0 | SELECT STATEMENT |
| | | |
|* 1 | FILTER |
| | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | AVW_STEPINSTANCE
| | | |
| 3 | NESTED LOOPS |
| | | |
| 4 | VIEW | FORM_XTICKET
| | | |
| 5 | UNION-ALL |
| | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| FORM_TROUBLETICKET_9
| | | |
|* 7 | INDEX RANGE SCAN | FORM_TROUBLETICKET_9_IDX_BEGIN | | | | |* 8 | TABLE ACCESS BY INDEX ROWID| FORM_SERVICETICKET_4
| | | |
|* 9 | INDEX RANGE SCAN | FORM_SERVICETICKET_4_IDX_BEGIN | | | | |* 10 | INDEX RANGE SCAN | AVW_SI_TYPE
| | | |
| 11 | AND-EQUAL |
| | | |
|* 12 | INDEX RANGE SCAN | AVW_FINST_PROCESS
| | | |
|* 13 | INDEX RANGE SCAN | AVW_FINST_FORM
| | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter( EXISTS (SELECT 0 FROM "AVW_FORMINSTANCE" "AVW_FORMINSTANCE" WHERE "AVW_FORMINSTANC E"."FORM"=:B1 AND "AVW_FORMINSTANCE"."PROCESS"=:B2)) 2 - filter(UPPER("SYS_ALIAS_1"."ID") LIKE '303' AND
("SYS_ALIAS_1"."STATUS"<>4 OR "SYS_ALIAS_1
"."STATUS" IS NULL))
6 - filter("FORM_TROUBLETICKET_9"."TICKET_STATE"<>'Closed')
7 -
))
filter("FORM_TROUBLETICKET_9"."TICKET_BEGIN">=TO_DATE('01-01-2005','DD-MM-YYYY HH24:MI:SS'
))
8 - filter("FORM_SERVICETICKET_4"."TICKET_STATE"<>'Closed')
9 -
access("FORM_SERVICETICKET_4"."TICKET_BEGIN">=TO_DATE('01-01-2005','DD-MM-YYYY
HH24:MI:SS'
))
filter("FORM_SERVICETICKET_4"."TICKET_BEGIN">=TO_DATE('01-01-2005','DD-MM-YYYY HH24:MI:SS'
))
10 - access("SYS_ALIAS_1"."TYPE"=22)
12 - access("AVW_FORMINSTANCE"."PROCESS"=:B1)
13 - access("AVW_FORMINSTANCE"."FORM"=:B1)
Note: rule based optimization
Thanx for your help.
Grmlfz
Received on Fri Jan 20 2006 - 10:06:12 CST
 |
 |