Kas tas ir?
Datu vārdnīca ir kopējs apzīmējums dažādām sistēmas iekšējām tabulām un skatījumiem, kas satur Oracle DBVS nepieciešamo informāciju tās darbināšanai. Kā Jūs domājat, kur tiek glabāta informācija par jaunu izveidotu tabulu, skatījumu, tabulai pieliktu kolonu vai ierobežojumu? Pareizi uzminējāt – tabulās. Tikai šoreiz tās ir sistēmas iekšējās tabulas, tā saucamie metadati jeb dati par datiem. Līdzīgi ir ne tikai attiecībā uz mūsu izveidotajiem permanentajiem objektiem datubāzē, bet arī ar krietni īslaicīgākajām lietām – sesijām, izpildītajiem SQL teikumiem, statistiku, kādas darbības un cik esam veikuši sesijas laikā – tas viss arī ir atrodams datu vārdnīcā.
Datu vārdnīcas virsbūve
Jebkuram cilvēkam, kas domā kaut ko nopietni darīt Oracle DBVS, būtu vismaz konceptuāli jāzin Oracle datu vārdnīcas pamatlietas ļoti vienkārša iemesla dēļ – tas ļoti bieži atvieglo dzīvi. Tātad datu vārdnīcas redzamākā daļa pārsvarā sastāv no statiskajiem un dinamiskajiem skatījumiem. Statiskajos skatījumos ir infomācija par Oracle datubāzē esošajiem objektiem – lietotājiem, tabulām, to kolonām, skatījumiem, trigeriem, datu failiem utt. Lielākajai daļai statiskajiem skatījumiem ir trīs varianti, kuru struktūra praktiski neatšķiras – ALL* un DBA* skatījumiem nāk klāt kolona owner, bet pārējās kolonas ir vienādas visiem variantiem.
- Tādi, kas satur informāciju tikai par lietotāja shēmā esošajiem objektiem – sākas ar USER, piemēram, USER_OBJECTS, USER_TABLES, USER_VIEWS. Pieejami jebkuram sistēmas lietotājam.
- Tādi, kas satur informāciju par lietotāja shēmā esošajiem objektiem un arī citās shēmās esošajiem objektiem, uz kuriem lietotājam ir piešķirtas tiesības – sākas ar ALL, piemēram, ALL_OBJECTS, ALL_TABLES, ALL_VIEWS. Pieejami jebkuram sistēmas lietotājam.
- Tādi, kas satur informāciju par visiem Oracle datubāzē esošajiem objektiem – sākas ar DBA, piemēram, DBA_OBJECTS, DBA_TABLES, DBA_VIEWS. Pieejami tikai privileģētiem lietotājiem ar speciālām tiesībām.
Dažiem statiskajiem skatījumiem nav kāda no variantiem. Tas ir tiem, kuros informācija ir par sistēmas līmeņa objektiem, piemēram, lomām (tikai DBA_ROLES), direktorijām (tikai ALL/DBA_DIRECTORIES). Daži no biežāk izmantotajiem skatījumiem varētu būt:
- USER/ALL/DBA_OBJECTS – satur informāciju par visiem objektiem datubāzē. Kolona object_type norāda kāda veida objekts tas ir, savukārt object_name ir objekta nosaukums.
- USER/ALL/DBA_TABLES – satur informāciju par visām tabulām datubāzē. Kolona num_rows nenozīmē aktuālo ierakstu skaitu, tas ir ierakstu skaits, kāds tika izrēķināts pēdējoreiz vācot statistikas un var būt galīgi aplams šajā brīdī.
- USER/ALL/DBA_SOURCE – satur informāciju par visām procedurālajām vienībām (izņemot trigerus) datubāzē, procedūrām, pakotnēm, funkcijām, tipiem.
- USER/ALL/DBA_TRIGGERS – satur informāciju par trigeriem datubāzē.
- USER/ALL/DBA_CONSTRAINTS – satur informāciju par ierobežojumiem datubāzē. Kolona constraint_type norāda kāda veida ierobežojums tas ir.
- USER/ALL/DBA_INDEXES – indeksu uzskaitījums.
- USER/DBA_RECYCLEBIN – nomesto (drop) objektu krātuve. Patiesībā sākot ar 10 g versiju vienkāršs DROP TABLE SQL teikums fiziski tabulu vienkārši pārsauc un pārsauktā tabula parādās šai skatījumā.
- USER/ALL/DBA_SEQUENCES – informācija par sekvencēm (objektiem, kas ģenerē unikālus skaitļus). Kolona last_number nenozīmē kāds skaitlis tika paņemts pēdējoreiz!!! Tas rāda pirmo skaitli, kas nav vēl iekešots (cached).
- USER/ALL/DBA_TAB_COLUMNS – dažādu objektu kolonas, ne tikai tabulu, kā varētu spriest pēc nosaukuma, bet arī, piemēram, skatījumu un klāsteru.
- USER/ALL/DBA_SYS_PRIVS, USER/ALL/DBA_TAB_PRIVS_MADE, USER/ALL/DBA_TAB_PRIVS_RECD – attiecīgi saņemtās sistēmas tiesības un iedotās vai saņemtās objektu tiesības.
Otra lielā daļa ir dinamiskie skatījumi, kas satur statistisku vai detalizētu informāciju par Oracle datubāzes stāvokli. Šo skatījumu nosaukumi pamatā sākas ar V$. Daži no tiem ir piejami visiem lietotājiem (piemēram V$VERSION), daži tikai privileģētiem lietotājiem, piemēram, V$SESSION. Daži no interesantākajiem skatījumiem varētu būt:
- v$instance – pamatinformācija par instanci.
- v$lock – esošās bloķēšanas un pieprasījumi pēc bloķēšanām (lock).
- v$mystat – tekošās sesijas statistikas rādītāji kopš pieslēgšanās brīža. Lai uzzinātu statistiku nosaukumus, jākombinē ar v$statname.
- v$nls_parameters – tekošās sesijas nls (national language settings, valodas un citi lokālie uzstādījumi) parametru vērtīnbas.
- v$segment_statistics – tabulu, indeksu apkopojošā statistika kopš instances startēšanas.
- v$session – visas instances sesijas.
- v$session_wait – katras sesijas tekošais vai pēdējais gaidīšanas notikums.
- v$sesstat – visu sesiju statistikas kopš to pieslēgšanās.
- v$sql – SQL teikumi, kas pašlaik ir kopējā SQL atmiņā (iekešoti).
- v$sql_plan – reālais sql teikuma izpildes plāns.
- v$systat – visas instances agregētās statistikas kopš instances startēšanas brīža.
- v$transaction – visas atvērtās tranzakcijas.
- v$version – informācija par datubāzes versiju.
Ar ko sākt?
Ar šo rakstu, protams, ar ko gan citu? 🙂 Bet papildus informāciju var meklēt divos veidos:
- atrast visu dokumentācijā – Reference satur informāciju par visiem statiskajiem un dinamiskajiem skatījumiem, tai skaitā visu kolonu aprakstu.
- rakt pašam. Lai to darītu ir nepieciešams atcerēties tikai vienu vārdu – DICT. Tas ir sinonīms prieks DICTIONARY, kas ir datu vārdnīcas skatījums, kas satur visus datu vārdnīcas skatījumus – foršs izskaidrojums, vai ne? 😉
Tātad skatamies kā izskatās DICT un ko tu var redzēt:
Piemērs 1. Dict (dictionary) saturs.
SQL> desc dict Name Null? Type --------------------------------- -------- -------------- TABLE_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000) SQL> SELECT * FROM dict WHERE table_name = 'USER_TABLES'; TABLE_NAME ------------------------------ COMMENTS ----------------------------------------------- USER_TABLES Description of the user's own relational tables
Datu vārdnīcas “iekšas” – statiskie skatījumi
Augstāk es stāstīju, ka viss Oraclē glabājas tabulās, bet kur tad tās ir? Visu laiku ir runa tikai par skatījumiem. Tad jāsāk rakt dziļāk.
Privileģētam lietotājam izpildam vaicājumu pirms tam uzstādot SQL*Plus autotrace komandu. Neinteresantās kolonas ir izdzēstas.
Piemērs 2. Skatījuma USER_TABLES izpildes plāns.
SQL> set autot traceonly explain SQL> select * from user_tables; Execution Plan ---------------------------------------------------- Plan hash value: 1735466533 ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | |* 1 | HASH JOIN RIGHT OUTER | | | 2 | TABLE ACCESS FULL | SEG$ | |* 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL | TS$ | |* 5 | HASH JOIN RIGHT OUTER | | | 6 | TABLE ACCESS FULL | USER$ | | 7 | NESTED LOOPS OUTER | | |* 8 | HASH JOIN OUTER | | | 9 | NESTED LOOPS | | | 10 | MERGE JOIN CARTESIAN | | |* 11 | HASH JOIN | | |* 12 | FIXED TABLE FULL | X$KSPPI | | 13 | FIXED TABLE FULL | X$KSPPCV | | 14 | BUFFER SORT | | |* 15 | TABLE ACCESS FULL | OBJ$ | |* 16 | TABLE ACCESS CLUSTER | TAB$ | |* 17 | INDEX UNIQUE SCAN | I_OBJ# | | 18 | TABLE ACCESS FULL | OBJ$ | | 19 | TABLE ACCESS BY INDEX ROWID| OBJ$ | |* 20 | INDEX UNIQUE SCAN | I_OBJ1 | ----------------------------------------------------
Redzam, ka izpildes plānā parādas dažādas dīvainas lietas. Tātad OBJ$, TAB$, USER$, TS$, SEG$ ir iekšējās (internal) tabulas SYS shēmā, kur, piemēram, tabulā OBJ$ glabājas visi objekti, bet tabulā USER$ – visi lietotāji. Nosaukumus, kas sākas ar X$ uz brīdi atstāsim malā. Savukārt, lai dabūtu šī skatījuma SQL teikumu (jo skatījums nav nekas vairāk, kā SQL teikums ar nosaukumu datubāzē) – atkal jāmeklē datu vārdnīcā.
Piemērs 3. Skatījuma USER_TABLES definīcija.
SQL> set long 100000000
SQL> SELECT text FROM dba_views WHERE view_name = 'USER_TABLES';
TEXT
------------------------------------------------------------------
select o.name, decode(bitand(t.property, 2151678048), 0,
ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
[...]
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv('SCHEMAID')
and o.obj# = t.obj#
[...]
Lūk arī FROM klauzā redzam no kurienes tas viss nāk un kā tiek iegūts.
Datu vārdnīcas “iekšas” – dinamiskie skatījumi
Šeit ir mazliet sarežģītāk. Iekš DBA_VIEWS neko jēdzīgu Jūs neatradīsiet. Jāskatās ir skatījumā v$fixed_view_definition.
Piemērs 4. Skatījuma V$SESSION definīcija.
SQL> desc v$fixed_view_definition
Name Null? Type
----------------------- -------- ----------------
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
SQL> SELECT * FROM v$fixed_view_definition
2 WHERE view_name = 'V$SESSION';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------
V$SESSION
select SADDR , SID , SERIAL# , AUDSID , PADDR ,
[..] from GV$SESSION where
inst_id = USERENV('Instance')
Kā redzat viss tiek atlasīts no gv$session ar where nosacījumu. Tāda definīcija normāli būs visiem V$ skatījumiem. Tātad gv$ skatījumi satur globālo informāciju pa visām instancēm – atceramies, ka Oracle RAC vidē var būt vairākas instances uz vienu datubāzi. V$ skatījumos, savukārt, izmantojot nosacījumu inst_id = USERENV(‘Instance’) tiek atlasīti tikai tekošās instances dati. Tātad nākošais solis skatamies, kas tad slēpjas gv$ vēderā?
Piemērs 5. Skatījuma GV$SESSION definīcija.
SQL> SELECT * FROM v$fixed_view_definition 2 WHERE view_name = 'GV$SESSION'; VIEW_NAME ------------------------------ VIEW_DEFINITION -------------------------------------------------- GV$SESSION select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuuds es,s.ksusepro,s.ksuudlui,s.ksuudlna,s.ksuudoct, [...] suseflg2,128),128,'TRUE','FALSE')from x$ksuse s, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand( s.ksuseflg,1)!=0 and s.ksuseopc=e.indx
Kā redzat šeit viss kļūst aizvien nesaprotamāk, bet from klauzā parādās tādi brīnumi kā x$ksuse un x$ksled, kas ir tā saucamās fiksētās tabulas (fixed tables) – patiesībā atmiņas struktūras, kam tabulas izskats ir tikai ērts interfeiss, kā tikt klāt to datiem. Šeit nosaukumi ja kļūst ļoti akronīmiski – ir hipotēze, ka x$ksuse nozīmē kernel service user session un x$ksled kernel service (lock/latch?) event definition.
Lietas, kas jāatceras par datu vārdnīcu
- No datu vārdnīcas var tikai lasīt. Tajā nedrīkst tieši veikt nekādas izmaiņas (fiksētajās tabulās Jums to vienkārši neļaus), citādi tam var būt pilnīgi neprognozējamas sekas, ieskaitot neglābjamu Oracle datu bāzes sabeigšanu.
- Fiksēto tabulu lasīšana (jo sevišķi to kombinēšana) negarantē konsistentu skatu uz datiem, tāpēc var gadīties, ka kādi no ierakstiem jau vairs nav spēkā, kad esat beidzis lasīt visus, vai arī mēģinat sakombinēt datus ar citu X$ tabulu.
- Jabūt uzmanīgiem ar lasīšanu no dinamiskajiem skatījumiem, jo to lasīšana var būt resursietilpīgs un, kas vēl nepatīkamāk – bloķējošs pasākums, piemēram, lai nolasītu kādu atmiņas struktūru, ir jābloķē (šeit lūk parādās latch) visas izmaiņas tajā uz lasīšanas laiku. Pat, ja šis brīdis šķiet mazs, daudzlietotāju vidē tas var ļoti nepatīkami ietekmēt citu lietotāju darbu.
- v$, gv$ un x$ fiksēto tabulu datus lasīt var tikai lietotājs SYS. Lai atsevišķi piešķirtu lasīšanas tiesības ir jāizmanto skatījumi, kas saucās v_$ un gv_$ (protams, var izmantot arī iebūvētās lomas), bet uz x$ tabulām tieši tas vispār nav iespējams.
Daži datu vārdnīcas vaicājumu piemēri
Piemērs 6. Datubāzes versija.
SQL> SELECT * FROM v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
Piemērs 7. Visas tabulas mana lietotāja shēmā.
SQL> SELECT table_name FROM user_tables; TABLE_NAME ------------------------------ SOURCE_PARENT SOURCE_CHILD DEST_PARENT PERSON_SOURCE
Piemērs 8. Tabulas PERSON_SOURCE kolonas.
SQL> SELECT column_name, data_type 2 FROM user_tab_columns 3 WHERE table_name = 'PERSON_SOURCE'; COLUMN_NAME DATA_TYPE ------------------------------ ----------- SRC_ID NUMBER SRC_PK VARCHAR2 SRC_NAME VARCHAR2 SRC_SURNAME VARCHAR2
Piemērs 9. Sesijas, kas pieslēgušās datubāzei.
SQL> SELECT sid, serial#, program 2 FROM v$session 3 WHERE type <> 'BACKGROUND'; SID SERIAL# PROGRAM ---------- ---------- ------------------ 137 6 sqlplusw.exe
Piemērs 10. Pēdējā SQL teikuma reālā izpildes plāna iegūšana. Šeit ir gatava iebūvētā funkcija, kas lasa datus no v$sql un v$sql_plan_statistics_all. Tiesa gan nepieciešama vismaz 10g versija.
SQL> SELECT /*+gather_plan_statistics*/ count(*) 2 FROM person_source; COUNT(*) ---------- 1000000 SQL> select * from table(dbms_xplan.display_cursor(null,null, 2 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------- SQL_ID akmshau7zk6yw, child number 1 ------------------------------------- SELECT /*+gather_plan_statistics*/ count(*) FROM person_source Plan hash value: 2792221198 --------------------------------------------------------------- |Id|Operation |Name |E-Rows|A-Rows| A-Time | --------------------------------------------------------------- |1 |SORT AGGREGATE | | 1 | 1|00:00:00.23| |2 | TABLE ACCESS FULL|PERSON_SOURCE| 999K| 1000K|00:00:03.00| ---------------------------------------------------------------
Turpmākā lasāmviela
- Oracle datu vārdnīcas skatījumu dokumentācija.
- Reāls scenārijs, kad dažu sekunžu vaicājums uz x$ tabulām nobeidz produkcijas sistēmu uz ~10 minūtēm.
- Pārspriedumi par to, kāpēc pirmo internetā ieraudzīto SQL vaicājumu nevajag pildīt produkcijā.
- Materiāli par fiksētajām tabulām, to nosaukumiem un saturu.
Datubāzes 