Mengoptimumkan pertanyaan dalam ORACLE menggunakan pelan pelaksanaan. Memahami Keputusan Melaksanakan Explain Plan dalam Oracle SQL Developer

Biar saya segera menjelaskan bahawa saya akan menerangkan menggunakan contoh menggunakan utiliti percuma OraDeveloper Studio. kenapa? Kerana tidak mungkin untuk melakukan ini dengan pertanyaan biasa, dan tidak ada masa atau keinginan untuk memikirkannya, kerana terdapat cara yang lebih mudah. 😉

Jadi, untuk apa ini? Saya akan menerangkannya kepada anda contoh khusus, kerana itu saya terpaksa melakukan pengoptimuman.

Tugasnya adalah untuk memuatkan puluhan ribu baris data ke dalam pangkalan data. Untuk setiap baris, anda mesti terlebih dahulu mencari data tambahan dalam pangkalan data dengan satu pertanyaan yang agak rumit (4 jadual melalui gabungan).
Masalahnya ialah memuatkan 15 ribu baris mengambil masa 8-9 jam. Oleh kerana, mengikut syarat tugas, perlu memuatkannya dengan kerap, dan bukan sekali setiap lima tahun... Secara umum, kita perlu membawa masa ke tahap yang boleh diterima.

Apa yang telah saya lakukan?
1. Saya mendapati bahawa ia adalah pilihan yang semakin perlahan (data dimasukkan dan dikemas kini dalam jadual di mana terdapat sekumpulan baris dan beberapa jadual tidak mempunyai indeks mahupun kunci - oleh itu keraguan tentang kesalahan pilihan) .
2. Menyemak kehadiran indeks pada medan yang digunakan oleh permintaan. Menambah yang hilang.
3. Meminta pertolongan daripada mereka yang tahu. 🙂

Mereka yang tahu menasihatkan kami untuk menganalisis pelan pelaksanaan pertanyaan dan menerangkan cara melakukan ini dalam OraDev.
Buat tetingkap pertanyaan baharu (Ctrl+N). Kami menyalin permintaan kami ke dalamnya. Tekan Alt+G. Pilih yang sedia ada atau buat satu meja baru rancangan.
Selepas pelaksanaan, pokok rancangan pelaksanaan akan muncul. Ia tidak begitu mudah untuk memikirkannya sendiri dan tanpa setengah liter. 😉

Apa yang menarik minat kita tentang pokok ini? Kami berminat dengan nod (langkah) yang menunjukkan Kos langkah yang besar. Anda boleh melihat harga langkah dalam sifat langkah (saya mempunyai tetingkap sifat sentiasa terbuka dan oleh itu saya hanya perlu memilih langkah yang betul; anda juga mungkin perlu memilih sifat dengan mengklik kanan dalam langkah). Kami mencari langkah perlahan (kami tidak mengambil kira nod paling atas, akar pokok pelan - jumlah harga permintaan akan ditunjukkan di sana, dan kami sudah tahu bahawa masalahnya adalah dalam permintaan ini). Terjumpa? Sekarang kita melihat jadual mana, medan yang mana dan bilangan baris yang berfungsi dengan langkah - ini adalah dalam sifat dan nama langkah. Kita tengok dan fikir, kenapa lambat sangat kat sini?
Sebagai contoh, salah satu langkah berfungsi dengan 4000 rekod dan bukannya satu atau tiga rekod (bukan beribu-ribu). Ini tidak sepatutnya berlaku pada dasarnya - saya mengehadkan sampel dengan tepat untuk memilih daripada julat yang diperlukan, dan bukan daripada timbunan sampah yang tidak perlu. Setelah melihat dengan teliti keadaan bergabung, saya mendapati bahawa saya telah terlepas salah satu medan. Saya menambahkan medan pada permintaan itu dan semuanya telah ditetapkan. Harga permintaan (penuh) menurun daripada 531 kepada 6. :)

Terima kasih kepada rakan-rakan bersarang dan mengesan atas bantuan mereka.

P.S. Maaf kerana tidak menyertakan tangkapan skrin. Ia akan menjadi lebih jelas dengan mereka, tetapi... Disebabkan kerahsiaan beberapa maklumat, 80% perlu dikilap dan sekali lagi ia akan menjadi tidak jelas.
P.P.S. Masa pemuatan keseluruhan telah dikurangkan dengan ketara. Ia mengambil masa 12 minit untuk memuatkan 17.5 ribu baris data ke dalam pangkalan data. Berbanding 8-9 jam... Nah, anda sudah memahami semuanya sendiri. 😉

5 jawapan

EXPLAIN PLAN output ialah output nyahpepijat pengoptimum Pertanyaan Oracle. COST ialah hasil akhir pengoptimum kos (CBO) yang matlamatnya adalah untuk memilih mana satu daripada banyak rancangan yang mungkin harus digunakan untuk menjalankan pertanyaan. CBO mengira kos relatif untuk setiap pelan, kemudian memilih pelan dengan kos terendah.

(Nota: Dalam sesetengah kes, CBO tidak mempunyai masa yang mencukupi untuk menilai semua rancangan yang mungkin, dalam kes ini ia hanya memilih pelan dengan kos terendah ditemui setakat ini)

Secara umum, salah satu sumbangan terbesar kepada pertanyaan perlahan ialah bilangan baris yang dibaca untuk melayani pertanyaan (lebih tepat, blok), jadi kos akan berdasarkan sebahagiannya pada bilangan baris yang dianggarkan oleh pengoptimum perlu dibaca .

Sebagai contoh, katakan anda mempunyai pertanyaan berikut:

PILIH emp_id DARIPADA pekerja DIMANA bulan_perkhidmatan = 6;

(Lajur months_of_service mempunyai kekangan NOT NULL padanya dan indeks biasa padanya.)

Terdapat dua rancangan utama yang boleh dipilih oleh pengoptimum di sini:

Pelan 1: Baca semua baris daripada jadual "pekerja", untuk setiap semakan jika predikat adalah benar (months_of_service=6).
Pelan 2: Baca indeks di mana months_of_service=6 (ini menghasilkan set ROWID), kemudian akses jadual berdasarkan ROWID yang dikembalikan.

Mari kita andaikan bahawa jadual "pekerja" mengandungi 1,000,000 (1 juta) baris. Mari kita bayangkan lagi bahawa nilai untuk bulan_perkhidmatan berjulat dari 1 hingga 12 dan atas sebab tertentu diagihkan secara sama rata.

harga Pelan 1, yang termasuk SCAN PENUH, akan dikenakan kos untuk membaca semua baris dalam jadual pekerja, iaitu kira-kira 1,000,000; tetapi memandangkan Oracle selalunya boleh membaca blok menggunakan bacaan berbilang blok, kos sebenar akan lebih rendah (bergantung pada cara pangkalan data anda dikonfigurasikan) - contohnya, katakan bilangan sampel dengan berbilang blok ialah 10 - anggaran kos penuh imbasan ialah 1,000,000/10 ; Jumlah kos = 100,000.

harga Pelan 2, yang termasuk IMBAS Julat INDEX dan carian jadual oleh ROWID, akan menelan kos imbasan indeks serta kos mengakses jadual menggunakan ROWID. Saya tidak akan membincangkan kos imbasan indeks julat, tetapi mari bayangkan bahawa kos imbasan indeks julat ialah 1 setiap baris; kami menjangkakan untuk mencari padanan 1 daripada 12 kali, jadi kos mengimbas indeks ialah 1,000,000/12 = 83,333; ditambah kos akses jadual (andaikan 1 blok dibaca setiap akses, kami tidak boleh menggunakan bacaan berbilang blok di sini) = 83.333; Jumlah kos = 166,666.

Seperti yang anda lihat, kos pelan 1 (imbasan penuh) adalah kurang daripada kos pelan 2 (semakan indeks + akses rowid) - ini bermakna CBO akan memilih imbasan PENUH.

Jika andaian yang dibuat di sini oleh pengoptimum adalah betul, maka sebenarnya Pelan 1 akan menjadi lebih baik dan lebih cekap daripada Pelan 2 - yang menyangkal mitos bahawa imbasan PENUH adalah "sentiasa buruk".

Hasilnya akan agak berbeza jika sasaran pengoptimum ialah FIRST_ROWS(n) dan bukannya ALL_ROWS - dalam hal ini pengoptimum lebih suka rancangan 2 kerana ia selalunya akan mengembalikan beberapa baris pertama dengan lebih cepat, dengan kos menjadi kurang cekap untuk keseluruhan pertanyaan .

CBO membina pepohon keputusan dengan menganggarkan kos setiap laluan pelaksanaan yang mungkin tersedia untuk setiap permintaan. Kos ditetapkan oleh parameter CPU_cost atau I/O_cost yang ditetapkan pada contoh. Dan CBO menganggarkan kos sebaik mungkin dengan statistik sedia ada jadual dan indeks yang akan digunakan dalam pertanyaan. Anda tidak seharusnya menyesuaikan permintaan anda berdasarkan kos semata-mata. Kos membolehkan anda memahami MENGAPA pengoptimum melakukan perkara yang dilakukannya. Tanpa kos, anda boleh memahami sebab pengoptimum memilih rancangan yang dilakukannya. Lagi kos rendah tidak bermakna permintaan yang lebih pantas. Ada masanya ini benar dan ada masanya ia salah. Kos adalah berdasarkan jadual statistik anda dan jika ia salah kos akan menjadi tidak betul.

Apabila menyediakan pertanyaan, anda harus melihat pada kardinaliti dan bilangan baris setiap langkah. Adakah mereka masuk akal? Adakah pengoptimum dianggap betul? Adakah rentetan dikembalikan dengan betul? Jika maklumat tidak hadir dengan betul, kemungkinan besar pengoptimum tidak mempunyai maklumat yang betul yang diperlukan untuk membuat keputusan yang betul. Ini mungkin disebabkan oleh statistik lapuk atau tiada dalam jadual dan indeks, serta statistik cpu. Adalah lebih baik untuk mengemas kini statistik semasa mengkonfigurasi pertanyaan anda untuk memanfaatkan pengoptimuman sepenuhnya. Mengetahui litar anda juga banyak membantu dengan persediaan. Mengetahui apabila pengoptimum telah benar-benar memilih keputusan yang buruk dan menunjukkannya cara yang betul dengan sedikit petunjuk, anda boleh menjimatkan masa.

Sebutan "PENUH" anda menunjukkan kepada saya bahawa pertanyaan sedang melakukan imbasan skrin penuh untuk mencari data anda. Dalam sesetengah kes ini baik, jika tidak, ia adalah penunjuk kemasukan pengindeksan/pertanyaan yang buruk.

Sebagai peraturan umum, anda mahu pertanyaan anda menggunakan kekunci supaya Oracle boleh mencari data yang anda perlukan dengan mengakses bilangan baris yang paling sedikit. Akhirnya, anda suatu hari nanti akan dapat mengakses seni bina jadual anda. Jika kos kekal terlalu tinggi, anda mungkin perlu memikirkan cara untuk menyesuaikan reka bentuk litar anda untuk prestasi yang lebih baik.

Baru-baru ini Versi Oracle COST mewakili masa pengoptimum menunggu pertanyaan, dinyatakan dalam unit masa yang diperlukan untuk membaca satu blok.

Jadi, jika membaca satu blok mengambil masa 2ms dan kosnya dinyatakan sebagai "250", permintaan mungkin mengambil masa 500ms untuk diselesaikan.

Pengoptimum mengira kos berdasarkan anggaran bilangan bacaan blok tunggal dan berbilang blok, serta penggunaan CPU pelan. yang terakhir boleh menjadi sangat berguna untuk meminimumkan kos dengan melakukan operasi tertentu sebelum yang lain untuk cuba mengelakkan kos CPU yang tinggi.

Ini menimbulkan persoalan bagaimana pengoptimum mengetahui berapa lama operasi yang diambil untuk diselesaikan. versi terkini Oracle membolehkan anda membuat koleksi "statistik sistem", yang pastinya tidak boleh dikelirukan dengan statistik jadual atau indeks. Statistik sistem ialah ukuran prestasi perkakasan, terutamanya penting:

Berapa lama masa yang diambil untuk membaca satu blok.
Berapa lama masa membaca multitasking?
Seberapa besar bacaan berbilang blok (selalunya berbeza daripada maksimum yang mungkin disebabkan saiz jadual kurang daripada nilai maksimum dan sebab lain).
Prestasi CPU

Nombor-nombor ini boleh berbeza-beza bergantung pada persekitaran operasi sistem, dan statistik yang berbeza boleh disimpan untuk operasi "OLTP siang hari" dan operasi "pelaporan berkala setiap malam", dan untuk "pelaporan akhir bulan" jika anda mahu.

Memandangkan set statistik ini, rancangan ini Pelaksanaan pertanyaan boleh dianggarkan untuk kos dalam persekitaran operasi yang berbeza, yang mungkin menggalakkan penggunaan imbasan jadual penuh dalam beberapa kes atau imbasan indeks dalam yang lain.

Kosnya tidak sempurna, tetapi pengoptimum menjadi lebih baik dalam memantau diri dengan setiap keluaran dan boleh bertindak balas terhadap kos sebenar berbanding anggaran kos untuk menerima penyelesaian terbaik pada masa akan datang. ia juga menyukarkan ramalan.

Sila ambil perhatian bahawa kos tidak diperlukan masa dinding, memandangkan operasi pertanyaan selari menggunakan jumlah masa merentas berbilang urutan.

Dalam versi lama Oracle, kos operasi CPU diabaikan, dan kos relatif bacaan tunggal dan berbilang blok telah dibetulkan dengan berkesan mengikut parameter init.

Anda juga boleh menanyakan v $sql dan v $session untuk mendapatkan statistik tentang pernyataan SQL, dan ini akan mempunyai metrik terperinci untuk semua jenis sumber, pemasaan dan pelaksanaan.

Ia seperti paku di tapak kasut kegemaran anda. Anda boleh berjalan, tetapi semakin kerap anda mendapati diri anda ingin kekal di tempat atau mewakilkan tugas kepada orang lain. Kesulitan kecil bukan sahaja melambatkan kerja kita, tetapi juga mengurangkan motivasi, mengganggu proses dan mengurangkan kualiti hasil. Dan jika anda menemui rakan yang mengajar anda untuk mengambil tukul dan menukul paku itu, anda bukan sahaja akan berterima kasih kepadanya atas bantuannya, tetapi anda juga akan membantu orang lain sendiri, menyelamatkan mereka daripada halangan kecil tetapi sangat menjengkelkan. Inilah sebabnya mengapa anda perlu berkomunikasi, berkongsi bukan sahaja pengetahuan mendalam dan intim dalam forum dan di tapak seperti Habr, tetapi juga helah mudah anda dan "helah kecil"

Seperti mana-mana teks, pertanyaan dan atur cara dalam SQL boleh dibuat dalam mana-mana penyunting teks. Tetapi jika anda seorang profesional, anda banyak bekerja dan sering menggunakan SQL, maka penserlahan sintaks dan pemformatan semula kod automatik tidak lagi mencukupi untuk anda, terutamanya jika anda perlu bertukar antara versi berbeza DBMS yang sama atau platform yang berbeza DBMS.

Baru-baru ini saya kebetulan berkomunikasi dengan salah seorang profesional terkemuka DBMS Oracle. Dia memberitahu banyak perkara menarik tentang bekerja dengan rancangan pelaksanaan pertanyaan dalam versi berbeza DBMS ini dan tidak teragak-agak untuk memberitahu semua orang tentang alat dan teknik yang digunakannya dan memberikan beberapa petua kecil yang berguna. Saya menterjemah salah satu artikel di blognya dan ingin membawanya kepada perhatian Khabravchan. Walaupun fakta bahawa teknik yang diterangkan telah digunakan untuk bekerja dengan Oracle, saya kini berjaya menggunakan pendekatan yang sama untuk MS SQL dan Sybase.

Jalankan pertanyaan pelaksanaan dan tab Rancangan Pertanyaan akan muncul, diisi dengan pelan pelaksanaan.

Letakkan kursor tetikus pada mana-mana nod dalam rajah dan tambahan maklumat yang berguna, berkaitan dengan langkah pelaksanaan ini daripada pelan pertanyaan!
Secara lalai, Rapid SQL menunjukkan pelan pelaksanaan secara grafik. Saya keluar dari dunia pengoptimuman lama…. Saya lebih suka versi teks, jadi saya klik butang kanan tetikus dalam tetingkap dengan pelan dan pilih "Lihat sebagai Teks".
Saya lebih suka melihat teks permintaan dan rancangan pada masa yang sama.

Ia mudah dilakukan. Adakah anda melihat tab tetingkap ISQL di bahagian bawah tetingkap utama? Pertama, kita perlu mengkonfigurasi Rapid SQL supaya ia memaparkan pelan dalam tetingkap yang berasingan.

Klik butang Pilihan (bulatan merah kiri) dan kemudian tetapkan pilihan 'Tidak dilampirkan' untuk tetingkap Keputusan. Ini akan mencipta dua tab berasingan di bahagian bawah Rapid SQL sebaik sahaja pertanyaan dijalankan. Hanya seret tetingkap ini sedikit mengikut tab dan segi empat tepat akan muncul di mana anda boleh mengalihkan tetingkap ini.
Atau anda boleh menggunakan item Tile windows dari menu utama program

Dan satu lagi perkara: semua ini juga berfungsi dalam DBArtisan - penyelesaian untuk pentadbir pangkalan data.

Seperti mana-mana teks, pertanyaan dan program dalam SQL boleh dibuat dalam mana-mana editor teks. Tetapi jika anda seorang profesional, anda banyak bekerja dan sering menggunakan SQL, maka penyerlahan sintaks dan pemformatan semula kod automatik tidak lagi mencukupi untuk anda, terutamanya jika anda perlu bertukar antara versi berbeza DBMS yang sama atau platform DBMS yang berbeza.

Baru-baru ini saya kebetulan berkomunikasi dengan salah seorang profesional Oracle DBMS terkemuka. Dia memberitahu banyak perkara menarik tentang bekerja dengan rancangan pelaksanaan pertanyaan dalam pelbagai versi DBMS ini dan tidak teragak-agak untuk memberitahu semua orang tentang alatan dan teknik yang dia gunakan serta memberikan beberapa petua kecil yang berguna. Saya menterjemah salah satu artikel di blognya dan ingin membawanya kepada perhatian Khabravchan. Walaupun fakta bahawa teknik yang diterangkan telah digunakan untuk bekerja dengan Oracle, saya kini berjaya menggunakan pendekatan yang sama untuk MS SQL dan Sybase.

Jalankan pertanyaan pelaksanaan dan tab Rancangan Pertanyaan akan muncul, diisi dengan pelan pelaksanaan.

Tuding tetikus anda pada mana-mana nod dalam rajah dan maklumat berguna tambahan yang berkaitan dengan langkah pelaksanaan itu daripada pelan pertanyaan akan muncul!
Secara lalai, Rapid SQL menunjukkan pelan pelaksanaan secara grafik. Saya keluar dari dunia pengoptimuman lama…. Saya lebih suka versi teks, jadi saya klik kanan dalam tetingkap pelan dan pilih "Lihat sebagai Teks".
Saya lebih suka melihat teks permintaan dan rancangan pada masa yang sama.

Ia mudah dilakukan. Adakah anda melihat tab tetingkap ISQL di bahagian bawah tetingkap utama? Pertama, kita perlu mengkonfigurasi Rapid SQL supaya ia memaparkan pelan dalam tetingkap yang berasingan.

Klik butang Pilihan (bulatan merah kiri) dan kemudian tetapkan pilihan 'Tidak dilampirkan' untuk tetingkap Keputusan. Ini akan mencipta dua tab berasingan di bahagian bawah Rapid SQL sebaik sahaja pertanyaan dijalankan. Hanya seret tetingkap ini sedikit mengikut tab dan segi empat tepat akan muncul di mana anda boleh mengalihkan tetingkap ini.
Atau anda boleh menggunakan item Tile windows dari menu utama program

Dan satu lagi perkara: semua ini juga berfungsi dalam DBArtisan - penyelesaian untuk pentadbir pangkalan data.

Penalaan Prestasi 11g Pangkalan Data Oracle.

Cara membaca rancangan pertanyaan

Apakah rancangan pelaksanaan?

Pelan pelaksanaan ialah output pengoptimum yang ditafsirkan dalam bahasa enjin pelaksanaan. Ia mengarahkan enjin pelaksanaan tentang operasi yang mesti dilakukan untuk mendapatkan data yang diminta oleh ungkapan secepat dan seefisien mungkin.

Ekspresi EXPLAIN PLAN menangkap pelan pelaksanaan yang dipilih oleh pengoptimum untuk melaksanakan ungkapan seperti SELECT, UPDATE, DELETE dan INSERT. Langkah pelan pelaksanaan tidak dilaksanakan mengikut susunan yang dinyatakan dalam pelan. Terdapat hubungan ibu bapa-anak antara langkah. pokok rentetan sumber adalah asas pelan pelaksanaan. Ia mengandungi maklumat berikut:

Mengisih jadual yang dirujuk oleh penyata
Kaedah capaian untuk setiap jadual yang dinyatakan dalam penyata
Kaedah gabungan untuk jadual, digunakan oleh operator gabungan dalam ungkapan
Operasi data seperti penapis, isihan atau pengagregatan

Sebagai tambahan kepada pokok rentetan sumber (atau pokok aliran data dalam operasi selari) jadual pelan mengandungi data berikut:

Data pengoptimuman seperti kos dan kardinaliti setiap operasi
Data partition, seperti set partition yang diakses
Data konkurensi seperti kaedah pengedaran operasi bergabung

Keputusan EXPLAIN PLAN boleh membantu anda menentukan sama ada pengoptimum memilih pelan pelaksanaan tertentu, seperti menggunakan gelung bersarang.

Di manakah saya boleh mencari rancangan pelaksanaan?

Terdapat banyak cara untuk mendapatkan rancangan pelaksanaan untuk ungkapan dalam pangkalan data, yang paling popular disenaraikan di bawah:

Perintah EXPLAIN PLAN membolehkan anda melihat pelan pelaksanaan yang boleh digunakan oleh pengoptimum untuk melaksanakan ungkapan. Perintah ini sangat berguna kerana ia membina pelan pelaksanaan dan menulisnya ke dalam jadual yang dipanggil PLAN_TABLE tanpa menyimpan pernyataan SQL.
V$SQL_PLAN menyediakan keupayaan untuk melihat rancangan pelaksanaan untuk kursor yang telah dilaksanakan baru-baru ini. Maklumat yang disimpan dalam V$SQL_LAN sangat serupa dengan maklumat yang dihasilkan oleh arahan EXPLAIN PLAN. Walau bagaimanapun, Explain Plan menunjukkan pelan pelaksanaan yang berpotensi, dan V$SQL_PLAN menyimpan rancangan pertanyaan yang telah dilaksanakan.
V$SQL_PLAN_MONITOR mengandungi statistik pemantauan peringkat pelan untuk setiap pernyataan SQL yang terdapat dalam V$SQL_MONITOR. Setiap baris yang terkandung dalam V$SQL_PLAN_MONITOR sepadan dengan operasi pelan pelaksanaan tertentu.
Rangka kerja AWR dan rancangan pelaksanaan kedai Statspack untuk SQL yang paling kerap dipanggil. Pelan diletakkan dalam paparan dBA_HIST_SQL_PLAN atau STATS$SQL_PLAN.
Pelan pelaksanaan dan sumber baris juga ditulis untuk mengesan fail yang dijana oleh DBMS_MONITOR.
Pangkalan Pengurusan SQL ialah sebahagian daripada kamus data yang disimpan dalam ruang jadual SYSAUX. Maklumat log tentang operasi, sejarah rancangan pelaksanaan dan talian rujukan disimpan di sini serta profil untuk pernyataan SQL.
Peristiwa diagnostik 10053, yang digunakan untuk merekodkan pengiraan pengoptimuman kos, juga boleh menjana rancangan pelaksanaan pertanyaan.
Bermula dengan versi 10.2, apabila anda membuang keadaan proses, pelan pelaksanaan juga disertakan dalam fail surih yang dijana.

Melihat rancangan pelaksanaan

Jika anda menjalankan perintah EXPLAIN PLAN dalam SQL*Plus, anda kemudian boleh memilih data daripada jadual PLAN_TABLE dan melihat rancangan pelaksanaan yang dihasilkan. Paling dengan cara yang mudah lihat rancangan pelaksanaan adalah menggunakan pakej DBMS_XPLAIN. Pakej DBMS_XPLAIN mengandungi lima fungsi yang tersedia:

PAPARAN: Digunakan untuk output berformat rancangan pelaksanaan.
DISPLAY_AWR: Digunakan untuk output pelan berformat pelaksanaan SQL ungkapan yang disimpan dalam repositori AWR.
DISPLAY_CURSOR: Digunakan untuk memformat output pelan pelaksanaan daripada mana-mana kursor yang dimuatkan
DISPLAY_SQL_PLAN_BASELINE: Digunakan untuk output berformat satu atau lebih pelan pelaksanaan untuk pernyataan SQL yang dikenal pasti oleh pengepala.
DISPLAY_SQLSET: Digunakan untuk output berformat pelan pelaksanaan yang disimpan dalam set Penalaan SQL.

Faedah menggunakan pakej DBMS_XPLAIN ialah ia membolehkan anda melihat pelan pelaksanaan terformat bagi pernyataan SQL, tanpa mengira sumbernya.

EXPLAIN PLAN arahan

Perintah EXPLAIN PLAN digunakan untuk menjana pelan pelaksanaan pertanyaan.
Setelah pelan telah dijana, ia boleh dilihat dengan menanyakan maklumat daripada jadual PLAN_TABLE

JADUAL PELAN dicipta secara automatik sebagai jadual sementara global, yang kemudiannya digunakan oleh semua pengguna untuk menyimpan rancangan pelaksanaan. Anda boleh membuat JADUAL PELAN anda sendiri menggunakan skrip $ORACLE_HOME/rdbms/admin/utlxplan.sql jika penyimpanan jangka panjang pelan pelaksanaan diperlukan.

JELASKAN PELAN Struktur Perintah

Perintah EXPLAIN PLAN memasukkan satu baris ke dalam JADUAL PELAN untuk setiap langkah pelan pelaksanaan.

EXPLAIN PLAN contoh arahan

Perintah ini memasukkan pelan pelaksanaan untuk ungkapan ke dalam JADUAL PELAN dan menambah teg demo01 untuk rujukan kemudian.

Terdapat banyak cara untuk mendapatkan pelan pelaksanaan. Kaedah di atas menggunakan perintah EXPLAIN PLAN. Perintah ini menjana pelan pelaksanaan untuk pernyataan SQL tanpa melaksanakannya, dan meletakkan hasilnya dalam JADUAL PELAN. JADUAL PELAN mewakili struktur pokok yang dengannya anda boleh mengembalikan pelan pelaksanaan untuk ungkapan menggunakan lajur ID dan PARENT_ID dan klausa CONNECT BY dalam PILIH ungkapan.

Mengeluarkan kandungan JADUAL PELAN

Contoh di atas menggunakan kekunci SEMUA untuk fungsi DBMS_XPLAIN.DISPLAY, yang membolehkan anda melihat semua maklumat yang ada tentang pelan pelaksanaan, disimpan dalam JADUAL PELAN. kesimpulan ini sebagai tambahan kepada maklumat standard, mengandungi Maklumat tambahan seperti maklumat PROJECTION, ALIAS dan REMOTE SQL jika operasi diedarkan.

Untuk kawalan maklumat output yang lebih tepat, perkara berikut boleh digunakan: parameter utama. setiap satu kata kunci menambah blok berasingan pada output maklumat daripada JADUAL PELAN. Kata kunci mesti dipisahkan dengan koma atau ruang:

ROWS, jika sesuai, menunjukkan bilangan baris yang dijangka akan dikira oleh pengoptimum.
ROWS, jika sesuai, menunjukkan bilangan bait yang dijangka akan dikira oleh pengoptimum.
KOS jika sesuai, tunjukkan kos,mungkin dikira oleh pengoptimum
PARTITION jika sesuai, tunjukkan patrician membuang oleh pengoptimum
SELARI atau adakah ia sesuai, menunjukkan maklumat PX (kaedahpengedaran maklumat dan maklumat tentang baris gilir capaian jadual)
PREDIKATatau adakah ia sesuai, menunjukkan maklumat tentang predikat
PROJECTION atau adakah ia sesuai, tunjukkan bahagianunjuran

Menggunakan Explain Plan dalam SQL Developer

AUTOTRACE

Apabila ungkapandilakukan dalam SQL*Plus atau SQL Developer, anda boleh mendapatkan pelan pelaksanaan dan statistik pelaksanaan ungkapan secara automatik. Laporan dijana secara automatik selepas melakukan sebarang jenis operasi seperti SELECT, INSERT, UPDATE dan DELETE. Maklumat ini boleh digunakan untuk mendiagnosis dan menala prestasi pernyataan SQL.

Untuk menggunakan AUTOTRACE, JADUAL PELAN mesti dibuat dalam pangkalan data dan pengguna yang melaksanakan AUTOTRACE mesti diberikan peranan PLUSTRACE. PerananPLUSTRACE dicipta dan dikeluarkan kepada peranan DBA menggunakan skrip$ORACLE_HOME/sqlplus/admin/plustrce.sql

Sintaks AUTOTRACE

Anda boleh melakukan Autotrace menggunakan sintaks yang ditunjukkan dalam rajah di atas. Pilihan berikut juga tersedia untuk digunakan:

OFF Menyahdayakan penggunaan pengesanan
HIDUP Mendayakan penggunaan penghalaan automatik
TRACE Mendayakan pengesanan automatik dan menyekat output SQL
EXPLAIN Menunjukkan pelan pelaksanaan tetapi tidak menunjukkan statistik
STATISTIK Menunjukkan statistik tanpa pelan pelaksanaan

Contoh penggunaan AUTOTRACE

AUTOTRACE: STATISTIK

Statistik yang direkodkan oleh pelayan semasa melaksanakan ungkapan mencerminkan kelantangan sumber sistem, dibelanjakan untuk melaksanakan ungkapan dan termasuk penunjuk utama berikut:

panggilan rekursif- kuantiti panggilan rekursif, dijana pada bahagian klien dan pelayan. Pangkalan Data Oracle menyokong jadual yang digunakan untuk pemprosesan dalaman. Apabila Pangkalan Data Oracle perlu membuat perubahan pada jadual ini, ia menjana pengendali dalaman SQL, yang seterusnya menghasilkan panggilan rekursif.
blok db mendapat- bilangan kali blok SEMASA diminta
konsisten dapat- bilangan kali operasi bacaan integral bagi blok data diminta.
bacaan fizikal- bilangan blok data dibaca dari cakera. Nombor ini mewakili jumlah nilai bacaan fizikal terus dan semua bacaan daripada penimbal cache.
buat semula saiz- jumlah bilangan yang dijana semula dalam blok
bait dihantar melalui SQL*Net kepada pelanggan- jumlah bilangan bait yang dipindahkan kepada klien daripada proses latar belakang.
bait yang diterima melalui SQL*Net daripada klien- jumlah bilangan bait yang diterima daripada pelanggan Oracle*Net
SQL*Net pergi balik ke/dari klien- Jumlah bilangan mesej Oracle NET yang dihantar dan diterima daripada pelanggan.
susun (ingatan)- bilangan operasi pengisihan yang berjaya diselesaikan dalam ingatan dan tidak memerlukan penulisan ke cakera.
susun (cakera)- bilangan operasi pengisihan yang memerlukan sekurang-kurangnya satu operasi cakera.
baris diproses- bilangan baris yang diproses semasa operasi.

db_block_gets menunjukkan operasi baca blok semasa daripada pangkalan data. konsisten dapat- ini adalah operasi membaca blok yang mesti memenuhi nombor SCN tertentu. bacaan fizikal menunjukkan blok bacaan daripada cakera. db_block_gets Dan konsisten dapat- penunjuk statistik yang sentiasa dipantau. Mereka sepatutnya rendah berbanding dengan bilangan baris yang diambil. Pengisihan dilakukan dalam ingatan, bukan pada cakera.

AUTO TRACE menggunakan SQL*Developer

Lihat V$SQL_PLAN

Paparan ini menunjukkan rancangan pelaksanaan untuk kursor yang masih dalam cache perpustakaan. Maklumat yang disimpan dalam paparan ini dalam banyak cara serupa dengan maklumat daripada JADUAL PELAN. Walau bagaimanapun, V$SQL_PLAN mengandungi rancangan pelaksanaan untuk ungkapan yang telah dilaksanakan. Pelan pelaksanaan yang dihasilkan oleh EXPLAIN PLAN mungkin berbeza daripada pelan pelaksanaan sebenar yang disimpan dalam kursor. Ini berlaku kerana parameter sesi dan nilai pembolehubah BIND mungkin berbeza daripada yang semasa.

Satu lagi pandangan berguna: V$SQL_PLAN_STATISTICS yang menyediakan statistik pelaksanaan untuk setiap operasi dalam pelan pelaksanaan setiap kursor cache. Satu lagi idea yang berguna V$SQL_PLAN_STATISTIC_ALL menggabungkan maklumat pelaksanaan daripada V$SQL_PLAN_STATISTICS Dan V$SQL_WORKAREA dengan rancangan pelaksanaan yang disimpan V$SQL_PLAN.

Perihalan lajur utama paparan V$SQL_PLAN

Paparan mengandungi lajur yang sama seperti JADUAL PELAN dan beberapa lajur tambahan. Lajur hadir dalam JADUAL PELAN dan mempunyai nilai yang sama:

ALAMAT
HASH_VALUE

PLAN_HASH_VALUE ialah lajur yang mengandungi perwakilan berangka rancangan ungkapan SQL untuk kursor. Dengan membandingkan nilai lajur PLAN_HASH_VALUE, anda boleh menentukan dengan cepat sama ada rancangan pelaksanaan untuk ungkapan yang sama berubah tanpa membandingkannya baris demi baris.

Lihat V$SQL_PLAN_STATISTICS

Paparan V$SQL_PLAN_STATISTICS menyediakan statistik semasa dengan pelaksanaan untuk setiap operasi dalam pelan pelaksanaan, seperti bilangan baris yang diproses atau masa pelaksanaan. Semua statistik, kecuali bilangan baris, terkumpul. Sebagai contoh, statistik gabungan jadual mungkin termasuk 3 operasi gabungan jadual. Statistik yang disimpan dalam V$SQL_PLAN_STATISTICS tersedia untuk kursor yang telah disusun dengan parameter permulaan STATISTICS_LEVEL = ALL atau menggunakan petunjuk pengoptimum GATHER_PLAN_STATISTICS.

Paparan V$SQL_STATISTICS_ALL mengandungi statistik penggunaan memori untuk semua baris sumber yang menggunakan memori SQL (isih atau gabungan HASH). Paparan ini menggabungkan maklumat yang disimpan dalam paparan V$SQL_PLAN dengan statistik pelaksanaan daripada pandangan V$SQL_PLAN_STATISTICS dan V$SQL_WORKAREA .

Hubungan antara pandangan prestasi dinamik yang penting

V$SQLAREA menunjukkan statistik untuk kawasan SQL yang dikongsi dan mengandungi satu baris untuk setiap satu rentetan SQL ungkapan. Pandangan ini menyediakan statistik pada pernyataan SQL yang telah dihuraikan, dalam ingatan, dan sedia untuk dilaksanakan:

SQL_ID- Pengecam SQL kursor induk dalam cache perpustakaan
VERSION_COUNT bilangan kursor anak yang terdapat dalam cache untuk kursor induk tertentu

V$SQL menyimpan statistik pada kawasan SQL yang dikongsi dan mengandungi satu baris untuk setiap ungkapan SQL turunan yang diperoleh daripada ungkapan SQL induk:

ALAMAT mewakili alamat pengepala kursor induk untuk kursor yang diberikan
HASH_VALUE-nilai ungkapan induk dalam cache perpustakaan
SQL_ID- Pengecam SQL kursor induk dalam cache perpustakaan
PLAN_HASH_VALUE- angka paparan SQL merancang untuk kursor ini
CHILD_NUMBER- nombor kursor kanak-kanak

Statistik yang disimpan dalam V$SQL dikemas kini apabila penyata SQL dilaksanakan. Walau bagaimanapun, untuk ungkapan jangka panjang, maklumat dalam paparan dikemas kini setiap lima saat. Ini memungkinkan untuk menilai kesan prestasi pertanyaan jangka panjang semasa ia dijalankan.

V$SQL_PLAN mengandungi maklumat pelan pelaksanaan untuk setiap kursor kanak-kanak yang dimuatkan ke dalam cache perpustakaan. Lajur ALAMAT, HASH_VALUE Dan CHILD_NUMBER boleh digunakan untuk berhubung dengan V$SQL untuk takrifan kursor kanak-kanak kemudian.

V$SQL_PLAN_STATISTIK menyediakan statistik pelaksanaan peringkat baris sumber untuk setiap kursor anak.Lajur ALAMAT, HASH_VALUE boleh digunakan untuk menggabungkan dengan pandangan V$SQLAREA untuk menentukan ibu bapakursor. Lajur ALAMAT, HASH_VALUE Dan CHILD_NUMBERboleh digunakan untuk berhubung denganV$SQLuntuk menentukan kursor kanak-kanak.

V$SQL_PLAN_STATISTIK_SEMUA mengandungi statistik penggunaan memori untuk semua baris sumber yang menggunakan memori SQL (isih atau gabungan HASH) Paparan ini mengagregatkan maklumat yang disimpan dalam paparan V$SQL_PLAN dengan statistik pelaksanaan daripada pandangan V$SQL_PLAN_STATISTICS Dan V$SQL_WORKAREA.

V$SQL_WORKAREAmenyediakan statistik mengenai kawasan kerja yang terlibat dalam proses tersebut kerja SQL ungkapan. Setiap pernyataan SQL yang disimpan dalam kumpulan kongsi mempunyai satu atau lebih kursor anak, maklumat mengenainya disimpan di dalamnya V$SQL. V$SQL_WORKAREA mengandungi maklumat tentang semua ruang kerja yang diperlukan oleh kursor kanak-kanak ini.

V$SQL_WORKAREAboleh berhubung dengan V$SQLAREA (ALAMAT, HASH_VALUE) dan dengan V$SQL ( ALAMAT, HASH_VALUE,CHILD_NUMBER).

Menggunakan perwakilan ini, anda boleh mendapatkan jawapan kepada soalan berikut:

10 kawasan kerja teratas yang paling memerlukankuantitimemori untuk cache
Untuk ruang kerja yang berjalan dalam mod AUTO, berapa peratus ruang kerja yang digunakan kuantiti maksimum ingatan?

V$SQLSTATS memaparkan statistik prestasi asas untuk kursor SQL, dengan setiap baris mewakili data menggabungkan teks ungkapan SQL dan pelan pelaksanaan SQL (gabungan SQL_ID Dan PLAN_HASH_VALUE). Lajur dalam V$SQLSTATS sama, V$SQL Dan V$SQLAREA. Walau bagaimanapun, pembentangan V$SQLSTATS berbeza daripada V$SQL Dan V$SQLAREA kelajuan pemprosesan, kebolehskalaan, tempoh penyimpanan data yang panjang (data statistik boleh disimpan dalam paparan, walaupun selepas kursor telah dialih keluar dari kumpulan kongsi).

Contoh pertanyaan data daripada paparan V$SQL_PLAN

Anda boleh menanyakan data daripada paparan V$SQL_PLAN menggunakan fungsi tersebut DBMS_XPLAIN.DISPLAY_CURSOR() untuk memaparkan ungkapan semasa atau terakhir dilaksanakan (seperti yang ditunjukkan dalam contoh). Anda boleh melepasi nilai SQL_ID sebagai parameter untuk mendapatkan pelan pelaksanaan untuk ungkapan yang diberikan. SQL_ID - SQL_ID ungkapan yang disimpan dalam cache kursor. Anda boleh mendapatkan nilai yang sepadan dengan menanyakan maklumat lajur SQL_ID V V$SQL Dan V$SQLAREA. Sebagai alternatif, anda boleh memilih PREV_SQL_ID untuk sesi tertentu daripada V$SESSION. Secara lalai, pilihan ini tidak ditentukan, dalam hal ini pelan yang disimpan dalam kursor yang dilaksanakan terakhir dipaparkan.

IOSTATS: Dengan mengandaikan pelaksanaan SQL mengumpul statistik asas untuk rancangan pelaksanaan, STATISTICS_LEVEL ditetapkan kepada SEMUA, atau HINT GATHER_PLAN_STATISTICS digunakan), format ini memaparkan statistik I/O untuk semua, jika SEMUA, ditentukan (atau hanya yang terakhir, jika LAST ditentukan) pelaksanaan kursor.
MEMSTATS: Andaikan digunakan kawalan automatik PGA (parameter pga_aggregate_target ditetapkan kepada nilai bukan sifar) format ini membolehkan anda menunjukkan statistik penggunaan memori jenis ini statistik hanya terpakai untuk operasi intensif memori, seperti HASH Join, sorting, atau beberapa operator bitmap.
SEMUA STATISTIK: Sinonim untuk "IOSTATS MEMSTATS"
TERAKHIR: Secara lalai, statistik pelan pelaksanaan ditunjukkan untuk semua pelaksanaan kursor. Menggunakan kata kunci LAST, anda boleh melihat statistik rancangan yang dijana sejak kali terakhir ia dilaksanakan.

Pandangan AWR Penting

Anda boleh melihat data AWR dalam Pengurus Perusahaan atau dengan menjana laporan AWR, dan anda juga boleh mengakses paparan prestasi dinamik berikut yang menyimpan data AWR:

V$ACTIVE_SESSION_HISTORY- paparan ini menunjukkan maklumat tentang aktiviti sesi terkini, dikemas kini setiap saat.
Paparan metrik V$ membentangkan data metrik untuk menjejak prestasi sistem. Senarai paparan metrik boleh dilihat dengan mengakses paparan V$METRICGROUP.
Pandangan DBA_HIST mengandungi data sejarah yang disimpan dalam pangkalan data. Kumpulan pandangan ini termasuk:

DBA_HIST_ACTIVE_SESS_HISTORY mengandungi kandungan sejarah sesi aktif yang dipilih daripada memori berdasarkan aktiviti sistem terkini
DBA_HIST_BASELINE mengandungi maklumat tentang talian rujukan yang disimpan dalam pangkalan data.
DBA_HIST_DATABASE_INSTANCE mengandungi maklumat tentang persekitaran pangkalan data
DBA_HIST_SNAPSHOT mengandungi maklumat tentang syot kilat yang disimpan dalam sistem
DBA_HIST_SQL_PLAN mengandungi maklumat tentang rancangan pelaksanaan
DBA_HIST_WR_CONTROL mengandungi maklumat tentang tetapan AWR

Meminta data daripada AWR

Menjana laporan untuk sesuatu yang khususSQLdaripada repositori AWR

Pemantauan SQL

alat Pemantauan SQL tersedia secara lalai apabila pilihan STATISTICS_LEVEL ditetapkan kepada nilai TYPICAL atau SEMUA. Untuk kegunaan instrumen ini anda juga mesti menetapkan nilai parameter CONTROL_MANAGEMENT_PACK_ACCESS dalam makna DIAGNOSTIK+PENALAAN untuk mendayakan pakej diagnostik dan konfigurasi.

Secara lalai, pemantauan SQL didayakan secara automatik apabila pernyataan SQL dilaksanakan secara selari atau apabila ia menggunakan lebih daripada lima saat masa CPU atau I/O semasa satu pelaksanaan.

Terdapat dua petunjuk pengoptimum untuk mendayakan atau melumpuhkan pemantauan SQL secara eksplisit untuk ungkapan - MONITOR Dan NO_MONITOR.

Anda boleh menjejaki statistik pelaksanaan pernyataan SQL menggunakan paparan V$SQL_MONITOR Dan V$SQL_PLAN_MONITOR.

Selepas mengaktifkan Pemantauan Pernyataan SQL dalam Paparan Prestasi Dinamik V$SQL_MONITOR Menambah maklumat yang diperlukan untuk menjejaki metrik prestasi utama seperti masa pelaksanaan, masa CPU, bilangan baca dan tulis, kependaman I/O dan metrik kependaman lain. Statistik ini dikemas kini dalam masa nyata semasa pelaksanaan SQL, secara lalai - setiap saat. Selepas pelaksanaan selesai, maklumat tentang pelaksanaan disimpan dalam paparan V$SQL_MONITOR satu minit lagi, selepas itu ia dikeluarkan.

Contoh laporan Pemantauan SQL

DALAM dalam contoh ini Ia menganggap bahawa anda memilih data daripada jadual JUALAN dan menjalankan Pemantauan SQL dalam sesi lain.

Fungsi DBMS_SQLTUNE.REPORT_SQL_MONITOR boleh menerima beberapa parameter yang mengehadkan atau mengembangkan tahap perincian laporan, menggunakan parameter tambahan anda juga boleh menentukan format output laporan (TEXT, HTML atau XML), secara lalai laporan dijana dalam teks format.

Untuk mengenal pasti secara unik dua pelaksanaan pernyataan SQL yang sama, kunci komposit yang dipanggil kunci pelaksanaan dijana. kunci ini terdiri daripada tiga atribut, setiap satunya merujuk kepada lajur dalam V$SQL_MONITOR:

SQL_ID
Pengecam yang dijana secara dalaman untuk memastikan bahawa yang diberikan kunci utama sebenarnya unik (SQL_EXEC_ID)
Cap masa mula pelaksanaan ungkapan (SQL_EXEC_START)

Tafsiran Rancangan Pelaksanaan

Kesimpulan JELASKAN RANCANGAN ialah perwakilan jadual bagi struktur pokok pelan pelaksanaan. Setiap langkah (baris dalam pelan pelaksanaan atau nod dalam pokok) mewakili Sumber Baris.
Susunan nod di bawah induk menunjukkan susunan pelaksanaan nod pada tahap itu. Jika dua langkah berada pada tahap yang sama, yang pertama dalam susunan akan dilaksanakan terlebih dahulu.
Dalam format pokok, daun di sebelah kiri pada setiap peringkat pokok mengenal pasti titik di mana pelaksanaan bermula.
Langkah-langkah pelan pelaksanaan tidak dilaksanakan mengikut urutan di mana ia dinomborkan. Terdapat hubungan ibu bapa-anak antara langkah.
Dalam PLAN_TABLE dan V$SQL_PLAN elemen penting untuk mendapatkan struktur pokok ialah lajur ID, PARRENT_ID dan POSITION. Dalam fail surih, lajur ini sepadan dengan medan id, pid dan pos, masing-masing.
Satu cara untuk membaca pelan pelaksanaan ialah menukarnya kepada graf yang mempunyai struktur pokok. Anda boleh mulakan di bahagian atas, entri dengan ID=1 ialah titik atas pokok. Ini benar untuk operasi yang mempunyai nilai parrent_id atau pid 1.
Untuk mewakili pelan sebagai pokok, lakukan perkara berikut:

Ambil ID dengan nilai terendah dan letakkan di bahagian atas pokok.
Kenal pasti baris yang mempunyai PID (id induk) bersamaan dengan nilai ini.
Letakkannya di dalam pokok di bawah rekod ibu bapa mengikut nilai POS mereka dari terkecil ke terbesar dari kiri ke kanan.
Setelah semua ID induk ditemui, turunkan satu tahap ke ID seterusnya dan ulangi proses itu, mencari baris baharu dengan PID yang sama.

Perkara pertama yang perlu ditentukan dalam pelan pelaksanaan ialah nod mana yang dilaksanakan dahulu. Kaedah yang ditunjukkan dalam rajah menerangkan cara melakukan ini, tetapi kadang-kadang dalam rancangan pelaksanaan yang kompleks sukar untuk melakukan ini dan juga sukar untuk mengikuti semua langkah hingga akhir. Pelan kompleks tidak berbeza dalam komposisi daripada yang mudah kecuali bilangan langkah. Perkara yang sama berlaku kepada mereka peraturan mudah. Anda sentiasa boleh menyembunyikan langkah dalam pelan anda yang tidak menggunakan sejumlah besar sumber.
Kaedah standard untuk mentafsir pelan pelaksanaan:

Mulakan di bahagian atas
Bergerak ke bawah sehingga anda mencapai operasi yang menghasilkan data tetapi tidak menggunakan apa-apa. Ini adalah permulaan operasi.
Lihatlah operasi anak yang dilakukan oleh ibu bapa ini. Operasi kanak-kanak akan dilakukan seperti berikut
Naikkan pokok sehingga semua urus niaga telah disemak.

Kaedah standard untuk mentafsir pepohon rancangan pelaksanaan ialah:

Mulakan di bahagian atas
Bergerak ke bawah dan ke kiri di sepanjang pokok sehingga anda mencapai nod kiri, ia dilaksanakan terlebih dahulu
Lihatlah anak-anak nod ini. kanak-kanak ini akan dihukum bunuh lagi.
Selepas anak-anak dilaksanakan, operasi induk akan terus dilaksanakan.
Sekarang, selepas operasi ini dan semua keturunannya selesai, naikkan pokok itu dan lihat keturunan siri operasi asal dan ibu bapanya. Ia dijalankan mengikut prinsip yang sama.
Navigasi ke atas pokok sehingga semua transaksi telah disemak

Tafsiran Rancangan Pelaksanaan: Contoh 1

Rajah di atas menunjukkan tafsiran rancangan pelaksanaan bagi ungkapan tersebut. Pertanyaan yang ditunjukkan dalam rajah cuba mencari pekerja yang gajinya berbeza daripada grid gaji. Pertanyaan memilih data daripada dua jadual dan termasuk subkueri berdasarkan pemilihan daripada jadual lain untuk menyemak tahap gaji.
Mari kita lihat susunan pelaksanaan untuk permintaan ini. Berdasarkan ini dan angka sebelumnya, perintah pelaksanaan adalah seperti berikut: 3-5-4-2-6-1:

3: pelaksanaan pelan akan bermula dengan imbasan penuh jadual EMP (ID=3)
5: baris dihantar ke langkah mengawal gelung bersarang (ID=2), yang menggunakannya untuk mencari baris dalam indeks PK_DEPT (ID=5)
4: ROWID bagi baris yang diperoleh selepas mengimbas PK_DEPT digunakan untuk mendapatkan maklumat selebihnya daripada jadual DEPT (ID=4)
2: ID=2, proses penggabungan gelung bersarang akan diteruskan sehingga ia selesai
6: Selepas ID=2 telah memproses semua baris sumber untuk gabungan, imbasan penuh jadual SALGRADE (ID=6) akan dilakukan.
1: Data yang diterima selepas melaksanakan ID=6 akan digunakan untuk menapis baris daripada ID=2 dan ID=6

Proses anak dilaksanakan sebelum proses induk, walaupun struktur sambungan mesti dibentuk sebelum proses anak dilaksanakan. Mungkin cara paling mudah untuk menerangkan perintah pelaksanaan ialah untuk melaksanakan operasi gabungan NESTED LOOPS dengan ID=2, kedua-dua kanak-kanak (ID=3 dan ID=4 (bersama anak-anak mereka)) mesti melengkapkan pelaksanaan mereka sebelum ID=2 adalah dilaksanakan.

Tafsiran Rancangan Pelaksanaan: Contoh 2

Pertanyaan ini mengembalikan nama, nama jabatan dan alamat pekerja yang jabatannya terletak di Seattle dan yang mempunyai pengurus.

Untuk memudahkan pemformatan, penomboran lain telah ditambahkan pada angka tersebut. Lajur di sebelah kiri mewakili ID dan lajur di sebelah kanan mewakili PID. Pelan pelaksanaan pertanyaan menunjukkan dua operasi NESTED LOOP JOIN. Kami mentafsir pelan pelaksanaan mengikut kaedah yang dibentangkan di atas:

Mari kita mulakan dari atas. ID=0
Kami menurunkan operasi sehingga kami mencapai operasi yang menghasilkan data tetapi tidak menggunakan apa-apa. DALAM dalam kes ini,ID 0,1,2, dan 3 sedang menggunakan data. ID=4 ialah operasi pertama dari atas yang tidak menggunakan sumber, tetapi menghasilkan data. Ini adalah sumber data pertama. IMBAS Julat INDEX akan mengembalikan ROWID baris yang akan digunakan untuk mengembalikan data daripada jadual LOCATIONS (ID=3)
Mari kita lihat adik-beradik sumber baris ini yang sama tarafnya dengan pokok. Adik beradik pada tahap yang sama mempunyai ID=3 dan ID=5. ID = 5 mempunyai anak - ID = 6, yang akan dilaksanakan sebelum itu. Ini ialah operasi IMBAS Julat INDEX pada indeks lain, mengembalikan ROWID yang kemudiannya akan digunakan untuk mendapatkan semula data daripada jadual DEPARTMENTS semasa ID pelaksanaan=5.
Selepas operasi kanak-kanak dilaksanakan, kawalan dipindahkan kepada moyangnya. Operasi seterusnya ialah NESTED LOOPS dengan ID=2 untuk menggabungkan data yang diterima sebelum ini.
Sekarang operasi induk dan semua keturunannya telah selesai, kami memanjat pokok dan melihat sama ada operasi induk itu mempunyai adik beradik yang setaraf dengannya. ID=2 berada pada tahap yang sama dengan operasi ID=7 yang mempunyai ID anak=8. Kanak-kanak ini akan dihukum bunuh dahulu. IMBAS UNIK INDEX akan dilaksanakan untuk mendapatkan ROWID baris, yang kemudiannya akan digunakan untuk mendapatkan data daripada jadual EMPLOYEES dalam operasi ID=7.
kami bergerak ke peringkat yang lebih tinggi selepas semua operasi di peringkat semasa dan keturunan mereka telah diproses. Operasi terakhir yang akan dilakukan ialah menggabungkan NESTED LOOPS dengan ID=1, selepas itu hasilnya akan dihantar ID=0.
Urutan operasi adalah seperti berikut: 4-3-6-5-2-8-7-1-0

Di bawah adalah Penerangan terperinci rancangan pelaksanaan:

Pertama, gelung bersarang dalaman dilaksanakan menggunakan LOCATIONS sebagai jadual utama, menggunakan akses indeks pada lajur CITY. Operasi ini berfungsi kerana anda hanya mencari jabatan di Seattle.

Hasilnya dicantumkan ke jadual DEPARTMENTS menggunakan indeks pada lajur LOCATION_ID untuk penyertaan. Hasil daripada operasi gabungan pertama ialah sumber data utama untuk gelung bersarang kedua.

Operasi gabungan kedua memeriksa indeks pada lajur EMPLOYEE_ID pada jadual EMPLOYEES. Operasi ini boleh dilakukan kerana sistem mengetahui (dari operasi gabungan pertama) ID semua pengurus jabatan di Seattle. Dalam kes ini, UNIQUE SCAN dilakukan kerana imbasan dilakukan pada indeks kunci utama.

Pertama, sistem mencincang jadual T3 ke dalam memori (ID=3)

Sistem kemudian mencincang jadual T1 ke dalam memori (ID=5)

Kemudian pengimbasan jadual T2 (ID-6) bermula

Sistem mengambil satu baris dari T2 dan memeriksa T1 (T1.i=T2.i)

Jika talian itu bertahan, sistem mencarinya dalam T3 (T1.i=T3.i)

Jika baris itu bertahan, sistem akan meneruskannya ke operasi seterusnya.

Isu sistem nilai maksimum daripada set keputusan sebelumnya.

Susunan pelaksanaan adalah seperti berikut: 3-5-6-4-2-1

Susunan penggabungan: T1-T2-T3

Membaca Pelan Pelaksanaan yang Lebih Komprehensif

Pelan yang ditunjukkan dalam rajah di sebelah kiri dibina daripada pertanyaan daripada kamus data. Ia sangat panjang, jadi sangat sukar untuk menggunakan kaedah tafsiran sebelumnya untuk mencari operasi pertama.

Anda boleh memendekkan garis besar untuk menjadikannya lebih mudah dibaca. Angka di sebelah kanan menunjukkan pelan pelaksanaan yang sama, hanya dipendekkan. Seperti yang ditunjukkan dalam rajah, ini mudah dilakukan menggunakan pengurus Perusahaan atau SQL*Developer. Seperti yang dapat dilihat dalam rajah, rancangan itu termasuk operasi penggabungan dua cawangan. Pengetahuan tentang kamus data membolehkan kami memahami bahawa kedua-dua cawangan itu sepadan dengan ruang jadual diurus kamus dan diurus setempat. Pengetahuan tentang pangkalan data membolehkan anda memahami bahawa pangkalan data tidak mempunyai ruang jadual yang diuruskan kamus. Dengan cara ini, jika ada masalah, ia berada di cawangan kedua. Untuk mengesahkan andaian anda, anda perlu melihat maklumat pelan dan statistik pelaksanaan setiap sumber baris untuk menentukan bahagian pelan yang menggunakan paling banyak sumber. Kemudian anda perlu mengembangkan cawangan di mana masalah ditemui. Untuk menggunakan kaedah ini, anda juga mesti menggunakan statistik pelaksanaan, yang boleh didapati dalam paparan V$SQL_PLAN_STATISTICS atau dalam laporan tkprof yang dijana daripada fail surih. Sebagai contoh, tkprof menjumlahkan masa yang diambil oleh setiap operasi induk untuk dilaksanakan ditambah dengan masa pelaksanaan semua operasi anak.

Gambaran Keseluruhan Rancangan Pelaksanaan

Apabila anda menyediakan ungkapan SQL dalam persekitaran OLTP, matlamatnya adalah untuk bermula dengan jadual yang mempunyai penapis selektiviti tertinggi digunakan. Ini bermakna bahawa lebih sedikit baris akan diteruskan ke operasi seterusnya. Jika langkah seterusnya ialah bergabung, diandaikan bahawa lebih sedikit baris akan dicantumkan. Anda juga perlu memastikan bahawa laluan akses data adalah optimum. Semasa anda memeriksa pelan pelaksanaan, perhatikan perkara berikut:

Pelan ini direka bentuk supaya penapis terbaik digunakan pada jadual utama
Perintah sertai direka bentuk supaya bilangan baris paling sedikit dihantar ke langkah seterusnya (iaitu, susunan gabungan harus pergi ke penapis terbaik yang belum digunakan)
Kaedah gabungan sepadan dengan bilangan baris yang dihantar kepadanya untuk bergabung. Contohnya, gabungan NESTED LOOP menggunakan indeks mungkin tidak optimum apabila banyak baris dikembalikan.
Pandangan digunakan dengan berkesan. Lihat pada PILIH senarai untuk menentukan di mana paparan itu perlu diakses.
Tiada operasi produk Cartesian bagi jadual (walaupun dengan jadual kecil).
Setiap jadual dibaca dengan cekap: pertimbangkan Predikat SQL ungkapan berhubung dengan bilangan baris dalam jadual. Lihat operasi yang mencurigakan secara terperinci, contohnya imbasan jadual penuh untuk jadual dengan jumlah yang besar rentetan yang terdapat dalam predikat.

Imbasan jadual penuh boleh digunakan dengan berkesan untuk meja kecil, atau untuk aplikasi jenis tertentu bergabung (cth HASH JOIN) untuk baris yang dikembalikan.

Jika mana-mana syarat di atas tidak optimum, anda perlu menulis semula SQL atau menyemak indeks yang tersedia pada jadual.

Pelan pelaksanaan itu sendiri tidak dapat memberikan maklumat tentang kecekapan pelaksanaan pertanyaan. Sebagai contoh, output EXPLAIN PLAN mungkin menunjukkan penggunaan indeks, tetapi ini tidak bermakna ungkapan itu berkesan. Kadangkala menggunakan indeks boleh menjadi sangat tidak cekap.

Untuk menentukan rancangan yang optimum pelaksanaan, anda perlu mendapatkan pelan awal, dan kemudian mengoptimumkannya untuk pelaksanaan yang lebih optimum menggunakan ujian. Semasa menukar pelan pelaksanaan, anda perlu memantau penggunaan sumber sistem.