Results 1 to 9 of 9
  1. #1
    Join Date
    May 2015
    Posts
    4

    Unanswered: database 9i gone extremely slow over night...help please

    Hi all, I am no DBA so please be gentle.. I have a 9i 9.2.0.1.0 and literally I added an additional 12 columns to my Table (my table now has 263 columns and 880,000 records) and the very next day after turning on my stand alone windows machine a script that would usually take 2minutes is now taking 2 hours. I am extremely concerned I have checked table spaces and increased all table spaces to have at least 40% free this has not affected matters, still dog slow.. My windows machine is XP service pack 3, (I hear you all gasp). Please can anybody give me some assistance in getting my antiquated system running back at full speed..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does query use CBO or RBO?

    You can do as below prior to running the query.

    ALTER SESSION SET SQL_TRACE=TRUE;

    which will produce a trace file.

    You then need to process the trace file using "TKPROF" while including the parameter EXPLAIN=username/password

    Post the resultant file content back here
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Do you gather statistics on a regular schedule. Especially since you added the new columns?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    statistics are ignored by the RBO
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by anacedent View Post
    statistics are ignored by the RBO
    I know but he never indicated if the database is running RBO or CBO
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    May 2015
    Posts
    4

    Explain plan

    Thanks guys for such a prompt response, sorry it's taken me a long time to come back with a response to your request. I struggled to work out what I had to do. I think I'm using cost based.

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------

    | Id | Operation | Name. | Rows. | Bytes. | Cost |
    -----------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT. | | 880K. |. 10M.| 491. |
    | 1. | INDEX FAST FULL SCAN. | IND_HRS_NAME. | 880K. |. 10M | 491. |
    ------------------------------------------------------------------------------------------------------------

    Note: cpu costing is off

    9 rows selected

    Thank you so much for your time and patience
    Kind regards

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    if you are running CBO then gather statistics, It makes a very big difference in the optimizer.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2015
    Posts
    4

    Stats gathering

    Hi, I have gathered statistics but it does not seem to make a difference. The speed Is still ridiculously slow.
    Kind regards

  9. #9
    Join Date
    May 2015
    Posts
    4

    optimizer settings

    SQL> show parameter optimizer

    NAME TYPE VALUE
    ------------------------------------ ----------- -------
    optimizer_dynamic_sampling integer 1
    optimizer_features_enable string 9.2.0
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    optimizer_max_permutations integer 2000
    optimizer_mode string CHOOSE

    I don't know if these settings help, in any way....cheers

Posting Permissions

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