IDS Forum

RE: RE: Seeing one oninit working hard while o....

Posted By: Knox, Ernest
Date: Sunday, 11 October 2009, at 7:42 p.m.

In Response To: RE: RE: Seeing one oninit working hard while o.... (Knox, Ernest)

Where is the capture parameter? I really only see the EXPLAIN_STAT
enable. Is that it? If I turn it off, will that possibly improve
anything?

# SQL Statement Cache Configuration Parameters
###################################################################
# STMT_CACHE - Controls SQL statement caching. Acceptable
# values are:
# 0 Disabled
# 1 Enabled at the session level
# 2 All statements are cached
# STMT_CACHE_HITS - The number of times an SQL statement must be
# executed before becoming fully cached.
# 0 indicates that all statements are
# fully cached the first time.
# STMT_CACHE_SIZE - The size, in KB, of the SQL statement cache
# STMT_CACHE_NOLIMIT - Controls additional memory consumption.
# Acceptable values are:
# 0 Limit memory to STMT_CACHE_SIZE
# 1 Obtain as much memory, temporarily, as needed
# STMT_CACHE_NUMPOOL - The number of pools for the SQL statement
# cache. Acceptable value is a positive
# integer between 1 and 256, inclusive.
###################################################################

STMT_CACHE 0
STMT_CACHE_HITS 0
STMT_CACHE_SIZE 512
STMT_CACHE_NOLIMIT 0
STMT_CACHE_NUMPOOL 1

###################################################################
# Operating System Session-Related Configuration Parameters
###################################################################
# USEOSTIME - The precision of SQL statement timing.
# Accepted values are 0 (precision to seconds)
# and 1 (precision to subseconds). Subsecond
# precision can degrade performance.
# STACKSIZE - The size, in KB, for a session stack
# ALLOW_NEWLINE - Controls whether embedded new line characters
# in string literals are allowed in SQL
# statements. Acceptable values are 1 (allowed)
# and any number other than 1 (not allowed).
# USELASTCOMMITTED - Controls the committed read isolation level.
# Acceptable values are:
# - NONE Waits on a lock
# - DIRTY READ Uses the last committed value in
# place of a dirty read
# - COMMITTED READ Uses the last committed value
# in place of a committed read
# - ALL Uses the last committed value in place
# of all isolation levels that support the last
# committed option
###################################################################

USEOSTIME 0
STACKSIZE 64
ALLOW_NEWLINE 0
USELASTCOMMITTED NONE

###################################################################
# Optimizer Configuration Parameters
###################################################################
# OPTCOMPIND - Controls how the optimizer determines the best
# query path. Acceptable values are:
# 0 Nested loop joins are preferred
# 1 If isolation level is repeatable read,
# works the same as 0, otherwise works same as 2
# 2 Optimizer decisions are based on cost only
# DIRECTIVES - Specifies whether optimizer directives are
# enabled (1) or disabled (0). Default is 1.
# EXT_DIRECTIVES - Controls the use of external SQL directives.
# Acceptable values are:
# 0 Disabled
# 1 Enabled if the IFX_EXTDIRECTIVES environment
# variable is enabled
# 2 Enabled even if the IFX_EXTDIRECTIVES
# environment is not set
# OPT_GOAL - Controls how the optimizer should optimize for
# fastest retrieval. Acceptable values are:
# -1 All rows in a query
# 0 The first rows in a query
# IFX_FOLDVIEW - Enables (1) or disables (0) folding views that
# have multiple tables or a UNION ALL clause.
# Disabled by default.
# AUTO_REPREPARE - Enables (1) or disables (0) automatically
# re-optimizing stored procedures and re-preparing
# prepared statements when tables that are referenced
# by them change. Minimizes the occurrence of the
# -710 error.
####################################################################

OPTCOMPIND 2
DIRECTIVES 1
EXT_DIRECTIVES 0
OPT_GOAL -1
IFX_FOLDVIEW 0
AUTO_REPREPARE 1

###################################################################
# SQL Tracing and EXPLAIN Plan Configuration Parameters
###################################################################
# EXPLAIN_STAT - Enables (1) or disables (0) including the Query
# Statistics section in the EXPLAIN output file
# SQLTRACE - Configures SQL tracing. The format is:
# SQLTRACE level=(low|med|high),ntraces=<#>,size=<#>,
# mode=(global|user)
###################################################################

EXPLAIN_STAT 1
#SQLTRACE level=low,ntraces=1000,size=2,mode=global

Thanks,

*******************************************************************

Ernie Knox

IT Database Administrator Specialist

Sears Holdings

3333 Beverly Rd., B4-266A

Hoffman Estates, IL. 60179

Office: (847) 286-5735

Email: Ernest.Knox@searshc.com

Blackberry: 2244650553@messaging.sprintpcs.com

Page via Skytel: 2244650553@sprint.skytel.com

Informix or MySQL Primary: 9110210@skytel.com

Informix or MySQL Secondary: 7276872@skytel.com

" Yes we can make a Change! "

" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!
"

" Lets not forget - GO Pistons and Red Wings! "

GSU

*******************************************************************

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Knox, Ernest
Sent: Sunday, October 11, 2009 6:27 PM
To: ids@iiug.org
Subject: RE: RE: Seeing one oninit working hard while o.... [17477]

We didn't plan on turning that on. I will check, but could that take up
resources and cause issues?

Thanks,

*******************************************************************

Ernie Knox

IT Database Administrator Specialist

Sears Holdings

3333 Beverly Rd., B4-266A

Hoffman Estates, IL. 60179

Office: (847) 286-5735

Email: Ernest.Knox@searshc.com

Blackberry: 2244650553@messaging.sprintpcs.com

Page via Skytel: 2244650553@sprint.skytel.com

Informix or MySQL Primary: 9110210@skytel.com

Informix or MySQL Secondary: 7276872@skytel.com

" Yes we can make a Change! "

" It's always a great day to watch Sports - GO LIONS, TIGERS, and BEARS!

"

" Lets not forget - GO Pistons and Red Wings! "

GSU

*******************************************************************

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Sunday, October 11, 2009 6:24 PM
To: ids@iiug.org
Subject: Re: RE: Seeing one oninit working hard while o.... [17476]

He probably has SQL capture running.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions

and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any
entity
with which I am affiliated nor those of the entities themselves.

On Fri, Oct 9, 2009 at 10:35 AM, JACQUES RENAUT <jrenaut@us.ibm.com>
wrote:

> One thing that strikes me as very very odd is on what you are calling
the
> remote server, the msc vp has used ~ 90% of the total cpu time for
that
> instance. That seems very abnormal. Normally, the highest usages vps
would
> be
> cpu vps. The msc vp is normally used to authenticate incoming
connections.
> When you just posed the ps output, was the 1 oninit you were concerned

with
> how much cpu time it was getting the msc vp (you can compare the ps
output
> obviously with the pid from the onstat -g glo)? If so, there would
appear
> to
> be some sort of issue here. You might want to try and get some stack
traces
> off the msc vp to see what it seems to be spending it's time doing and

then
> see if that leads you to some configuration issue or the like with how

you
> are
> authenticating users on that machine.
>
> Jacques Renaut
> IBM Informix Advanced Support
> APD team
>
> You wrote:
> Remote Server:
>
> Remote:/usr/informix>onstat -g glo
>
> IBM Informix Dynamic Server Version 11.50.UC3 -- On-Line -- Up
> 03:06:40 -- 1242192 Kbytes
>
> MT global info:
> sessions threads vps lngspins
> 116 185 16 2911
>
> sched calls thread switches yield 0 yield n yield
> forever
> total: 36843457 12620339 24225482 558900 2206927
> per sec: 1196 488 708 52 147
>
> Virtual processor summary:
> class vps usercpu syscpu total
> cpu 3 427.30 210.58 637.88
> aio 4 5.86 12.28 18.14
> shm 1 0.03 0.07 0.10
> lio 1 0.36 0.83 1.19
> pio 1 0.35 0.84 1.19
> adm 1 0.41 0.28 0.69
> soc 4 24.11 60.51 84.62
> msc 1 7832.11 280.25 8112.36
> total 16 8290.53 565.64 8856.17
>
> Individual virtual processors:
> vp pid class usercpu syscpu total Thread Eff
> 1 663652 cpu 239.61 123.81 363.42 363.42 100%
> 2 1380526 adm 0.41 0.28 0.69 0.00 0%
> 3 938224 cpu 168.60 79.53 248.13 283.67 87%
> 4 1478902 lio 0.36 0.83 1.19 1.19 100%
> 5 1286202 pio 0.35 0.84 1.19 1.19 100%
> 6 913522 aio 3.81 9.32 13.13 562.13 2%
> 7 340018 msc 7832.11 280.25 8112.36 10577.03 76%
> 8 311550 aio 1.34 1.52 2.86 79.80 3%
> 9 430252 aio 0.40 0.75 1.15 9.49 12%
> 10 1155232 aio 0.31 0.69 1.00 4.69 21%
> 11 1032332 soc 5.27 12.14 17.41 NA NA
> 12 1347764 soc 5.67 13.77 19.44 NA NA
> 13 712894 soc 4.88 11.96 16.84 NA NA
> 14 1019938 soc 8.29 22.64 30.93 NA NA
> 15 1261766 shm 0.03 0.07 0.10 NA NA
> 16 1544216 cpu 19.09 7.24 26.33 26.33 100%
>
> tot 8290.53 565.64 8856.17
>
>
>
>
************************************************************************

*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174bdcc809678c0475b11b37

************************************************************************

*******
Forum Note: Use "Reply" to post a response in the discussion forum.

************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Seeing one oninit working hard while others are no
  Knox, Ernest -- Thursday, 8 October 2009, at 3:55 p.m.
  • Re: Seeing one oninit working hard while others ar
    Madison Pruet -- Thursday, 8 October 2009, at 4:01 p.m.
    • RE: Seeing one oninit working hard while other....
      Knox, Ernest -- Thursday, 8 October 2009, at 4:53 p.m.
      • RE: Seeing one oninit working hard while other....
        Madison Pruet -- Friday, 9 October 2009, at 9:31 a.m.
      • Re: RE: Seeing one oninit working hard while other
        JACQUES RENAUT -- Friday, 9 October 2009, at 10:35 a.m.
        • Re: RE: Seeing one oninit working hard while o....
          Art Kagel -- Sunday, 11 October 2009, at 7:23 p.m.
          • RE: RE: Seeing one oninit working hard while o....
            Knox, Ernest -- Sunday, 11 October 2009, at 7:26 p.m.
            • RE: RE: Seeing one oninit working hard while o....
              Knox, Ernest -- Sunday, 11 October 2009, at 7:42 p.m.
              • Re: RE: Seeing one oninit working hard while o....
                Art Kagel -- Monday, 12 October 2009, at 11:40 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.