Oracle Database Technology

--contributed by Manjunath Ulthi, It becomes a challenge to figure out the right amount of UNDO to be allocated to a database. Try this method : UndoSpace = [UR * (UPS * DBS)] + (DBS * 24) where, (UR) UNDO_RETENTION in seconds - an init parameter (UPS) Number of undo data blocks generated per second (DBS) Overhead varies based on extent and file size (db_block_size) Two (UPS) can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE. The third piece of the formula (DBS) requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT. The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds: SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat; The following query calculates the number of bytes needed: SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter

--contributed by Manjunath Ulthi, Senior DBA at Infosys Technologies limited 1 Introduction Problem: OCI client spins when machine uptime >= 249 days in Linux machines. Once the uptime crosses 249 days the database client hangs and leads to high CPU utilization and server hung state. The user will not be able to connect to the databases This is oracle known bug 4612267. Solution: This can be handled by applying the patch 4612267. The patch has been applied in the servers server126, server041 & server120 and tested. After the patch has been applied the client connections were normal after 249 days and all the operations of databases/applications were normal and didn’t encounter any problems. For further information refer the Metalink Notes Note: 338461.1: SQL*Plus With Instant Client 10.2.0.1 Hangs, When System Uptime Is More Than 248 Days Note:4612267.8 : Bug 4612267 - OCI client spins when machine uptime >= 249 days 2 General Instructions Procedure to apply the patch 1. Do

Back to square one, where I started this blog ? ... Nooooo ... Here I am not providing a step by step procedure for Oracle performance tuning, however a framework to help a DBA with tackling Oracle performance issues. This could be seen as a reference to understand where to start, what information to collect, where to proceed, how to conclude, etc. . Without much ado, let's go ahead. What's the problem ? Some of us (DBA's) get bad performance notice from users & we might tend to directly jump into trying to find a solution. Instead of trying that, a better way to do that is to first find out what that problem is & how is it seen by the user. First, start with these questions asking the requestor : 1 Is the whole application slow or only some modules are slow ? 2. When did you first notice this problem ? 3. Is the problem noticed all of sudden? Is it gradual ? 4. Is it recurring as you mentioned ? 5. Is there any recent upgrade to application ? 6.

For most of us, writing procedures, functions or say just a PL/SQL Code Block based on a given logic is wasy. The challenge lies in designing and placing the "Exception Handling" Block with the intent of capturing the correct line of Code that raised the exception so that one can them Zoom into the erroneous line and do make the necessary fixes. Now many of you would agree that this comes with experience 'n' ofcourse proactive thinking. Now conder the following Code Snippet, WHEN NO_DATA_FOUND THEN dbms_output.put_line (sqlerrm); END; Looks familiar...? But here's a catch - this code block will keep rescuing us as far as Error String is less than 255 Characters. (The limitation with put_line() is that it can capture a max of 255 Characters). Oracle has revolved this by its offering - DBMS_UTILITY .FORMAT_ERROR_STACK BEGIN error(); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); END; / ORA-009

Have you ever faced this ? ... You have your statspack report ready & are trying to work with your Statspack analyzer tool, which performs statspack report analysis & generates an html file, which is more understandable to non-DBA's too (with some charts, top-queries, recommendations, etc.) . At this moment, either your analysis tool gives up, or the website which you used for analysis, is unavailable (recently happened with oraperf, which we used) ! A solution to this, for a DBA, is to be able to read through the statspack report & identify the key areas, issues of importance . This place offers a simple statspack survival guide , to help understand the report. Hope this helps a DBA in speedy statspack analysis, instead of depending on a tool that is not working, or giving erratic results.