PL/SQL programátor už NEbloguje – vývoj aplikací pod databází Oracle

Při hledání jednoho objektu v all_objects jsem narazil na mnou neznámé view DBA_HIST_COLORED_SQL. Obarvené sql? Neznal jsem a vypadalo to dost zajímavě. Takže jsem otevřel dokumentaci, abych se s view blíže seznámil a dokumentace říká: View se váže na funkci AWR a možnost reportovat nejen TOP SQL, ale také ty, které si označíme (“obarvíme”) pomocí funkce DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL.

Zní docela fajn, AWR sbírá jen TOP SQL a tohle je možnost jak do AWR dostat SQL, které tam mít chceme, ale není v TOP.

Takže go, hébičky go, jdem to zkusit!

Nejprve nějaký dotaz, který bude jednoduchý, tak aby nebyl v TOP SQL a bylo na něm vidět obarvení:

select max(dummy) as from dual;

Pak zjistit jeho sql_id:

select * from v$sql s where s.sql_text like '%max(dummy)%'

Což je : ’1twnyk3cacmfw’. A přidat do sledovaných sql_id pro AWR report:

begin
dbms_workload_repository.add_colored_sql('1twnyk3cacmfw');
end;

A test:

Tak to by bylo přidání mezi obarvená sql a nyní sebrat snapshot, spustit sql, sebrat snapshot a zjistit jestli je opravdu reportované.

declare
i int;
begin
i:=dbms_workload_repository.create_snapshot;
dbms_output.put_line('ID='||to_char(i));
end;

Spustit sql:

select max(dummy) as from dual;

A znovu snapshot

declare
i int;
begin
i:=dbms_workload_repository.create_snapshot;
dbms_output.put_line('ID='||to_char(i));
end;

A nyní ověření, že i sql, které není zcela určitě v TOP se objeví v AWR reportu:

select * from table(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_TEXT(
l_dbid=>848445615,
l_inst_num=>1,
l_bid=>893,
l_eid=>894,
l_sqlid=>'1twnyk3cacmfw'));


WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL8          848445615 orcl8               1 24-Kvě-13 13:17 11.2.0.1.0  NO 

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:       893 25-Kvě-13 09:12:13        40       3.3
  End Snap:       894 25-Kvě-13 09:12:41        37       3.5
   Elapsed:                0.47 (mins)
   DB Time:                0.02 (mins)

SQL Summary                               DB/Inst: ORCL8/orcl8  Snaps: 893-894

                Elapsed
   SQL Id      Time (ms)
------------- ----------
1twnyk3cacmfw          0
Module: SQL Developer
select max(dummy) as from dual

          -------------------------------------------------------------       

SQL ID: 1twnyk3cacmfw                     DB/Inst: ORCL8/orcl8  Snaps: 893-894
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> select max(dummy) as from dual

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   2522405774                      0             1           894            894
          -------------------------------------------------------------       

Plan 1(PHV: 2522405774)
----------------------- 

Plan Statistics                           DB/Inst: ORCL8/orcl8  Snaps: 893-894
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                                 0            0.4     0.0
CPU Time (ms)                                     0            0.0     0.0
Executions                                        1            N/A     N/A
Buffer Gets                                       3            3.0     0.1
Disk Reads                                        0            0.0     0.0
Parse Calls                                       1            1.0     0.4
Rows                                              1            1.0     N/A
User I/O Wait Time (ms)                           0            N/A     N/A
Cluster Wait Time (ms)                            0            N/A     N/A
Application Wait Time (ms)                        0            N/A     N/A
Concurrency Wait Time (ms)                        0            N/A     N/A
Invalidations                                     0            N/A     N/A
Version Count                                     1            N/A     N/A
Sharable Mem(KB)                                 14            N/A     N/A
          -------------------------------------------------------------       

Execution Plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Takže pro případ, že je třeba reportovat sql v AWR, které jsou pro nás zajímavé, ale nejsou v TOP:
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL,

Tip: Víte,  jaký je rozdíl v alokaci paměti při následujících deklarací:

DECLARE
mala_promernna VARCHAR2(200):='a';
stredni_promenna VARCHAR2(3999):='aa';
velka_promenna VARCHAR2(4000):='aaaa';
extra_velka:promenna VARCHAR2(4001):='aaaa'
extremne_velka_promenna VARCHAR2(32768):='aaaaa'
..


Možná se to nezdá, ale je signfikantní rozdíl mezi tím jestli proměnnou pl/sql programátor deklaruje jako VARCHAR2(3999) nebo VARCHAR2(4000). Oracle pracuje při deklaraci VARCHAR2 následujícím způsobem:

• Pokud je proměnná menší než 4000, vymezí Oracle při kompilaci – celé místo a tedy 4000 bytů. To znamená, že mala_promenna a stredni_promenna si vezmou obě 4000bytů paměti a alokována bude při kompilaci.

• Pokud je proměnná rovna nebo větší než 4000bytů pak Oracle alokuje paměť až v run time a to přesně podle potřebné velikosti. A tedy velka_promenna si vezme 3 byty, extra_velka_promenna si vezme 4 byty a maximum si vezme extremne_velka_promenna – 5 bytů.

Oracle nabízí již od verze 10g možnost updatovat a insertovat data přímo do databázového pohledu. Osobně jsem rád za každou další funkcionalitu a každou další možnost, kterou Oracle databáze umožňuje ;)  Možnost updatování a insertování přímo do pohledu jsem snad ještě nikdy nevyužil a důvodů je hned několik. Například, když hledáte v ALL_SOURCE tabulce všechny scripty/balíky, které insertují “záhadně” data do nějaké tabulky, tak scripty, které insertují/updatují data přímo přes view musíme vzít v potaz také a tedy i delší hledání ale i spoustu vztekání, pokud si na takovouhle možnost nevzpomenete ;).

Pohledy, které lze updatovat přímo (tzn. bez nutnosti napsání triggeru INSTEAD OF) jsou však dost strikně omezené, select nesmí obsahovat :

• Žádnou množinovou operaci (MINUS,INTERSECT, UNION)
• DISTINCT
• Agregační funkce (COUNT, MAX..)
• GROUP BY
• ORDER BY (podle manuálu, Oracle 11g to však pustí)
• CONNECT BY
• START WITH
• JOIN
• SUBSELECT v select listu (první část selectu s výběrem sloupcu)

Jestli je VIEW updatovatelné nebo není lze zjistit v pohledech _VIEWS tedy USER_VIEWS,  ALL_VIEWS, DBA_VIEWS.

Od Oracle 11g přibyla nová nastavení na úrovni session i systému. Jedním z nich je i velice zajímavý DDL_LOCK_TIMOUT, který udává jak dlouho bude čekat DDL příkaz ve frontě zámků (potřeba exlusive lock) na svou šanci dostat se k modifikované tabulce/objektu. Hodnota je uvedena v sekundách a je celočíslená. Možný rozsah je od 0, který je adekvátní NOWAITu do 1 000 000, což je hodnota, kdy bude čekat DDL příkaz nekonečně dlouho než se dostane na řadu k objektu.

Použití je následující :
ALTER SESSION SET ddl_lock_timeout = 10;

Tímto alterem lze na úrovni session či systému řídit kdy a jestli vůbec obdržíme chybovou hlášku : “ORA-00054: resource busy”