On Mar 18, 9:21 am, oracle <[email protected]> wrote:
> I have some views that I use. An example is below. is there anything
> that PL/SQL can do to make the performance better?
Explain what you mean by 'better'.
> The view below
> works fine for the way that I access it. But for another project, it
> now uses that same view and the way it accesses it, it is a little
> slower as it uses this view to create another view. Or is this more of
> a tuning thing?
>
PL/SQL is the procedural 'arm' of the SQL language in Oracle, it's not
usually considered a performance enhancer although it can produce
better performing code when used properly and in the areas for which
it was designed.
> Basically in my projects I am trying to use views vs. loading some
> data into my tables and then running a job to create or update other
> tables. Instead, the views replace the need for having the jobs.
>
A common usage.
> So I guess I am asking if pl/sql can replace the views
No.
> or make them
> faster.
>
No.
> select g.TERRITORY, coalesce(rev.TRANS_DT ,can.TRANS_DT )
> TRANS_DT,g.FISCAL_YEAR,g.FISCAL_QTR,
> coalesce(rev.revenue,can.revenue)revenue, g.goal from
> aff_rev_goal g,
> (select c.TERRITORY, c.TRANS_DT ,c.FISCAL_YEAR,c.FISCAL_QTR,
> coalesce(c.revenue,0) + coalesce(d.revenue,0) +
> coalesce(e.revenue,0) revenue
> from
> (select a.TERRITORY,TRANS_DT,r.FISCAL_YEAR,r.FISCAL_QTR, sum(revenue)
> revenue
> from AFF_TERR_ALIGNMENT a, aar023.quarterly_revenue r
> where a.ORIGIN_loc_cd=r.ORIGIN_loc_cd and a.REGION_CD != 'CAN'
> and a.FISCAL_HALF = GET_HALF_BY_QTR(r.FISCAL_QTR)
> and a.FISCAL_YEAR = r.FISCAL_YEAR
> group by a.TERRITORY, TRANS_DT, r.FISCAL_YEAR
> ,r.FISCAL_QTR)c,
> (select a.TERRITORY, TRANS_DT,r.FISCAL_YEAR, r.FISCAL_QTR, sum
> (revenue) revenue
> from AFF_KIAC_ACCT_ALIGNMENT a, quarterly_revenue r where
> r.KIAC_ACCT_NBR = a.KIAC_ACCT_NBR and a.FISCAL_HALF = GET_HALF_BY_QTR
> (r.FISCAL_QTR)
> and a.FISCAL_YEAR = r.FISCAL_YEAR and a.ORIGIN_LOC_CD=r.ORIGIN_LOC_CD
> group by a.TERRITORY,TRANS_DT,r.FISCAL_YEAR
> ,r.FISCAL_QTR)d ,
> (select r.TRANS_DT, r.TERRITORY, r.FISCAL_YEAR, r.FISCAL_QTR, revenue
> revenue from
> AFF_UNASGNED_QUARTERLY_REV r)e
> where
> c.FISCAL_YEAR = d.FISCAL_YEAR(+) and
> c.FISCAL_QTR = d.FISCAL_QTR(+) and
> c.TERRITORY = d.TERRITORY(+)
> and
> c.FISCAL_YEAR = e.FISCAL_YEAR(+) and
> c.FISCAL_QTR = e.FISCAL_QTR(+) and
> c.TERRITORY = e.TERRITORY(+)
> and
> c.TRANS_DT = d.TRANS_DT(+) and
> c.TRANS_DT = e.TRANS_DT(+)
> ) rev,
> (select '8-8-19-29-0-0-0' as
> territory,TRANS_DT,r.FISCAL_YEAR,r.FISCAL_QTR, sum(revenue) revenue
> from quarterly_revenue r where
> r.ORIGIN_loc_cd in (select a.ORIGIN_loc_cd from
> AFF_TERR_ALIGNMENT a where a.FISCAL_HALF = GET_HALF_BY_QTR
> (r.FISCAL_QTR) and
> a.FISCAL_YEAR = r.FISCAL_YEAR and a.REGION_CD='CAN')
> group by '0-0-0-0-0-0-0', TRANS_DT,r.FISCAL_YEAR ,r.FISCAL_QTR)can
> where
> g.FISCAL_YEAR = can.FISCAL_YEAR(+) and
> g.FISCAL_QTR = can.FISCAL_QTR(+) and
> g.TERRITORY = can.TERRITORY(+)
> and
> g.FISCAL_YEAR = rev.FISCAL_YEAR(+) and
> g.FISCAL_QTR = rev.FISCAL_QTR(+) and
> g.TERRITORY = rev.TERRITORY(+)
You'll need to use either EXPLAIN PLAN or autotrace to see what the
optimizer is doing with the above code. Once you know that you have
the beginnings of your tuning plan and you can embark upon a process
where performance improvement is the desired result. Also you'll need
to use either technique to evaluate how your queries use this view and
how the queries from this other project utilize it. As it stands at
the moment you have your gun, the ducks are available and in season
but you forgot to bring your ammunition. Using autotrace (my
preferred method) reports the execution plan plus the per-query
statistics such as block reads, consistent gets, disk sorts, memory
sorts and rows returned so you can (provided you understand how Oracle
accesses data) see, beyond the plan, what is happening at the I/O
level and can then determine if additional indexes are needed or if
two indexes can be combined into one to eliminate a filtering
operation. As an example let's say you have application data that
involved latitude and longitude, and both are always part of the WHERE
clause. Let's also say that latitude and longitude are indexed
independently. Oracle will, most often, access the data by latitude
and then need to filter on longitude, which can be a terribly resource-
intensive process. Creating one concatenated index, on latitude and
longitude, will cause Oracle to use that concatenated index for
access, reducing the data subject to a filtering operation by a
considerable amount, thus improving performance.
You have a way to go to get this view, and all of your views, to
perform better (presuming that is a possibility). You've been given
the starting tools to use to collect the necessary data; do that and
come back, and someone here will be able to assist you further.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
