Penulis artikel ini adalah
Victor Varlamov (
varlamovVp18 ), OCP.
Artikel asli diterbitkan pada 07/07/2017.
Terima kasih khusus kepada penulis terjemahan -
brutaltag .
Sistem pelaporan kami biasanya mengeksekusi ratusan permintaan panjang yang dipicu oleh berbagai peristiwa. Parameter kueri adalah daftar klien dan interval waktu (harian, mingguan, bulanan). Karena data yang tidak rata dalam tabel, satu kueri dapat menghasilkan satu baris dan satu juta baris, tergantung pada parameter laporan (klien yang berbeda memiliki jumlah baris yang berbeda dalam tabel fakta). Setiap laporan dibuat dalam bentuk paket dengan fungsi utama yang mengambil parameter input, melakukan transformasi tambahan, lalu membuka kursor statis dengan
variabel terkait dan akhirnya mengembalikan kursor terbuka ini. Parameter DB CURSOR_SHARING diatur ke FORCE.
Dalam situasi seperti itu, kita harus berurusan dengan kinerja yang buruk, baik dalam hal penggunaan kembali rencana permintaan oleh pengoptimal, dan dengan
analisis lengkap permintaan dengan parameter dalam bentuk
literal . Variabel terikat dapat menyebabkan rencana kueri optimal.
Dalam bukunya "Oracle Expert Practices," Alex Gorbachev memberikan kisah menarik yang diceritakan oleh Tom Kite. Setiap Senin hujan, pengguna harus berurusan dengan rencana kueri yang dimodifikasi. Sulit dipercaya, tapi itu:
“Menurut pengamatan pengguna akhir, saat hujan deras pada hari Senin, kinerja basis data sangat buruk. Pada hari lain dalam seminggu atau pada hari Senin tidak ada masalah tanpa hujan. Dari percakapan dengan DBA, Tom Kite mengetahui bahwa kesulitan berlanjut sampai database dipaksa untuk memulai kembali, setelah itu kinerja menjadi normal. Itu solusi yang sangat baik: hari Senin hujan - mulai lagi. ”
Ini adalah kasus nyata, dan masalahnya diselesaikan sepenuhnya tanpa sihir, hanya berkat pengetahuan yang sangat baik tentang cara kerja Oracle. Saya akan menunjukkan solusinya di akhir artikel.
Ini adalah contoh kecil bagaimana variabel terkait bekerja.
Buat tabel dengan data tidak rata.
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
Dengan kata lain, kami memiliki tabel VVP_HARD_PARSE_TEST dengan sejuta baris, di mana dalam 10.000 kasus bidang adalah C2 = 99, 8 catatan dengan C2 = 1, dan sisanya dengan C2 = 1.000.000. Histogram melintasi bidang C2 menunjukkan pengoptimal Oracle tentang distribusi data ini. Situasi ini dikenal sebagai
distribusi data yang tidak merata , dan histogram dapat membantu Anda memilih paket kueri yang tepat berdasarkan data yang diminta.
Kami mengamati pertanyaan sederhana ke tabel ini. Jelas untuk permintaan itu
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :pjika p = 1, maka INDEX RANGE SCAN adalah pilihan terbaik, untuk kasus p = 1000000 lebih baik menggunakan FULL TABLE SCAN. Query1 dan Query1000000 queries identik, dengan pengecualian teks dalam komentar, ini dilakukan untuk mendapatkan pengidentifikasi rencana kueri yang berbeda.
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Sekarang mari kita lihat rencana permintaan:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Seperti yang Anda lihat, rencana untuk berbagai permintaan dibuat hanya sekali, pada saat eksekusi pertama (hanya satu kursor anak dengan CHILD_NUMBER = 0 ada untuk setiap permintaan). Setiap permintaan dieksekusi dua kali (EXECUTION = 2). Selama penguraian keras, Oracle mengambil nilai dari variabel terkait dan memilih paket sesuai dengan nilai-nilai ini. Tapi dia menggunakan rencana yang sama untuk putaran berikutnya, meskipun variabel terkait berubah pada putaran kedua. Rencana non-optimal digunakan - Query1000000 dengan variabel C2 = 1 menggunakan FULL TABLE SCAN, bukan INDEX RANGE SCAN, dan sebaliknya.
Jelas bahwa memperbaiki aplikasi dan menggunakan parameter sebagai literal dalam kueri adalah cara yang paling cocok untuk memecahkan masalah, tetapi mengarah ke SQL dinamis dengan kekurangan yang diketahui. Cara lain adalah dengan menonaktifkan kueri untuk variabel terkait (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) atau hapus histogram (
tautan ).
Salah satu solusi yang mungkin adalah penggunaan alternatif kebijakan akses data, juga dikenal sebagai
Virtual Private Database (kontrol akses terperinci,
Fine Grained Access Control , kontrol level baris). Ini memungkinkan Anda untuk mengubah permintaan dengan cepat dan karenanya dapat menyebabkan analisis lengkap dari rencana permintaan setiap kali permintaan menggunakan kontrol akses terperinci. Teknik ini dijelaskan secara rinci dalam sebuah
artikel oleh Randalph Geist . Kerugian dari metode ini adalah meningkatnya jumlah parse lengkap dan ketidakmampuan untuk memanipulasi rencana kueri.
Lihat apa yang akan kita lakukan sekarang. Setelah menganalisis data kami, kami memutuskan untuk membagi pelanggan menjadi tiga kategori - Besar, Menengah dan Kecil (LMS atau 9-5-1) - sesuai dengan jumlah transaksi atau transaksi selama tahun tersebut. Juga, jumlah baris dalam laporan sangat tergantung pada periode: Bulanan - Besar, Mingguan - Tengah, Harian - Kecil atau 9-5-1. Selanjutnya, solusinya sederhana - kami akan membuat predikat kebijakan keamanan tergantung pada setiap kategori dan pada setiap periode. Jadi, untuk setiap permintaan kami mendapatkan 9 kursor anak yang memungkinkan. Selain itu, kueri dengan kebijakan berbeda akan mengarahkan kita ke pengidentifikasi kueri yang sama, ini memungkinkan untuk mengimplementasikan SQL PLAN MANAGEMENT (baseline rencana sql).
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
Sekarang, jika kami ingin menanamkan teknologi seperti itu dalam laporan, kami perlu menambahkan HARD_PARSE_TABLE ke kueri (ini tidak akan merusaknya sedikit pun) dan panggil CALC_PREDICATES sebelum kueri utama dijalankan.
Mari kita lihat bagaimana teknik ini dapat mengubah contoh sebelumnya:
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Mari kita lihat rencana eksekusi:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Tampak hebat! Setiap kueri dieksekusi dua kali, dengan kursor anak berbeda dan paket berbeda. Untuk parameter C2 = 1.000.000, kita melihat FULL TABLE SCAN di kedua kueri, dan untuk parameter C1 = 1 kita selalu melihat INDEX RANGE SCAN.
Pada akhirnya saya memberikan solusi untuk kasus Senin hujan:
“Ternyata setiap akhir pekan pada hari Minggu ada cadangan dingin , sehingga semua rencana permintaan dibuat kembali pada eksekusi pertama pada Senin pagi. Salah satu karyawan biasanya memulai pekerjaan mereka lebih awal dari yang lain, dan rencana permintaannya dieksekusi dengan baik untuk pengguna lain selama seminggu. Namun, jika hujan, pengguna ini terlambat pada awal hari kerja karena masalah dengan rute paginya. Kemudian perhitungan batch laporan adalah yang pertama kali dimulai, tetapi rencana kueri benar-benar buruk untuk kasus yang tersisa karena nilai yang tidak sesuai dari variabel terkait. "
Dan beberapa tampilan sistem yang berguna:
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind