Code Analyst DBMS Notes
Go Up to Code Analyst
Code Analyst is available for:
Rapid SQL utilizes debugger technology to capture the data for each line of executed code. For Oracle, you can use the debugger or using Oracle's supplied DBMS_Profiler package.
- Tip: For Oracle, you can specify to use the debugger or the DBMS_Profiler package on the Code Analyst Options.
The Code Analyst will step through each line of code, stopping to record data for those lines of code onto which a breakpoint can be issued. Some debuggers cannot capture time metrics for all lines of a stored procedure or function.
Procedures and functions that contain looping constructs will require more time to run. The additional amount of time needed to run is proportional to the number of iterations in the loop.
Contents
Microsoft SQL Server Data Captured by Code Analyst
In order to execute a Code Analyst session against a Microsoft SQL Server database, the SQL Server debugger must be installed and functioning properly. Please refer to IDERA SQL Debugger for details concerning set up.
Oracle Data Captured by Code Analyst
When using the PL/SQL Profiler, Oracle has documented an issue regarding extremely large times being returned by the profiler. The times are sometimes hundred times larger than the actual run time of the stored procedure or function. Oracle documents that this is a vendor/os problem rather than an Oracle problem, because the RDTSC instruction is reporting wrong time stamp counter. They indicate that they have seen this problem on some INTEL Pentium processors.
IBM DB2 LUW Data Captured by Code Analyst
Code Analyst utilizes the IBM Debugger when capturing time data.
The debugger is verified to run on IBM DB2 LUW version 7.2 and up. There is a known issue running version 7.2 with Fixpack 9.
DB2 has documented limitations on lines of code can be profiled.
The following are SQL statements that are NOT valid break point lines:
BEGIN
BEGIN
BEGIN NOT ATOMIC
BEGIN ATOMIC
CLOSE CURSOR
DECLARE cursor WITH RETURN FOR <sql statement>
DECLARE , var without default
DECLARE CONDITION (CONDITION) FOR SQLSTATE (VALUE) "..."
DECLARE CONTINUE HANDLER
DECLARE CURSOR
DECLARE EXIT HANDLER
DECLARE RESULT_SET_LOCATOR [VARYING]
DECLARE SQLSTATE
DECLARE SQLCODE (unless there is a default)
DECLARE UNDO HANDLER (unless they are entered)
DO
ELSE
END
END CASE
END IF
END FOR
END REPEAT
END WHILE
ITERATE
LEAVE
LOOP
OPEN CURSOR
REPEAT (as a keyword alone)
RESIGNAL
SIGNAL
THEN
labels, e.g. P1:
- Note: Code containing these statements will not have times associated with them.
Sybase ASE Data Captured by Code Analyst
Sybase has documented a problem with their debugger API. The problem involves reporting the wrong line number through the debugger. Because of this bug, Code Analyst may report back data for blank lines or lines that contain comments. Sybase has fixed this problem release 12.5.2 of the database. All procedures affected must be dropped and recreated in order to correct the problem.