Monthly Archives: October 2016

What is Swap?

Linux divides its physical RAM (random access memory) into chucks of memory called pages. Swapping is the process whereby a page of memory is copied to the preconfigured space on the hard disk, called swap space, to free up that page of memory. The combined sizes of the physical memory and the swap space is the amount of virtual memory available.

Swapping is necessary for two important reasons. First, when the system requires more memory than is physically available, the kernel swaps out less used pages and gives memory to the current application (process) that needs the memory immediately. Second, a significant number of the pages used by an application during its startup phase may only be used for initialization and then never used again. The system can swap out those pages and free the memory for other applications or even for the disk cache.

However, swapping does have a downside. Compared to memory, disks are very slow. Memory speeds can be measured in nanoseconds, while disks are measured in milliseconds, so accessing the disk can be tens of thousands times slower than accessing physical memory. The more swapping that occurs, the slower your system will be. Sometimes excessive swapping or thrashing occurs where a page is swapped out and then very soon swapped in and then swapped out again and so on. In such situations the system is struggling to find free memory and keep applications running at the same time. In this case only adding more RAM will help.

There is an old thumb rule for sizing swap, i.e: Swap space should be twice in the size of the RAM. From above you can understand that more swap will not solve your performance problem as SWAP space is based on your DISK’s rather than fast memory RAM, hence modern systems are no more using old thumb rule of twice the size. Rather sizing swap as per the use.

How much swap space is currently used by the system?

Free command displays the swap space. free -h shows the output according to the size.

[root@psserver ~]# free -h
       total   used  free  shared  buff/cache   available
Mem:     51G   579M   50G    8.3M        140M         50G
Swap:     9G     0B    9G

Swapon command with option -s, displays the current swap space in KB.

[root@psserver ~]# swapon -s
Filename            Type            Size    Used    Priority
/root/myswapfile    file    10485756        0       -1
/dev/sda2           partition       1020    0       -2

You can also verify the above details from /proc/swaps

[root@psserver ~]# cat /proc/swaps 
Filename            Type            Size    Used    Priority
/root/myswapfile    file            10485756        0       -1
/dev/sda2           partition       1020    0       -2

In the following post I will show two options which can be used to create/increase swap space on the server.

Option – A: Using Hard Drive Partition

If you have an additional hard disk, (or space available in an existing disk), create a partition using fdisk command. Let us assume that this partition is called /dev/sda2

Now setup this newly created partition as swap area using the mkswap command as shown below.

[root@psserver ~]# mkswap /dev/sda2
mkswap: /dev/sda2: warning: wiping old swap signature.
Setting up swapspace version 1, size = 1020 KiB
no label, UUID=cb5b3c11-5a63-4c57-9963-f5f45270c885

Enable the swap partition for usage using swapon command as shown below.

[root@psserver ~]# swapon /dev/sda2

To make this swap space partition available even after the reboot, add the following line to the /etc/fstab file.

[root@psserver ~]# cat /etc/fstab|grep swap
/dev/sda2        swap            swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

[root@psserver ~]# swapon -s
Filename           Type            Size    Used    Priority
/dev/sda2          partition       1020    0       -1
/dev/sdb1          partition       10200    0       -2
[root@psserver ~]# free -h
       total  used free shared  buff/cache   available
Mem:     51G  613M  50G   8.3M        152M         50G
Swap:    11G    0B  11G

Note: In the output of swapon -s command, the Type column will say “partition” if the swap space is created from a disk partition.

Option – B: Use a File for Additional Swap Space

If you don’t have any additional disks, you can create a file somewhere on your filesystem, and use that file for swap space.

The following dd command example creates a swap file with the name “swapfile” under /root directory with a size of 10240MB (10GB).

[root@psserver ~]# dd if=/dev/zero of=/root/swapfile bs=1M count=10240
10240+0 records in
10240+0 records out
10737418240 bytes (11 GB) copied, 8.2161 s, 1.3 GB/s
[root@psserver ~]# ls -ltr /root/swapfile 
-rw-r--r--. 1 root root 10737418240 Oct 11 03:08 /root/swapfile

Change the permission of the swap file so that only root can access it.

[root@psserver ~]# chmod 600 /root/swapfile

Make this file as a swap file using mkswap command.

[root@psserver ~]# mkswap /root/swapfile
Setting up swapspace version 1, size = 10485756 KiB
no label, UUID=9b22a18e-59ab-45f0-a94c-6d60e3f19b1a

Enable the newly created swapfile.

[root@psserver ~]# swapon /root/swapfile

To make this swap file available as a swap area even after the reboot, add the following line to the /etc/fstab file.

[root@psserver ~]#  cat /etc/fstab|grep swap
/root/myswapfile        swap            swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

[root@psserver ~]# swapon -s
Filename            Type            Size    Used    Priority
/root/swapfile    file    10485756        0       -1
[root@psserver ~]# free -h
       total  used free shared  buff/cache   available
Mem:     51G  613M  50G   8.3M        152M         50G
Swap:    10G    0B  10G

Note: In the output of swapon -s command, the Type column will say “file” if the swap space is created from a swap file.

If you don’t want to reboot to verify whether the system takes all the swap space mentioned in the /etc/fstab, you can do the following, which will disable and enable all the swap partition mentioned in the /etc/fstab

Manual Creation of SQL Profile

SQL Profiling is required when there are multiple execution plan available in the database and there is a need to use or better performance in one of the execution plan. Now, it is not guaranteed that CBO will always use the same plan hence there is a need of SQL Profiling. In SQL Profiling we lock the execution plan which we and CBO feels the optimal performance can be achieved.

For Test, I will be taking following query from PeopleSoft Application.

SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,
TO_CHAR (INVOICE_DT, ‘YYYY-MM-DD’),
VENDOR_NAME_SHORT,
VENDOR_ID,
NAME1,
VOUCHER_STYLE,
VOUCHER_ID_RELATED,
ENTRY_STATUS_SRH,
VCHR_SRC
FROM PS_VOUCHER_SRCH_VW
WHERE BUSINESS_UNIT = ‘COMP1’ AND VOUCHER_ID LIKE ‘123456%’
ORDER BY BUSINESS_UNIT, VOUCHER_ID DESC ;

Lets Begin:

First, We have to create tuning task.

For Tuning task we need to have the sql id for the sql and then create tuning task, to get sqlid we can use following:

select * from v$sql where sql_text like “SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,%”

Once, we have the sqlid we can create tuning task as follow:

declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘7u4t0k75bavcp’);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/

This will give a task ID, in my case it was “TASK_87241”

Next, We execute the task as follow:

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_87241’);
end;
/

Once task is executed, we have the recommendation from CBO. Which can be queried as follow:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => ‘TASK_87241’) from dual;

 

If you are satisfied with the sql profile recommended by the CBO then you can accept it as follow:

DECLARE
l_sql_tune_task_id  VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => ‘TASK_87204’,
name      => ‘TEST_PROFILE’,
force_match  => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/

If you are not satisfied with the SQL profile then try changing the session parameter and try running the above steps again, optimizer should provide different results.

 

Leave a comment if you found anything which can be changed or not up to the mark. There are multiple ways to do this, this is just one way. In other blog I will be taking through the process of locking down the SQL profile in one database and then copy the profile to other database.

 

Thanks.