Oracle Database Technology

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...