Tag: database

Menemukan Query yang dieksekusi

sedang asik asik “nguery”

ketik ketik cantik panjang, tekan f5 di SQL Server Management Studio (SSMS) ,

copy hasil ke excel

lalu klik close window di SSMS , wew lupa save query panjang itu !!

jangan keburu panik dan bunuh diri…

kembali ke SSMS anda, dan jalankan query :


SELECT dest.TEXT AS [Query],
deqs.execution_count [Count],
deqs.last_execution_time AS [Time]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

alhasil query kita masih dapat dikembalikan lagi ..
wew leganya…

Menghapus semua FK constraint pada tabel innodb di mysql

ternyata banyak juga ya yang belum bisa move on!

hehe , dari postingan saya sebelumnya,  ternyata banyak yang masih cinta sama MyISAM !..

tapi setelah mengikuti contoh, banyak yang gagal, kenapa ?

tentu karena adanya constraint foreign key dari InnoDB yang telah terinstall

untuk itu untuk membereskannya, harus di hapus semua dulu foreign key constraint yang sudah kadung melekat di dalam dada..

caranya tentu dengan menghapus constraint satu per satu, namun karena kita pintar dan malas, tentu kita pakai script saja untuk mengenerate script menghapus tersebut

berikut scriptnya


select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';')
from information_schema.table_constraints
where constraint_type='FOREIGN KEY'

yai… dengan script ini maka semua rintangan halangan dan constraint foreign key akan terhapus juga..

Merubah semua engine table pada mysql

bagi yang menggunakan mysql, secara paham atau tidak,
suka atau tidak , sadar atau tidak pasti akan menggunakan table engine yang tersedia di mysql

jaman dulu sih biasanya menggunakan MyISAM sebagai default table engine nya, sedangkan yang kekinian menggunakn INNODB

anggap saja saya orang yang susah move on, MyISAM tetap jadi primadona… dengan segala kekurangannya.

nah karena sekarang setiap membuat table otomatis INNODB, maka sedikit banyak mengganggu, apalagi kalo buat tabel yang banyak sekali sampe lupa ngecek.. jadi benalu dalam palung hati yang terluka ini.

untuk merubahnya menjadi myISAM bisa kita lakukan dengan merubah satu demi satu tabel, tapi apa daya, rasa malas ini mengalahkan daya juang api dingin ini… , solusinya adalah mengenerate script yang akan merubah tipe engine ini.
script tersebut adalah :

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;')
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

yayy!!.. semoga membantu orang2 malas yang lain…

happy sql ing

btw , tentu script ini juga bisa membalikkan keadaan dari myISAM ke INNODB dll 😀

Ringkasan Macam Bentuk Backup pada Oracle Database

Proses Backup salah satu proses yang penting pada Sistem Basis Data. Beribu alasan dapat dikemukakan mengapa backup ini sangat penting mulai dari sekedar memindahkan data sampai mengembalikan data yang hilang akibat kelalaian personal ataupun kesalahan sistem.

kali ini mari kita lihat opsi proses backup apa saja yang ada di salah satu database tersohor di dunia, yaitu Oracle . Opsi – opsi tersebut adalah :

Export

Opsi ini merupakan opsi logikal dari proses backup. Proses ini akan melakukan “copy” database object yang akan dilakukan export ke dalam bentuk propietary binary. Opsi ini cocok untuk proses backup yang dilakukan pada target objek database spesifik, misalnya pada satu atau beberapa tabel saja, walaupun opsi ini juga bisa dilakukan untuk keseluruhan objek dari database.  Model ini sangat mudah untuk dilakukan , sehingga banyak pengguna oracle menggunakan metode ini untuk melakukan backup.

Salah satu kekurangan dari model ini adalah adanya kemungkinan terjadi ketidakkonsistenan dari hasil backup, terutama jika tabel atau objek database yang dilakukan backup tidak mencantumkan table yang berelasi secara schema (foreign key constraint) maka akan ditemukan kesulitan saat melakukan restore karena pengencekan foreign key constraint tersebut. Model ini juga model backup yang termasuk lamban, dan tidak cocok untuk database dengan ukuran yang  besar, beberapa pengguna oracle mengatakan ukuran yang besar adalah > 50GB.

Contoh penggunaan :


exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

Data Pump (Export)

Model backup ini merupakan upgrade dari model sebelumnya. Model ini muncul pertama kali di database oracle 10G.

contoh penggunan

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle';
Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;
Grant succeeded.

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Cold / Offline Backup

prinsipnya, cold backup ini dilakukan dengan cara mematikan database , lalu melakukan copy file database seperti *.dbf dan *.ora

Hot / Online Backup

hot backup ini merupakan kebalikan dari cold backup, dengan teknik teknik khusus , file database dapat dicopy walaupun database masih dalam kondisi online.

RMAN Backup

rman adalah utilitas khusus yang dibangun oleh oracle untuk kebutuhan backup dan restore database dengan lebih cepat dan lebih effisien, mendukung fitur – fitur khusus seperti incremental backup dan lain sebagainya.

  • catatan penting : konfigurasi penting dari database oracle adalah mode archivelog, konfigurasi ini menentukan jenis backup apa saja yang dapat dilakukan.

archivelog on :

  1. export
  2. offline backup
  3. online backup
  4. rman

archivelog off (noarchivelog) :

  1. export
  2. offline backup
  3. rman

 

sekian ringkasan kali ini…

Cara Cepat Menampilkan History Database Size

Seringkali, kita diminta secara mendadak untuk menampilkan history ukuran database dari waktu ke waktu,

idealnya kita harus menghitung ukuran per baris dari data yang ada. namun terdapat juga beberapa query yang dapat membantu kita tanpa melakukan hal tersebut , beberapa yang berhasil saya kumpulkan antara lain :

{ ORACLE } :


select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');

dengan query ini, kita akan mendapatkan ukuran database dari waktu ke waktu memanfaatkan v_$datafile , yang mana tentu syaratnya database anda belum di restart. jika sudah direstart maka v_$datafile pun akan kehilangan catatannya.

{SQL SERVER} :


SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

dengan query ini , kita memanfaatkan pencatatan backup yang dilakukan oleh SQL SERVER, tentunya ini hanya berlaku untuk database yang rutin di backup , jika tidak rutin ya wassalam.

ok sekian selamat berhitung

Clustered Index vs Non Clustered Index pada SQLSERVER

Indexing merupakan salah satu cara untuk mempercepat kinerja dari sistem basis data.  Namun banyak dari kita yang belum memahami dengan baik index tersebut. Maksud saya dari belum memahami, yaitu dengan menggunakan misalnya kondisi default dari index. Misalnya saja, pada pengguna SQLSERVER maka secara default saat anda menggunakan SQLSERVER Management Studio untuk membuat tabel dengan sebuah primary key, maka kolom yang dijadikan primary key tersebut akan dibuat sebuat index clustered.  Padahal, kita mempunyai opsi lain yaitu non-clustered, lalu apa bedanya ? apa efek dari penggunaannya ?

Clustered Index dan Non Clustered Index

dalam situs sqlserver sebenarnya telah dijelaskan dengan umum apa bedanya [1],[2]. Struktur kedua index tersebut sebenarnya mirip , yaitu menggunakan B+Tree Index [5]. hanya saja terdapat perbedaan yang sangat fundamental pada leaf node masing – masing jenis index tersebut.

ic157372

 sumber (https://msdn.microsoft.com/en-us/library/ms177443.aspx)

pada gambar di atas adalah gambar index clustered yang saya ambil dari situs [1]. Pada gambar tersebut terlihat struktur standard index B+Tree  yang mana leaf node nya mengarah langsung ke data. Di situs yang lain, Pakde Markus Winand [3] memaparkan dengan lebih detail dan lebih jelas menurut saya. Menurut beliau, dengan menggunakan clustered index, sistem akan memaksa leaf node berurutan sesuai dengan urutan dari index nya, bahasa saya sih leaf node akan berjajar rapi. Sehingga jika saya bayangkan, bahwa dengan menggunakan clustered index ini, menjadikan model penyimpanan data kita menjadi tightly coupled dengan index, artinya tidak ada lagi pemisahan antara index dengan leaf node (atau data). Dengan begitu juga dapat dibayangkan bahwa sebuah tabel pasti tidak bisa memiliki lebih dari 1 clustered index.

berbeda dengan non clustered, dari namanya saja sudah dapat dibayangkan kalau itu merupakan lawan dari clustered :D.

ic88960
Sumber (https://msdn.microsoft.com/en-us/library/ms177484.aspx)

Gambar di atas merupakan gambar struktur index non clustered yang saya ambil dari situs [2]. Dari gambar tersebut terlihat struktur terpisah menjadi 2 yaitu (root node + leaf node) dan data pages. Untuk (root node + leaf node) merupakan struktur non clustered indexnya sendiri dan untuk data pages ini bisa langsung data (heap) atau bahkan clustered index. Artinya bisa saya katakan jika di dalam tabel tersebut tidak ada clusterd index, maka data pages ini akan berupa heap structure yang menyimpan data ( bayangkan saja seperti linked list [4] ). Namun jika dalam tabel tersebut terdapat clustered index, maka data pages nya akan mengacu pada clustered index tersebut. Dari gambar ini dapat kita lihat jika non clustered index lebih bersifat loosly coupled, yang mana keberadaan index tidak akan mempengaruhi bagaimana data tersebut disimpan.

Efek Penggunaan Clustered Index pada Index non Clustered

Pakde Markus Winand [3], memiliki gambar yang lebih ciamik lagi untuk menggambarkan keadaan index yang menggunakan clustered atau tidak, seperti yang terlihat pada gambar – gambar di bawah ini.

fig05_02_index_on_heap_table-x1
Sumber (http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key)

pada gambar di atas , merupakan contoh struktur non clustered index pada tabel yang tidak memiliki clustered index. Sehingga pada leaf node nya akan memiliki referensi RID ke data pages nya. Hal ini merupakan cost tambahan pada pencarian data karena akan melakukan RID lookup ke data untuk mencari kolom – kolom yang dibutuhkan pada query yang diinginkan.

fig05_03_secondary_index_on_clustered_index-x1
Sumber (http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key)

Sedangkan pada gambar di atas merupakan contoh non clustered index pada sebuah tabel yang memiliki clustered index. Perbedaannya adalah leaf node pada non clustered index tidak mengacu pada RID lagi namun kepada ID primary key dari clustered index. Sehingga tidak terjadi RID lookup namun key lookup. Mengapa keadaan ini terjadi, mengapa tidak tetap menggunakan RID ?. Hal ini karena sifat tightly coupled dari clustered index terhadap data tersebut. Dengan sifat tersebut maka data akan diatur sedemikian rupa untuk mengikuti aturan dari clustered index yang berbasis b+tree. Jika terdapat penambahan data atau pengurangan data, maka sebuah b+ tree pasti akan melakukan penyesuaian – penyesuaian agar tree yang ada tetap balance, karena dengan keadaan balance tree tersebut lebih menjamin kecepatan dalam melakukan pengaksesan data ( tree traversal ). Hal itu berarti juga data yang tersimpan mungkin saja akan mengalami pergerseran tempat disesuaikan dengan keadaan clustered indexnya. Sehingga RID tidak dapat dijadikan patokan tempat bernaung sebuah data, karena data dapat berpindah sesuai dengan keadaan terkini. Hal ini disebut oleh pakde MarkusWinand sebagai Living Creature.  Dan hal tersebut berarti biaya dari non clustered index akan ditambah biaya pencarian clustered index. Hal itu disebut sebagai clustered index penalty oleh pakde MarkusWinand.

Uji Clustered Index vs Non Clustered Index

Setelah membaca beberapa paparan di atas, mungkin anda melihat tendensi saya sedikit memojokkan clustered index, sebenarnya tidak demikian namun memang dikarenakan saya melihat clustered index memiliki kemampuan yang baik namun pada kasus kasus tertentu.

Secara umum , guideline dan kelebihan clustered index dan non clustered index dapat dilihat pada [6], [7]. Namun guildeline itu bagi saya masih sangat ambigu dan tidak menjawab dengan tegas dimana letak kelebihan masing – masing model index. Untuk itu pada post kali ini kita akan menggunakan percobaan kita sendiri. Kita akan membuat 2 buah tabel yang identik secara kolom namun berbeda secara penggunaan clustered index.

persiapan pembuatan tabel

untuk tahap awal mari kita membuat tabel seperti berikut :

pada Script di atas, kita akan membuat 2 tabel yang memiliki kolom :

  1. ID : primary Key, auto increment integer
  2. Nama : nama person varchar(50)
  3. Umur : umur person int

kedua tabel memiliki definisi index constraint PK yang berbeda , salah satu menggunakan constraint CLUSTERED yang merupakan settingan default dari sqlserver, sisanya menggunakan constraint PK yang NON CLUSTERED.

selain itu kedua tabel ditambahkan index NON CLUSTERED pada kolom umur.

Setelah itu, data [8] yang berjumlah 700.000 record dimasukkan ke dalam kedua tabel.

Percobaan pada constraint Primary Keys

Percobaan menggunakan keyword  range ” > “

untuk percobaan ini kita melakukan 2 query .

hasil dari query tersebut adalah :

hasil_tes1_cluster

sedangkan untuk query  :

hasilnya adalah :

hasil_tes1_non_cluster.png

dari hasil di atas beberapa variabel yang dapat kita amati adalah :

  1. Logical Read : Seberapa banyak data yang akan dibaca secara logic bukan secara real ( karena secara real sangat dipengaruhi oleh cache)
  2. CPU Time : seberapa lama CPU time yang dibutuhkan
  3. Elapsed Time : seberapa lama Waktu yang dibutuhkan

Jika kita melihat hasilnya, dapat dilihat bahwa untuk index non clustered akan menghasilkan logical reads yang lebih banyak dibandingkan dengan index clustered. hal ini dikarenakan adanya biaya RID lookup pada masing  – masing item pada leaf node di index non clustered (silakan lihat lagi gambar index non clustered) . Berbeda dengan index clustered yang datanya telah terintegrasi dengan struktur B+ Tree, maka tidak perlu banyak logical reads untuk mendapatkan data tersebut. Index clustered hanya membutuhkan titik leafnode awal untuk start dan berakhir pada 10000 (select top 10000) data dengan cara direct linked list traversal tidak lagi menggunakan tree traversal. Dari sini dapat kita lihat kelebihan dari index clustered.

Percobaan dengan menggunakan keyword “between” (range)

untuk Query

hasilnya :

hasil_tes_3_cluster

sedangkan untuk Query :

hasilnya :

hasil_tes_3_non_cluster.png

untuk hasil dengan keyword between, kembali primary keys (PK) dengan index clustered sangat unggul dari PK dengan index non clustered. Hal ini jelas memang keunggulan dari index clustered yang tidak memiliki RID lookup sehingga hanya memerlukan 1 kali tree traversal untuk menemukan data dengan nilai terendah dan dilanjutkan dengan linked list walking pada data tersebut sampai menemukan nilai tertinggi, maka dengan itu memang logical reads nya jadi sangat rendah yaitu 3 , yang mana asumsinya adalah hal ini merupakan tree traversal.  Berbeda dengan index non clustered yang mana memiliki logical reads sampai 57. Hitungan – hitungan kasar merupakan ( jml hop pada tree traversal + jml RID lookup) => (3 (asumsi tree travelsal sama dengan clustered index) + 54 (jml data)). Hal ini berarti memang untuk tiap item data akan dilakukan RID lookup pada data, hal ini yang menyebabkan logical reads menjadi tinggi.

Percobaan pada index non clustered

Percobaan menggunakan keyword  range ” > “

pada percobaan ini kita ingin melihat apa ada efek penggunaan index clustered pada index non clustered dalam 1 buah tabel .

untuk query

hasilnya :

hasil_tes_2_clustered.png

sedangkan untuk query

hasilnya :

hasil_tes_2_non_cluster

dari hasil tersebut dapat dilihat bahwa clustered index sangat mempengaruhi non clustered index dalam 1 tabel. Pada tabel yang memiliki clustered index, nilai logical reads bisa sampai 3 kali lipat dari tabel yang tidak memiliki clustered index. Hal ini yang bisa disebut sebagai clustered index penalty. Karena leaf node index non clustered tidak langsung mengarah pada physical address dari data (RID) namun leaf node akan berhubungan dengan key dari index clustered. Dengan kata lain kita akan punya 2 kali tree traversal untuk mendapatkan sebuah record.

Diskusi ….

Dari percobaan di atas, dapat dilihat penggunaan clustered index pada tabel sangat powerfull untuk range query pada kolom yang dijadikan index tersebut. Hal ini dikarenakan sifat dari clustered index yang terintegrasi dengan data sehingga data menjadi terurut dan teratur sesuai aturan index tersebut. Di lain sisi , non clustered index menggunakan RID lookup yang menjadi titik lemah saat query range ini.

Namun dengan adanya clustered index pada sebuah tabel, membuat index lain (pada kolom lain) yang pastinya non clustered terpengaruh. Hasil ujicoba memperlihatkan bahwa tabel dengan PK non clustered akan mengungguli tabel dengan PK clustered. Hal ini dikarenakan adanya clustered index penalty yang membuat table dengan PK clustered harus melakukan 2 tree traversal.

Sistem default dari SQLSERVER yang langsung menganggap PK adalah clustered perlu diwaspadai oleh pengguna, karena sangat mempengaruhi kinerja dari sistem selanjutnya. pertanyaan yang mungkin dapat membantu menentukan menurut saya adalah :

  1. Seberapa sering PK akan diquery range ?
  2. dan Seberapa banyak index non cluster lain yang akan diakses oleh sistem ?

jika bobot nya lebih banyak pada no 1, yaitu lebih ke arah PK maka memang lebih baik menggunakan clustered index, namun saya yakin bahwa akan lebih banyak kasus lebih menitik beratkan pada no 2, yang mana non clustered PK lebih prefer untuk digunakan. Mengapa demikian ? karena menurut pengetahuan saya, kebanyakan PK digunakan oleh sistem modern sekarang adalah sebagai surrogate key [9], yang mana sangat jarang untuk kita melakukan query range tersebut.

bagaimana dengan anda ?

Referensi :

[1] https://msdn.microsoft.com/en-us/library/ms177443.aspx

[2] https://msdn.microsoft.com/en-us/library/ms177484.aspx

[3] http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key

[4] https://en.wikipedia.org/wiki/Linked_list

[5] https://en.wikipedia.org/wiki/B%2B_tree

[6] https://msdn.microsoft.com/en-us/library/ms190639.aspx

[7] https://msdn.microsoft.com/en-us/library/ms179325.aspx

[8] https://github.com/radityopw/blog_files/blob/master/clustered%20vs%20non-clustered%20index%20sqlserver/data_person.csv

[9] https://en.wikipedia.org/wiki/Surrogate_key

Uncategorized No Comments

offscale

mungkin kita biasa menggunakan SCM semacam SVN, Git sebagai soure code management kalo pas ngoding bahasa gaol macam PHP Java dkk

tapi pas banyak “ngoding” stored procedure , function dkk di lingkungan database kita bingung nih gimana manage code itu menggunakan SCM..

sementara solusinya ya menggunakan offscale ini, tapi masih ada masalah nih… gak ada installer untuk windows nya 🙁

have fun code