Performance Tuning Verbeteringen in Oracle Database 10g
Oracle 10g komt met een aantal performance tuning verbeteringen waaronder:

 

Automatic Performance Diagnostic en Tuning Mogelijkheden


Oracle 10g heeft een aantal mogelijkheden gerelateerd aan automatische performance en tuning waaronder:

  • Automatic Optimizer Statistics Collection - De naam zegt het al.
  • Automatic Workload Repository (AWR) - Een uitgebreide versie van de STATSPACK repository dat de kern is van
    alle nieuwe diagnose en tuning mogelijkheden.
  • Automatic Database Diagnostic Monitoring (ADDM) - Een automatische diagnose en tuning tool die de informatie
    gebruikt die opgeslagen is in de AWR.
  • Automatic SQL Tuning Advisor - Een ingebouwde SQL tuning mogelijkheid. De meeste van deze mogelijkheden
    gaan te ver voor dit document en zullen in afzonderlijke artikelen besproken .

Automatic Shared Memory Management


Automatic Shared Memory Management laat Oracle de toewijzing van geheugen binnen het SGA beheren. De SGA_TARGET parameter geeft het aantal geheugen aan wat beschikbaar is voor de SGA. Deze parameter kan dynamisch aangepast worden tot aan het maximum die in de waarde van SGA_MAX_SIZE parameter is gezet. Indien de STATISTICS_LEVEL op TYPICAL of ALL is gezet en de SGA_TARGET op een waarde anders dan "0" , dan zal Oracle de memory pools beheren die anders door de volgende parameters gezet werden:

  • DB_CACHE_SIZE (default block size)
  • SHARED_POOL_SIZE
  • LARGE_POOL_SIZE
  • JAVA_POOL_SIZE

Als de waarden van deze parameters anders dan op 0, dan representeren ze de minimum grootte van de Pool. Deze minimum waarden kunnen noodzakelijk zijn indien de applicatie errors krijgt als bepaalde pool grootten beneden een bepaalde treshold komen.

De volgende parameters moeten handmatig gezet worden en verminderen het quotum welke is aangegeven in de SGA_TARGET parameter:

  • DB_KEEP_CACHE_SIZE
  • DB_RECYCLE_CACHE_SIZE
  • DB_nK_CACHE_SIZE (non-default block size)
  • STREAMS_POOL_SIZE
  • LOG_BUFFER

Wait Model Verbeteringen


Een aantal views zijn vernieuwd en toegevoegd om het wait model te verbeteren. De vernieuwde views:

  • V$EVENT_NAME
  • V$SESSION
  • V$SESSION_WAIT

De nieuwe views:

  • V$ACTIVE_SESSION_HISTORY
  • V$SYSTEM_WAIT_HISTORY
  • V$SESS_TIME_MODEL
  • V$SYS_TIME_MODEL
  • V$SYSTEM_WAIT_CLASS
  • V$SESSION_WAIT_CLASS
  • V$EVENT_HISTOGRAM
  • V$FILE_HISTOGRAM
  • V$TEMP_HISTOGRAM
Hieronder een paar voorbeeld van hoe de nieuwe views kunnen worden gebruikt:

De V$EVENT_NAME view heeft 3 nieuwe kolommen erbij gekregen (WAIT_CLASS_ID, WAIT_CLASS# and WAIT_CLASS) wat het event classificeert. Dit zorgt voor een makkelijkere samenvoeging van de event details:

 

-- Laat de wachttijd zien voor iedere wait class.
SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited
FROM v$event_name a
JOIN v$system_event b ON a.name = b.event
GROUP BY wait_class;

WAIT_CLASS TIME_WAITED
---------------------------------------------------------------- -----------
Application ,000029
Commit ,001733
Concurrency ,00476
Configuration ,000176
Idle 3,203279
Network ,000018
Other ,00186
System I/O ,006668
User I/O ,030975

9 rows selected.

De V$SESSION view heeft er enkele kolommen bij gekregen welke blocking session en wait informatie bevatten. Door de wait informatie hoef je nu niet meer te joinen met V$SESSION_WAIT om informatie over een sessie te krijgen:

 

-- Laat blocked session zien met hun details.
SELECT sid, serial#, blocking_session_status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;

no rows selected

-- Laat de resource of het eventzien war het event voor wacht.
SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM v$session
ORDER BY sid;

SID SERIAL# EVENT SECONDS_IN_WAIT
---------- ---------- ----------------------------------- ---------------
131 20 SQL*Net message from client .000015
133 501 wakeup time manager .000138
134 45 SQL*Net message to client 0
135 4 queue messages .000003
137 8 SQL*Net message from client .000132
.....
167 1 rdbms ipc message 0
168 1 rdbms ipc message 0
169 1 rdbms ipc message .079485
170 1 pmon timer .092645

31 rows selected.
De V$SESSION_WAIT_CLASS view stelt je in staat stelt je in staat de sessie informatie te zien onderverdeeld in wait class voor iedere sessie:
-- Laat de sessie wait informatie zien per wait class.
SELECT *
FROM v$session_wait_class
WHERE sid = 134;

SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ------------------- ----------- -----------
134 28448 4217450380 1 Application 2 0
134 28448 3875070507 4 Concurrency 1 2
134 28448 2723168908 6 Idle 57 392127
134 28448 2000153315 7 Network 68 5

4 rows selected.

De V$SYSTEM_WAIT_HISTORY view laat historische informatie zien wat the mogelijk maakt dat je issues kan bekijken nadat de sessie is afgelopen.

 

Automatic Optimizer Statistics Collection

 

Oracle 10g worden de optimizer statistieken deafault automatisch verzameld met de job GATHER_STATS_JOB. Deze job loopt default van 10 P.M. tot 6 A.M. door de week en in het weekend de hele dag.

De job roept de interne procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC aan die de statistieken voor ieder tabel met danwel lege danwel stale statistieken bevatten, vergelijkbaar aan de DBMS_STATS.GATHER_DATABASE_STATS procedure die aangeroepen wordt met de GATHER AUTO optie. Het grootste verschil is dat de interne job de table met met de grootste urgentie eerste geupdate zal worden.

In sommige gevallen kan het automatisch verzamelen van de statistieken problemen opleveren. Vluchtige tabellen en load tabellen kunnen hun statistieke krijgen al er een onevenredig aantal rijen in de tabellen aanwezig. Dit kan op 2 manieren voorkomen worden:

De huidige statistieken kunnen verwijderd worden en gelocked om DBMS_STATS er van te weerhouden om nieuwe statistieken te verzamelen. Als de OPTIMIZER_DYNAMIC_SAMPLING parameter op 2 is gezet (the default) of hoger dan zullen de nodige statistieken verzameld worden als onderdeel van de query optimization stage (Zie Dynamic Sampling):

 

BEGIN
DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
/

De statistieken kunnen verzameld worden en gelocked op een tijdstip dat de table wel de juiste data:

 

BEGIN
DBMS_STATS.gather_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
/

Systeem statistieken en statistieken voor fixed objects, zoals dynamische performance tabellen, worden niet automatisch verzameld.

 

Dynamic Sampling


Dynamic sampling stelt de server in staat om de performance te verbeteren door:

  • Berekenen van instelbare selectiviteit voor enkele tabellen, waar geen statistieken voor beschikbaar zijn en daardoor kunnen leiden tot slechte schattingen.
  • Berekenen van statistieken voor tabellen en indexen met missende statistieken.
  • Berekenen van statistieken voor tabellen en indexen met oude statistieken.

Dynamic sampling wordt gecontroleerd door de OPTIMIZER_DYNAMIC_SAMPLING parameter die waarden accepteert in de range van 0 (uit) tot 10 met 2 als default. Bij het compileren zal Oracle nagaan od dynamic sampling de query performance zou verbeteren. Indien dit zo is dan zal het recursief statements uitvoeren om de nodige statistieken te berekenen.

Dynamic sampling kan voordeel hebben als:

  • De sample, tijd ten opzichte van de overall query executie tijd, klein is.
  • Dynamic sampling resulteerd in een beter presterende query.
  • De query meerdere keren moet draaien

Samen met de OPTIMIZER_DYNAMIC_SAMPLING system parameter , kan de dynamic sampling level gezet worden met de DYNAMIC_SAMPLING optimizer hint voor bepaalde queries like

 

SELECT /*+ dynamic_sampling(emp 10) */
empno,ename,job,sal
FROM emp
WHERE deptno = 30;

Het resultaat van dynamic sampling worden herhaaldelijk gegenereerd, er worden geen rows inserted, updated of deleted van de berekende tabel. De OPTIMIZER_FEATURES_ENABLE parameter zal dynamic sampling uitzetten als het gezet is naar een waarde eerder dan 9.2.0.

CPU Costing


Als default zal de cost model van de optimizer staan op CPU+I/O, met de tijd als cost unit.

 

Optimizer Hints


Nieuwe hints:

  • SPREAD_MIN_ANALYSIS – Specificeerd analyze opties voor spreadsheets.
  • USE_NL_WITH_INDEX - Specificeerd een nested loops join.
  • QB_NAME - Specificeerd een naam voor een query block.
  • NO_QUERY_TRANSFORMATION – Weerhoud de optimizer om performing query transformations uit te voeren.
  • NO_USE_NL, NO_USE_MERGE, NO_USE_HASH, NO_INDEX_FFS, NO_INDEX_SS en NO_STAR_TRANSFORMATION – Exclude bepaalde opreaties van het quey plan.
  • INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC - Excludes range scans van het query plan.

Geupdate hints:

  • Hints die tabelnamen specificeren zijn uitgebreid om Global Table Hints aan te kunnen. Op deze manier kan je een base table binnen een view definieren met de "view-name.table-name" syntax.
  • Hints die specify index namen specificeren zijn uitgebereid om Complexe Index Hints aan te kunnen. Op deze manier kan je een index specificeren met de “(table-name.column-name)" syntax in plaats van de index naam.
  • Sommige hints kunnen optioneel een query block parameter aan.

Hernoemde hints:

  • NO_PARALLEL - Was NOPARALLEL.
  • NO_PARALLEL_INDEX - Was NOPARALLEL_INDEX.
  • NO_REWRITE - Was NOREWRITE.

Uitgefaseerde hints:

 

  • AND_EQUAL
  • HASH_AJ
  • MERGE_AJ
  • NL_AJ
  • HASH_SJ
  • NL_SJ
  • EXPAND_GSET_TO_UNION
  • ORDERED_PREDICATES
  • ROWID
  • STAR

 

Rule Based Optimizer Verwijderd


De Rule Based Optimizer (RBO) is verwijderd in Oracle 10g. De functionaliteit bestaat nog wel maar er is geen nieuwe functionaleit aan toegevogd en Oracle support the niet meer. Het bestaat alleen nog maar om de backwards compatibility tijdens migraties te geranderen aan de query optimizer (Cost Based Optimizer). Het resultaat van het verwijderen is:

  • De CHOOSE en RULE opties voor de OPTIMIZER_MODE parameter bestaat nogw el maar wordt niet meer gesupport.
  • De default waarde voor de OPTIMIZER_MODE parameter is ALL_ROWS.
  • De CHOOSE en RULE optimizer hints bestaan nog wel maar worden niet meer gesupport.
  • Code die nodig was voor RBO moet gemigreerd worden naar de query optimizer.

Tracing Verbeteringen


Oracle Trace functionaliteit is verwijderd uit Oracle 10g. In plaats daarvan zal de SQL Trace en TKPROF functionaliteit gebruikt moeten worden.

 

In multi-tier omgevingen waar statements doorgegeven naar verschillende sessies door de applicatie server, kan het moeilijk worden om een individueel proces van het begin tot het eind te tracen. Om dit op te lossen heeft OracleEnd to End Application Tracing geïntroduceerd zodat je een client proces kan identificeren via de client identifier in pplaats van de typische sessie id. Ieder deel van de trace informatie wordt gelinked naar de volgende informatie:

  • Client Identifier – Specificeert de “echte”. Je kan dit zetten met de DBMS_SESSION.SET_IDENTIFIER procedure.
  • Service - Specificeert een Group van gerelateerde applicaties. Je kan dit zetten met de DBMS_SERVICE.CREATE_SERVICE procedure.
  • Module – Specificeert een functioneel gebied of mogelijkheid voor een applicatie. Je kan dit zetten met de DBMS_APPLICATION_INFO.SET_MODULE procedure.
  • Action - Specificeert de huidige aktie (INSERT, UPDATE, DELETE etc.) binnen de huidige module. Je kan dit zetten met de DBMS_APPLICATION_INFO.SET_ACTION procedure.

 

End to end tracing kan gemaneged worden met de Enterprise Manager of een aantal APIs en views. Een paar voorbeelden van het aan/uitzetten van een paar type tracing:

 

BEGIN
-- Aan/Uitzetten Client Identifier Trace.
DBMS_MONITOR.client_id_trace_enable (client_id => 'my_id');

DBMS_MONITOR.client_id_trace_disable (client_id => 'my_id');

-- Aan/Uitzetten Service, Module en Action Trace (verschillende overloads).
DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service');

DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service',
module_name => 'my_module');

DBMS_MONITOR.serv_mod_act_trace_enable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');

DBMS_MONITOR.serv_mod_act_trace_disable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');

-- Aan/Uitzetten Session Trace (various overloads).
DBMS_MONITOR.session_trace_enable;

DBMS_MONITOR.session_trace_enable (
session_id => 15,
serial_num => 1234);

DBMS_MONITOR.session_trace_disable (
session_id => 15,
serial_num => 1234);
END;
/

Als de traces eenmaal zijn gemaakt kan de trcsess command line utility gebruikt worden om de relevante data uit de verschillende files te filteren. De volgende parameters kunnen gebruikt worden:

  • OUTPUT – Specifeerd de naam van de samengevoegde trace file.
  • SESSION – Voegt de file gebaseerd op de gespecificeerde session id (SID.SERIAL# kolom van V$SESSION) samen.
  • CLIENT_ID - Voegt de file gebaseerd op de gespecificeerde client identifier (CLIENT_IDENTIFIER kolom van V$SESSION)samen.
  • SERVICE - Voegt de file gebaseerd op de gespecificeerde service (SERVICE_NAME kolom van V$SESSION) samen.
  • MODULE - Voegt de file gebaseerd op de gespecificeerde specified module (MODULE kolom van V$SESSION) samen.
  • ACTION - Voegt de file gebaseerd op de gespecificeerde action (ACTION kolom van V$SESSION) samen.
  • TRACE_FILES – Een space gescheiden lijst van trace files die gescant moeten worden. Wanneer deze niet opgegeven wordt zullen alle files in de huidige directory gescant worden.

Tenminste 1 van de criteria moet gespecificeerd worden. Indien er meer dan 1 gespecificeerd wordt dan zullen alleen die traces die aan alle criteria voldoen, samengevoegd worden.Voorbeelden van trcsess gebruik:

 

# Zoek alle files voor deze sessie.
trcsess output=session.trc session=144.2274

# Zoek de gespecificeerde files voor deze client identifier.
trcsess output=client.trc client_id=my_id db10g_ora_198.trc db10g_ora_206.trc

# Zoek de gespecificeerde files voor deze service, module en action combinatie.
trcsess output=client.trc service=my_service module=my_module action=INSERT db10g_ora_198.trc db10g_ora_206.trc

Als de samengevoegde trace eenmaal is gemaakt kan het met de TKPROF utility bekeken worden als ieder andere trace file.

Default worden statistieken verzameld op sessie level. Met de DBMS_MONITOR package kan je dit aanpassen om de client identifier, service of combinaties van de service, module en action:

 

BEGIN
-- Aan/Uitzetten van Client Identifier Statistics.
DBMS_MONITOR.client_id_stat_enable (client_id => 'my_id');

DBMS_MONITOR.client_id_stat_disable (client_id => 'my_id');

-- Aan/Uitzetten van Service, Module en Action Statistics (varschillende overloads).
DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service');

DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service',
module_name => 'my_module');

DBMS_MONITOR.serv_mod_act_stat_enable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');

DBMS_MONITOR.serv_mod_act_stat_disable (
service_name => 'my_service',
module_name => 'my_module',
action_name => 'INSERT');
END;
/

De verzamelde statistieken kunnen met de volgende views bekeken worden:

  • DBA_ENABLED_AGGREGATIONS - Accumulated global statistics.
  • V$CLIENT_STATS - Accumulated statistics for the specified client identifier.
  • V$SERVICE_STATS - Accumulated statistics for the specified service.
  • V$SERV_MOD_ACT_STATS - Accumulated statistics for the specified service, module and action combination.
  • V$SVCMETRIC - Accumulated statistics for elapsed time of database calls and CPU usage.

SAMPLE Clause Verbeteringen


De SAMPLE clause geeft je de mogelijkheid een voorbeedl van eeen aantal rijen terug te geven door het opgeven van een percentage van rijen of blokken die gescant moeten worden. Deze clause kan nu in complexe queries gebruikt worden:

 

-- Query 10% van de rijen.
SELECT e.empno, e.ename, d.dname
FROM emp SAMPLE (10) e
JOIN dept d ON e.deptno = d.deptno;

-- Query 10% van de blokken.
SELECT e.empno, e.ename, d.dname
FROM emp SAMPLE BLOCK (10) e
JOIN dept d ON e.deptno = d.deptno;

Hash Partitioned Global Indexes


Support voor hash partitioned global indexes is toegevoegd in Oracle 10g wat de performance kan verbeteren al seen klein aantal leaf blocks last hebben van hoge contentie waarden. De syax om een hash paritioned global index te creeren is:

 

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);

Meer infomatie:

  • What's New in Oracle Performance?
  • Introduction to Performance Tuning Features and Tools
  • Automatic Shared Memory Management
  • Automatic Statistics Gathering
  • Estimating Statistics with Dynamic Sampling
  • Moving from RBO to the Query Optimizer
  • Optimizer Hints
  • Understanding the Query Optimizer
  • Using Application Tracing Tools
  • Creating a Hash-Partitioned Global Index

 

 

Advertentie

>

Poll

Voorkeur
 

Wie is er aanwezig

We hebben 176 gasten online