• CozWeAreFriends Banner. Nice isn't it?
  • CozWeAreFriends Banner. Nice isn't it?
  • Oracle SQL to generate maximum/high CPU on OVM Guest

    Posted on July 18th, 2015 by fath

    Hi All,

    There was a time during my project migrating our Oracle database servers to Oracle Virtual Machine (OVM) Guest (VM) that I had an issue regarding how the CPU works. Consider this scenario, you already provisioned 4 VCPUs to your guest running on Windows Server 2008 and then suddenly there is a need to increase the CPU since your Oracle database (oracle.exe) is starving for more processing power. What you did is simply increase the Guest’s VCPU on the fly through your OVM Manager console. The CPU somehow is reflected to your Windows OS (CPU in your task manager changed from 4 to 8 cores) but your oracle.exe process remained utilizing maximum 4 cores and not growing into the new 4 cores.

    I had searched through MOS and Google but cannot find much information related to the scenario except for some documentations regarding Guest sort-of bug but it however was affecting earlier version of OVM.

    Based on our testing in the environment, we found out that there’s no way to let our Oracle database process to switch to 8 VCPUs dynamically except after OS reboot. To test this, we had to ensure that our oracle.exe will initially consume maximum 4 CPUs and we tried to increase the VCPU afterwards.

    It was too bad that the database size was not that big hence I could not figure out how to simulate very high load in an easy way. So what I did was to use the following SQL so that the CPU retained at maximum all the time. I hope it will help anyone of you who is looking for the same answer.

    *Note: you may not want to run this in your production environment.

    SQL:
    select /*+ PARALLEL(100) */ distinct a.obj#, a.obj#
    from SOURCE$ a, WRH$_SYSMETRIC_HISTORY b



    Oracle SQLplus – How to check database version

    Posted on April 30th, 2012 by fath

    Hi All.

    A lot of you might’ve been knowing how to achieve what I mentioned in the title. But sometime, we are clueless and we dig down here and there, but found nothing. Hehe. So here’s the easy way how to get Oracle database version using SQLplus.

    Just login to your SQLplus. You will see it there. 🙂

     

    $ sqlplus ‘/as sysdba’

    SQL*Plus: Release 10.2.0.4.0 – Production on Mon Apr 30 17:19:49 2012

    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

     



    Siebel Restart WebServer Script When Memory Reaching Threshold

    Posted on April 27th, 2012 by fath

    Hello All,

    There’s this occasion whereby Siebel will somehow crash if the server memory reaching certain level. To encounter/as a workaround for this, I wrote a monitoring script that will check the server utilization and perform Siebel’s WebServer restart whenever only small amount of memory left in the server. Please have a look at the script below 🙂

     

    #!/usr/bin/sh
    # Script to grep Free RAM
    # Perform Web Server restart once the RAM meet threshold
    # Written by Fath

    # THRESHOLD IN ‘K’ bytes UNIT
    THRESHOLD=1400000

    WEB_SERVICE_DIR=/opt/hpws/apache32/bin

    FREE_RAM_OUTPUT=/home/admin/FREE_RAM_OUTPUT.txt
    TOP_OUTPUT=/home/admin/TOP_OUTPUT.txt
    top -d1 -f ${TOP_OUTPUT}

    MEMORY_ALL=`grep “Memory:” ${TOP_OUTPUT}`
    FREE_RAM=`echo ${MEMORY_ALL} | awk ‘{print $8}’`
    FREE_RAM_NO=`echo $FREE_RAM | awk ‘{gsub (/K/,””); print}’`

    echo “———————————————–” >> ${FREE_RAM_OUTPUT}
    date >> ${FREE_RAM_OUTPUT}

    if [ “$FREE_RAM_NO” -lt “$THRESHOLD” ];then
    echo “Free RAM is less than ${THRESHOLD}K (${FREE_RAM_NO}K). Performing Web Service restart…” >> ${FREE_RAM_OUTPUT}
    echo “httpd processes running in server:” >> ${FREE_RAM_OUTPUT}
    ps -ef | grep httpd | grep apache32 >> ${FREE_RAM_OUTPUT}
    echo “Shutting down.. ” >> ${FREE_RAM_OUTPUT}
    . ${WEB_SERVICE_DIR}/stopapa >> ${FREE_RAM_OUTPUT}
    sleep 1
    PROC_COUNT=`ps -ef | grep httpd | grep apache32 | wc -l`
    while [ “$PROC_COUNT” -ne “0” ]
    do
    sleep 1
    PROC_COUNT=`ps -ef | grep httpd | grep apache32 | wc -l`
    done
    echo “0 httpd process running. Starting up..” >> ${FREE_RAM_OUTPUT}
    . ${WEB_SERVICE_DIR}/startapa >> ${FREE_RAM_OUTPUT}
    while [ “$PROC_COUNT” -eq “0” ]
    do
    sleep 1
    PROC_COUNT=`ps -ef | grep httpd | grep apache32 | wc -l`
    done
    echo “OK! httpd processes are running!” >> ${FREE_RAM_OUTPUT}
    else
    echo “There’s enough free RAM (${FREE_RAM_NO}K).” >> ${FREE_RAM_OUTPUT}
    fi

    echo “———————————————–” >> ${FREE_RAM_OUTPUT}
    rm ${TOP_OUTPUT}

     



    UNIX – Shell script to capture SQL query using Oracle sqlplus

    Posted on April 22nd, 2012 by fath

    Hi All,
    In my project, as part of Performance Testing team, I’ve wrote a number of scripts to help us perform our duty in system tunings. Here. I’d like to share one of my shell script that can help to capture SQL query running in the background.Feel free to modify this script to fit your own requirement.

     

    ###############################
    # CAPTURE SQL – by Fath #
    ###############################
    ################# PARAMETERS #################

    ##############################################
    #SH_LOG=”$location/CAPTURE_SQL.log”

    clear
    echo “*****************************************************************”
    echo “PV SQL CAPTURE SCRIPT”
    echo “*****************************************************************”

    echo “LOADING CAPTURING SQL..”

    echo “”
    pros=`ps -ef | grep ora_pmon | awk ‘{print $9}’ | grep ora_pmon_ | awk ‘{print substr($1,10)}’`

    echo “Database running on Oracle Process = $pros”
    echo “Oracle SID = $ORACLE_SID”
    echo “Proceed? [y/n]”
    read option
    echo “”

    if [ “$option” = “y” ]
    then
    echo “Enter sid and serial#”
    echo “SID:”
    read sid
    echo “Serial #:”
    read serial

    result=`sqlplus -s ‘/as sysdba’ << ENDOFSQL
    begin
    dbms_system.set_ev(‘$sid’,’$serial’,10046,12,”);
    end;
    /

    begin
    DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>’$sid’, serial#=>’$serial’,sql_trace=>TRUE);
    end;
    /
    show parameter user_dump_dest
    exit;
    ENDOFSQL`

    #path=”$result”

    echo “”
    echo “——————————-”
    echo “Remarks:”
    echo “Spooled queries will be generated in:”
    #echo “$path” | grep /app | awk ‘{print $3}’

    path=”/app/oracle/admin/NSBLPVT/udump”
    echo “$path”
    echo “——————————-”
    echo “”

    while true
    do
    echo “Stop capturing? [y/n]”
    read option2
    echo “”

    if [ “$option2” = “y” ]
    then
    result2=`sqlplus -s ‘/as sysdba’ << ENDOFSQL
    begin
    DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>’$sid’, serial#=>’$serial’,sql_trace=>FALSE);
    end;
    /
    exit;
    ENDOFSQL`

    echo “——————————-”
    echo “Results: ”
    echo “SQL captured in: ”
    result3=`ls -lrt $path | tail -n 1 | awk ‘{print $9}’`
    echo “$result3”
    echo “Converting trace file to log…”
    result4=`tkprof $path/$result3 SQL_CAPTURED[$result3].log sys=no waits=yes sort=exeela`
    echo “Converted to SQL_CAPTURED[$result3].log…”
    echo “Completed.”
    echo “——————————-”

    break
    fi
    done
    fi

    echo “”
    echo “SEE YOU AGAIN!!”
    echo “”

     

     



    How to trace Garbage Collection

    Posted on March 15th, 2012 by fath

    How to trace Garbage Collection

    The two primary measures of garbage collection performance are throughput and pauses. Throughput is the percentage of the total time spent on other activities apart from GC. Pauses are times when an application appears unresponsive due to GC.

    Two other considerations are footprint and promptness. Footprint is the working size of the JVM process, measured in pages and cache lines. Promptness is the time between when an object becomes dead, and when the memory becomes available. This is an important consideration for distributed systems.

    JVM diagnostic output will display information on pauses due to GC. If you start the server in verbose mode (use the command asadmin start-domain –verbose domain), then the command line argument “-verbose:gc” prints information for every collection. Here is an example of output of the information generated with this JVM flag:

    [GC 50650K->21808K(76868K), 0.0478645 secs]
    [GC 51197K->22305K(76868K), 0.0478645 secs]
    [GC 52293K->23867K(76868K), 0.0478645 secs]
    [Full GC 52970K->1690K(76868K), 0.54789968 secs]

    On each line, the first number is the combined size of live objects before GC, the second number is the size of live objects after GC, the number in parenthesis is the total available space, which is the total heap minus one of the survivor spaces. The final figure is the amount of time that the GC took. This example shows three minor collections and one major collection. In the first GC, 50650 KB of objects existed before collection and 21808 KB of objects after collection. This means that 28842 KB of objects were dead and collected. The total heap size is 76868 KB. The collection process required 0.0478645 seconds.

    Other useful monitoring options:

    • -XX:+PrintGCDetails –  more detailed logging information
    • -Xloggc:file to save the details in a log file