Re: performance questions and help!

On Dec 6, 8:18 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Dec 6, 9:08 pm, scorpio1..._at_yahoo.com wrote:
>
>
>
>
>
> > Hi
> > I’m kind of new in oracle administration, take the performance class,
> > and try to learn and understanding the concepts. I came up with some
> > questions. If you can just give me a hint or just help me to find out
> > where should I go to get my answers.
> > Here they are:
>
> > 1.      A user complains that a part of their application always runs
> > slowly.  You determine that it is a single SQL statement that is slow,
> > not the database or whole application. What suggestions do you make to
> > your user for how to identify how the database is processing the SQL
> > statement?
>
> > 2.      Your database is performing a significant amount of reading and
> > writing to the temporary tablespace.  What steps can you take to
> > reduce the sorting?
>
> > 3.      You are seeing a significant amount of duplicate SQL statements in
> > the library cache that are not being shared.  What steps can you take
> > to increase statement reuse?
>
> > Thanks for the helps.
>
> Are there correct answers to these questions, or are they meant to
> help you think about performance issues?
>
> For question #1: how was it determined that the user's performance
> problem was not an application issue, where the application repeatedly
> sends a single SQL statement over a high latency network connection
> each time with different bind variable values, rather than using a
> different SQL statement which accomplishes the same with a single
> parse, execution, and fetch (possibly fetching 100 or more rows in a
> single fetch call).  How do we know that it is not a network issue, or
> a compatibility issue with other software installed on the client or
> server (Mr. Virus and Ms. Worm)?  How was it pinpointed that the
> problem was a single SQL statement?  If I made it to this point, I
> probably would have already used a 10046 trace at level 12, which
> would output bind variables, wait events, and STAT lines containing
> the actual execution statistics.  I would then likely run a 10053
> trace at level 1 during a hard parse of the SQL statement to determine
> why the specific execution plan was selected.  A DBMS Xplan showing
> allstats last might be helpful between the 10046 and 10053 traces just
> to get a feel for the differences in estimated cardinality and actual
> rows returned, as well as the access predicates used at the various
> stages of the plan.
>
> For question #2: but what if the SORTS (DISK) statistic in the V
> $SYSSTAT performance view shows that there were 0 disk sorts?  What if
> the significant writing is caused by hash joins spilling to the
> temporary tablespace?  What if changes to SORT_AREA_SIZE have little
> to no effect on this particular database instance, which happens to be
> running Oracle 11.1.0.7?  It would seem that there would be a way to
> potentially force Oracle to change the execution plan, or possibly
> there would be another way to write the SQL statement which is less
> resource intensive.
>
> For question #3: but what if the SQL statement is using bind variables
> and it is listed multiple times in V$SQL, each time with the same
> hash_value, but with a different child_number, and V$SQL_SHARED_CURSOR
> indicates OPTIMIZER_MISMATCH, BIND_MISMATCH, OPTIMIZER_MODE_MISMATCH,
> ROLL_INVALID_MISMATCH or something else?  What if the same SQL
> statement is listed multiple times in V$SQL with a different hash
> value?  Programmers really should be careful to make certain that they
> use consistent formatting of SQL statements.
>
> Warning about the above: the above, while potential answers to the
> questions, are definitely not the answers that your instructors expect
> to see.  The above is intended to help you think about performance
> issues, and they include clues to the simple answers that your
> instructors likely expect.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

thank you so much for the help.
i realy like to understand the topic and concepts but i can't find the good place to look and all i have to do is to search and aske.
any way thank you very much. Received on Sat Dec 06 2008 - 22:45:40 CST