I would recommend you to use SqlDeveloper which has a built in explain plan creation that will allow you to understand what's going on. This can come in handy as it will tell you if oracle is using an index or not. Here is a link to the website:
http://www.oracle.com/technology/software/products/sql/index.html 2009/3/18 ddf <[email protected]> > > > > 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 -~----------~----~----~----~------~----~------~--~---
