Veri Madenciligi(Data Mining) Nedir ve Nerelerde Kullanılır

Gelişen ve değişen çevre koşulları, sınırların kalkması ile küreselleşen dünya, farklı pazarlama ve ar-ge(araştırma geliştirme) yöntemleri “veri”nin değil “bilgi”nin önemini her geçen gün daha da artacak şekilde ortaya koymaktadır. İnternetin yaygınlaşması ve kolaylaşması ar-ge ekiplerinin “bilgi”ye erişmelerini zorlaştırmaktadır. İnternette arama motorları kullanılarak yapılan araştırmalar çoğu zaman istenilenden farklı bir şekilde sonuçlanmaktadır. Tıbbi bir araştırma sonucunda elde edilen verilerin yorumlanıp analiz edilmesiyle bilgiye ulaşılabilmektedir. Büyük bir perakendecinin, fatura bilgilerinden müşteri eğilimlerini belirleyip ona göre pazarlama taktikleri üretebilmesi, rakiplerinin önüne geçmesini sağlayacaktır. Verilen örneklere dikkat edilirse, “veri”nin “bilgi”ye dönüşme işleminin vurgulandığı görülecektir. Bilginin kimi yöntemler ile analiz edilmesi ve çıkan sonuçların bir uzman gözüyle yorumlanmasıyla geçmiş verilerden gelecek tahminleri yapma işlemi veri madenciliği(data mining) olarak belirtilebilir

1.    Veri Madenciliği Nedir?

Yukarıda belirtildiği gibi veri madenciliği, eldeki verilerden üstü kapalı, çok net olmayan, önceden bilinmeyen ancak potansiyel olarak kullanışlı bilginin çıkarılmasıdır. Bu da; kümeleme, veri özetleme, değişikliklerin analizi, sapmaların tespiti gibi belirli sayıda teknik yaklaşımları içerir (William J. Frawley, Gregory Piatetsky – Shapiro, Cristopher J. Matheus).

Başka bir deyişle, veri madenciliği, verilerin içerisindeki desenlerin, ilişkilerin, değişimlerin, düzensizliklerin, kuralların ve istatistiksel olarak önemli olan yapıların yarı otomatik olarak keşfedilmesidir.

Temel olarak veri madenciliği, veri setleri arasındaki desenlerin ya da düzenin, verinin analizi ve yazılım tekniklerinin kullanılması ile ilgilidir. Veriler arasındaki ilişkiyi, kuralları ve özellikleri belirlemekten bilgisayar sorumludur. Amaç, daha önceden fark edilmemiş veri desenlerini tespit edebilmektir.

Veri madenciliğini istatistiksel bir yöntemler serisi olarak görmek mümkün olabilir. Ancak veri madenciliği, geleneksel istatistikten birkaç yönde farklılık gösterir. Veri madenciliğinde amaç, kolaylıkla mantıksal kurallara ya da görsel sunumlara çevrilebilecek nitel modellerin çıkarılmasıdır. Bu bağlamda, veri madenciliği insan merkezlidir ve bazen insan – bilgisayar arayüzü birleştirilir.

Veri madenciliği sahası, istatistik, makine bilgisi, veri tabanları ve yüksek performanslı işlem gibi temelleri de içerir.

Veri madenciliği konusunda bahsi geçen geniş verideki geniş kelimesi, tek bir iş istasyonunun belleğine sığamayacak kadar büyük veri kümelerini ifade etmektedir. Yüksek hacimli veri ise, tek bir iş istasyonundaki ya da bir grup iş istasyonundaki disklere sığamayacak kadar fazla veri anlamındadır. Dağıtık veri ise, farklı coğrafi konumlarda bulunan verileri anlatır.

Veri madenciliği, günlük yaşamda birçok şekilde kullanılabilmektedir. Bunlardan bazıları aşağıdaki gibi sıralanabilir:

• Hastanelere yapılan tedavi taleplerinin bölgelere, zamana ve ihtiyaca göre değerlendirmesi salgın hastalık riskinin ilk aşamada tespiti, kontrolü ve kaynak planlama açısından faydalı olur.
• Kaçak enerji kullananların profillerini tespit eden bir model, olası kaçak enerji kullanıcılarını tahmin etmeyi sağlayacak, düşük maliyet ile kaçaklarla etkin mücadele edilmesine olanak tanıyacaktır.

• Karayollarının bölgelere ve zamana göre yoğunluklarını öngörme amaçlı bir çalışma doğru zamanda doğru kaynak planlaması ile örneğin kaza oranlarının asgariye indirilmesini sağlayacaktır.
• Kamu kurumları destek programlarını uygularken, verilecek desteğin doğru miktarda ve doğru hedefleri olan kuruluşlara verilmesini sağlayacak kurumsal risk skorlaması yapılmasıyla uygulanan programların başarısı artar.
• Kredileri tahsis ederken ödememe riski olan profillerin tespit edilmiş olması batık kredi miktarlarını azaltır.

1.1. Örnek Uygulamalar

Veri Madenciliğinde kullanılan yaklaşımları aşağıda belirtildiği gibi sıralayabiliriz:

• Bağıntı: “Çocuk bezi alan müşterilerin %30’u bira da satın alır.” Sepet analizinde (basket analysis) müşterilerin beraber satın aldığı malların analizi yapılır. Buradaki amaç mallar arasındaki pozitif veya negatif korelâsyonları bulmaktır. Çocuk bezi alan müşterilerin mama da satın alacağını veya bira satın alanların cips de alacağını tahmin edebiliriz ama ancak otomatik bir analiz bütün olasılıkları göz önüne alır ve kolay düşünülemeyecek, örneğin çocuk bezi ve bira arasındaki bağıntıları da bulur.

• Sınıflandırma: “Genç kadınlar küçük araba satın alır, yaşlı, zengin erkekler büyük, lüks araba satın alır.”Amaç bir malın özellikleri ile müşteri özelliklerini eşlemektir. Böylece bir müşteri için ideal ürün veya bir ürün için ideal müşteri profili çıkarılabilir. Örneğin bir otomobil satıcısı şirket geçmiş müşteri hareketlerinin analizi ile yukarıdaki gibi iki kural bulursa genç kadınların okuduğu bir dergiye reklâm verirken küçük modelinin reklâmını verir.

• Regresyon: “Ev sahibi olan, evli, aynı iş yerinde beş yıldan fazladır çalışan, geçmiş kredilerinde geç ödemesi bir ayı geçmemiş bir erkeğin kredi skoru 825’dir.” Başvuru skorlamada (application scoring) bir finans kurumuna kredi için başvuran kişi ile ilgili finansal güvenilirliğini notlayan örneğin 0 ile 1000 arasında bir skor hesaplanır. Bu skor kişinin özellikleri ve geçmiş kredi hareketlerine dayanılarak hesaplanır.

• Zaman İçinde Sıralı Örüntüler: “İlk üç taksitinden iki veya daha fazlasını geç ödemiş olan müşteriler %60 olasılıkla kanuni takibe gidiyor.” Davranış skoru (behavioral score), başvuru skorundan farklı olarak kredi almış ve taksitleri ödeyen bir kişinin sonraki taksitlerini ödeme/geciktirme davranışını notlamayı amaçlar.

• Benzer Zaman Sıraları: “X şirketinin hisse fiyatları ile Y şirketinin hisse fiyatları benzer hareket ediyor.” Amaç zaman içindeki iki hareket serisi arasında bağıntı kurmaktır. Bunlar örneğin iki malın zaman içindeki satış miktarları olabilir. Örneğin dondurma satışları ile kola satışları arasında pozitif, dondurma satışları ile salep satışları arasında negatif bir bağıntı beklenebilir.

·        Fark Saptanması: “Normalden farklı davranış gösteren müşterilerim var mı?” Amaç önceki uygulamaların aksine kural bulmak değil, kurala uymayan istisnai hareketleri bulmaktır. Bu da örneğin olası sahtekârlıkların saptanmasını (fraud detection) sağlar. Örneğin Visa kredi kartı için yapılan CRIS sisteminde bir yapay sinir ağı kredi kartı hareketlerini takip ederek müşterinin normal davranışına uymayan hareketler için müşterinin bankası ile temasa geçerek müşteri onayı istenmesini sağlar.

• Doküman Madenciliği: “arşivimde (veya internet üzerinde) bu dokümana benzer hangi dokümanlar var?”Amaç dokümanlar arasında ayrıca elle bir tasnif gerekmeden benzerlik hesaplayabilmektir (text mining). Bu genelde otomatik olarak çıkarılan anahtar sözcüklerin tekrar sayısı sayesinde yapılır.

2.    Veri Madenciliğinin Kullanım Alanları

Veri Madenciliği kullanım alanı olarak çok geniş bir yelpazeye sahiptir. Örnek uygulama alanları aşağıda belirtilmiştir:

2.1. Finans Sektörü

Finans ve sigorta sektörü günümüzde sundukları hizmet, ürün ve servislerle bilgiye dayalı yönetime en fazla ihtiyaç duyan kuruluşlardır. Bu sektörde bilgiye dayalı yönetim özellikle ekonomik krizin yaşattığı sonuçlar göz önüne alındığında tartışmasız önemli ve zorunludur. Finans sektöründe en temel uygulamalar çapraz satış, risk derecelendirme, mevcut müşteriyi elde tutma, yeni müşteriler kazanma, maliyetleri azaltma, kayıp ve kaçakları engelleme, alternatif kanallar oluşturma, müşteri memnuniyetini sağlama olarak özetlenebilir. Hangi müşteri profilinin neyi, ne zaman ve neden tercih ettiğini anlayabilen bir kuruluş hem talep yaratma, hem de doğru zamanda doğru talebi karşılama ve sunma avantajına sahip olacaktır. Kuruluşun karlılığı artarken, müşterinin memnuniyeti de artacağından, aynı zamanda müşteri sadakati de sağlanmış olacaktır ki, ağ ekonomisinin en büyük kaosu budur. Mevcut müşteri kaybı, finans ve sigorta sektörlerinde en önemli problemi teşkil etmektedir. Yeni bir müşteri kazanmanın maliyetinin müşteriyi elde tutma maliyetinden daha yüksek olduğu, kaybedilen bir müşteriyi yeniden kazanma maliyetinin yeni müşteriler edinme maliyetinden daha fazla olduğu göz önüne alındığında şirketler müşteri odaklı gitmek ve mevcut müşteriyi ellerinde tutmak zorundadır. Bankalar, mevcut müşterilerden rakip bankaya geçme ihtimali olan müşterileri, profillerini ve kaybettikleri müşterilerin hangi sebepler yüzünden sistemden ayrıldıklarını tespit etmek istemektedir.

2.2. Haberleşme Sektörü

Telekom sektöründe en önemli sorun müşteri kaybıdır. Kuruluşlar hangi müşterilerini kaybedebileceklerini önceden belirleyebildikleri taktirde bu müşterilerini elde tutma amaçlı stratejiler geliştirebilir, düşük maliyetli ve etkili kampanyalar düzenleyebilirler. Kaybetme olasılığı olmayan bir müşteriye kalıcılığını sağlama amaçlı bir mesaj göndermek hem müşterinin kendisine verilmek istenen mesajın ne olduğunu algılamasını zorlaştıracak hem de maliyetleri artıracaktır. Örneğin Amerika’nın en büyük kablosuz iletişim sağlayıcısı olan Verizon kaybetme olasılığı yüksek olan müşterilerini ve müşteri kaybına neden olan faktörleri belirleme amaçlı bir Veri Madenciliği çalışması yapmıştır.

2.3. Sağlık Sektörü

Doğru ve zamanında karar almanın hasta sağlığı üzerindeki etkisi tartışmasız çok önemlidir. Hastane bünyesinde toplanan operasyonel veriler, hasta verileri, uygulanan tedavi yöntemi ve tedavi sürecine dair veriler yöneticiler açısından bakıldığında; hastanedeki servislerin ve programların başarısının görüntülenmesi, kaynakların maliyetlerle göreceli olarak kullanımı, kaynak kullanımı ve hasta sayıları ile ilgili trendlerin tahmini, harcamalarla ilgili normal olmayan durumların anlık tespiti ve yolsuzlukların engellenmesi, hastanede uygulanan tedavi yöntemlerinin başarısının irdelenmesi açısından önemli bilgileri içermektedir. Bu veriler başarılı tedavi sonuçları almada etken faktörlerin belirlenmesi, ameliyatlarda yüksek risk faktörlerinin sınanması, hasta verilerinin yaş, cinsiyet, ırk ve tedavi yöntemi gibi faktörlere göre sınıflanması, hasta sağlığı açısından geriye dönük faktörlerin sınanması, tedavi yöntemi geliştirme vb. amaçlarla kullanılmaktadır. Dünya çapında çok sayıda başarılı uygulama örneği mevcuttur. Örneğin, San Francisco Hearth Institute; hasta sonuçlarının iyileştirilmesi, hastanın hastanede kalma süresinin azaltılması, vb amaçlarla bir çalışma başlatmış ve kurum bünyesinde toplanan verilerden hastanın geçmişine ait veriler, laboratuar verileri, kollestrol verileri, diğer medikal verileri bilgiye dönüştürmüştür.

2.4. Devlet Uygulamaları

Kamu yöneticileri günümüzde verinin ve bilginin önemini kavramışlardır. Müşteriye özel hizmet sunan ticari kuruluşlarda olduğu gibi devlet kurumları da vatandaşlarının ihtiyaçlarına özel hizmet sunabilmenin önemini kavramışlardır. Kamu yöneticileri için en önemli uygulamalar kaynakların doğru olarak kullanımını sağlama ve planlama; kamu güvenliğini sağlama amacı ile güvenlik problemlerini önceden tahmin etmek, rastlantısal olaylardaki sorunların çözümüne dair izleri keşfetme ve olası güvenlik sorunlarını eş zamanlı olarak tespit edebilme ve çözüm üretebilme; vergi ile ilgili yolsuzlukları ve izlerini belirleme, yolsuzlukları eş zamanlı olarak belirleme, sağlık ödemeleri, programların uygulanması vb. konularda şüpheli durumların tespiti, suiistimal ve israfları belirleme ve milyonlarca dolarlık zararı engelleme, örnekleri artırmak mümkündür. Kamuda enformasyon ve bilgi ihtiyacı sonsuzdur. Emniyet birimleri için suç istatistiklerine dair online raporlama, hangi profildeki insanların ne tür suçlara meyilli olduklarını belirleme, eş zamanlı suç engelleme politikaları oluşturmak ancak ileri analitik uygulamalar ile mümkündür. Günümüzde e-devlet kavramı oldukça kritiktir. E-devlet uzmanlarının en önemli hedefi bilgiye eş zamanlı olarak ulaşmak ve daha iyi hizmet vermektir. E-devlet uygulaması gerçekleştirilen ülkelerde kamu kuruluşları ziyaretçilerin sayfalarını nasıl kullandığı, ihtiyaç duyulan formlara kolayca ulaşılıp ulaşılamadığı, web sayfa tasarımın nasıl en iyi kullanılabilir hale getirilebileceği, hangi sayfaların hangi sıra ile ziyaret edildiğinin anlaşılması, geçmişteki ziyaretçi davranışlarına göre kurumun web sayfasını vatandaşın ihtiyacına daha iyi yanıt verecek şekilde yeniden düzenlemek mümkündür.

Kaynaklar

• Alper Vahaplar, Dr. Mustafa Murat İnceoğlu: Veri Madenciliği ve Elektronik Ticaret, VII. Türkiye’de İnternet Konferansı, 1–3 Kasım 2001
• Ethem Alpaydın, Ham Veriden Altın Bilgiye Ulaşma Yöntemleri, Boğaziçi Üniversitesi
• SPSS Kamu Günü, Sunu Notları,14 Nisan 2006
• http://www.spss.com.tr

Parallel Processing In PL/SQL

Parallel processing is to run the same process-usally a heavy process- simultaneously. It is called Parallel computing in computer science. Main idea of Parallel computing is to get faster responses. Wikipedi describes as

“Parallel computing is the simultaneous execution of the same task (split up and specially adapted) on multiple processors in order to obtain results faster. The idea is based on the fact that the process of solving a problem usually can be divided into smaller tasks, which may be carried out simultaneously with some coordination”

In database systems, Parallel processing takes role in huge datasets. For performance reasons, you can take advantages of Parallel processing. As Kyte mentions

“Parallel query is suitable for a certain class of large problems: very large problems that have no other solution. Parallel query is my last path of action for solving a performance problem; it's never my first course of action.”

Oracle has some Parallel procesing features as i gave in further readings section. You can investigate them. But the aim of this article is a question was asked by me in forums.oracle. According to business rules, i have to implement a kind of Parallel processing. The busines logic has implemented in pro C units. It was not possible to take logic into pl/sql because of business logic needs extra features that has to be implemented in C units. Business logic is a heavy process. To gain performance, it must be executed parallely. In case of updates, it is also prevented to get deadlocks. To achieve this situation, i find out some methods which i described below.

1.   Process

For demonstration a table called locks is used. Table is formed with an id and status columns.

drop table locks;

create table locks as SELECT rownum id FROM all_objects  WHERE rownum <= 100;

alter table locks add( status varchar2(16) );

Main process is taking records of locks table and updating them with given status.

UPDATE locks SET status = :new_status;

For simulating real world because of a heavy C routine executes, after every update statement, pl/sql engine forced to wait 3 seconds.

dbms_lock.sleep(3);

To analyze how much time spent to finih locks table, analyze_locks procedure will be used. This routine selects forever locks table until at least one status update is done. It takes the time and selects again locks table forever. When all rows are uptaded, time differenece is printed out.

Aim of this procedure to get exactly howmuch time passed to finish process. It will not be clear to taking out elapsed time SQL*Plus with set timing on because of Parallel processes that executes different sessions.

This procedure must be called when locks table is cleaned out and in seperate session before main process executes.

CREATE OR REPLACE PROCEDURE analyze_locks IS
  effected_row_count NUMBER := 0;
  start_time         NUMBER;
  diff               NUMBER;
BEGIN
  WHILE effected_row_count = 0 LOOP
    SELECT COUNT(*)
    INTO effected_row_count
    FROM locks
    WHERE status IS NULL;
  END LOOP;
  start_time := dbms_utility.get_time;
  WHILE effected_row_count > 0 LOOP
    SELECT COUNT(*)
    INTO effected_row_count
    FROM locks
    WHERE status IS NULL;
  END LOOP;
  diff := dbms_utility.get_time - start_time;
  DBMS_OUTPUT.PUT_LINE('Finished in ' || trunc(diff / 100) || ' ms.');
END;

Before processing starts, tables are recreated and analyze_locks procedure called.

2.   Methods

There are three methods called Usual, Bucket and Bounds mentioned

2.1.                   Usual

There is no Parallel process. Records of table processed as a single process. A cursor is opening and taking records one by one. Processing done with a procedure as shown below:

CREATE OR REPLACE PROCEDURE process_locks_table_usual_way(new_status IN VARCHAR2) AS

BEGIN

  FOR rec_locks IN (SELECT id, status

                      FROM locks

                     WHERE status IS NULL

                     ORDER BY id) LOOP

    UPDATE locks

       SET status = new_status || to_char(rec_locks.id)

     WHERE id = rec_locks.id;

    dbms_lock.sleep(3);

  END LOOP;

  COMMIT;

END;

2.1.1.   Processing

Because of there is no Parallel processes, only one session will be enough to execute procedure.

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table_usual_way('W');

PL/SQL procedure successfully completed

SQL>

Analyze results are

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec analyze_locks;

Finished in 367 ms.

PL/SQL procedure successfully completed

SQL>

The average execution time of this procedure after calling 3 times is 340 ms.

2.2.                   Bucket

In bucket way there is a pre process called bucketing. To making a Parallel process with bucketing, the records must be bucketed in a given size. This size can not be changed after bucketing. Every Parallel process will process only its records.

Bucketing can be renamed with packeting or grouping. When the table is bucketed, table is divided into groups with given size, logically. For instance when you give bucket size as 3, all records will be grouped in 3 different categories such as 1,2,3. The bucketing is done as balanced mode. That is all three categories has the same size(group 1 has 33 records, group 2 has 34 records, group 3 has 33 records)

Main drawback of method is, it is not possible to change bucket size when buckets are created. Processes must stopped and rebucketed the table with new bucket size. Another drawback is, when one process drops, it must be executed again with the same bucket number. Because other processes will not process records of dropped bucket number.

CREATE OR REPLACE PROCEDURE process_locks_table_bucket_way(

                                                 new_status IN VARCHAR2

                                                ,bucket_no  IN NUMBER) AS

BEGIN

  FOR rec_locks IN (SELECT id, status

                      FROM locks

                     WHERE bucket = bucket_no

                           AND status IS NULL

                     ORDER BY id) LOOP

    UPDATE locks

       SET status = new_status || to_char(rec_locks.id)

     WHERE id = rec_locks.id;

    dbms_lock.sleep(3);

  END LOOP;

  COMMIT;

END;

2.2.1.   Pre-Process : Bucketing

In order to bucketing it must be decided how many buckets will be created. Bucket size will determine number of Parallel processes. In this demonstration bucket size is defined as 3. Bucketing is done via rownum pseudocolumn and mod operator.

alter table locks add( bucket number );

update locks set bucket = mod( rownum, 3) + 1;

commit;

2.2.2.   Processing

Locks table is bucketed with size 3. That is, it is possible to execute 3 parallel process simultaneously in different sessions. On calling the procedure, it must be determined that which bucket will be processed in procedure.

In session 1

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table_bucket_way('A',1);

PL/SQL procedure successfully completed

SQL>

In session 2

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table_bucket_way('B',2);

PL/SQL procedure successfully completed

SQL>

In session 3

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table_bucket_way('C',3);

PL/SQL procedure successfully completed

SQL>

Analyze results are

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec analyze_locks;

Finished in 108 ms.

PL/SQL procedure successfully completed

The average execution time of this procedure after calling 3 times is 110 ms.

2.3.                   Bounds

Main idea of this method is to get bounds(especially primary key field of table) with a helper function. When one process starts, it will check whether more unprocessed records are left. If yes, it will get the bounds and process records only given range. Other process will take its own bounds and so on.

With Bounds method, there is no need of a predefined number of Parallel processes wheras Bucket method needs. Number of Parallel processes can be increased with requirements of business logic.When one of Parallel process dropped, other processes will process records. At least one process executes, it will process all records as a single process.

In this method an extra table called row_range is needed. This  table will store lower and upper bounds of locks table. It must be filled with 0,0 for initial bounds.

drop table row_range;

create table row_range(first_id number, last_id number );

INSERT INTO row_range VALUES( 0, 0);

commit;

A hepler procedure called get_bounds is called before one process start. Process takes a row range by the help of this procedure. row_count determines how many rows will be taken to create bounds. If it is specified with 1, every process will take only one record and when finished it will commit and take another record. LEAD analytic function is used for taking next row_countth record.

CREATE OR REPLACE PROCEDURE get_bounds(lower_bound OUT NUMBER

                                      ,upper_bound OUT NUMBER

                                      ,row_count   IN NUMBER) IS

  PRAGMA AUTONOMOUS_TRANSACTION;

  upper         NUMBER;

  next_upper    NUMBER;

  default_upper NUMBER := 999999999;

BEGIN

  SELECT r.last_id INTO upper FROM row_range r FOR UPDATE;

  BEGIN

    SELECT n

    INTO next_upper

    FROM (SELECT lead(id, row_count, default_upper) over(ORDER BY id) n

          FROM locks

          WHERE id >= upper)

    WHERE rownum = 1;

  EXCEPTION

    WHEN no_data_found THEN

      next_upper := default_upper;

   

  END;

  lower_bound := upper;

  upper_bound := next_upper;

  UPDATE row_range r SET r.first_id = lower_bound, r.last_id = upper_bound;

  COMMIT;

END;

Every process will take its bounds and process them as given procedure. There is an outer while loop that determines whether there is more unprocessed records left.

CREATE OR REPLACE PROCEDURE process_locks_table(

       new_status          IN VARCHAR2

      ,processed_row_count IN NUMBER) AS

  lower         NUMBER;

  upper         NUMBER;

  default_upper NUMBER := 999999999;

BEGIN

  get_bounds(lower, upper, processed_row_count);

  WHILE lower <> default_upper LOOP

   

    FOR rec_locks IN (SELECT id, status

                      FROM locks

                      WHERE status IS NULL

                            AND id >= lower

                            AND id < upper

                      ORDER BY id) LOOP

      UPDATE locks

      SET status = new_status || to_char(rec_locks.id)

      WHERE id = rec_locks.id;

      dbms_lock.sleep(3);

    END LOOP;

    COMMIT;

    get_bounds(lower, upper, processed_row_count);

 

  END LOOP;

END;

2.3.1.   Processing

On calling procedure, it must be defined howmany records will be processed in every Parallel process. In this demonstration, only three Parallel process is executed simultaneously. It can be increased in order to business needs.

In session 1

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table('A',1);

PL/SQL procedure successfully completed

SQL>

In session 2

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table('B',1);

PL/SQL procedure successfully completed

SQL>

In session 3

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec process_locks_table('C',1);

PL/SQL procedure successfully completed

SQL>

Analyze results are

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0

Connected as hr

SQL> exec analyze_locks;

Finished in 145 ms.

PL/SQL procedure successfully completed

The average execution time of this procedure after calling 3 times is 119 ms.

3.   Conclusion

To compare methods the table below can be formed.

Procedure	Time(ms)	Pros	Cons
Usual Way	340	No developing time	No Parallel process
Bucket Way	110	Parallel process	Buckets are determined first, it is not possible to change, when one process drops all records in that bucket will not process.
Bound Way	119	Parallel process, bounds are taking dynamically, you can execute an extra process while others are processing, when one process drops other processes will take place and process the records.

As shown in table above bucket method and bounds method are nearly the same in terms of average execution time. But in usual method because of no Parallel process is used, it took a long time(nearly 3 times other two methods, 3 is Parallel process count ). It can be reduced average time of executions with incrementing Parallel process counts.

In bucket method, first you must decide how many buckets will be created. After deciding it is not possible to add an extra process. But in bounds method, it is possible to add an extra process that can not effect others.

In bounds method, every process takes its record range. In bucket method every process will take its own bucket. So it will be solved deadlock problems in bucket and bounds methods.

If one process drops in bucket method, it has to be reexecuted in order to process that bucket records whereas other processes will take place of dropped process in bounds method.

Query to Get Last Version Of Table Data With Different Methods(KEEP, FIRST, DENSE_RANK, ROW_NUMBER)

A general programming practise to store old snapshot of data with versioning.
For instance currency table may be updated three or more times a day.
When application developers need currency info, first they have to get last version of curency rates for given day.
For future analysis, old currencies have to be stored, too.
So you have to write some queries to get last version of table data.
I demonstrate 3 methods to achieve situation:

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 15 15:14:28 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table currency(code varchar2(3), day date, version number, price number );
Table created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 1, 1.435);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 2, 1.437);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE, 3, 1.441);
1 row created.
SQL> INSERT INTO currency VALUES ('EUR', SYSDATE, 1, 1.800);
1 row created.
SQL> INSERT INTO currency VALUES ('EUR', SYSDATE, 2, 1.798);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE + 1, 1, 1.444);
1 row created.
SQL> INSERT INTO currency VALUES ('USD', SYSDATE + 1, 2, 1.443);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on
SQL> SELECT c.code, c2.DAY, c.price
2 FROM currency c
3 ,(SELECT code, trunc(DAY) DAY, MAX(version) version
4 FROM currency
5 GROUP BY code, trunc(DAY)) c2
6 WHERE c.code = c2.code
7 AND trunc(c.DAY) = c2.DAY
8 AND c.version = c2.version;
COD DAY PRICE
--- --------- ----------
USD 16-FEB-07 1.443
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441

Execution Plan
----------------------------------------------------------
Plan hash value: 3100612241
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 75 | 6 (34)| 00:00:01|
|* 1 | FILTER | | | | ||
| 2 | HASH GROUP BY | | 1 | 75 | 6 (34)| 00:00:01|
|* 3 | HASH JOIN | | 1 | 75 | 5 (20)| 00:00:01|
| 4 | TABLE ACCESS FULL| CURRENCY | 7 | 350 | 2 (0)| 00:00:01|
| 5 | TABLE ACCESS FULL| CURRENCY | 7 | 175 | 2 (0)| 00:00:01|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"."VERSION"=MAX("VERSION"))
3 - access("C"."CODE"="CODE" AND TRUNC(INTERNAL_FUNCTION("C"."DAY"))=
TRUNC(INTERNAL_FUNCTION("DAY")))
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
125 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> SELECT code
2 ,trunc(DAY) DAY
3 ,MIN(price) keep(dense_rank FIRST ORDER BY version DESC) price
4 FROM currency
5 GROUP BY code, trunc(DAY);
COD DAY PRICE
--- --------- ----------
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441
USD 16-FEB-07 1.443

Execution Plan
----------------------------------------------------------
Plan hash value: 3859059256
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 266 | 3 (34)| 00:00:01 |
| 1 | SORT GROUP BY | | 7 | 266 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CURRENCY | 7 | 266 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> SELECT code, DAY, price
2 FROM (SELECT code
3 ,trunc(DAY) DAY
4 ,price
5 ,row_number() over(PARTITION BY code, trunc(DAY) ORDER BY version DESC) rn
6 FROM currency)
7 WHERE rn = 1;
COD DAY PRICE
--- --------- ----------
EUR 15-FEB-07 1.798
USD 15-FEB-07 1.441
USD 16-FEB-07 1.443

Execution Plan
----------------------------------------------------------
Plan hash value: 2059110773
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 245 | 3 (34)| 00:00:01 |
|* 1 | VIEW | | 7 | 245 | 3 (34)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 7 | 266 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL | CURRENCY | 7 | 266 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY
"CODE",TRUNC(INTERNAL_FUNCTION("DAY")) ORDER BY INTERNAL_FUNCTION("VERSION")
DESC )<=1)
Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
592 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>