Feeds:
Posts
Comments

Posts Tagged ‘oracle 10G’

Oleh : Stefanus Januar (1200952163)

Flashback
Teknologi flashback menyediakan feature baru untuk melihat dan mengembalikan data sesuai waktu yang diinginkan. Teknologi flashback merupakan revolusi untuk meperbaiki data secara mudah. Merupakan teknologi pada oracle yang memungkinkan penggunanya untuk melakukan recover dengan cepat dan mudah.

Gambar 1. Teknologi Flashback (Best & Billings, 2005, p 17-3)

Menurut Alapati, 2005 ,p 164, teknologi flashback hanya dapat digunakan pada saat terjadi logical corruption. Yang dimaksud dengan logical corruption adalah kesalahan user dalam memanipulasi data, misalkan terjadi ketidaksengajaan dalam menghapus sebuah table dan juga disebabkan oleh virus yang merusak  data secara logical. Yang dimaksud dengan physical corruption adalah kerusakan fisik pada database tersebut sehingga perlu menggunakan data backup dari media lain untuk memperbaiki database tersebut. Pada physical corruption , flashback tidak dapat memperbaikinya. Masing – masing teknologi flashback dapat diakses melalui enterprise manager. Teknologi flashback memiliki level-level tertentu yang memiliki dampak yang berbeda ketika digunakan, dapat dilihat pada gambar 1.

Database Administration Features
Masing-masing teknologi saling terkait satu sama lain dalam hal penggunaannya. Teknologi flashback terbagi  menjadi beberapa bagian yaitu :

  • Flashback Database
    Merupakan  teknologi untuk memperbaiki seluruh database sesuai waktu yang diinginkan.  Menggunakan Flashback log untuk memperbaiki data. Dalam hal ini ketika dilakukan flashback seluruh data dalam database berubah sesuai dengan parameter waktu yang digunakan. Dalam penggunaan flashback database , database harus dalam kondisi mount exclusive.
    SHUTDOWN DATABASE
    STARTUP MOUNT EXCLUSIVE
    FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
  • Flashback Drop
    Merupakan teknologi untuk mengembalikkan table yang telah dilakukan perintah drop . Menggunakan Recycle bin untuk memperbaiki data. Recycle bin adalah tabel data dictionary yang menampung informasi tentang tabel yang dilakukan drop. Terdapat 2 jenis recycle bin yaitu DBA recycle bin dan user recycle bin. Untuk menggunakan flashback drop biasanya digabungkan dengan perintah flashback tabel.
    FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;
  • Flashback Tabel
    Merupakan teknologi untuk mengembalikan tabel yang telah ada sesuai dengan parameter waktu yang digunakan. Menggunakkan undo data untuk memperbaiki data. Flashback tabel menyediakan cara kepada user untuk memodifikasi tabel dengan mudah dan cepat tanpa bantuan dari DBA selama user tersebut memiliki hak akses untuk melakukan flashback.    FLASHBACK TABLE hr.employees TO TIMESTAMP
    TO_TIMESTAMP(‘2005-05-05 05:32:00′,’YYYY-MM-DD HH24:MI:SS’);

Ketiga teknologi flashback tersebut , ketika dilakukan flashback maka akan mengubah data-data pada database.

Application Development Features
Menurut Adams & Paapanen, 2003, p 516 ,teknologi flashback menawarkan kapabilitas untuk melakukan perintah query pada data history untuk melakukan analisis. Berbeda dengan teknologi flashback database, drop , dan tabel , flashback time navigation tidak mengubah data pada database melainkan melakukan perintah query pada data history untuk dilakukan analisis untuk memperbaiki data sesuai dengan kebutuhan. Dengan demikian user dapat melakukan navigasi pada setiap perubahan data yang ada. Untuk melakukan navigasi memerlukan parameter waktu dalam bentuk TIMESTAMP atau SCN.
Terdapat 3 teknologi yaitu

  • Flashback Query : Melakukan query data pada data history pada waktu yang spesifik. Dilakukan dengan perintah select dengan tambahkan AS OF dan spesifikasikan waktu untuk menampilkan data.Menggunakan undo data untuk menampilkan data.

SELECT employee_id, salary FROM employees
AS OF TIMESTAMP t1
WHERE employee_id = 200

  • Flashback Versions Query : Melakukan query data history dengan parameter waktu mulai dan akhir , maka data akan ditampilkan berdasarkan versi-versi dari row tersebut. Sebagai catatan flashback hanya dapat menampilkan data yang sudah dicommit. Menggunakan undo data untuk menampilkan data.

SELECT versions_xid, salary FROM employees
VERSIONS BETWEEN TIMESTAMP t1 and t2
WHERE employee_id = 200;

  • Flashback Transaction Query : Merupakan tool yang dapat digunakan untuk melihat perubahan data berdasarkan level transaksi. Yang dimaksud dengan level transaksi adalah transaksi yang dilakukan oleh user sampai dilakukan commit. Menggunakan undo data untuk menampilkan data.

SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query

Ketiga flashback ini menampilkan data-data yang ada untuk dilakukan analisis. Perlu diperhatikan bahwa flashback sangat dipengaruhi oleh undo retention time. Parameter ini menentukan apakah data masih ada pada undo data, jika sudah lewat dari waktu yang ditentukan maka data tidak dapat dilakukan flashback. Secara default Oracle memiliki undo retention time sebesar 15 menit.

Flashback Versions Query
Flashback versions query menyediakan cara untuk melakukan audit data pada sebuah baris dari table dan mengambil informasi tentang transaksi yang mempengaruhi baris tersebut. Menurut Adams & Paapanen, 2003, p 525 ,dalam flashback versions query terdapat pseudocolumn yaitu :

  • VERSIONS_XID         :  Sebagai penanda pada transaksi.
  • VERSIONS_OPERATION     : Kolom ini berisi informasi tentang operasi yang dilakukan , apakah insert , delete , atau update.
  • VERSIONS_STARTTIME    : Kolom ini berisi tentang informasi kapan row tersebut dibentuk.
  • VERSIONS_ENDTIME    :  Kolom ini berisi tentang informasi kapan row tersebut expired.

Contoh Kasus
Select Pada Awal

Gambar 2. Hasil dari Select

Pertama lakukan perintah select salary untuk employee id yang 200, maka di dapat data Jennifer dengan salary sebesar 4400. Untuk lebih jelas perhatikan gambar 2.

Terjadi Kesalahan Update data

Gambar 3. Contoh Update

Setelah itu lakukan update data untuk employee id 200 , dengan salary 4600 , lalu commit kemudian lakukan update kembali untuk employee id  200 , dengan salary 4800 dan commit. Untuk lebih jelas perhatikan gambar 3.

Melakukan Analisis Data

Gambar 4. Contoh Analisis Data

Untuk memperbaiki data seperti gambar 3 , maka kita dapat melakukan flashback versions query untuk menganalisis kesalahan yang terjadi untuk melakukan perbaikan data. Contoh flashback versions query dapat dilihat pada gambar 4.

Untuk Mengambil Query

Gambar 5. Contoh pengambilan data

Setelah mendapatkan data salary yang ingin dikembalikan , untuk memperbaiki data tersebut ,kita dapat menggunakan tabel flashback transaction query. Dengan menggunakan tabel tersebut ,kita dapat dengan mudah memperoleh perintah sql untuk mengembalikan data tersebut . Pada gambar 5, ROWID merupakan kolom yang dibentuk oleh oracle secara otomatis pada setiap tabel yang dibuat oleh user, untuk melihat isi kolom tersebut dapat dilakukan perintah select ROWID. Setelah itu lakukan perintah tersebut untuk melakukan perbaikan. Gambar 6 merupakan contoh ketika kita melakukan flashback menggunakan enterprise manager.

Gambar 6. Contoh Flashbcak

Hambatan
Flashback versions query tidak dapat dilakukan pada hal-hal sebagai berikut :

  • External tabel : merupakan tabel yang digunakan untuk menyimpan data dari file lain. Misalkan kita memiliki file excel yang ingin dimasukkan pada oracle , maka oracle akan menyimpan file tersebut kedalam tabel external. Flashback versions query tidak dapat melakukan fungsinya pada external tabel.
  • Temporary tabel : merupakan tabel yang sifat nya hanya sementara. Tabel jenis ini digunakan pada transaksi yang rumit sehingga perlu dilakukan penyimpanan sementara. Flashback versions query tidak dapat melakukan fungsinya pada temporary tabel.
  • Fixed tabel : merupakan tabel yang ada pada oracle yang sudah didefinisikan oleh oracle untuk keperluan system. Flashback versions query tidak dapat melakukan fungsinya pada fixed tabel.
  • DDL Statement : flashback versions query tidak dapat dilakukan ketika struktur dari tabel berubah.
  • Shrink Operation : merupakan operasi yang dilakukan untuk mengurangi ukuran dari file oracle pada saat dilakukan delete. Misalkan ukuran tabel memiliki ukuran 10mb dengan pertumbuhan 2mb. Lalu dilakukan delete sehingga menghapus data sebesar 6 mb, namun ukuran tabel tetap 10mb sehingga perlu dilakukan operasi shrink sehingga ukuran tabel menjadi 4mb. Flashback versions query tidak dapat dilakukan jika operasi ini dijalankan.

Daftar Pustaka

  • Adams, D., & Paapanen, E. (2003). Oracle® Database Application Developer’s Guide – Fundamentals 10g Release 1 (10.1). California: Oracle Corporation.
  • Alapati, S. R. (2005). Expert Oracle Database. New York: Grace Wong.
  • Best, T., & Billings, M. (2005). Oracle Database 10g Administration Workshop I (Edition 3.0 ed.). California: Joseph Fernandez.

Read Full Post »

Oleh : Irfan Haryanto

Database grid, sebuah konsep yang sangat menarik yang telah diimplementasi oleh perusahaan Oracle mulai dari produknya Oracle 10g. Konsep ini merupakan sebuah perubahaan yang revolusioner dari Oracle dan merubah struktur Databasenya agar mendapatkan peningkatan performa yang signifikan serta menghemat pengeluaran.

Menurut Olofson (2008, p. 2) Grid computing merupakan sebuah istilah yang sudah diaplikasikan pada berbagai arsitektur yang didesain untuk memberikan keuntungan dari sebuah IT Grid. Ini adalah sebuah pendekatan pada computing yang memisahkan fungsionalitas sofware pada hardware tertentu dengan cara menghubungkan sistem dan storage pada resource yang dapat dialokasikan maupun dinonalokasikan contohnya pada database.

Grid Computing menggabungkan berbagai macam resource yang heterogen dihubungkan dengan jaringan bisa internet ataupun ethernet untuk menyelesaikan tugas tertentu. Istilah ini merupakan sebuah pendekatan untuk komputerisasi yang memudahkan administrator untuk menentukan tugas pada resource komputer yang diinginkan atau menentukan penempatan data pada storage yang diperlukan dengan cara tertentu sehingga data, resource dan tugas bisa dikurangi atau ditambahkan ketika diperlukan.

Konsep dari Grid computing ini bisa diibaratkan dengan konsep power grid atau tiang listrik. Misalnya pada sebuah distrik yang belum memiliki jaringan listrik ingin ditambahkan beberapa tiang listrik untuk menopang daya listrik tersebut, maka tiang listrik akan ditambahkan dan dihubungkan dengan jaringan listrik yang sudah berjalan namun sistem tiang listrik yang lama tidak akan terganggu atau terhenti ketika terjadi penambahan ini sama dengan Grid computing. Olofson (2008, p. 3) juga menyebutkan  Grid computing membuat resource yang berhubungan dapat dengan mudah ditambahkan dan dikurangi seperti aplikasi, database ataupun lingkungan storage semudah memindahkan energi dari tiang listrik.

Ada alasan mengapa Oracle mengeluarkan produk terbarunya 10g yang menggantikan produk sebelumnya yaitu 9i. Produk terbaru ini tidak lagi memakai logo “I” yang merupakan singkatan dari internet, tapi memakai logo baru yaitu “g” yang merupakan singkatan dari grid.

2 hal penting yang mendasari alasan ini adalah sebagai berikut :

  1. Low cost.
    Grid memungkinkan perusahaan untuk menambah atau mengurangi hardware dengan mudah sehingga tidak perlu lagi melakukan over-provisioning. Over provisioning dilakukan dengan melebihkan kapasitas hardware dari jumlah yang sebenarnya diperlukan dengan tujuan performa dan menghindari adanya penambahan hardware di masa mendatang. Dengan adanya grid computing maka over provisioning tidak perlu dilakukan sehingga tidak ada hardware yang terbuang percuma.
    Grid juga mengurangi biaya yang besar akibat dari penambahan hardware baru. Perusahaan – perusahaan biasanya menghabiskan banyak biaya karena penambahan ini sehingga banyak yang menghindari ini terjadi atau menundanya agar sekaligus merombak total sistem secara keseluruhan.
  2. Higher performance.
    Performa yang lebih tinggi didapat dari parallel processing, kemampuan grid untuk memproses beberapa tugas secara parallel.
Truth in Advertising – Advanced Data Compression in Oracle 11g. Sumber : (O’Mahony, 2010)

Ada sebuah iklan web dari Oracle yang menunjukkan keunggulan dari Grid computing. Pada gambar 1 dibandingkan 2 merk yang sekarang ini sudah tidak asing lagi di masyarakat luas yaitu Sun Oracle dengan IBM, Computer milik Oracle diklaim 7x lebih cepat menggunakan teknologi Grid sedangkan IBM membutuhkan 6x energi consumption yang lebih banyak.

Beberapa perusahaan besar memiliki pendekatan tersendiri dalam mengimplementasi grid computing. Pendekatan yang diambil umumnya dibedakan berdasarkan cara kerja sistem perusahaan dan tujuan yang ingin dicapai. Beberapa pendekatan yang umum dipakai antara lain :

  1. Compute Grid
    Grid ini digunakan untuk pencapaian kerja sebuah tugas yang berskala besar seperti superkomputer menggunakan hardware dengan biaya lebih murah dalam jumlah banyak yang bekerja secara parallel.
  2. Application Grid
    Grid ini digunakan untuk pencapaian hasil dan ketersediaan yang lebih tinggi dengan membagi aplikasi menjadi komponen – komponen yang terinstallkan pada beberapa server aplikasi dengan harapan ketika salah satu server sedang down maka server yang lain bisa menutupi.
  3. Storage Grid
    Storage dibagi atas beberapa disk dan diatur sedemikian rupa agar data dapat dikelola dengan optimal untuk meningkatkan ketersediaan dan skalabilitas . Hal ini bertujuan agar ketika ada keperluan untuk menambahkan dan mengurangi disk, database dan aplikasi tidak mengalami gangguan.

Diluar dari ketiga tipe diatas, ada konsep yang digunakan oleh Oracle yaitu Database Grid. Grid ini merupakan gabungan fitur dari Application Grid dan Storage Grid.

(Mahmood & Fernandez, 2005) menyatakan bahwa Database grid mengalokasikan servis dari beberapa nodes untuk menyediakan performa yang tinggi sehingga ketika salah satu node mati, grid dapat beradaptasi dengan menggunakan node lain untuk meresponnya.

RAC dan ASM merupakan fitur yang berperan penting untuk mendapatkan keuntungan dari database Grid. Olofson (2008, p. 4) berkata bahwa Oracle menyebut fitur yang memberikan kemampuan server cluster berbasis grid dengan Real Application Cluster atau RAC dan fitur yang memberikan kemampuan storage grid dengan Automatic Storage Management, ASM. Kedua fitur ini bisa didapat dengan menginstall Oracle Enterprise Manager’s Grid Control.

Oracle Enterprise Manager merupakan sebuah komponen dari Oracle yang berguna untuk pengaturan database dari Oracle. Komponen ini berbasis web dan dapat dijalankan dengan mengkakses browser seperti mozilla firefox atau internet explorer.

Database Control adalah tipe dari Oracle Enterprise Manager untuk mengontrol sebuah database namun untuk mengatur database yang sudah tersusun dalam konsep grid maka kita membutuhkan komponen Oracle Enterprise Manager’s Grid Control.

Database Grid merupakan konsep yang telah diimplementasi oleh Oracle yang mampu memanfaatkan fitur – fitur dari application grid dan storage grid seperti penambahan aplikasi dan storage yang heterogen dengan keunggulan mempermudah pengurangan dan penambahan hardware serta meningkatkan performa komputasi juga meningkatkan skalabilitas dan ketersediaan tanpa menganggu sistem yang sudah berjalan.

Q&A :

  1. Apa kelemahan dari konsep Grid Computing?
    Memerlukan koneksi network yang cepat seperti internet atau ethernet, beberapa aplikasi membutuhkan modifikasi agar dapat memanfaatkan grid computing secara sempurna, dan yang paling signifikan adalah, hasil dari semua proses dikirim pertama kali ke seluruh nodes dari grid baru digabungkan, sehingga tidak mungkin mendapatkan hasil akhir sebelum node yang terakhir ikut berkolaborasi. Namun disamping kekurangan dari konsep grid computing, kelebihan yang didapat dirasakan lebih dominan sehingga patut untuk diimplementasikan.
  2. Apakah konsep grid ini memungkinkan untuk penambahan hardware dengan merk berbeda?
    Sangat mungkin karena konsep grid bersifat heterogen sehingga bisa memakai hardware yang berbeda – beda untuk menjadi sebuah sistem computing.
  3. Apa itu enterprise grid? Apa yang membedakan enterprise grid dengan database grid?
    Enterprise Grid merupakan implementasi konsep grid Computing dengan skala yang besar sedangkan database grid merupakan sebuah pendekatan dari konsep Grid Computing.

Daftar Pustaka :

Mahmood, Z., & Fernandez, A. (2005). Building and Scaling Out Database Grid. Clustering Special Section Oracle 10g , 4.

Olofson, C. W. (2008). Grid Computing with Oracle Database . WHITE PAPER , 8.

O’Mahony, C. (2010, 05 20). Truth in Advertising – Oracle’s Claims about Performance and Energy Consumption. Retrieved 11 21, 2011, from Conor O’Mahony’s Database Diary: http://database-diary.com/

Read Full Post »

Oleh : Erik Wijaya (1200999330)

1. Pengertian
Seperti kita ketahui, banyak sekali fungsi-fungsi pada Oracle yang dapat membantu kita untuk mempermudah dalam melakukan query data dari database. Menurut Oracle Database:oracle, fungsi-fungsi tersebut adalah:
  • SQL Functions
  • Single-row Functions
  • Numeric Functions
  • Character Functions Returning Character Values
  • NLS Character Functions
  • Character  Functions Returning Number Values
  • Datetime Functions
  • General Comparison Functions
  • Conversion Functions
  • Large object Functions
  • Collection Functions
  • Hierarchical Functions
  • Data Mining Functions
  • XML Functions
  • Encoding and Decoding Functions
  • Null Related Functions
  • Environment and Identifier function
  • Aggregate Functions
  • Analytic Functions
  • Object Reference Functions
  • Model Functions

Tilisan ini akan secara lebih jauh akan membahas mengenai salah satu fungsi pada database Oracle di atas yaitu Analytic Functions.

Menurut Oracle Database:oracle dan  SQL:orablogger Inspired by oracle, Analytic Functions merupakan salah fungsi pada database Oracle yang berfungsi untuk menghitung nilai aggregate berdasarkan sekumpulan row yang dinamakan window. Fungsi ini sekilas hampir sama dengan aggregate function tetapi sebenarnya ada perbedaannya yaitu aggregate function dapat menghitung nilai aggregate dan hanya mengembalikan satu value atau row yang merupakan hasil dari pengolahan nilai beberapa value atau row yang ada sedangkan analytic function dapat menghitung nilai aggregate dan dapat mengembalikan lebih dari satu value atau row.

Sintaks Umum dari Analytic Functions adalah sebagai berikut:

            Function(arg1,…, argn) OVER ( [PARTITION BY <…>]
[ORDER BY <….>] )

Pada sintaks di atas terdiri dari:
1) 
Function(arg1,..,argn)  merupakan nama tipe fungsi dari Analytic Functions yang bisa memiliki argument.
2)
OVER merupakan bagian sintaks dari Analytic Functions yang menghubungkan antara nama tipe fungsi dengan klausa partition by dan klausa order by.
3)  Klausa PARTITION BY  (….) . Menurut (ORACLE SQL) klausa partition by  merupakan klausa yang mempunyai fungsi yang hampir sama dengan GROUP BY(….) pada Aggregate Functions  namun perbedaannya adalah klausa GROUP BY akan mengembalikan satu value atau row sedangkan klausa PARTITION BY akan mengembalikan lebih dari satu value atau row.
4)  Klausa ORDER BY untuk menampilkan hasil dari query dengan Analytic Functions dengan terurut baik secara ascending maupun descending.

2. Tipe Analytic Functions
Menurut Analytical, Analytic Functions dapat dibedakan menjadi 4 tipe yaitu:

a)      Ranking Functions
Fungsi utama dari Ranking Functions adalah untuk memberikan ranking berdasarkan hasil dari pengolahan data yang ditampilkan. Selain untuk menentukan ranking atau peringkat, pada fungsi ini juga terdapat fungsi lain yang juga menghasilkan angka namun bukan merupakan peringkat seperti misalnya nomor row, pembagian kelompok data, dan lain-lain.

Berikut merupakan beberapa contoh dari Ranking Functions:

1)      RANK()

Menurut  Oracle Reference:PSOUG.org, fungsi ini berguna untuk memberikan ranking namun bisa menyebabkan adanya ranking yang tidak berurutan.

  Sintaks:
RANK() OVER() (<opartition_clause><order_by_clause>)

Contoh:
SELECT d.department_name, e.last_name, e.salary ,
RANK() OVER(partition by e.department_id order by e.salary)
“RANK”
FROM employees e, departments d
WHERE e.department_id = d.department_id and
d.department_id in (30,60);

Gambar 1.1 Contoh fungsi RANK()

Pada contoh di Gambar 1.1 yaitu pada kolom ke-4 ditampilkan ranking atau peringkat pada tiap department_id yang diurutkan berdasarkan salary secara descending dengan menggunakan fungsi RANK() sehingga yang mendapatkan salary paling besar mendapatkan ranking 1 dan seterusnya. Fungsi ini akan memberikan ranking yang sama ketika terdapat value yang sama. Pada contoh di atas terdapat ranking yang sama yaitu ranking 3 yang disebabkan karena adanya value yang sama pada salary 4800. Pada row berikutnya, ranking yang diberikan adalah ranking 5 dan tidak ada ranking 4. Inilah yang dimaksudkan dengan fungsi rank bisa menyebabkan ranking yang tidak berurutan atau dengan istilah lain adanya gap antar ranking.

2)      DENSE_RANK()
Menurut  Oracle Reference:PSOUG.org, fungsi ini berguna untuk memberikan ranking dan tidak menyebabkan adanya ranking yang tidak berurutan.

Sintaks:
DENSE_RANK() OVER()
(<partition_clause><order_by_clause>)

Contoh:

SELECT d.department_name, e.last_name, e.salary,
DENSE_RANK() OVER(partition by e.department_id order
by e.salary) “DENSE_RANK”
FROM employees e, departments d
WHERE e.department_id = d.department_id and
d.department_id in (30,60);

Gambar 1.2 Contoh fungsi DENSE_RANK

Pada contoh di Gambar 1.2 yaitu pada kolom ke-4 ditampilkan ranking atauperingkat pada tiap department_id yang diurutkan berdasarkan salary secara descending dengan menggunakan fungsi DENSE_RANK() sehingga yang mendapatkan salary paling besar mendapatkan ranking 1 dan seterusnya. Fungsi ini akan memberikan ranking yang sama ketika terdapat value yang sama. Pada contoh di atas terdapat ranking yang sama yaitu ranking 3 yang disebabkan karena adanya value yang sama pada salary 4800. Pada row berikutnya, ranking yang diberikan adalah ranking 4 berbeda dengan fungsi RANK() yang memberikan ranking 5 sehingga bisa disimpulkan bahwa fungsi DENSE_RANK() tidak akan menyebabkan adanya ranking yang tidak berurutan atau dengan istilah lain tidak adanya gap antar ranking.

3)      ROW_NUMBER()

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menampilkan nomor row pada setiap row yang ditampilkan.

Sintaks:
ROW_NUMBER() OVER
           (<partition_clause> <order_by_clause>)

Contoh:
SELECT department_id, last_name, salary,
row_number() over(partition by department_id
order by salary) “ROW_NUMBER”
FROM employees
WHERE department_id = 80;

Gambar 1.3 Contoh fungsi ROW _NUMBER()

Pada contoh Gambar 1.3 pada kolom ke-4 ditampilkan angka-angka yang merupakan nomor row dimana row pertama mendapat nomor row 1 , row kedua mendapat nomor row 2 dan seterusnya sehingga lebih cocok bila kolom yang mengandung nomor row diletakkan pada kolom yang kiri sebagai kolom [nomor row].

4)      NTILE ( value )

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk membagi hasil data menjadi kelompok-kelompok data dengan jumlah kelompok data yang ditentukan pada parameter fungsi NTILE.

Sintaks:
NTILE(value) OVER (<partition_clause> <order_by_clause>)

Contoh:
SELECT department_id, last_name, salary,
NTILE(4)
OVER(partition by department_id order
by salary) “NTILE”
FROM employees
WHERE department_id = 80;

Gambar 1.4 Contoh fungsi NTILE(value)

Pada contoh Gambar 1.4 pada kolom ke-4 ditampilkan pembagian kelompok data berdasarkan fungsi NTILE. Pada contoh di atas, terdapat 34 row atau row data yang dibagi menjadi 4 kelompok data dengan pembagian kelompok 1 memiliki 9 anggota, kelompok 2 memiliki 9 anggota, kelompok 3 memiliki 8 anggota, dan kelompok 4 memiliki 8 anggota. Hasil pembagian ini didasarkan atas sisa hasil bagi atau modulus. Bila 34 row dibagi menjadi 4 kelompok data dengan jumlah anggota yang sama maka akan dihasilkan 4 kelompok dengan 8 anggota tetapi terdapat sisa hasil bagi 2 row. Angka 2 row ini berarti 2 kelompok atas yaitu kelompok 1 dan 2 akan ditambahkan 1 anggota lagi sehingga menjadi 9 , 9 , 8 , 8.

5)      PERCENT_RANK()

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk mengubah peringkat atau ranking dari fungsi RANK() menjadi angka persentase yang valuenya berada antara 0 dan 1.
Rumusnya adalah:
                        (n – 1) / (m – 1)
n = ranking berdasarkan fungsi RANK()
m = jumlah row

Sintaks:
  PERCENT_RANK() OVER (<partition_clause>
        <order_by_clause>)
Contoh:
SELECT department_id, last_name, salary,
PERCENT_RANK() OVER(partition by department_id
order by salary desc) “PERCENT_RANK”
FROM employees
WHERE department_id = 80;

Gambar 1.5 Contoh fungsi PERCENT_RANK()

Pada contoh Gambar 1.5 pada kolom ke-4 ditampilkan hasil pengubahan ranking menjadi persentase ranking dengan menggunakan fungsi PERCENT_RANK(). Sebagai contoh, pada row ke-2 bisa ditampilkan hasil .03030303 karena :

(n-1) / (m-1)  = (2-1)/(34-1) = 0.03030303

Catatan: ranking pada row ke-2: 2 , jumlah row: 34

b)      Window Functions

Contoh dari fungsi ini antara lain adalah SUM, MAX, AVG, COUNT, MIN yang biasanya sering dipakai pada Aggregate Function. Menurut Oracle Database:oracle dan  SQL:orablogger Inspired by oracle, perbedaannya adalah bila digunakan pada Aggregate Function maka fungsi di atas akan mengembalikan satu value atau row sedangkan bila digunakan pada Analytic Functions akan mengembalikan lebih dari satu value atau row.
Berikut merupakan contoh dari fungsi SUM dan AVG dengan menggunakan Analytic Functions:

1)      SUM (value)

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menghitung kumulatif value berdasarkan kolom yang ditentukan.

Sintaks:
SUM(value) OVER (<partition_clause> <order_by_clause>)

Gambar 1.6 Contoh fungsi SUM(value) dengan Analytic Functions

Contoh:
SELECT department_id, last_name, salary, sum(salary) over(partition by department_id order by department_id)     “SUM_SAL”        FROM employees        WHERE department_id = 80;

Pada contoh Gambar 1.6 di kolom ke-4 ditampilkan jumlah salary pada department_id yang bernilai 80.
Perhatikan contoh berikut dengan menggunakan Group By:

SELECT department_id, last_name, salary, sum(salary) “SUM_SAL”  FROM employees WHERE department_id = 80 GROUP BY department_id, last_name, salary;

Gambar 1.7 Contoh fungsi SUM(value) dengan Aggregate Functions

Pada Aggregate Function bila kita ingin menampilkan hasil yang sesuai dengan yang kita inginkan maka kita juga harus membatasi jumlah kolom yang ditampilkan . Pada gambar 1.7, jumlah salary yang ditampilkan bukan merupakan jumlah salary keseluruhan dari department_id yang bernilai 80 namun merupakan jumlah salary berdasarkan kolom department_id, last_name, dan salary.
Sedangkan pada gambar 1.6 walaupun ditampilkan department_id, last_name, dan salary  namun tetap bisa didapatkan jumlah salary pada department_id yang bernilai 80.

2)      AVG (value)

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menghitung rata-rata value berdasarkan kolom yang ditentukan.

Sintaks:
AVG(value) OVER (<partition_clause> <order_by_clause>)

Gambar 1.8 Contoh fungsi AVG(value) dengan Analytic Functions

Contoh:
SELECT department_id, last_name, salary, avg(salary)
over(partition by department_id order by department_id)
“AVG_SAL” FROM employees WHERE department_id = 80;

Pada contoh gambar 1.8 di kolom ke-4 ditampilkan rata-rata salary pada department_id yang bernilai 80.
Perhatikan contoh berikut dengan menggunakan Group By:

SELECT department_id, last_name, salary, avg (salary)
“AVG_SAL”  FROM employees WHERE department_id = 80
GROUP BY department_id, last_name, salary;

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;}

Gambar 1.9 Contoh fungsi AVG(value) dengan Aggregate Functions

Sama seperti contoh fungsi SUM pada contoh sebelumnya, rata-rata salary yang ditampilkan pada gambar 1.9 bukan merupakan rata-rata salary dari department_id yang bernilai 80 tetapi merupakan rata-rata salary

berdasarkan kolom department_id, last_name, dan salary.
Sedangkan pada gambar 1.8 walaupun ditampilkan department_id, last_name, dan salary namun tetap bisa didapatkan rata-rata salary pada department_id yang bernilai 80.

c)      Reporting Functions
Fungsi ini bisa disebut sebagai fungsi yang melaporkan hasil pengolahan data yang telah ditampilkan.
Salah satu contoh dari fungsi ini adalah RATIO_TO_REPORT(value) yang menurut  Oracle Reference:PSOUG.org, berguna untuk membandingkan value sebuah row dengan jumlah value dari seluruh row.

Sintaks:
     RATIO_TO_REPORT(value) OVER (<partition_clause>
           <order_by_clause>)

Gambar 2.0 Contoh fungsi RATIO_TO_REPORT (value)

Contoh:
SELECT department_id, last_name, salary,         ratio_to_report(salary) over(partition by department_id)
“RATIO_TO_REPORT”
FROM employees
WHERE department_id = 80;

Pada contoh gambar 2.0 di kolom ke-4 ditampilkan kolom RATIO_TO_REPORT yang merupakan hasil perbandingan antara salary dari setiap row dengan jumlah salary untuk semua row yaitu 304500.
Sebagai contoh pada row ke-1, hasilnya adalah .045977011 yang didapatkan dari salary row ke-1 (14000) dibagi dengan jumlah salary untuk semua row (304500).

c)      Lag/Lead Functions
Fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan. Contoh penggunaan fungsi ini dalam kehidupan nyata adalah misalnya perbandingan antara penjualan bulan ini dengan bulan sebelumnya atau sesudahnya (April dengan Maret atau April dengan Mei).

1)      LAG ( value_expression,offset,default )
Menurut Oracle Reference:PSOUG.org, fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan yaitu sejauh n baris di atas baris yang dispesifikasikan.

Sintaks:
LAG
(<value expression>, <offset>, <default>)  OVER
([<query partition clause>] <order_by_clause>)

LAG functions memiliki 3 parameter yaitu:

  • Value expression   : merupakan nama kolom yang ingin diakses
  • Offset                    : jumlah lompatan untuk menentukan baris mana yang akan ditampilkan (ke atas)
  • Default                  : default value bila baris yang diakses tidak memiliki value

Gambar 2.1 Contoh fungsi LAG

Contoh:
SELECT department_id, last_name, salary, lag(salary,2,3)      OVER(partition by department_id order by salary desc) “LAG” FROM employees WHERE department_id = 80;

Pada contoh gambar 2.1 di kolom ke-4 ditampilkan hasil pengaksesan kolom salary dengan perbedaan 2 lompatan urutan baris ke atas baris yang dispesifikasikan dengan menggunakan fungsi LAG.
Sebagai contoh pada baris ke-1 hasilnya adalah 3 karena bila baris ke-1 mengakses 2 baris ke atas baris ke-1 maka menghasilkan tidak adanya baris yang dapat diakses sehingga yang ditampilkan adalah default value yaitu 3.
Sedangkan pada baris ke-3 hasilnya adalah 14000 karena bila baris ke-3 mengakses 2 baris ke atas baris ke-3 maka akan berhenti pada baris ke-1 yang mempunyai value 14000 sehingga yang ditampilkan pada kolom LAG di baris ke-3 adalah 14000.

2)      LEAD ( value_expression, offset, default )

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan yaitu sejauh n baris di bawah baris yang dispesifikasikan.

Sintaks:
LEAD
(<value expression>, <offset>, <default>)  OVER
([<query partition clause>] <order_by_clause>)

LEAD functions memiliki 3 parameter yaitu:

  • Value expression   : merupakan nama kolom yang ingin diakses
  • Offset                    : jumlah lompatan untuk menentukan baris mana yang akan ditampilkan (ke bawah)
  • Default                  : default value bila baris yang diakses tidak memiliki value

Gambar 2.2 Contoh fungsi LEAD

Contoh:
SELECT department_id, last_name, salary, lead(salary,2,3) over(partition by department_id order by salary desc) “LAG” FROM employees  WHERE department_id = 80;

Pada contoh gambar 2.2 di kolom ke-4 ditampilkan hasil pengaksesan kolom salary dengan perbedaan 2 lompatan urutan baris ke bawah baris yang dispesifikasikan dengan menggunakan fungsi LEAD.
Sebagai contoh, pada baris ke-1 hasilnya adalah 12000 karena bila baris ke-1 mengakses 2 baris ke bawah baris ke-1 maka akan berhenti pada baris ke-3 yang mempunyai value 12000 sehingga yang ditampilkan pada kolom LEAD di baris ke-1 adalah 12000.

DAFTAR PUSTAKA

Read Full Post »

Oleh : Diana Suharlim  /  1200941960

Transaksi – transaksi yang ada dalam database Oracle bisa di-commit dan di-rollback. Perbedaan antara commit dan rollback adalah :

  1. Commit : jika transaksi di-commit, maka perubahan yang kita lakukan terhadap data pada transaksi tersebut akan dibuat menjadi permanen, sedangkan
  2. Rollback : membatalkan perubahan yang dilakukan terhadap data, sehingga data akan kembali ke nilai awal sebelum dilakukan transaksi.

Agar transaksi bisa dibatalkan (rollback), maka nilai original dari data tersebut harus disimpan. Data original yang disimpan ini disebut dengan before image data. Before image data akan disimpan untuk setiap transaksi yang dilakukan, sehingga jika terjadi kesalahan pada saat mengubah data, data masih bisa dikembalikan ke originalnya (rollback).

Karena before image data digunakan untuk tujuan undo(mengembalikan data), maka before image data juga disebut dengan undo information. Pada Oracle versi sebelumnya, yaitu Oracle 7, 8, dan 8i, undo information disimpan dalam segment yang disebut dengan rollback segment. Rollback segment ini dibuat secara manual oleh DBA (Database Administrator). DBA harus memperhatikan berapa banyak dan berapa ukuran rollback segment yang harus dibuat? serta berapa banyak extent yang harus dibuat di tiap-tiap segment ? Hal ini menyulitkan DBA dalam menyediakan space untuk rollback. Oleh karena itu, pada versi selanjutnya dari Oracle, yaitu Oracle 9i, Oracle 10g dan Oracle 11i, space management untuk rollback telah disederhanakan menjadi Automatic Undo Management (AUM). AUM menggunakan undo tablespace untuk mengatur space untuk undo information. Disebuat “Automatic” karena server akan secara otomatis membentuk dan mengatur jumlah dan ukuran undo segment dalam sebuah undo tablespace. DBA tidak perlu membuat rollback segment secara manual lagi. Walaupun menggunakan AUM, undo information masih disimpan dalam rollback segment, dimana rollback segment itu sekarang disebut sebagai undo segment. Yang disimpan dalam undo tablespace adalah data yang merupakan before image data, jadi ukuran dari undo tablespace disesuaikan dengan ukuran dari data original sebelum transaksi dilakukan. Undo segment ini ada dalam undo tablespace dan dibuat dan diatur secara otomatis oleh Oracle. Pada Oracle 9i, DBA perlu membentuk terlebih dahulu undo tablespace, sedangkan pada Oracle 10g dan Oracle 11i, undo tablespace yang merupakan default telah terbentuk tanpa harus dibentuk (create) oleh DBA secara manual. Pada Oracle 10g, undo management default yang digunakan adalah undo management dengan mode MANUAL. Sedangkan pada Oracle 11i, secara default telah menggunakan Automatic Undo Management.

Bagaimana Undo bekerja?
Seperti yang telah dijelaskan sebelumnya, undo information akan disimpan dalam undo tablespace. Berikut akan dijelaskan bagaimana undo bekerja pada transaksi INSERT, UPDATE dan DELETE.

  1. INSERT
    Pada proses INSERT, space undo tablespace yang akan digunakan untuk menyimpan undo information pasti lebih kecil, karena proses INSERT, tidak punya before image data.
    Tetapi nilai baru akan disimpan, sehingga transaksi bisa di­-redo.

    Gambar 1.1 State sistem setelah redo log buffer di-flush (Kyte, 2005, p. 289)

    Ketika buffer cache sudah penuh, maka DBWr (Database Writer) akan mengirimkan signal ke LGWr (Log Writer) untuk melakukan flushing redo log. Jika database berada dalam mode ARCHIVELOG, maka ARCn (Archiver) akan memasukkan redo entries ke dalam archive log ketika redo log di-flush, jika database berada dalam mode NOARCHIVELOG, maka setelah di-flush, redo entries akan hilang. Perlu kita ingat bahwa redo log buffer akan di-flush setiap 3 detik. Gambar 1.1 diatas  adalah state sistem setelah redo log buffer di-flush.

  2. UPDATE
    Pada proses UPDATE,space undo tablespace yang digunakan akan lebih besar dibandingkan dengan proses  INSERT, karena akan menyimpan undo information yang berupa before image data. Gambar 1.2 adalah state sistem setelah dilakukan UPDATE, undo segment akan disimpan dalam buffer cache. Undo segmentakan menyimpan data original

    Gambar 1.2 State system setelah update (Kyte, 2005, p. 289)

    sebelum update dilakukan dan redo log buffer akan menyimpan data baru yang diupdate. Jika user melakukan rollback, maka sistem akan mencari undo information yang disimpan dalam undo segment yang ada dalam buffer cache. Setelah itu informasi itu akan diberikan ke block data dan block index dalam buffer cache. Lalu data original yang ada dalam buffer cache akan di-flush dan datanya dimasukkan ke dalam data file. Selama proses rollback, redo log tidak akan diikut sertakan (Kyte, 2005, p. 289).

  3. DELETE
    Proses DELETE hampir sama dengan proses UPDATE. Space undo tablespace yang akan digunakan juga lebih besar dibandingkan dengan INSERT, karena undo information akan disimpan dalam undo segment. Ketika transaksi di-rollback, maka data yang sesuai dari undo segment akan diambil dan dikembalikan ke data file.

DBA bisa mengubah mode dari undo management dengan menggunakan inisialisasi parameter UNDO_MANAGEMENT. Jika ingin menggunakan manual undo management, maka set parameter UNDO_MANAGEMENT menjadi MANUAL dan jika ingin menggunakan AUM, maka set parameter UNDO_MANAGEMENT menjadi AUTO.
Menurut Jeff Hunter, ada beberapa hal yang perlu diperhatikan ketika menggunakan AUM, yaitu :

  1. Jika ingin mengatur rollback segment secara manual, seperti pada versi Oracle sebelumnya, maka set inisialisasi parameter UNDO_MANAGEMENT menjadi MANUAL.
  2. Untuk membedakan dua tipe segment,  maka rollback segment disebut sebagai undo segment ketika AUM digunakan.
  3. Oracle Database akan secara otomatis akan membentuk undo segment dalam undo tablespace. DBA tidak perlu lagi membuat rollback segment satu per satu secara manual. undo segment dimiliki oleh PUBLIC
  4. Semua undo segment akan di-drop jika DBA drop undo tablespace.
  5. Tidak semua undo segment dalam undo tablespace memiliki status ONLINE pada saat Oracle di-startup. Jumlah dari undo segment yang aktif ditentukan oleh parameter instance SESSION. Semua OFFLINE undo segment dalam undo tablespace yang aktif, akan diubah statusnya menjadi ONLINE jika semakin banyak transaksi memerlukan undo segment.
  6. Kita bisa saja membuat rollback segment dalam undo tablespace, jika parameter UNDO_MANAGEMENT adalah  MANUAL, tetapi hal ini sangat tidak disarankan, karena hal ini tidak berguna. Rollback segment tidak dapat diubah menjadi ONLINE ketika parameter UNDO_MANAGEMENT diset menjadi AUTO.
  7. Kita hanya bisa menggunakan satu undo tablespace dalam AUM. Walaupun demikian, kita tetap bisa membuat lebih dari satu undo tablespace. Jika ada lebih dari satu undo tablespace, maka tentukan undo tablespace mana yang akan digunakan melalui parameter undo_tablespace. Jika tidak ditentukan, maka Oracle akan menggunakan undo tablespace default, yaitu undotbs1.dbf
  8. undo tablespace selalu dibuat dengan locally-managed extents dengan alokasi sistem extent.

Undo Retention
Undo retention adalah waktu minimum yang digunakan untuk mempertahankan undo information yang sebelumnya dalam undo tablespace sebelum undo information tersebut di-overwrite. Undo information yang umurnya lebih tua dari undo retention disebut dengan expired undo information dan undo information yang umurnya belum melebihi undo retention disebuat dengan unexpired undo information. Untuk mengatur undo retention, gunakan inisialisasi parameter UNDO_RETENTION. Undo retention period diisi dalam satuan detik. Jika ada transaksi baru dan undo informationnya akan diisi ke dalam undo tablespace, dimana undo tablespace sudah tidak memiliki space kosong, maka expired undo information yang ada dalam undo tablespace akan di-overwrite. Jika semua expired undo information telah di-¬overwrite, dan ada undo information baru yang akan masuk, maka unexpired undo information-lah yang akan di-overwrite.
Ada dua yang perlu diperhatikan dalam undo retention, yaitu :

  1. undo retention pada fixed undo tablespace (undo tablespace yang memiliki ukuran tetap), maka jika ada undo information baru yang akan masuk, sedangkan tidak ada lagi space yang kosong, maka ada kemungkinan unexpired undo tablespace juga akan di-overwrite.
  2. untuk undo tablespace dengan AUTOEXTEND, ketika space dalam undo tablespace sudah tidak ada, maka undo information yang baru akan meng-overwrite expired undo information. Jika semua expired undo information telah di-overwrite, dan ada undo information baru yang akan masuk lagi, maka undo tablespace akan di-autoextend. Jika MAXSIZE dari AUTOEXTEND undo tablespace ditentukan, maka ada kemungkinan unexpired undo information juga akan di-overwrite jika undo tablespace telah mencapai ukuran maksimalnya dan space sudah tidak ada lagi.

View untuk melihat informasi tentang UNDO
Tabel berikut adalah beberapa view yang dapat digunakan untuk melihat informasi dari undo tablespace :

View

Deskripsi

V$UNDOSTAT berisi statistik untuk monitor dan tuning undo space. View ini bisa digunakan untuk membantu mengestimasi berapa ukuran undo space untuk beban kerja sekarang. Informasi ini juga dapat membantu database untuk melakukan tuning undo usage dala sistem. Data statistik diambil setiap 10 menit sekali dalam 24 jam.
V$ROLLSTAT dan  V$ROLLNAME View ini digunakan untuk memonitor kinerja undo tablespace secara keseluruhan.
V$TRANSACTION Berisi informasi undo segment.
DBA_UNDO_EXTENTS Menyediakan informasi extent yang ada dalam undo tablespace.
DBA_HIST_UNDOSTAT Berisi statistical snapshot dari informasi V$UNDOSTAT.
DBA_ROLLBACK_SEGS Berisi informasi rollback segment yang dibuat oleh Oracle.

Parameter untuk mengatur Automatic Undo Management
Berikut adalah table yang menjelaskan mengenai inisialisasi parameter yang digunakan untuk mengatur AUM :

Parameter Name

Default Value

Valid Value

undo_management Manual Auto, Manual
undo_retention 900 seconds 0 – max value 32 bits
undo_suppress_errors True True, false
undo_tablespace undotbs01 or  rollback segment Valid tablespace name
  • undo_management : parameter ini menentukan apakah AUM sedang diaktifkan atau tidak. Jika parameter ini di-set AUTO, maka AUM sedang digunakan dan jika parameter ini di¬-set MANUAL, maka AUM tidak digunakan.
  • undo_retention : parameter ini menentukan berapa lama undo data akan dipertahankan dalam undo tablespace, sebelum di-overwrite. Satuan dari nilai undo_retention adalah dalam detik (seconds).
  • undo_suppress_errors : parameter ini digunakan untuk mengontrol apakah pesan error akan ditampilkan atau tidak. Parameter ini hanya akan menunjuk efek jika Automatic Undo Management-lah yang digunakan.
  • undo_tablespace : menentukan undo tablespace mana yang akan digunakan untuk menyimpan undo data.

Membuat undo tablespace
Berikut adalah contoh pembentukan undo tablespace dengan menggunakan AUTOEXTEND :

CREATE UNDO TABLESPACE undotbs_02
DATAFILE ‘/u01/app/oracle/oradata/orcl/undo0201.dbf’ SIZE 2M REUSE
AUTOEXTEND ON;

Contoh diatas adalah sintaks yang digunakan untuk membuat undo tablespace dengan nama undotbs_02 dan membentuk data file undo0201.dbf yang disimpan dalam directory /u01/app/oracle/oradata/orcl/ dengan ukuran data file adalah 2M dan menggunakan AUTOEXTEND.

Altering undo tablespace
Undo tablespace
dapat dialter dengan menggunakan command ALTER TABLESPACE. Aksi yang bisa dilakukan dengan ALTER TABLESPACE adalah :

  • penambahan datafile

ALTER TABLESPACE undotbs_01
ADD DATAFILE ‘/u01/app/oracle/oradata/orcl/undo0201.dbf’
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Pada contoh alter tablespace diatas, yang dilakukan adalah menambahkan data file ke dalam tablespace undotbs_01. Tablespace menggunakan pilihan AUTOEXTEND dengan ukuran maksimal yang tidak terbatas.

  • mengubah ukuran data file

ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/orcl/undo0201.dbf’ RESIZE 10M;

Pada contoh diatas, data file undo0201.dbf diubah ukurannya menjadi 10M.

  • melakukan operasi backup

ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

Dropping undo tablespace
Untuk menghapus undo tablespace, kita bisa menggunakan DROP TABLESPACE. Berikut adalah contoh dropping undo tablespace undotbs_01  :

DROP TABLESPACE undotbs_01 ;

Yang perlu diperhatikan ketika melakukan drop tablespace adalah jangan drop undo tablespace jika undo tablespace tersebut sedang digunakan oleh instance. Dengan melakukan drop undo tablespace, berarti semua undo information akan dihapus juga termasuk unexpired undo information. Oleh karena itu, kita harus berhati-hati jangan sampai melakukan drop sebuah undo tablespace yang masih menyimpan undo information yang masih digunakan.

Switching undo tablespace
Administrator
bisa melakukan switching undo tablespace yang digunakan dengan ALTER SYSTEM SET. Berikut adalah contoh switching undo tablespace :

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Yang perlu diperhatikan pada saat melakukan switching undo tablespace adalah :

  • tablespace harus ada dalam sistem,
  • tablespace haruslah undo tablespace,
  • undo tablespace tersebut sedang tidak digunakan oleh instance lain.

ALTER SYSTEM SET dapat digunakan juga untuk :

  • merubah undo_retention yang digunakan :

    ALTER SYSTEM SET UNDO_RETENTION=5;

  • merubah undo_suppress_error :

    ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

  • merubah status dari undo_management, apakah MANUAL atau AUTO. Jika AUTO, berarti Automatic Undo Management diaktifkan :

 ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

DAFTAR PUSTAKA


Read Full Post »

Oleh : Ferryandi (1201003636)

1.  Latar Belakang
Minoui & Fritz (2011) munculnya fitur Advanced Queuing (AQ) bermula dari kebutuhan pasar akan satu fitur yang bisa menjembatani koneksi dan pertukaran data antar aplikasi. Sebuah aplikasi yg terdistribusi (Distributed Application Development) membutuhkan middle-ware (Message-Oriented Middleware) atau fitur yang bisa menjadi penengah dari pertukaran data aplikasi tersebut. Oleh karena itu, ada motto yang mengatakan bahwa : “Every DAD needs MOM” yang artinya adalah setiap aplikasi terdistribusi mesti memiliki middle-ware (MOM). Jadi karena kebutuhan pasar inilha maka muncullah Oracle Advanced Queuing.
Dalam hal komunikasi antar aplikasi terjadi 2 tipe model pengiriman data yaitu :

  • Enqueue
  • Dequeue

Dengan kata lain, setiap aplikasi yang saling berkomunikasi dalam 1 jaringan melakukan proses menaruh message dalam queue (Enqueue) dan menarik message dari queue (Dequeue). Juga terdapat 2 model aplikasi yaitu :

  • Synchronous (online, connected)
    Program mengirim requests dan menunggu sampai program lain membalas requests dari program tersebut.
  • Asynchronous (deffered, disconnected)
    User
    mengirim requests dan menaruh messages tersebut dalam queue dan dilanjutkan oleh user lainnya untuk menverifikasi messages tersebut dari queue setelah itu baru dieksekusi.

2.  Pembahasan
a.     
Pengertian Oracle Advanced Queuing
Oracle Advanced Queing (AQ) adalah salah satu fungsionalitas queuing messages performan tinggi pada database Oracle. Dengan AQ ini komunikasi message secara otomatis diaudit, yang merupakan kebutuhan penting khususnya untuk keperluan aplikasi finansial. Kita bisa melihat beberapa contoh implementasi dari AQ ini misalnya pada aplikasi finansial, aplikasi yang terintegrasi satu sama lain, dan lain sebagainyan. Untuk mengetahui cara kerja AQ kita bisa melihat dengan jelas di Gambar 1.

Gambar 1. Integrated Application Environment Using Advanced Queuing Sumber : http://download.oracle.com

 Pada gambar 1, kita dapat melihat bahwa dua aplikasi yang berjalan saling berkomunikasi. Ketika internet user melakukan requests ke aplikasi yang berdiri dalam database Oracle, maka terjadi pengiriman messages akan tetapi messages tersebut disimpan lebih dulu ke dalam fitur AQ dimana diproses dulu di dalam queue atau antrian baru dikirim ke aplikasi. Setiap internet user  melakukan requests maka data yang dikirim akan disimpan kedalam antrian atau queue baru diproses oleh database ke aplikasi.

b.      What AQ Addresses ?
Oracle Advanced Queuing mempunyai 5 fitur, yaitu :

  • End to End Tracking
    Arti dari End to End Tracking adalah dimana AQ ini mampu menyimpan current state dan recent history dari setiap message. Setiap messages akan diberitahu dimana posisi dan letak terakhir atau lokasi terakhir penyimpanan dari data messages tersebut.
  • Automatic Correlation
    AQ
    akan secara otomatis memberikan respon diantara messages yang masuk seperti Cause/Effect, Request/Reply, and Original/Copy relationships.
  • Automatic Auditing
    Di dalam setiap environtments dari sebuah aplikasi, messages yang berjalan diantara aplikasi yang berbeda merepresentasikan transaksi yang penting. Maka diperlukan proses audit, AQ ini mempunyai solusi tersebut dengan memberikan fitur automatic auditing.
  • Automatic Exception Handling
    Setiap messages yang tidak dipakai lagi oleh user akan otomatis dipindahkan ke exception queue. Jadi AQ ini mampu memberikan jaminan penggunaan data kepada user jika data tersebut tidak dipakai lagi dalam jangka waktu tertentu sehingga tidak mengganggu transaksi data lainnya.
  • Message Warehousing
    Fitur ini dapat digunakan untuk keperluan yang bervariasi misalnya untuk Business Process Analysis dan Dispute Resolution.

c. Features of Oracle Advanced Queuing
Dibawah ini ada beberapa fitur yang terdapat dalam Oracle Advanced Queuing. Untuk itu kita akan membahasnya satu per satu agar lebih mengenal apa fitur dari Oracle Advanced Queuing ini dan bagaimana cara kerjanya.

  • Point to Point dan Publish Subscribe Messaging
    Untuk mengenal lebih jelas fitur ini bisa dilihat dari gambar 1.2 berikut ini :
Gambar 2. Point to Point Messaging Sumber : http://download.oracle.com

Dari gambar 2, kita tampak bahwa AQ mempunyai fitur yang dapat melakukan proses pendataan aplikasi secara langsung yaitu point to point. Dalam hal ini terjadi proses Enqueue dan Dequeue bersamaan.
Sekarang kita juga akan membahas tentang Publish Subscribe Messaging. Untuk lebih jelasnya silahkan perhatikan gambar 3 di bawah ini :

Gambar 3. Publish Subscribe Messaging Sumber : http://download.oracle.com

Dari gambar 3, bisa kita tarik kesimpulan bahwa AQdapat dipakai oleh multiple aplikasi secara bersamaan.

  • Oracle Internet Directory
    Oracle Internet Directory ini merupakan bagian native dari LDAPv3 directory yang dibuat diatas Oracle Database yang dapat mensentralisasi sejumlah informasi.
  • Message Format Transformation
    AQ
    mensupport format data yang dipakai aplikasi yang berbeda. Dengan fitur ini dapat memudahkan terjadinya pertukaran data dalam format yang berbeda dari aplikasi tersebut. Untuk lebih jelasnya bisa dilihat di gambar 1.4 berikut ini :

Gambar 4. Transformation in Application Integration Sumber : http://download.oracle.com

  • SQL Access
    Messages
    yang disimpan dalam beberapa baris di dalam database, dapat di lakukan proses queries. Ini menunjukkan bahwa messages tersebut dapat diakses dan dilakukan proses queries menggunakan syntax SQL.
  • Support for Statistic Views
    Dapat digunakan dengan menggunakan GV$AQ di terminal linux yang sudah di install Oracle EM.
  • Structured Payloads
    Dengan fitur ini kita dapat menggunakan object tertentu untuk di susun dan dimanage suatu messages payloads. Untuk lebih jelasnya dapat mengunjungi link berikut: “Structured Payloads” .
  • Retention and Message History
    AQ
    menyimpan informasi mengenai history dari setiap messages dan dapat diakses lagi menggunakan fitur ini. Untuk lebih jelasnya bisa mengunjungi link berikut : to Retention and Message History.
  • Tracking and Event Journals
    Dengan AQ tracking features kita dapat menggunakan SQL SELECT dan JOIN statements untuk mendapatkan informasi dari AQ$QUETABLENAME dan views dari ENQ_TRAN_ID, DEQ_TRAN_ID, USER_DATA (the payload), CORR_ID, and MSG_ID.
  • Queue-Level Access Control
    Dengan fitur ini seorang AQ administrator dapat membuat beberapa akses kontrol untuk user. Untuk lebih jelasnya silahkan kunjungi link berikut : “Queue-Level Access Control”.
  • Nonpersistent Queues
    Fitur ini membuat AQ dapat mengirim nonpersisten queues kepada subscribers dalam satu transaksi. Lebih jelas bisa baca dari link berikut : “Nonpersistent Queues”.
  • XMLType Payloads
    Dengan menggunakan fitur ini kita bisa melakukan berbagai hal berikut :
  • Menyimpan berbagai tipe messages dalam satu queue.
  • Menyimpan dokumen secara internal sebagai CLOBs.
  • Menyimpan lebih banyak tipe payloads dalam queue.
  • Query XMLType columns menggunakan operator ExistsNode() dan SchemaMatch().
  • Menspesifikasin operator dalam subscribers rules atau dequeu conditions.
  •  Internet Integration and Internet Data Access Presentation
    Kita dapat mengakses AQ via internet menggunakan Simple Object Access Protocol (SOAP).

d.      Creating AQ Administrators and Users
Kita dapat membuat AQ administrators dan user menggunakan cara berikut :

  • AQ  Administrator created by DBA :
  • Create the user (aqadm)
  • Grant AQ_ADMINISTRATOR_ROLE
  • Grant connect, resource to aqdm

    Berikut setting user bagi Admin :

CONNECT sys/sys CREATE USER aqadm IDENTIFIED BY aqadm;
GRANT AQ_ADMINISTRATOR_ROLE TO aqadm;
GRANT CONNECT, RESOURCE TO aqadm;  

  • Additional grants :
  • Execute on dbms_aqadm
  • Execute on dbms_aq

Sebagai tambahan Anda bisa lakukan grant ke user yang telah ane create:

GRANT EXECUTE ON DBMS_AQADM TO aqadm;
GRANT EXECUTE ON DBMS_AQ TO aqadm;  

 Jika Anda ingin mengcreate user yang dapat membuat dan mengakses queue dalam schemanya sendiri, lakukan langkah berikut :

CONNECT sys/sys;
CREATE USER aquser1 IDENTIFIED BY aquser1;
GRANT CONNECT, RESOURCE TO aquser1;

Sebagai tambahannya, Anda bisa melakukan grant privilege ke user tersebut dalam AQ packages seperti dibawah ini :

GRANT EXECUTE ON DBMS_AQADM to aquser1;
GRANT EXECUTE ON DBMS_AQ TO aquser1;

Dibawah ini adalah cara membuat queue table dan queue of object type :

 /* Creating a message type: *

CREATE type aq.Message_typ as object (
subject     VARCHAR2(30),
text        VARCHAR2(80));    

 /* Creating a object type queue table and queue: */

EXECUTE DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table        => ‘aq.objmsgs80_qtab’,

queue_payload_type => ‘aq.Message_typ’); 

EXECUTE DBMS_AQADM.CREATE_QUEUE (
queue_name         => ‘msg_queue’,

queue_table        => ‘aq.objmsgs80_qtab’); 

EXECUTE DBMS_AQADM.START_QUEUE (
queue_name         => ‘msg_queue’);

Object dari AQ juga bisa di drop caranya adalah sebagai berikut :

/* Cleans up all objects related to the object type: */

CONNECT aq/aq
EXECUTE DBMS_AQADM.STOP_QUEUE ( queue_name => ‘msg_queue’);
EXECUTE DBMS_AQADM.DROP_QUEUE ( queue_name => ‘msg_queue’);
EXECUTE DBMS_AQADM.DROP_QUEUE_TABLE ( queue_table => ‘aq.objmsgs80_qtab’);

  • Revoking Roles and Privileges

 CONNECT sys/change_on_install DROP USER aq;

Untuk melihat syntax dan kegunaan lainnya dai AQ bisa dilakukan views berikut pada terminal linux yang telah di install Oracle EM :

  • DBA_QUEUE_TABLES
  • USER_QUEUE_TABLES
  • DBA_QUEUES
  • USER_QUEUES
  • AQ$<queue_table>

3.      Simpulan

Sekian dari paper mengenai Oracle Advance Queuing ini. Moga kiranya dengan penjelasan fitur dari Oracle Advanced Queuing ini kita dapat memaksimalkan penanganan data dalam database Oracle untuk keperluan berbagai proses bisnis terutama adalah aplikasi finansial. Untuk materi lebih lanjut mengenai Oracle Advanced Queuing ini bisa dilihat di http://download.oracle.com . Sekian dan terima kasih.

Daftar Pustaka

Read Full Post »

Oleh : Henoch Immanuel Setiadi (1200951904)

1.    Introduction : Workload Management

(Introduction to Workload Management, 2010) menuliskan bahwa pertama untuk mengenal Load Balancing, kita harus mengetahui terlebih dahulu mengenai Workload Management. Workload Management ini memungkinkan kita untuk mengatur distribusi Workload untuk menghasilkan performa yang optimal bagi Users dan Applications. Workload Management terdiri dari :
•    Services
Oracle Database 10g memperkenalkan fasilitas Workload Management yang Powerful, disebut dengan Services, untuk memungkinkan konsep Enterprise Grid. Services merupakan Entities yang didefinisikan di Oracle RAC Database yang memungkinkan kita untuk mengelompokkan Database Workloads dan mengarahkan Work ke Instances yang optimal yang menyediakan Service yang diminta.
•    Connection Load Balancing
Merupakan sebuah fitur dari Oracle Net Services yang menyimbangkan Connections yang masuk ke seluruh Instances yang menyediakan Service yang diminta.
•    High Availability Framework
Sebuah komponen Oracle RAC yang memungkinkan Oracle Database untuk Maintain komponen dalam keadaan jalan setiap waktu.
•    Fast Application  Notification (FAN)
Mekanisme notifikasi yang digunakan Oracle RAC untuk secara cepat memberikan informasi pada aplikasi mengenai konfigurasi dan perubahan level dari Workload Service.
•    Load Balancing Advisory
Menyediakan informasi ke aplikasi mengenai level dari Current Service yang Database dan Instances – nya sediakan. Load Balancing Advisory membuat rekomendasi ke aplikasi mengenai ke mana untuk mengarahkan permintaan aplikasi untuk mendapatkan layanan terbaik berdasarkan Policy yang telah kita atur.
•    Fast Connection Failover
Ini merupakan salah satu kemampuan Oracle Clients untuk menyediakan koneksi yang memiliki Failover cepat dengan Subscribing ke FAN Events.
•    Runtime Connection Load Balancing
Ini merupakan kemampuan Oracle Clients untuk menyediakan alokasi koneksi yang pintar dalam Connection Pool didasarkan pada Current Service Level yang disediakan oleh Database Instances ketika aplikasi meminta koneksi untuk menyelesaikan beberapa tugas.

2.    Connection Load Balancing

Gambar 1 Connection Load Balancing

Menurut (Introduction to Workload Management, 2010) salah satu kemampuan dari Oracle Net Services untuk menyeimbangkan koneksi klien ke seluruh Instances dalam sebuah Oracle RAC Configuration. Ada 2 tipe dari Load Balancing yaitu Client – Side dan Server – Side Load Balancing. Client – Side Load Balancing menyeimbangkan connection requests ke seluruh

Listeners. Dengan Server – Side Load Balancing, Listener mengarahkan Connection Request ke Instance terbaik yang menyediakan layanan tersebut dengan menggunakan Load Balancing Advisory. Dalam Oracle RAC Database, Client Connection harus menggunakan kedua tipe tersebut.

Untuk konfigurasi Connection Load Balancing memiliki 2 Goals, yaitu :
• Long
Digunakan untuk aplikasi yang memiliki Long – Lived Connection. Sering digunakan untuk Connection Pools dan SQL *Form Sessions. Long merupakan

Default dari  Connection Load Balancing Goals. Berikut ini adalah contoh dari Modifying sebuah Service, POSTMAN, dengan PL/SQL DBMS_SERVICE Package dan CLB_GOAL_LONG Package Constant untuk mendefinisikan Connection Load Balancing untuk Long – Lived Sessions :

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘POSTMAN’ , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

• Short

Digunakan untuk aplikasi yang memiliki ¬Short – Lived Connections. Berikut ini contoh Modifies sebuah Service, Order, dengan menggunakan DBMS_SERVICE.CLB_GOAL_SHORT Package Constant untuk mengatur Goal menjadi Short :

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘ORDER’        , CLB_GOAL => DBMS_SERVICE.CLB_GOAL_SHORT);

Ketika kita membuat sebuah Oracle RAC Database dengan Database Configuration Assistant (DBCA), DBCA mengkonfigurasikan dan memungkinkan Server – Side Load Balancing secara Default. DBCA juga membuat contoh definisi Client – Side Load Balancing dalam tnsnames.ora pada Server. Service apapun yang dibuat melalui DBCA memiliki Default Setting CLB_GOAL=CLB_GOAL_LONG.
Ketika Oracle Net Services membuat koneksi ke sebuah Instance, koneksi tersebut tetap terbuka sampai Client menutup koneksi tersebut, Instance Shutdown, atau terjadi Failure. Jika kita mengkonfigurasikan Transparent Application Failover (TAF), maka Oracle akan memindahkan Session ke Instance yang masih hidup, jika terjadi Outage.
TAF dapat Restart sebuah Query setelah Failover selesai, tetapi untuk transaksi tipe lain, seperti INSERT, UPDATE, DELETE, aplikasi harus Rollback transaksi yang gagal dan Resubmit transaksi tersebut. Kita harus Reexecute Session Customizations apapun, dengan kata lain, ALTER SESSION Statements, setelah terjadi Failover. Namin, dengan TAF, sebuah koneksi tidak bergerak selama proses normal, sekalipun jika Workload – nya berubah melebihi waktu.
Services menyederhanakan Deployment dari TAF. Kita dapat mendefinisikan TAF Policy buat sebuah Service dan seluruh koneksi yang menggunakan Service ini akan memiliki TAF aktif. Hal ini tidak membutuhkan  perubahan dari Client – Side. TAF Service pada sebuah Service Overrides TAF Setting apapun di Client – Side Definition. Untuk mendefinisikan TAF Policy untuk sebuah Service, gunakan PL/SQL Procedure seperti contoh di bawah ini :

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘gl.us.oracle.com’
, aq_ha_notifications => TRUE
, failover_method => DBMS_SERVICE.FAILOVER_METHOD_BASIC
, failover_type => DBMS_SERVICE.FAILOVER_TYPE_SELECT
, failover_retries => 180
, failover_delay => 5
, clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

Client Side Load Balancing didefinisikan di Client Connection milik kita dengan mengatur Parameter LOAD_BALANCE=ON (Default adalah ON untuk Description Lists). Ketika kita merubah Parameter ini menjadi ON, Oracle akan secara Random memilih sebuah alamat dalam daftar alamat dan konek ke Node’s Listener tersebut. Hal ini menyeimbangkan Client Connection ke seluruh Listeners yang tersedia dalam Cluster tersebut. Ketika Listener menerima Connection Request, Listener tersebut Connects User tersebut ke sebuah Instance yang Listener tahu menyedikan Service yang diminta. Kalau inginmelihat Services apa yang didukung oleh sebuah Listener, jalankan lsnrctl services command.

3.    Load Balancing Advisory

Gambar 2 Load Balancing Advisory

Menurut (Introduction to Workload Management, 2010) Load Balancing mendistribusikan tugas ke seluruh Oracle RAC Database Instances yang tersedia. Oracle merekomendasikan aplikasi untuk menggunakan koneksi yang kuat yang meluaskan Instance yang menawarkan layanan tertentu. Koneksi dibuat secara Infrequently dan berada untuk waktu yang lama. Tugas dating ke System dengan frekuensi tinggi. Meminjam koneksi – koneksi ini, dan berada untuk durasi yang pendek. Load Balancing Advisory menyediakan informasi mengenai bagaimana untuk mengarahkan tugas yang masuk ke Instances yang menyediakan kualitas Service yang optimal untuk tugas tersebut. Hal ini mengurangi kebutuhan untuk mengalokasikan tugas tersebut nanti.
Dengan menggunakan THROUGHPUT  atau SERVICE_TIME Goals, feedback dibangun di System. Tugas diarahkan untuk menyediakan Service Time terbaik secara global, dan mengarahkan respon untuk mengubah kondisi system. Dalam Steady State, system mencapai Equilibrium yang meningkatkan Throughput ke seluruh Oracle RAC Instance.
Arsitektur standard yang dapat digunakan untuk Load Balancing Advisory termasuk Connection Load Balancing, Transaction Processing Monitors, Application Servers, Connection Concentrators, Hardware and Software Load Balancers, Job Schedulers, Batch Schedulers, and Message Queuing Systems. Semua ini dapat mengalokasikan tugas.
Load Balancing Advisory Deployed dengan Key Oracle Clients, seperti Listener, JDBC Implicit Connection 10g, ODP.NET Connection Pool. Load Balancing Advisory juga dibuka untuk subskripsi Third Party dengan Way Of ONS.
Kita dapat konfigurasikan Environment untuk menggunakan Load Balancing Advisory dengan mendefinisikan Service – Level Goals untuk setiap Service yang ingin diaktifkan Load Balancing – nya. Ada 2 tipe dari Service – Level Goals untuk Runtime :
•    SERVICE_TIME
Mencoba untuk mengarahkan Work Requests ke Instances berdasarkan waktu respon. Load Balancing Advisory Data didasarkan pada waktu yang sudah dijalankan untuk menyelesaikan suatu pekerjaan dalam Service dan Bandwith yang tersedia untuk Service tersebut. Contoh kegunaan SERVICE_TIME adalah untuk beban kerja seperti Internet Shopping dimana tingkat permintaan berubah – rubah :

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘OE’
, goal => DBMS_SERVICE.GOAL_SERVICE_TIME –
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

•    THROUGHPUT
Mencoba untuk mengarahkan Work Requests ke Instances tergantung dari Throughput. Load Balancing Advisory Data didasarkan pada tingkat keberhasilan tugas di dalam Service ditambah dengan Bandwith yang tersedia di Serrvice tersebut. Contoh penggunaan THROUGHPUT ini adalah untuk beban kerja seperti Batch Processes, dimana tugas berikutnya dilakukan ketika tugas terakhir sudah selesai :

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => ‘sjob’ –
        , goal => DBMS_SERVICE.GOAL_SERVICE_TIME –
       , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

Mengubah Setting menjadi NONE akan menonaktifkan Load Balancing untuk Service tersebut. Kita dapat melihat Goal Setting di dalam Data Dictionary dan di dalamViews DBA_SERVICES, V$SERVICES, dan V$ACTIVE_SERVICES.

Reference


Read Full Post »

Oleh : Ellen Suwandi (1200991536)

PENDAHULUAN
Schema adalah kumpulan objek-objek database, mencakup tabel, view, index, dan sinonim.
Ada beberapa cara dalam menyusun objek-objek schema dalam model schema yang dirancang untuk data warehouse. Salah satu schema untuk data warehouse adalah star schema. Schema sampel sh merupakan model menggunakan star schema (lihat gambar 1).

Gambar 1: Contoh Star Schema

Namun, terdapat model-model schema lain yang umum digunakan untuk data warehouse. Yang paling umum digunakan adalah third normal form (3NF) schema. Selain itu, beberapa schema untuk data warehouse bukan merupakan star schema ataupun 3NF schema, tetapi memiliki karakteristik dari kedua schema. Schema ini dikenal dengan model schema hybrid.

Gambar 2: Contoh 3nf Schema

Oracle Database dirancang untuk mendukung semua schema untuk data warehouse. Beberapa fitur bersifat spesifik terhadap satu model schema (seperti fitur star transformation, yang spesifik terhadap star schema). Namun, mayoritas dari fitur-fitur data warehousing pada Oracle dapat diterapkan pada star schema, 3NF schema, dan hybrid schema.

Gambar 3: Contoh Snowflake Schema

Kapabilitas kunci dari data warehousing seperti partitioning, paralelisme, materialized view, dan analytic SQL diimplementasikan di dalam semua model schema. Penentuan model schema mana yang sebaiknya digunakan untuk data warehouse harus didasarkan pada kebutuhan dan kebiasaan dari tim proyek data warehouse. (Lane, 2005, p. 19-1).

STAR SCHEMA

Disebut star schema karena entity-relationship diagram atau ERD-nya yang menyerupai konstelasi bintang, beberapa bintang  besar (fact table) dikelilingi bintang-bintang yang lebih kecil (dimension tables). (Wikimedia Foundation, Inc., 2011)

  • Fact table menampung nilai-nilai metric yang direkam untuk suatu kejadian yang spesifik. Sehubungan dengan tujuan untuk menampul data atomik, biasanya terdapat sejumlah besar record (jutaan). Pengelolaan khusus dilakukan untuk meminimalisasi jumlah dan ukuran atribut dengan tujuan membatasi ukuran tabel keseluruhan dan mengatur performa. Fact tables biasanya berupa transaksi (fakta-fakta mengenai kejadian tertentu, misalnya penjualan), snapshot (fakta-fakta yang direkam pada suatu waktu tertentu, misalnya rincian Account pada awal bulan), dan tabel-tabel snapshot akumulasi (misalnya, penjualan month-to-date untuk suatu produk).
  • Dimension tables biasanya memiliki record yang lebih sedikit dibandingkan fact tables, tetapi bisa memiliki jumlah atribut yang besar untuk mendeskripsikan data fakta.

Mengapa menggunakan Star Schema?

Menurut Lane (2005, pp. 19-3), kelebihan utama dari star schema adalah bahwa star schema:

  • Menyediakan pemetaan langsung dan intuitif antara entitas-entitas bisnis yang sedang dianalisa oleh end users dan desain schema.
  • Menyediakan performa teroptimasi untuk star query umum.
  • Didukung oleh banyak business intelligence tools, yang dapat melakukan antisipasi atau bahkan membutuhkan agar schema data warehouse berisi tabel-tabel dimensi.

STAR QUERY

Star query adalah join antara tabel fakta dan sejumlah tabel dimensi. Setiap tabel dimensi di-join-kan kepada tabel fakta menggunakan join dari primary key ke foreign key, tetapi tabel-tabel dimensi tidak saling di-join-kan. Optimizer mengenali star query dan menghasilkan execution plan yang efisien untuk star query. (Lane, 2005, pp. 19-2)

Tabel fakta umumnya berisi keys dan measures (ukuran). Contohnya, dalam schema sampel sh, tabel fakta, sales, berisi ukuran quantity_sold, amount, dan cost, dan keys cust_id, time_id, prod_id, channel_id, dan promo_id. Tabel-tabel dimensi antara lain customers, times, products, channels, and promotions. Tabel dimensi products, contohnya, berisi informasi mengenai setiap nomor produk yang muncul di dalam tabel fakta. (Lane, 2005, pp. 19-2)

Star join adalah join primary key ke foreign key dari tabel dimensi ke tabel fakta. (Lane, 2005, pp. 19-2)

Mengoptimasi Star Query

Menurut Lane (2005, pp. 19-4), untuk memperoleh performa terbaik yang mungkin bagi star queries, penting untuk mengikuti panduan-panduan dasar berikut:

  • Harus dibuat bitmap index pada setiap kolom foreign key dari tabel fakta.
  • Parameter inisialisasi STAR_TRANSFORMATION_ENABLED harus bernilai TRUE. Ini dilakukan untuk mengaktifkan fitur optimizer yang penting untuk star query.

Ketika data warehouse memenuhi kondisi-kondisi tersebut, mayoritas star queries yang berjalan pada data warehouse akan menggunakan strategi eksekusi yang dikenal dengan star transformation. Star transformation menyediakan performa query yang efisien bagi star queries.

STAR TRANSFORMATION

Star transformation merupakan teknik optimasi yang efektif yang berdasar pada penulisan ulang (transformasi) secara implisit terhadap SQL dari star query asli. End user tidak perlu mengetahui detil-detil dari star transformation. Query optimizer dari Oracle secara otomatis memilih star transformation jika diperlukan. (Lane, 2005, pp. 19-4)

Ide dasar dari transformasi ini adalah untuk menghindari penggunaan metode akses full table scan pada tabel-tabel besar, yaitu tabel-tabel fakta dalam star schema. Dalam star query, tabel fakta di-join ke beberapa tabel-tabel dimensin yang jauh lebih kecil. Tabel fakta biasanya berisi sebuah key (foreign key) untuk setiap tabel dimensi dan juga sejumlah kolom-kolom pengukuran seperti jumlah penjualan. Key yang berkoresponden di dalam tabel dimensi merupakan primary key. Join dilakukan antara foreign key dari tabel fakta dengan primary key yang berkoresponden dari tabel dimensi. Query juga berisi predikat-predikat filter pada kolom-kolom lain dari tabel-tabel dimensi yang biasanya sangat membatasi. Kombinasi dari filter-filter ini membantu mengurangi secara drastis himpunan data yang diproses dari tabel fakta. Tujuan dari star transformation adalah untuk mengakses hanya himpunan data yang telah dikurangi dari tabel fakta ini. (Oracle Corporation, 2010)

Oracle memproses star query menggunakan dua fase dasar. Fase pertama mengambil hanya baris-baris yang diperlukan dari tabel fakta (result set). Karena pengambilan ini memanfaatkan bitmap indexes, maka sangat efisien. Fase kedua men-join-kan result set ini ke tabel-tabel dimensi. (Lane, 2005, pp. 19-4)

Sebagai contoh, perhatikan star query berikut ini. Query ini bertujuan untuk mencari total penjualan di semua kota di California untuk kuartal 01 dan 02 pada tahun 1999 melalui internet.

Query 1:

SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c, channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id  AND c.cust_state_province = ‘CA’ AND ch.channel_desc = ‘Internet’AND t.calendar_quarter_desc IN (‘1999-01′,’1999-02’)
GROUP BY c.cust_city, t.calendar_quarter_desc;

Sales adalah tabel fakta sedangkan tabel-tabel lain merupakan tabel-tabel dimensi. Tabel Sales berisi satu baris untuk setiap penjualan produk dan karena itu bisa berisi jutaan record penjualan. Namun hanya beberapa diantaranya yang dijual kepada konsumen di California melalui Internet untuk kuartal yang ditentukan. Query di atas ditransformasi menjadi Query 2.

Query 2:

SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sales s, times t, customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id AND s.time_id IN (SELECT time_id
FROM times
WHERE calendar_quarter_desc IN(‘1999-01′,’1999-02′))
AND s.cust_id IN (SELECT cust_id
FROM customers
WHERE cust_state_province=’CA’)
AND s.channel_id IN (SELECT channel_id
FROM channels
WHERE channel_desc = ‘Internet’)
GROUP BY c.cust_city, t.calendar_quarter_desc;

Star transformationpada intinya adalah menambahkan predikat-predikat subquery yang berkorespon dengan batasan-batasan dimensi. Predikat-predikat subquery ini disebut juga dengan predikat-predikat semi-join bitmap. Transformasi tersebut dilakukan ketika terdapat index pada kolom-kolom fact join (s.timeid, s.custid…). Dengan menggunakan operasi-operasi AND dan OR (bitmap bisa dari bitmap index atau dihasilkan dari B-Tree index biasa) dari nilai-nilai key yang disediakan oleh subquery, hanya baris-baris yang relevan dari tabel fakta yang perlu diambil. Apabila filter pada tabel-tabel dimensi menyaring banyak data (hanya sedikit data yang diambil), cara ini akan jauh lebih efisien dibandingkan full tabel scan pada tabel fakta. Setelah  baris-baris yang relevan telah diambil dari tabel fakta, perlu dilakukan join kembali ke tabel-tabel dimensi. Dalam kasus-kasus tertentu, join kembali bisa dihilangkan.

Berikut adalah execution plan sebagai hasil dari star transformation.

1        SELECT STATEMENT
2        HASH GROUP BY
3        HASH JOIN
4             TABLE ACCESS FULL  CHANNELS
5                 HASH JOIN
6                      TABLE ACCESS FULL CUSTOMERS
7                      HASH JOIN
8                          TABLE ACCESS FULL TIMES
9                          PARTITION RANGE ITERATOR
10                             TABLE ACCESS BY LOCAL INDEX ROWID SALES
11                                 BITMAP CONVERSION TO ROWIDS
12                                      BITMAP AND
13                                          BITMAP MERGE
14                                               BITMAP KEY ITERATION
15                                                   BUFFER SORT
16                                                        TABLE ACCESS FULL CUSTOMERS
17                                                   BITMAP INDEX RANGE SCAN SALES_CUST_BIX
18                                          BITMAP MERGE
19                                               BITMAP KEY ITERATION
20                                                   BUFFER SORT
21                                                        TABLE ACCESS FULL CHANNELS
22                                                   BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX
23                                          BITMAP MERGE
24                                               BITMAP KEY ITERATION
25                                                   BUFFER SORT
26                                                        TABLE ACCESS FULL TIMES
27                                                   BITMAP INDEX RANGE SCAN SALES_TIME_BIX

Seperti telah dijelaskan sebelumnya, query diproses dalam dua fase. Pada fase pertama, Oracle Database menggunakan bitmap index pada kolom-kolom foreign key dari tabel fakta untuk mengidentifikasi dan mengambil hanya baris-baris yang diperlukan dari tabel fakta. Yaitu, Oracle Database akan memperoleh result set dari tabel fakta.

Tabel sales memiliki bitmap access path alih-alih full table scan. Untuk setiap nilai key dari subquery (baris 16, 21, 26), bitmap diambil dari index dalam tabel fakta (baris 17, 22, 27). Setiap bit di dalam bitmap berkorespon dengan suatu baris di dalam tabel fakta. Bit diubah apabila nilai key dari subquery sama dengan nilai di dalam baris dari tabel fakta. Bit diubah jika nilai key dari subquery sama dengan nilai di dalam baris dari tabel fakta. Contohnya, bitmap [1][0][1][0][0][0]…(baris-baris berikutnya yang tersisa semuanya bernilai 0) mengindikasikan bahwa baris 1 dan 3 dari tabel fakta cocok dengan nilai key dari subquery. Misalkan bitmap di atas untuk nilai key dari subquery dari tabel times yang korespon terhadap penjualan dalam 1999-01.

Operasi pada baris 14, 19, 24 beriterasi berdasarkan key-key dari subquery dan memperoleh bitmap yang sesuai. Misalkan subquery dari tabel times menghasilkan satu lagi bitmap [0][1][0][0][0][0]… yang korespon terhadap penjualan pada 1999-02.

Bitmap untuk setiap subquery di-merge (dilakukan operasi OR) (baris 13, 18, 23). Pada contoh di atas, akan dihasilkan bitmap  [1][1][1][0][0][0]… untuk subquery times setelah merge kedua bitmap.

Bitmap yang di-merge kemudian dilakukan operasi AND (baris 12). Misalkan bitmap dari channels adalah [1][0][0][0][0][0]… Apabila Anda menjalankan operasi AND pada bitmap ini dengan bitmap dari subquery customers akan dihasilkan bitmap [1][0][0][0][0]…

Rowid dari bitmap akhir diperoleh (baris 11). Baris-baris dari tabel fakta diperoleh dengan rowid (baris 10). Pada contoh di atas, akan dihasilkan hanya satu rowid yang berkorespon dengan baris pertama dan mengambil hanya satu baris alih-alih men-scan keseluruhan tabel fakta.

Representasi dari bitmap pada contoh di atas hanya untuk tujuan ilustrasi. Dalam   Oracle, hanya disajikan dan disimpan dalam bentuk yang kompres.

Fase kedua dari query ini adalah men-join kembali baris-baris dari tabel fakta ini (result set) ke tabel-tabel dimensi. Subquery dan bitmap tree hanya menyaring tabel fkata berdasarkan filter-filter dimensi, sehingga masih diperlukan join ke tabel dimensi. Join kembali ke tabel dimensi ini dihilangkan ketika semua pedikat pada tabel dimensi merypakan bagian dari predikat subquery semijoin, kolom-kolom yang diseleksi dari subquery adalah unik dan kolom-kolom dimensi tidak berada dalam daftar select, group by, dan lain-lain. Pada contoh di atas, tabel channels tidak di-join kembali ke tabel sales karena tidak direferensi di luar dan channel­­_id unik.

Oracle akan menggunakan metode yang paling efisien untuk mengakses dan men-join-kan tabel-tabel dimensi. Kebanyakan dimensi berukuran sangat kecil, dan table scan biasanya merupakan metode akses yang paling efisien untuk table-tabel dimensi ini. Untuk tabel-tabel dimensi berukuran besar, table scans bisa jadi bukan metode akses yang paling efisien. Pada contoh sebelumnya, bitmap index pada product.department dapat digunakan untuk mengidentifikasi secara cepat semua produk-produk di dalam departemen grosir. Optimizer pada Oracle secara otomatis menetapkan metode akses mana yang paling tepat untuk tabel dimensi tertentu, berdasarkan pengetahuan optimizer mengenai ukuran dan distribusi data dari setiap tabel dimensi.

Metode join yang spesifik (dan juga metode indexing) untuk setiap tabel dimensi akan ditentukan oleh optimizer. Hash join seringkali merupakan algoritma yang paling efisien untuk men-join-kan tabel-tabel dimensi. Jawaban akhir yang dikembali kepada user setelah semua tabel-tabel dimensi telah di-join-kan. Teknik query untuk mengambil hanya baris-baris yang cocok dari sebuah tabel dan kemudian men-join-kannya dengan tabel lain biasanya dikenal dengan semijoin.

Penggunaan Star Transformation pada Oracle

Optimizer menghasilkan dan menyimpan plan terbaik yang dapat dihasilkannya tanpa transformasi. Apabila transformasi diaktifkan, optimizer kemudian mencoba untuk menerapkannya pada query, dan jika sesuai, menghasilkan plan terbaik menggunakan query yang ditransformasi. Berdasarkan perbandingan estimasi cost antara plan terbaik untuk kedua versi dari query, optimizer kemudian akan menentukan apakah akan menggunakan plan terbaik untuk versi yang ditransformasi atau yang tidak ditransformasi.

Apabila query membutuhkan akses ke baris-baris dari tabel fakta dalam jumlah besar, akan lebih baik menggunakan full table scan dan tidak menggunakan transformasi. Namun, jika predikat-predikat yang membatasi tabel-tabel dimensi cukup selektif sehingga hanya sebagian ekcil dari tabel fakta yang perlu diambil, plan berdasarkan transformasi mungkin akan sangat membantu.

Perhatikan bahwa optimizer menghasilkan subquery untuk tabel dimensi hanya jika optimizer memutuskan bahwa hal itu memungkinkan untuk dilakukan berdasarkan jumlah dari kriteria. Tidak ada jaminan bahwa subquery akan dihasilkan untuk semua tabel dimensi. Optimizer dapat juga memutuskan, berdasarkan karakteristik dari tabel-tabel dan query, bahwa transformasi tidak berguna jika diterapkan pada query tertentu. Dalam kasus ini plan terbaik dari query biasa akan digunakan. (Lane, 2005, pp. 19-8)

Referensi:

Read Full Post »