Professional Web Applications Themes

which sql statement causes sequential scans? - Informix

Hi! I've an application that stores a lot of data in my database-tables (Informix DS 9.30). As there are some problems with the performance, I yze, among other things, the parameter seqscans (given by onstat -p) and which tables causes them (script: "select tabname, seqscan, nrows from sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and a.partnum=b.partnum order by seqscans desc"). How can I retrieve information, which specific SQL-Statement cause the sequential scan on a table? Any help and suggestions would be appreciated. Thanks in advance! Thomas...

  1. #1

    Default which sql statement causes sequential scans?

    Hi!

    I've an application that stores a lot of data in my database-tables
    (Informix DS 9.30). As there are some problems with the performance, I
    yze, among other things, the parameter seqscans (given by onstat -p) and
    which tables causes them (script: "select tabname, seqscan, nrows from
    sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and
    a.partnum=b.partnum order by seqscans desc").

    How can I retrieve information, which specific SQL-Statement cause the
    sequential scan on a table?

    Any help and suggestions would be appreciated.
    Thanks in advance!
    Thomas



    Thomas Guest

  2. #2

    Default Re: which sql statement causes sequential scans?

    Thomas wrote:
    > Hi!
    >
    > I've an application that stores a lot of data in my database-tables
    > (Informix DS 9.30). As there are some problems with the performance, I
    > yze, among other things, the parameter seqscans (given by onstat -p) and
    > which tables causes them (script: "select tabname, seqscan, nrows from
    > sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and
    > a.partnum=b.partnum order by seqscans desc").
    >
    > How can I retrieve information, which specific SQL-Statement cause the
    > sequential scan on a table?
    >
    > Any help and suggestions would be appreciated.
    > Thanks in advance!
    > Thomas
    >
    >
    >
    You should start by identifying the tables which get sequential scans.
    The only way to identify the query would be to set explain on for all sessions... It's not easy.
    Don't forget that sequential scans are perfectly normal in small tables.

    onstat -g ppf

    can be used to see which tables are being sequential scan. You'll have to match the partnum with systables.
    Don't forget that onstat will give you the partnum in hexadecimal form.

    Regards.

    Fernando Nunes Guest

  3. #3

    Default Re: which sql statement causes sequential scans?

    Thomas schrieb:
    > Hi!
    >
    > I've an application that stores a lot of data in my database-tables
    > (Informix DS 9.30). As there are some problems with the performance, I
    > yze, among other things, the parameter seqscans (given by onstat -p) and
    > which tables causes them (script: "select tabname, seqscan, nrows from
    > sysptprof a, sysptnhdr b where seqscans > 100 and dbsname = 'DB' and
    > a.partnum=b.partnum order by seqscans desc").
    >
    > How can I retrieve information, which specific SQL-Statement cause the
    > sequential scan on a table?
    >
    > Any help and suggestions would be appreciated.
    > Thanks in advance!
    > Thomas
    >
    >
    >
    Maybe this is a little bit off topic but yesterday I found out that when using
    nvl() in a query which runs in a foreach loop causes excessive allocation of
    memory in my 4GL programs (RDS 7.30UC6 with IDS 7.30UC10 on Unixware 7.1.1).
    For each loop new memory is allocated which is freed only at the end of the
    program. After eleminating all nvl() functions a program which had run for more
    than 24 hours now runs in about half an hour.

    --
    Roland Wintgen (Systemadministrator)

    EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG
    Trompeterallee 244-246, D-41189 Moenchengladbach
    Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90
    [url]www.evg.de[/url] [email]rwevg.de[/email]

    Roland Wintgen Guest

Similar Threads

  1. Blotchy Color Scans
    By Ray Woodcock in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: April 2nd, 09:11 PM
  2. DataBinding to a Sequential File
    By Tom McLaughlin in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 16th, 01:24 AM
  3. sequential functions?
    By originalknappster webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 5th, 09:17 PM
  4. hi res scans
    By Rockie Moch in forum Adobe Photoshop Elements
    Replies: 3
    Last Post: July 16th, 09:53 PM
  5. Profiler Question (Scans)
    By Amos Soma in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 14th, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139