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
-~----------~----~----~----~------~----~------~--~---
