Informix

 View Only
  • 1.  How many sql statements

    Posted Tue September 27, 2022 10:31 AM
    Hello 
    In order to activate SQLTrace, wanted to have an idea on how much sql statements are run during a day?
    is there a system table that record this stat ? or is there another way (Informix 14.1)

    thanks in advance

    ------------------------------
    John Smith
    ------------------------------


  • 2.  RE: How many sql statements

    IBM Champion
    Posted Tue September 27, 2022 11:12 AM
    Edited by Hrvoje Zokovic Tue September 27, 2022 11:12 AM
    You can turn it on with default settings and at the end of the day you will see how many statements went through the buffer
    HTH
    Hrvoje

    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 3.  RE: How many sql statements

    Posted Wed September 28, 2022 03:58 AM

    Hi John,

    To my knowledge, there is no system table storing this kind of information until you activate SQLTRACE.

    When you activate SQLTRACE:
    - the sysmaster:syssqltrace_info will contain this statistics in the sqlseen column corresponding to the number of SQL items traced since start or resizing of your SQLTrace buffer.
    - the onstat -g his will give another information, the duration of buffer in second, for example, if you specifiy 10 000 for your number of traces to SQLtrace, you can get an idea of how long the 10 000 SQL queries will be stored in the SQLTrace buffer with the duration of buffer.
    For example, imagine the duration of SQLTrace buffer is 600 seconds for 10 000 traces, then you can deduce that after one hour you will have traced about 60 000 queries (but you will store only 10 000 maximum traces at a time t in the sqltrace buffer and then you must think to save the old traces).
    Of course, this is just an estimate, because this duration of the SQLTrace buffer will vary during the day depending on user activity.

    Regards,

    -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile    : 33 (0) 6 78 81 09 33 Fax       : 33 (0) 1 39 12 18 18
    Le 27/09/2022 à 16:31, John Smith via IBM Community a écrit :
    010001837f5c36e2-1e76e2c6-ba16-403a-88e2-7299414f055f-000000@email.amazonses.com">
    Hello  In order to activate SQLTrace, wanted to have an idea on how much sql statements are run during a day? is there a system table that record...
    IBM Community

    Informix

    Post New Message
    How many sql statements
    Reply to Group Reply to Sender
    John Smith
    Sep 27, 2022 10:31 AM
    John Smith
    Hello 
    In order to activate SQLTrace, wanted to have an idea on how much sql statements are run during a day?
    is there a system table that record this stat ? or is there another way (Informix 14.1)

    thanks in advance

    ------------------------------
    John Smith
    ------------------------------
      Reply to Group Online   View Thread   Recommend   Forward  



     
    You are subscribed to "Informix" as franck.thomas@consult-ix.fr. To change your subscriptions, go to My Subscriptions. To unsubscribe from this community discussion, go to Unsubscribe.





  • 4.  RE: How many sql statements

    IBM Champion
    Posted Wed September 28, 2022 08:53 AM
    Just turn SQLTRACE on with the defaults of 1000 2K buffers trace level on MEDIUM in global mode:

    SQLTRACE level=med,ntraces=1000,size=2,mode=global

    Then you can select the row with the highest value for sql_id from sysmaster:syssqltrace to get a baseline query number (sql_id), then select again after the passage of time for which you want to capture all or most of the SQL in the buffers to get the current sql_id. The difference will be the number of SQL statements added to the trace buffers over that period of time. Then you can reconfigure SQLTRACE even on the fly with the SQL API, or just configure it in the ONCONFIG file for the next restart. 

    So, let's say you want to poll SQLTRACE data every 10 minutes, so do this during a peak time:

    echo 'SELECT max(sql_id) FROM syssqltrace;' | dbaccess sysmaster -
    sleep 660  ## doing 11 minutes to allow for a busier period
    echo 'SELECT max(sql_id) FROM syssqltrace;' | dbaccess sysmaster -

    Then set up SQLTRACE with ntraces equal to the difference.

    Art


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------