How Developers Can Help DBAs Identify and Prevent SQL Performance Problems
Database performance should not be viewed as the responsibility of the DBA alone. Developers often understand the application, business processes, and SQL better than anyone else, while DBAs understand database behavior, optimizer decisions, statistics, indexing, and system resources.
When developers and DBAs share some basic performance information, many problems can be identified before they become production incidents.
One of the most useful skills a developer can have is the ability to read a basic Oracle SQL execution plan.
An execution plan shows how Oracle intends to retrieve the data required by a SQL statement.
For example, Oracle might choose:
Developers do not necessarily need to understand every optimizer operation. They should, however, become familiar enough with plans to recognize major differences.
For example, suppose a query normally uses an index:
INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
After a change, the same SQL begins using:
TABLE ACCESS FULL
That does not automatically mean the new plan is wrong. However, it is something worth investigating, particularly if the table contains millions of rows.
The developer can bring this information to the DBA and say:
"This query previously used an index and now appears to be performing a full table scan."
That gives the DBA a much better starting point than simply reporting:
"The database is slow."
Oracle's optimizer makes many of its decisions based upon statistics.
One of the simplest comparisons developers and DBAs can make involves the number of rows Oracle believes an object contains.
For example:
SELECT owner,
table_name,
num_rows,
last_analyzed
FROM dba_tables
WHERE owner = 'APPLICATION_OWNER'
AND table_name = 'ORDERS';
The NUM_ROWS value represents the approximate number of rows recorded when statistics were gathered.
This can be compared with the actual table:
SELECT COUNT(*)
FROM application_owner.orders;
On very large production tables, running COUNT(*) may itself be expensive, so it should not be performed casually. DBAs may use other techniques to estimate the current row count.
The important concept is the comparison.
For example:
Optimizer Statistics NUM_ROWS: 250,000
Approximate Actual Rows: 12,000,000
Last Analyzed: 45 days ago
This would immediately raise questions about whether the optimizer statistics accurately represent the current data.
Developers should also understand the importance of LAST_ANALYZED.
For example:
SELECT owner,
table_name,
num_rows,
blocks,
last_analyzed
FROM dba_tables
WHERE owner = 'APPLICATION_OWNER'
ORDER BY last_analyzed;
If an application rapidly loads or deletes large amounts of data, statistics can become unrepresentative of the current data.
That can cause Oracle to make decisions based upon an inaccurate picture of the table.
Developers can help considerably by telling DBAs when an application performs activities such as:
This information can help the DBA determine whether statistics need to be gathered differently or whether the normal automatic statistics process is sufficient.
Tables are not the only objects that matter.
Developers and DBAs can also review indexes associated with important SQL.
For example:
SELECT owner,
index_name,
table_name,
num_rows,
distinct_keys,
clustering_factor,
last_analyzed
FROM dba_indexes
WHERE table_owner = 'APPLICATION_OWNER'
AND table_name = 'ORDERS';
Again, the developer does not need to become an Oracle optimizer expert.
The objective is simply to collect enough information so that developers and DBAs can have a meaningful discussion about what Oracle is seeing.
Another very useful technique is to compare execution plans from periods when an application performs well with periods when it performs poorly.
Suppose SQL normally finishes in two seconds but occasionally takes 90 seconds.
Rather than immediately changing the SQL, the DBA and developer should determine whether the execution plan changed.
Useful information includes:
If the fast executions use one PLAN_HASH_VALUE and the slow executions use another, there may be a plan stability issue.
This type of evidence can dramatically shorten the troubleshooting process.
One of the best things a development team can provide a DBA is a collection of SQL statements that represent the application's important workloads.
These might include:
The objective is not necessarily to collect every SQL statement in the application.
Instead, developers and DBAs can identify a manageable set of representative SQL that describes the important workload.
For example:
Project: Customer Billing
SQL-01 Retrieve customer account
SQL-02 Calculate current balance
SQL-03 Retrieve invoice history
SQL-04 Generate monthly statement
SQL-05 Post customer payment
SQL-06 Nightly billing calculation
SQL-07 Aging report
These statements can effectively become a performance test set for the project.
Once representative SQL has been identified, the team can establish a basic performance baseline.
The baseline might contain information such as:
SQL Normal Elapsed Time Plan Hash Value Buffer Gets Typical Rows
SQL-01 0.10 sec 123456789 1,250 1
SQL-02 0.35 sec 345678912 8,500 12
SQL-03 1.20 sec 567891234 45,000 350
SQL-04 8.50 sec 789123456 850,000 12,000
The exact numbers are less important than having a known point of comparison.
Later, if SQL-03 suddenly takes 20 seconds instead of approximately one second, the DBA has something concrete to investigate.
The team can ask:
Without a baseline, everyone is trying to determine whether the current behavior is actually abnormal.
It is important to distinguish between a performance baseline and an Oracle SQL Plan Baseline.
A performance baseline is simply documented historical behavior.
For example:
This SQL normally executes in 1.5 seconds and performs approximately
20,000 logical reads.
An Oracle SQL Plan Baseline is an Oracle optimizer feature that can be used to control or stabilize which execution plans Oracle considers acceptable.
The first should generally come before the second.
The DBA should understand how the SQL normally behaves before deciding whether a particular execution plan should be preserved.
Representative SQL can also be used when making significant changes.
Application Release
|
v
Run Representative SQL
|
v
Compare With Baseline
|
+------ Execution Time
+------ Execution Plan
+------ Buffer Gets
+------ Row Estimates
+------ Object Statistics
|
v
Investigate Significant Differences
|
v
Release to Production
This can be particularly valuable before:
Instead of waiting until users complain, the team can identify regressions during testing.
A DBA looking at the database may see:
SQL_ID: abc123
Executions: 150,000
Average Time: 3.2 seconds
But the DBA may not know what that SQL actually does for the business.
The developer might immediately recognize it as:
"That SQL runs every time a customer opens the order screen."
That changes the conversation considerably.
Similarly, a developer may know:
"This table normally has 100,000 rows, but after month-end processing it temporarily contains 15 million."
That is extremely valuable information for a DBA investigating optimizer behavior.
The DBA may see things developers normally cannot.
For example:
Application
|
v
SQL Statement
|
v
Optimizer
|
+---- Object Statistics
+---- Histograms
+---- Index Statistics
+---- System Statistics
+---- Optimizer Parameters
|
v
Execution Plan
|
v
Database Resources
|
+---- CPU
+---- Memory
+---- I/O
+---- Concurrency
+---- Locks
A SQL statement that appears perfectly reasonable from the application's perspective may behave very differently depending upon the database environment.
That is why performance troubleshooting works best when developers and DBAs investigate together.
A project could adopt a very simple process.
Developers identify the SQL that represents the application's critical workload.
DBAs capture execution plans and basic performance statistics.
Record important tables, indexes, approximate row counts, and statistics information.
Document approximate execution times and resource usage.
Run representative SQL after application, database, statistics, or infrastructure changes.
Developers and DBAs investigate significant regressions together.
Where justified, the DBA can evaluate Oracle capabilities such as SQL Plan Baselines, SQL Profiles, SQL Patches, or other optimizer-management techniques.
Developers do not need to become Oracle DBAs.
DBAs do not need to become application developers.
The objective is to create a common language.
Instead of:
Developer: "The database is slow."
DBA: "The database looks fine."
The conversation becomes:
Developer:
"This SQL normally runs in about two seconds.
It is now taking approximately 40 seconds.
The plan hash value changed.
The ORDERS table has grown significantly,
and the statistics appear to have been gathered before the latest load."
DBA:
"Now we have something specific to investigate."
That type of collaboration can dramatically reduce the amount of time required to diagnose performance problems.
SQL performance is rarely improved by treating the application and the database as completely separate systems.
Developers understand what the application is trying to accomplish.
DBAs understand how Oracle is attempting to accomplish it.
By teaching developers to understand basic execution plans, compare object statistics, recognize changes in row counts, identify important SQL, and establish representative workloads, the development and DBA teams can build a shared performance baseline.
That baseline gives both teams something extremely valuable:
Evidence of what good performance looks like before something goes wrong.
That changes SQL tuning from a reactive exercise into a cooperative and proactive process.
--------------------------------------------------------
Oracle Database 26ai Free Edition Released
Complete installation guide, new AI features,
vector search, and Docker deployment.
--------------------------------------------------------
https://www.oracle.com/downloads/
Oracle Database 21.3.0.0.0
GoldenGate Downloads
https://docs.oracle.com/en/database/goldengate/index.html
Oracle Documentation
https://docs.oracle.com/en/database/index.html
When developing software applications, it's often considered best practice for application vendors to avoid writing code that is specific to a single type of database. Here's why:
1. Portability: One of the primary reasons is to ensure that the application remains database-agnostic. This means that the application can be easily ported to work with different database systems without requiring major code changes.
2. Broader Market Appeal: By keeping applications database-agnostic, vendors can cater to a wider range of customers. Different organizations might have different database preferences based on their existing infrastructure, licensing costs, expertise, and other factors. If an application only works with one type of database, it could limit its potential customer base.
3. Future-Proofing: Technologies and industry trends change. Today's leading database system might not be the leader in a few years. By keeping the code independent of a specific database, vendors ensure that their applications remain relevant and adaptable to future technological shifts.
4. Maintenance: Writing code for a specific database system can introduce complexities in the codebase. If a vendor supports multiple database-specific code paths, it can increase the maintenance overhead, as each database-specific implementation might need updates, bug fixes, or enhancements.
5. Licensing and Cost Concerns: Tying an application to a specific database can introduce licensing complexities and potential additional costs for the end-users. By remaining neutral, application vendors give customers the flexibility to choose a database that fits their budget and licensing preferences.
6. Avoiding Vendor Lock-in: Relying heavily on one database vendor's specific features can lead to a situation known as vendor lock-in. This is where an organization becomes overly dependent on a single supplier's products, leading to reduced flexibility and potentially higher costs in the long run.
7. Use of ORM (Object-Relational Mapping) Libraries: Modern applications often use ORM libraries, such as Hibernate in Java or Entity Framework in .NET, which provide an abstraction over the database. These libraries allow developers to work with databases in an object-oriented manner, often without needing to write SQL code directly. This not only makes the code more database-independent but also reduces the need for database-specific optimizations in many cases.
8. Performance Considerations: While there's a belief that database-specific code can be optimized better, it's often the case that generalized solutions, when done right, can offer performance that is more than adequate for most applications.
9. Database Features: Most relational databases support the SQL standard to some extent. While there are proprietary extensions and features in each database system, most common tasks can be achieved using standard SQL, negating the need for database-specific code.
10. Complexity: Introducing database-specific logic can increase the complexity of deployment, configuration, and testing. By keeping the codebase consistent across all supported databases, developers can ensure a smoother user experience.
In summary, while there are occasional valid reasons for writing database-specific code, for most application vendors, the benefits of maintaining a database-agnostic stance often outweigh the potential advantages of optimizing for a specific database system.
If you're working in an Oracle Real Application Clusters (RAC) environment and you have multiple instances for a single database, each instance has its own unique name. When you want to modify parameters specific to an instance in a shared SPFILE, you can use the `sid` qualifier in the parameter name.
Here's how you can modify the shared SPFILE for a specific instance:
1. **Backup the SPFILE**:
Always backup the SPFILE before making any changes.
```sql
CREATE PFILE='/path_to_backup/init.ora' FROM SPFILE;
```
2. **Connect to the database**:
Start SQL*Plus and connect to the database as a user with SYSDBA privileges.
```bash
sqlplus / as sysdba
```
3. **Modify the SPFILE for a specific instance**:
If you want to modify or add a parameter specific to an instance, use the `sid` qualifier. The format is `parameter_name.sid_instance_name`.
For example, let's assume you have an instance named "ORCL1" and you want to set the `pga_aggregate_target` parameter only for that instance to 200M in the shared SPFILE:
```sql
ALTER SYSTEM SET pga_aggregate_target.ORCL1=200M SCOPE=SPFILE;
```
4. **Restart the specific instance**:
To apply the changes to the specific instance, restart it. If you're connected to the instance you're changing:
```sql
SHUTDOWN IMMEDIATE;
STARTUP;
```
If you're in a RAC environment and connected to a different instance, you'll need to use Oracle's cluster management tools or scripts specific to your setup to restart the desired instance.
5. **Verify your changes**:
Connect to the instance for which you made the changes and verify by querying the `V$PARAMETER` view:
```sql
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'parameter_name%';
```
Always make sure to thoroughly test any changes in a development or test environment before applying to production. This ensures that you understand the impact of your changes and can avoid potential issues.
This is a GIT guide
sysresv
The sysresv command in Linux can be used to view the currently allocated IPC resources for shared memory. The sysresv utility has been provided by Oracle since version 8i.
MAA GoldenGate Performance
https://www.oracle.com/technetwork/database/availability/maa-gg-performance-1969630.pdf