help ! How can capture the SQL?

Hi ! help ! How can capture the SQL which using the sequential scans With SMI?? Now I know the sysmaster's table sysptprof include the profile of sequential scans, but no table contains the sql state with every time of sequential scans.

SHAN SALEON wrote: > Hi ! help ! How can capture the SQL which using the sequential scans With > SMI?? > Now I know the sysmaster's table sysptprof include the profile of sequential > scans, but no table contains the sql state with every time of sequential > scans. > > Version information would be helpful here. In IDS 11.10 this is trivial. On earlier releases, you have to catch the session that issued the sequential scan SQL before it runs another query (or at best before it frees the statement id for the offending query) since only active queries are available in the SMI tables. Art S. Kagel

Hello, For what it#s worth you can get the sqls from the syssqlstat table I believe it is. This includes all SQLs though not just those causing sequential scans. If you experiencing performance problems? What OS are you using? I can provide you with a couple of scripts that will capture the SQL for you in a useful format. About every 3 months I spent a day or so capturing SQL from the instance and then spend a bit of time analysing them. In all the places I have worked at the developers don't always tell the DBAs if they have installed some new software or not and occasionally some shocking code get in and messes things up. Regards Andy Grantham. > To: ids@iiug.org> From: shanshl@msn.com> Subject: help ! How can capture the SQL? [10427]> Date: Wed, 21 Nov 2007 06:57:46 -0500> > Hi ! help ! How can capture the SQL which using the sequential scans With > SMI?? > Now I know the sysmaster's table sysptprof include the profile of sequential > scans, but no table contains the sql state with every time of sequential > scans. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ The next generation of MSN Hotmail has arrived - Windows Live Hotmail http://www.newhotmail.co.uk

Thank you for upstairs ! My IDS version : 10 OS: HP-UNIX Andrew Grantham can you send me your scrips . my e-mail: shanshl@msn.com Thanks I can get the current sessions by smi, and can get the sql of current, but the sql not containts squentials informations. get from the sysptprof table only have the count of every table's sequential scans, I want capture the current sql which use sequential scans.

Shan, try running this: select tabname, seqscans, npused, seqscans*npused from sysptprof, syspthdr where sysptprof.partnum = syspthdr.partnum and ( seqscans * npused ) > 10000 Obviously the 10000 can be any value you like. It will tell you the names of the tables at least. You can then capture the SQLs or with some clever scripting you can get the sqls from the syssqlstat table or running on onstat -g stm and grepping out the table names. As for the script I will need to do that later as they are on a machine at home. Regards Andy Grantham. > To: ids@iiug.org> From: shanshl@msn.com> Subject: Re: help ! How can capture the SQL? [10432]> Date: Wed, 21 Nov 2007 07:41:43 -0500> > Thank you for upstairs ! > My IDS version : 10 > OS: HP-UNIX > > Andrew Grantham can you send me your scrips . my e-mail: shanshl@msn.com > Thanks > > I can get the current sessions by smi, and can get the sql of current, but the > sql > not containts squentials informations. > get from the sysptprof table only have the count of every table's sequential > scans, I want capture the current sql which use sequential scans. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ The next generation of MSN Hotmail has arrived - Windows Live Hotmail http://www.newhotmail.co.uk

thank you very much. you telled me the Problem-solving ideas, I'll try it later. I will go home, because our time is 22:00 pm, :-) thanks.

SHAN SALEON wrote: > Thank you for upstairs ! > My IDS version : 10 > OS: HP-UNIX > > Andrew Grantham can you send me your scrips . my e-mail: shanshl@msn.com > Thanks > The only way is to capture ALL of the SQL for the offending session and rerun the queries using SET EXPLAIN ON; Since you have a later release (10.00 or later) you can add the clause to not actually execute the query but only optimize and explain it (see the Guide to SQL Syntax manual for details). One option is to get one of the tools available that can monitor SQL activity for you and give you some help analyzing what's going on. Two excellent options are AGS's Server Studio with Sentinel. Another is CobraSonic's DbSonar. Both capture SQL in real-time and record it and other server state information into a data repository that you can mine later for information. Both will provide runtime stats on those pesky queries and help you track down which are running slowly which are executing sequential scans and help you determine why (is it a missing index? outdated stats? what?). Art S. Kagel > I can get the current sessions by smi, and can get the sql of current, but the > sql > not containts squentials informations. > get from the sysptprof table only have the count of every table's sequential > scans, I want capture the current sql which use sequential scans. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >