Oracle`ın Parallel Execution Yetenekleri ve

Oracle’ın Parallel Execution
Yetenekleri ve Performans
İncelemesi
Emrah METE
TURKCELL TEKNOLOJİ
Emrah METE
Marmara Üniversitesi
Bilgisayar Teknolojisi ve Programlama
Yıldız Teknik Üniversitesi
Bilgisayar Mühendisliği
Turkcell Teknoloji
Yazılım Geliştirme Uzmanı
emrahmete.wordpress.com
Facebook, twitter, Google+, Linkedin
TROUG
www.troug.org
İçerik
•
•
•
•
Parallel Execution Nedir?
Neden Parallel Execution Kullanırız?
Parallel Execution Ne Zaman Kullanmalıyız?
Oracle’ın Parallel Execution Yetenekleri
–
–
–
–
–
Parallel Query
Parallel DML
Parallel DDL
Procedural Parallelism
Oracle 11g New Features
• dbms_parallel_execute
• Auto DoP ve PSQ
Parallel Execution ?
Neden Parallel Execution
Kullanırız?
• Seri çalışan çok büyük görevleri
paraleleştirerek daha hızlı ve performanslı
sonuçlar almak için kullanırız.
HIZ + PERFORMANS !
Parallel Execution Ne Zaman
Kullanmalıyız?
1 – Paralel çalıştıracağımız görev büyük bir işlem
olmalı(Ör: 50 GB’lık bir datayı load etmek.)
(OLAP/OLTP)
2- Sistemimizin donanım karakteristiğinin parallel
execution yapmaya yeterli olması gerekir.
3- Parallel Execution yapmak için sistemimizde
yeterli available kaynakların olması gerekir.
Oracle’ın Parallel Execution
Yetenekleri
•
•
•
•
•
Parallel Query
Parallel DML (PDML)
Parallel DDL
Procedural Parallelism
Oracle 11g New Features
– Auto DoP ve PSQ (Parallel Statement Queue)
– DBMS_PARALLEL_EXECUTION
Parallel Query
• select count(status) from big_table
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
Parallel DML (PDML)
• Insert – Update – Delete – Merge
ORA-12838: cannot read/modify an object after modifying it in
parallel.
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
Parallel DDL
•
•
•
•
•
•
CREATE INDEX
CREATE TABLE AS SELECT
ALTER INDEX REBULD
ALTER TABLE MOVE
ALTER TABLE SPLIT|COALESCE PARTITION
ALTER INDEX SPLIT PARTITION
Parallel DDL
• Create Table big_table as Select ....
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
Procedural Parallelism
• Selecting Rows
• Processing
them
• Insert/ Update
another table
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
Procedural Parallelism
Parallel Pipelined Function
Unparallelized, Unpipelined Table Functions
Pipelining and Parallel Execution
Procedural Parallelism
•
Select * from table(dbms_xplan.display);
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
DBMS_PARALLEL_EXECUTE
(Do it yourself parallesim)
<<Expert, Oracle Database Architecture 11g,10g,9i>> Tom KYTE (2010)
DBMS_PARALLEL_EXECUTE
(Do it yourself parallesim)
Automatic
Degree of Parallelism
(Auto DoP)
PARALLEL_DEGREE_POLICY
PARALLEL_DEGREE_LIMIT
PARALLEL_MIN_TIME_THRESHOLD
DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT (DEFAULT)
https://blogs.oracle.com/datawarehousing/entry/auto_dop_and_parallel_statemen
Parallel Statement Queue (PSQ)
https://blogs.oracle.com/datawarehousing/entry/auto_dop_and_parallel_statemen
Kaynaklar
• «Expert, Oracle Database Architecture 11g,10g,9i» Tom
KYTE (2010)
• tahiti.oracle.com
• Oracle 11g Release 2 New Features Guide
• Oracle 11g Release 2 Concept Guide
• Oracle Datawarehouse Guide
• blogs.oracle.com
– The Datawarehouse Insider, Auto DOP and Parallel Statement
Queuing
• Oracle Technology Network
- On Working in Parallel By Steven Feuerstein
- Automating Parallelism By Arup Nanda
Sorular?/Yorumlar
Teşekkürler