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