Oracle Tips by Burleson

Using STATSPACK for Forecasting

After completing the reading assignments for this tutorial write a one thousand word summary explaining how to use STATSPACK reports to forecast Oracle database performance trends.

This paper will explore those types of Oracle tuning metrics what you would want to forecast, and a discussion of the IT management who would want trend reports from the Oracle database.

Submit to instructor by the due date.

ANSWER

The student should indicate that forecasting in Oracle is most commonly used for:

* CPU
* Disk space
* RAM usage
* Data buffer pool sizing
* Shared pool sizing

Question 1

You have been asked to come into a new Oracle shop where there have been complaints of poor response time.  You will only have 8 hours to identify and correct performance problems, so you must work quickly and efficiently.  Prepare a checklist of

1 - The things you would check
2 - The order that you would check them
3 - The corrective action you would take.

Question 2 

You are investigating poor performance of an Oracle database, and you suspect that there is a disk bottleneck.  Prepare a description of all Oracle metrics (both v$ views and STATSPACK tables) that can be used to infer disk enqueues and an I/O-bound Oracle database.

Question 3

You are creating a new Oracle 10g database and you have the ability to implement multiple blocksizes.  Discuss the merits of having multiple blocksizes and multiple data buffers.  Be as specific as possible, and provide guidelines for this new system.

Question 4

You have been asked to troubleshoot an Oracle database that is experiencing slow performance with SQL inserts and updates.  The performance of SQL queries is acceptable, but the customer is complaining that their shop-floor order entry is far too slow.  This customer has 1,000 order entry terminals, all adding and updating rows to the same tables.  Prepare a checklist of the likely causes of this problem and the corrective actins that will alleviate the poor DML performance.

Question 5

You have a customer who has a large Oracle data warehouse, and they have been complaining that their data analysis tasks take far too long.  Their analysis tasks consist of displaying averages and product comparisons for tables with over 100 million rows.  Prepare a checklist of the common causes for this performance problem and the Oracle tools and techniques that can be used to improve the end-users response time.

ANSWERS

Question 1

You have been asked to come into a new Oracle shop where there have been complaints of poor response time.  You will only have 8 hours to identify and correct performance problems, so you must work quickly and efficiently.  Prepare a checklist of

1 - The things you would check
2 - The order that you would check them
3 - The corrective action you would take.

The student should indicate that disk I/O is the largest component of slow response time, and they should mention that they would check the RAID level, size of the data buffers, and the size of the shared pool.  The student should also indicate that they will check the SQL optimizer mode to ensure that acceptable execution plans are generated for the database queries.

Question 2

You are investigating poor performance of an Oracle database, and you suspect that there is a disk bottleneck.  Prepare a description of all Oracle metrics (both v$ views and STATSPACK tables) that can be used to infer disk enqueues and an I/O-bound Oracle database.

The student should describe the v$filestat view and the stats$filestatxs table and indicate how they can use this data to locate data files with excessive amounts of disk I/O.  The student should also indicate that they need to know the number of physical disk and the RAID for the disks.

Question 3

You are creating a new Oracle 10g database and you have the ability to implement multiple blocksizes.  Discuss the merits of having multiple blocksizes and multiple data buffers.  Be as specific as possible, and provide guidelines for this new system.

The student should indicate that the multiple data buffer are used to segregate I/O by object, and the use of large blocksizes for indexes and small blocksizes for OLTP row access.

Question 4

You have been asked to troubleshoot an Oracle database that is experiencing slow performance with SQL inserts and updates.  The performance of SQL queries is acceptable, but the customer is complaining that their shop-floor order entry is far too slow.  This customer has 1,000 order entry terminals, all adding and updating rows to the same tables.  Prepare a checklist of the likely causes of this problem and the corrective actins that will alleviate the poor DML performance.

The student should indicate that the cause may be too few freelists on the tables, and describe the process of identifying the tables with buffer busy waits, and how to determine the best number of freelists.  The student should also mention LMTs and ASM in Oracle 10g.

Question 5

You have a customer who has a large Oracle data warehouse, and they have been complaining that their data analysis tasks take far too long.  Their analysis tasks consist of displaying averages and product comparisons for tables with over 100 million rows.  Prepare a checklist of the common causes for this performance problem and the Oracle tools and techniques that can be used to improve the end-users response time.

The student should mention OPQ and materialized views to speed data access.  The student should also mention table and index partitioning.

The main points of this tutorial include:

* Statistical forecasting starts by computing a statistical average of data.

* Oracle STATSPACK is ideal for preparing forecasts.

* STATSPACK forecasts are used to predict future hardware usage including RAM, CPU and disk.

* The farther out the forecast, the wider the confidence interval.

* STATSPACK data can be easily plotted and forecasts created by using the MS-Excel chart wizard.

Remote DBA Services

Burleson Consulting can offer world-class remote Oracle database support at super-affordable prices.

Our remote Oracle DBA service provides 100% of the remote Oracle database administration needs for your company, and includes 24x7 access to our staff of 100% OCP Certified Oracle DBAs. 

We require a 12 month service commitment and include the following services:

• Initial configuration review and problem identification
• Installation of Oracle statistics collection mechanisms and quarterly database growth summaries
• Hourly monitoring of your Oracle database for pending problems
• Periodic performance analysis & identification of tuning activities
• Twenty Four hour Oracle emergency support
• Reporting and resolving all serious Oracle alert log messages
• Free use of the BC TablePack, ServerPack and AuditPack services
• Quick response emergency support for production database outages
• Four hours of free remote DBA support every month. You can use these free hours for any DBA activity, including database analysis, system design, production migrations or personal mentoring.

For more information, please visit www.remote-dba.cc or email info@remote-dba.net.