Write a report on/ answer the following: Users of your database application comp

Question

Write a report on/ answer the following:

Users of your database application complain of slow response time for queries. Upon analysis,you realize that the queries are being reparsed because they are aged out quickly from SGA.

1. As an administrator what you do to reduce the reparsing of queries?

2. You tried to access Oracle enterprise manager (Database Control) from the web browser and you got error message that page not found, assume that the database control configured properly and you entered the correct URL. Explain the possible reason and suggest a solutions to make the page accessible.

3. Assume that, the data to process is huge (more than the size SQL work area). How oracleHandles the processing?

4. Explain in detail the Advanced Query Optimization Techniques.

5. How do you detect and correct the corruption in data files using RMAN.

Explanation / Answer

1. As an administrator, in order to reduce the reparsing of queries one must modify the library cache.

The Library Cache is a piece of memory within the SGA that Oracle uses in order to store SQL Statements. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. If for example I do a insert into city (name, abbr) values (‘India’, 'IN'); the Library cache checks if there is a table named city having the columns name and abbr. As an additional task, the Library Cache also checks if the user's privileges are sufficient to execute the statement.

In a similar way, the Library Cache also caches PL/SQL Statements and Objects. Due to lack of shared pool RAM in SGA, there might be high library cache reloads, high row cache reloads and shared pool latch contention because of which we may get the error: "ORA-04031: Out of shared pool memory". So this is the reason we will need to modify the Library Cache as an administrator to reduce reparsing of queries.

2)

The "page not found" error is common on OEM when a PATH variable is missing, or when an OEM component is not properly installed. But in this case it is mentioned that OEM component is properly configured, so in order to fix the "page not found" error on Oracle OEM, we can follow the below steps:

3)

For complex queries, a big portion of the runtime area known as SQL Work Area is dedicated to work areas allocated by memory-intensive operators such as the following:

·         Sort-based operators

·         Hash-join

·         Bitmap merge

·         Bitmap create

For example, a sort operator uses a work area to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area to build a hash table from its left input. If the amount of data to be processed by these two operators does not fit into a work area, then the input data is divided into smaller pieces. This allows some data pieces to be processed in memory while the rest are spilled to temporary disk storage to be processed later. Although bitmap operators do not spill to disk when their associated work area is too small, their complexity is inversely proportional to the size of their work area. Thus, these operators run faster with larger work area.

The size of a work area can be controlled and tuned. Generally, bigger database areas can significantly improve the performance of a particular operator at the cost of higher memory consumption. Optimally, the size of a work area is big enough such to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases, because part of the input data must be spilled to temporary disk storage. In the extreme case, if the size of a work area is far too small compared to the input data size, multiple passes over the data pieces must be performed. This can dramatically increase the response time of the operator.

4)

Advanced Query Optimization is essential in generating a suitable access plan for a query. This means a large query which runs in a time span of an hour might take 5-10 minutes to execute. Once a query is parsed, the query optimizer analyzes it and decides on an access plan that computes the result using as few resources as possible. Optimization begins just before execution. If cursors are being used in any application, optimization commences when the cursor is opened.

Unlike many other commercial database systems, SQL Anywhere usually optimizes each statement just before executing it. Because SQL Anywhere performs just-in-time optimization of each statement, the optimizer has access to the values of host and stored procedure variables, which allows for better selectivity estimation analysis. In addition, just-in-time optimization allows the optimizer to adjust its choices based on the statistics saved after previous query executions.

To operate efficiently, SQL Anywhere rewrites your queries into semantically equivalent, but syntactically different, forms. SQL Anywhere performs many different rewrite operations. If you read the access plans, you frequently find that they do not correspond to a literal interpretation of your original statement. For example, to make your SQL statements more efficient, the optimizer tries as much as possible to rewrite subqueries with joins.

5) RMAN is Oracle Recovery Manager. Below is the detailed explaination on how to Detect and Correct the corrupted data files using RMAN:

RMAN (BACKUP VALIDATE, RESTORE VALIDATE, VALIDATE):

Oracle Recovery Manager (RMAN) can validate the database using the BACKUP VALIDATE command. The command is as follows:

The process outputs the same information you would see during a backup, but no backup is created. Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view, as well as in the RMAN output.

By default the command only checks for physical corruption. Add the CHECK LOGICAL clause to include checks for logical corruption.

RMAN can validate the contents of backup files using the RESTORE VALIDATE command.

In a similar way to the BACKUP VALIDATE command, the RESTORE VALIDATE command mimics the process of a restore, without actually performing the restore.

Prior to 11g, the straight VALIDATE command could only be used to validate backup related files. In Oracle 11g onward, the VALIDATE command can also validate data-files, table-spaces or the whole database, it can be used in place of the BACKUP VALIDATE command as follows:

Any block corruptions are visible in the V$DATABASE_BLOCK_CORRUPTION view. You can identify the objects containing a corrupt block using a query as below:

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.