1 / 41

ISYS366: Week 04

ISYS366: Week 04. Database Tuning - Processes, Memory. Reasons for tuning. Online application needs to run faster Batch application needs to run faster Management wants the system to run faster [get the idea???]. Three aspects to performance tuning. Faster hardware

mckinneys
Download Presentation

ISYS366: Week 04

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ISYS366: Week 04 Database Tuning - Processes, Memory ISYS366 - Week 4

  2. Reasons for tuning • Online application needs to run faster • Batch application needs to run faster • Management wants the system to run faster • [get the idea???] ISYS366 - Week 4

  3. Three aspects to performance tuning • Faster hardware • Effective database configuration • e.g., don’t size any part of the System Global Area (SGA) outside of memory • Effective application design • e.g., effective query construction, effective use of parallelism; denormalization ISYS366 - Week 4

  4. DB_CACHE_SIZE DB_CACHE_SIZE Tuning the System Global Area • Automatic shared memory management simplifies the configuration of the SGA by automatically distributing the memory in the SGA for the following memory pools: • Database buffer cache (default pool): DB_CASHE_SIZE • Shared pool: SHARED_POOL_SIZE • Large pool: LARGE_POOL_SIZE • Java pool: JAVA_POOL_SIZE • Streams pool: STREAMS_POOL_SIZE ISYS366 - Week 4

  5. Understand your goals • Improve performance of specific queries • Improve performance of applications • Improve overall performance ISYS366 - Week 4

  6. Steps (in order) • Tune the query • Tune the application • Tune memory structures • Tune disk I/O • Detect and eliminate resource contention ISYS366 - Week 4

  7. When to tune the application • Before application rollout • Relatively inexpensive • Prototype • Issues • Database size • Database structure • Centralized • Distributed • Activity levels • After application rollout • Relatively expensive ISYS366 - Week 4

  8. Strategies • Multiple CPUs • Scheduling • Move specific applications to different servers ISYS366 - Week 4

  9. Parallelism • NOPARALLEL • specifies serial execution of an operation. This is the default. • PARALLEL • specifies parallel execution of an operation. • DEGREE • determines the degree of parallelism for an operation on a single instance. That is, the number of query servers used in the parallel operation. • Integer • use integer query servers. • DEFAULT • the default number of query servers used is calculated from the number of CPUs and the number of DEVICES storing tables to be scanned in parallel. ISYS366 - Week 4

  10. Parallelism • PARALLEL • INSTANCES • determines the number of parallel server instances used in the parallel operation. This keyword is ignored if you do not have a parallel server. • integer • use integer instances • DEFAULT • use all available instances • Note: INSTANCES only applies to an instance using the Oracle Parallel Server ISYS366 - Week 4

  11. Parallelism – Examples • The following command creates a table using 10 query servers, 5 to scan SCOTT.EMP and another 5 to populate EMP_DEPT: CREATE TABLE emp_dept PARALLEL (DEGREE 5) AS SELECT * FROM scott.emp WHERE deptno = 10; ISYS366 - Week 4

  12. Parallelism – Examples • The following command creates an index using 10 query servers, 5 to scan SCOTT.EMP and another 5 to populate the EMP_IDX index: CREATE INDEX emp_idx ON scott.emp (ename) PARALLEL 5; ISYS366 - Week 4

  13. Parallelism – Examples • The following command changes the default number of query servers used to query the EMP table: ALTER TABLE emp PARALLEL (DEGREE 9); ISYS366 - Week 4

  14. Parallelism – Examples • The following command causes the index to be rebuilt from the existing index by using 6 query servers, 3 each to scan the old and to build the new index: ALTER INDEX emp_idx REBUILD PARALLEL 3; ISYS366 - Week 4

  15. Parallelism – Examples • The following command performs tablespace recovery using 5 recovery processes on 5 instances in a parallel server, for a total of 25 (5 * 5) query servers: ALTER DATABASE RECOVER TABLESPACE binky PARALLEL (DEGREE 5 INSTANCES 5); ISYS366 - Week 4

  16. Tools – DBMS specific • Statistics Scripts • UTLBSTAT (Begin Statistics) • UTLESTAT (End Statistics – REPORT.TXT) • They must be modified • They could become very large, therefore they need to be put in a tablespace OTHER than SYSTEM • Remote databases • Server Manager • Data dictionary views (system level) ISYS366 - Week 4

  17. The methods of Collecting statistics 1. DICTIONARY and DYNAMIC views Oracle server displays all system statistics in the V$SYSSTAT view and uses many other views for performance and troubleshooting information.You can find culmative totals since the instance started and is often unhelpful. If the instance is often rarely shutdown the statistics may cover a long period and have little meaning. ISYS366 - Week 4

  18. The methods of Collecting statistics 2. UTLBSTAT and UTLESTAT Utilities Using this utilities to gather performance figures over a well defined period say during the busiest time of day or end of the Month and produce a report. ISYS366 - Week 4

  19. The methods of Collecting statistics 3. TOOLS Using oracle GUI tools -Diagonostics and Tuning Packs-which addresses many oracle Performance management areas such as Graphical monitoring analysis and automated tuning of oracle Database. ISYS366 - Week 4

  20. The Steps before the UTLBSTAT and UTLESTAT .To gather performance over a period of defined time..Produce a Hard Copy report.Run the script from server manager connected as SYSDBA..Set TIMED_STATISTICS to TRUE. ISYS366 - Week 4

  21. UTLBSTAT and UTLESTAT • Utility to begin statistics and Utility to end statistics. These are basically predefined SQL scripts comes along with oracle . ISYS366 - Week 4

  22. UTLBSTAT and UTLESTAT • SVRMGR>@oracle_HOME/rdbms/admin/utlbstat.sqlSVRMGR>@oracle_HOME/rdbms/admin/utlestat.sql ISYS366 - Week 4

  23. Report.txt • Library cache statistics • system statistics • Wait events statistics • Latch statistics • Rollback contention statistics • Buffer Busy Wait statistics • Dictionary cache statistics • I/O statistics per datafile/tablespacePeriod of measurement ISYS366 - Week 4

  24. Tablespace for the scripts • The script connects as SYSDBA and creates tables in SYS's default TS SYSTEM. Before running the script create a New TS for this purpose and change SYS's default TS to this new one. • After finishing running the both the scripts change once again SYS's default TS to SYSTEM. ISYS366 - Week 4

  25. UTLBSTAT 1)While first executing the UTLBSTAT script from the above path already mentioned....2)The script creates BEGIN and END tables and takes the snapshot of data from the dynamic performance tables(V$xxx) to collect initial statistics and stores these in the BEGIN tables. ISYS366 - Week 4

  26. UTLESTAT • 1)Execute the UTLESTAT as mentioned from the above path.2)The script takes a new snapshot of data from the dynamic performance tables (V$xxx) to collect final statistics and stores these in the END tables.3)The script creates DIFFERENCE tables where it stores the values of the substraction of the results of the initial statistics from the final statistics.4)The script drops all the temporary views and tables.5)The script generates a report by selecting the data from the DIFFERENCE tables. ISYS366 - Week 4

  27. UTLBSTAT and UTLESTAT • Based on the Report.txt file information you can understand which areas the tuning is required . ISYS366 - Week 4

  28. Application Tuning • Denormalize tables Instead of: create table employee ( fname char(15) not null, lname char(15) not null, minit char(1), ssn char(9) not null, bdate date, address char(30), sex char(1), salary number(7), superssn char(9), dno number(1), start_date date default(sysdate) not null, end_date date, primary key (ssn)); create table department (dname varchar2(20), dnumber number(1) primary key, mgrssn char(9), mgrstartdate date); ISYS366 - Week 4

  29. Application Tuning • Denormalize tables Use create table employee ( fname char(15) not null, lname char(15) not null, minit char(1), ssn char(9) not null, bdate date, address char(30), sex char(1), salary number(7), superssn char(9), start_date date default(sysdate) not null, end_date date, dname varchar2(20), dnumber number(1), mgrssn char(9), mgrstartdate date, primary key (ssn)); ISYS366 - Week 4

  30. Application Tuning • Denormalize tables Instead of: create table employee ( fname char(15) not null, lname char(15) not null, minit char(1), ssn char(9) not null, bdate date, address char(30), sex char(1), salary number(7), superssn char(9), dno number(1), start_date date default(sysdate) not null, end_date date, primary key (ssn)); create table dependent (essn char(9) references employee(ssn), dependent_name char(20), sex char(1), bdate date, relationship char(15), primary key (essn, dependent_name)); ISYS366 - Week 4

  31. Application Tuning • Denormalize tables Use create table employee ( fname char(15) not null, lname char(15) not null, minit char(1), ssn char(9) not null, bdate date, address char(30), sex char(1), salary number(7), superssn char(9), dno number(1), start_date date default(sysdate) not null, end_date date, dependent_name char(20), sex char(1), bdate date, relationship char(15), primary key (ssn, dependent_name)); ISYS366 - Week 4

  32. Application Tuning • PARALLEL clause of ALTER/CREATE TABLE and INDEX • PARALLEL clause of SELECT • Oracle Optimizer • Use of INDEXes • EXPLAIN PLAN • SQL Trace ISYS366 - Week 4

  33. Application Tuning • Compiling PROCEDURES • SQL statements must be • Parsed • Compiled • Stored in SGA • Procedures must be • Stored in memory ISYS366 - Week 4

  34. Application Tuning • A word about how operations access tables • SELECT without a WHERE causes a "full table scan" (TABLE ACCESS FULL) • Read multiple blocks into memory • Expensive with many rows and/or many users • Inexpensive with tables with few rows (faster than using an index) • /*+ FULL (employee) */ ISYS366 - Week 4

  35. Application Tuning • PARALLEL clause of SELECT • SELECT /*+ PARALLEL(employee, 4) */ • PARALLEL clause of SELECT with Parallel Server Option • SELECT /*+ PARALLEL(employee, 4, 2) */ ISYS366 - Week 4

  36. Application Tuning • Oracle Optimizer • OPTIMIZER_MODE in INIT.ORA • RULE • COST • ANALYZE • Rows in a table • Distinct keys in an index • DBMS_UTILITY.ANALYZE_SCHEMA analyses all tables in a schema • CHAINED_ROWS rows that do not fit into an Oracle data block become “chained” • CHOOSE ISYS366 - Week 4

  37. Application Tuning • Use of INDEXes • Indexes won't be used *IF* • String constant starts with a wildcard • Using a function in the where clause • IS [NOT] NULL • != • NOT IN • Leading value of a multicolumn index is not set to equal a value ISYS366 - Week 4

  38. Application Tuning • EXPLAIN PLAN explain plan set statement_id = 'MYTEST' for select ssn, dnumber from employee, department where dno = dnumber; SQL> select * From plan_table where statement_id = 'MYTEST'; ISYS366 - Week 4

  39. Application Tuning • EXPLAIN PLAN SQL> desc plan_table Name Null? Type ------------------------------- -------- ---- 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 NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ISYS366 - Week 4

  40. Application Tuning • EXPLAIN PLAN Continued … ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG ISYS366 - Week 4

  41. Operating System Dependent Issues • OS block size • Caching issues • And the list goes on … • (make friends with your Sys Admin) ISYS366 - Week 4

More Related