Database Performance Tuning On- Premise & Cloud : Step by Step Hands-On Lab Exercise

If you are working as a DBA, Cloud DBA, Apps DBA and you are very much concern about the performance of your database either deployed on On-Premise or Cloud and you don’t know how to tune it to get the maximum performance of it, then don’t worry, in this blog post, I have covered the exact same tasks & Sneak Peek of What You should do to make your database perform flawlessly.

One of the most effective tuning methods is to maintain an established performance baseline that you can reference when a performance problem occurs. By easily identifying peak usage periods, you can set performance targets and have a firm understanding of your system capabilities. Consistently measuring existing performance provides exact expectations of how your database should be running, which is quite helpful when tuning any bottlenecks that will inevitably come up in the future.

Let’s see the below Guides which are required to administrate performance tuning tasks performed by a DBA’s, Cloud DBAs & APPS DBA.

1. Activity Guide I: Install Scott & Install Sample Schemas

The sample database schemas provide a common platform for examples in each release of the Oracle Database. The sample
schemas are a set of interlinked database schemas. This set provides an approach to complexity:

2. Activity Guide II:  Retrieve All Rows From a Given Table within Your Database & Examine the OE Schema

You need to write a query to retrieve all rows from a given table within your database. List of employee name in last-comma-first format, Write a query against the employee’s table to accomplish this task & Examine the OE schema and come up with the result set containing a list of female customers that have placed more than four orders.

3. Activity Guide III: Implement Bind Variables & Hard Parses & Soft Parses

As a DBA/Developer, you are getting an excessive amount of hard parsing for your SQL statements and want to
lower the number of SQL statements that go through the hard-parse process.

4. Activity Guide IV: Bind Variable Peeking & Adaptive Cursor Sharing

Bind Variable Peeking and Adaptive cursor sharing, Let us start with the setup script to demonstrate this phenomenon. We'll set up a very skewed set of data for this to work:

5. Activity Guide V: Bind Variables & CURSOR_SHARING

The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for
applications with many similar statements, setting CURSOR_SHARING to FORCE can sometimes significantly
improve cursor sharing. The replacement of literals with system-generated bind values can lead to reduced memory
usage, faster parses, and reduced latch contention. However, FORCE/SIMILAR is not meant to be a permanent
development solution.

6. Activity Guide VI: Cost Based Optimizer Fundamentals & Traditional Costing Model

Starting with Oracle you have the ability to view the estimated CPU, TEMP and I/O costs for every SQL execution plan step.  Oracle Corporation has noted that typical OLTP databases are becomingly increasingly CPU-bound and has provided the ability for the DBA to make the optimizer consider the CPU costs associated with each SQL execution step.

7. Activity Guide VII: Understanding Cardinality, Selectivity & Density

You must gather statistics on your table to get cardinality, density statistics.
Density is a statistic used by the Cost Based Optimizer to give selectivity estimates for columns where better
information is unavailable (i.e. from histograms etc.).
One of the most common performance issues DBAs encounter are bad execution plans and this is due to bad
estimates of cardinality by the optimizer. Generally, the bad cardinality is the result of non-representative table/column
stats, but it also may be due to data correlation or other factors. This is where it pays off to know and understand the
size and shape of the data. If the Optimizer still chooses a bad plan even with the correct cardinality estimates, it’s
time to place a call to Oracle Support as more in-depth debugging is likely required.

8. Activity Guide VIII: CPU Costing: Modes of System Statistics, Gather NOWORKLOAD & WORKLOAD System Statistics

System statistics were introduced in Oracle 9i to allow the cost-based optimizer to take into account that single-block
I/Os and multi-block I/Os should be treated differently in terms of costing and to include a CPU component in the cost
calculation. The system statistics tell the cost-based optimizer (CBO) among other things the time it takes to perform a single
block read request and a multi-block read request. Given this information, the optimizer ought to be able to come to
estimates that better fit the particular environment where the database is running on and additionally use an
appropriate costing for multi-block read requests that usually take longer than single block read requests. Given the
information about the time, it takes to perform the read requests the cost calculation can be turned into a time
estimate.

9. Activity Guide IX: Various Scan & Understanding Join Operations

In this activity, we will implement various Scan processes and various join Operations

10. Activity Guide X: Need for Histograms, Width-Balanced or Frequency Histograms, Height-balanced Histograms, Top N Frequency Hybrid

Need for Histograms, In order to estimate the selectivity (or in other words come up with optimal execution plan), CBO
takes various inputs in the form of statistics, configuration parameters etc. From a table’s column perspective, CBO collects the following statistics:

11. Activity Guide XI: Diagnostics

This module describes how you can use Enterprise Manager and Automatic Workload Repository to proactively tune your database. Many of the analysis tools used by the Tuning Advisor are exposed, allowing the database administrator to perform reactive tuning as well.

12. Activity Guide XII: Using Tuning tools (Manual Tuning)

In this activity guide, you will Use SQL Developer to Generate and display the Explain plan of a SQL query, Execute Autotrace Plan, Examine the effects of changing column order in a composite index, Use SQL Plan Baseline to associate a hinted execution plan with a hard-coded SQL statement

13. Activity Guide XIII: Automatic Tuning Advisor

The SQL Tuning Advisor analyzes high-volume SQL statements and offers tuning recommendations. It takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. It can run against any given SQL statement. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits. The recommendation or advice provided relates to the collection of statistics on objects, the creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.

14. Activity Guide XIV: SQL Baselines

This activity guide describes how you can manage your SQL execution plans in Oracle Database.

15. Activity Guide XV: SQL Performance Advisor

This activity describes how you can proactively tune your SQL using the SQL Performance Analyzer.

You can get all these Step by Step Activity Guide including Live Interactive Sessions (Theory) when you register for our Database Performance Tuning On-Premise & Cloud

If you register for our course, You’ll also get:

1. Live Instructor-led Online Sessions
2. Unlimited FREE retake for next 1 Years
3. Training Material with Hands-on Lab Exercises (8+ Activity guides + Bonus Guides)
4. Recording of Live Interactive Session for Lifetime Access
5. Email (Ticketing system) & WhatsApp Support
6. Help in CV Preparation
7. 100% Money Back Guarantee (If you attend sessions, practice and don’t get results, We’ll do full REFUND, check our refund policy

Have queries? Contact us at contact@k21academy.com or if you wish to speak then share your phone number and country code and a convenient time to speak.

Check out our Private Facebook Group for Cloud at https://k21academy.com/community for Learning New Things & Discussions related to Oracle Cloud.

Next Task

Want to move ahead in your career and want to get a higher Earning Job?
Get 7+ Courses for DBAs & Apps DBA in a bundle program and learn from the Industry’s best Experts.