Autotrace

janvāris 30, 2008

Turpinot tēmu par ātrdarbības meklējumumiem Oracle datubāzēs šoreiz paskatīsimies uz SQL*Plus rīka komandu autotrace. Tā jau dod vairāk informācijas nekā vienkārši patērētais laiks, ko var noskaidrot vairākos veidos.
Jāpiebilst gan, ka izpildes plānu un statistiskos rādītājus izmantojot autotrace ir iespējams dabūt tikai DML teikumiem – SELECT, INSERT, UPDATE, MERGE, DELETE.
Tātad par visu pēc kārtas:

Autotrace priekšnosacījumi

Lai varētu pilnvērtīgi izmantot šo SQL*Plus komandu, ir divi priekšnosacījumi:

  • Lietotājam jābūt iedotai PLUSTRACE lomai. Lomas izveides skripts atrodas $oracle_home\sqlplus\admin\plustrce.sql, kur $oracle_home ir direktorija, kur uzinstalēta Oracle datubāze.
  • Lietotājam jābūt pieejamai tabulai PLAN_TABLE. Tā var būt uzinstalēta kādā datubāzes lietotāja shēmā un iedotas tiesības visiem pārējiem lietotājiem, vai arī katra paša lietotāja shēmā, tas nav svarīgi. Tabulas izveides skripts atrodas $oracle_home\rdbms\admin\utlxplan.sql . Vajadzētu lietot tieši konkrētajai Oracle versijai paredzēto tabulu tāpēc, ka katrā nākošajā versijā parasti nāk klāt jaunas kolonas.

Autotrace iespējošana/atspējošana

  • SET AUTOTRACE OFF  – autotrace atskaite netiek ģenerēta. Normālā situācija.
  • SET AUTOTRACE ON EXPLAIN  – autotrace atskaite parāda tikai izpildes plānu.
  • SET AUTOTRACE ON STATISTICS – autotrace atskaite parāda tikai SQL teikuma izpildes statistiku.
  • SET AUTOTRACE ON – autotrace atskaite attēlo gan izpildes plānu, gan SQL teikuma izpildes statistiku.
  • SET AUTOTRACE TRACEONLY – autotrace atskaite attēlo gan izpildes plānu, gan SQL teikuma izpildes statistiku, bet netiek attēlots SELECT vaicājuma izpildes rezultāts. Ērti izmantot tad, ja vaicājuma izpildes rezultāts ir liels un mūs neinteresē, bet interesē tikai izpildes plāns un statistika.
  • SET AUTOTRACE TRACEONLY EXPLAIN – autotrace atskaite attēlo izpildes plānu, bet netiek attēlots vaicājuma izpildes rezultāts. Ja tas ir SELECT teikums, tad šajā gadījumā patiesībā SQL SELECT teikums NETIEK izpildīts.
  • SET AUTOTRACE TRACEONLY STATISTICS – autotrace atskaite attēlo SQL teikuma izpildes statistiku, bet netiek attēlots vaicājuma izpildes rezultāts.

Dažas lietas, ko vērts atcerēties:

  • INSERT, UPDATE, DELETE, MERGE teikumi tiek reāli izpildīti vienmēr, bet SELECT teikums patiesībā netiek izpildīts, ja ir SET AUTOTRACE TRACEONLY EXPLAIN. Šis variants reizēm ir noderīgs, ja select teikums izpildās stundu, bet mēs gribam redzēt tikai izpildes plānu. Kā arī reizēm rodas nesapratne – kāpēc, ja ir SET AUTOTRACE TRACEONLY EXPLAIN, tad select teikums izpildās zibenīgi, bet citādi iet stundu? Atbilde tātad – select pieprasījums patiesībā netiek izpildīts, tiek uzģenerēts tikai izpildes plāns.
  • Nelietot autotrace SYS lietotājam. Šis lietotājs ir īpašs un šim lietotājam autotrace atskaite nav izmantojama.
  • Autotraces rezultātā iegūtais SQL pieprasījuma izpildes plāns var nebūt īstais, kas patiesībā ir jūsu programmā. Vairumā gadījumu izpildes plāni sakrīt un ir vienādi, bet var būt gadījumi, kad tas tā nav, piemēram, ja atšķiras vides uzstādījumi sesijām (izdalītā atmiņa utml). Otrs iemesls, kad izpildes plāni var atšķirties ir tad, ja jūs lietojat BIND mainīgos un Oracle datubāze reālā vaicājuma izpildes plāna ģenerēšanas laikā pirmajā reizē skatās uz doto mainīgo vērtībām. Autotrace ģenerējot SQL vaicājuma izpildes plānu to nedara. Piemērs – jums ir tabula ar kolonu, kurā ir viena “A” vērtība un 1000 “B” vērtības. Ja atlasa ierakstu ar “A” vērtību, tad tabulu ir vērts lasīt pēc indeksa, ja atlasa ierakstus ar “B” vērtību, tad tabulu ir izdevīgāk lasīt visu neizmantojot indeksu. Šajā gadījumā autotrace var rādīt vienu izpildes plānu, bet reālais var būt atšķirīgs.

Izmantošanas piemērs

Izmantotā tabula ir šāda: 

CREATE TABLE personas (
  prs_personas_kods VARCHAR2(11) NOT NULL PRIMARY KEY,
  prs_vards VARCHAR2(40),
  prs_uzvards VARCHAR2(40));
INSERT INTO personas VALUES ('11111112345', 'JĀNIS', 'BĒRZIŅŠ');
INSERT INTO personas VALUES ('12121212345', 'PĒTERIS', 'SŪNIŅŠ');
INSERT INTO personas VALUES ('13131312345', 'RŪDIS', 'BĒRZIŅŠ');
COMMIT;
SQL> set autotrace on
SQL> SELECT * FROM personas WHERE prs_personas_kods = '11111112345';
PRS_PERSONA PRS_VARDS                                PRS_UZVARDS
----------- ---------------------------------------- --------------
11111112345 JĀNIS                                    BĒRZIŅŠ
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1
Bytes=51)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'PERSONAS' (TABLE)
(Cost=1 Card=1 Bytes=51)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C00112708' (INDEX (UNIQUE))
(Cost=1 Card=1)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        459  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tātad mēs šeit redzam izpildes plānu, kurā tiek parādīts:

  • kādas darbības (SQL vaicājuma izpildes plāns) Oracle DB veic, lai iegūtu rezultātu,
  • kāds ir izpildes plāna un tās soļu izmaksu novērtējums (Cost);
  • kāds ir sagaidāmo ierakstu skaits ko Oracle DB cer iegūt katra soļa rezultātā (Card, no cardinality);
  • kāds ir rezultāta apjoms baitos (Bytes).

Ja Cost, Crad un Bytes nav, tad tas nozīmē, ka jūs izmantojat RBO (rule based optimizer)  un šeit ir rakstīts, kāpēc to nevajag darīt.

Par katru no statistikām vairāk nākošajā sadaļā.
Lai paskatītos, cik grūti ir iegūt visus mums pieejamos objektus no datu vārdnīcas, varam pašus rezultātus neskatīties, bet tikai statistiku.

SQL> set autotrace traceonly statistics
SQL> select * from all_objects;
 61295 rows selected.
Statistics
------------------------------------------------------
       5467  recursive calls
          0  db block gets
     113664  consistent gets
          0  physical reads
          0  redo size
    3032446  bytes sent via SQL*Net to client
      45458  bytes received via SQL*Net from client
       4088  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      61295  rows processed

Tātad kā redzam, ieraksti rādīti netika, bet statistikas atskaite ir redzama.

Statistiku nozīme

  • recursive calls – rekursīvo izsaukumu skaits (SQL teikumu skaits), ko veic sistēmas vai lietotāja līmenī. Daži no izplatītākajiem iemesliem varētu būt lietotāja definēto funkciju izsaukumi, kas kaut ko dara datu bāzē un sistēmas izsaukumi, lai varētu izveidot SQL Pieprasījuma izpildes plānu.
  • db block gets – bloka nolasīšana current mode. Pārsvarā tas parādās datu koriģēšanā, kad jāmaina tikai bloka pēdējā versija.
  • consistent gets – bloka lasīšana no Oracle DB bufera keša read consistent mode. Pārvarā tas parādās vaicājumos un iekļauj arī bloka iegūšanu no UNDO segmentiem (iepriekšējām bloka versijām atmiņā).
  • physical reads – bloka lasīšana no diska gan izmantojot tiešo lasīšanu, gan bufera kešu. Protams, arī šeit jāatceras, ka ne vienmēr tā patiesi ir lasīšana no diska – jo pastāv vēl gan operētājsistēmas kešs, gan iespējams arī katram diskam ir kaut kāds kešs. Bet no Oracle DB viedokļa tā ir lasīšana no diska.
  • redo size – norāda cik daudz tiek ģenerēts REDO. Pārsvarā attiecas tikai uz datu koriģēšanas teikumiem.
  • bytes sent through SQL*Net to client – no servera uz klienta pārsūtīto baitu skaits.
  • bytes received via SQL*Net from client – no klienta uz servera pārsūtīto baitu skaits.
  • SQL*Net roundtrips to/from client – SQL*Net ziņojumu skaits, kas sūtīts starp datu bāzi un klientu.
  • sorts (memory) – veikto kārtošanu skaits atmiņā.
  • sorts (disk) – kārtošanu skaits, kam ir bijusi nepieciešamība pēc pagaidu vietas uz diska.
  • rows processed – atlasīto (select), pievienoto (insert, merge), koriģēto (update, merge), dzēsto (delete, merge),  ierakstu skaits.

Kam pievērst uzmanību (nosacītā svarīguma dilšanas kārtībā):

  • Pats svarīgākais ir censties samazināt consistent gets un db block gets. Jo tas nozīmē, ka jums mazāk datu būs jālasa no atmiņas, mazāk datu būs jāapstrādā, mazāk būs jāveic atmiņas struktūru skanēšana, kas protams ir ļoti ātra, bet tai pašā laikā prasa nozīmīgus CPU resursus. Nevajadzētu iedomāties, ka “ieraujot visu datu bāzi” atmiņā tiks atrisinātas visas ātrdarbības problēmas. Pie tam samazinot šo statistiku automātiski parasti samazinās arī physical reads. Consistent reads ir atkarīgs arī no tā kāds ir vienā reizē pārsūtīto ierakstu skaits starp datubāzi un klientu un līdz ar to pārsūtīto ziņojumu skaits (SQL*Net roundtrips to/from client) vienam un tam pašam SQL vaicājumam. Jo šis skaits ir lielāks, jo vienam un tam pašam SQL vaicājumam var būt vairāk consistent gets. Katrā vidē to nosaka savādāk, bet SQL*Plusā to nosaka izmantojot komandu SET ARRAYSIZE <skaitlis>.
  • recursive calls visu laiku (ne tikai pirmajā izsaukuma reizē) saglabājas liels skaitlis (simti, tūkstoši). Tas var norādīt uz to, ka vai nu nepārtraukti SQL teikums tiek parsēts atkal un atkal (kas autotraces un vienkārša DML teikuma gadījumā ir neparasti), vai arī tiek izpildītas lietotāja definētas funkcijas, kas var būt paslēptas skatījumā un vispārīgā gadījumā var būt pamatīgs ātrdarbības grāvējs.
  • Liels redo size. Jo lielāks redo size, jo vairāk datu Oracle ir spiesta rakstīt uz diska. Jāatceras, ka katrs indekss, kas datu modificēšanas laikā ir papildus jāuztur, palielina šo metriku.
  • Liels physical reads. Jānoskaidro kāpēc tas tāds ir. Iespējams, ka tas ir neizbēgami – ja jums piemēram ir jānolasa liela tabula un tā ir jānolasa visa, lai iegūtu kādu atskaiti, tad parasti visefektīvākais veids ir to visu arī vienkārši nolasīt.
  • Sorts – mērķis ir samazināt metriku sorts (disk), jo tas nozīmē, ka kārtošana nesatilpst atmiņā. Ir 2 iespējas kā to paveikt – palielināt atmiņu vai samazināt kārtošanu skaitu vispār 🙂

Turpmākā lasāmviela


Patērētā laika mērīšana Oraclē

novembris 13, 2007

Turpinot iesākto tēmu “Kā noskaidrot, kur paliek laiks?” tagad sīkāk par to kā procedūras, funkcijas, SQL teikuma utt. izpildes laiku var izmērīt Oracle datubāzē.
Protams, varētu mēģināt sēdēt ekrāna priekšā ar hronometru vai pulksteni rokā un tādā veidā noskaidrot cik tad laiks tika patērēts. Tiesa gan tā nav no tām precīzākajām metodēm un arī ļoti mazus izpildes laikus vis nenoķersi. Tā kā jārīkojas mazliet gudrāk.
Pati elementārākā iespēja ir izmantot rīku SQLPlus un izmantot šī rīka komandu SET TIMING ON.
Tas izskatās šādi:

SQL> set timing on
SQL> exec dari_kautko
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.00
SQL>

Ne vienmēr, protams, visas PL/SQL procedūras un/vai SQL teikumi tiek izsaukti izmantojot SQLPlusu, kā arī bieži rodas ļoti liela vēlēšanās mērīt procedūras iekšienē, cik ilgi katrs SQL teikums vai kāds procedūras bloks izpildās. Tādā gadījumā ir iespējams izmantot vairākas iespējas. Visvienkāršākā, protams, ir lietot mainīgo ar datu tipu date, kas patiesībā ir datumlaiks, bet tā mīnuss ir tas, ka mazākā vienība ir sekunde. Itin bieži tas jau ir pārāk liels laika sprīdis.
Jau krietni labāka iespēja ir lietot iebūvētās pakotnes dbms_utility funkciju get_time, kas atgriež laiku sekundes simtdaļās. Šī pakotne ir pieejama vismaz no versijas 8i, tai skaitā protams 9i, 10g un arī jaunajā 11g. Parasti get_time lieto tikai, lai fiksētu sākuma atskaites punktu un tad iegūtu starpību starp beigu atskaites punktu un iepriekš fiksēto sākuma atskaites punktu, piemēram, šādi:

SQL> set serveroutput on
SQL> declare
  2 v number;
  3 begin
  4 v := dbms_utility.get_time;
  5 dari_kautko;
  6 dbms_output.put_line(‘Patērētais laiks ir:’ ||
  7 (dbms_utility.get_time – v) / 100 || ‘ sekundes’);
  8 end;
  9 /
Patērētais laiks ir:3 sekundes
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.00
SQL>

Nākošā iespēja ir lietot timestamp datu tipu, kas satur arī sekundes daļas līdz pat 9 cipariem aiz komata (noklusēti 6). Atņemot no viena timestamp datu tipa otru tiek iegūts intervāls. Diemžēl intervāla datu tipam ir nepatīkama atšķirība no datuma un timestamp datu tipa, jo attēlojot to uz ekrāna nav iespējams ērti lietot formatēšanas modeļa elementus un tāpēc nākas darboties nedaudz viltīgi, lai neteiktu, piedodiet par izteicienu “čerez ž…“. Šajā piemērā intervāls tiek pieskaitīts timestamp vērtībai, kam tiek nogriezta visa laika daļā, līdz ar to piemērs strādās korekti, ja intervāls būs mazāks kā 24 stundas. Vairumā gadījumu ar to pilnīgi pietiek.

SQL> set serveroutput on
SQL> declare
  2 v timestamp;
  3 begin
  4 v := systimestamp;
  5 dari_kautko;
  6 dbms_output.put_line(‘Patērētais laiks ir:’ ||
  7 to_char(cast (trunc(systimestamp) as timestamp) + (systimestamp – v),’hh24:mi:ss.FF’));
  8 end;
  9 /
Patērētais laiks ir:00:00:03.000000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.03

Ja jūs lietojat kādu tā saucamo grafisko lietotājam draudzīgo rīku, piemēram, PL/SQL Developer vai tamlīdzīgus, tad vismaz visai procedūrai kopā parasti izpildes laiks tiek parādīts, piemēram, šī rīka SQL Window izpildes loga statusa joslā tiek attēlots paziņojums “Done in 3,015 seconds”.

Nākošais līmenis jau ir mērīt ne tikai patērēto laiku, bet arī veikt citus mērījumus, kas sniedz plašāku ieskatu, kas tad tur apakšā patiesībā notika. Par to turpinājums ir rakstā par SQL*Plus komandu autotrace.


Nedaudz par SQL*Plus

novembris 6, 2007

Savā pirmajā rakstā nedaudz pastāstīšu par to, kā padarīt darbu ar Oracle SQL*Plus ērtāku, bet pirms tam atbildēšu uz jautājumu – kādēļ tas vispār vajadzīgs?

SQL*Plus ir standarta rīks darbam ar Oracle datu bāzēm un kā tāds, tas ir pieejams visās Oracle klienta un servera instalācijās. Ne vienmēr jums būs pieejams kāds no ierastajiem izstrādes vai administrēšanas rīkiem, tādēļ iesaku apgūt vismaz pašus SQL*Plus pamatus, lai nenonāktu situācijā, kad nepieciešams steidzami veikt kādu darbu, bet, piemēram, PL/SQL Developer instalācijas trūkuma dēļ to nevariet paveikt.

Vairāk par SQL*Plus pamatiem variet atrast šeit:

  • SQL*Plus® Quick Reference
  • SQL*Plus® User’s Guide and Reference
  • Un tagad – pie lietas!

    1) Optimizējam piekļuvi saviem iecienītajiem skriptiem

    Regulāriem SQL*Plus lietotājiem bieži vien ir izveidojusies kolekcija ar skriptiem, kas tiek lietoti ikdienā – procesu, sesiju, ieplānoto darbu, tabulvietu parlūkošanai un administrēšanai. Ja lietojat SQL*Plus Windows versiju (sqlplusw.exe), tad skriptus var izsaukt divos veidos – izmantojot File -> Open izvēlnes vai līdzīgi kā komandrindas SQL*Plus (sqlplus / sqlplus.exe) norādot skripta nosaukumu aiz “@” zīmes, piemēram:

    SQL> @/export/home/san/scripts/oracle/skripts.sql

    Ja izsaucamie SQL skripti atrodas SQL*Plus darba direktorijā, tad nav nepieciešams rakstīt pilnu ceļu skripta nosaukumā:

    SQL> @skripts.sql

    Parasti gan SQL*Plus darba direktorija sakrīt ar konsoles darba direktoriju, bet sqlplusw.exe gadījumā tā ir direktorija, no kuras pēdējo reizi ar File -> Open izvēlnes palīdzību tika izsaukti skripti. Lai padarītu skriptu izsaukšanu pēc iespējas ērtāku, ieteicams izmantot vides mainīgā SQLPATH sniegtās priekšrocības. Ar SQLPATH palīdzību var definēt SQL*Plus darba direktoriju:

    bash$ pwd
    /
    bash$ export SQLPATH=/export/home/san/scripts/oracle/
    bash$ sqlplus ‘/ as sysdba’
    — ar šo komandu tiek izsaukts tas pats skripts.sql,
    — kas atrodas /export/home/san/scripts/oracle/,
    — tikai daudz ērtāk un ātrāk

    SQL> @skripts

    Windows vidē mainīgo SQLPATH var uzstādīt nospiežot Win+Break, aizejot uz Advanced tab’u -> Environment variables -> New …

    Unix/Linux to var ierakstīt savā .profile failā un dzīvot laimīgi.

    2) Modificējam SQL*Plus vidi

    Droši vien katram, kas ikdienā strādā ar vairāk par vienu DB, kādreiz ir sanācis nejauši veikt kādas darbības nepareizajā datu bāzē. Ja tas ir update vai pat delete, tad bēda maza – gandrīz vienmēr var glābt rollback. Ar nodropotiem objektiem jau ir nopietnākas problēmas.
    Šādas kļūdas nevar pilnībā novērst, bet samazināt to iespējamību gan var viegli.

    Šīm mērķim es izmantoju login.sql:

    REM modify sqlplus prompt

    set termout off

    define gname=idle
    column global_name new_value gname

    select lower(user) || ‘@’ ||
    substr(global_name, 1, decode (dot, 0, length(global_name), dot-1)) global_name
    from (select global_name, instr(global_name, ‘.’) dot from global_name);

    set sqlprompt ‘&gname> ‘

    set termout on

    Un rezultāts:

    sqlplus ‘/ as sysdba’
    SQL> — tagad izpildīšu login.sql …
    SQL> @login.sql
    sys@TEST>
    sys@TEST> — kā redzams, SQL> nomainījās pret sys@TEST>,
    sys@TEST> — kas parāda, ka esmu pieslēdzies TEST bāzei kā SYS lietotājs
    sys@TEST>

    Šādi man vienmēr ir redzams, kurai bāzei esmu pieslēdzies un pastāv mazāka iespēja, ka pārslēdzoties starp vairākiem logiem izpildīšu kādu komandu nepareizā sesijā.

    Ja login.sql ievieto direktorijā uz kuru norāda SQLPATH vides mainīgais, tad šīs skripts izpildīsies automātiski pie SQL*Plus palaišanas:

    bash$ sqlplus ‘/ as sysdba’

    SQL*Plus: Release 9.2.0.5.0 – Production on Wed Oct 10 21:37:06 2007

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 – Production

    sys@TEST>

    Un pēdējais komentārs par login.sql – ja nokonfigurējat sistēmu tā, lai šis skripts izpildītos automātiski pie SQL*Plus palaišanas, tad ir ērti to papildināt ar vēl dažiem ierakstiem, piemēram:

    set linesize 200

    column object_name format a30
    column segment_name format a30
    column file_name format a75

    define _editor=vi

    utt.