Sql Server Supported Compatibility Levels

Product	Database Engine version	Default compatibility level designation	Supported compatibility level values
Azure SQL Database	16	150	160, 150, 140, 130, 120, 110, 100
Azure SQL Managed Instance	16	150	160, 150, 140, 130, 120, 110, 100
SQL Server 2022 (16.x)	16	160	160, 150, 140, 130, 120, 110, 100
SQL Server 2019 (15.x)	15	150	150, 140, 130, 120, 110, 100
SQL Server 2017 (14.x)	14	140	140, 130, 120, 110, 100
SQL Server 2016 (13.x)	13	130	130, 120, 110, 100
SQL Server 2014 (12.x)	12	120	120, 110, 100
SQL Server 2012 (11.x)	11	110	110, 100, 90
SQL Server 2008 R2 (10.50.x)	10.5	100	100, 90, 80
SQL Server 2008 (10.0.x)	10	100	100, 90, 80
SQL Server 2005 (9.x)	9	90	90, 80
SQL Server 2000 (8.x)	8	80	80

Configuration options for SQL Plan Management (SPM)

The DBMS_SPM.CONFIGURE package provides various configuration options for SQL Plan Management (SPM). It allows you to control different aspects of plan capture, plan selection, and plan retention.
Here are the few details and example commands for using DBMS_SPM.CONFIGURE:


1.Automatic Plan Capture Configuration:
   
  AUTO_CAPTURE: Specifies whether automatic plan capture is enabled for the entire database.

  
  -- Enable automatic plan capture
  
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE', 'TRUE');

  -- Disable automatic plan capture
  
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE', 'FALSE');


2.AUTO_CAPTURE_PDB_STATS: Specifies whether automatic plan capture is enabled for pluggable databases (PDBs) individually.

    
 -- Enable automatic plan capture for PDBs
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PDB_STATS', 'TRUE');
    
    
 -- Disable automatic plan capture for PDBs
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PDB_STATS', 'FALSE');

   
3.Automatic Plan Synchronization Configuration:


 AUTO_PURGE: Specifies whether to automatically purge plans that are no longer accepted or used.
  
 -- Enable automatic plan purging
EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE', 'TRUE');


 -- Disable automatic plan purging
EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE', 'FALSE');



4.AUTO_PURGE_CANCELED_PLANS: Specifies whether to automatically purge plans that have been canceled.
    
 -- Enable automatic purging of canceled plans
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE_CANCELED_PLANS', 'TRUE');
    
    
 -- Disable automatic purging of canceled plans
    
 EXEC DBMS_SPM.CONFIGURE('AUTO_PURGE_CANCELED_PLANS', 'FALSE');



5.Optimizer Mode Configuration:
    
 OPTIMIZER_MODE: Sets the optimizer mode to be used for selecting plans. Options are 'ALL_ROWS', 'FIRST_ROWS', or 'CHOOSE'.

    
 -- Set optimizer mode to 'ALL_ROWS'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'ALL_ROWS');
    
    
 -- Set optimizer mode to 'FIRST_ROWS'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'FIRST_ROWS');
    
    
 -- Set optimizer mode to 'CHOOSE'
    
 EXEC DBMS_SPM.CONFIGURE('OPTIMIZER_MODE', 'CHOOSE');


 
6.Plan Retention Configuration:
    
 PLAN_RETENTION_ENABLED: Specifies whether plan retention is enabled.

  
 -- Enable plan retention

 EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_ENABLED', 'TRUE');
 

-- Disable plan retention
 
EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_ENABLED', 'FALSE');


 
7.PLAN_RETENTION_WEEKS: Sets the number of weeks that plans should be retained in the SQL plan baseline.
    
  -- Set plan retention to 26 weeks
    
  EXEC DBMS_SPM.CONFIGURE('PLAN_RETENTION_WEEKS', 26);
  
 
8.Verification and Reporting Configuration:

  
  VERIFICATION_ENABLED: Specifies whether plan verification is enabled.
  
  -- Enable plan verification

  EXEC DBMS_SPM.CONFIGURE('VERIFICATION_ENABLED', 'TRUE');


  -- Disable plan verification
  
EXEC DBMS_SPM.CONFIGURE('VERIFICATION_ENABLED', 'FALSE');



9.VERIFICATION_RATE: Sets the rate at which SQL execution plans are verified.

  -- Set verification rate to 50%
o    
  EXEC DBMS_SPM.CONFIGURE('VERIFICATION_RATE', 50);

 
10.SPACE_BUDGET_PERCENT: Parameter specifies the maximum percentage of the shared pool that can be used for storing SQL plan baselines.
  -- Set space usage to 10%
  
EXEC DBMS_SPM.CONFIGURE('space_budget_percent', 10);

Move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database

To move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database, you can follow these steps:

 

Get lob Segment Details:

SELECT table_name, column_name, segment_name, tablespace_name

FROM dba_lobs

WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

 

Move to other tablespace:

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace);

Replace 'your_schema' with the schema name, 'your_table' with the table name, 'column_name' with the name of the LOB column, and 'new_tablespace' with the name of the new tablespace where you want to move the LOB segment.

After the LOB segment has been moved, you can verify

SELECT table_name, column_name, segment_name, tablespace_name  FROM dba_lobs  WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

Ensure that the tablespace_name column shows the name of the new tablespace where the LOB segment resides.

 

Note: Moving a LOB segment is a resource-intensive operation, and it should be performed during a maintenance window or a period of low database activity to minimize impact. Additionally, take appropriate backups before making any changes to critical database objects.

 

Fetch First Rows feature in Oracle

FETCH FIRST feature is known as the "ROWNUM" pseudocolumn or the "FETCH FIRST n ROWS ONLY" clause. It allows you to limit the number of rows returned in a query result. Here's how you can use it:

Using ROWNUM pseudocolumn:
SELECT *
FROM table_name
WHERE ROWNUM <= n;

In this example, replace table_name with the name of the table you're querying, and n with the desired number of rows you want to fetch.

Note: When using the ROWNUM pseudocolumn, it's essential to apply the condition on ROWNUM after any necessary ordering or filtering, as ROWNUM is assigned to rows before ordering is applied.

Using FETCH FIRST n ROWS ONLY clause (starting from Oracle 12c):
SELECT *
FROM table_name
ORDER BY column_name
FETCH FIRST n ROWS ONLY;

In this example, replace table_name with the name of the table you're querying, column_name with the column you want to order by, and n with the desired number of rows you want to fetch.

The FETCH FIRST clause allows you to specify an ORDER BY clause, ensuring consistent results when retrieving a limited number of rows.

Performance benefits of using the "FETCH FIRST":
Reduced Data Transfer: By limiting the number of rows fetched from the database, the "FETCH FIRST" feature reduces the amount of data transferred between the database server and the client application. This can significantly improve query performance, especially when dealing with large result sets.
 
Query Optimization: The "FETCH FIRST" feature allows the Oracle optimizer to optimize the query execution plan based on the specified row limit. It enables the optimizer to choose more efficient access paths, such as using an index scan or a fast full scan, instead of performing a full table scan when only a limited number of rows are required.
 
Faster Response Time: By fetching only the necessary rows, the "FETCH FIRST" feature can reduce the time it takes to process and return query results. This can lead to faster response times for queries, improving the overall user experience.
 
Efficient Pagination: When implementing pagination functionality, the "FETCH FIRST" feature can be particularly useful. It allows you to retrieve specific pages of data efficiently by combining the "FETCH FIRST" clause with appropriate offset values. This can enhance performance when displaying paginated results in applications.
 
Improved Scalability: By limiting the number of rows fetched, the "FETCH FIRST" feature helps in optimizing resource utilization. It reduces memory consumption and can improve the scalability of the application by efficiently handling large result sets.  
       

Microservice architecture Vs monolithic architecture

Microservice architecture and monolithic architecture are two different approaches to software development.

Monolithic architecture is a traditional approach where the entire application is built as a single, self-contained unit. All the functionalities of the application are tightly integrated and dependent on each other. This approach can be simple to develop and deploy, but it can become complex and difficult to maintain as the application grows and changes over time.

 

On the other hand, Microservice architecture is an approach where the application is broken down into smaller, independent services that can communicate with each other through APIs. Each microservice is responsible for a specific business capability and can be developed, deployed, and scaled independently. This approach provides flexibility, scalability, and allows for faster innovation and release cycles. However, it can also add complexity in terms of managing the communication and coordination between the microservices.

Main differences between microservice architecture and monolithic architecture are:

• Monolithic architecture is a single, self-contained unit while microservice architecture is composed of smaller, independent services.
• In monolithic architecture, all functionalities are tightly integrated and dependent on each other while in microservice architecture, each service is responsible for a specific business capability.
• Monolithic architecture can be simpler to develop and deploy but can become complex and difficult to maintain while microservice architecture provides flexibility, scalability, and faster innovation but can add complexity in terms of managing communication and coordination between services.

ORA-00979: not a GROUP BY expression

ORA-00979 error in Oracle usually occurs when you try to execute a SQL statement that includes a GROUP BY clause, but one or more non-aggregated columns in the SELECT clause are not included in the GROUP BY clause.

Here's an example that demonstrates the issue:

SELECT dept_name, employee_name, AVG(salary)
FROM employees
GROUP BY dept_name;

This query calculates the average salary for each department in the employees table, but it includes the non-aggregated employee_name column in the SELECT clause without including it in the GROUP BY clause. This will result in the ORA-00979 error, because employee_name is not included in the GROUP BY clause and it's not part of an aggregate function.

To fix this error, you need to include all non-aggregated columns in the SELECT clause in the GROUP BY clause as well. Here's the corrected query:

SELECT dept_name, employee_name, AVG(salary)
FROM employees
GROUP BY dept_name, employee_name;

In this query, we include the employee_name column in the GROUP BY clause along with the dept_name column. This ensures that all non-aggregated columns in the SELECT clause are included in the GROUP BY clause, and the AVG function can be calculated for each combination of dept_name and employee_name.

HANDLECOLLISIONS in GoldenGate

When data is replicated from a source database to a target database, conflicts can occur if the same data is modified in both databases simultaneously. For example, if a row is updated in the source database and the same row is also updated in the target database, a conflict arises."HANDLECOLLISIONS" parameter allows you to specify how these conflicts should be handled.

The "HANDLECOLLISIONS" parameter can take various values to determine the resolution strategy for conflicts. Some common values include:

ABORT: This value causes Oracle GoldenGate to abort the Replicat process when a conflict is encountered. This might be appropriate if you want to manually address conflicts before continuing replication.

SKIP: This value instructs Oracle GoldenGate to skip the conflicting transaction and continue replication with subsequent transactions.

OVERWRITE: This value allows the target transaction to overwrite the source transaction when a conflict occurs.

WARNING: This value generates a warning message and continues replication with one of the transactions.

DISCARD: This value discards the conflicting transaction without applying it to the target.

FAILONCOLLISION: This value causes Replicat to fail when a collision is encountered.

The appropriate value to use depends on your business logic and the importance of maintaining data integrity. You can set the "HANDLECOLLISIONS" parameter in your Oracle GoldenGate configuration file for the Replicat process. Here's an example:

HANDLECOLLISIONS OVERWRITE;

 

Best practices for improving delete performance in Oracle

Here are some ways to improve delete query performance in Oracle:

1.DELETE statement with the WHERE clause: The DELETE statement with a WHERE clause is the most efficient way to delete rows from a table. The WHERE clause can be used to specify the conditions that must be met for a row to be deleted, which can reduce the number of rows that need to be deleted.

2.Batch processing: Instead of deleting all records in one large transaction, consider deleting records in smaller batches using a loop. This can help reduce the amount of undo and redo generated by the delete operation and improve performance.

Use COMMIT_INTERVAL parameter

3.Use the TRUNCATE statement: If you need to delete all records from a table, consider using the TRUNCATE statement instead of the DELETE statement. TRUNCATE is a faster operation because it does not generate undo and redo, and it resets the high-water mark of the table.

4.Disable indexes and constraints: If you have indexes or constraints on the table being deleted, consider disabling them temporarily during the delete operation. This can improve performance because the indexes and constraints do not need to be updated during the delete operation. Just remember to re-enable them once the delete operation is complete.

5.Partitioning: If the table being deleted is partitioned, consider using partitioning to delete only the partitions that need to be deleted. This can help reduce the amount of data that needs to be deleted and improve performance.

6.NOLOGGING option: If you do not need to recover the deleted data, consider using the NOLOGGING option during the delete operation. This can help reduce the amount of undo and redo generated by the delete operation and improve performance.

7.Parallel Processing: If your system has multiple CPUs, consider using parallel processing to delete the data. This can help divide the workload among the CPUs and improve performance.

By following these best practices, We should be able to improve delete performance in Oracle. However, it is important to test the performance of delete operation after implementing any of these optimizations to ensure that it is improving performance as expected.

Shared Server Vs Dedicated Server in Oracle

In this blog post, we will discuss the differences between shared server and dedicated server and help you determine which option is best for your database.

Dedicated Server:

In a dedicated server configuration, each user is allocated a dedicated server process. The server process is dedicated to that user for the duration of their session. This configuration provides a higher degree of control over the database resources and eliminates the need for context switching. Context switching occurs when the operating system switches from one process to another. With a dedicated server configuration, the server process is always available, and there is no need for context switching.

Dedicated servers have several advantages:

1. Predictable Performance: As each user has their dedicated server process, there is a high degree of predictability in terms of database performance. Dedicated servers provide consistent performance regardless of the number of users or the complexity of the queries.

2. Resource Allocation: Dedicated servers allow for the allocation of specific resources to each user, such as CPU and memory. This ensures that each user has the resources they need to perform their tasks efficiently.

3. Simplified Tuning: In a dedicated server configuration, performance tuning is simplified as each user has their dedicated server process. It is easier to isolate and tune specific server processes.

However, dedicated servers also have some disadvantages:

1. Cost: Dedicated servers require more hardware resources, which can increase the overall cost of the database.

2. Scalability: As the number of users increases, adding more dedicated servers can become expensive and may not be scalable.

Shared Server:

In a shared server configuration, multiple users share a set of server processes. When a user connects to the database, they are assigned a dispatcher process. The dispatcher process manages the user's connection and directs SQL requests to a shared server process. The shared server process is then responsible for processing the user's SQL request.

Shared servers have several advantages:

1. Resource Efficiency: Shared servers are more resource-efficient than dedicated servers. The server processes are shared among multiple users, which reduces the overall resource requirements.

2. Scalability: Shared servers are highly scalable, and additional server processes can be added as the number of users increases.

3. Cost: Shared servers require fewer hardware resources, which can reduce the overall cost of the database.

However, shared servers also have some disadvantages:

1. Performance: As server processes are shared among multiple users, there can be a decrease in performance, especially when many users are accessing the database simultaneously.

2. Complexity: Shared server configurations are more complex than dedicated server configurations. The need for dispatcher and shared server processes can make the configuration more challenging to manage.

Which One to Choose?

When deciding whether to use shared server or dedicated server architecture, there are several factors to consider. If you have a large number of client connections and want to conserve system resources, shared server architecture may be the better choice. If you have a smaller number of clients or need the best performance for individual clients, dedicated server architecture may be the better choice.

Ultimately, the choice between shared server and dedicated server architecture depends on your specific needs and workload. Both options have their own advantages and disadvantages, and it's important to carefully evaluate your requirements before making a decision.