OCP 11gR2: Administration II 練習筆記 (八)

Lesson 08

===============================================================================

01. Database performance

- set performance planning

- instance tuning

        Well-defined goals (process 500 business sales transactions per mins).

        Memory turning, HDD or network I/O maybe bottleneck of database.

        Tune step from top down: (1. Check instance parameter, 2. Check application code, 3. Check design).

        Consider SQL using resource, the longest wait, the largest service time.

        Stop tuning when the goal is met.

- Performance monitoring

        By EM (may need flashplayer plug-in) > performance

- Performance tuning statistics by AWR (user session, SQL, service)

 * parameter statistics = typical (because basic no time column), (All for 16 core CPU up)

 SQL performance tuning depends on accurate statistics

 Statistics affect database search methods(e.g… by index or full scan table)

 Optimizer statistics: object statistics (table size … column values …), O/S statistics

        exec dbms_stats.gather_table_stats('scott','emp');

 collect statistics by automatic maintenance task or manual "dbms_stats" (after bulk operation)

        EM > scheduler central > optimizer statistics gathering > schema.table

                Or

        exec dbms_stats package

- optimizer_dynamic_sampling default is 2, which when found a table no statistic, db will auto collect it.

- skewness make statistics distort, collect statistics need use option "100%" collecation

- stale_percent = the table change many % then collect statis

- view table statistics by "v$dba_tab_statistics" or "v$dba_tab_col_statistics"

Oracle wait events (idle wait, buffer busy wait, latch(RAM lock), I/O wait….)

        EM > performance > CPU wait

        select sid, program from v$session where program like '%sqlplus%'); <- to found sid

        select * from v$system_wait_class where wait_class like '%I/O%';

        EM > performance > top activity

- SQL Trace (*.trc file): parameter sql_trace default is false, record all resource when enable

        exec dbms_session.set_sql_trace(true); <- will slow down database performance a lot;

        select * from 'table_name';

        select sid, sql_trace from v$session;

- service related statistics, check workload connect from service.

        EM > performance > top activity > to service

        Create services by "dbms_service.create_service(……)"

- resource contention

        V$lock, v$undostat, v$waitstat, v$latch (immediate_get, immediate_misses)

- AWR, automatic workload repository

 AWR in sysaux, manual snapshot by ADDM "dbms_workload_repository.create_snapshot()"

        Check report: EM > server > automatic workload repository > run compare periods report

        "dba_hist_db_cache_advice", "dba_hist_dispatcher", "dba_hist_dyn_remaster_stats", "

- Database replay: capture workload and reply in other machine

02. SQL tuning: EM > advisor central . SQL advisors

- SQL tuning advisor: identify poorly tuned SQL statement (target by individual SQL)

        Structure, indexes, materialize, view, statistics analysis, SQL profiling, access path…

        From top activity, SQL tuning set (create set between time), historical SQL (AWR)

e.g.  import bigfile.cvs

        EM > SQL tuning sets > create > name as tuningset1 > next

        Set the frequenting to 'duration 1 minutes', freq 5 sec

        Set filter like select * from 'table' > finish

        select * from table where…..

        select * from table where…..

        select * from table where…..

        …

        ..

        Back to sql tuning set, select tuningset1 and click schedule sql tuning adviser

        Set time limt, scope of analysis to comprehensive and submit (option "limited" will not create SQL profile)

        * duplicate SQL means SQL structure same but some value not same

- SQL access advisor: turn entire workload of SQL (by bulk workload)

        Bulk workload to advisor to tune, it can verify access path, hypothetical workload (DB simulate DML for self-tune).

e.g. EM > advisor central > SQL Access Advisor > recommend new access structures using existing tuningset1 >

        comprehensive, time 1 min, journaling Full (log ) and submit.

- Automatic SQL tuning Result: check with finding option <- if recommend Ok, click implement (for first start DB only)

SQL profile:

 Stored access plan, database use the profile and no need to consider how to run the statement

-sql performance analyzer: predict potential performance problem (system change)

        Predict oracle different version

        Predict parameter change…

OCP 11gR2: Administration II 練習筆記 (九)

OCP 11gR2: Administration II 練習筆記 (七)