Intermediate SQL Color Coded SQL, UNIX and Database Essays

Tools

2001-02-13: ORA_CPU for Linux ora_cpu.linux.tar.gz

This tool dynamically shows important OS (such as CPU priority) and ORACLE (such as current SQL or current wait) statistics for active database sessions. It now works in both AIX and Linux.

AIX Paging Space Test Programs

Related to post:

ADRCI Greplet Functions (extract useful information from ORACLE Alert logs) adrci_func.txt

Related to Post:

ORACLE/AIX Memory Monitoring Tool set ora_mem1.2.tar.gz

Related to Posts:

Tool set to test ORACLE Implicit Type Conversion: itc_test.zip

Related to posts:

SQL performance Measurement Toolset: tracking_sql_performance.zip

Related to posts:

Comments (17) Trackbacks (0)
  1. 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. 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. 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. 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. 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. Any comment HOW oracle decrease SGA?
    Any swapping involved?

  7. 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. 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. 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. 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. 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. Hello Maxym,
    After removal of lc() call program working fine.
    Thank you.
    Best regards,
    Milenko Glisic

  13. 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. 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

  15. Hello Maxym,

    Very impresive and helpfully article/scripts. Thank’s a lot for your great work.

    Greetings from Zürich (Switzerland).
    Florian

  16. Thanks Florian. I’m glad that you found it useful.

  17. Maxym,

    Words cannot express how grateful I am to have stumbled upon your site. It is such a tremendous resource for someone like me who had such difficulties trying to get a grasp of how this AIX-Oracle memory thing works. Thanks so much for the time and effort put forth in producing these articles and scripts and sharing them with us.


Leave a comment

No trackbacks yet.