Re: performance questions and help!

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. Received on Sat Dec 06 2008 - 22:18:48 CST