SQL Trace and TKPROF

Understanding SQL Trace
Times when program is performing poorly, creating and examining a trace file is one of the best way to find what is causing the problem for poor performance.
Following is the list of some of the SQL Trace statictics that are generated in the trace file.

Parse, execute, and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Commits and Rollback

There are several ways by which you can turn on/off SQL Trace

Turn on
DBMS_SESSION.SET_SQL_TRACE(TRUE);
ALTER SESSION SET SQL_TRACE = TRUE;

Turn off
DBMS_SESSION.SET_SQL_TRACE(FALSE);
ALTER SESSION SET SQL_TRACE = FALSE;

Click here to set SQL Trace on a concurrent program.
The trace file is created in the udump directory.

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) --> Concurrent --> Program --> Define

Query for the concurrent program and check enable trace button.


Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.

select * from v$parameter
where name like '%user_dump_dest%'

The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Click for more info on TKPROF

Oracle Table Rename Syntax

There is a direct command to rename table.
The syntax is as follows

alter table
   table_name
rename to
   new_table_name;

Note: When the table is renamed the referenced object like PL/SQL, applications etc are not updated. Hence this may make those invalid. However the indexes, constraints etc are updated.

Dependent Value Parameter in Discoverer

In Discover 4i there was no way to filter the list of second parameter based on the value selected in first parameter.
Discoverer Version 10g has overcome this problem and allows to filter the List of Values based on another parameter. This is very similar to dependent value set that we create in Oracle Apps.
In the paramter screen(shown in the screen shot below) Select Option "Filter the List of Values based on the selection conditions, then select the parameter based on which the value need to be filtered. Click OK and you are all set to filter the list based on another parameter.

Not Required Parameter in Discoverer

The old version of Discover4i forced to enter values for all the paramters. If the value needs to be passed as NULL, then user had to enter either NULL or '' as the parameter value.

Discover 10g has got rid of this problem. The parameter can now be made a non-required parameter by checking "Require User to Enter a Value" checkbox as shown in the screenshot below.

Password Policy and profile options

On apps there are a lot of profile options that are useful in making apps passwords difficult to guess, the profiles are

1-Signon password failure limit
2-Signon Password Length
3-Signon Password No Reuse
4-Signon Password Hard to Guess

For the first one it means how many time can I try to access the system using wrong password. It is recommended to change this value to 3. The default value is null.

The second one to allow minimum password length. The default value is 5, it is recommended to make it 6 or 7.

The 3rd profile is for not allowing using same password again for specified number of days.

The default value for 4th profile option is No. Following are the password rules if the value is set to Yes
1) The password contains at least one letter and at least one number.
2) The password does not contain the username.
3) The password does not contain consecutively repeating characters.

Reference: Metalink Note 362663.1

Basic VI Editor Commands

Cursor Movements

h, j, k, l	Move left, Down, Up, Right
0	Move to First Character of the Line
$	Move to end of Line
w	Move to next word
CTRL + D	Move Page Down
CTRL + U	Move Page Up
G	Move to the End of File
1G or :1	Move to the Top of File
nG	Move to Line number n. (replace n is any integer value)
CTRL + G	Display current Line Number

 

Find a Text

/text	Find a text in Forward Direction
?text	Find a text in Reverse Direction
n	Repeat the previous Search

Note: text needs to be replaced by the string that needs tobe searched.

 

Editing, Inserting orDeleting a Text

a	Append after Cursor
A	Append to the end of Line
i	Insert before cursor
I	Insert from the Beginning of the line
o	Insert a new Line in the edit mode
cw	Change word
cc	Change whole line
C	Change text to end of line
dd	Delete Current Line
5dd	Delete current line and next 4
D	Delete from Current Position to the end of line
u	Undo last change
U	Restore Current Line

 

Copy(yanking) and Paste

yy	Yank a copy of Current Line
p	Insert previously yanked Line

 

Saving and Exiting

ZZ or :wq	Save and exit file
:q!	Exit without Saving
:w filename1	Save edited file as filename1. Similar to Save as.