Re: performance-problem with views

grmlfz wrote:
> 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 -
> access("FORM_TROUBLETICKET_9"."TICKET_BEGIN">=TO_DATE('01-01-2005','DD-MM-YYYY
> HH24:MI:SS'
> ))
>
> 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

Well there are a couple of things that jump out at me consider the following:

1. You have created a cartesian join between avw_stepinstance pi and form_xticket xt. There is nothing in the WHERE clause that defines the relationship between records in one table and records in the other.
2. TO_DATE('01-01-2005', 'DD-MM-YYYY HH24:MI:SS') You have no hours, minutes, or seconds. How about: TO_DATE('01-JAN-2005')?
3. OID is a reserved word. SELECT keyword FROM v$reserved_words WHERE keyword LIKE 'OI%';

    Rename the column.

4. ((pi.id like '303'))
This is not a proper use of LIKE. Either use wild card characters such as _ and % or use an equals sign '='.

5. (pi."TYPE"=22)
Bet rid of the double quotes. They inconsistent with the rest of your syntax.

6. Why RULE BASED OPTIMIZATION? What version of Oracle?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)