Basic metrics to look upon in dynatrace

1. Time spent in each API 
2. Number of calls into API 
3. Number of total domains 
4. Total size 
5. Number of items per page 
6. Number of AJAX per page 

Symptoms of low performing SQL

1. Consume high CPU, buffer, I/O, PGA memory 
2. Long running SQL or significantly different runtime 
3. High I/O, CPU ,memory, network waits
4. SQl consuming high DB time ( can be analysed using AWR, ASH )
5. Long running SQLs and operations 
6. SQL with execution plan change ( need to enable trace to see these using sqlplus)

Why SQL Statements regressed

• Bad execution plans 
• Full tables scans 
• Cartesian join (details)
• Poorly written statement , e.g converting literal once and not the whole column

Bad query as it will convert literal for all row : Select * from employee where to_ch(salary) =sal  

Good query as it convert only 1 row : select * from employee where salary = to_number(sal)

• Cursor sharing 

• Hardware resources high utilization 
• CPU, Memory, IO, Network 
• Data fragmentation 
• Logical contention 
• Row lock contention 
• Block update contention 
• Index contention 

How DML statement get processes in a oracle Database

1. Listener established a connection with App Server 
2. A server process get created  by PGA for App server 
3. Server process act as a proxy between App and DB , it create a HASH Value for corresponding SQL and sent the details to SGA 
4. if same sql is ran earlier there would be a sql area exist if not then a sql area will be get created 
5. SQL engine inside SGA will parse the sql based on syntax, semantics and privileges
6. The request data now get updated in Buffer Cache and  UNDO block and Log Buffer (log buffer will have old as well new value 
7. Now if commit triggered then all the data from Log buffer get transfer to Redo logs , Please note whenever commit command is given all the data (All sql statement )from log buffer get transfer to Redo logs along with SCN(System change number). UNDO data get rewritten with updated value 
8. Buffer cache will be update with new data 
9. Now whenever DBWR get initiated by system the data from buffer cache get updated in data files .

How Select statement get processes in a oracle Database

Basic Diagram

Steps:

High Level: Parse (Check syntax, semantics and privileges )
                    Execution Plan
                    Fetch

1. Listener established a connection with App Server 
2. A server process get created  by PGA for App server 
3. Server process act as a proxy between App and DB , it create a HASH Value for corresponding SQL and sent the details to SGA 
1. if same sql is ran earlier there would be a sql area exist if not then a sql area will be get created 
2. SQL engine inside SGA will parse the sql based on syntax, semantics and privileges
3. Optimizer will create the execution plan 
4.  SP process will read the data files  by making I/O and bring it to buffer cache
5.  if data is already present in buffer cache , no I/O occur
4. SP will deliver the data to App server 

Useful link
https://docs.oracle.com/database/121/TGDBA/tune_pga.htm#TGDBA363

Introduction to predictive modelling

What is Predictive modeling?

• A set of methods to arrive at quantitative solution to problems of business interests
• It is a part of Data Science or Statistical learning
• Examples

1.  Predict whether a patient, hospitalized due to a heart attack, will have a second heart attack.   The prediction is to be based on demographic, diet and clinical measurements for that patient.
2. Predict the price of a stock in 6 months from now, on the basis of company performance measures and economic data.
3. Identify the numbers in a handwritten ZIP code, from a digitized image.
4. Estimate the amount of glucose in the blood of a diabetic person, from the infrared absorption spectrum of that person’s blood.

Predictive modeling process

Types of predictive model learning

The learning problems that we consider can be roughly categorized as either supervised or unsupervised

• Supervised learning

         In supervised learning, the goal is to predict the value of an outcome measure based on a                      number of input measures

         

         Examples

            Predict whether a patient, hospitalized due to a heart attack, will have a second heart attack.                 The prediction is to be based on demographic, diet and clinical measurements for that patient.

            Predict the price of a stock in 6 months from now, on the basis of company performance                       measures and economic data.

            Identify the numbers in a handwritten ZIP code, from a digitized image.

            Estimate the amount of glucose in the blood of a diabetic person, from the infrared absorption             spectrum of that person’s blood.

                

• Unsupervised learning

           In unsupervised learning, there is no outcome measure, and the goal is to describe the                          associations and patterns among a set of input measures.

         Examples

           Identifying the products that are usually sold together
            
           Identifying of typical profile of employees who quit quickly

Variable Types and Terminology

•  X-----> Set of inputs/Independent variables/Predictors
• Y------->Set of outputs/Dependent variables/Responses

  

program to capture and validate argument from command line

usage() {
        echo "usage: $0 [options]" >&2
        cat >&2 <<"EOF"
Options:
  -h, --help            show this help message and exit
  -t TIMEINTERVAL       time interval between each dumps
  -i INSTANCE         websphere instance name
  -v, --verbose         Enable verbose output

EOF
        exit
}
optspec=":hv:t:i:-:"
while getopts "$optspec" optchar; do
    case "${optchar}" in
        -)
            case "${OPTARG}" in
                timeinterval=*)
                    sleep_time=${OPTARG#*=}
                    ;;

                instance=*)
                    instance_name=${OPTARG#*=}
                    ;;
                verbose)
                    VERBOSE=true
                    ;;
                        *)
                    if [ "$OPTERR" = 1 ] && [ "${optspec:0:1}" != ":" ]; then
                        echo "Unknown option --${OPTARG}" >&2
                        usage
                    fi
                    ;;
            esac
            ;;
        t)
            sleep_time=${OPTARG}
            ;;
        i)
            instance_name=${OPTARG}
            ;;

        h)
            usage
            exit 2
            ;;
        *)
            if [ "$OPTERR" != 1 ] || [ "${optspec:0:1}" = ":" ]; then
                echo "Non-option argument: '-${OPTARG}'" >&2
                usage
            fi
            ;;
    esac
done
shift $((OPTIND-1))

if [ -z "$sleep_time" ]
then
        echo need to set sleep_time
        usage
fi
if [ -z "$instance_name" ]

then
        echo need to provide instance name for which dumps need to be collected
        usage
fi

echo "$instance_name"
echo "$sleep_time"