Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    99

    Unanswered: replay sql to oracle database.

    Gents,

    I'm using a 10grel2 database, what I'm trying to achieve is to trace the database based on the production traffic (sql statements)...I would then like to re-play the generated trace files back to the database.

    The goal is to replay the trace file back to the database after restart, ie to attempt to fill the buffer cache before allowing users back on the system.

    One of the problems encountered during a database restart is heavy I/O wait due to index scans etc of very large tables/indexes for at least 10-20 mins after restart (10-50% I/O wait). This is due to heavy concurreny of the application ie. 200 active sessions attempting to query the database every 2 seconds. Once the cache is partially filled and sql statements parsed the I/O is eventually reduced to 0.x%.

    any suggestions welcome!

    thanks.

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Quote Originally Posted by zaki_mtk
    ...heavy I/O wait due to index scans etc of very large tables/indexes for at least 10-20 mins after restart (10-50% I/O wait).
    Hoy many records aproximately?
    What about table/index partitioning?

  3. #3
    Join Date
    Jan 2004
    Posts
    99
    The two main tables which may be responsible for the I/O wait contain 300 million + records.....due to high amount of traffic concurreny causes I/O waits only for the first 10-20mins.

    partioning is not a option at the moment because the table structure has no real partitioning key column.

    Any suggestions about replaying back trace would be useful.

    thanks.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How about switching auditing on (will be some overhead) to catch all the sql in aud$. The just select the queries and stick them in a text file which you could run though sqlplus (set feedback off etc). Remember to switch auditing off afterwards .

    Alan

    P.S. as for partitioning does the table have a PK as that usually helps with coming up with a partitioning column/s.

  5. #5
    Join Date
    Jan 2004
    Posts
    99
    thanks Alan, I tried that the only downside is the bind variables are stored in a seperate column.....would be nice to convert a trace file and feed it straight back as .sql file.

    cheers.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    OK if your queries are generally selecting from the 2 large tables, if your cache is big enough, could you select * from your two tables using sqlplus to preload your caches.

    Alan

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your solution makes no sense. You have the following two scenarios.

    1) Your uses get on as soon as they can and start being productive. During the first 20 minutes, they will run slowly while the cache is refilled.

    2) You lock your people out for 20 minutes while the cache is being programmatically refilled and they are completely unproductive during the first 20 minutes.

    Personally, I would prefer the first scenario. That being said, why are you shutting down your machine on a regular basis? If it is a production server, everything including backup can be done without shutdown. There should be no reason that you shutdown and lose your cache entries.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2004
    Posts
    99
    beilstwh,

    thanks for you suggestions:

    1) Your uses get on as soon as they can and start being productive. During the first 20 minutes, they will run slowly while the cache is refilled.

    --- yes that is the problem

    2) You lock your people out for 20 minutes while the cache is being programmatically refilled and they are completely unproductive during the first 20 minutes.

    -- yes we have another copy of the database they use....however the main problem is cache filling!!

    Planning to run the select * from both the large tables to fill as much in 20 mins as per alan's suggestions, will let you know the outcome.

    tah.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The main question is still, why is the production database being bounced? For example, our production database (2tb of storage) is bounced maybe twice a year for patches. and typically, we have very close to 100% cache utilization.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2004
    Posts
    99
    yes I agree the database should not be shutdown except for maintainance. This is a new oracle environment, the main reason why this is shut-down is due to hardware upgrades or changes to parameter values such as sga_max_size, session_cached_cursors which require a bounce.

    thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •