DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Oracle9i(9.2) is slower than Oracle 8i(8.1.7)

  1. #1
    Join Date
    Jul 2002
    Posts
    205

    Oracle9i(9.2) is slower than Oracle 8i(8.1.7)

    I have couple of Stored Procedures. Which I run on Oracle 8.1.7(32 bit) Sun Solaris 2.8. It has 1.2 GB of SGA.

    In the same machine I removed Oracle 8.1.7, installed Oracle 9i(9.2 64 bit, created the database. Kept TEMP space same as earlier also UNDO tablespace also same as earlier. Imported the data. The SGA I kept is 3.5 GB. The taotal RAM in the machine is 12 GB.

    Now I run the same stored procedure in Oracle 9i. One of the stored procedures take 6 minutes more and other 10 minutes more.

    Any idea..?

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    What was the total run time? 6 or 10 minutes more than ...?

    Was the procedure pinned as part of a package in 8.1.7?
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Jul 2002
    Posts
    205
    1) The stored procedures were not pinned, in the shared pool.
    2) The 1st Stored procedure was running around 15 minutes in Oracle 8.1.7 , now it is taking around 21 minutes in Oracle 9i. The 2nd one was taking 26 minutes now it is taking around 36 minutes.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What do the stored procedures do?

    Which part of the procedures is slower than before?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2002
    Posts
    205
    These two stored procedures are batch jobs.

    It selects records from multiple tables, process the data and insert into only one table.

  6. #6
    Join Date
    Dec 2002
    Location
    USA
    Posts
    53
    Could you check
    1) compute statistics done in 9i db ?
    2) AUTO PGA management setting in 9i?
    3) v$sql_plan to check current execution plan and to compare privious one (if you had in 8i)

  7. #7
    Join Date
    Jul 2002
    Posts
    205
    Could you check
    1) compute statistics done in 9i db ?
    Done for the schema not for the whole database.
    2) AUTO PGA management setting in 9i?
    No.
    3) v$sql_plan to check current execution plan and to compare privious one (if you had in 8i)
    The cost based optimizer is on. There are lot of SQLs inside the stored procedure. For each SQL I need to see if the execution plan is same. But do you anticipate the execution plan will changes in 9i automatically..?

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    From 9.0.1 upwards it's recommended to gather stats for the SYS schema also.

    It is possible that the execution plans are different between the two versions as the optimizer has changed. You need to investigate how the new features may affect your processing, for instance you may be using indexes that weren't previously being used (index skip scanning: http://www.oracle-base.com/Articles/...ipScanning.asp) which could reduce performance in your batch process.

    I'm afraid the only sensible approach is to start using TKPROF and v$sql_plan to chack the execution plans.

    I would consider using the new features for automatic undo management:

    http://www.oracle-base.com/Articles/...Management.asp

    and automatic PGA management:

    http://www.oracle-base.com/Articles/...nagement9i.asp

    I've heard people say different but I've found these perform as well or better than manual under 8i and it's one less thing to worry about.

    Good luck
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by TimHall
    From 9.0.1 upwards it's recommended to gather stats for the SYS schema also.
    That one is a news to me. Tim, can you provide any reference that would confirm this?

    I've searched the documentation and various Oracle related sites but could not find anything that would support this claim. On the contrary, I've found many warnings against gathering statistics for the data dictionary, valid still for 9i. And there were also some *very logical* explanations as to why SYS objects still shouldn't be analyzed. BTW, DBMS_STATS.GATHER_DATABASE_STATS procedure indeed has a parameter GATHER_SYS, however it default's to FALSE! I would imagine they would rurn it to TRUE if it realy was "recommended to gather stats for the SYS schema also".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    in note 35934.1 it says you can analyze SYS in 9i but the deadlock issue is still not fixed

Posting Permissions

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


Click Here to Expand Forum to Full Width