I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)
We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.
I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.
We needed “something” to hold a copy of the EBS instance while it was being virtualized.
So, I cloned this machine & before continuing let me describe what this is hardware is like:
Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 1 Core(s) per socket: 4 Socket(s): 2 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 23 Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz Stepping: 6 CPU MHz: 1992.000 BogoMIPS: 4987.29 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 6144K NUMA node0 CPU(s): 0-7
[root@erp-bak ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sdj2 59G 12G 44G 22% / tmpfs 9.7G 3.9M 9.7G 1% /dev/shm /dev/sdj1 2.0G 325M 1.5G 18% /boot /dev/sdj5 738G 531G 170G 76% /u01 /dev/mapper/mpathbp1 591G 320G 242G 57% /u02 /dev/mapper/mpathcp1 197G 38G 150G 20% /u03 /dev/sdf1 591G 70M 561G 1% /u04 /dev/sdi1 269G 59M 256G 1% /u05
in addition to the local disks this server have partitions mounted from a IBM SAN.
Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)
I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.
Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.
While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.
Few of the persistent issues were:
After a cold boot
- The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
- It takes ages for to open the concurrent programs window
- Our custom forms & LOVs lag to extremes and so on..
Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!
Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…
I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye
I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!
I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!
Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:
Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.
Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)
I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!