Thanks for the help so far. I have sql developer and I click on explain and on autotrace and all I see is a big green dot. And of course oracle is doing maintenance on the help forums right now. Is it something else I should do to get the explain plan working?
On Wed, Mar 18, 2009 at 1:08 PM, Javier Montani <[email protected]> wrote: > 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 >> >> > > > > -- Anthony Smith "Having education and talent doesn't make you better than the world... it makes you responsible for it!" --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
