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 0
Nov 292013
 

Tak si projíždím APEX a narazil jsem tam na funkcionalitu, která by se mi vcelku líbila v PL/SQL. Konkrétně vytvoření API package nad tabulkou (procedury na update, insert). Není problém si na to napsat script či ho někde sehnat, nicméně když už jsem to tam viděl.. tak mi napadlo, že by bylo fajn to umět provolat. Když jsem se podíval do packages pod uživatelem APEX, tak patrně zodpovědný za tuto funkcionalitu je package wwv_flow_generate_api. Který však obsahuje pouze jednu funkci (create_api), která rozhodně negeneruje kód, to byla ale jen specifikace balíku wwv_flow_generate_api. Když ovšem utilitou na unwrap kódu vezmu body, potkávám funkci, která se mi líbí:

FUNCTION GENERATE_CODE(P_APP_NAME VARCHAR2, P_TABLE_LIST TABLE_LIST_TYPE, P_OWNER VARCHAR2, P_TYPE VARCHAR2) RETURN SYS.DBMS_SQL.VARCHAR2A IS..

Čímž to svým způsobem přestává být zajímavé.  Ale zagooglil jsem jak vlastně takový unwrap tool funguje a narazil jsem na úžasnou přednášku, která se tím zabývá a to tak, že vcelku podrobně. Navíc odhaluje jak vlastně taková kompilace funguje, včetně toho kam se to ukládá do systémových tabulek Oracle atd.

Autor: Pete Finnigan
Zdroj: http://www.blackhat.com/presentations/bh-usa-06/BH-US-06-Finnigan.pdf
Download: BH-US-06-Finnigan

Výše uvedené PDF má na mé stupnici pejska 10 z 10 možných hébiček ;)

 Posted by at 06:22
Feb 132013
 

Nedočkavě sleduji tak to tedy bude s releasem Oracle Database 12c a kdy to tedy můžeme čekat ;). Minulý týden byly na internetu zprávy, že už je to skoro na spadnutí. Náhodou jsem však našel knihu New Features Oracle Database 12c, která sice neni ještě vydaná, ale na stránce rampamt pressu je seznam témat a jedním z nich je i “New BBED commands“.

BBED je tool, na který jsem ještě neměl moc času, ale je to jedna z utilit se kterou se dá pekelně vyřádit! BBED jeinterní tool Oracle sloužící k editaci databázových bloků. BBED umožňuje editovat hodnoty, smazat řádky pomocí editace datového bloku, přepočítat mu checksum ale také i obnovit řádky, – Oracle funguje podobně jako operační systém a po smazání řádku je řádek fyzicky pouze označen jako smazaný a přežívá do fyzického přepisu (update/insert jiných řádků, truncate, rebuild, shrink table..).

BBED je interní tool Oracle, takže do Oracle 10g  byl chráněn heslem (profláknutým a hardcodovaným..), od Oracle 11g bohužel chybí některé knihovny nutné pro spuštění tohoto toolu a je nutné je nakopírovat z Oracle 10g a snad právě pro to mi zaujalo téma “New BBED commands“,  neb to vypadá, že ani v novém Oracle nepřijdeme o BBED a bude možnost ho použít i když nepracujeme v Oracle Supportu, nebo alespoň já ne ;)

A nyní link na užasný dokument o BBED a jeden z nejvíce sexy dokumentů o Oracle:
Disassembling The Oracle Data Block

A narozdíl od profláknuté hlášky “do not try this at home”, tady platí pravý opak – doma a pouze doma.

 Posted by at 02:54
Sep 202012
 

Otázka na začátek – může databáze vracet jiné výsledky pro stejný select ale pro jeho jiné exekuční plány? Neměla by.., pokud je select správně napsaný, pokud se nemluví o bugu Oracle a pokud … a pokud? Pokud vím, tak v dokumentaci je jedna vyjímka, kde Oracle přímo píše, že dva stejné selecty s různým exekučním plánem mohou “legálně” vracet jiné hodnoty a to v případě poškozeného datového bloku v tabulce a nastaveném skipu “corrupted blocks” (DBMS_REAPIR) a dva selecty – jeden proti tabulce full scanem a druhý proti  indexu (bez “corrupted blocks”).

Dneska jsem se rozhodl Oracle trošku potrápit – a přinutit ho k nehezkému chování, tím že mu poruším odkazy v indexu a Oracle bude vracet jiné řádky přes index a jiné přes full scan. Takže go hébičky go!

Upozornění: Updatovat systémové tabulky pod SYSem “není doporučeno” a může (a dělá to) poškozovat databázi.

1) Nejprve založení nějaké testovací tabulky
create table invalid_index_test_tbl tablespace USERS as select rownum id_key, rownum my_value from dual connect by level<20000;

2) Nyní založení indexu
create index inx_index_unusable_text on invalid_index_test_tbl(id_key);

3) Alterování indexu do UNUSABLE stavu
alter index inx_index_unusable_text unusable;

4) Odmazání řádek
Je třeba odmazat nějaké řádky, abychom získali prostor na shirnk space a rozhodili pozici řádků vůči rowid uloženým v indexu:
delete from invalid_index_test_tbl where my_value<8000 or (my_value>12000 and my_value<15000)
commit;

5) Enable row movement a Shrink space
Povolíme fyzický přesun řádků (enable row movement) a přesuneme řádky na promazané místo, tzn “setřesení tabulky” (shrink space)
alter table invalid_index_test_tbl enable row movement;
alter table invalid_index_test_tbl shrink space;

6) Hledání co vlastně updatovat
Nejzajímavější část je najít, co je třeba vlastně updatovat, nejjednodušší je podívat se na definici ALL_INDEXES, ve které je vidět:
decode(bitand(i.property, 2), 2,
decode(i.type#, 9, decode(bitand(i.flags, 8),
8, 'INPROGRS', 'VALID'), 'N/A'),
decode(bitand(i.flags, 1), 1, 'UNUSABLE',
decode(bitand(i.flags, 8), 8, 'INRPOGRS',
'VALID')))

Podle definice ALL_INDEXES je to update do tabulky sys.ind$ a bitově sloupec FALGS, potenciálně sloupec property. Co třeba je vědět je číslo objektu, například přes  select * from sys.obj$ where name like upper(‘%inx_index_unusable_text%‘) . Moje číslo je 21988 a updatovat flags budu na 2050 (zjištěno z testovacího indexu,nemá cenu se počítat s bity)

7) Update sys.ind$
update sys.ind$  set flags=2050 where obj# in (21988);
commit;

8) Index je “validní” v systémových tabulkách Oracle, takže test:
select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=2
ORA-01502: index ‘SYS.INX_INDEX_UNUSABLE_TEXT’ or partition of such index is in unusable state
01502. 00000 – “index ‘%s.%s’ or partition of such index is in unusable state”
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition

Proč? Updatovat systémové tabulky pod SYSem by se nemělo, Oracle při tom dělá spoustu věcí (hlavně validacích, že..), které uživatel neudělá nebo udělat ani nemůže. Jednou z nich bude patrně invalidace stavu objektu v paměti, ale to naštětí lze řešit:

9) Čištění..
ALTER SYSTEM FLUSH SHARED_POOL;
Z mých zkušeností plyne, že je třeba flushnout shared_pool téměř vždy, když si hraji pod SYSem..

10) Výsledný pohled na selecty – jeden přes znásilněný index, druhý fullscannem:

Full vs Index

Full vs Index

Super, ne? Stejné selecty – jiný hint a jiný výsledek.

Vada na kráse? Tím jak byla shrinkuta tabulka jsme posunuly řádky a tabulka byla zmenšena – tedy index pro vyšší hodnoty (za předpokladu, že Oracle insertnul data z selectu za sebou – neni zaručeno) odkazuje někam do pryč.. tedy:

11) Fail select:

select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=15000;

S výsledkem (což je ta lepší varianta)
ORA-08103: object no longer exists
08103. 00000 – “object no longer exists”
*Cause: The object has been deleted by another user since the operation
began, or a prior incomplete recovery restored the database to
a point in time during the deletion of the object.
*Action: Delete the object if this is the result of an incomplete
recovery.

A

select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=10800;

Což je ta horší varianta:
(ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 – “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]“
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug – the first argument is the internal error number)

Vylepšení? Tabulku znovu roztáhnout – nainsertovat řádky a zase je smazat (???? pokud pujdou do stejné části datového souboru, jako byly předtím). Trefit to přesně na záznam tak tu máte úžasné chování ;) Další možností je updatovat segmenty indexu a udělat si vlastní “polotrucate” indexu a zkrátit ho.

Vždycky lítaj třísky, když si člověk hraje, nicméně vypadá to, že pak stačí rebuildnout index:

 alter index inx_index_unusable_text rebuild;

 K čemu je to dobré? K ničemu. Na stranu druhou – to dokazuje, že shrink space funguje a dokonce i jak se tam sypou řádky – o kolik je posunut index vůči řádkům…¨

—————-

A neodpustím si jednu věc ze života mimo Oracle pro dnešek:

Zlato ve tmě modře nesvítí, což je dost nepříjemný fyzikální constrain :-/

 Posted by at 23:37
Dec 282011
 

Kolegovi přišel požadavek na zjištění počtu transakcí, které probíhají nad tabulkou (potřeba vytvořit materializované view) od našich DBA. Trošku zváštní požadavek od DBA, nemyslíte? Proč by měli žádat vývojáře o tuto informaci, když si jí mohou zjistit sami? Nakonec se zjistilo, že DBA se původně ptali na to, jak velká bude submnožina v materializovaném view kvůli místu. Nechal jsem šéfa se zlobit, jak máme plnit požadavky, když námi přijmutý požadavek je úplně něco jiného než požadoval zadavatel a díval jsem z na to z pohledu programátora:

  • Počet transakcí nad tabulkou jde zjistit z Oracle Streams (pokud je to možné)
  • Z pohledu ALL_TAB_MODIFICATIONS

    SELECT INSERTS+UPDATES+DELETES FROM USER_TAB_MODIFICATIONS WHERE table_name='MY_TABLE';
    1271062015

    Co je podstatné, že ALL_TAB_MODIFICATIONS je definováno jako “last time statistics were gathered on the tables”. Tedy chce select ještě trošku učesat a zahrnout do něj ještě čas poslední analýzy tabulky:

    SELECT round(txs/(SELECT(SYSDATE-LAST_ANALYZED)*86400 FROM ALL_TABLES a WHERE a.TABLE_NAME=t.table_name and a.owner=t.table_owner),2) as "Tx/s" FROM(
    (SELECT SUM(INSERTS+UPDATES+DELETES) txs, MAX(table_name) table_name, MAX (table_owner) table_owner
    FROM ALL_TAB_MODIFICATIONS t WHERE t.table_name='MY_TABLE' and t.table_owner='AZOR')) t;


    Nevýhoda je zřejmá, od poslední analýzy mohlo uplynout spoustu hodin i dní a výsledek tedy neukazuje peak, který je nejzajímavější.
  • Pomocí Flashback version query
    Pokud je na to databáze správně nakonfigurovaná, pak je Flashback version query podle mne nejsilnější nástroj jak získat počet transakcí nad tabulkou, včetně rozpadnutí podle jednotlivých hodin.

    SELECT COUNT(*)/60 AS "Tx/s" FROM (
    SELECT id,versions_operation,rawtohex(versions_xid) xid, versions_starttime
    FROM MY_TABLE
    VERSIONS BETWEEN TIMESTAMP
    SYSTIMESTAMP - INTERVAL '60' MINUTE AND
    SYSTIMESTAMP
    WHERE versions_operation IS NOT NULL)


    Výhodou je možnost rozpadu po minutách/hodinách, vypsání pouze hodin v peaku, počet transakcí (nikoliv počet updatů/insertů)
  • Pomocí aplikační logiky  a Flashabacku – Siebel

    SELECT
    TO_NUMBER(SELECT SUM(MODIFICATION_NUM) FROM SIEBEL.S_ORDER)-
    (SELECT SUM(MODIFICATION_NUM) FROM SIEBEL.S_ORDER AS OF TIMESTAMP SYSDATE-1/24)
    FROM dual;

Způsobů by se asi dalo vymyslet více v závislosti na konfiguraci databáze nebo na úrovni aplikační logiky.

 Posted by at 23:02
Dec 072011
 

Dnes jsem si chtěl zkusit jestli je možné přidat sloupce do tabulky, nikoliv však ALTER TABLE t_rename_col ADD (B NUMBER) , ale jak je to zábavné – pomocí úprav systémových tabulek pod účtem SYS. Po chvilce pátrání v systémových pohledech, tabulkách a auditování jsem si řekl, že je to možná trošku velká výzva a že pro začátek začnu s přejmenováním, so hurá do toho :

1) Založení testovací tabulky:

CREATE TABLE t_rename_col (a NUMBER);

2) Všechny sloupce jsou v pohledu ALL_TAB_COLS (pod SYS), tedy se podívejme na to, co je pod tímto view:

SELECT text FROM all_views WHERE view_name='ALL_TAB_COLS' AND owner='SYS';
Vcelku nehezký select (který mi nyní ve 2:30 ráno odradil od pokusu přidání sloupce), který se odkazuje na vcelku známou tabulku : sys.col$

3) Záznamy sloupců jsou v řádcích tabulky podle unikátního ID sloupce – so, sloupec který hledáme bude mít nejvyšší obj# (nebo jedno z nejvyšších a název A)

SELECT * FROM sys.col$ ORDER BY obj# DESC;
110489

4) Máme sloupec, máme hlavní systémovou tabulku – so zbývá jen update:

UPDATE sys.col$ SET name='TEST' WHERE obj#=110489;
commit;

5) A nyní test:

desc t_add_col;
Name Null Type
---- ---- ------
A (NULL) NUMBER

Docela zklamání :/, nadruhou stranu – co mi zafungovalo:

ALTER SYSTEM FLUSH SHARED_POOL;

desc t_add_col;
Name Null Type
---- ---- ------
TEST (NULL) NUMBER

Stop nudnému ALTER TABLE t_rename_col RENAME COLUMN A TO TEST  ;)

 Posted by at 02:04
Nov 272011
 

Existuje spousta způsobů jak se vzdělávat v programování od pouhého čtení dokumentace přes školení až po to ten nejlepší způsob, kterým je psaní kódu a praxe. Další a velice efektivní způsob, jakým se vzdělávat a posouvat své znalosti dál je takzvané rýpání ;) – které spočivá v tom, že se daný systém snažím ze zadu pošťuchovat, trápit a zkouším co všechno si mohu dovolit a co všechno se dá vyzkoumat, “hacking” chcete-li.
A do rozsáhlých software, jako je databáze Oracle se dá rýpat opravdu hodně – otevřeli jste si někdy SGA (System Global Area) v hexaeditoru? Alterujete objekty pod účtem SYS? Hledáte v RAM nakešovanou sekvenci či blok? Snažíte se označit blok jako dirty zápisem do RAM?
Oracle Database Concept velice hezky popisuje, jak vypadá datový blok – hlavička + data. Takže jsem se rozhodl podrobit zkoumání datové soubory databáze v hexaeditoru. Jako nejjednodušší mi přišlo zkusit změnit SCN datového bloku (System Change Number), ale o tom někdy příště – muj dnešní tip je, jak přinutit Oracle, aby zapsal změny z redologu do datových souborů, což typicky nemá žádné větší použití, ale právě při zkoumání datových souborů a změn v nich, je to velice šikovný příkaz:

ALTER SYSTEM CHECKPOINT;

Což udělá následující:

  • Zapíše do datových souborů z buffer cache a synchronizuje je
  • Zapíše SCN do datových souborů
  • Zapíše SCN do control souborů

Pokud neni checkpoint vynucen, je prováděn po LOG_CHECKPOINT_INTERVAL , pri každém switchnutí redo logů nebo po LOG_CHECKPOINT_TIMEOUT (defalutně 1800).

 Posted by at 11:45
Oct 122011
 

Index je fantastická věc, pokud je správně použit, ale vcelku rychle se naopak může stát velkou brzdou při insertech, updatech i mazání. Mimo nutného udržování kondice indexů je občas dobré udělat nějakou analýzu využívání či nevyužívání indexů a přesvěčit se, zda stále benefit (sql selecty) převyšuje zpomalení (DML) a ostatní neduhy (maintenence, místo..), které index přinese. Oracle nám k tomu nabízí šikovný příkaz, který dokáže monitorovat indexy a  odhalit ty, které již nejsou používané a nebo ty, které nejsou používané, ale měli by být -Oracle je však nepoužívá – špatné exekuční plány, špatné outline, špatné hinty, neaktuální statistiky..

Hurá na ukázku monitorování indexů:

1) Nejprve si vytvoříme tabulku

CREATE TABLE tbl_index_test AS SELECT 1 myid,MOD(ROWNUM,100) myinx FROM DUAL CONNECT BY ROWNUM<200000;

2) Vytvoříme index

CREATE INDEX inx_test ON tbl_index_test(myinx) ONLINE;

3) Zapneme monitoring indexu inx_text

ALTER INDEX inx_test MONITORING USAGE;

4) Nyní vyzkoušíme select nad tabulkou bez použítí indexu

SELECT * FROM tbl_index_test;

5) Průběžný výsledek zapnutého monitorování lze nalést v v$object_usage pohledu

SELECT * FROM V$OBJECT_USAGE

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
INX_TEST TBL_INDEX_TEST YES NO 10/12/2011 22:36:11 null

6) Nyní zkusíme náš index použít
SELECT /*+ index(t,inx_test) */ * FROM tbl_index_test t WHERE myinx=2;

A Oracle již ukazuje, že byl použit:

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
INX_TEST TBL_INDEX_TEST YES YES 10/12/2011 22:36:11 null

Po analýze je samozřejmě nutné monitoring indexu vypnout – nebere to sice moc zdrojů, ale je dobré šetřit všude, kde se dá:

ALTER INDEX inx_test NOMONITORING USAGE;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
INX_TEST TBL_INDEX_TEST YES YES 10/12/2011 22:36:11 10/12/2011 22:57:45

Že se v v$object_usage index neobjeví, ještě nutně neznamená, že není třeba – je nutné počkat nějaký čas – alespoň jeden buisness cyklus, než lze přemýšlet o dropnutí indexu – může být využíván jednou za měsíc, za to může být pekelně důležitý – na to pozor. Je dobré také prohledat pohledy typu ALL_SOURCE na indexovaný sloupec, pokud je to možné -  čert ani člověk nikdy neví ;)

 Posted by at 21:13