Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
May 252013
 

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:
AWR Report a obarvení sql

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,

 Posted by at 08:05
Feb 072013
 

Jak moc důležité je komentovat kód ví asi každý z nás – stačí se za pár měsícu vrátit ke svému programu či scriptu a člověk neví, která bije, to je prostě fakt – takže soubor tipů pro Oracle, kde je možné vložit komentář a vecpat tam svůj informační text.

1) Komentář nad tabulkou
COMMENT ON TABLE my_table IS 'azorova tabulka';
Komentář nad tabulkou by tak nějak měl být samozřejmostí, že? Mimo komentáře je také vhodný název tabulky, viděl jsem několik standardů jak se v systémech pojmenovávaly tabulky a objekty a žádný z nich nebyl vysloveně špatný, důležité je především nějaký mít. Osobně preferuji mimo báječného jména také nějaký sufix či prefix, který mi navíc podá další informaci. Jakou? Viděl jsem systémy, kde se do prefixu dávala informace, že je to tabulka (T_ ,TBL_), kde se tam dávalo schéma (AZOR_), kde se dávala další dodatečná informace (_CX,_SX) atd.
Takže mimo komentáře a hezkého názvu tabulky doporučiji sufix/prefix, který podá nějakou další informaci – taková která je pro daný systém nejlepší (pokud používáte aliasy hodí se například i to schéma).

2) Komentář nad sloupcem
COMMENT ON COLUMN my_table.my_column IS 'primární klíč, výrobní číslo';
Podobně jako nad tabulkou může být komentář i nad sloupcem. A i zde si myslim, že do 30 znaků se mimo hezkého jména i vejde nějaká dodatečná informace, například prefix PK_, pokud se jedná o primární klíč či _FK. Alternativně se hodně používá změna pořadí – ID_TABLE je primární klíč, zatímco TABLE_ID je cizí klíč. A mimo informace o klíči si občas vecpe nikdo do názvu jestli to má constraint či nějakou další informaci (_X jako má Siebel custom sloupce)

3) Nastavení SESSION – module/action pomocí dbms_aplication_info.
Oracle nabízí (jednoduchý a krátký balík) package dbms_aplication_info, který umožňuje označit si session a nastavit jim modul/action. Použití je následující:

begin
dbms_application_info.set_module('STAHOVANI_DAT','cisteni_temporary_tabulek');
end;

Což nastaví hodnoty modul a action pro aktuální session, které jsou pak vidět například v$session:

select module,action,t.* from v$session t where user#=sys_context('USERENV', 'SESSION_USERID');

Session info setnuté pomocí dbms_aplication_info

Session info setnuté pomocí dbms_aplication_info

Co je nepříjemné je omezená délka – 48 znaků pro modul, 32 pro action. A co tam patří? Ideálně package či procka, která v dané session běží a pokud to jde, dávám tám i informaci o % dokončení.

4) Komentování kódu a PLSQLDOC
Popisovat celé PLSQL_DOC by asi nemělo smysl, takže pouze link: PLSQL_DOC s informací, že utilitka trošku zastarává a jsou novější náhrady (v SQL Developeru například), nicméně stále šikovná utilitka – z headu balíku to vygeneruje něco, co vypadá jako klasické JAVADOC.

5) Pojmenovní transakce
 a) stará verze
COMMIT COMMENT 'moje transakce, klidne rollbacknete';
b) nová verze
SET TRANSACTION NAME 'moje transakce';
To je asi featura, která nikdo z nás moc nepouživá. Bohužel. Tak alespoň na distribuovaných databází by to chtělo.

6) Sledování selectu s komentářem
Na internetu jsou často vidět “pojemenování” selectu pomocí komentáře za slovem select, aby se lépe identifikoval v pohledech:

select /* azoruv_select */ from all_tables where table_name like..

Což se přiznám moc nepouživám – hodně to nahrazuje dbms_aplication_info.

7) Propis do V$SESSION_LONGOPS
Package dbms_aplication_info má mimo set_module ještě jednu zajímavou funkci set_session_longops, která umožní založit záznam do V$SESSION_LONGOPS, příklad má Oracle přímo v dokumentaci k package DBMS_APLICATION_INFO.

Edit: Na základě komentáře ke článku (za který děkuji) jsem opravil slovo surfix na sufix - a někdo by to měl opravit i ve všech packages a veškeré dokumentaci, kterou jsem kdy udělal ;), protože to nebyl překlep, ale celý život to píšu špatně, wow.

 Posted by at 19:09
Aug 142011
 

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ů.
 Posted by at 20:00
May 082011
 

Nedávno jsem postřehl na builder.cz vlákno, kde programátoři řešili jak efektivně získat nejmenší (největší) hodnotu ze 3 čísel. Podobnou úlohu jsem také řešil asi před rokem a zjistil jsem, že vzásadě není žádné úplně uspokojové řešení nebo funkce, která by v Oracle dokázala jednoduše vrátit nejmenší (největší)  hodnotu z několika čísel. Jsou však nějaké možnosti, jak toho docílit

 

1 ) Pomocí MIN a MAX z SQL a skalráního subSELECTU, prasárna

2) Pomocí vlastního CASE, takto se to typicky i dělá

3) Vlastní funkce v PL/SQL

4) Použití funkce LEAST/GREATEST.

5) Jiné možnosti…

 

Na první pohled je jednoznačný favorit funkce LEAST (GREATEST), což je funkce které poskytnete  libovolný počet parametrů  a ona vám vrátí tu nejmenší (největší) hodnotu.  Zdá  se, že stačí zavolat jen některou z těchto dvou funkcí a získáme přesně to co chceme, bohužel – funkce LEAST a GREATEST zachází z matematického hlediska správně s hodnotou NULL ale asi ne tak, jak by se nám to v PL/SQL programch šiklo. Inu, co s tím – v závislosti na datech si lze pomoci některou funkcí, která pracuje s NVL – NVL, NVL2. V nejhorším případě musíme u každé vstupní proměnné použít COALESCE funkci a do té ještě vložit všechny parametry a ještě ve správném pořadí,  tedy:
select greatest(coalesce(a,b,c),coalesce(b,c,a),coalesce(c,b,a)) from tbl_s_abc_cols;

Pokud nám však nevadí jak se obě funkce chovají k NULL hodnotám, pak je nejmenší (největší z n) opravdu jednoduché :

select greatest(1,3,9,0,300) from dual;

 Posted by at 11:51
Mar 192011
 

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.

 Posted by at 20:54
Mar 182011
 

Balík DBMS_FLASHBACK je s námi již od Oracle9iR2 a firma Oracle jej s každou další verzí vylepšuje a přidává nové funkcionality. DBMS_FLASHBACK je balík, který nám umožní získat původní data a to i v případě, že jsme spustili nějaký ten DML a s hrůzou jsme zjistili, že jsme to rozhodně commitnout nechtěli ;) Použití si určitě najde každý – pohled na data, která existovala v tabulce (a nejen v tabulce, je umožněn i FLASHBACK celé databáze) před nějakou změnou si najde spoustu využití. Je až pozoruhodné kolik mých kolegů tuto možnost do nedávna ještě neznalo. Přitom znalost této funkčnosti programátorem ušetří spoustu vztekání a často i problémů. Další důvod proč mít tuto znalost je často omezená doba platnosti ;) Cirman je věčný UNDO logy nemusí být nemusí.

DBMS_FLASHBACK toho umí opravdu hodně, takový rychlotip je select do tabulky na historická data. Nejprve je třeba vědět v jaké verzi/kdy chcete vidět data. Určení času/verze lze dvěma způsoby: timestamp, což je ta horší volba protože je nepřesná. Druhá a lepší volba je určení času/verze pomocí SCN (System Change Number), což je naprosto přesné číslo, kterým je určena každá změna  v databázi, narozdíl od timestamp, který může obsahovat i více změn + perioda (3s) s jakou se zaznamenává.

Zjištění SCN před změnou.

select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
SCN=349998023

Omylem smažeme tabulku.

delete MY_TABLE;
commit;

A nyní pohled na historická data před tímto osudným smazáním:


select * from MY_TABLE as of scn 349998023;

O proti Oracle 9iR2 a 10g jde 11g mnohem dále a umožňuje v FlashBackem vracet data, dotazovat se celé databáze, vráti relevantní transakce, vypisovat SCN atd..

Co je důležité vědět je, že historický pohlede je čten u UNDOlogu a možnost obnovy silně závisí na nastavení databáze a to konkrétně parametru UNDO_RETENTION, který v minutách (výchozích je pouze 15 !) vaší možnost obovy a případně RETENTION GUARANTEE parametr, který to zaručuje.

Commitly jste něco, co jste němeli? Použijte DBMS_FLASHBACK - jediné co se může stát je obdržení chyby ORA-08180 a pocit ještě větší flustrace ;)

 Posted by at 19:55
Mar 162011
 

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” ;)

 Posted by at 01:01