Tuning

[cb1] What will happen in SGA when we run UPDATE?

When a DML UPDATE statement is executed in Oracle, the following activities happen in the SGA (System Global Area):

1. The SQL statement is parsed and optimized by the SQL engine, and an execution plan is generated.
2. The execution plan is sent to the row source generator, which generates the rows to be updated.
3. The redo entries for the changes to be made are generated and stored in the redo log buffer.
4. The data blocks containing the rows to be updated are read into the buffer cache.
5. The rows are updated in the buffer cache.
6. The undo information for the changes is generated and stored in the undo tablespace.
7. If the changes involve any indexes, the index entries are updated accordingly.
8. The changes are committed to the database.
9. The redo log buffer is flushed to disk.
10. The updated data blocks and index entries are written to disk.

Overall, the SGA activities involved in an UPDATE statement are mostly related to reading, modifying, and writing data blocks in the buffer cache, generating and storing redo and undo information, and updating indexes. The exact details may vary depending on the specifics of the statement and the configuration of the database.

[cb2] Major difference between oracle 11g vs 12c as developer perspective?

As a developer, some major differences between Oracle 11g and 12c are:

1. Multitenant architecture: Oracle 12c introduced a new feature called "Multitenant Architecture" that allows multiple databases to share a single container database. This feature provides greater flexibility, efficiency, and cost savings compared to the traditional approach of managing multiple databases.
2. SQL pattern matching: Oracle 12c introduced support for SQL pattern matching, which allows developers to easily search for patterns in data using regular expressions. This feature provides powerful capabilities for data analysis and manipulation.
3. JSON support: Oracle 12c introduced native support for JSON (JavaScript Object Notation) data. This allows developers to store and manipulate JSON data directly in the database, eliminating the need for third-party tools or libraries.
4. In-memory column store: Oracle 12c introduced the In-Memory Column Store, which allows data to be stored and retrieved faster by storing data in memory instead of on disk. This feature provides significant performance improvements for analytical queries.
5. Improved security features: Oracle 12c introduced several new security features, including Privilege Analysis, Data Redaction, and Transparent Sensitive Data Protection. These features help to enhance the security of the database and protect sensitive data.
6. Automatic Data Optimization: Oracle 12c introduced the Automatic Data Optimization (ADO) feature, which allows developers to automate the process of moving data between different storage tiers based on its usage patterns. This helps to improve performance and reduce storage costs.

Overall, Oracle 12c provides many new features and improvements that make it a more powerful and efficient platform for developers compared to Oracle 11g.

[cb3] What is result cache in oracle?

1. Result cache in Oracle is a feature that allows frequently used SQL query results to be cached in memory, reducing the overhead of executing the query repeatedly.
2. When a SQL query is executed, Oracle checks if the result is already cached in the result cache. If the result is found in the cache, it is returned immediately without re-executing the query. If the result is not found in the cache, the query is executed as usual and the result is cached in memory for future use.
3. The result cache is implemented at the SQL statement level, which means that the same SQL statement with different parameters will result in different cached results. The cache is also automatically invalidated when the underlying data is modified, so cached results remain fresh and consistent.
4. Result cache is enabled by default in Oracle Database 11g and later versions. It can be configured at both the system level and the session level, allowing administrators to control the size of the cache and the caching behavior of specific SQL statements.
5. Using the result cache can significantly improve the performance of frequently executed SQL queries, especially those with complex logic or expensive computations. However, it's important to note that the result cache should not be used indiscriminately, as caching inappropriate or stale results can lead to incorrect query results.

[cb4] What is Cursor Sharing in Oracle? 

Cursor sharing is a feature in Oracle that allows multiple SQL statements to share a single cursor. The goal of cursor sharing is to reduce the number of unique SQL statements that need to be parsed and compiled by the database. This can improve performance by reducing the amount of work that the database needs to do to execute SQL statements.

In cursor sharing, the database checks whether a SQL statement is equivalent to another SQL statement that is already in the cursor cache. If a match is found, the new statement can reuse the existing cursor instead of creating a new one.

There are three types of cursor sharing in Oracle:

• Exact cursor sharing: This occurs when two SQL statements are identical in terms of their text and bind variable values. The database can reuse the same cursor for both statements.
• Similar cursor sharing: This occurs when two SQL statements are similar in terms of their text and bind variable values. The database can reuse the same cursor for both statements, but may need to perform additional checks to ensure that the statements are equivalent.
• Force cursor sharing: This forces the database to use cursor sharing for all SQL statements, even if it may not be the most optimal approach. This can be useful in cases where an application is experiencing performance issues due to excessive parsing and compilation of SQL statements.

Here's an example of cursor sharing in action:

Suppose we have a table called employees with columns id, last_name, department, and salary. We want to retrieve the names of all employees in a specific department whose salary is greater than a certain amount.

Without cursor sharing, the SQL statement might look like this:

SELECT last_name FROM employees

WHERE department = 'Sales' AND salary > 50000;

If this statement is executed multiple times with different department names, Oracle might create a separate cursor in the shared pool for each execution. This can lead to memory issues and decreased performance.

With cursor sharing, we can use bind variables to create a single cursor that can be shared across all executions. The statement would look like this:

SELECT last_name FROM employees

WHERE department = :dept_name AND salary > :salary_amt;

In this case, :dept_name and :salary_amt are bind variables that can be set to different values for each execution. The cursor can be reused for multiple executions, reducing the memory usage and improving performance.

[cb5] A function developed and being called in select statement what will be mechanism to optimize.

There are several mechanisms to optimize a function being called in a select statement:

• Avoid using functions that are not deterministic, as they may execute for each row returned in the select statement.
• Consider using inline views or subqueries to reduce the number of rows that the function needs to process.
• Use bind variables instead of literals in the function parameters to improve the performance of the function.
• Make sure that the function uses efficient algorithms and SQL statements to retrieve and process data.
• Consider using a materialized view that precomputes the result of the select statement that calls the function.
• Consider using parallelism to execute the select statement that calls the function faster.
• Overall, the best approach to optimize a function being called in a select statement depends on the specific use case and the characteristics of the data involved.

[cb6] Write a query to know active session on oracle database?

SELECT sid, serial#, username, status, osuser, machine, program

FROM v$session WHERE type = 'USER'