Optimització

1. Optimització Toni Navarrete Enginyeria del Software II – UPF 2002

2. Introducció • Si hi ha més d’un índex, com tria Oracle quin és el que ha d’utilitzar? • I si és millor no utilitzar-ne cap? • Podem escriure les consultes de forma que s’executin de forma més ràpida? • Dues parts: • Accés a les taules • Joins

3. Com es processa una query

4. Com es processa una query • Creació d’un cursor • El cursor és l’àrea de memòria que manté la definició d’un statement(sentència) SQL ja obert • Parsejat • Es comprova la sintaxi de la instrucció SQL i de les referències a objectes. Es comprova si ja ha estat parsejada abans (PreparedStatements) • Binding de variables • Es donen els valors a les variables • Execució • Si la instrucció és DML o DDL (no una consulta) s’executa • Si és una consulta, es prepara el statement per recuperar les dades • Fetch • Recupera una o més files del result set (només si és una consulta) • Quasi sempre és la més costosa

5. Parsejat • L’ús de PreparedStatements millora molt aquesta fase • En aquesta fase també es decideix el pla d’execució (ho fa l’optimitzador d’Oracle) • Hi ha un parámetre SESSION_CACHED_CURSORS que fa que es gestioni una caché de cursors • També es pot forçar a que el parser substitueixi literals per variables de binding. Exemple: • SELECT * FROM clients WHERE nom=‘pep s.a.’; • Alter session set cursor_sharing=FORCE; ho transforma a: SELECT * FROM clients WHERE nom=:SYS_B_0;

6. Fetch • 4 tipus d’accés a les taules: • Table scan • És llegeix cada fila de la taula a la memòria • ROWID access • És una pseudo-columna (tot i que no forma part de la taula, és pot fer un select sobre ella) • Representa la localització física d’una fila • És típicament la forma més ràpida d’accedir a una fila • Típicament s’obté d’un índex • Index lookup • És una cerca mitjançant un índex: es cerquen valors de claus (lookup=“resolució”) • Hash key access • S’utilitza per accedir a clusters hash

7. Fetch • 3 tècniques per fer joins: • Sort merge • És un join sense utilitzar índexs: • S’ordena cada taula a partir de la columna de join i s’ajunten en una única taula resultant • Nested loops • S’utilitza un índex (o un hash de un cluster) almenys en una de les taules • Es recuperen les files de la primera taula (potser fent un full-scan) i després es fa un lookup sobre la segona taula • Hash join • Es crea un hash per accedir a la taula més grosa a partir de la més petita (és una espècie d’índex creat on-the-fly) • S’usa quan les taules són de tamany molt diferent • Útil especialment si el hash cap en memòria • Paràmetre de configuració SORT_AREA_SIZE • Per a sorts (ORDER BY), agregacions (GROUP BY, DINSTINCT, MAX, COUNT,...) i operacions de conjunt (UNION, INTERSECT, MINUS)

8. Algunes consideracions importants sobre l’ús d’índexs (d’arbre B*) • L’ordre és molt important quan es defineix un índex multiclau. • No només es pot utilitzat l’índex “complet” (totes les columnes) • També en parts, si es fa en l’ordre en que està definit • Exemple: • Si definim un índex amb tres claus (A,B,C) • Si apareixen A, B i C al where, pot utilitzar l’índex sobre les 3 cols. • Si apareixen A i B al where, pot utilitzar l’índex sobre A i B • Si apareixen A i C al where, pot utilitzar l’índex només per A (no per C perquè prèviament hi ha B, que no surt al where) • Si apareix B al where, no pot utilitzar l’índex • Posar al devant els camps amb més probabilitat de sofrir cerques no conjuntes • Quasi sempre es crearan índexs per a les foreign keys, per tal de millorar l’eficiència dels joins • Els valors NULL no s’indexen • Això és un problema si cerquem els valors nuls al where. En canvi, sí s’usarà l’índex si cerquem els NOT NULL

9. Recordatori Joins • Inner Join • Equijoin vs thetajoin • Outer Join (+) • Nota: els drivers JDBC d’Oracle no soporten la notació OUTER JOIN (left outer join, right outer join, full outer join). Veure exemple a transp. següent • Self Join • Conjunts (Sets) • UNION, MINUS, INTERSECT • Operacions de grup • AVG, COUNT, MAX, MIN, SUM, STDDEV... • Queries jeràrquiques

10. Exemple d’outer join en les dues notacions SELECT ename, dname FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno} ORDER BY ename SELECT ename, dname FROM emp e, dept d WHERE e.deptno = d.deptno(+) ORDER BY ename

11. L’optimitzador • L’optimitzador determina la millor manera per processar una sentència SQL • Oracle té dos optimitzadors: • Basat en regles (rule-based) • Té una sèrie de regles prefixades per fer les decissions. Per exemple, sempre preferirà un índex a un full-scan, però no distingeix entre fer un full-scan d’una taula de 2 files o de 2 milions de files • Basat en el cost (cost-based) • Usa principalment informació estadística relativa al volum de les dades i la distribució de les dades en les taules i índexs. • Disponible des d’Oracle 7

12. Els dos optimitzadors d’Oracle • No són perfectes • No fan miracles!! Cap dels dos permet: • Re-escriure queries mal formulades • Crear índexs • L’optimitzador basat en regles és més predecible (i més segur) • L’optimitzador basat en costos utilitza algorismes no públics • Per un bon funcionament de l’optimitzador basat en costos cal recollir estadístiques periòdicament

13. Aspectes comuns dels dos optimitzadors • Transformacions de consultes equivalents • IN passa a un inner join • OR (no usa índexs) passa a UNION ALL • “Select ... From nombre_vista Where ...”, se transforma al Select només utilitzant taules (sense vistes), a partir de la definició de la vista

14. Descripció del procés (per ambdòs optimitzadors) • Per a cada taula es consideren les opcions que hi ha per recuperar les dades • S’analitza per quina taula començar i quin ordre seguir per fer el join • Independentment de l’optimitzador, s’apliquen aquestes regles: • Si un join resulta en una única fila (potser perquè ambdues taules tenen una clau primària o una constrain unique), se li dóna preferència a aquest join • Si hi ha un outer join, la taula externa (la que pot tenir Nulls) s’ajuntarà després de l’altra (la interna)

15. Seleccionant l’objectiu de l’optimitzador • Objectiu de l’optimitzador: quin enfocament utilitza l’opt. per decidir • Hi ha quatre possibles valors: • RULE • CHOOSE (defecte) • ALL_ROWS • FIRST_ROWS • Per seleccionar (tres formes): • Paràmetre init.ora: optimizer_mode=FIRST_ROWS • ALTER SESSION SET OPTIMIZER_GOAL=RULE • Usant hints: • SELECT /*+ ALL_ROWS */id, nombre FROM alumne WHERE ...;

16. Ranking de l’optimitzador basat en regles • Single row by ROWID • Single row by cluster join • Single row by hash cluter key with unique or primary key • Single row by unique or primary key • Cluster join • Hash cluster key • Indexed cluster key • Composite key (but only if all keys are used) • Single column indexes • Bounded range search on indexed columns • Unbounded range search on indexed columns • Sort-merge join • MAX or MIN of indexed columns • ORDER BY on indexed columns • Full-table scan

17. Ranking de l’optimitzador basat en regles (resum) • Es prefereixen lookups que donen una sola fila que els que donen múltiples files • Els índexs es prefereixen als full-scan de taules o als sort merges • Es prefereixen lookups amb igualtat que amb rangs • Rangs afitats (per exemple amb between) es prefereixen a rangs no afitats (>) • Es prefereix utilitzar totes les columnes d’un índex concatenat (multiclau) a només algunes d’elles

18. Optimització basada en costos. Obtenció d’estadístiques • Sense estadístiques de les taules i índexs, l’optimitzador no pot decidir de forma precissa • ANALYZE ANALYZE TABLE | INDEX | CLUSTER nom [ COMPUTE STATISTICS | ESTIMATE STATISTICS SAMPLE tamany [ROWS|PERCENT] ] Exemple: ANALYZE TABLE alumne COMPUTE STATISTICS; Exemple: ANALYZE TABLE alumne ESTIMATE STATISTICS SAMPLE 10 PERCENT; • En el cas d’un índex es guarda a INDEX_STATS i d’una taula a DBA_TABLE

19. Optimització basada en costos. Obtenció d’estadístiques • Histograma de les columnes per tenir més detalls de com es distribueixen els valors: ANALYZE TABLE nom [ COMPUTE STATISTICS | ESTIMATE STATISTICS ...] {FOR COLUMNS llista_columnes | FOR ALL COLUMNS | FOR ALL INDEXED COLUMNS} SIZE n (n és el nombre de caixes de l’histograma) Exemple: ANALYZE TABLE matricula COMPUTE STATISTICS FOR COLUMNS assignatura_id SIZE 10;

20. Hints • Introduint hints a les sentències SQL es poden forçar camins d’accés. Exemples: • Select /*+ index(alumne) */ ... • Fa que forçosament s’usi algun índex de la taula alumne • Select /*+ index(alumne, ind_al_pk) */ ... • Fa que forçosament s’usi l’índex ind_al_pk sobre la taula alumne • La coma no és obligatòria • Podrien haver més índexs en la llista • Select /*+ full(alumne) */ ... • Fa que forçossament es faci un full-scan de la taula alumne • Select /*+ ordered */ ... • Fa que forçossament el join s’executi en l’ordre en què esta especificat al FROM • /*+ use_nl(...) */ fa que s’usi un nested loop • /*+ use_merge(...) */ fa que es faci un merge

21. Com analitzar els plans de consulta • Pla de consulta: • Com l’optimitzador executa una sentència SQL • Analitzant el pla de consulta per una query podem descobrir problemes • 3 eines: • EXPLAIN PLAN • SQL_TRACE I TKPROF • AUTOTRACE

22. Explain plan Explain plan Set statement_id=‘cons_alumnes’ Into plan_table For select * from alumnes; (en vermell la consulta que es vol analitzar) Prèviament cal crear la taula plan_table

23. Taula de plan de consulta CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_INSTANCE NUMERIC, OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMERIC, PARENT_ID NUMERIC, POSITION NUMERIC, COST NUMERIC, CARDINALITY NUMERIC, BYTES NUMERIC, OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMERIC, OTHER LONG, DISTRIBUTION VARCHAR2(30));

24. Presentació de les dades del pla de consultes select lpad(' ',2*(level-1))||operation||' '|| options, object_name "QUERY PLAN", cost, cardinality from plan_table start with id=0 and statement_id=‘cons_alumnes' connect by prior id=parent_id;

25. Presentació de les dades del pla de consultes. Operacions més freqüents TABLE ACCESS (FULL) fila a fila (BY ROWID) s’accedeix a una fila a partir del seu rowid (CLUSTER) utilitzant un cluster INDEX (UNIQUE SCAN) retorna el rowid d’una única fila (RANGE SCAN) retorna el rowid de vàries files, ja sigui perquè l’índex no és unique o perquè s’utilitza un comparador en el where (FULL SCAN) recorre cada entrada de l’índex en ordre de clau (FAST FULL SCAN) recorre cada entrada de l’índex en l’ordre de bloc, possiblement usant lectures multi-bloc

26. Presentació de les dades del pla de consultes. Operacions més freqüents Per fer els joins: MERGE JOIN s’ordena cada taula a partir de la columna de join i s’ajunten en una única taula resultant (no utilitza índex) NESTED LOOPS es recuperen les files de la primera taula i després es fa un lookup sobre la segona taula (s’usa un índex) HASH JOIN cada fila d’una banda (típicament la més petita) s’usa com a hash per accedir a files de l’altra (la més grossa). Es crea una espècie de índex on-the-fly SORT (ORDERED BY) resultat de la clàusula order by (JOIN) ordena els valors en preparació d’un merge join (UNIQUE) un sort per eliminar files duplicades, típic amb la clàusula distinct (AGGREGATE) amb una clàusula d’agregac. (GROUP BY) amb una clàusula group by

27. Presentació de les dades del pla de consultes. Exemple SELECT STATEMENT MERGE JOIN SORT JOIN MERGE JOIN SORT JOIN TABLE ACCESS FULLT2 SORT JOIN TABLE ACCESS FULLT12 SORT JOIN TABLE ACCESS FULLT1

28. Presentació de les dades del pla de consultes. Exemple SELECT STATEMENT NESTED LOOPS NESTED LOOPS INDEX UNIQUE SCANPK1 INDEX RANGE SCANPK12 TABLE ACCESS BY INDEX ROWIDT2 INDEX UNIQUE SCANPK2

29. SQL*TRACE i TKPROF • Explain Plan dóna molt poques dades • Amb SQL*TRACE podem obtenir informació sobre: • Nombre de files processades • Temps de CPU (en centèssimes de segon) • Temps total invertit (en centèssimes de segon) • Operacions d’accés a disc • Lectures lògiques • ... • Divisió per fases (parse, execute, fetch)

30. SQL*TRACE i TKPROF alter session set sql_trace=true timed_statistics=true; select t2.* from t1,t12,t2 where t1.id1=1 and t12.id1=t1.id1 and t2.id2=t12.id2; alter session set sql_trace=true timed_statistics=false; • Es genera un arxiu .trc a al directori del servidor %OracleHOME%/ADMIN/nom_bd/udump • Després cal executar la comanda tkprof per generar una sortida llegible. Per exemple: • tkprof ORA03072.TRC out.txt

31. SQL*TRACE i TKPROF. Exemple de sortida select t2.* from t1,t12,t2 where t1.id1=1 and t12.id1=t1.id1 and t2.id2=t12.id2 call count cpuelapseddiskquerycurrentrows ------- -------------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.06 0 00 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.04 0 6 8 3 ------- ------ ---------------------------- ---------- ---------- ---------- total 4 0.020.100683 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 37 Rows Row Source Operation ------- --------------------------------------------------- 3 NESTED LOOPS 6 NESTED LOOPS 2 INDEX FAST FULL SCAN (object id 12260) 6 TABLE ACCESS FULL T2 3 INDEX UNIQUE SCAN (object id 12264) Nombre de files retornades Nombre de lectures de disc Nombre de blocs llegits, l’important és el total (“lectures lògiques”)

32. SQL*TRACE i TKPROF. Aspectes a tenir en compte • Pla d’execució de la consulta • Relació entre els blocs lògics llegits (total query+total current) i les files processades (total rows) • Quant més blocs s’han de processar per recuperar una fila, més costosa és l’operació • Ratios per damunt de 10 a 20 indiquen que la consulta es pot millorar • Excepcions, per exemple, casos com count(*) sempre retornen una fila, amb la qual cosa la relació és molt alta

33. SQL*TRACE i TKPROF. Aspectes a tenir en compte • Relació entre el cost de parsejar i d’executar • Idealment el parse count hauria de ser 1 (o proper) • Si és superior que el execute count, s’hauria de reescriure la consulta • Relació entre lectures de disc i lectures lògiques • S’ha de minimitzar el nombre de lectures de disc • El rati hauria de ser inferior a un 10% (10 vegades més lectures lògiques que de disc) • El contrari indica un mal ús de la caché • Comprovar que no es processen més files que les que té la taula • Això indicaria que estem recorrent diverses vegades la taula

34. AUTOTRACE • Es pot fer des de SQL*PLUS • No és tan detallat com SQL*TRACE, però sí més que EXPLAIN PLAN • SET AUTOTRACE • OFF • ON EXPLAIN • ON STATISTICS • ON (ambdues) • TRACEONLY

35. AUTOTRACE. Un exemple set autotrace on select t2.* from t1,t12,t2 where t1.id1=1 and t12.id1=t1.id1 and t2.id2=t12.id2; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=56) 1 0 NESTED LOOPS (Cost=2 Card=2 Bytes=56) 2 1 NESTED LOOPS (Cost=2 Card=5 Bytes=80) 3 2 INDEX (FAST FULL SCAN) OF 'PK1' (UNIQUE) (Cost=1 Card=1 Bytes=8) 4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=5 Bytes=40) 5 1 INDEX (UNIQUE SCAN) OF 'PK12' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 8 db block gets 6 consistent gets 0 physical reads 0 redo size 1233 bytes sent via SQL*Net to client 715 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 3 rows processed

36. Taules organitzades per índex i clusters • La disposició de les dades als blocs no és aleatòria • Clusters: organitzats per una clau (o conjunt de claus) • Taules organitzades per índex: els valors estan als nodes fulla de l’arbre B de l’índex

37. ORD_NO PROD QTY ... ----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 ORD_NO ORD_DT CUST_CD ------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45 Cluster Clustered ORD and ITEM tables Unclustered ORD and ITEM tables

38. Hash cluster Dos tipus de clusters Index cluster Hash function

39. Creant un cluster. Un exemple create cluster clu (as_id number(3)) tablespace tt; create index ind_clu on cluster clu tablespace ti; create table assignatura( as_id number(3), ... constraint pk_assignatura primary key (as_id) using index tablespace ti ) cluster clu(as_id); create table matricula ( ma_num number(6), ma_al_id number(4) not null, ma_as_id number(3) not null, constraint pk_matricula primary key (ma_num) ) cluster clu(ma_as_id);

40. Creant un cluster amb Hash. Un exemple CREATE CLUSTER hashclu (as_id number(3)) HASHKEYS 10 TABLESPACE tt;

41. Quan és útil un cluster Criterion Uniform key distribution Evenly spread key values Rarely updated key Often joined master-detail tables Predictable number of key values Queries using equality predicate on key Index Hash Y Y Y Y Y Y Y Y

42. Indexed access on table Accessing index-organized table Taula organitzada per índex ROWID Non-key columns Key column Row header

43. Comparació amb taules “normals” Regular Table Unique identifier—ROWID ROWID implicitSupports several indexes FTS returns rows in no specificorder Unique constraints allowed Distribution, replication, andpartitioning supported Index-Organized Table Identified by primary key No ROWIDNo secondary indexes Full index scans returnrows in PK order No support for unique constraints Distribution, replication, andpartitioning not supported

44. Creació d’una taula organitzada per índex. Un exemple CREATE TABLE sales ( office_cdNUMBER(3), qtr_endDATE, revenueNUMBER(10,2), review VARCHAR2(1000), CONSTRAINT sales_pk PRIMARY KEY(office_code, qtr_end) ) ORGANIZATION INDEX TABLESPACE data01 PCTTHRESHOLD 20 OVERFLOW TABLESPACE data02;