Professional Web Applications Themes

Database is extremely slow - Oracle Server

I have an Oracle 7.3.4.4 database that is experiencing extremely slow response times in the past few days. I know that one of the tables had as many as 3,000,000 rows, but over the past week some of the information has been purged, so now there are about 2,200,000 rows; other tables have also been drastically reduced in row count. Last night, I updated statistics (hoping it might make a difference), but unfortunately, we don't observe any real difference. I have two questions: 1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this mean that the server does ...

  1. #1

    Default Database is extremely slow

    I have an Oracle 7.3.4.4 database that is experiencing extremely slow
    response times in the past few days. I know that one of the tables
    had as many as 3,000,000 rows, but over the past week some of the
    information has been purged, so now there are about 2,200,000 rows;
    other tables have also been drastically reduced in row count. Last
    night, I updated statistics (hoping it might make a difference), but
    unfortunately, we don't observe any real difference.

    I have two questions:

    1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this
    mean that the server does not rely on statistics stored in the catalog
    when it is putting together the execution plan for a query? I am
    considering changing this to CHOOSE. Can I do this without having an
    adverse effect on performance or changing query results?

    2. Should I go ahead and rebuild the indexes on the tables that I
    know have been radically reduced in row count?

    This system has never performed well, it is running on older, obsolete
    hardware and software, and a replacement system for it has been
    targeted and is being tested as I write this, but does anyone out
    there have any specific recommendations that I could try to help our
    current situation?

    Thanks,

    Scott Ashby
    DBA
    Swedish Match North America
    Scott Ashby Guest

  2. #2

    Default Re: Database is extremely slow

    "Scott Ashby" <Scott.AshbySMNA.com> wrote in message
    news:bd74662.0306250453.f13d73fposting.google.com ...
    > I have an Oracle 7.3.4.4 database that is experiencing extremely slow
    Ah OK. No point going into details, then.
    > 1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this
    > mean that the server does not rely on statistics stored in the catalog
    > when it is putting together the execution plan for a query? I am
    > considering changing this to CHOOSE. Can I do this without having an
    > adverse effect on performance or changing query results?
    Most likely no.
    >
    > 2. Should I go ahead and rebuild the indexes on the tables that I
    > know have been radically reduced in row count?
    Yes.
    >
    > This system has never performed well, it is running on older, obsolete
    > hardware and software, and a replacement system for it has been
    > targeted and is being tested as I write this, but does anyone out
    Hurry up then.


    --
    Cheers
    Nuno Souto
    [email]wizofoz2k.au.nosp[/email]am


    Noons Guest

  3. #3

    Default Re: Database is extremely slow

    "Scott Ashby" <Scott.AshbySMNA.com> wrote in message
    news:bd74662.0306250453.f13d73fposting.google.com ...
    > I have an Oracle 7.3.4.4 database that is experiencing extremely slow
    > response times in the past few days. I know that one of the tables
    > had as many as 3,000,000 rows, but over the past week some of the
    > information has been purged, so now there are about 2,200,000 rows;
    > other tables have also been drastically reduced in row count. Last
    > night, I updated statistics (hoping it might make a difference), but
    > unfortunately, we don't observe any real difference.
    >
    > I have two questions:
    >
    > 1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this
    > mean that the server does not rely on statistics stored in the catalog
    > when it is putting together the execution plan for a query? I am
    > considering changing this to CHOOSE. Can I do this without having an
    > adverse effect on performance or changing query results?
    Yes, and You dont know without trying it. However 7.3.4 was pretty much the
    first production release with a CBO in it. So you'd be changing from a
    mature technology to a 1.x technology. Guess which is likely to be more
    reliable.
    > 2. Should I go ahead and rebuild the indexes on the tables that I
    > know have been radically reduced in row count?
    Yes.

    As to recommendations, find out when the problem started and if this
    coincides with any significant changes.


    --
    Niall Litchfield
    Oracle DBA
    Audit Commission UK


    Niall Litchfield Guest

  4. #4

    Default Re: Database is extremely slow

    On Wed, 25 Jun 2003 17:20:38 +0100, "Niall Litchfield"
    <n-litchfieldaudit-commission.gov.uk> wrote:
    >However 7.3.4 was pretty much the
    >first production release with a CBO in it
    Completely incorrect. CBO was available from 7.1. However it was so
    lousy at that time that no one used. Stating though 7.3.4 is the first
    release with CBO 1.x in it is just plain untrue.


    Sybrand Bakker, Senior Oracle DBA

    To reply remove -verwijderdit from my e-mail address
    Sybrand Bakker Guest

  5. #5

    Default Re: Database is extremely slow

    "Scott Ashby" <Scott.AshbySMNA.com> schrieb im Newsbeitrag
    news:bd74662.0306250453.f13d73fposting.google.com ...
    > I have an Oracle 7.3.4.4 database that is experiencing extremely slow
    > response times in the past few days. I know that one of the tables
    > had as many as 3,000,000 rows, but over the past week some of the
    > information has been purged, so now there are about 2,200,000 rows;
    > other tables have also been drastically reduced in row count. Last
    > night, I updated statistics (hoping it might make a difference), but
    > unfortunately, we don't observe any real difference.
    >
    > I have two questions:
    >
    > 1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this
    > mean that the server does not rely on statistics stored in the catalog
    > when it is putting together the execution plan for a query? I am
    > considering changing this to CHOOSE. Can I do this without having an
    > adverse effect on performance or changing query results?
    >
    > 2. Should I go ahead and rebuild the indexes on the tables that I
    > know have been radically reduced in row count?
    >
    In addition to what the other posters said:

    it is not only the indexes to rebuild, but also
    the tables themselves also (ALTER TABLE xyz MOVE), as the High Water Mark
    remains up when
    deleting large data portions of it, and so e.g. a COUNT(*) would just take
    the same time as if no rows were deleted. Unfortunately, I think that MOVE
    is only available from 8i on ...

    hth, Jan


    Jan Gelbrich Guest

Similar Threads

  1. #39968 [NEW]: date() is extremely slow
    By katzenmayer at erfnet dot de in forum PHP Bugs
    Replies: 1
    Last Post: December 27th, 10:35 PM
  2. Net::SSH::Perl extremely slow?
    By Yon in forum PERL Modules
    Replies: 1
    Last Post: July 15th, 07:11 PM
  3. Extremely Slow Printing in 6.01
    By Adam_Gadsby@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: April 1st, 07:16 PM
  4. Extremely Slow WebService
    By Laurel in forum ASP.NET Web Services
    Replies: 1
    Last Post: October 21st, 02:35 PM
  5. Extremely slow wireless connection over 802.11b
    By Tony Clark in forum Windows Networking
    Replies: 0
    Last Post: July 17th, 07:26 AM

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