Loading ...
Sorry, an error occurred while loading the content.
 

Clearing DB/OS Cache completely + See Plan before Query execution

Expand Messages
  • Tom Conlon
    Hi, Whilst testing some queries is there a way to clear the DB (and perhaps OS) cache *completely*. I ve tried disconnecting from the database and restarting
    Message 1 of 13 , Jan 31, 2007
      Hi,

      Whilst testing some queries is there a way to clear the DB (and
      perhaps OS) cache *completely*.

      I've tried disconnecting from the database and restarting the service,
      but the first 'cold' run of certain queries still run quick - unless
      I completely reboot the machine.

      Also, is there a way of seeing the plan to be used *without* actually
      going onto to run the query against a large table?

      Thanks,
      Tom
    • Steve Wiser
      If you are running classic on linux maybe you can kill the lock manager process as well as the other firebird processes. Just a guess though, I am not sure if
      Message 2 of 13 , Jan 31, 2007
        If you are running classic on linux maybe you can kill the lock manager
        process as well as the other firebird processes. Just a guess though, I
        am not sure if it will work...

        -steve

        Tom Conlon wrote:
        >
        > Hi,
        >
        > Whilst testing some queries is there a way to clear the DB (and
        > perhaps OS) cache *completely*.
        >
        > I've tried disconnecting from the database and restarting the service,
        > but the first 'cold' run of certain queries still run quick - unless
        > I completely reboot the machine.
        >
        > Also, is there a way of seeing the plan to be used *without* actually
        > going onto to run the query against a large table?
        >
        > Thanks,
        > Tom
        >
        >
      • Vlad Horsun
        ... This will clear DB cache, but not an OS cache ... This is not necessary ... This is because of OS cache ... Just prepare it and request a plan Regards,
        Message 3 of 13 , Jan 31, 2007
          > Whilst testing some queries is there a way to clear the DB (and
          > perhaps OS) cache *completely*.
          >
          > I've tried disconnecting from the database

          This will clear DB cache, but not an OS cache

          > and restarting the service,

          This is not necessary

          > but the first 'cold' run of certain queries still run quick - unless
          > I completely reboot the machine.

          This is because of OS cache

          > Also, is there a way of seeing the plan to be used *without* actually
          > going onto to run the query against a large table?

          Just prepare it and request a plan

          Regards,
          Vlad
        • Ann W. Harrison
          ... I m afraid that a reboot is the only sure way to clear out the OS file system cache. ... In ISQL, there s SET PLANONLY ... which prepares the query without
          Message 4 of 13 , Jan 31, 2007
            Tom Conlon wrote:
            >
            > Whilst testing some queries is there a way to clear the DB (and
            > perhaps OS) cache *completely*.

            I'm afraid that a reboot is the only sure way to clear out the OS
            file system cache.
            >
            > Also, is there a way of seeing the plan to be used *without* actually
            > going onto to run the query against a large table?
            >
            In ISQL, there's SET PLANONLY ... which prepares the query without
            executing it.

            Regards,


            Ann
          • geo.mastique@laposte.net
            do you now a udf function i can declare in firebird 1.5 ? i would like to find some data by searching on a date field format. i would like to extract all data
            Message 5 of 13 , Jan 31, 2007
              do you now a udf function i can declare in firebird 1.5 ?

              i would like to find some data by searching on a date field format.

              i would like to extract all data for a periode,
              for example a periode from 12 july to 24 september.
              of course i want the query to return data for all years.

              i can do it by shaking month + day function,
              but it would be more difficult to do.

              Thanks,
              Julien
            • Adam
              ... Hello Julien, This is not really a job for a UDF. It is like using a screw driver to hit a nail into a wall - it can probably be done but this is not what
              Message 6 of 13 , Jan 31, 2007
                --- In firebird-support@yahoogroups.com, geo.mastique@... wrote:
                >
                > do you now a udf function i can declare in firebird 1.5 ?
                >
                > i would like to find some data by searching on a date field format.
                >
                > i would like to extract all data for a periode,
                > for example a periode from 12 july to 24 september.
                > of course i want the query to return data for all years.
                >
                > i can do it by shaking month + day function,
                > but it would be more difficult to do.
                >

                Hello Julien,

                This is not really a job for a UDF. It is like using a screw driver to
                hit a nail into a wall - it can probably be done but this is not what
                UDF's do.

                UDFs are good for conversions etc. For example, Celcius to Farenheit
                or Absolute value or even metaphone. I pass in something, and it
                returns something based on my input.

                Depending on exactly what you want, a stored procedure could be used
                or even a single query.

                To collect data from all years, it is going to have to look at every
                record in your table, which is unecessarily expensive.

                I suppose you could create a UDF to convert a timestamp to a code
                representing the month and day

                For example, let Jan be 10, Feb be 11, ... Dec be 22. You could then
                use an integer to represent the time, eg 1712 would represent July 12.

                You would then just query

                select *
                from mytable
                where TimestampToMonthDay(SomeTimestampField) between 1712 and 1924

                Adam
              • Ivan Prenosil
                ... Look at builtin function EXTRACT(YEARDAY FROM yourfield) Do you require the select to use index ? Ivan
                Message 7 of 13 , Jan 31, 2007
                  > do you now a udf function i can declare in firebird 1.5 ?
                  >
                  > i would like to find some data by searching on a date field format.
                  >
                  > i would like to extract all data for a periode,
                  > for example a periode from 12 july to 24 september.
                  > of course i want the query to return data for all years.
                  >
                  > i can do it by shaking month + day function,
                  > but it would be more difficult to do.

                  Look at builtin function
                  EXTRACT(YEARDAY FROM yourfield)

                  Do you require the select to use index ?

                  Ivan
                • Adam
                  ... YEARDAY for a specific date will be different during leap years from March onwards. Adam
                  Message 8 of 13 , Jan 31, 2007
                    --- In firebird-support@yahoogroups.com, "Ivan Prenosil"
                    <Ivan.Prenosil@...> wrote:
                    >
                    > > do you now a udf function i can declare in firebird 1.5 ?
                    > >
                    > > i would like to find some data by searching on a date field format.
                    > >
                    > > i would like to extract all data for a periode,
                    > > for example a periode from 12 july to 24 september.
                    > > of course i want the query to return data for all years.
                    > >
                    > > i can do it by shaking month + day function,
                    > > but it would be more difficult to do.
                    >
                    > Look at builtin function
                    > EXTRACT(YEARDAY FROM yourfield)
                    >
                    > Do you require the select to use index ?

                    YEARDAY for a specific date will be different during leap years from
                    March onwards.

                    Adam
                  • Adam
                    Correction to my prior post, you will need to add 10 to the Day number as well or you will end up with strange results for the first 9 days of the month. Also,
                    Message 9 of 13 , Jan 31, 2007
                      Correction to my prior post, you will need to add 10 to the Day number
                      as well or you will end up with strange results for the first 9 days
                      of the month.

                      Also, although YEARDAY may not be appropriate because of leap years,
                      it gave me an idea on how you can do it.

                      select *
                      from MyTable
                      where Cast(Cast(Extract(Month from <timestamp field>)+10 as Char(2))
                      || Cast(Extract(Day from <timestamp field>)+10 as char(2)) as Integer)
                      between :StartDateFormat and :EndDateFormat.

                      The date format you would need to use is

                      Month+10 together with Day+10

                      For example, to get all records between 3 Feb and 18 Jun:

                      select *
                      from MyTable
                      where Cast(Cast(Extract(Month from <timestamp field>)+10 as Char(2))
                      || Cast(Extract(Day from <timestamp field>)+10 as char(2)) as Integer)
                      between 1213 and 1628

                      Of course this will hit need to read every record in MyTable, which
                      may not be pretty.

                      Adam
                    • Ivan Prenosil
                      ... what about WHERE SUBSTRING( FROM 6 for 5) BETWEEN 02-03 AND 06-18 Ivan
                      Message 10 of 13 , Jan 31, 2007
                        > For example, to get all records between 3 Feb and 18 Jun:

                        what about

                        WHERE SUBSTRING(<timestamp field> FROM 6 for 5) BETWEEN '02-03' AND '06-18'

                        Ivan
                      • Adam
                        ... 06-18 ... Clever. Adam
                        Message 11 of 13 , Jan 31, 2007
                          --- In firebird-support@yahoogroups.com, "Ivan Prenosil"
                          <Ivan.Prenosil@...> wrote:
                          >
                          > > For example, to get all records between 3 Feb and 18 Jun:
                          >
                          > what about
                          >
                          > WHERE SUBSTRING(<timestamp field> FROM 6 for 5) BETWEEN '02-03' AND
                          '06-18'
                          >

                          Clever.

                          Adam
                        • geo.mastique@laposte.net
                          thanks adam, as you said, YEARDAY for a specific date will be different during leap years, so i can not use it. i thought that sql extract ( day or month or
                          Message 12 of 13 , Feb 1, 2007
                            thanks adam,

                            as you said, YEARDAY for a specific date will be different during leap
                            years,
                            so i can not use it.

                            i thought that sql extract ( day or month or year) were not in firebird
                            because i list all fonction of ... interbase6
                            and not of firebird !
                            in interbase no extract but extrac exist in firebird...

                            so i am just going to make a procedure for a periode because there are 3
                            options :
                            1) from 2 feb to 24 feb, so one month only :
                            where month= feb and days between 2 and 24

                            2) from 20 july to 19 august, so 2 month :
                            where (jmonth = july and day>=20) or (month = august and day <=19)

                            3) from 14 march to 2 oct, so more then 2 month.
                            a syntaxe for first month : where (jmonth = march and day>=14)
                            + a loop procedure to get "full month" : or month in ( april, may, jun,
                            july, august,sept)
                            + a syntaxe for last month: or (month = oct and day <= 2)

                            --------------------------------------

                            This is not really a job for a UDF. It is like using a screw driver to
                            hit a nail into a wall - it can probably be done but this is not what
                            UDF's do.

                            UDFs are good for conversions etc. For example, Celcius to Farenheit
                            or Absolute value or even metaphone. I pass in something, and it
                            returns something based on my input.

                            Depending on exactly what you want, a stored procedure could be used
                            or even a single query.
                          • Ivan Prenosil
                            ... In fact it is in Interbase 6, but it is not described in Language reference but in Release notes (ask Borland why). Ivan
                            Message 13 of 13 , Feb 1, 2007
                              > i thought that sql extract ( day or month or year) were not in firebird
                              > because i list all fonction of ... interbase6
                              > and not of firebird !
                              > in interbase no extract but extrac exist in firebird...

                              In fact it is in Interbase 6, but it is not described in Language reference
                              but in Release notes (ask Borland why).

                              Ivan
                            Your message has been successfully submitted and would be delivered to recipients shortly.