-
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..?
-
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
-
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.
-
What do the stored procedures do?
Which part of the procedures is slower than before?
-
These two stored procedures are batch jobs.
It selects records from multiple tables, process the data and insert into only one table.
-
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)
-
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..?
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|