IDS Forum

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

Posted By: Art Kagel
Date: Monday, 12 October 2009, at 11:40 a.m.

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

No the parameter is SQLTRACE which appears to be off, at least the config
line you posted is commented out:

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

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 Sun, Oct 11, 2009 at 7:42 PM, Knox, Ernest <Ernest.Knox@searshc.com>wrote:

> 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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174ff094f1805e0475bebe07

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.