Home » Posts tagged 'Oracle'

Tag Archives: Oracle

Un-official Project SAP OSDB Migration done !!


Satu lagi tanggungan gw tahun ini bisa diselesaikan.

Sejak sister company (yang dulu gw maintain) pindah ke hyperconverged Nutanix dan menggunakan Acropolis sebagai hypervisornya, ada satu sistem SAP R/3 4.7 ext 200 yang berjalan di mesin HP rx4640 dengan OS HPUX 11.23 dan database server Oracle 9.2 yang harus dimigrasikan ke mesin Nutanix. Mesin HP rx4640 sudah tidak diperpanjang maintenance supportnya. Sedangkan secara sistem, SAP R/3 4.7 ext 200 ini juga sudah tidak disupport oleh SAP baik instance SAPnya maupun database servernya.

Target menggunakan OS Linux SLES 11 SP 4 dan database server DB2 9.7 FP 5.

Secara garis besar proses migrasi dilakukan sebagai berikur :

  1. Export data-data yang ada di SAP R/3 4.7 ext 200 di mesin HPUX source
  2. Install database server DB2 9.7 di Linux target
  3. Install Central Instance di Linux target
  4. Install Database instance di Linux target dengan memilih System Copy dan hasil export (proses no.1) sebagai data yang diimport.

Proses export berjalan dengan lancar. Problem mulai muncul saat proses no.4 diantaranya :

  1. Proses database load sempat terhenti karena filesystem sapdata penuh. Solusi : extend partisi sapdata dan filesystemnya
  2. Proses database load sempat terhenti lagi karena filesystem online archive log penuh. Solusi : extend partisi dan tambahkan jumlah file LOGARCHIVE second.

Setelah proses import selesai, masih ada masalah lagi yang muncul, yaitu SAP kernel 6.40 yang digunakan tidak mengenali kernel linux 3.0 (yang digunakan SLES 11 SP 4) serta database DB2 9.7. Solusinya adalah download patch SAP kernel 6.40 EX2 versi terakhir (version 414).

Project un-official SAP OSDB migration done by un-certified Basis like me¬† ūüėÄ

Oracle 11g index key compression for SAP R/3 4.7 almost completed


Setelah akhir bulan April 2012 lalu gw tuntasin pekerjaan untuk upgrade database Oracle dari 10g (10.2.0.4) menjadi 11g (11.2.0.2), maka sejak akhir bulan April dimulailah pekerjaan untuk melakukan kompresi. Index key compression menjadi pekerjaan yang pertama dilakukan.

Dimulai dari beberapa index yang kecil lalu menjangkau index yang lebih besar. Dan sampai hari kemaren (22 Mei 2012) hampir semua index (yang major dan digunakan) sudah terkompress. Masih tersisa 2 index terbesar (ACCTCR~0 dan VBOX~0) yang belum terkompress, index-index tabel yg tidak boleh dikompres seperti index milik tabel CDHDR, CDCLS, VBDATA, dll.

Proses index compression ini sendiri menggunakan tools dari SAP, BRTOOLS 7.20 patch 13 (patch terakhir pada saat tulisan ini dibuat adalah patch 23) dan dilakukan secara online. Hasil dari index key compression sendiri memberikan freespace yang cukup besar, yaitu lebih dari 1,6 TB. Dari sisi performance sendiri terlihat peningkatan performance query yang sangat signifikan karena extent dan block menjadi lebih kecil, penggunaan data buffer menjadi lebih efisien dan bahkan tidak ada peningkatan konsumsi memory dan CPU.

Secara umum, gw sangat satisfied dengan performance dari index key compression di Oracle 11g ini

Rencana upgrade Oracle 9i (9.2.0.7) ke Oracle 11g (11.2.0.3)


Berbekal manual dokumentasi dari SAP (untuk upgrade Oracle 9i ke Oracle 10g dan upgrade Oracle 10g ke Oracle 11g) dan pengalaman melakukan upgrade Oracle 9i ke Oracle 11g di server testing AIX 5.3, akhirnya diputuskan bahwa gw disuruh melakukan upgrade di mesin Dev, QA, dan Production.

Rencana upgrade ini mesti didesain sungguh-sungguh dan penuh perhitungan karena menyangkut server production yang menjadi jantung sistem ERP SAP R/3 4.7 di company gw. Artinya gw mesti mempertaruhkan dan mengerahkan semua kemampuan dan knowledge gw pada project ini.

Perkiraan waktu untuk upgrade dilakukan pada minggu ke-2 atau ke-3 bulan Februari tahun 2012 ini.

Karena project ini sangat krusial, maka gw udah buru-buru membereskan pekerjaan instalasi server DEV dan QA SAP ERP 6.0 with EHP5 milik company yg lain (yg menjadi tanggung jawab gw juga). Gw mau full konsentrasi mempersiapkan tahapan upgrade ini.

Well, semoga project ini berjalan lancar.

Performance Tuning database server Oracle 9i….Damn, I’m Good !!


Sudah 2 minggu terakhir ini gw berkutat dengan performance tuning untuk database server. Database server sistem ERP SAP R/3  4.7 mengalami degradasi performance. Alhasil, gw di-komplain kanan kiri dah sama user-user.

Solusi pertama adalah gw cek di penggunaan data buffer cache. Gw coba menaikkan db_cache_size dari 7 GB menjadi 10 GB. Ternyata cara ini tidak menolong sama sekali.

Gw coba cara lain yaitu menaikkan parameter pga_aggregate_target untuk melihat respon proses-proses yang dilakukan user. Size pga_aggregate_target sebelumnya 4 GB menjadi 6 GB. Cara ini lumayan meningkatkan performa walaupun tidak signifikan.

Gw sempet hopeless….dah…

Akhirnya puter-puter cari info untuk checking-checking performance. Dilihat dari hasil statistik gw melihat bahwa akses ke disk sangat tinggi dan bahkan penggunaan CPU ikut-ikutan naik dan memperparah performance sistem. Akhirnya gw coba melokalisir beberapa table untuk dilakukan cek statistics baik table maupun indexnya. Beberapa table yang sering diakses serta table-table yang besar gw lakukan update statistics. Update statistics ini dilakukan pada malam hari. Untuk update statistics ini gw sangat tertolong dengan whitepaper SAP untuk CBO Oracle. Proses update statistics gw lakukan secara estimate dan mengambil sample size tergantung jumlah row dari table tersebut.

Cara-cara ini ternyata berhasil menyelesaikan problem gw. Gw udah terima acknowledgement dari end user bahwa performa sistem kembali ok dan semakina ciamik.

Damn, I’m good !! (dalam hati gw)..

List of all Oracle Server Parameters


for Oracle 9i and 10g, Green = New in 10g

 Grey  = Valid in 9i but dropped (or hidden) in 10g
        Bold  = Static Parameter - change in Pfile/SPfile
        Normal= Dynamic Parameter - change in Pfile/SPfile/ALTER SYSTEM/SESSION

   PARAMETER                       DESCRIPTION
  ------------------------------  ----------------------------------------
   ACTIVE_INSTANCE_COUNT  = int   Active instances in the cluster
   AQ_TM_PROCESSES = int          Number of AQ Time Managers to start
   ARCHIVE_LAG_TARGET = int       Max no. seconds of redos the standby could lose
 asm_diskgroups = string        Disk groups to mount automatically
   asm_diskstring = string        Disk set locations for discovery
   asm_power_limit = int          Number of processes for disk rebalancing
   AUDIT_FILE_DEST = 'directory'  Directory in which auditing files are to reside AUDIT_SYS_OPERATIONS = {TRUE|FALSE} AUDIT_TRAIL = {NONE | FALSE | DB | TRUE | OS} Enable system auditing 9i
 AUDIT_TRAIL = {NONE | DB | DB_EXTENDED| OS} Enable system auditing 10g
 BACKGROUND_CORE_DUMP = {PARTIAL | FULL} 
   BACKGROUND_DUMP_DEST = 'path or directory'

(more…)

Rebuild Index dan Alter Move Table


Dalam beberapa minggu terakhir ini gw cukup banyak berkutat dengan kasus index Oracle yang dalam state “UNUSABLE”. Awalnya juga heran kenapa bisa terjadi demikian. Setelah index-index itu dibenerin, gw mulai mencari-cari cara untuk mendapatkan informasi yang lebih jelas dan akurat.

Barusan baca ebook dari Rampant Techpress (milik jagoannya Oracle, yaitu Mr. Donald Burleson), ternyata ada informasi (yang dishare oleh Mike Hordila) bahwa sebagai rule of thumb adalah selalu melakukan rebuild index apabila kita telah melakukan rebuild table. Jadi jika kita melakukan rebuild dan compress index, maka kita diharuskan melakukan rebuild index (milik tabel yang bersangkutan). Karena jika tidak dilakukan maka index tersebut akan menjadi dalam state “UNUSABLE”.

Weleh…weleh…mesti dingat-ingat neeh…

Morale Story : “Lakukan rebuild index apabila kita melakukan rebuild table

Script untuk rebuild index (kasus ora-01502)


Seperti postingan gw sebelumnya (), berikut adalah script untuk melakukan rebuild index-index yang berada dalam state “UNUSABLE“. Script kecil¬† sql sebagai berikut :

select 'alter index '||owner||'.'||index_name||' rebuild;' from
dba_indexes where STATUS = 'UNUSABLE';

Berikut adalah hasilnya :

‘ALTERINDEX’||OWNER||’.’||INDEX_NAME||’REBUILD;’
——————————————————————————–
alter index SAPWPR.ACCTIT~Z01 rebuild;
alter index SAPWPR.S514~VAB rebuild;
alter index SAPWPR.TCJ_POSITIONS~0 rebuild;
alter index SAPWPR.S055~VAB rebuild;
alter index SAPWPR.S055~0 rebuild;
alter index SAPWPR.GLPCA~7 rebuild;
alter index SAPWPR.GLPCA~3 rebuild;
alter index SAPWPR.GLPCA~2 rebuild;
alter index SAPWPR.FMEP~W rebuild;
alter index SAPWPR.FMEP~V rebuild;
alter index SAPWPR.FMEP~R rebuild;

‘ALTERINDEX’||OWNER||’.’||INDEX_NAME||’REBUILD;’
——————————————————————————–
alter index SAPWPR.FMEP~M rebuild;
alter index SAPWPR.FMEP~K rebuild;
alter index SAPWPR.ACCTIT~1 rebuild;
alter index SAPWPR.ACCTIT~0 rebuild;

15 rows selected.

Tinggal modifikasi sedikit menjadi seperti berikut :

alter index SAPWPR.”ACCTIT~Z01″ rebuild online compress;
alter index SAPWPR.”S514~VAB” rebuild online compress;
alter index SAPWPR.”TCJ_POSITIONS~0″ rebuild online compress;
alter index SAPWPR.”S055~VAB” rebuild online compress;
alter index SAPWPR.”S055~0″ rebuild online compress;
alter index SAPWPR.”GLPCA~7″ rebuild online compress;
alter index SAPWPR.”GLPCA~3″ rebuild online compress;
alter index SAPWPR.”GLPCA~2″ rebuild online compress;
alter index SAPWPR.”FMEP~W” rebuild online compress;
alter index SAPWPR.”FMEP~V” rebuild online compress;
alter index SAPWPR.”FMEP~R” rebuild online compress;
alter index SAPWPR.”FMEP~M” rebuild online compress;
alter index SAPWPR.”FMEP~K” rebuild online compress;
alter index SAPWPR.”ACCTIT~1″ rebuild online compress;
alter index SAPWPR.”ACCTIT~0″ rebuild online compress;

Bisa juga dijadikan script sql (misal buat file rebuild_index.sql) dan berisi seperti berikut :

sqlplus ‘/as sysdba’
spool /oracle/WPR/index_rebuild_log.txt
alter index SAPWPR.”ACCTIT~Z01″ rebuild online compress;
alter index SAPWPR.”S514~VAB” rebuild online compress;
alter index SAPWPR.”TCJ_POSITIONS~0″ rebuild online compress;
alter index SAPWPR.”S055~VAB” rebuild online compress;
alter index SAPWPR.”S055~0″ rebuild online compress;
alter index SAPWPR.”GLPCA~7″ rebuild online compress;
alter index SAPWPR.”GLPCA~3″ rebuild online compress;
alter index SAPWPR.”GLPCA~2″ rebuild online compress;
alter index SAPWPR.”FMEP~W” rebuild online compress;
alter index SAPWPR.”FMEP~V” rebuild online compress;
alter index SAPWPR.”FMEP~R” rebuild online compress;
alter index SAPWPR.”FMEP~M” rebuild online compress;
alter index SAPWPR.”FMEP~K” rebuild online compress;
alter index SAPWPR.”ACCTIT~1″ rebuild online compress;
alter index SAPWPR.”ACCTIT~0″ rebuild online compress;
spool off
exit

Selamat mencoba !!

Perbandingan antara VMware vSphere 4.1/5.0 dan Oracle VM 3.0


Teknologi virtualisasi berkembang sangat cepat. Di level enterprise terdapat cukup banyak pilihan untuk virtualisasi ini antara lain VMware vSphere 4.1 (terbaru adalah versi 5.0), Xen Enterprise (dari Citrix), Microsoft dengan Hyper-V, RHEV 2.2 (teknologi virtualisasi berbasiskan KVM dari Redhat. Saat ini sudah keluar versi 3.0 beta), Oracle VM 3.0 (teknologi virtualisasi berbasis kan Xen untuk arsitektur x86 dan Solaris VM untuk arsitektur Sparc dari Oracle), dan lain-lain.

Gw hanya akan menyoroti 2 produk, yaitu VMware vSphere 4.1/5.0 dari VMware dan Oracle VM 3.0 (dari Oracle). VMware vSphere 4.1/5.0 memang teknologi yang paling advanced saat ini karena memang untuk teknologi virtualisasi x86, VMware memang pioneer-nya. Bahkan untuk versi terbaru (5.0) mampu menangangi IOPS (Input Output Operation per Second) sampai 1.000.000. Hanya saja lisensi VMware memang terkenal mahal. Kalo tidak salah, sekitar US$ 5000-an lebih untuk per 2 prosesor. Software yang didownload dan diinstall hanya dapat dijalankan selama masa trial 60 hari.

Oracle VM 3.0 sendiri baru saja direlease oleh Oracle. Oracle meng-gadang-gadang bahwa Oracle VM ini sudah lebih advanced daripada versi sebelumnya 2.2.1. Oracle VM 3.0 bisa di-download secara gratis dan tidak ada masa trial seperti VMware vSphere. Lisensi yang diberikan adalah secara subscription. Artinya support didapatkan jika membayar lisensi. Mirip-mirip dengan lisensi support aplikasi-aplikasi open source seperti RHEL dan SLES. Hanya saja, gw blom mendapatkan informasi tentang maksimum IOPS yang bisa didapatkan dengan Oracle VM 3.0. Mungkin Anda punya info-nya, bisa dishare ke gw…

Oracle : ORA-01502 on SAP R/3 4.7 Ent + Oracle 11g


Setelah beberapa bulan yang lalu melakukan upgrade Oracle dari versi 9i ke 11g dan melakukan beberapa kali aktivasi advanced compression (dalam hal ini table compression dan index compression), team support melakukan testing pada beberapa fungsi seperti create Sales Order lewat VA01, dll.

Namun dalam testing sering kali terjadi error,yaitu ORA-01502. Lebih lengkapnya seperti ini :

“(FUNCTION)”, or declared in the procedure’s RAISING clause.
To prevent the exception, note the following:
Database error text……..: “ORA-01502: index ‘SAPWPR.FMEP~0’ or partition of
such index is in unusable state”
Internal call code………: “[RSQL/INSR/FMEP ]”
Please check the entries in the system log (Transaction SM21).

Beberapa index (sampai saat ini yang menjadi kasus adalah primary index, yaitu index yang mengandung primary key) mengalami state unstable. Solusi yang didapatkan adalah melakukan drop dan recreate index atau melakukan rebuild index. Karena index ini adalah primary index dari SAP maka tidak bisa dilakukan drop dan recreate index. Satu-satunya cara adalah melakukan rebuild index. Dan cara ini berhasil dengan baik…so far…

Oracle : Tuning PGA_AGGREGATE_TARGET in Oracle 9i


Tuning PGA_AGGREGATE_TARGET in Oracle 9i
Brian Peasland, Oracle Pipeline SYSOP
Abstract ‘
Oracle 9i introduced the PGA_AGGREGATE_TARGET parameter to help better manage session working areas in a session’s Program Global Area (PGA). This paper discusses available methods to help tune this new Oracle 9i parameter.
Introduction
Each Oracle session needs memory set aside for it to perform certain work operations. For instance, if the application requests a sorting operation by using certain SQL statements, like GROUP BY or ORDER BY, the application’s session can perform this sort in memory, provided enough memory has been reserved for that sort operation. If there is not enough reserved memory, then the sort operation is done in pieces using a temporary holding area on disk in the TEMP tablespace.
Before Oracle 9i, the DBA configured a session’s working areas by configuring a number of parameters such as BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, HASH_AREA_SIZE, and SORT_AREA_SIZE. Collectively, these parameters are referred to as the *_AREA_SIZE parameters. The problem with this approach is that one often reserved memory that was never used. If I set SORT_AREA_SIZE=1M and HASH_AREA_SIZE=5M, then I have allocated at least 6MB of working area. What if I needed 2MB for sorting and I am not going to perform any hash joins? I have 5MB of allocated working area that is sitting there idle that I would like to use to assist my sort operation. The pre-9i Oracle DBA had to carefully determine how to allocate the best working area sizes so that optimal performance could be achieved, without using up too much of the server’s physical memory.
To address this type of situation, Oracle created a way to let the instance automatically manage the working areas of the database sessions. Oracle 9i now has the ability to reserve a large chunk of working area space in memory and to let the instance dynamically change the working area allocations depending on the session’s operations. One session that needs 1MB of sort space and 4MB of hash area space would fit well into an allocation as defined above. A second session that needs 4MB of sort space and 1MB of hash area space would also fit well into a similar allocation since Oracle 9i now has the capability to dynamically change these working area allocations depending on the usage. Oracle 9i uses the PGA_AGGREGATE_TARGET initialization parameter to define the total amount of PGA reserved memory.
Initial Setup
To begin using this new Oracle 9i feature, you need to set the PGA_AGGREGATE_TARGET initialization parameter. This parameter can be set without restarting the Oracle instance. As a guideline, Oracle recommends initially setting this parameter to 16% of your server’s physical memory for OLTP systems and 40% of your server’s physical memory for DSS systems. Like any other memory configuration guidelines from Oracle Corp, this is just a starting place. You will most likely want to tune this setting depending on the usage of your system’s resources. My server has 1GB of physical memory. Using the above guidelines, I will initially set my PGA_AGGREGATE_TARGET initialization parameter. You can see an example of this in Figure 1.
Figure 1. ‚Äď Setting PGA_AGGREGATE_TARGET
ORA9I SQL> alter system set pga_aggregate_target=160M;
System altered.
ORA9I SQL> show parameter pga_aggregate_target
NAME                                 TYPE        VALUE
———————————— ———– —————————
pga_aggregate_target                 big integer 167772160
With this parameter set, Oracle will now automatically perform dynamic working area memory management.
Please note that the amount of memory set aside for the PGA_AGGREGATE_TARGET is for all server processes, not for each server process. This parameter can be set to zero to turn off dynamic working area memory management. The acceptable range of values is between 10MB and 4096GB ‚Äď 1.
Tuning PGA_AGGREGATE_TARGET
To illustrate how to tune this parameter, I will set my PGA_AGGREGATE_TARGET to the minimum value allowed, 10MB and run a load simulation on my instance. We want to determine when the PGA_AGGREGATE_TARGET is too low to give optimal performance and too high so as to not waste allocated memory.
Oracle 9i gives us many different views to query to see how well our dynamic working area memory management is performing. Oracle 9i has added a few statistics to V$SYSTAT and V$SESSTAT.
Figure 2. ‚Äď V$SYSTAT with 10MB PGA Target
ORA9I SQL> select name,value from v$sysstat
2>¬† where name like ‘workarea executions%’;
NAME                                          VALUE
—————————————- ———-
workarea executions Рoptimal                   510
workarea executions Рonepass                     1
workarea executions Рmultipass                   4
The query in Figure 2 shows us how many operations, or executions, were performed in the work areas. These executions fall into three categories. The optimal executions are those operations that were performed entirely in memory. As the name suggests, this is the most favorable type of execution. If the operation was too big to be performed in memory, then part of the operation spills onto disk. If only one pass was needed on disk, then this execution is noted in the onepass statistic. If more than one pass was needed on disk, then this execution is noted in the multipass statistic. Ideally, all executions should be in the optimal statistic and the statistics for onepass and multipass should be zero. From the query in Figure 2, I can see that my value for PGA_AGGREGATE_TARGET is too small.
Oracle 9i includes a new view called V$PGASTAT. This view can give you additional statistics on how well the dynamic working area memory management is performing.
Figure 3. V$PGASTAT with 10MB PGA Target
ORA9I SQL> select * from v$pgastat;
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter             10485760 bytes
aggregate PGA auto target                   4248576 bytes
global memory bound                          524288 bytes
total PGA inuse                             5760000 bytes
total PGA allocated                        10342400 bytes
maximum PGA allocated                      42925056 bytes
total freeable PGA memory                     65536 bytes
PGA memory freed back to OS                 9306112 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas          631808 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas        529408 bytes
over allocation count                          9201
bytes processed                            55100416 bytes
extra bytes read/written                  159971328 bytes
cache hit percentage                          25.61 percent
16 rows selected.
The first line of output in Figure 3 shows that my PGA_AGGREGATE_TARGET is currently set to 10MB. Some parts of the PGA are used for non-tunable information such as session context information and other overhead. The rest of the PGA memory footprint is dynamically tunable and is indicated by the aggregate PGA auto target statistic. The value in the second line of output of Figure 3 should not be significantly smaller than the value in the first line, as it is above. If this value is too small, then Oracle does not have enough memory to dynamically adjust.
Oracle 9.2 includes two additional rows of output to V$PGASTAT. They are over allocation count and cache hit percentage. If Oracle determines that it cannot honor the setting for PGA_AGGREGATE_TARGET, then it needs to allocate additional memory. The number of times Oracle detects this condition since instance startup is noted by the over allocation count statistic. Ideally, this value should be zero. The cache hit percentage statistic shows a hit ratio on the number of bytes where optimal executions were performed compared the total number of bytes for all executions, optimal, one-pass, and multi-pass. If all executions where optimal, then this statistic should be 100%.
It should be obvious from the queries in Figures 2 and 3 that the PGA_AGGREGATE_TARGET is under allocated. The question that remains is how much to increase this parameter to obtain optimal performance without wasting allocated memory? Oracle 9i includes a new V$PGA_TARGET_ADVICE view to help us answer this question. In order to use this view, one needs to ensure that the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL. Let’s look at Figure 4 to see what advice Oracle can give us if we change our PGA_AGGREGATE_TARGET to a different value.
Figure 4. ‚Äď V$PGA_TARGET_ADVICE with 10MB PGA Target
ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
2>              bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
3>              estd_pga_cache_hit_percentage as est_hit_pct,
4>              estd_overalloc_count as est_overalloc
5>        from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
————– ————— —————— ———– ————-
10        51766272           80197632          39             4
12        51766272           79773696          39             3
14        51766272           79773696          39             3
16        51766272           79773696          39             2
18        51766272           79773696          39             2
20        51766272           79773696          39             2
30        51766272           19943424          72             0
40        51766272           19943424          72             0
60        51766272           19943424          72             0
80        51766272           19943424          72             0
10 rows selected.
The output in Figure 4 shows us statistics that Oracle estimates would happen under our current workload if the PGA were changed to a different target size. Using this information, we should first attempt to reduce the estimated over allocations to zero. The first target size listed where this value is eliminated is 30MB. Notice that as we increase the target size, the estimated cache hit percentage increases, and the estimated number of bytes read and written in one-pass or multi-pass executions falls as well. Knowing that I need to increase this value, and looking at the target advice, I’m going to set PGA_AGGREGATE_TARGET to 80MB and restart the instance to clear all statistics. I’ll then rerun the same simulated load on the database.
I expect that I will still have some executions that will require reads and writes to disk since the read/write estimate show in Figure4 is non-zero for a 80MB PGA target. After restarting the instance and running the simulated load, I can see that this is true from the query in Figure 5.
Figure 5. ‚Äď V$SYSTAT with 80MB PGA Target
ORA9I SQL> select name,value from v$sysstat
2> where name like ‘workarea executions%’;
NAME                                          VALUE
—————————————- ———-
workarea executions Рoptimal                   511
workarea executions Рonepass                     4
workarea executions Рmultipass                   0
I have eliminated the multi-pass executions, but a few one-pass executions remain. Let’s now check the results in V$PGASTAT show in Figure 6.
Figure 6. ‚Äď V$PGASTAT with 80MB PGA Target
ORA9I SQL> select * from v$pgastat;
NAME                                          VALUE UNIT
—————————————- ———- ————
aggregate PGA target parameter             83886080 bytes
aggregate PGA auto target                  70290432 bytes
global memory bound                         4194304 bytes
total PGA inuse                             5790720 bytes
total PGA allocated                        10792960 bytes
maximum PGA allocated                      15750144 bytes
total freeable PGA memory                    196608 bytes
PGA memory freed back to OS                11403264 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas         4319232 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                             0
bytes processed                            47950848 bytes
extra bytes read/written                   39481344 bytes
cache hit percentage                          54.84 percent
We can see in Figure 6 that we have eliminated the over allocation count statistic. The auto target statistic is very close to the target parameter. So we are getting closer. But the cache hit percentage is still far away from 100% and there are a large number of extra bytes read and written. We will look for advice for our next setting in Figure 7.
Figure 7. V$PGA_TARGET_ADVICE with 80MB PGA Target
ORA9I SQL> select round(pga_target_for_estimate/1024/1024) as target_size_MB,
2  bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
3  estd_pga_cache_hit_percentage as est_hit_pct,
4  estd_overalloc_count as est_overalloc
5  from v$pga_target_advice;
TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
————– ————— —————— ———– ————-
10        45456384           84205568          35             2
20        45456384           78962688          37             0
40        45456384           19740672          70             0
60        45456384           19740672          70             0
80        45456384           19740672          70             0
96        45456384           19740672          70             0
112        45456384           19740672          70             0
128        45456384           13095936          78             0
144        45456384           13095936          78             0
160        45456384           13095936          78             0
240        45456384           13095936          78             0
320        45456384                  0         100             0
480        45456384                  0         100             0
640        45456384                  0         100             0
The query output in Figure 7 shows that we can eliminate the extra read and write bytes if we allocate 320MB to the PGA_AGGREGATE_TARGET. We can also hit our 100% cache hit ratio. We can further see that allocating 640MB or even 480MB of memory would not help us achieve any better performance. These settings would waste memory. Our next step would be to change the PGA_AGGREGATE_TARGET parameter and then perform the same queries at regular intervals to ensure that we are achieving optimal performance.
Conclusion
The benefit of letting Oracle 9i dynamically manage your working area memory is a great tool for the DBA to employ. The DBA does not have to worry about setting each of the *_AREA_SIZE parameters correctly. New statistics to V$SYSTAT and the new V$PGASTAT and V$PGA_TARGET_ADVICE views assist the DBA in determining if this parameter is not set correctly and the best setting for the PGA_AGGREGATE_TARGET parameter.
References

Oracle 9i Database Reference
Metalink Note: 148346.1 Oracle 9i Monitoring Automated SQL Execution Memory Management
Metalink Note: 223730.1 Automatic PGA Memory Management in 9i