Professional Web Applications Themes

Oracle 8.1.7.4 Performance Issues - Oracle Server

Hi, I have a question about Oracle 8.1.7.4 performance, where I need to find the answer or solution. We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size. The db_block_buffer are in total 1,2 GB in size. When I run the regular queries on the database, the took approx. 0,05 to 0,4 seconds for each query. For my tests, I run the query frequently on the database and they took between 0,05 and 0,4 sec. That fine for me. In the next morning, ...

  1. #1

    Default Oracle 8.1.7.4 Performance Issues

    Hi,


    I have a question about Oracle 8.1.7.4 performance, where I need to find
    the answer or solution.

    We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with
    2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size.
    The db_block_buffer are in total 1,2 GB in size.

    When I run the regular queries on the database, the took approx. 0,05 to
    0,4 seconds for each query. For my tests, I run the query frequently on
    the database and they took between 0,05 and 0,4 sec. That fine for me.

    In the next morning, I ran the same queries and the first say 20 queries
    are totally slow, means times from 5 sec up to 20 seconds for each
    query. After the first 20-30 queries, the query time goes down to 0,05
    to 0,4 seconds.

    What is the reason for that. There were no queries in the night on the
    database.
    Only a online Backup was running. Is this a normal case, that when
    starting in the morning, the first queries take this long time ?
    Are there any parameters which I can check within Oracle configuration.


    Regards


    Burkhard Kiesel

    Burkhard Kiesel Guest

  2. #2

    Default Re: Oracle 8.1.7.4 Performance Issues

    Burkhard Kiesel <burkhard.kieselsiemens.com> wrote in message news:<3DFD733A.84080CD3siemens.com>...
    > Hi,
    >
    >
    > I have a question about Oracle 8.1.7.4 performance, where I need to find
    > the answer or solution.
    >
    > We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with
    > 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size.
    > The db_block_buffer are in total 1,2 GB in size.
    >
    > When I run the regular queries on the database, the took approx. 0,05 to
    > 0,4 seconds for each query. For my tests, I run the query frequently on
    > the database and they took between 0,05 and 0,4 sec. That fine for me.
    >
    > In the next morning, I ran the same queries and the first say 20 queries
    > are totally slow, means times from 5 sec up to 20 seconds for each
    > query. After the first 20-30 queries, the query time goes down to 0,05
    > to 0,4 seconds.
    >
    > What is the reason for that. There were no queries in the night on the
    > database.
    > Only a online Backup was running. Is this a normal case, that when
    > starting in the morning, the first queries take this long time ?
    > Are there any parameters which I can check within Oracle configuration.
    >
    >
    > Regards
    >
    >
    > Burkhard Kiesel
    You basically state here 'It doesn't work'
    Apart from that you don't provide too much clues.
    Your real problem however are your queries, not the database.
    You seem to have cranked up db_block_buffers to way beyond the
    threshold Oracle advises, in order to get performance. Oracle
    recommends sizing the SGA to not more than one third of RAM. Your SGA
    amounts to 60 percent. Your server must be heavily faulting.
    If you also have a default install of Solaris, you will have to cope
    with a file cache. There is no real distinction in Solaris between
    file cache and virtual memory. Backup is an I/O intensive operation,
    which *will* use the file cache.

    Before you start blaming Oracle instead of your application, you'll
    need to find out (using statspack and/or v$system_events,
    v$session_events and v$session_waits) *what* the database is waiting
    for.
    Do you have too many full table scans, too many sorts, inefficient
    queries with way too much logical I/O (your db_block_buffer setting
    clearly points to that)?Ultimately these are all factors which will
    saturate your disk.
    You would also need to run the top command on the server to see what
    the server is actually doing. Is the cpu loaded or is it just waiting
    for I/O.

    These are a few things which you would need to post, before anyone can
    give any sensible advice.

    Regards

    Sybrand Bakker
    Senior Oracle DBA
    Sybrand Bakker Guest

  3. #3

    Default Re: Oracle 8.1.7.4 Performance Issues

    "Burkhard Kiesel" <burkhard.kieselsiemens.com> wrote in message
    news:3DFD733A.84080CD3siemens.com...
    > Hi,
    >
    >
    > I have a question about Oracle 8.1.7.4 performance, where I need to find
    > the answer or solution.
    >
    > We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with
    > 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size.
    > The db_block_buffer are in total 1,2 GB in size.
    >
    > When I run the regular queries on the database, the took approx. 0,05 to
    > 0,4 seconds for each query. For my tests, I run the query frequently on
    > the database and they took between 0,05 and 0,4 sec. That fine for me.
    >
    > In the next morning, I ran the same queries and the first say 20 queries
    > are totally slow, means times from 5 sec up to 20 seconds for each
    > query. After the first 20-30 queries, the query time goes down to 0,05
    > to 0,4 seconds.
    >
    > What is the reason for that. There were no queries in the night on the
    > database.
    > Only a online Backup was running. Is this a normal case, that when
    > starting in the morning, the first queries take this long time ?
    > Are there any parameters which I can check within Oracle configuration.
    >
    Hi Burkhard,

    As always, the best thing to do is check out any specific waits for the
    sessions and see what the hold up might be.

    I would suspect that cached info is being lost and needs to be reloaded via
    the first few runs of these queries. This info could be sql areas that need
    to be repd and/or the data blocks that need to re-read from disk.

    Once everything is cached again, performance improves.

    Then again, it could just be Oracle needing a few fags and cups of coffee to
    get going in the morning ;)

    Cheers

    Richard
    >
    > Regards
    >
    >
    > Burkhard Kiesel
    >

    Richard Foote Guest

  4. #4

    Default Re: Oracle 8.1.7.4 Performance Issues

    Burkhard Kiesel wrote:
    > Hi,
    >
    >
    > I have a question about Oracle 8.1.7.4 performance, where I need to find
    > the answer or solution.
    >
    > We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with
    > 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size.
    > The db_block_buffer are in total 1,2 GB in size.
    >
    > When I run the regular queries on the database, the took approx. 0,05 to
    > 0,4 seconds for each query. For my tests, I run the query frequently on
    > the database and they took between 0,05 and 0,4 sec. That fine for me.
    >
    > In the next morning, I ran the same queries and the first say 20 queries
    > are totally slow, means times from 5 sec up to 20 seconds for each
    > query. After the first 20-30 queries, the query time goes down to 0,05
    > to 0,4 seconds.
    >
    > What is the reason for that. There were no queries in the night on the
    > database.
    > Only a online Backup was running. Is this a normal case, that when
    > starting in the morning, the first queries take this long time ?
    > Are there any parameters which I can check within Oracle configuration.
    >
    >
    > Regards
    >
    >
    > Burkhard Kiesel
    >
    My first guess is You hit a delayed block clean out.
    Something (batch?) or somebody did Heavy update on your tables in
    question. To speed up creating free buffers for the update, the
    db-writer wrote the blocks 'as is' from the buffer cache to disk. That
    is, blocks that are not quite clean and compact/organized as usual.
    The next process that reads those blocks from disk, has inherited the
    cleanup work, witch will give prolonged execute time and more cpu usage.

    rgds

    /Svend Jensen

    Svend Jensen Guest

  5. #5

    Default Re: Oracle 8.1.7.4 Performance Issues

    Burkhard Kiesel <burkhard.kieselsiemens.com> wrote in message news:<3DFD733A.84080CD3siemens.com>...
    >
    > In the next morning, I ran the same queries and the first say 20 queries
    > are totally slow, means times from 5 sec up to 20 seconds for each
    > query. After the first 20-30 queries, the query time goes down to 0,05
    > to 0,4 seconds.
    >
    You sure there isn't any stuff running at that time that
    uses up all memory and makes Oracle be paged out?
    Can't think of any reason for this slow-down early morning,
    other than Richard's suggestion of lack of coffee...

    Cheers
    Nuno Souto
    [email]nsoutooptusnet.com.au.nosp[/email]am
    Nuno Souto Guest

Similar Threads

  1. Flash Performance issues
    By reach4thelasers in forum Macromedia Flash Player
    Replies: 17
    Last Post: February 19th, 02:30 PM
  2. Performance issues when tunneling???
    By Foppen in forum Macromedia Flash Flashcom
    Replies: 2
    Last Post: October 26th, 07:33 PM
  3. CFMX7 Performance Issues
    By jeff_gombala in forum Coldfusion Server Administration
    Replies: 1
    Last Post: July 18th, 08:38 PM
  4. Performance issues
    By Marcus in forum Coldfusion Server Administration
    Replies: 4
    Last Post: February 28th, 07:21 PM
  5. InDesign CS performance issues
    By Jon_Harm@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: June 8th, 06:15 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