| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
Cary,
I have bought your excellent book “Optimizing Oracle Performance” at the
time of Paris Open World last year.
I read it twice and trying to hold it near for references. Even now it is
in the range of my vision.
Thank you for sharing your experience with us Oracle users!
But ;) there some situations there the Method R is difficult to use for performance diagnostic. In case we have some process that running to slow from business perspective, we can use described method with big success.
Lets imagine the Business saying to me: “Well, at the middle of the working day activity A have no appropriate response time, but out of business ours we have no problems with mentioned activity”. I have made TRC for this activity at the problem time. You probably know that I can see from trace file. There is CPU time starvation, because of the other processes in the running queue.
What we can do in such situation? I imagine 3 possible solutions (2 for hardware vendors, 1 for Oracle peoples DBA/Dev/Consultants)
Third one
(1 - add more CPUs, 2 – change CPU to modern ones, second can be more
popular because of Oracle licensing ;)
is to find TOP CPU consumers and try to optimise those.
The question is: What is the most effective way to find TOP consumers? Root of my initial question is what is the best way (BUFFER_GETS or CPU_TIME) to accomplish this task.
You know peoples often use statspack as well, as your web site to diagnose system overall performance. If bottleneck is CPU, then advice is to find TOP SQL from BUFFER_GETS perspective. My point is: may be now the better measure is CPU_TIME? I show in previous posting situation then CPU_TIME figure is 10 times accurate then BUFFER_GETS. Can you show me contrariwise situation?
Best regards,
Jurijs
PS In the most live situation the Method R is the best way to work, but there are exceptions. What is the best way to fight exceptions?
On 19.06.2004 18:32:39 oracle-l-bounce wrote:
>Jurijs,
>
>What you're saying is one of the key reasons that I pay attention almost
>always to trace data and almost never to V$ data.
>
>The problem with paying attention to sort counts and things like that =
>(even
>LIO counts!) is that some sorts are cheap, and some are really =
>expensive.
>You cannot tell how long they take by counting how many times they =
>happen.
>However, from your trace data, it's pretty simple to see when c has a =
>value
>that's larger than is normally explained by your cr+cu value, and you =
>can
>corroborate the time consumption by noticing SORT row source operations =
>in
>your STAT lines. In 9.2.0.2 and beyond, you can even see elapsed time
>consumption per row source operation in your STAT output (but watch for =
>bug
>3009359).
>
>With V$ data, you have to be lucky to find the problem, because of the =
>way
>the information gets aggregated before you can even collect it. For =
>example,
>it's entirely possible for a workload A with sorts=3D1 to take more time
=
>than
>a workload B with sorts=3D1003. All it takes is for the sort of A to be =
>1004
>times more expensive than the average sort duration of B. It is MUCH =
>more
>difficult to tell whether this is happening by looking at your V$ data =
>(if
>it's even possible at all)...
>
>The big problem with Statspack or ANY other tool that relies upon V$ =
>data is
>that, because of the aggregation done inside the Oracle kernel, it =
>presents
>very little solid evidence about response time. What you by looking at =
>event
>counts and aggregations of response time consumption qualifies =
>technically
>as only circumstantial evidence about the response time of an individual
>user action under inspection.
>
>It is also very simple with trace data to find the "time spent =
>preempted"
>problem that Dan brought up. It is considerably more difficult to do =
>with V$
>data. I'm not sure how you'd possibly go about it, actually, because =
>there's
>no place in the V$ data where you can pick up statistics about =
>individual
>executions of dbcalls (parse, exec, fetch, etc.).
>
>* There are certain problems (a lot of them in my experience) that you =
>will
>never be able to solve reliably with Statspack or ANY other tool that's
>based upon V$ data. *
>
>More generally, the problem is not V$ data in particular, it's ANY
>performance diagnostic data that is aggregated before you can collect =
>it. If
>you're interested in this phenomenon, there is a lot more detail present
=
>in
>the book "Optimizing Oracle Performance"--especially chapters 1 and 8.
>
>
>Cary Millsap
>Hotsos Enterprises, Ltd.
>http://www.hotsos.com
>* Nullius in verba *
>
>Upcoming events:
>- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 =
>Boston
>- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
>- Hotsos Symposium 2005: March 6-10 Dallas
>- Visit www.hotsos.com for schedule details...
>
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org =
>[mailto:oracle-l-bounce_at_freelists.org]
>On Behalf Of J.Velikanovs_at_alise.lv
>Sent: Saturday, June 19, 2004 8:01 AM
>To: oracle-l_at_freelists.org
>Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
>Subject: Re: The best CPU usage measurement in Oracle: BUFFER_GETS or
>CPU_TIME?
>
>Thank you all for responses.
>I totally agree with Daniel regarding "CPU starvation" issues. But if we
=
>
>have one we cant see it ether from BUFFER_GETS or CPU_TIME any way ;)
>
>Lets imagine we have no one. And we would like find TOP SQL on which=20
>spending our time we will get most.
>
>As Jonathan Lewis note there are can be situations then some activates=20
>takes CPU resources, but doesn't reflect in the logical read statistics.
=
>
>As mentioned Jonathan latch contention can be the issue or buffer is=20
>pinned.
>
>But I would like to pay your attention to activities which, by my =
>option,=20
>takes place more often then others CPU consuming activates and can takes
=
>
>much significant CPU resources.
>
>It is !SORTING!
>
>I have gotten results bellow:
>1. Activity=20
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>PL/SQL procedure successfully completed.
>
>vvvvvvvvvvvvvvvvv
>Elapsed: 00:00:01.35
>^^^^^^^^^^^^^^^^^^
>NAME VALUE
>----------------------------------------------------------------=20
>----------
>vvvvvvvvvvvvvvvvv
>session logical reads 23002
>CPU used by this session 137
>^^^^^^^^^^^^^^^^^^
>
>2. Activity=20
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>PL/SQL procedure successfully completed.
>
>vvvvvvvvvvvvvvvvv
>Elapsed: 00:00:14.22
>^^^^^^^^^^^^^^^^^^
>
>NAME VALUE
>----------------------------------------------------------------=20
>----------
>vvvvvvvvvvvvvvvvv
>session logical reads 23006
>CPU used by this session 1422
>^^^^^^^^^^^^^^^^^^
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>As you can see both activities have taken equal amount of "logical =
>reads",=20
>but CPU consuming for second one is 10 times bigger.
>The key answer why I have gotten such unproportional results is SORTING.
>For fists activity
>sorts (memory) 1
>For second
>sorts (memory) 1001
>
>So. I would say, it is better from this perspective to take a look first
=
>
>on CPU utilization not on BUFFER_GETS, or if you want on both. But not=20
>just on BUFFER_GETS as in statspack.
>
>Please correct me if I am wrong.
>
>Jurijs
>9268222
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>http://otn.oracle.com/ocm/jvelikanovs.html
>
>PS Activities code
>
>1.
>declare
>vn number;
>begin
>for f in 1..1000 loop
>select count(*) into vn from (select * from testsort);
>end loop; end;
>/
>
>2.
>declare=20
>vn number;
>begin
>for f in 1..1000 loop
>select count(*) into vn from (select * from testsort order by 1);
>end loop; end;
>/
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sat Jun 19 2004 - 15:49:25 CDT
 |
 |