IDS Forum

Re: What are the Performance Hits using SQLTRACE?

Posted By: John Miller iii
Date: Friday, 22 March 2013, at 2:09 a.m.

In Response To: Re: What are the Performance Hits using SQLTRACE? (George_Palmer@aotx.uscourts.gov)

George:

Thanks you for providing this information. I will just make a one
additional comments on
SQLTRACE. In version 11.70 we realized that tracing the database
scheduler threads
by default is probably unwanted by our customers so these threads have
tracing turned
off by default in 11.70.

John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 03/21/2013 07:13:28 AM:

> From: "George_Palmer@aotx.uscourts.gov" <George_Palmer@aotx.uscourts.gov>
> To: ids@iiug.org,
> Date: 03/21/2013 07:18 AM
> Subject: Re: What are the Performance Hits using SQLTRACE? [29853]
> Sent by: ids-bounces@iiug.org
>
> I use SQLTRACE on production RHEL 5.8 system running IDS 11.50FC8
> Frequently.
>
> I have a couple hundred, similar installation that sometme need to be
> searched for slow SQL
>
> I have some advice, which may sound strange, but it may help you.
>
> 1) Never set up the SQLTRACE through the ONCONFIG setting. This will
start
> a monitoring job in the sysadmin DB that will capture the SQLTRACE output

> to a table and if you need to capture large amounts of data this will
fill
> space in sysmaster ( or sysadmin, I forget ) Always start SQLTRACE
through
> the sysadmin task stored procedure or admin stored procedure.
>
> 2) The number of SQL to keep and the size will be allocated out of
SHMVIRT
> so start small and use the task/admin functions to size it to what you
can
> hold.
>
> 3) When you first start it you will probably not have a good idea of how
> many SQL are run in a minute, or hour, use the sysmaster.syssqltrace_info

> table information sql_seen and duration, and starttime to figure out the
> size that will accommodate your needs.
>
> 4) If you are looking for just long running single SQL statements you may

> wish to size your SQLTRACE to hold only a minute of SQL then dump the SQL

> with long runtimes once a minutes through cron. ( The dump from scanning
> the couple hundred thousand SQL in SHMVIRT will show up as long running,
> may want to filter out on sql_database or filter results based on
> "sqltrace" stuff )
>
> 5) The amount of resources for running the SQLTRACE ( in my experience )
is
> nominal I start it and stop on production machines without anyone in the
> user base even noticing. Machine load is unchanged and percentage of CPU
is
> not noticeably changed. The tracing is _very_ light.
>
> For what I have I am running DB with a total memory footprint of about
> 5.5G, Disk of about 200G and 6 CPU VP. The DB is not the only thing on
the
> box and they are not large boxes. mostly the difference in CPU
utilization
> is only 1 percentage point ( but CPU is not an issue on these machines )
>
> For the traces I run, I have run up to 200,000 traces of size 2K or 8K in

> low and that gives me the info I need for one to two minutes ( Lots more
> statements in an OLTP system then you might believe on the onset.)
>
> These are just suggestions. Post back what works for you after you figure

> it out.
>
> George.
>
> From: "DAN MUELLER" <ddmueller@intercall.com>
> To: ids@iiug.org
> Date: 03/21/2013 08:43 AM
> Subject: What are the Performance Hits using SQLTRACE? [29850]
> Sent by: ids-bounces@iiug.org
>
> AIX 6
> IDS11.50.FC7W3
>
> I need to find my query bad guys after a new software release and want to

> use
> SQLTRACE. I have done my homework and have played with it in test but I
do
> not
> have a box that will get hit anywhere near as hard as my production box
> will.
>
> Can nayone give me a good ballpark figure as to how much setting SQLTRACE

> on
> to low will affect my performance? ALso what would be the ramifications
of
> upping it to med or high?
>
> TIA,
> Dan
>
>
>
*******************************************************************************

>
> 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

• What are the Performance Hits using SQLTRACE?
  DAN MUELLER -- Thursday, 21 March 2013, at 9:42 a.m.
  • Re: What are the Performance Hits using SQLTRACE?
    Art Kagel -- Thursday, 21 March 2013, at 9:52 a.m.
  • Re: What are the Performance Hits using SQLTRACE?
    George_Palmer@aotx.uscourts.gov -- Thursday, 21 March 2013, at 10:13 a.m.
    • Re: What are the Performance Hits using SQLTRACE?
      John Miller iii -- Friday, 22 March 2013, at 2:09 a.m.
      • Re: What are the Performance Hits using SQLTRACE?
        DAN MUELLER -- Monday, 25 March 2013, at 9:58 a.m.
  • Re: What are the Performance Hits using SQLTRACE?
    KERNOAL STEPHENS -- Friday, 22 March 2013, at 8:12 a.m.
  • Re: What are the Performance Hits using SQLTRACE?
    DAN MUELLER -- Friday, 22 March 2013, at 8:22 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.