Oracle: hash_area_size and sort_area_size

higher value for hash_area_size, though is desirable, does not affect the hashing performance in a big way. Reason could be that memory is only used for creating hashes and then the records are flushed to the temp segments (disk).

Whereas in case of sorting, due to complex logic performed for sorting all the records, data moves back and forth multiple time between memory and disk, thus requiring higher sort_area_size for minimizing these iterations. Temp space requirement also falls to a good extent.

One needs to set workarea_size_policy to MANUAL and specify the desired sort and hash area sizes. if hash_area_size is not defined then it defaults to 2*sort_area_size.

Solaris : Troubleshooting Memory & CPU Consumption

Use the following command to see the TOP 5 processes which are consuming system memory.
prstat -s size -n 5

output shows the resource statistics for each thread of a server application:

prstat -L -p 3295

Processes consuming the most CPU resource:

prstat -s cpu -a -n 8

PERL : diff two files

use strict;

open TXT1, "1.txt" or die "$!";
open TXT2, "2.txt" or die "$!";
my %diff;

$diff{$_}=1 while ();

while(){
 print unless $diff{$_};
}

close TXT2;
close TXT1;

Remove ^M from files transferred from Windows to Linux

Inside vi [in ESC mode] type:

:%s/^M//g (to make the ^M -> CTRL+V then CTRL+M)

Oracle ODBC : Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

While configuring DSN using Oracle ODBC driver, one might encounter error "Driver's SQLAllocHandle on SQL_HANDLE_ENV failed". Cause being, Permissions of the remote desktop software did not allow the user access via the remote desktop software.

Do the following to resolve the issue:

1. Select Administration Tools , Local Security Setting and Local Policy.
2. Then select "User Rights Assignment"
3. Double click on "Create Global Objects"
4. Select Add User or Group.
5. Make sure Object Types Group Box is checked.
6. Select Locations and highlight the name of the server you are working on (Not the Domain).
7. Enter "Remote Desktop Users" or "Everyone" (without the quotes) into the Object Names Box.
8. Select OK.
9. Add the same users to the group "Power User".
10. If this does not work, after step 8 a reboot may be required.

Oracle: Spooling Column of type Long

Set the following sqlplus options for spooling a long column. This will avoild truncation of lines to a particular width.

SQL> set trimspool on
SQL> set linesize 20000
SQL> set long 100000000
SQL> set longchunksize 2000
SQL> set pagesize 0

Oracle : Support for Very Large Memory (VLM) Configurations

Oracle Database for Windows supports Very Large Memory (VLM) configurations in Windows to access more than the 4 gigabyte (GB) of RAM traditionally available to Windows applications.

The requirements for taking advantage of this support are:

1. The computer on which Oracle Database is installed must have more than 4 GB of memory.

2. The operating system must be configured to take advantage of Physical Address Extensions (PAE) by adding the /PAE switch in boot.ini. See Microsoft Knowledge Base article Q268363 for instructions on modifying boot.ini to enable PAE.

3. It is advisable (though not necessary) to enable 4GT support by adding the /3GB parameter in boot.ini. See Microsoft Knowledge Base article Q171793 for additional requirements and instructions on modifying boot.ini to enable 4GT.

4. The user account under which Oracle Database runs (typically the LocalSystem account), must have the "Lock memory pages" Windows 2000 and Windows XP privilege.

5. USE_INDIRECT_DATA_BUFFERS=TRUE must be present in the initialization parameter file for the database instance that will use VLM support. If this parameter is not set, then Oracle Database 10g Release 1 (10.1) behaves in exactly the same way as previous releases.

6. Initialization parameters DB_BLOCK_BUFFERS and DB_BLOCK_SIZE must be set to values you have chosen for Oracle Database.

7. Registry parameter AWE_WINDOW_MEMORY must be created and set in the appropriate key for your Oracle home. This parameter is specified in bytes and has a default value of 1 GB. AWE_WINDOW_MEMORY tells Oracle Database how much of its 3 GB address space to reserve for mapping in database buffers.

8. Once this parameter is set, Oracle Database can be started and will function exactly the same as before except that more database buffers are available to the instance. In addition, disk I/O may be reduced because more Oracle Database data blocks can be cached in the System Global Area (SGA).

9. If DB_BLOCK_SIZE is large, however, the default AWE_WINDOW_MEMORY value of 1 GB may not be sufficient to start the database.

Reference Note:
http://download-west.oracle.com/docs/cd/B14117_01/win.101/b10113/architec.htm#sthref58