10 trik untuk bekerja dengan Oracle

Ada beberapa praktik Oracle di Sberbank yang mungkin berguna bagi Anda. Saya rasa beberapa di antaranya tidak asing bagi Anda, tetapi kami tidak hanya menggunakan alat ETL untuk memuat, tetapi juga prosedur tersimpan Oracle. Oracle PL / SQL mengimplementasikan algoritma paling kompleks untuk memuat data ke dalam penyimpanan, di mana Anda perlu "merasakan setiap byte".

• Pencatatan kompilasi otomatis
• Apa yang harus dilakukan jika Anda ingin membuat tampilan dengan parameter
• Menggunakan statistik dinamis dalam kueri
• Bagaimana cara menyimpan rencana kueri saat memasukkan data melalui link database
• Menjalankan prosedur dalam sesi paralel
• Menarik melalui sisa makanan
• Menggabungkan banyak cerita menjadi satu
• Normalizer
• Merender dalam format SVG
• Aplikasi Pencarian Metadata Oracle

Pencatatan kompilasi otomatis

Pada beberapa database Oracle, Sberbank memiliki pemicu kompilasi yang mengingat siapa, kapan dan apa yang berubah dalam kode objek server. Dengan demikian, pembuat perubahan dapat dibuat dari tabel log kompilasi. Sistem kontrol versi juga diterapkan secara otomatis. Bagaimanapun, jika programmer lupa untuk mengirimkan perubahan ke Git, maka mekanisme ini akan melakukan hedge. Mari kita gambarkan contoh implementasi sistem pencatatan kompilasi otomatis semacam itu. Salah satu versi sederhana dari pemicu kompilasi yang menulis ke log dalam bentuk tabel ddl_changes_log terlihat seperti ini:

create table DDL_CHANGES_LOG
(
  id               INTEGER,
  change_date      DATE,
  sid              VARCHAR2(100),
  schemaname       VARCHAR2(30),
  machine          VARCHAR2(100),
  program          VARCHAR2(100),
  osuser           VARCHAR2(100),
  obj_owner        VARCHAR2(30),
  obj_type         VARCHAR2(30),
  obj_name         VARCHAR2(30),
  previous_version CLOB,
  changes_script   CLOB
);

create or replace trigger trig_audit_ddl_trg
  before ddl on database
declare
  v_sysdate              date;
  v_valid                number;
  v_previous_obj_owner   varchar2(30) := '';
  v_previous_obj_type    varchar2(30) := '';
  v_previous_obj_name    varchar2(30) := '';
  v_previous_change_date date;
  v_lob_loc_old          clob := '';
  v_lob_loc_new          clob := '';
  v_n                    number;
  v_sql_text             ora_name_list_t;
  v_sid                  varchar2(100) := '';
  v_schemaname           varchar2(30) := '';
  v_machine              varchar2(100) := '';
  v_program              varchar2(100) := '';
  v_osuser               varchar2(100) := '';
begin
  v_sysdate := sysdate;
  -- find whether compiled object already presents and is valid
  select count(*)
    into v_valid
    from sys.dba_objects
   where owner = ora_dict_obj_owner
     and object_type = ora_dict_obj_type
     and object_name = ora_dict_obj_name
     and status = 'VALID'
     and owner not in ('SYS', 'SPOT', 'WMSYS', 'XDB', 'SYSTEM')
     and object_type in ('TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW');
  -- find information about previous compiled object
  select max(obj_owner) keep(dense_rank last order by id),
         max(obj_type) keep(dense_rank last order by id),
         max(obj_name) keep(dense_rank last order by id),
         max(change_date) keep(dense_rank last order by id)
    into v_previous_obj_owner, v_previous_obj_type, v_previous_obj_name, v_previous_change_date
    from ddl_changes_log;
  -- if compile valid object or compile invalid package body broken by previous compilation of package then log it
  if (v_valid = 1 or v_previous_obj_owner = ora_dict_obj_owner and
     (v_previous_obj_type = 'PACKAGE' and ora_dict_obj_type = 'PACKAGE BODY' or
     v_previous_obj_type = 'PACKAGE BODY' and ora_dict_obj_type = 'PACKAGE') and
     v_previous_obj_name = ora_dict_obj_name and
     v_sysdate - v_previous_change_date <= 1 / 24 / 60 / 2) and
     ora_sysevent in ('CREATE', 'ALTER') then
    -- store previous version of object (before compilation) from dba_source or dba_views in v_lob_loc_old
    if ora_dict_obj_type <> 'VIEW' then
      for z in (select substr(text, 1, length(text) - 1) || chr(13) || chr(10) as text
                  from sys.dba_source
                 where owner = ora_dict_obj_owner
                   and type = ora_dict_obj_type
                   and name = ora_dict_obj_name
                 order by line) loop
        v_lob_loc_old := v_lob_loc_old || z.text;
      end loop;
    else
      select sys.dbms_metadata_util.long2clob(v.textlength, 'SYS.VIEW$', 'TEXT', v.rowid) into v_lob_loc_old
        from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u
       where o.obj# = v.obj#
         and o.owner# = u.user#
         and u.name = ora_dict_obj_owner
         and o.name = ora_dict_obj_name;
    end if;
    -- store new version of object (after compilation) from v_sql_text in v_lob_loc_new
    v_n := ora_sql_txt(v_sql_text);
    for i in 1 .. v_n loop
      v_lob_loc_new := v_lob_loc_new || replace(v_sql_text(i), chr(10), chr(13) || chr(10));
    end loop;
    -- find information about session that changed this object
    select max(to_char(sid)), max(schemaname), max(machine), max(program), max(osuser)
      into v_sid, v_schemaname, v_machine, v_program, v_osuser
      from v$session
     where audsid = userenv('sessionid');
    -- store changes in ddl_changes_log
    insert into ddl_changes_log
      (id, change_date, sid, schemaname, machine, program, osuser,
       obj_owner, obj_type, obj_name, previous_version, changes_script)
    values
      (seq_ddl_changes_log.nextval, v_sysdate, v_sid, v_schemaname, v_machine, v_program, v_osuser,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, v_lob_loc_old, v_lob_loc_new);
  end if;
exception
  when others then
    null;
end;

Dalam pemicu ini, nama dan konten baru dari objek yang dikompilasi diperoleh, dilengkapi dengan konten sebelumnya dari kamus data, dan ditulis ke log perubahan.

Apa yang harus dilakukan jika Anda ingin membuat tampilan dengan parameter

Keinginan seperti itu sering kali dapat dikunjungi oleh pengembang di Oracle. Mengapa dimungkinkan untuk membuat prosedur atau fungsi dengan parameter, tetapi tidak ada tampilan dengan parameter input yang dapat digunakan dalam penghitungan? Oracle memiliki sesuatu untuk menggantikan konsep yang hilang ini, menurut pendapat kami.

Mari kita lihat contohnya. Biarkan ada tabel dengan penjualan menurut divisi untuk setiap hari.

create table DIVISION_SALES
(
  division_id INTEGER,
  dt          DATE,
  sales_amt   NUMBER
);

Kueri ini membandingkan penjualan menurut departemen selama dua hari. Dalam hal ini, 04/30/2020 dan 09/11/2020.

select t1.division_id,
       t1.dt          dt1,
       t2.dt          dt2,
       t1.sales_amt   sales_amt1,
       t2.sales_amt   sales_amt2
  from (select dt, division_id, sales_amt
          from division_sales
         where dt = to_date('30.04.2020', 'dd.mm.yyyy')) t1,
       (select dt, division_id, sales_amt
          from division_sales
         where dt = to_date('11.09.2020', 'dd.mm.yyyy')) t2
 where t1.division_id = t2.division_id;

Berikut adalah pandangan yang ingin saya tulis untuk meringkas permintaan semacam itu. Saya ingin memberikan tanggal sebagai parameter. Namun, sintaksis tidak mengizinkan hal ini.

create or replace view vw_division_sales_report(in_dt1 date, in_dt2 date) as
select t1.division_id,
       t1.dt          dt1,
       t2.dt          dt2,
       t1.sales_amt   sales_amt1,
       t2.sales_amt   sales_amt2
  from (select dt, division_id, sales_amt
          from division_sales
         where dt = in_dt1) t1,
       (select dt, division_id, sales_amt
          from division_sales
         where dt = in_dt2) t2
 where t1.division_id = t2.division_id;

Solusi seperti itu disarankan. Mari buat tipe garis dari tampilan ini.

create type t_division_sales_report as object
(
  division_id INTEGER,
  dt1         DATE,
  dt2         DATE,
  sales_amt1  NUMBER,
  sales_amt2  NUMBER
);

Dan buat tipe untuk tabel dari string tersebut.

create type t_division_sales_report_table as table of t_division_sales_report;

Alih-alih sebuah view, mari tulis fungsi pipelined dengan parameter input tanggal.

create or replace function func_division_sales(in_dt1 date, in_dt2 date)
  return t_division_sales_report_table
  pipelined as
begin
  for z in (select t1.division_id,
                   t1.dt          dt1,
                   t2.dt          dt2,
                   t1.sales_amt   sales_amt1,
                   t2.sales_amt   sales_amt2
              from (select dt, division_id, sales_amt
                      from division_sales
                     where dt = in_dt1) t1,
                   (select dt, division_id, sales_amt
                      from division_sales
                     where dt = in_dt2) t2
             where t1.division_id = t2.division_id) loop
    pipe row(t_division_sales_report(z.division_id,
                                     z.dt1,
                                     z.dt2,
                                     z.sales_amt1,
                                     z.sales_amt2));
  end loop;
end;

Anda bisa merujuknya seperti ini:

select *
  from table(func_division_sales(to_date('30.04.2020', 'dd.mm.yyyy'),
                                 to_date('11.09.2020', 'dd.mm.yyyy')));

Permintaan ini akan memberi kita hasil yang sama dengan permintaan di awal posting ini dengan tanggal yang diganti secara eksplisit.

Fungsi pipelined juga bisa berguna saat Anda perlu meneruskan parameter di dalam permintaan yang kompleks.

Misalnya, pertimbangkan tampilan kompleks di mana bidang1, yang Anda gunakan untuk memfilter data, disembunyikan di suatu tempat jauh di dalam tampilan.

create or replace view complex_view as
 select field1, ...
   from (select field1, ...
           from (select field1, ... from deep_table), table1
          where ...),
        table2
  where ...;

Dan kueri dari tampilan dengan nilai tetap bidang1 mungkin memiliki rencana eksekusi yang buruk.

select field1, ... from complex_view
 where field1 = 'myvalue';

Itu. alih-alih memfilter deep_table terlebih dahulu dengan kondisi field1 = 'myvalue', kueri pertama-tama dapat menggabungkan semua tabel, setelah memproses data dalam jumlah besar yang tidak perlu, dan kemudian memfilter hasilnya dengan field kondisi1 = 'myvalue'. Kompleksitas ini dapat dihindari dengan membuat fungsi dengan parameter yang ditetapkan ke bidang1 alih-alih tampilan pipelined.

Menggunakan statistik dinamis dalam kueri

Itu terjadi bahwa permintaan yang sama dalam proses database Oracle setiap kali jumlah data yang berbeda dalam tabel dan subkueri digunakan di dalamnya. Bagaimana Anda mendapatkan pengoptimal untuk mengetahui cara mana untuk menggabungkan tabel kali ini dan indeks mana yang akan digunakan setiap kali? Pertimbangkan, misalnya, kueri yang menghubungkan sebagian saldo akun yang telah berubah sejak unduhan terakhir ke direktori akun. Porsi saldo akun yang diubah sangat bervariasi dari download ke download, berjumlah ratusan baris, kemudian jutaan baris. Bergantung pada ukuran bagian ini, saldo yang diubah harus digabungkan dengan akun baik dengan metode / * + use_nl * /, atau dengan metode / * + use_hash * /. Tidak nyaman untuk mengumpulkan ulang statistik setiap kali, terutama jika jumlah baris berubah dari pemuatan ke pemuatan bukan dalam tabel yang digabungkan, tetapi dalam subkueri yang digabungkan.Petunjuk / * + dynamic_sampling () * / dapat membantu di sini. Mari kita tunjukkan bagaimana pengaruhnya, menggunakan permintaan contoh. Biarkan tabel change_balances berisi perubahan saldo, dan akun - direktori akun. Kami menggabungkan tabel ini dengan bidang account_id yang tersedia di masing-masing tabel. Di awal percobaan, kita akan menulis lebih banyak baris di tabel ini dan tidak akan mengubah isinya.

Pertama, mari kita ambil 10% dari perubahan residual di tabel change_balances dan lihat rencana apa yang akan menggunakan dynamic_sampling:

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test1'
  3   INTO plan_table
  4  FOR  with c as
  5   (select /*+ dynamic_sampling(change_balances 2)*/
  6     account_id, balance_amount
  7      from change_balances
  8     where mod(account_id, 10) = 0)
  9  select a.account_id, a.account_number, c.balance_amount
 10    from c, accounts a
 11   where c.account_id = a.account_id;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 874320301

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  9951K|   493M|       |   140K  (1)| 00:28:10 |
|*  1 |  HASH JOIN         |                 |  9951K|   493M|  3240K|   140K  (1)| 00:28:10 |
|*  2 |   TABLE ACCESS FULL| CHANGE_BALANCES |   100K|  2057K|       |  7172   (1)| 00:01:27 |
|   3 |   TABLE ACCESS FULL| ACCOUNTS        |    10M|   295M|       |   113K  (1)| 00:22:37 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")
   2 - filter(MOD("ACCOUNT_ID",10)=0)

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Jadi, kami melihat bahwa kami disarankan untuk memeriksa tabel change_balances dan akun menggunakan pemindaian penuh dan bergabung dengan mereka menggunakan hash join.

Sekarang mari kita secara drastis mengurangi sampel dari change_balances. Mari ambil 0,1% dari sisa perubahan dan lihat rencana apa yang akan menggunakan dynamic_sampling:

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test2'
  3   INTO plan_table
  4  FOR  with c as
  5   (select /*+ dynamic_sampling(change_balances 2)*/
  6     account_id, balance_amount
  7      from change_balances
  8     where mod(account_id, 1000) = 0)
  9  select a.account_id, a.account_number, c.balance_amount
 10    from c, accounts a
 11   where c.account_id = a.account_id;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 2360715730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        | 73714 |  3743K| 16452   (1)| 00:03:18 |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        | 73714 |  3743K| 16452   (1)| 00:03:18 |
|*  3 |    TABLE ACCESS FULL         | CHANGE_BALANCES        |   743 | 15603 |  7172   (1)| 00:01:27 |
|*  4 |    INDEX RANGE SCAN          | IX_ACCOUNTS_ACCOUNT_ID |   104 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| ACCOUNTS               |    99 |  3069 |   106   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(MOD("ACCOUNT_ID",1000)=0)
   4 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.

Kali ini, tabel akun dilampirkan ke tabel change_balances dengan loop bersarang dan indeks digunakan untuk membaca baris dari akun.

Jika petunjuk dynamic_sampling dihapus, maka dalam kasus kedua rencana akan tetap sama seperti pada kasus pertama, dan ini tidak optimal.

Detail tentang petunjuk dynamic_sampling dan kemungkinan nilai untuk argumen numeriknya dapat ditemukan di dokumentasi.

Bagaimana cara menyimpan rencana kueri saat memasukkan data melalui link database

Kami memecahkan masalah ini. Server sumber data memiliki tabel yang perlu digabungkan dan dimuat ke gudang data. Misalkan sebuah view ditulis pada server sumber, yang berisi semua logika transformasi ETL yang diperlukan. Tampilan ditulis secara optimal, ini berisi petunjuk untuk pengoptimal yang menyarankan cara menggabungkan tabel dan indeks mana yang akan digunakan. Di sisi server gudang data, Anda perlu melakukan hal sederhana - memasukkan data dari tampilan ke tabel target. Dan di sini kesulitan bisa muncul. Jika Anda memasukkan ke dalam tabel target dengan perintah seperti

insert into dwh_table
  (field1, field2)
  select field1, field2 from vw_for_dwh_table@xe_link;

, maka semua logika rencana kueri yang terdapat dalam tampilan tempat kita membaca data melalui tautan basis data dapat diabaikan. Semua petunjuk yang disematkan dalam tampilan ini dapat diabaikan.

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test'
  3   INTO plan_table
  4  FOR  insert into dwh_table
  5    (field1, field2)
  6    select field1, field2 from vw_for_dwh_table@xe_link;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 1788691278

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                  |     1 |  2015 |     2   (0)| 00:00:01 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | DWH_TABLE        |       |       |            |          |        |      |
|   2 |   REMOTE                 | VW_FOR_DWH_TABLE |     1 |  2015 |     2   (0)| 00:00:01 | XE_LI~ | R->S |
-------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT /*+ OPAQUE_TRANSFORM */ "FIELD1","FIELD2" FROM "VW_FOR_DWH_TABLE" "VW_FOR_DWH_TABLE"
       (accessing 'XE_LINK' )


16 rows selected.

Untuk menyimpan rencana kueri dalam tampilan, Anda dapat menggunakan penyisipan data ke dalam tabel target dari kursor:

declare
  cursor cr is
    select field1, field2 from vw_for_dwh_table@xe_link;
  cr_row cr%rowtype;
begin
  open cr;
  loop
    fetch cr
      into cr_row;
    insert into dwh_table
      (field1, field2)
    values
      (cr_row.field1, cr_row.field2);
    exit when cr%notfound;
  end loop;
  close cr;
end;

Kueri dari kursor

select field1, field2 from vw_for_dwh_table@xe_link;

sebagai lawan menyisipkan

insert into dwh_table
  (field1, field2)
  select field1, field2 from vw_for_dwh_table@xe_link;

akan menyimpan rencana permintaan, diletakkan dalam tampilan di server sumber.

Menjalankan prosedur dalam sesi paralel

Seringkali tugasnya adalah memulai beberapa perhitungan paralel dari beberapa prosedur induk dan, setelah menunggu masing-masing selesai, lanjutkan menjalankan prosedur induk. Ini dapat berguna dalam komputasi paralel jika sumber daya server mengizinkannya. Ada banyak cara untuk melakukan ini.

Mari kita gambarkan implementasi yang sangat sederhana dari mekanisme semacam itu. Prosedur paralel akan dilakukan dalam pekerjaan “satu kali” paralel, sedangkan prosedur induk akan menunggu dalam satu putaran untuk menyelesaikan semua pekerjaan ini.

Mari buat tabel dengan metadata untuk mekanisme ini. Untuk memulainya, mari kita buat tabel dengan grup prosedur yang berjalan secara paralel:

create table PARALLEL_PROC_GROUP_LIST
(
  group_id   INTEGER,
  group_name VARCHAR2(4000)
);
comment on column PARALLEL_PROC_GROUP_LIST.group_id
  is '    ';
comment on column PARALLEL_PROC_GROUP_LIST.group_name
  is '    ';

Selanjutnya kita akan membuat tabel dengan script yang akan dieksekusi secara paralel dalam kelompok. Pengisian tabel ini dapat berupa statis atau dibuat secara dinamis:

create table PARALLEL_PROC_LIST
(
  group_id    INTEGER,
  proc_script VARCHAR2(4000),
  is_active   CHAR(1) default 'Y'
);
comment on column PARALLEL_PROC_LIST.group_id
  is '    ';
comment on column PARALLEL_PROC_LIST.proc_script
  is 'Pl/sql    ';
comment on column PARALLEL_PROC_LIST.is_active
  is 'Y - active, N - inactive.          ';

Dan kami akan membuat tabel log, di mana kami akan mengumpulkan log yang prosedurnya dimulai di pekerjaan mana:

create table PARALLEL_PROC_LOG
(
  run_id      INTEGER,
  group_id    INTEGER,
  proc_script VARCHAR2(4000),
  job_id      INTEGER,
  start_time  DATE,
  end_time    DATE
);
comment on column PARALLEL_PROC_LOG.run_id
  is '   run_in_parallel';
comment on column PARALLEL_PROC_LOG.group_id
  is '    ';
comment on column PARALLEL_PROC_LOG.proc_script
  is 'Pl/sql    ';
comment on column PARALLEL_PROC_LOG.job_id
  is 'Job_id ,      ';
comment on column PARALLEL_PROC_LOG.start_time
  is '  ';
comment on column PARALLEL_PROC_LOG.end_time
  is '  ';

create sequence Seq_Parallel_Proc_Log;

Sekarang mari kita berikan kode prosedur untuk memulai aliran paralel:

create or replace procedure run_in_parallel(in_group_id integer) as
  --        parallel_proc_list.
  --  -    parallel_proc_list
  v_run_id             integer;
  v_job_id             integer;
  v_job_id_list        varchar2(32767);
  v_job_id_list_ext    varchar2(32767);
  v_running_jobs_count integer;
begin
  select seq_parallel_proc_log.nextval into v_run_id from dual;
  -- submit jobs with the same parallel_proc_list.in_group_id
  -- store seperated with ',' JOB_IDs in v_job_id_list
  v_job_id_list     := null;
  v_job_id_list_ext := null;
  for z in (select pt.proc_script
              from parallel_proc_list pt
             where pt.group_id = in_group_id
               and pt.is_active = 'Y') loop
    dbms_job.submit(v_job_id, z.proc_script);
    insert into parallel_proc_log
      (run_id, group_id, proc_script, job_id, start_time, end_time)
    values
      (v_run_id, in_group_id, z.proc_script, v_job_id, sysdate, null);
    v_job_id_list     := v_job_id_list || ',' || to_char(v_job_id);
    v_job_id_list_ext := v_job_id_list_ext || ' union all select ' ||
                         to_char(v_job_id) || ' job_id from dual';
  end loop;
  commit;
  v_job_id_list     := substr(v_job_id_list, 2);
  v_job_id_list_ext := substr(v_job_id_list_ext, 12);
  -- loop while not all jobs finished
  loop
    -- set parallel_proc_log.end_time for finished jobs
    execute immediate 'update parallel_proc_log set end_time = sysdate where job_id in (' ||
                      v_job_id_list_ext ||
                      ' minus select job from user_jobs where job in (' ||
                      v_job_id_list ||
                      ') minus select job_id from parallel_proc_log where job_id in (' ||
                      v_job_id_list || ') and end_time is not null)';
    commit;
    -- check whether all jobs finished
    execute immediate 'select count(1) from user_jobs where job in (' ||
                      v_job_id_list || ')'
      into v_running_jobs_count;
    -- if all jobs finished then exit
    exit when v_running_jobs_count = 0;
    -- sleep a little
    sys.dbms_lock.sleep(0.1);
  end loop;
end;

Mari kita periksa bagaimana prosedur run_in_parallel bekerja. Mari buat prosedur pengujian yang akan kita panggil dalam sesi paralel.

create or replace procedure sleep(in_seconds integer) as
begin
  sys.Dbms_Lock.Sleep(in_seconds);
end;

Mari isi nama grup dan tabel dengan script yang akan dieksekusi secara paralel.

insert into PARALLEL_PROC_GROUP_LIST(group_id, group_name) values(1, ' ');

insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(5); end;', 'Y');
insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(10); end;', 'Y');

Mari kita mulai sekelompok prosedur paralel.

begin
  run_in_parallel(1);
end;

Setelah selesai, mari kita lihat lognya.

select * from PARALLEL_PROC_LOG;

Kami melihat bahwa waktu pelaksanaan contoh prosedur pengujian memenuhi harapan.

Menarik melalui sisa makanan

Mari kita gambarkan sebuah varian dari pemecahan masalah perbankan yang cukup khas dari "menarik saldo". Misalkan ada tabel fakta perubahan saldo akun. Diperlukan untuk menunjukkan saldo akun saat ini untuk setiap hari di kalender (yang terakhir untuk hari itu). Informasi seperti itu seringkali dibutuhkan di gudang data. Jika pada hari tertentu tidak ada pergerakan dalam hitungan, maka Anda perlu mengulangi sisa terakhir yang diketahui. Jika jumlah data dan daya komputasi server memungkinkan, maka Anda dapat menyelesaikan masalah ini menggunakan kueri SQL, bahkan tanpa menggunakan PL / SQL. Fungsi last_value (* ignore nulls) over (partisi by * order by *) akan membantu kita dalam hal ini, yang akan meregangkan sisa yang diketahui terakhir ke tanggal berikutnya di mana tidak ada perubahan.

Mari buat tabel dan isi dengan data uji.

create table ACCOUNT_BALANCE
(
  dt           DATE,
  account_id   INTEGER,
  balance_amt  NUMBER,
  turnover_amt NUMBER
);
comment on column ACCOUNT_BALANCE.dt
  is '     ';
comment on column ACCOUNT_BALANCE.account_id
  is ' ';
comment on column ACCOUNT_BALANCE.balance_amt
  is '  ';
comment on column ACCOUNT_BALANCE.turnover_amt
  is '  ';

insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('01.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 23, 23);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 01:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 45, 22);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 44, -1);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 67, 67);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 77, 10);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('07.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 72, -5);

Kueri di bawah ini memecahkan masalah kami. Subkueri 'cld' berisi kalender tanggal, di subkueri 'ab' kami mengelompokkan saldo untuk setiap hari, di subkueri 'a' kami mengingat daftar semua akun dan tanggal awal sejarah untuk setiap akun, di subkueri 'pre' untuk setiap akun kami membuat kalender hari sejak awalnya cerita. Permintaan terakhir menambahkan saldo terakhir untuk setiap hari ke kalender hari-hari aktif dari setiap akun dan memperpanjangnya ke hari-hari di mana tidak ada perubahan.

with cld as
 (select /*+ materialize*/
   to_date('01.01.2020', 'dd.mm.yyyy') + level - 1 dt
    from dual
  connect by level <= 10),
ab as
 (select trunc(dt) dt,
         account_id,
         max(balance_amt) keep(dense_rank last order by dt) balance_amt,
         sum(turnover_amt) turnover_amt
    from account_balance
   group by trunc(dt), account_id),
a as
 (select min(dt) min_dt, account_id from ab group by account_id),
pre as
 (select cld.dt, a.account_id from cld left join a on cld.dt >= a.min_dt)
select pre.dt,
       pre.account_id,
       last_value(ab.balance_amt ignore nulls) over(partition by pre.account_id order by pre.dt) balance_amt,
       nvl(ab.turnover_amt, 0) turnover_amt
  from pre
  left join ab
    on pre.dt = ab.dt
   and pre.account_id = ab.account_id
 order by 2, 1;

Hasil kueri sesuai yang diharapkan.

Menggabungkan banyak cerita menjadi satu

Saat memuat data ke dalam penyimpanan, masalah sering terpecahkan ketika Anda perlu membangun satu riwayat untuk suatu entitas, memiliki riwayat terpisah dari atribut entitas ini yang berasal dari berbagai sumber. Misalkan ada beberapa entitas dengan kunci primer primary_key_id, di mana sejarah (start_dt - end_dt) dari tiga atribut berbeda diketahui, terletak di tiga tabel berbeda.

create table HIST1
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute1     NUMBER
);

insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2014-01-01','yyyy-mm-dd'), 7);
insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2015-01-01','yyyy-mm-dd'), 8);
insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2016-01-01','yyyy-mm-dd'), 9);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2014-01-01','yyyy-mm-dd'), 17);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2015-01-01','yyyy-mm-dd'), 18);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2016-01-01','yyyy-mm-dd'), 19);

create table HIST2
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute2     NUMBER
);
 
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2015-01-01','yyyy-mm-dd'), 4);
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2016-01-01','yyyy-mm-dd'), 5);
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2017-01-01','yyyy-mm-dd'), 6);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2015-01-01','yyyy-mm-dd'), 14);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2016-01-01','yyyy-mm-dd'), 15);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2017-01-01','yyyy-mm-dd'), 16);

create table HIST3
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute3     NUMBER
);
 
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2016-01-01','yyyy-mm-dd'), 10);
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2017-01-01','yyyy-mm-dd'), 20);
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2018-01-01','yyyy-mm-dd'), 30);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2016-01-01','yyyy-mm-dd'), 110);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2017-01-01','yyyy-mm-dd'), 120);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2018-01-01','yyyy-mm-dd'), 130);

Sasarannya adalah memuat satu riwayat perubahan dari tiga atribut ke dalam satu tabel.

Di bawah ini adalah kueri yang memecahkan masalah ini. Ini pertama-tama membentuk tabel diagonal q1 dengan data dari sumber yang berbeda untuk atribut yang berbeda (atribut yang tidak ada dalam sumber diisi dengan nol). Kemudian, menggunakan fungsi last_value (* ignore nulls), tabel diagonal diciutkan menjadi satu riwayat, dan nilai atribut terakhir yang diketahui diperluas ke tanggal yang tidak ada perubahannya:

select primary_key_id,
       start_dt,
       nvl(lead(start_dt - 1)
           over(partition by primary_key_id order by start_dt),
           to_date('9999-12-31', 'yyyy-mm-dd')) as end_dt,
       last_value(attribute1 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute1,
       last_value(attribute2 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute2,
       last_value(attribute3 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute3
  from (select primary_key_id,
               start_dt,
               max(attribute1) as attribute1,
               max(attribute2) as attribute2,
               max(attribute3) as attribute3
          from (select primary_key_id,
                       start_dt,
                       attribute1,
                       cast(null as number) attribute2,
                       cast(null as number) attribute3
                  from hist1
                union all
                select primary_key_id,
                       start_dt,
                       cast(null as number) attribute1,
                       attribute2,
                       cast(null as number) attribute3
                  from hist2
                union all
                select primary_key_id,
                       start_dt,
                       cast(null as number) attribute1,
                       cast(null as number) attribute2,
                       attribute3
                  from hist3) q1
         group by primary_key_id, start_dt) q2
 order by primary_key_id, start_dt;

Hasilnya seperti ini:

Normalizer

Terkadang masalah muncul dari normalisasi data yang datang dalam format bidang yang dibatasi. Misalnya berupa tabel seperti ini:

create table DENORMALIZED_TABLE
(
  id  INTEGER,
  val VARCHAR2(4000)
);

insert into DENORMALIZED_TABLE(id, val) values(1, 'aaa,cccc,bb');
insert into DENORMALIZED_TABLE(id, val) values(2, 'ddd');
insert into DENORMALIZED_TABLE(id, val) values(3, 'fffff,e');

Kueri ini menormalkan data dengan menempelkan bidang yang ditautkan koma sebagai beberapa baris:

select id, regexp_substr(val, '[^,]+', 1, column_value) val, column_value
  from denormalized_table,
       table(cast(multiset
                  (select level
                     from dual
                   connect by regexp_instr(val, '[^,]+', 1, level) > 0) as
                  sys.odcinumberlist))
 order by id, column_value;

Hasilnya seperti ini:

Merender dalam format SVG

Seringkali ada keinginan untuk memvisualisasikan indikator numerik yang disimpan dalam database. Misalnya, buat grafik, histogram, bagan. Alat khusus seperti Oracle BI dapat membantu. Namun lisensi untuk alat ini dapat menghabiskan banyak uang, dan menyiapkannya dapat memakan waktu lebih lama daripada menulis kueri SQL "langsung" ke Oracle, yang akan mengembalikan gambar yang sudah jadi. Mari kita tunjukkan dengan contoh bagaimana menggambar dengan cepat gambar seperti itu dalam format SVG menggunakan query.

Misalkan kita memiliki tabel dengan data

create table graph_data(dt date, val number, radius number);

insert into graph_data(dt, val, radius) values (to_date('01.01.2020','dd.mm.yyyy'), 12, 3);
insert into graph_data(dt, val, radius) values (to_date('02.01.2020','dd.mm.yyyy'), 15, 4);
insert into graph_data(dt, val, radius) values (to_date('05.01.2020','dd.mm.yyyy'), 17, 5);
insert into graph_data(dt, val, radius) values (to_date('06.01.2020','dd.mm.yyyy'), 13, 6);
insert into graph_data(dt, val, radius) values (to_date('08.01.2020','dd.mm.yyyy'),  3, 7);
insert into graph_data(dt, val, radius) values (to_date('10.01.2020','dd.mm.yyyy'), 20, 8);
insert into graph_data(dt, val, radius) values (to_date('11.01.2020','dd.mm.yyyy'), 18, 9);

dt adalah tanggal relevansi,

val adalah indikator numerik, dinamika yang kita visualisasikan dari waktu ke waktu,

jari-jari adalah indikator numerik lain yang akan kita gambar dalam bentuk lingkaran dengan radius seperti itu.

Katakanlah beberapa patah kata tentang format SVG. Ini adalah format grafik vektor yang dapat dilihat di browser modern dan diubah ke format grafik lain. Di dalamnya, antara lain, Anda dapat menggambar garis, lingkaran, dan menulis teks:

<line x1="94" x2="94" y1="15" y2="675" style="stroke:rgb(150,255,255); stroke-width:1px"/>
<circle cx="30" cy="279" r="3" style="fill:rgb(255,0,0)"/>
<text x="7" y="688" font-size="10" fill="rgb(0,150,255)">2020-01-01</text>

Di bawah ini adalah kueri SQL ke Oracle yang memplot grafik dari data di tabel ini. Di sini subkueri const berisi berbagai pengaturan konstan - ukuran gambar, jumlah label pada sumbu grafik, warna garis dan lingkaran, ukuran font, dll. Di subkueri gd1, kami mengonversi data dari tabel graph_data ke koordinat x dan y pada gambar. Subkueri gd2 mengingat titik waktu sebelumnya, dari mana garis harus ditarik ke titik baru. Blok 'header' adalah header gambar dengan latar belakang putih. Blok 'garis vertikal' menggambar garis vertikal. Label blok 'tanggal di bawah garis vertikal' tanggal pada sumbu x. Blok 'garis horizontal' menggambar garis horizontal. Blok 'nilai dekat garis horizontal' memberi label nilai pada sumbu y. Blok 'lingkaran' menggambar lingkaran dengan radius tertentu di tabel data_graf.Blok 'data grafik' membangun grafik dinamika indikator val dari tabel data_graf dari garis. Blok 'footer' menambahkan tag trailing.

with const as
 (select 700 viewbox_width,
         700 viewbox_height,
         30 left_margin,
         30 right_margin,
         15 top_margin,
         25 bottom_margin,
         max(dt) - min(dt) + 1 num_vertical_lines,
         11 num_horizontal_lines,
         'rgb(150,255,255)' stroke_vertical_lines,
         '1px' stroke_width_vertical_lines,
         10 font_size_dates,
         'rgb(0,150,255)' fill_dates,
         23 x_dates_pad,
         13 y_dates_pad,
         'rgb(150,255,255)' stroke_horizontal_lines,
         '1px' stroke_width_horizontal_lines,
         10 font_size_values,
         'rgb(0,150,255)' fill_values,
         4 x_values_pad,
         2 y_values_pad,
         'rgb(255,0,0)' fill_circles,
         'rgb(51,102,0)' stroke_graph,
         '1px' stroke_width_graph,
         min(dt) min_dt,
         max(dt) max_dt,
         max(val) max_val
    from graph_data),
gd1 as
 (select graph_data.dt,
         const.left_margin +
         (const.viewbox_width - const.left_margin - const.right_margin) *
         (graph_data.dt - const.min_dt) / (const.max_dt - const.min_dt) x,
         const.viewbox_height - const.bottom_margin -
         (const.viewbox_height - const.top_margin - const.bottom_margin) *
         graph_data.val / const.max_val y,
         graph_data.radius
    from graph_data, const),
gd2 as
 (select dt,
         round(nvl(lag(x) over(order by dt), x)) prev_x,
         round(x) x,
         round(nvl(lag(y) over(order by dt), y)) prev_y,
         round(y) y,
         radius
    from gd1)
/* header */
select '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' txt
  from dual
union all
select '<svg version="1.1" width="' || viewbox_width || '" height="' ||
       viewbox_height || '" viewBox="0 0 ' || viewbox_width || ' ' ||
       viewbox_height ||
       '" style="background:yellow" baseProfile="full" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ev="http://www.w3.org/2001/xml-events">'
  from const
union all
select '<title>Test graph</title>'
  from dual
union all
select '<desc>Test graph</desc>'
  from dual
union all
select '<rect width="' || viewbox_width || '" height="' || viewbox_height ||
       '" style="fill:white" />'
  from const
union all
/* vertical lines */
select '<line x1="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1))) || '" x2="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1))) || '" y1="' ||
       to_char(round(top_margin)) || '" y2="' ||
       to_char(round(viewbox_height - bottom_margin)) || '" style="stroke:' ||
       const.stroke_vertical_lines || '; stroke-width:' ||
       const.stroke_width_vertical_lines || '"/>'
  from const
connect by level <= num_vertical_lines
union all
/* dates under vertical lines */
select '<text x="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1) - x_dates_pad)) ||
       '" y="' ||
       to_char(round(viewbox_height - bottom_margin + y_dates_pad)) ||
       '" font-size="' || font_size_dates || '" fill="' || fill_dates || '">' ||
       to_char(min_dt + level - 1, 'yyyy-mm-dd') || '</text>'
  from const
connect by level <= num_vertical_lines
union all
/* horizontal lines */
select '<line x1="' || to_char(round(left_margin)) || '" x2="' ||
       to_char(round(viewbox_width - right_margin)) || '" y1="' ||
       to_char(round(top_margin +
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1))) || '" y2="' ||
       to_char(round(top_margin +
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1))) ||
       '" style="stroke:' || const.stroke_horizontal_lines ||
       '; stroke-width:' || const.stroke_width_horizontal_lines || '"/>'
  from const
connect by level <= num_horizontal_lines
union all
/* values near horizontal lines */
select '<text text-anchor="end" x="' ||
       to_char(round(left_margin - x_values_pad)) || '" y="' ||
       to_char(round(viewbox_height - bottom_margin -
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1) +
                     y_values_pad)) || '" font-size="' || font_size_values ||
       '" fill="' || fill_values || '">' ||
       to_char(round(max_val / (num_horizontal_lines - 1) * (level - 1), 2)) ||
       '</text>'
  from const
connect by level <= num_horizontal_lines
union all
/* circles */
select '<circle cx="' || to_char(gd2.x) || '" cy="' || to_char(gd2.y) ||
       '" r="' || gd2.radius || '" style="fill:' || const.fill_circles ||
       '"/>'
  from gd2, const
union all
/* graph data */
select '<line x1="' || to_char(gd2.prev_x) || '" x2="' || to_char(gd2.x) ||
       '" y1="' || to_char(gd2.prev_y) || '" y2="' || to_char(gd2.y) ||
       '" style="stroke:' || const.stroke_graph || '; stroke-width:' ||
       const.stroke_width_graph || '"/>'
  from gd2, const
union all
/* footer */
select '</svg>' from dual;

Hasil kueri dapat disimpan ke file dengan ekstensi * .svg dan dilihat di browser. Jika mau, Anda dapat menggunakan salah satu utilitas untuk mengonversinya ke format grafik lain, meletakkannya di halaman web aplikasi Anda, dll.

Gambar yang dihasilkan adalah:

Aplikasi Pencarian Metadata Oracle

Bayangkan mencoba menemukan sesuatu dalam kode sumber di Oracle dengan mencari informasi di beberapa server sekaligus. Ini tentang mencari melalui objek kamus data Oracle. Tempat kerja untuk pencarian adalah antarmuka web, di mana pemrogram pengguna memasukkan string pencarian dan memilih kotak centang di mana server Oracle untuk melakukan pencarian ini.

Mesin pencari web dapat mencari baris di objek server Oracle secara bersamaan di beberapa database bank yang berbeda. Misalnya, Anda dapat mencari:

• Oracle 61209, ?
• accounts ( .. database link)?
• , , ORA-20001 “ ”?
• IX_CLIENTID - SQL-?
• - ( .. database link) , , , ..?
• - - ? .
• Oracle ? , wm_concat Oracle. .
• - , , ? , Oracle sys_connect_by_path, regexp_instr push_subq.

Berdasarkan hasil pencarian, pengguna diberikan informasi tentang server mana dalam kode yang berfungsi, prosedur, paket, pemicu, tampilan, dll. menemukan hasil yang dibutuhkan.

Mari kita gambarkan bagaimana mesin pencari tersebut diterapkan.



Sisi klien tidak rumit. Antarmuka web menerima string pencarian yang dimasukkan oleh pengguna, daftar server yang akan dicari, dan login pengguna. Halaman web meneruskannya ke prosedur tersimpan Oracle di server penangan. Sejarah permintaan ke mesin pencari, mis. yang mengeksekusi permintaan yang dicatat untuk berjaga-jaga.



Setelah menerima permintaan pencarian, sisi server di server pencarian Oracle menjalankan beberapa prosedur dalam pekerjaan paralel yang memindai tampilan kamus data berikut pada link database di server Oracle yang dipilih untuk mencari string yang diperlukan: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsource, dba_scheduler_jobs , dba_views. Setiap prosedur, jika menemukan sesuatu, menulis apa yang ditemukannya ke tabel hasil pencarian (dengan ID permintaan pencarian yang sesuai).



Ketika semua prosedur pencarian telah selesai, bagian klien memberikan pengguna semua yang tertulis dalam tabel hasil pencarian dengan ID permintaan pencarian yang sesuai.

Tapi itu belum semuanya. Selain mencari di kamus data Oracle, pencarian di repositori Informatica PowerCenter juga disekrup ke mekanisme yang dijelaskan. Informatica PowerCenter adalah alat ETL populer yang digunakan oleh Sberbank untuk memuat berbagai informasi ke dalam gudang data. Informatica PowerCenter memiliki struktur repositori yang terbuka dan terdokumentasi dengan baik. Di repositori ini, dimungkinkan untuk mencari informasi dengan cara yang sama seperti di kamus data Oracle. Tabel dan bidang apa yang digunakan dalam kode unduhan yang dikembangkan dengan Informatica PowerCenter? Apa yang dapat ditemukan dalam transformasi port dan kueri SQL eksplisit? Semua informasi ini tersedia dalam struktur repositori dan dapat ditemukan. Untuk para ahli PowerCenter, saya akan menulis bahwa mesin pencari kami memindai lokasi repositori berikut untuk mencari pemetaan, sesi, atau alur kerja,berisi string pencarian di suatu tempat: sql override, atribut mapplet, port, definisi sumber dalam pemetaan, definisi sumber, definisi target dalam pemetaan, target_definitions, pemetaan, mapplet, alur kerja, worklet, sesi, perintah, port ekspresi, instance sesi, bidang definisi sumber, bidang definisi target, tugas email.

: , SberProfi DWH/BigData.



SberProfi DWH/BigData , Hadoop, Teradata, Oracle DB, GreenPlum, BI Qlik, SAP BO, Tableau .