Cursus performance tuning voor ontwikkelaars

1
Cursus performance tuningvoor ontwikkelaars

• 26 januari 2006

De beste manier om een query te verbeteren, is
door hem niet uit te voeren.
2
Onderwerpen

• Meetmethodes
• Cost based optimizer
• Interne verwerking van SQL
• Diversen
• Pauze
• Marcel Hoefs gedeelte

3
Meetmethodes explain plan

• utlxplan.sql / PLAN_TABLE
• Syntax
• dbms_xplan.display
• Leesvolgorde
• Voorspelling vooraf

cpt_explain.sql
cpt_xplan_display.sql
cpt_plan_lezen.sql
4
Meetmethodes SQL Trace

• Instrumentatie van Oracle zelf
• Syntax m.b.v. alter session
• Syntax m.b.v. dbms_session
• Syntax m.b.v. dbms_system
• USER_DUMP_DEST
• TIMED_STATISTICS
• MAX_DUMP_FILE_SIZE
• _TRACE_FILES_PUBLIC

cpt_sql_trace1.sql
cpt_sql_trace2.sql
cpt_sql_trace3.sql
5
Meetmethodes tkprof

• ltinstancegt_ora_ltprocesIDgt
• _lttracefile_identifiergt.trc
• Formattering
• Syntax tkprof
• Lezen van een tkprof-uitvoerbestand
• Redenen voor onvolledig uitvoerbestand
• De waarheid

charonmvs_ogt tkprof
cpt_onvolledig.sql
6
Meetmethodes Trace events

• Wat kan je met events ?
• 10046 event
• Verklaring verschil tussen cpu en elapsed met
  level 8 of 12
• Syntax m.b.v. alter session
• Syntax m.b.v. dbms_system
• Basis van Veritas I3 Indepth for Oracle
• 10053 event

cpt_1401.sql
cpt_10046_1.sql
cpt_10046_2.sql
7
Cost based optimizer

• Opvolger van Rule based optimizer
• Cost
• OPTIMIZER_MODE
• Statistieken
• analyze versus dbms_stats
• Monitoring
• Histogrammen

cpt_statistieken.sql
cpt_analyze.sql
cpt_dbms_stats.sql
cpt_monitoring.sql
cpt_histogrammen.sql
8
Interne verwerking van SQL - schematisch

• Syntax controle
• Semantische controle
• Opzoeken in library cache
• Optimalisatie
• Plangeneratie
• Uitvoeren volgens plan
• Resultaat teruggeven

cpt_syntax.sql
cpt_seman.sql
cpt_optim.sql
9
Interne verwerking Minder parsen

• OPEN cursor ? parse ?
• SESSION_CACHED_CURSORS
• Session cursor cache
• PL/SQL cursor cache
• SQLPlus

cpt_open_parse.sql
10
Interne verwerking van SQL - opgave
Nr Soort hard parses soft parses exec Snelst
1 Rij per rij
2 Bulk
3 Dyn. SQL
4 Dyn. SQL met b.v.
5 SQL
1
0
10.000
3
0
100
2
1
0
5
10.000
10.000
1
10.000
9.999
4
1
0
1
1
11
Interne verwerking - bindingsvariabelen

• Plaatsvervangers voor constanten in SQL
• Hard parse versus soft parse
• Latches / schaalbaarheid
• CURSOR_SHARING
• PL/SQL en OracleForms
• Peeking
• SQL-injectie

cpt_latches.sql
cpt_peeking.sql
cpt_injectie.sql
12
Diversen - Hints

• Algemeen liever niet gebruiken !
• Uitzonderingen
• FIRST_ROWS(n), ALL_ROWS, CHOOSE, (NO)REWRITE,
  DRIVING_SITE, (NO)PARALLEL, (NO)APPEND,
  CARDINALITY, DYNAMIC_SAMPLING, CURSOR_SHARING_EXAC
  T

13
Diversen Outer joins

• Nadeel van outer join
• Is de outer join echt nodig ?
• Een alternatief

cpt_outerjoin1.sql
cpt_outerjoin2.sql
14
Diversen Analytische functies

• Mogelijkheid om meer in SQL, minder in PL/SQL te
  doen
• Verzamelingen
• Kan self join voorkomen
• Berekeningen over rijen heen
• Lopende totalen

cpt_af1.sql
cpt_af2.sql
15
Einde deel 1

• VRAGEN ?