Thanks for the question, anji reddy.
Asked: January 27, 2017 - 6:44 am UTC
Last updated: January 27, 2017 - 8:53 am UTC
Version: 12.1.0.1.0
Viewed 1000+ times
You Asked
Dears,
We have been upgrading from 9i to 12c and trying to ensure 12c performs better than 9i as part of the upgradation process, so we selected 20 different queries, which covered multiple tables, joins, functions, and functionalities e.t.c., We have been collecting the elapsed time details for each of these queries in both 9i and 12c environments in 3 different cycles by reopening the session every time we execute a query. And averaging the elapsed time from these 3 cycles.
18 out of 20 queries try to retrieve, insert, update or delete less than 10 records. These 18 having elapsed time less than a second in both the environments. Other 2 queries retrieves 512 and 1641 records respectively and having elapsed time as more than a second in both the environments. 12 out of 20 queries performing better in 12c when compare to 9i as per the elapsed time. Query that returns 1641 rows performing better in 12c but query that returning 512 rows performing better in 9i.
SGA exist in 9i as
Total SGA 3.58 GB
Fixed size 0.71 MB
Variable size 1.19 GB
Database buffers 2.39 GB
Redo buffers 1.26 MB
SGA exist in 12c as
Total SGA 7.14 GB
Fixed size 3.48 MB
Variable size 4.34 GB
Database buffers 2.78 GB
Redo buffers 11.71 MB
Hardware specification differs as 12c exist in virtual machine but 9i exists in physical machine. External storage in 12c is "VMDK datastore mounted from an external NetApp storage connected via normal LAN. Throughput is 1GB/sec". External storage in 9i is "IBM RAID storage, connected with Fiber channel with throughput 'uknown' GB/sec". Cores, Ram, and other configurations are same in both the environments. patch set 12.1.0.0.0, and psu 12.1.0.1.8 applied to 12c.
9i and 12c got similar structure of tables and data remain same in both of these environments.
I would like to know the best approach to conclude 12c perform better than 9i. Is it fair to look at the elapsed time for those set of 20 queries and conclude 12c perform better since 12 out of 20 queries having low elapsed time in 12c or should we ensure 20 out of 20 queries having low elapsed time in 12c?
Thanks,
Anji
and Chris said...
There are a huge number of optimizations in the database engine between 9i and 12c. When it comes to raw processing 12c should come out faster. For example, Craig Shallahamer has a detailed comparison of buffer gets in 11g and 12c showing 12c coming out better:
http://shallahamer-orapub.blogspot.co.uk/2013/08/is-oracle-12c-faster-than-11g.html But benchmarks like this are sort-of irrelevant when it comes to to your application. The real question you should ask is:
Does 12c run my workload fast enough?
In the unlikely event all your queries are 0.1s slower on 12c, it probably doesn't matter.
If you have 1,000 queries in your application and 999 run 50% faster, that's great. But if the last one is business critical and 100 times slower your app may be unusable.
The point is, check as much of your application as you can. If any parts are slower, ask:
- Is the increased time tiny or acceptable to the end users?
- If no to the above, investigate why and take appropriate action.
There are a huge number of changes to the optimizer from 9i -> 12c. So it's likely that the slower queries in 12c are due to it choosing different (slower) plans. Check them to see if this is the case. If you need help doing this, read:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution https://oracle-base.com/articles/9i/dbms_xplan