703. TIPTOP数据库管理(Oracle) - OraclePeranceTuning_cant_touch_code.ppt

1、Oracle Performance Tuning ,鄭 樹 .tw 逸凡科技,Database Problems 0.90 0.95,Database Buffers in 9iSGA_MAX_SIZE,At instance startup, Oracle server request SGA_MAX_SIZE bytes in memory spfileSID.ora changes are immediate (no shutdown) Set an SGA_MAX_SIZE If 128M granule size 16M SGA_MAX_SIZE not dynamic !,Dyn

2、amic Memory allocation / resizing,DB_CACHE_SIZE / DB_BLOCK_BUFFERS,DB_BLOCK_BUFFERS = 0 in Oracle9i If you set DB_BLOCK_BUFFERS then DB_CACHE_SIZE = DB_BLOCK_SIZE * DB_BLOCK_BUFFERS,DB_BLOCK_BUFFER - dont use it even through its compatible,ALTER SYSTEM SET DB_CACHE_SIZE = 17M;,Multiple Database Buff

3、er Pools,Define Individual pools size DB_CACHE_SIZE DB_KEEP_CACHE_SIZE (BUFFER_POOL_KEEP) DB_RECYCLE_CACHE_SIZE (BUFFER_POOL_RECYCLE) Above three parameters are dynamic Latches are automatically allocated by Oracle RDBMS,In Oracle9i,ALTER TABLE STORAGE (BUFFER POOL KEEP) ; ALTER TABLE STORAGE (BUFFE

4、R POOL RECYCLE) ;,Buffer Cache Advisory V$DB_CACHE_ADVICE,ALTER SYSTEM SET DB_CACHE_ADVICE = ON ; ( OFF | READY | ON ),Cache Size (MB) Buffers Factor Phys. Reads 30 3,802 18.70 192,317,943 (10%) 60 7,604 12.83 131,949,536 91 11,406 7.36 75,865,861 . 273 34,218 1.13 11,583,180 304 38,020 1.00 10,282,

5、475 Current Size 334 41,822 0.93 9,515,878 364 45,624 0.87 8,909,026 . 547 68,436 0.69 7,104,280 577 72,238 0.67 6,895,122 608 76,040 0.66 6,739,731 (200%),STATISTICS_LEVEL (9iR2),Controls Collection of Advisories and Statistics (BASIC | TYPICAL | ALL),Shared Pool Advice Buffer Cache Advice PGA Advi

6、ce MTTR Advice Segment Level Statistics Timed Statistics Timed OS Statistics Plan Execution Statistics,Statistics Viewshow parameters statisticsshow parameters times,Database Writer Tuning,Multiple DB Writer process can be deployed with DB_WRITER_PROCESSES (DBW0 to DBW9) Consider increasing DBWn pro

7、cesses if you see a high number of free_buffer_waits in V$SYSTEM_EVENT,SQL SELECT total_waits FROM V$SYSTEM_EVENT 2 WHERE EVENT = free buffer waits ;,Checkpoints Tuning,LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT FAST_START_MTTR_TARGET Size of the smallest log,Regulate the checkpoint queue with t

8、he following initialization parameters,Redo Log Tuning,LGWR writes when log buffer 1/3 full, or on COMMIT Too small results in frequent inefficient IO Too large results in too long to write Size actual redo logs such that switch every 30 minutes, or based on the amount of data you can afford to lose

9、,Operations Requiring Sorts,Index creation ORDER BY or GROUP BY clauses DISTINCT values selection UNION, INTERSECT or MINUS operators Sort-merge joins ANALYZE command execution,Sorts Tuning,Avoid sort operations whenever possible Reduce swapping and paging by making sure that sorting is done in memo

10、ry when possible Reduce space allocation calls by allocating temporary space appropriately Sort parameters WORKAREA_SIZE_POLICY = AUTO PGA_AGGREGATE_TARGET (10MB 4000GB) * 80% * 20% for OLTP * 80% * 50% for DSS WORKAREA_SIZE_POLICY = MANUAL SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE,Library Cache Tuning

11、,Make sure that users can share statements Prevent statements from being aged out by allocating enough space Avoid invalidations that induce reparsing,Minimized reparsing,Reserving space for large memory requirements Pinning frequently required large objects Eliminating large anonymous PL/SQL blocks

12、 Enabling the use of large pool for Oracle Shared Server connections,Reduce memory fragmentation,CURSOR_SHARING,FORCE If a large number of SQL statements differ only in the values of their literals Literals will be replaced by system generared bind variables where possible Too many hard parses and l

13、ibrary cache latch contention SIMILAR (9i) Cause statements that may differ in some literals, but are otherwise identical Oracle determines which literals are “safe” for substitution with bind variables EXACT,Determine what kind SQL statement can share the same cursor,Keeping Large Objects,Install b

14、y dbmspool.sql script Pin Package/Procedure/Function exec DBMS_SHARED_POOL.KEEP(SYS.STANDARD) Pin Trigger exec DBMS_SHARED_POOL.KEEP(SCOTT.TIGER,R),SELECT name, sharable_mem FROM V$DB_OBJECT_CACHE WHERE sharable_mem 10000 AND (type = PACKAGE or type = PACKAGE BODY or type = PROCEDURE or type = FUNCT

15、ION) AND kept = NO ;,Query processing,Query rewrite,Parse,Query Optimization RBO / CBO,Query Execution,QEP Generation,OPTIMIZER,Query,Result,SQL Statement Processing Phases,PARSE,EXECUTE FETCH,Statement matching, syntactic and semantic checks,Query Transformation,CBO,RBO Plan determined using rules,

16、Determine object costs and cardinalities,Cost different join orders,Build structures for runtime,Parsing Flow Diagram,SQL statement submitted,Is it in an open cursor ?,Is SESSION_CACHED_CURSOR,Is HOLD_CURSOR,Open a cursor,Statement is hashed and compared with the hashed value in sql area,Is it in sq

17、l area ?,no,no,no,no,Hard Parse,Soft Parse,Execute,yes,yes,yes,Client side,Server side,yes,Why does SQL need tuning ?,It is easy to write functional SQL It is harder to write efficient, high performance SQL,Why dont people tune their SQL ?,Too busy now. Ill do it later. Is that what Oracle the Oracl

18、e optimizer is for ? Im a java not a SQL programmer. It works, I have got my data. I am happy. I dont know how.,SQL Query Tuning can be modified,Add hints Create inline views Add more selective predicates Divide up the work of one query into multiple queries Rewrite the query in plsql Rewrite the qu

19、ery,SQL Query Tuning cannot be modified,Modify statistics Gather table statistics with a different sample size Add histogram to relevant columns Add or remove indexes Change OPTIMIZER_MAX_PERMUTATIONS parameter (if 8 or more table in the join) Create a view with embedded hints (distributed query) Cr

20、eate a materialized view (snapshot) Modify instance parameters Modify the degree of parallelism Use stored outlines to maintain the access path you want,Find Problem SQL of CPU Bound,Verify the reference number of CPU used by this session statistic SELECT name, statistic# FROM V$STATNAME WHERE name

21、LIKE %CPU%session ; Determine which session is using most of the CPU SELECT * FROM V$SESSTAT WHERE statistic# = 12 ; Lookup details for this session SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions, buffer_gets / executions AVG FROM V$SQLAREA a,V$SESSION s WHERE sid = AND s.sql_ad

22、dress = a.address AND executions 0 ORDER BY 5 ; Use V$SQLTEXT to extract the whole SQL text SELECT sql_text FROM V$SQLTEXT WHERE address = ORDER BY piece ;,Find Problem SQL of I/O Bound,SELECT address, executions, disk_reads, buffer_gets, ROUND(buffer_gets disk_reads) / buffer_gets,2) Hit_Ratio, ROU

23、ND(disk_reads / executions,2) Reads_Per_Run, sql_text FROM V$SQLAREA WHERE executions 0 AND buffer_gets 0 AND (buffer_gets disk_reads) / buffer_gets 0.80 ORDER BY 5 DESC;,Optimizer,It is an Oracle engine that would choose the most efficient method on retrieving data on a given query. The steps chose

24、n is called execution plan. Two kind of Optimizer on Oracle: RBO or Rule Based Optimizer since v6 CBO or Cost Based Optimizer since v7 OPTIMIZER_MODE FIRST_ROWS_n FIRST_ROWS ALL_ROWS CHOOSE RULE,CBO Architecture,Parsed query enters trans. Transformed query sent to estimator Stats are reviewed from d

25、ata dict. Query and estimates sent to plan gen. Plan gen. either returns plan to est., or sends to row source gen.,How CBO Works,CBO generates set of potential plans for the statement CBO estimates the cost of each plan based on statistics from ANALYZE command or DBMS_STATS package Statistics are st

26、ored in data dictionary Cost is the estimated value in proportion to the expected resource use CBO can be used if one or more tables in a query have been analyzed CBO makes estimates about non-analyzed tables based on analyzed table(s) CBO chooses the plan with the lowest cost,CBO: Cost of a SQL sta

27、tement,Cost is the estimated number of I/O, CPU, Network operations that a statement requires. CPU cost (parse) has little impact on a SQL tuning on most cases. While I/O is the most. Difference between logical and physical I/O It is affected by some parameters: db_file_multiblock_read_count sort_ar

28、ea_size hash_area_size hash_multiblock_io_count bitmap_merge_area_size,OPTIMIZER_MAX_PERMUTATIONS,Restricts the maximum number of permutations of the tables in query with joins Default : 2000 for = 9.0.0 80000 for 9.0.0,OPTIMIZER_INDEX_CACHING,Represents % of blocks that can be found in the cache Ra

29、nge 0 100 Default is 0 implies that index access will require a physical read Should be set to 90 for nested loop join,OPTIMIZER_INDEX_COST_ADJ,Represents cost of index access to full table scans Range 1 10,000 Default is 100 means index access is as costly as full table scan Should be set between 1

30、0 50 for OLTP and approximate 50 for DSS,Optimizer Statistics,Table statistics Number of rows, blocks, avg. row length Column statistics Number of distinct values and nulls, data distribution (histogram) Index statistics Number of leaf blocks, levels, clustering factor System statistics I/O ANALYZE

31、INDEX COMPUTE STATISTICS; ANALYZE TABLE ESTIMATE STATISTICS SAMPLE 100 ROWS; ANALYZE TABLE ESTIMATE STATISTICS SAMPLE 15 PERCENT; DBMS_UTILITY EXEC DBMS_Utility.Analyze_Schema(SCOTT,COMPUTE); EXEC DBMS_Utility.Analyze_Schema(SCOTT,ESTIMATE, estimate_rows = 100); EXEC DBMS_Utility.Analyze_Schema(SCOT

32、T,ESTIMATE, estimate_percent = 15); EXEC DBMS_Utility.Analyze_Database(COMPUTE); EXEC DBMS_Utility.Analyze_Database(ESTIMATE, estimate_rows = 100); EXEC DBMS_Utility.Analyze_Database(ESTIMATE, estimate_percent = 15);,Cost Based Optimizer (CBO) statistics new fashion,DBMS_STATS EXEC DBMS_Stats.Gather

33、_Database_Stats; EXEC DBMS_Stats.Gather_Database_Stats(estimate_percent = 15); EXEC DBMS_Stats.Gather_Schema_Stats(SCOTT); EXEC DBMS_Stats.Gather_Schema_Stats(SCOTT, estimate_percent = 15); EXEC DBMS_Stats.Gather_Table_Stats(SCOTT, EMPLOYEES); EXEC DBMS_Stats.Gather_Table_Stats(SCOTT, EMPLOYEES, est

34、imate_percent = 15); EXEC DBMS_Stats.Gather_Index_Stats(SCOTT, EMPLOYEES_PK);,Statistics Gathering Guidelines,Use individual statistics gathering commands for more control Gather statistics on large tables with a 5% sample Gather statistics on indexes with compute Add histograms where column data is

35、 known to be skewed,Scheduling CBO Statistics,Scheduling Stats SET SERVEROUTPUT ON DECLARE v_job NUMBER; BEGIN DBMS_Job.Submit ( v_job,BEGIN . DBMS_Stats.Gather_Schema_Stats(SCOTT); END;, Sysdate, Sysdate + 30 ); COMMIT; DBMS_Output.Put_Line(Job: | v_job); END; /,Move Table Statistics,Create a table

36、 to hold the statistics in old database exec DBMS_STATS.CREATE_STAT_TABLE (SCOTT,STATS) ; Move the statistics from the data dictionary to your created table exec DBMS_STATS.EXPORT_TABLE_STATS (SCOTT,EMP,NULL,STATS,NULL,TRUE) ; Use export / import to move the data (statistics) exp scott/tiger tables=

37、STATS file=expstat.dmp Imp scott/tiger file=expstat.dmp full=y Populate the data dictionary in new database exec DBMS_STATS.IMPORT_TABLE_STATS (SCOTT,EMP,NULL,STATS,NULL,TRUE) ;,Common CBO problem,The skewness problem 30% Analyzing with wrong data 25% Mixing optimizer in joins 20% Choosing inferior

38、index 20% Joining too many tables 5% Incorrect INIT.ORA settings 5%,Make problem SQL Faster,Indexing Re-write Use hints,When to create an index,Frequently retrieve less than 15% rows of a large table Index columns used for joins of multiple tables Create an index on a foreign key Small table dont re

39、quire indexes,Rebuild indexes When ,Index has too many levels HEIGHT in INDEX_STATS Index is too board DEL_LF_ROWS is 10% of LF_ROWS in INDEX_STATS Index clustering factor too high CLUSTERING_FACTOR in DBA_INDEXES,ANALYZE INDEX VALIDATE STRUCTURE ;,ALTER INDEX REBUILD COMPUTE STATISTICS ONLINE ;,STO

40、RED OUTLINE,Oracle hashes the SQL statement to see if it is already in the shared pool. If so, execute. If not in the shared pool, check for a stored outline. If found, load in the shared pool and execute. Otherwise, Oracle will parse, develop an execution plan, and execute the statement. Stored out

41、lines are considered when either the SESSION or SYSTEM parameter, USE_STORED_OUTLINES, is set to TRUE or a category name.,STORED OUTLINE Parameters,QUERY_REWRITE_ENABLED = true CURSOR_SHARING = force STAR_TRANSFORMATION_ENABLED = true OPTIMIZER_FEATURES_ENABLED = true OPTIMIZER_MODE = choose,STORED

42、OUTLINE Example (1),SQL ALTER SESSION SET create_stored_outlines = true; Session altered. SQL SELECT ename, dname 2 FROM scott.emp, scott.dept 3 WHERE emp.deptno = dept.deptno; . SQL SELECT name FROM dba_outlines; NAME - SYS_OUTLINE_021009222259501 SQL select hint from dba_outline_hints;,STORED OUTL

43、INE Example (2),SQL CREATE OUTLINE ord_test FOR CATEGORY category1 2 ON SELECT ename, dname 3 FROM scott.emp, scott.dept 4* WHERE emp.deptno = dept.deptno; Outline created. SQL SELECT name, category FROM dba_outlines; NAME CATEGORY - - SYS_OUTLINE_021009222259501 DEFAULT ORD_TEST CATEGORY1,How to En

44、able AUTOTRACE目錄 $ORACLE_HOME/sqlplus/admin,SQL conn / as sysdba SQL $ORACLE_HOME/sqlplus/admin/plustrce SQL grant plustrace to ; SQL conn / SQL $ORACLE_HOME/rdbms/admin/utlxplan SQL SET AUTOTRACE ON | TRACEONLY ;,Create SQL trace from another session,Alter system to set timed_statistics=true Get the session list in the current system Execute application and connect a user Get the session list to identify new create session Grant