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:
- Live Instructor-led Online Sessions
- Unlimited FREE retake for next 1 Years
- Training Material with Hands-on Lab Exercises (8+ Activity guides + Bonus Guides)
- Recording of Live Interactive Session for Lifetime Access
- Email (Ticketing system) & WhatsApp Support
- Help in CV Preparation
- 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.
Rajesh Sahu says
This course look to be more on SQL tuning which is looks good.
Do you have any plan to add below topics
like on database and instance tuning.
Analyzing AWR and ASH reports.
Deep drive into Oracle wait events etc..
Rohit Pathak says
Hi Rajesh,
Thanks for show interest!
Yes, we are covering Analyzing AWR and ASH reports & Deep dive into Oracle wait for events etc under Module 7,8 & 9 you can also check this from https://k21academy.com/tuning03 & also we have dedicated hands-on based on this which is Activity Guide XII: Using Tuning tools (Manual Tuning).
Thanks & Regards,
Rohit(Teamk21)
Om Prakash Tiwari says
How much are you charging for this course ?
Rahul Dangayach says
Hi Om,
Please drop us an email at contact@k21academy.com and the team will help.
Thanks and Regards
Rahul Dangayach
Team K21Academy