Intermediate SQL Color Coded SQL, UNIX and Database Essays

1. 

   Brandon
   May 18th, 2010 - 17:42

   Hi, just found your sight and am very impressed with the articles on AIX Memory usage. Thanks a lot for all the detailed information and scripts. Just a heads up – when I downloaded the ora_mem1.2.tar.gz file, it came out as ora_mem1.2.tar.tar instead of having the gz extension at the end, but worked fine after I renamed it and unzipped it.
2. 

   Maxym Kharchenko
   May 18th, 2010 - 19:53

   Brandon, Thanks.

   I’m glad you found the articles useful.

   As for the TAR/GZ issue – this is most likely caused by browser settings, but I’ll look into that …
3. 

   Rick
   June 21st, 2010 - 03:26

   Hello, very helpfull articles. Have been reading them with great interest. Downloaded your scripts, our databases do not run under user oracle, so I had to adjust the script. No big issue there, got not output from the scripts, suggesting to run this as root. Is the output dependend on the user privileges?
   I’m trying to figure out if the memory space between sga_max and sga_target is ever used, as I understood it is not allocated immediate. But will it ever been used, some one claimed it is not used under AIX.
4. 

   Maxym Kharchenko
   June 21st, 2010 - 09:18

   Hello Rick,

   The output is likely dependent on AIX patch level. Up until 5300-08, permissions of svmon were restricted to:

   AIX> ll /usr/bin/svmon
   -r-x—— 1 root system

   while, starting with 5300-09, they seem to have been eased by IBM to:

   -r-xr-xr-x 1 root system

   so now, anyone can run it (even, oracle ).

   Memory between SGA_TARGET and SGA_MAX_SIZE can definitely be used. The only thing you need to do is: (dynamically) increase SGA_TARGET. It works like this:

   SGA_TARGET is the current memory allocation limit, while SGA_MAX_SIZE is the potential MAX memory allocation limit. The “memory” between SGA_TARGET and SGA_MAX_SIZE is NOT allocated (in other words, large value in SGA_MAX_SIZE is not likely to affect the system). You can keep increasing SGA_TARGET up until SGA_MAX_SIZE is reached and more memory will be allocated by ORACLE.

   This works even in 10g, however the problem is – in 10g SGA memory can never be released (short of restarting the instance).
5. 

   Maxym Kharchenko
   June 21st, 2010 - 09:24

   The other thing – if ora_mem.pl cannot be run from “oracle” account due to restricted svmon permissions, you can always run omem_proc.sh which takes its data from ps and should be able to run anywhere.

   The memory usage info will be slightly less precise with omem_proc.sh but still useful.
6. 

   alex.p
   July 26th, 2010 - 13:43

   Any comment HOW oracle decrease SGA?
   Any swapping involved?
7. 

   Maxym Kharchenko
   July 29th, 2010 - 17:05

   Alex,

   Thank you for your comment.

   These two events (deallocation of SGA and paging out) are rather separate.

   Deallocation of SGA (available from 11g) is caused by ORACLE code running certain C functions (I have not been able to trace which ones exactly yet, but most, likely: disclaim()). When you deallocate memory from SGA the total size of SGA memory (“virtual size”) is decreased.

   You can easily observe it by decreasing sga_target and at the same time increasing pga_aggregate_target and looking at svmon output (if on AIX). You will see that shared memory segments are now “using” fewer pages. These pages are not “saved” (in paging space or elsewhere), they are rather completely “gone”.

   Paging out is caused by memory pressure in your operating system (your programs request more memory than the system has) and thus OS (i.e. AIX) needs to “save” some of that memory in paging space.

   Note that:

   1) “Paging” out is NOT controlled by ORACLE, but rather external to ORACLE - in fact, ORACLE is not even aware of its SGA being paged out.
   2) The total size of SGA is NOT decreased, rather some of SGA memory is “moved” to paging space.

   Regards,
   Maxym Kharchenko
8. 

   Milenko Glisic
   November 10th, 2010 - 05:58

   Hello,
   We found error in the execution of ora_mem>ora_mem.pl:
   Aix_H2 /home/ax101250/ora_mem>ora_mem.pl -i report

   Display Memory Statistics for ORACLE Processes on AIX
   Author: Maxym Kharchenko, 2010

   Displaying memory statistics for ORACLE instance: report. Report mode: basic
   Database session information has NOT been requested
   svmon: Not a recognized flag: O

   in the source you have:
   # Run SVMON - this is the MAIN thing that we do here
   my $szCmd = ‘svmon -O segment=category -O filterprop=data -P ‘ . join ” “, (sort keys %$rhProc);
   my $szCmdOut = `$szCmd`;
   Thank you in advance.
   Best regards,
9. 

   Maxym Kharchenko
   November 10th, 2010 - 14:58

   Hello Milenko,

   You must have older version of AIX (-O option should be available in 5.3 and 6.1). Try to use: ora_mem_bc.pl (“bc” = “backward compatible”) – logic was redone there NOT to use svmon -O parameters …

   Let me know if it does not work …

   Regards,
   Maxym Kharchenko
10. 

    Milenko Glisic
    November 11th, 2010 - 02:18

    Hello Maxym,
    Talking about AIX my running environment is :
    bash-3.00# oslevel -s
    5300-08-07-0920
    I tried with ora_bc.pl program and it working fine with one exception. Program not recognized ORACLE_SID with capital leters.
    bash-3.00# ps -def|grep ora_dbw
    oracle 393502 1 0 00:48:15 – 0:00 ora_dbw0_mktd02
    oracle 1540424 1 3 Oct 31 – 15:24 ora_dbw0_report
    root 1855824 1675626 0 08:07:54 pts/1 0:00 grep ora_dbw
    oracle 889462 1 0 00:48:09 – 0:00 ora_dbw0_assets
    oracle 1213234 1 0 00:48:20 – 0:00 ora_dbw0_dbsig
    oracle 1434568 1 0 00:49:01 – 2:38 ora_dbw0_DLTSGYB

    bash-3.00# ./ora_mem_bc.pl -i DLTSGYB

    Display Memory Statistics for ORACLE Processes on AIX
    Author: Maxym Kharchenko, 2010

    Database instance dltsgyb is NOT running at ./ora_mem_bc.pl line 385.

    and

    bash-3.00# export ORACLE_SID=DTLSGYB
    bash-3.00# ./ora_mem_bc.pl

    Display Memory Statistics for ORACLE Processes on AIX
    Author: Maxym Kharchenko, 2010

    Database instance DTLSGYB is NOT running at ./ora_mem_bc.pl line 385.
    bash-3.00#

    Thank you in advance.
    Best regards,
11. 

    Maxym Kharchenko
    November 11th, 2010 - 10:06

    Hello Milenko,

    This is a slight bug on my part. When I accept “-i” parameter (or $ORACLE_SID env var) I convert it to lowercase in this line:

    $g_rhScriptVars->{ORACLE_SID} = lc($Options{i}) if exists $Options{i};

    Just open ora_mem_bc.pl and remove the lc() call (and then, call the script with -i parameter) …

    Regards,
    Maxym Kharchenko
12. 

    Milenko Glisic
    November 12th, 2010 - 04:06

    Hello Maxym,
    After removal of lc() call program working fine.
    Thank you.
    Best regards,
    Milenko Glisic
13. 

    Milenko Glisic
    November 12th, 2010 - 04:09

    Hello Maxym,
    After removal of lc() call program ora_mem_bc.pl working fine with $ORACLE_SID in capital leters.
    Thank you!
    best regards,
    Milenko Glisic
14. 

    Larry
    September 19th, 2011 - 23:04

    Hello Maxym,
    Talking about AIX my running environment is :
    bash-3.00# oslevel -s
    5300-08-07-0920
    I tried with ora_bc.pl program and it working fine with one exception. Program not recognized ORACLE_SID with capital leters.
    bash-3.00# ps -def|grep ora_dbw
    oracle 393502 1 0 00:48:15 – 0:00 ora_dbw0_mktd02
    oracle 1540424 1 3 Oct 31 – 15:24 ora_dbw0_report
    root 1855824 1675626 0 08:07:54 pts/1 0:00 grep ora_dbw
    oracle 889462 1 0 00:48:09 – 0:00 ora_dbw0_assets
    oracle 1213234 1 0 00:48:20 – 0:00 ora_dbw0_dbsig
    oracle 1434568 1 0 00:49:01 – 2:38 ora_dbw0_DLTSGYB
    +1

Leave a comment Cancel reply