Huge use CPU of lab128.exe in AIX S.O

[ral281]

Hello:

I am load the last version of Lab128 and I see a huge consume about
20% of CPU.

I see the process with topas comand in AIX S.O

There are some tunning for this issue?

Is this normal ?

In advance thanks

Ricardo

rab...@vtr.net

[Sergei - Lab128]

Hi Ricardo,

No, this is not normal. With your help we can identify which query is
causing high CPU usage and either tune it, or reduce the frequency of
executions, or disable it. Lab128 can detect expensive queries,
including its own.

First, can you tell which version of Oracle server you use? RAC or non-
Rac? and other info, that you think may be relevant;

Second, please have Lab128 connected and running for few minutes, if
it have not been running. Then open SQL Explorer (Main menu | View |
SQL Explorer). Uncheck the "Exclude Lab128" box in this window. Select
those several minutes with the mouse in the upper graphical chart.
Then click on "CPU Time" header in the data table in the middle
section to sort by CPU time in descending order. Then find first
Lab128 from the top and provide the text of query. Also provide the
value in the "CPU Time, %" column, which is next from "CPU Time"
column. Then find second top Lab128 query and get same two things
about it and provide them too.

Thank you,
Sergei

> rabu...@vtr.net

[ral281]

Hello Sergei:

I mean al S.O level. With TOPAS I see one spid like 87662 and then,
when query the v$process I see program= lab128.exe . Whe I kill this
one, the use of CPU at S.O is fine.

In advance, thanks.
Best Regards Ricardo.

> > rabu...@vtr.net- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

[Sergei - Lab128]

Ricardo,

Was it just an isolated case when some query from Lab128 stuck and
then you killed the process/session and everything is OK since then.

Or it is consistent - when Lab128 runs you see the OS process taking
20% of CPU, and that OS process is running on behalf of Lab128
session? If this is the case, we need to find which query is causing
high CPU usage - you need to do things described in my previous post.

Regards,
Sergei

[ral281]

Hello Sergei:

Thanks for your inputs.

When I just start the Lab128 with double-click in the icon I can see
the high CPU usage from program lab128.exe.

Then, I go to AIX-IBM box and run TOPAS for find out wich PID is with
high use of CPU and then I run one query in BD:

SELECT
sid,p.spid,s.username,s.status,s.program,logon_time,s.schemaname,s.process,s.machine,s.terminal,s.command
FROM v$process p,v$session s
Where p.addr = S.paddr
and spid in(2404440,774450);

SID SPID USERNAME STATUS
PROGRAM LOGON_TIM
SCHEMANAME PROCESS
MACHINE
TERMINAL COMMAND
------------------------------------------------------
------------------------------ ----------
1564 2404440 SYSTEM INACTIVE lab128.exe 21-DEC-09
SYSTEM 176:3416 RIPLEY-MAIN\P-
RABUFONL P-
RABUFONL 0

1813 774450 SYSTEM INACTIVE lab128.exe 21-DEC-09
SYSTEM 176:952 RIPLEY-MAIN\P-RABUFONL P-
RABUFONL 0

Those SPID just startup LAb128 are lab128.exe program.

If I kill or just close de Lab128 all is fine, obvious.

The problem is with the EXE no with the sessions or querys with some
activity.

Or, at SO level I just see only one session(lab128.exe) for all the
session in Database ??

Why in another AIX-IBM box is different and no peack ?

Can I send you the archive with screeshots ??

In advance, thanks

Best Regards.

Ricardo.

> > > - Mostrar texto de la cita -- Hide quoted text -
>
> - Show quoted text -

[Sergei - Lab128]

Well, lab128.exe is running on your workstation in windows
environment. When it makes a connection, Oracle starts a session and a
server process, which is same as the OS process on AIX. You can find
session ID (SID) and OS process id (pid) using the query you pointed
out (from v$session and v$process). You can find the process in OS by
pid using "ps" command. One of the session attributes is the Program
Name; it is visible through PROGRAM column in v$session view. The
oracle client provides the program name automatically when connection
is made, so you see "lab128.exe" in this column. What is really
important: what Oracle server process is doing. The server process is
running queries submitted by the program connected to the server,
lab128 in our case. If no queries are submitted, the server process
should be idling and you should see about 0% CPU usage. You can make a
small experiment: you can pause Lab128 - it will keep connection (and
server/OS process) alive, but will not execute any query. To do that:
in the Main window right-click on DB Name in the upper right corner,
the select "Pause" in the popup menu. I expect that the OS process
will go idle. You can check that.

All queries submitted by Lab128 should be fast and efficient and
should not take much of CPU. It seems that one or two queries
submitted by Lab128 are not as efficient as they should be on this
server. That's why you see this process running at OS level. So we
need to find these queries and find out why they are not performing
well.

Sure, you can send me the snapshots: ser...@lab128.com

Regards,

[ral281]

Thanks Sergei .

I send you the screeshots and some coments. You are right when you say
Paused, the activity is low, but when Resume
CPU % consume is very high.

This issue is with the new version for Lab128 1.5.9.6. With another
versions like 1.5.9.4 the problem is low.

Please read your e-mail.

In advance, thanks

Best Regards

Ricardo.

[ral281]

Hello Sergei:

I found one query makes , perhaps, some high CPU or this query is
associated with PID of S.O in AIX box:

--lab128
select sid,inst_id,serial#,saddr,paddr,
sql_address sqladdr,sql_hash_value,sql_id,sql_child_number,
prev_sql_addr,prev_hash_value,prev_sql_id,prev_child_number,
username,command,status,server,osuser,machine,terminal,
program,module,action,type,logon_time,ownerid,schemaname,
seq#,
event "Event",p1text,p1,p2text,p2,p3text,p3,wait_time,
seconds_in_wait sw,state
from gv$session a

Regards,

Ricardo

> > - Show quoted text -- Hide quoted text -

[Sergei - Lab128]

Hi Ricardo,

Did you see this query in the SQL Explorer? If so, what was the number
in "CPU Time, %" column?

This query was not changed since Lab128 1.5.9.4, so we should make
sure it is the one. Can you also check the frequency of this query.
The easiest way to do that is to open Sessions window, and check in
the "Refresh rate, sec" box. By default it should be 7 seconds. You
can also try to set it to high value 60 seconds and see if CPU load
changes.

Are running RAC? What Oracle version of the server?
Thanks,
Sergei