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

RE: [magicu-l] RE: About Hint option in the Table Properties

Expand Messages
  • Ian Ledzion
    In Oracle there is one hint which has very clear uses: /*+ FIRST_ROWS */. This instructs Oracle to return the first part of a query s resultset. On large
    Message 1 of 14 , Aug 1, 2003
    • 0 Attachment
      In Oracle there is one hint which has very clear uses: /*+ FIRST_ROWS
      */. This instructs Oracle to return the first part of a query's
      resultset. On large tables which have to be accessed in online tasks
      load times for the task are massively improved.

      Apart from that I have never managed to second guess the Oracle
      optimiser's index choices. Not once.


      -----Original Message-----
      From: Brookes, Alan [mailto:alan.j.brookes@...]
      Sent: 01 August 2003 08:35
      To: 'magicu-l@yahoogroups.com'
      Subject: [magicu-l] RE: About Hint option in the Table Properties


      Lakshman,

      This feature allows you to pass a hint to the SQL optimiser when
      accessing the table.

      Although the hint will force the optimiser to use (e.g.) a specific
      index, there is never a guarantee that you will improve performance.

      You may be lucky, but the optimiser uses dozens of ways to calculate the
      cost of a query, and it will not be very often that you come up with a
      better plan than it does!

      Best left alone, IMHO.

      Regards,

      Alan


      -----Original Message-----
      From: enext [mailto:support@...]
      Sent: Friday, August 01, 2003 6:54 AM
      To: magicu-l@yahoogroups.com
      Subject: About Hint option in the Table Properties


      Dear all,

      I would like to know where exactly the hint option can able to use it,
      which is available at table properties in SQL tab.

      Does it give any additional feature or any optimization.

      Regards,
      Lakshman


      --------------------------------------------------------------
      ETC AUTOMATION SYSTEMS, MUMBAI, INDIA



      Your use of Yahoo! Groups is subject to
      http://docs.yahoo.com/info/terms/
    • Steve Worthington
      Unless you are a SQL expert, i.e. DBA with a lot of experience NEVER hint! ... From: enext To:
      Message 2 of 14 , Aug 1, 2003
      • 0 Attachment
        Unless you are a SQL expert, i.e. DBA with a lot of experience NEVER hint!
        ----- Original Message -----
        From: "enext" <support@...>
        To: <magicu-l@yahoogroups.com>
        Sent: Friday, August 01, 2003 1:54 AM
        Subject: [magicu-l] About Hint option in the Table Properties


        > Dear all,
        >
        > I would like to know where exactly the hint option can able to use it,
        > which is available at table properties in SQL tab.
        >
        > Does it give any additional feature or any optimization.
        >
        > Regards,
        > Lakshman
        >
        >
        > --------------------------------------------------------------
        > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
        >
        >
        >
        >
        >
        > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
        >
        >
        >
      • Steve Worthington
        will only give performance improvement for on-line when the query RESULT is large (indexed query on large table may only return small result set). If the query
        Message 3 of 14 , Aug 1, 2003
        • 0 Attachment
          will only give performance improvement for on-line when the query RESULT is
          large (indexed query on large table may only return small result set). If
          the query result is small it will give adverse performance results! However
          in batch tasks this will kill your performance.
          ----- Original Message -----
          From: "Ian Ledzion" <ian_ledzion@...>
          To: <magicu-l@yahoogroups.com>
          Sent: Friday, August 01, 2003 3:01 AM
          Subject: RE: [magicu-l] RE: About Hint option in the Table Properties


          > In Oracle there is one hint which has very clear uses: /*+ FIRST_ROWS
          > */. This instructs Oracle to return the first part of a query's
          > resultset. On large tables which have to be accessed in online tasks
          > load times for the task are massively improved.
          >
          > Apart from that I have never managed to second guess the Oracle
          > optimiser's index choices. Not once.
          >
          >
          > -----Original Message-----
          > From: Brookes, Alan [mailto:alan.j.brookes@...]
          > Sent: 01 August 2003 08:35
          > To: 'magicu-l@yahoogroups.com'
          > Subject: [magicu-l] RE: About Hint option in the Table Properties
          >
          >
          > Lakshman,
          >
          > This feature allows you to pass a hint to the SQL optimiser when
          > accessing the table.
          >
          > Although the hint will force the optimiser to use (e.g.) a specific
          > index, there is never a guarantee that you will improve performance.
          >
          > You may be lucky, but the optimiser uses dozens of ways to calculate the
          > cost of a query, and it will not be very often that you come up with a
          > better plan than it does!
          >
          > Best left alone, IMHO.
          >
          > Regards,
          >
          > Alan
          >
          >
          > -----Original Message-----
          > From: enext [mailto:support@...]
          > Sent: Friday, August 01, 2003 6:54 AM
          > To: magicu-l@yahoogroups.com
          > Subject: About Hint option in the Table Properties
          >
          >
          > Dear all,
          >
          > I would like to know where exactly the hint option can able to use it,
          > which is available at table properties in SQL tab.
          >
          > Does it give any additional feature or any optimization.
          >
          > Regards,
          > Lakshman
          >
          >
          > --------------------------------------------------------------
          > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
          >
          >
          >
          > Your use of Yahoo! Groups is subject to
          > http://docs.yahoo.com/info/terms/
          >
          >
          >
          >
          >
          > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
          >
          >
          >
        • asedlmeier
          Do you update your statistics, Ian ? I also remember that Oracle optimizer s output required you to be a scientist to read but something I saw very often is
          Message 4 of 14 , Aug 1, 2003
          • 0 Attachment
            Do you update your statistics, Ian ?

            I also remember that Oracle optimizer's output required you to be a
            scientist to read but something I saw very often is that people
            forgot to update statistics on a regular basis. It has been a while
            since I worked last time with Oracle but I think Oracle does not do
            this automatically (or by default).

            If there's no statistics the optimizer gets a rule based system and
            it is likely that it does a sub-optimal job then. As a result one can
            often observe linear decreasing performance, wrong index choices, ...
            over time.

            Best regards,

            Andreas.

            --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
            wrote:
            > In Oracle there is one hint which has very clear uses: /*+
            FIRST_ROWS
            > */. This instructs Oracle to return the first part of a query's
            > resultset. On large tables which have to be accessed in online
            tasks
            > load times for the task are massively improved.
            >
            > Apart from that I have never managed to second guess the Oracle
            > optimiser's index choices. Not once.
            >
            >
            > -----Original Message-----
            > From: Brookes, Alan [mailto:alan.j.brookes@e...]
            > Sent: 01 August 2003 08:35
            > To: 'magicu-l@yahoogroups.com'
            > Subject: [magicu-l] RE: About Hint option in the Table Properties
            >
            >
            > Lakshman,
            >
            > This feature allows you to pass a hint to the SQL optimiser when
            > accessing the table.
            >
            > Although the hint will force the optimiser to use (e.g.) a specific
            > index, there is never a guarantee that you will improve performance.
            >
            > You may be lucky, but the optimiser uses dozens of ways to
            calculate the
            > cost of a query, and it will not be very often that you come up
            with a
            > better plan than it does!
            >
            > Best left alone, IMHO.
            >
            > Regards,
            >
            > Alan
            >
            >
            > -----Original Message-----
            > From: enext [mailto:support@m...]
            > Sent: Friday, August 01, 2003 6:54 AM
            > To: magicu-l@yahoogroups.com
            > Subject: About Hint option in the Table Properties
            >
            >
            > Dear all,
            >
            > I would like to know where exactly the hint option can able to use
            it,
            > which is available at table properties in SQL tab.
            >
            > Does it give any additional feature or any optimization.
            >
            > Regards,
            > Lakshman
            >
            >
            > --------------------------------------------------------------
            > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
            >
            >
            >
            > Your use of Yahoo! Groups is subject to
            > http://docs.yahoo.com/info/terms/
          • Ian Ledzion
            There s a process which updates the stats every night. The problem with browsing on large tables lies with Magic, not Oracle. Magic opens one cursor to get the
            Message 5 of 14 , Aug 2, 2003
            • 0 Attachment
              There's a process which updates the stats every night.

              The problem with browsing on large tables lies with Magic, not Oracle.
              Magic opens one cursor to get the ROWID of all the records which will
              need to be displayed. It then opens further cursors for each record.
              The problem lies with this first cursor which reads the whole table,
              leading to this enormous delay when loading the task. You can avoid
              reading the whole table by using the FIRST_ROWS hint.

              Give it a try on a big table, say 200,000+ records on a PIII
              monoprocessor server.

              -----Original Message-----
              From: asedlmeier [mailto:sedlmeier@...]
              Sent: 01 August 2003 21:41
              To: magicu-l@yahoogroups.com
              Subject: [magicu-l] Re: About Hint option in the Table Properties


              Do you update your statistics, Ian ?

              I also remember that Oracle optimizer's output required you to be a
              scientist to read but something I saw very often is that people
              forgot to update statistics on a regular basis. It has been a while
              since I worked last time with Oracle but I think Oracle does not do
              this automatically (or by default).

              If there's no statistics the optimizer gets a rule based system and
              it is likely that it does a sub-optimal job then. As a result one can
              often observe linear decreasing performance, wrong index choices, ...
              over time.

              Best regards,

              Andreas.

              --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
              wrote:
              > In Oracle there is one hint which has very clear uses: /*+
              FIRST_ROWS
              > */. This instructs Oracle to return the first part of a query's
              > resultset. On large tables which have to be accessed in online
              tasks
              > load times for the task are massively improved.
              >
              > Apart from that I have never managed to second guess the Oracle
              > optimiser's index choices. Not once.
              >
              >
              > -----Original Message-----
              > From: Brookes, Alan [mailto:alan.j.brookes@e...]
              > Sent: 01 August 2003 08:35
              > To: 'magicu-l@yahoogroups.com'
              > Subject: [magicu-l] RE: About Hint option in the Table Properties
              >
              >
              > Lakshman,
              >
              > This feature allows you to pass a hint to the SQL optimiser when
              > accessing the table.
              >
              > Although the hint will force the optimiser to use (e.g.) a specific
              > index, there is never a guarantee that you will improve performance.
              >
              > You may be lucky, but the optimiser uses dozens of ways to
              calculate the
              > cost of a query, and it will not be very often that you come up
              with a
              > better plan than it does!
              >
              > Best left alone, IMHO.
              >
              > Regards,
              >
              > Alan
              >
              >
              > -----Original Message-----
              > From: enext [mailto:support@m...]
              > Sent: Friday, August 01, 2003 6:54 AM
              > To: magicu-l@yahoogroups.com
              > Subject: About Hint option in the Table Properties
              >
              >
              > Dear all,
              >
              > I would like to know where exactly the hint option can able to use
              it,
              > which is available at table properties in SQL tab.
              >
              > Does it give any additional feature or any optimization.
              >
              > Regards,
              > Lakshman
              >
              >
              > --------------------------------------------------------------
              > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
              >
              >
              >
              > Your use of Yahoo! Groups is subject to
              > http://docs.yahoo.com/info/terms/




              Your use of Yahoo! Groups is subject to
              http://docs.yahoo.com/info/terms/
            • asedlmeier
              I can not give it a try, Ian. I do not have Oracle available. Your explanation for the long delay when opening a large Oracle table is very reasonable and the
              Message 6 of 14 , Aug 2, 2003
              • 0 Attachment
                I can not give it a try, Ian. I do not have Oracle available.
                Your explanation for the long delay when opening a large Oracle table
                is very reasonable and the hint makes sense. It is btw. in accordance
                with the suggestions from a technote from Magic
                (http://support.magicsoftware.com/beta/SQL/OrclTabs.htm)

                I would not expect such behaviour however. One uses a cursor to avoid
                exactly the behaviour you describe. Magic can handle these and Oracle
                is able to provide a "real one" in maybe more cases than their
                competition.

                What the heck is that rowid cursor you mention good for ?
                Can you try if its just there because of "positioning" ?
                Try removing any index reference from a simple browse program (this
                may confuse the optimizer) and use PK (or any other clustered index)
                for position. This (and I hope I do not err) should open you the
                table very fast.

                Magic behaves like that at least with other DBMSs (MSSQL, DB2/400).
                In case of DB2/400 this possibility to have browse programs on huge
                resultset came ... when the SQL gateway was changed in a way that it
                always adds "FOR FETCH ONLY OPTIMIZE FOR 100 ROWS" to the generated
                SQL. Having this clause in every SQL statement now seems suspicious
                to me, we experienced however no problems with that so far. Before we
                had to workaround quite similar to that what you do with Oracle.

                Best Regards,

                Andreas.


                --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                wrote:
                > There's a process which updates the stats every night.
                >
                > The problem with browsing on large tables lies with Magic, not
                Oracle.
                > Magic opens one cursor to get the ROWID of all the records which
                will
                > need to be displayed. It then opens further cursors for each
                record.
                > The problem lies with this first cursor which reads the whole table,
                > leading to this enormous delay when loading the task. You can avoid
                > reading the whole table by using the FIRST_ROWS hint.
                >
                > Give it a try on a big table, say 200,000+ records on a PIII
                > monoprocessor server.
                >
                > -----Original Message-----
                > From: asedlmeier [mailto:sedlmeier@h...]
                > Sent: 01 August 2003 21:41
                > To: magicu-l@yahoogroups.com
                > Subject: [magicu-l] Re: About Hint option in the Table Properties
                >
                >
                > Do you update your statistics, Ian ?
                >
                > I also remember that Oracle optimizer's output required you to be a
                > scientist to read but something I saw very often is that people
                > forgot to update statistics on a regular basis. It has been a while
                > since I worked last time with Oracle but I think Oracle does not do
                > this automatically (or by default).
                >
                > If there's no statistics the optimizer gets a rule based system and
                > it is likely that it does a sub-optimal job then. As a result one
                can
                > often observe linear decreasing performance, wrong index
                choices, ...
                > over time.
                >
                > Best regards,
                >
                > Andreas.
                >
                > --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                > wrote:
                > > In Oracle there is one hint which has very clear uses: /*+
                > FIRST_ROWS
                > > */. This instructs Oracle to return the first part of a query's
                > > resultset. On large tables which have to be accessed in online
                > tasks
                > > load times for the task are massively improved.
                > >
                > > Apart from that I have never managed to second guess the Oracle
                > > optimiser's index choices. Not once.
                > >
                > >
                > > -----Original Message-----
                > > From: Brookes, Alan [mailto:alan.j.brookes@e...]
                > > Sent: 01 August 2003 08:35
                > > To: 'magicu-l@yahoogroups.com'
                > > Subject: [magicu-l] RE: About Hint option in the Table Properties
                > >
                > >
                > > Lakshman,
                > >
                > > This feature allows you to pass a hint to the SQL optimiser when
                > > accessing the table.
                > >
                > > Although the hint will force the optimiser to use (e.g.) a
                specific
                > > index, there is never a guarantee that you will improve
                performance.
                > >
                > > You may be lucky, but the optimiser uses dozens of ways to
                > calculate the
                > > cost of a query, and it will not be very often that you come up
                > with a
                > > better plan than it does!
                > >
                > > Best left alone, IMHO.
                > >
                > > Regards,
                > >
                > > Alan
                > >
                > >
                > > -----Original Message-----
                > > From: enext [mailto:support@m...]
                > > Sent: Friday, August 01, 2003 6:54 AM
                > > To: magicu-l@yahoogroups.com
                > > Subject: About Hint option in the Table Properties
                > >
                > >
                > > Dear all,
                > >
                > > I would like to know where exactly the hint option can able to use
                > it,
                > > which is available at table properties in SQL tab.
                > >
                > > Does it give any additional feature or any optimization.
                > >
                > > Regards,
                > > Lakshman
                > >
                > >
                > > --------------------------------------------------------------
                > > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
                > >
                > >
                > >
                > > Your use of Yahoo! Groups is subject to
                > > http://docs.yahoo.com/info/terms/
                >
                >
                >
                >
                > Your use of Yahoo! Groups is subject to
                > http://docs.yahoo.com/info/terms/
              • asedlmeier
                ... to be sure I just tested this now with DB2/400 on a 20 million records table with a huge amount of data. The browse program opens VERY fast (I cannot see
                Message 7 of 14 , Aug 2, 2003
                • 0 Attachment
                  ... to be sure I just tested this now with DB2/400 on a 20 million
                  records table with a huge amount of data. The browse program opens
                  VERY fast (I cannot see any delay).

                  --- In magicu-l@yahoogroups.com, "asedlmeier" <sedlmeier@h...> wrote:
                  > I can not give it a try, Ian. I do not have Oracle available.
                  > Your explanation for the long delay when opening a large Oracle
                  table
                  > is very reasonable and the hint makes sense. It is btw. in
                  accordance
                  > with the suggestions from a technote from Magic
                  > (http://support.magicsoftware.com/beta/SQL/OrclTabs.htm)
                  >
                  > I would not expect such behaviour however. One uses a cursor to
                  avoid
                  > exactly the behaviour you describe. Magic can handle these and
                  Oracle
                  > is able to provide a "real one" in maybe more cases than their
                  > competition.
                  >
                  > What the heck is that rowid cursor you mention good for ?
                  > Can you try if its just there because of "positioning" ?
                  > Try removing any index reference from a simple browse program (this
                  > may confuse the optimizer) and use PK (or any other clustered
                  index)
                  > for position. This (and I hope I do not err) should open you the
                  > table very fast.
                  >
                  > Magic behaves like that at least with other DBMSs (MSSQL, DB2/400).
                  > In case of DB2/400 this possibility to have browse programs on huge
                  > resultset came ... when the SQL gateway was changed in a way that
                  it
                  > always adds "FOR FETCH ONLY OPTIMIZE FOR 100 ROWS" to the generated
                  > SQL. Having this clause in every SQL statement now seems suspicious
                  > to me, we experienced however no problems with that so far. Before
                  we
                  > had to workaround quite similar to that what you do with Oracle.
                  >
                  > Best Regards,
                  >
                  > Andreas.
                  >
                  >
                  > --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                  > wrote:
                  > > There's a process which updates the stats every night.
                  > >
                  > > The problem with browsing on large tables lies with Magic, not
                  > Oracle.
                  > > Magic opens one cursor to get the ROWID of all the records which
                  > will
                  > > need to be displayed. It then opens further cursors for each
                  > record.
                  > > The problem lies with this first cursor which reads the whole
                  table,
                  > > leading to this enormous delay when loading the task. You can
                  avoid
                  > > reading the whole table by using the FIRST_ROWS hint.
                  > >
                  > > Give it a try on a big table, say 200,000+ records on a PIII
                  > > monoprocessor server.
                  > >
                  > > -----Original Message-----
                  > > From: asedlmeier [mailto:sedlmeier@h...]
                  > > Sent: 01 August 2003 21:41
                  > > To: magicu-l@yahoogroups.com
                  > > Subject: [magicu-l] Re: About Hint option in the Table Properties
                  > >
                  > >
                  > > Do you update your statistics, Ian ?
                  > >
                  > > I also remember that Oracle optimizer's output required you to be
                  a
                  > > scientist to read but something I saw very often is that people
                  > > forgot to update statistics on a regular basis. It has been a
                  while
                  > > since I worked last time with Oracle but I think Oracle does not
                  do
                  > > this automatically (or by default).
                  > >
                  > > If there's no statistics the optimizer gets a rule based system
                  and
                  > > it is likely that it does a sub-optimal job then. As a result one
                  > can
                  > > often observe linear decreasing performance, wrong index
                  > choices, ...
                  > > over time.
                  > >
                  > > Best regards,
                  > >
                  > > Andreas.
                  > >
                  > > --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                  > > wrote:
                  > > > In Oracle there is one hint which has very clear uses: /*+
                  > > FIRST_ROWS
                  > > > */. This instructs Oracle to return the first part of a
                  query's
                  > > > resultset. On large tables which have to be accessed in online
                  > > tasks
                  > > > load times for the task are massively improved.
                  > > >
                  > > > Apart from that I have never managed to second guess the Oracle
                  > > > optimiser's index choices. Not once.
                  > > >
                  > > >
                  > > > -----Original Message-----
                  > > > From: Brookes, Alan [mailto:alan.j.brookes@e...]
                  > > > Sent: 01 August 2003 08:35
                  > > > To: 'magicu-l@yahoogroups.com'
                  > > > Subject: [magicu-l] RE: About Hint option in the Table
                  Properties
                  > > >
                  > > >
                  > > > Lakshman,
                  > > >
                  > > > This feature allows you to pass a hint to the SQL optimiser
                  when
                  > > > accessing the table.
                  > > >
                  > > > Although the hint will force the optimiser to use (e.g.) a
                  > specific
                  > > > index, there is never a guarantee that you will improve
                  > performance.
                  > > >
                  > > > You may be lucky, but the optimiser uses dozens of ways to
                  > > calculate the
                  > > > cost of a query, and it will not be very often that you come up
                  > > with a
                  > > > better plan than it does!
                  > > >
                  > > > Best left alone, IMHO.
                  > > >
                  > > > Regards,
                  > > >
                  > > > Alan
                  > > >
                  > > >
                  > > > -----Original Message-----
                  > > > From: enext [mailto:support@m...]
                  > > > Sent: Friday, August 01, 2003 6:54 AM
                  > > > To: magicu-l@yahoogroups.com
                  > > > Subject: About Hint option in the Table Properties
                  > > >
                  > > >
                  > > > Dear all,
                  > > >
                  > > > I would like to know where exactly the hint option can able to
                  use
                  > > it,
                  > > > which is available at table properties in SQL tab.
                  > > >
                  > > > Does it give any additional feature or any optimization.
                  > > >
                  > > > Regards,
                  > > > Lakshman
                  > > >
                  > > >
                  > > > --------------------------------------------------------------
                  > > > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
                  > > >
                  > > >
                  > > >
                  > > > Your use of Yahoo! Groups is subject to
                  > > > http://docs.yahoo.com/info/terms/
                  > >
                  > >
                  > >
                  > >
                  > > Your use of Yahoo! Groups is subject to
                  > > http://docs.yahoo.com/info/terms/
                • N.Srinivasan
                  Hi Guys We have an installtion with a farm of 3 citrix reservers. The customer wishes to import files every hour between 05:00 Am and 11.00 Pm every day. We
                  Message 8 of 14 , Aug 2, 2003
                  • 0 Attachment
                    Hi Guys

                    We have an installtion with a farm of 3 citrix
                    reservers. The customer wishes to import files every
                    hour between 05:00 Am and 11.00 Pm every day.

                    We setup a batch process in Magic with seperate ini to
                    do this task . The task runs fine if magic is executed
                    by a desktop shortcut.

                    But when loaded on a scheduler, the process never
                    kicks off on a citrix environment, could any one
                    suggest a way out of this


                    Best regards
                    shrini

                    __________________________________
                    Do you Yahoo!?
                    Yahoo! SiteBuilder - Free, easy-to-use web site design software
                    http://sitebuilder.yahoo.com
                  • Steve Worthington
                    Ian your observation is true,. but in 90% of online tasks, where there is an indexed fetch, this hint will have an adverse effect, how often are you opening an
                    Message 9 of 14 , Aug 2, 2003
                    • 0 Attachment
                      Ian your observation is true,. but in 90% of online tasks, where there is an
                      indexed fetch, this hint will have an adverse effect, how often are you
                      opening an online task that will fetch 200,000 records (except for a
                      CTRL+G).....
                      ----- Original Message -----
                      From: "Ian Ledzion" <ian_ledzion@...>
                      To: <magicu-l@yahoogroups.com>
                      Sent: Saturday, August 02, 2003 4:04 AM
                      Subject: RE: [magicu-l] Re: About Hint option in the Table Properties


                      > There's a process which updates the stats every night.
                      >
                      > The problem with browsing on large tables lies with Magic, not Oracle.
                      > Magic opens one cursor to get the ROWID of all the records which will
                      > need to be displayed. It then opens further cursors for each record.
                      > The problem lies with this first cursor which reads the whole table,
                      > leading to this enormous delay when loading the task. You can avoid
                      > reading the whole table by using the FIRST_ROWS hint.
                      >
                      > Give it a try on a big table, say 200,000+ records on a PIII
                      > monoprocessor server.
                      >
                      > -----Original Message-----
                      > From: asedlmeier [mailto:sedlmeier@...]
                      > Sent: 01 August 2003 21:41
                      > To: magicu-l@yahoogroups.com
                      > Subject: [magicu-l] Re: About Hint option in the Table Properties
                      >
                      >
                      > Do you update your statistics, Ian ?
                      >
                      > I also remember that Oracle optimizer's output required you to be a
                      > scientist to read but something I saw very often is that people
                      > forgot to update statistics on a regular basis. It has been a while
                      > since I worked last time with Oracle but I think Oracle does not do
                      > this automatically (or by default).
                      >
                      > If there's no statistics the optimizer gets a rule based system and
                      > it is likely that it does a sub-optimal job then. As a result one can
                      > often observe linear decreasing performance, wrong index choices, ...
                      > over time.
                      >
                      > Best regards,
                      >
                      > Andreas.
                      >
                      > --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                      > wrote:
                      > > In Oracle there is one hint which has very clear uses: /*+
                      > FIRST_ROWS
                      > > */. This instructs Oracle to return the first part of a query's
                      > > resultset. On large tables which have to be accessed in online
                      > tasks
                      > > load times for the task are massively improved.
                      > >
                      > > Apart from that I have never managed to second guess the Oracle
                      > > optimiser's index choices. Not once.
                      > >
                      > >
                      > > -----Original Message-----
                      > > From: Brookes, Alan [mailto:alan.j.brookes@e...]
                      > > Sent: 01 August 2003 08:35
                      > > To: 'magicu-l@yahoogroups.com'
                      > > Subject: [magicu-l] RE: About Hint option in the Table Properties
                      > >
                      > >
                      > > Lakshman,
                      > >
                      > > This feature allows you to pass a hint to the SQL optimiser when
                      > > accessing the table.
                      > >
                      > > Although the hint will force the optimiser to use (e.g.) a specific
                      > > index, there is never a guarantee that you will improve performance.
                      > >
                      > > You may be lucky, but the optimiser uses dozens of ways to
                      > calculate the
                      > > cost of a query, and it will not be very often that you come up
                      > with a
                      > > better plan than it does!
                      > >
                      > > Best left alone, IMHO.
                      > >
                      > > Regards,
                      > >
                      > > Alan
                      > >
                      > >
                      > > -----Original Message-----
                      > > From: enext [mailto:support@m...]
                      > > Sent: Friday, August 01, 2003 6:54 AM
                      > > To: magicu-l@yahoogroups.com
                      > > Subject: About Hint option in the Table Properties
                      > >
                      > >
                      > > Dear all,
                      > >
                      > > I would like to know where exactly the hint option can able to use
                      > it,
                      > > which is available at table properties in SQL tab.
                      > >
                      > > Does it give any additional feature or any optimization.
                      > >
                      > > Regards,
                      > > Lakshman
                      > >
                      > >
                      > > --------------------------------------------------------------
                      > > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
                      > >
                      > >
                      > >
                      > > Your use of Yahoo! Groups is subject to
                      > > http://docs.yahoo.com/info/terms/
                      >
                      >
                      >
                      >
                      > Your use of Yahoo! Groups is subject to
                      > http://docs.yahoo.com/info/terms/
                      >
                      >
                      >
                      >
                      >
                      > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                      >
                      >
                      >
                    • Frank Van Herreweghe
                      shrini, i had last week kind of the same problem but not on citrix but directly on the data-server (after the citrix). Check out the security of your shortcut
                      Message 10 of 14 , Aug 2, 2003
                      • 0 Attachment
                        shrini,

                        i had last week kind of the same problem but not on citrix but directly on
                        the data-server (after the citrix). Check out the security of your shortcut
                        ! i had administrator rights when the original shortcut was created but
                        somehow the shortcut didn't had the fll control AND SYSTEM.

                        why not running some batchprocessor on the dataserver instead of
                        starting/stopping the whole day a runtime ? You can give it much other jobs
                        too. I start my batchprocessor at 5:00 and stop it at 23:00 Hr to make it
                        possible to backup (no open files).

                        hope this helps
                        Regards

                        Frank Van Herreweghe
                        Frank.van.herreweghe@...

                        -----Original Message-----
                        From: N.Srinivasan [mailto:srinisun@...]
                        Sent: zaterdag 2 augustus 2003 13:45
                        To: magicu-l@yahoogroups.com
                        Subject: [magicu-l] Magic9.3(client server MFF) Does not execute in
                        Windows scheduler on Citrix environment


                        Hi Guys

                        We have an installtion with a farm of 3 citrix
                        reservers. The customer wishes to import files every
                        hour between 05:00 Am and 11.00 Pm every day.

                        We setup a batch process in Magic with seperate ini to
                        do this task . The task runs fine if magic is executed
                        by a desktop shortcut.

                        But when loaded on a scheduler, the process never
                        kicks off on a citrix environment, could any one
                        suggest a way out of this


                        Best regards
                        shrini

                        __________________________________
                        Do you Yahoo!?
                        Yahoo! SiteBuilder - Free, easy-to-use web site design software
                        http://sitebuilder.yahoo.com




                        Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                      • Rafi Erlitzki [@UPS]
                        Also take in account that, in a typical application, about 90% of database query activity is the result on LINKs. In most cases the locate-criteria of LINKs
                        Message 11 of 14 , Aug 2, 2003
                        • 0 Attachment
                          Also take in account that, in a typical application, about 90% of database
                          query activity is the result on LINKs.
                          In most cases the locate-criteria of LINKs uniquely define a single record.
                          In these cases it makes no sense to use the FIRST_ROWS hint.

                          The two major contributers to the execution plan that the Oracle optimizer
                          chooses are the WHERE clause and the ORDER BY clause.
                          Using the FIRST_ROWS hint practically instructs oracle to favor the ORDER BY
                          clause.

                          In my team we have experimented a lot with the effects of the FIRST_ROWS
                          hint, especially in relation to LINKs.
                          Our conclusions:
                          - Never define a hint at the table level.
                          - In LINKs that have locate-criteria that uniquely define a single record
                          DO NOT use INDX for the LINK (there's no point telling Oracle to ORDER BY
                          when your query result will contain a single record).


                          So ... when used in links you do not want to use a hint.
                          However, when the same table is used as the main table of an online task
                          (that queries many records) a hint sometimes can help. Our guideline is not
                          to use a hint unless you can prove improved performance, on a database that
                          is similar in size and nature of data to a real production database used by
                          our customers. Also, the test database must have updated statistics.

                          Let's assume that you proved that a hint is required for the main table of
                          an online program. But the same table is used in numerous LINKs accross the
                          application. If you add a hint at the table level you improve one component
                          but you risk degrading performance in other areas of your application.
                          How can you have a hint only for the main table, but not when the same table
                          is used in a link?

                          The answer -- you can define a hint in the properties of an INDEX !!!
                          - In the tables repository create two indexes for your table with identical
                          structure (as the structure of a physical Oracle index).
                          - One will be your "general purpose" index and the other will be a "hinted"
                          index. make sure to name them accordingly.
                          - Open the properties (Ctrl+P) of the "hinted" index. In the SQL tab set
                          the "index type" to "virtual". Set the "hint" field to "Yes" and type
                          /*+FIRST_ROWS*/ in the space provided.

                          - When you use the table as the main table of an online program (that
                          requires a hint), use the "hinted" index.
                          - In LINKs do not use an index at all (unless it's a must).
                          - In all other cases use the "general purpose" index.

                          Regards,
                          Rafi.



                          ----- Original Message -----
                          From: <steve@...>
                          To: <magicu-l@yahoogroups.com>
                          Sent: Saturday, August 02, 2003 07:12
                          Subject: Re: [magicu-l] Re: About Hint option in the Table Properties


                          Ian your observation is true,. but in 90% of online tasks, where there is an
                          indexed fetch, this hint will have an adverse effect, how often are you
                          opening an online task that will fetch 200,000 records (except for a
                          CTRL+G).....
                          ----- Original Message -----
                          From: "Ian Ledzion" <ian_ledzion@...>
                          To: <magicu-l@yahoogroups.com>
                          Sent: Saturday, August 02, 2003 4:04 AM
                          Subject: RE: [magicu-l] Re: About Hint option in the Table Properties


                          > There's a process which updates the stats every night.
                          >
                          > The problem with browsing on large tables lies with Magic, not Oracle.
                          > Magic opens one cursor to get the ROWID of all the records which will
                          > need to be displayed. It then opens further cursors for each record.
                          > The problem lies with this first cursor which reads the whole table,
                          > leading to this enormous delay when loading the task. You can avoid
                          > reading the whole table by using the FIRST_ROWS hint.
                          >
                          > Give it a try on a big table, say 200,000+ records on a PIII
                          > monoprocessor server.
                          >
                          > -----Original Message-----
                          > From: asedlmeier [mailto:sedlmeier@...]
                          > Sent: 01 August 2003 21:41
                          > To: magicu-l@yahoogroups.com
                          > Subject: [magicu-l] Re: About Hint option in the Table Properties
                          >
                          >
                          > Do you update your statistics, Ian ?
                          >
                          > I also remember that Oracle optimizer's output required you to be a
                          > scientist to read but something I saw very often is that people
                          > forgot to update statistics on a regular basis. It has been a while
                          > since I worked last time with Oracle but I think Oracle does not do
                          > this automatically (or by default).
                          >
                          > If there's no statistics the optimizer gets a rule based system and
                          > it is likely that it does a sub-optimal job then. As a result one can
                          > often observe linear decreasing performance, wrong index choices, ...
                          > over time.
                          >
                          > Best regards,
                          >
                          > Andreas.
                          >
                          > --- In magicu-l@yahoogroups.com, "Ian Ledzion" <ian_ledzion@f...>
                          > wrote:
                          > > In Oracle there is one hint which has very clear uses: /*+
                          > FIRST_ROWS
                          > > */. This instructs Oracle to return the first part of a query's
                          > > resultset. On large tables which have to be accessed in online
                          > tasks
                          > > load times for the task are massively improved.
                          > >
                          > > Apart from that I have never managed to second guess the Oracle
                          > > optimiser's index choices. Not once.
                          > >
                          > >
                          > > -----Original Message-----
                          > > From: Brookes, Alan [mailto:alan.j.brookes@e...]
                          > > Sent: 01 August 2003 08:35
                          > > To: 'magicu-l@yahoogroups.com'
                          > > Subject: [magicu-l] RE: About Hint option in the Table Properties
                          > >
                          > >
                          > > Lakshman,
                          > >
                          > > This feature allows you to pass a hint to the SQL optimiser when
                          > > accessing the table.
                          > >
                          > > Although the hint will force the optimiser to use (e.g.) a specific
                          > > index, there is never a guarantee that you will improve performance.
                          > >
                          > > You may be lucky, but the optimiser uses dozens of ways to
                          > calculate the
                          > > cost of a query, and it will not be very often that you come up
                          > with a
                          > > better plan than it does!
                          > >
                          > > Best left alone, IMHO.
                          > >
                          > > Regards,
                          > >
                          > > Alan
                          > >
                          > >
                          > > -----Original Message-----
                          > > From: enext [mailto:support@m...]
                          > > Sent: Friday, August 01, 2003 6:54 AM
                          > > To: magicu-l@yahoogroups.com
                          > > Subject: About Hint option in the Table Properties
                          > >
                          > >
                          > > Dear all,
                          > >
                          > > I would like to know where exactly the hint option can able to use
                          > it,
                          > > which is available at table properties in SQL tab.
                          > >
                          > > Does it give any additional feature or any optimization.
                          > >
                          > > Regards,
                          > > Lakshman
                          > >
                          > >
                          > > --------------------------------------------------------------
                          > > ETC AUTOMATION SYSTEMS, MUMBAI, INDIA
                          > >
                          > >
                          > >
                          > > Your use of Yahoo! Groups is subject to
                          > > http://docs.yahoo.com/info/terms/
                          >
                          >
                          >
                          >
                          > Your use of Yahoo! Groups is subject to
                          > http://docs.yahoo.com/info/terms/
                          >
                          >
                          >
                          >
                          >
                          > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                          >
                          >
                          >




                          Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                        • Frank Van Herreweghe
                          ... From: Frank Van Herreweghe [mailto:Frank.Van.Herreweghe@pandora.be] Sent: zaterdag 2 augustus 2003 23:30 To: magicu-l@yahoogroups.com Subject: RE:
                          Message 12 of 14 , Aug 4, 2003
                          • 0 Attachment
                            -----Original Message-----
                            From: Frank Van Herreweghe [mailto:Frank.Van.Herreweghe@...]
                            Sent: zaterdag 2 augustus 2003 23:30
                            To: magicu-l@yahoogroups.com
                            Subject: RE: [magicu-l] Magic9.3(client server MFF) Does not execute in
                            Windows scheduler on Citrix environment


                            shrini,

                            i had last week kind of the same problem but not on citrix but directly on
                            the data-server (after the citrix). Check out the security of your shortcut
                            ! i had administrator rights when the original shortcut was created but
                            somehow the shortcut didn't had the fll control AND SYSTEM.

                            why not running some batchprocessor on the dataserver instead of
                            starting/stopping the whole day a runtime ? You can give it much other jobs
                            too. I start my batchprocessor at 5:00 and stop it at 23:00 Hr to make it
                            possible to backup (no open files).

                            hope this helps
                            Regards

                            Frank Van Herreweghe
                            Frank.van.herreweghe@...

                            -----Original Message-----
                            From: N.Srinivasan [mailto:srinisun@...]
                            Sent: zaterdag 2 augustus 2003 13:45
                            To: magicu-l@yahoogroups.com
                            Subject: [magicu-l] Magic9.3(client server MFF) Does not execute in
                            Windows scheduler on Citrix environment


                            Hi Guys

                            We have an installtion with a farm of 3 citrix
                            reservers. The customer wishes to import files every
                            hour between 05:00 Am and 11.00 Pm every day.

                            We setup a batch process in Magic with seperate ini to
                            do this task . The task runs fine if magic is executed
                            by a desktop shortcut.

                            But when loaded on a scheduler, the process never
                            kicks off on a citrix environment, could any one
                            suggest a way out of this


                            Best regards
                            shrini

                            __________________________________
                            Do you Yahoo!?
                            Yahoo! SiteBuilder - Free, easy-to-use web site design software
                            http://sitebuilder.yahoo.com




                            Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/







                            Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                          Your message has been successfully submitted and would be delivered to recipients shortly.