You have to look for the FULL SCANS in tables and then create the corresponding indexes to avoid them. Be careful with this because the more indexes you have the worse the performance when the table grows.
2009/3/18 ddf <[email protected]> > > > > On Mar 18, 1:34 pm, Anthony Smith <[email protected]> wrote: > > 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!"- Hide quoted text - > > > > - Show quoted text - > > You need access to a table called PLAN_TABLE, and Oracle provides the > script to create it in $ORACLE_HOME/rdbms/admin/utlxplan.sql. You may > also need to create the PLUSTRACE role and grant it to the account > you're using to run these queries; that script is available as > $ORACLE_HOME/sqlplus/admin/plustrce.sql. At that point when using > SQL*Plus all you need do is: > > SQL> set autotrace on > SQL> > > and run your queries. The plan, and the per-query statistics, will be > presented after the last row has been retrieved and displayed. Note, > too, that EXPLAIN PLAN may return a different plan than autotrace, as > autotrace (as I've illustrated its activation) actually executes the > query and thus the plan is one the optimizer actually generated. > EXPLAIN PLAN may make assumptions which can cause it to return a > different plan because it doesn't actually go through the optimizer to > derive it. Yes, GUI tools are nice and pretty, but that doesn't > matter if you don't understand what Oracle is telling you in the plan > because you'll not be very successful in fixing the performance issue > you experience. Again, simply because you're using an index doesn't > mean it's the correct index to use, or that any index currently > created against that table is doing the job you think needs to be > done. I go back to my latitude and longitude example, with indexes on > both, where only one is used and creates a filter operation which is I/ > O intensive. The correct index in that situation would be a > concatenated index on latitude and longitude (which doesn't currently > exist in that example). The query plan shows an index being used, and > according to what I've read thus far that's about as tuned as some > posters think it needs to be, but changing the plan by creating a new > index may be exactly what the 'doctor ordered'. This is where the per- > query statistics from autotrace really help in evaluating the issue, > and those are displayed within SQL*Plus. Sometimes the old tools are > the best tools. > > It might be a good idea to purchase "Cost-Based Oracle Fundamentals", > by Jonathan Lewis, so you can understand the basics of the cost-based > optimizer and be better equipped to start your tuning adventure. > > > 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 -~----------~----~----~----~------~----~------~--~---
