Real time performance tuning using Oracle SQL Developer

Real time performance tuning using Oracle SQL Developer

 

SQL developer is free tool can be downloaded from Oracle .I use it frequently as my performance tuning day to day arsenal.It is great tool to analyze both overall system performance as well as particular SQL tuning.
Let me go thorugh how we can use it to leverage it’s utility to dig down performance bottleneck.

 

1.Let us simulate the same test case which I explained in following link

 

https://clouddba.co/how-to-investigate-real-time-over-all-performance-bottleneck-enq-tx-row-lock-contention-using-os-tools-like-top-sar-vmstatashawroratop-and-oem/

2.SQL developer with system/power user.Let me navigate to Tools-> Monitor sessions.The session monitor console shows you all real time active and in-active sessions details

 

3.Now I will filter to only ‘ACTIVE’ sessions IN SQL DEVELOPER.

 

4.All active sessions are filtered.We can get various information IN SQL DEVELOPER.

.

 

INST_ID:-Instance id in case of RAC

SID=Session identifier

Username:-Schema name who is executing the query.

Seconds in wait:-Very important column to monitor how long the sessions are waiting.If it is small,means very short time it is executed which is good.Long time sometimes indicate problems.

Machine:-From which terminal the query is being executed.

Status:-Active means the session is actively executing the query.

Module:-From which module the query is being executed.

5.Now select the sessions individually and check Active SQL tab to check full SQL and SQL_ID IN SQL DEVELOPER.

.

 

6.You can check the execution plan as well from “Explain plan” tab.

 

7.This tab “Waits” is very important to check real time wait.We can see Event as “Enq:TX – row lock contention” IN SQL DEVELOPER.

 

8.This section “Server” shows various details like “Logon_time”,SPID details InSQL developer.

 

9.This tab “Client” informs from which machine and terminal user is connected.

 

10.”Application” tab shows what is command type and program responsible for the query execution.

 

11.The “Contention” session shows for what reason and who is blocking the transactions.This is very important section to identify waiting sessions IN SQL DEVELOPER.

 

12.This “Long Opsq” session in sql developer is also important to monitor and estimate long running sessions.There is a dynamic performance view v$session_longops that is populated for many long running operations in Oracle. The primary criterion for any operation to appear in v$session_longops is to run more than 6 seconds.

 

For more details on session long ops refer below:-

http://www.gplivna.eu/papers/v$session_longops.htm

How to investigate real time over all performance bottleneck (enq: TX – row lock contention) using OS tools like top/sar/vmstat,ASH,AWR,ORATOP and OEM

How to investigate real time over all performance bottleneck (enq: TX – row lock contention) using OS tools like top/sar/vmstat,ASH,AWR,ORATOP and OEM

 

I will simulate “row lock contention” using following procedure( I will resue my test case mentioned in Test case 4:-Now I will run update in parallel 10 sessions of https://clouddba.co/test-case-when-index-size-can-grow-than-column-size-and-effect-of-rebuild-index/)

CREATE OR REPLACE procedure TEST.manyupdates as
v_m number;
begin
for i in 1..100000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
update t set id=v_m/15*i where rownum<10;
commit;
end loop;
end;
/

CREATE OR REPLACE procedure TEST.manysessions_update as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyupdates;’, sysdate);
END LOOP;
commit;
end;
/

Exec TEST.manysessions_update;

As we are running this procedure parallel with 10 sessions,it will definitely create row lock contention even in very short time.

Now application team will raise concern that system is very slow and you as DBA start to investigate.

A.real time performance tuning using OS tools

 

Tool1:-top utility

 

You should check %CPU section to identify top CPU consuming processes

You will notice load average is almost 5.It means at least 5-6 processes are running at a time.You can check in TOP with column S where it shows ‘R’ means running and ‘S’ means sleeping.

Also note 72% idle and 22% CPU busy.It is just a indication that your system is bit busy with CPU load.

%Cpu(s): 22.6 us, 4.6 sy, 0.0 ni, 72.3 id, 0.1 wa, 0.0 hi, 0.5 si, 0.0 st

If you want to investigate which process takes more memory,you need to sort the output on basis of “RES”

Tool 2:-SAR utility with -U option to check CPU and IO bottleneck

sar -u 10 8

%user: This shows the total time that the processor is spending on different process .

%iowait: the name iowait itself suggests that its the time spend by processor waiting for devices(input and output)

%nice: Most of you guys must be knowing that a user can change the priority of a linux process. This table shows the time spend by CPU for process whose nice value has been changed.

%steal:  This column shows the amount to time spend by a CPU (which is virtualized), for resources from the physical CPU

%idle: This suggests the idle time spend by the processor.

A low %idle time could point to which processes are using the most CPU . Use the ps or top command  to find a CPU-intensive job. A poorly written query requiring a large amount of disk access can also cause a large amount of CPU usage as well.
In the above sar output, the cause for concern is the large values being returned for %user means application is using more cpu.%iowait (waiting for block I/O) is low here so we are not having disk contention.

We need to make below consideration while checking sar report of CPU

  • Low CPU idle times.
  • High percentage of time spent waiting on I/O or %iowait> 10.
  • Bottlenecks with %system> 15, which could indicate that swapping, paging, or backups are causing a bottleneck.
  • Abnormally high %user, which could be due to applications not being tuned properly or CPU over utilization.This is our case.

Tool 3:vmstat report

vmstat 1 10

r column is runnable processes.So 7 processes are currently running.IDLE percent is overall 70%.

Any non-zero value of “SO” column leads to probable memory leak in SWAP which needs to be analyzed immediately.

For more info about vmstat:-

Linux vmstat Command – Tool to Report Virtual Memory Statistics

Tool 4:To identify disk bottleneck

 

sar -d 5 2

tps: tps stands for transfer per second, so it shows the transfer per second to that particular device

rd_sec/s: this shows you the total number of sectors on that device which is being read

wr_sec/s: if rd_sec/s is sectors being read per second then obviously wr_sec is sectors being written per second.

await: this shows the total number of time that the processor waited for requests regarding IO

%util on a device that is greater than 50 percent denotes disk bottleneck.In our case it is normal for highly utilized disk.
If await is greater than svctm due to Unbalanced disk I/O load.

Please note disk dev129-160 is most busiest disk.The tps is 100 almost.

Tool 5:-sar -b to report disk usage

 

  • tps – Transactions per second (this includes both read and write)
  • rtps – Read transactions per second
  • wtps – Write transactions per second
  • bread/s – Bytes read per second
  • bwrtn/s – Bytes written per second

The sar –b command reports on the system’s buffer cache activities (not the Oracle buffer cache). It gives you the number of transfers per second between system buffers and block devices.

Tool 6:-sar -q to find processes under queue.We need to look blocked section

 

Tool 7:-To identify memory usage using sar -r

 

in the above output most of the columns are self explanatory(and most of the outputs are in KB).
kbmemfree: this shows the amount of free memory
Kbmemused: memory used
%memused: percentage of memory used
kbbuffers: buffer memory used by the kernel.
kbcached: cached memory used by the kernel
all other entries for memory are swap(free,used,percentage etc)

B.real time performance tuning using ORATOP report

 

./oratop -f -d -i 10 / as sysdba

 

ASW:-Active session waiting

Event:-Enq:TX – row lock contention is top wait event.AVG_MS is almost 0.It will be big concern if AVG_MS is very large which means your session is being blocked long time.

SQLID/BLOCKER:-You will get which session is blocking and which sessions are blocked.

Other statistics are also will be useful for more investigation.

Please refer for more info on oratop :-

AWR report analysis in depth-part 1

C.real time performance tuning using OEM monitoring:-

 

Form OEM console you can check how many active sessions are waiting and RED means concurrency wait.You can also find SQL_ID responsible.

D.real time performance tuning using AWR report:-

 

You may look below section for AWR.The most important top 10 foreground events .42% DB time has been contributed by enqTX – row lock contention.

This section segments by row lock waits show which objects are getting locked.

E.Real time performance tuning using ASH report:-

ASH report is very handy for generating which SQL are responsible for locking.

Please check top sessions with SID and SERIAL for deeper details.Please check %event column to check what are most eventful wait events.

F.Real time performance tuning using SQL Developer Please refer below:-

 

Real time performance tuning using Oracle SQL Developer

Test case when index size can grow than table size and effect of rebuild index

Test case preparation

 

I am going to create test case to simulate different index test cases

 

Test case 1:-First we create procedure to parallel insert in 10 sessions.The insert query will insert data based on random values generated from select query.Please note I will run gather stats after every test case.

 

SQL>create table t(id number,sometext varchar2(50),mydate date);

SQL>CREATE OR REPLACE procedure TEST.manyinserts as
v_m number;
begin
for i in 1..1000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/

 

SQL>CREATE OR REPLACE procedure TEST.manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,'manyinserts;', sysdate);
END LOOP;
commit;
end;
/

SQL> create index I1_t1 on t(ID) pctfree 0;

SQL> create index I2_ti on t(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,'T');
SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='T';

SUM(BYTES)/1024/1024/1024

————————-

.7734375

Index statistics

 

Col INDEX_NAME format a10;

Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

Size of index and table

 

select bytes/1024/1024/1024,segment_name from user_segments where segment_name in ('I1_T','I2_T','T');
BYTES/1024/1024/1024 SEGMENT_NA
——————– ———-
            .265625 I1_T
           .8671875 I2_T
           .7890625 T

Test case 2:-Second we create procedure to  insert serially.

 

SQL>create table t1(id number,sometext varchar2(50),mydate date);
SQL>CREATE OR REPLACE procedure TEST.manyinserts1 as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t1 values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
end loop;
commit;
end;
/

SQL> create index I1_t1 on t1(ID) pctfree 0;

SQL> create index I2_ti on t1(SOMETEXT);

SQL> exec dbms_stats.gather_table_stats(user,’T’);

 

SQL> Set linesize 300;

INDEX STATISTICS

 

SQL> Select index_name,pct_increase,pct_free,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name in (‘I1_T’,’I2_T’);

INDEX_NAME PCT_INCREASE   PCT_FREE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS
———-         ————           ———-   ———- ———–         —————–               ———-
I1_T                                                      0 2            20296              9402633                       9589796
I2_T                                                    10 2            85153              10138784                     10138858

INDEX AND TABLE SIZE

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NAME
——————– ——————————————————————————————————————————–
           .6484375 I2_T
           .7890625 T
           .1640625 I1_T

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where segment_name=’T’;

SUM(BYTES)/1024/1024/1024
————————-
              .7734375

Test case3:-Now I will run some select statement in parallel 10 sessions.I forcefully making index on column ID.THE TABLE I SELECTED WHERE I INSERTED DATA PARALLELY.

CREATE OR REPLACE procedure TEST.manyselect as
v_m number;
begin
for i in 1..100 loop
select count(id) into v_m from t;
end loop;
end;
/
CREATE OR REPLACE procedure TEST.manysessions_select as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyselect;’, sysdate);
END LOOP;
commit;
end;
/

 

Exec TEST.manysessions_select;

Now let me check SQL statistics and buffer read from AWR

 

Test case 4:-Now I will run update in parallel 10 sessions

 

CREATE OR REPLACE procedure TEST.manyupdates as
v_m number;
begin
for i in 1..100000 loop
 select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
 update t set id=v_m/15*i where rownum<10;
commit;
end loop;
end;
/

 

CREATE OR REPLACE procedure TEST.manysessions_update as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyupdates;’, sysdate);
END LOOP;
commit;
end;
/

 

Exec TEST.manysessions_update;

Table statistics

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.265625 I1_T

.8671875 I2_T

.7890625 T

Index statistics:-

 

Test case 5:-Now I will run SELECT AGAIN

 

Exec TEST.manysessions_select;

TEST case 6:-Let me rebuild index on column ID

 

SQL> alter index I1_t rebuild;

Index altered.

TABLE and INDEX size

 

SQL> select bytes/1024/1024/1024,segment_name from user_segments where segment_name in (‘I1_T’,’I2_T’,’T’);

BYTES/1024/1024/1024 SEGMENT_NA

——————– ———-

.1875 I1_T

.8671875 I2_T

.7890625 T

Index statistics

 

Test case 7:-Let me run the select again in parallel

 

Exec TEST.manysessions_select;

Observations:-

 

Please note that select query was taking more time (3.07 s) after bulk update which I think was due to temp area usage (see segments by logical read section in awr) .AFter index reuild the query response time (0.30 s)improved.

 

select query which traverses index I1_T Elapsed time/exec Buffer gets
Before bulk update 0.35 35809
After bulk update 3.07 41832
after rebuild index 0.30 23704

 

Also please note index size I2_T is more in size than table T.Index rebuild reduced the index size though cluster factor remains same.

 

TABLE_SIZE IN GB INDEX SIZE I1_T IN GB INDEX SIZE I2_T in GB CLUSTER FACTOR I1_T CLUSTER FACTOR I2_T
PARALLEL INSERT .7890625 .265625 .8671875 9676232 10117165
SERIAL INSERT .7890625 .1640625 .6484375 9402633 10138858
PARALLEL UPDATE ON COLUMN ID .7890625 .265625 .8671875 10262755 9399026
AFTER REBUILD I1_T .7890625 .1875 .8671875 9999904 9399026

CONNECTING TO MICROSOFT SQL SERVER DATABASE FROM ORACLE USING DATABASE GATEWAY USING SQL SERVER GATEWAY FOR ORACLE

Author:-SOUMEN KUMAR DAS

 

Connecting to Microsoft SQL Server database from Oracle using SQL SERVER GATEWAY FOR ORACLE

 

Hostname Port DB Name
Oracle Database (Source) esesslxXXXX.world 1521 ORAPROD1
SQLSERVER Data base (Target) ESESSMW9999.world 1433 SQLDB_PROD
Oracle Gateway esesslxXXXX.world 1522 DG4MSQL

 

Following steps are involved..

  1. Download Oracle Database Gateways CD if you have not already installed it
  2. Install Oracle Database Gateway for Microsoft SQL Server
  3. Configure Database Gateway for Microsoft SQL Server (DG4MSQL).

 

  • Download Oracle Database Gateways from Oracle eDelivery site. (download the correct version)

 

 

 

  • Install Oracle Database Gateway for Microsoft SQL server
Unzip the downloaded zip file. It will create a directory named “gateways”
Change the directory to gateways and start installer.
-bash-3.00$ cd /software/11gR2-Gateways/gateways/
-bash-3.00$ ./runInstaller

Select PATH for the Oracle Home. The Path defaults to Oracle Home. No need to change the same

 

 

Once it prompts with above screen, open another shell and run the script as root

Click Close to Finish the installation.

  • Check if port is opened in target server (SQL SERVER) from Source (Oracle) for sql server gateway for oracle .
esesslxXXXX.world:ORAPROD1:> telnet ESESSMW9999.world 1433
Trying 153.88.xxx.xx...
Connected to ESESSMW9999.world.
  • Configure Oracle Database Gateway for SQL SERVER GATEWAY FOR ORACLE .

Make sure that the MS SQL database details are correct in the dg4msql parameter file.

esesslxXXXX.world:ORAPROD1:> cat initdg4msql1.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[ESESSMW9999.world]:1433//SQLDB_PROD
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
  • Add below entry to /opt/oracle/12.2.0.1/db/network/admin/listener.ora
LISTENER_DG4MSQL =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= TCP)(Host= esesslxXXXX.world)(Port= 1522))
  )

SID_LIST_LISTENER_DG4MSQL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
     (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
      (SID_DESC =
      (SID_NAME = dg4msql1)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
 (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
  )
  • Start newly created Listener  for sql server gateway for oracle .
esesslxXXXX.world:ORAPROD1:> lsnrctl stat LISTENER_dg4msql
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2018 10:49:48
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=esesslxXXXX.world)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_dg4msql
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                29-JAN-2018 10:41:11
Uptime                    0 days 0 hr. 8 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/12.2.0.1/db/network/admin/listener.ora
Listener Log File         /opt/oracle/12.2.0.1/db/network/log/listener_dg4msql1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=esesslxXXXX.world)(PORT=1522)))
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


  • Append following in tnsnames.ora  for sql server gateway for oracle .
 dg4msql = 
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522))
(CONNECT_DATA= (SID=dg4msql))
   (HS=OK))
  • Check tnsping  for sql server gateway for oracle .
-bash-3.00$ tnsping dg4msql
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2018 11:27:09
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/12.2.0.1/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK))
OK (10 msec)
  •  Create a new database link pointing to this TNS using SQL Server login     credentials. This username must be already created in the Microsoft SQL Server database.
SQL> CREATE PUBLIC DATABASE LINK SQLDB_DBLINK CONNECT TO <username> IDENTIFIED BY <password> using 'dg4msql'; 

Database link created.
  • Connect and check tables from target Database.
SQL> select count(*) from T_PA_OBJECTS@SQLDB_DBLINK;
  COUNT(*)
 --------
   16402

This concludes setup steps to read and write data of Microsoft SQL server tables from Oracle database.

AWR report analysis 12c in depth-part 2

AWR report analysis 12c in depth-part 2

This document is continuation of AWR report analysis in depth step by step which will provide you an idea to understand AWR report more quickly.

Please refer to first part:-

AWR report analysis in 12c depth-part 1

H.SQL Ordered by Elapsed Time in AWR REPORT

For A:-

For B:-

For A:-Total Elapsed Time = CPU Time + Wait Time. If a SQL statement appears in the total elapsed time area of the report this means its CPU time plus any other wait times made it. Excessive Elapsed Time could be due to excessive CPU usage or excessive wait times.
Please note that first 3 rows have highest elapsed time.Hence we will provide our attention to analyze the queries.
First row is clearly indicates the PL/SQL block we ran in first part of AWR analysis report.It’s elapsed time is cumulative sum of insert statements and select statements executed.
PL/SQL block executed 10 times,per execution it took 4,868 seconds which is 58% of total DB time spent (83385 sec total DB TIME).It took 33% CPU time of total CPU time(16250 sec total CPU time).

I.SQL Ordered by CPU time in AWR REPORT

For A:-

For B:-

For A:-When a statement appears in the Total CPU Time area this indicates it used excessive CPU cycles during its processing. Excessive CPU processing time can be caused by sorting, excessive function usage or long parse times. Indicators that you should be looking at this section for SQL tuning candidates include high CPU percentages in the service section for the service associated with this SQL.
In that section,again the 3 queries appeared as most CPU consuming queries.We need to take close look how to reduce CPU time.The first row is PL/SQL block which is cumulative CPU time of the other 2 expensive insert and select query.

J.SQL ordered by user I/O wait time in AWR REPORT

for A:-

For B:-

For A:-In that section,We need to identify which query is making most I/O .The gather_stats job needs to scan the tables hence it is accounted for large IO time.But still I can say,here we need to worry much on IO time as it is very less(123 sec) compared to DB Time(83348 sec). Please check “Segments by table scans” to understand what may have been contributed in your IO time .

K.SQL Ordered by Gets in AWR REPORT

For A:-

For B:-

For A:-Total buffer gets mean a SQL statement is reading a lot of data from the db block buffers. Buffer gets (Logical IO) are OK, except when they become excessive. LIO may  have incurred a PIO in order to get the block into the buffer in the first place. Reducing buffer gets is very important and should not be ignored.
To get a block from db block buffers, we have to latch it (i.e. in order to prevent someone from modifying the data structures we are currently reading from the buffer). Although latches are less persistent than locks, a latch is still a serialization device.
Also note that by lowering buffer gets you will require less CPU usage and less latching. Thus to reduce excessive buffer gets, optimize SQL to use appropriate indexes and reduce full table scans.
Please check “Segments by Logical Reads” section of AWR REPORT.

For A:-

For B:-

L.SQL ordered by Reads in AWR REPORT.

For A:-

For B:-

For A:-High total disk reads mean a SQL statement is reading a lot of data from disks rather than being able to access that data from the db block buffers.  Excessive disk reads do cause performance issues. The usual norm is to increase the db buffer cache to allow more buffers and reduce ageing . Total disk reads are caused by high physical reads, a low buffer cache hit ratio, with high IO wait times. Higher wait times for Disk IO can be associated with a variety of reasons (busy or over saturated SAN, slower underlying storage, low capacity in HBC and other hardware causes). Statistics on IO section in AWR, plus the Operating System diagnostic tools as simple as iostat can help in identifying these issues. To reduce excessive disk reads, consider partitioning, use indexes and look at optimizing SQL to avoid excessive full table scans.
In our case first query is doing physical reads o 879299 blocks;
Please check “Segments by Physical Reads” in AWR report.

M.SQL ordered by Execution in AWR REPORT.

For A:-

For B:-

High total executions need to be reviewed to see if they are genuine executions or loops in SQL code.Here we see Top 2 queries have most executions typically due to loop in PL/SQL.
In general statements with high numbers of executions usually are being properly reused. However, there is always a chance of unnecessary loop in PL/SQL, Java or C#. Statements with high number of executions, high number of logical and or physical reads are candidates for review to be sure they are not being executed multiple times when a single execution would serve.

N.SQL ordered by Parse Calls IN AWR REPORT.

For A:-

For B:-

Whenever a statement is issued by a user or process, regardless of whether it is in the SQL pool it undergoes a parse.  As explained under Parsing, the parse can be a hard parse or a soft parse. Excessive parse calls usually go with excessive executions. If the statement is using what are known as unsafe bind variables then the statement will be reparsed each time. If the header parse ratios are low look here and in the version count areas.

O.SQL ordered by shareable Memory in AWR REPORT.

For A:-

For B:-

For A:-Shareable Memory refers to Shared Pool memory area in SGA , hence this particular section in AWR Report states about the SQL STATEMENT CURSORS which consumed the maximum amount of the Shared Pool for their execution.
We need to be concerned if some query takes large chunk of shareable memory.

P.Key Instance Activity stats in AWR REPORT.

For A:-

For B:-

DB Block changes:-Denotes you have frequent change in DB block due to huge DML.Please check “Segments by physical writes” for more details in AWR.

which supports following statistics:-
1.Execute count :-very high number of executions
2.Physical writes :-Huge physical writes.Please check “Segments by physical writes” for more details in AWR.

3.Redo size:-58 GB total redo generated during snapshot time.Please search “log switches” in AWR to check how many log switches happened during snapshot capture time.We can see 43 per hour is very bad.Ideally it should be around 3-4 log switches per hour.

4.User commits:-Number of user commits very high (Case A) .But low in case B (As commit was done outside loop)

For A:-

For B:-

Q.Tablespace IO stat in AWR REPORT

Functions:-
LGWR generates 113 GB Data total(Redo data).Data/sec is 23 MB.IOPS is 1047 for redo.
DBWR reads 28 MB total and writes 21 GB data.IOPS for read is 1 and IOPS for write is 301.
Direct reads read 7GB data( Full table scan of T table which was direct path read )
In Total:-
8.7GB data in read.
141 GB data in write.
1358 IOPS derived from reqs per second.

For A:-

For B:-

It is evident that DATA and UNDOTBS1 have undergone most reads/write during snapshot period.
If the I/O activity is not properly distributed among your files, you may encounter performance bottlenecks during periods of high activity. As a rule of thumb, you don’t want more than 100 I/Os per second per 10,000 RPM disk (even with a RAID array). If the Av Rd(ms) column (Average Reads per millisecond) is higher than 14 ms (given a fair amount of reading is being done), you may want to investigate, since most disks should provide at least this much performance. If rows in this column show 1,000 ms or more in the Av Rd(ms) column or in the Av Buf Wt(ms) column (Average Buffer Writes per millisecond), you probably have some type of I/O problem, and if it shows ###### (meaning it couldn’t fit the value in the size of the field), then you have a serious I/O problem of some kind (this can also be a formatting problem, but anything greater than 1,000 is a problem when a fair number of reads are being done).

AWR report analysis in 12c depth-part 1

1.Let me  create a test case : I will take different OS statistics and run time statistics to compare with AWR report generated after execution.

Case A.About test case in lower CPU:-

I will create a table first

create table t (id number, sometext varchar2(50),my_date date) tablespace data;

Now I will create a simple procedure to load bulk data
create or replace procedure manyinserts as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
commit;
end loop;
end;
This insert will be executed in 10 parallel sessions using dbms_job.This will fictitiously increase load on database.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/

Now I will execute manysessions which will fork 10 parallel sessions

exec manysessions;

Case B.About test case in higer CPU:-

I will create a table first

create table t (id number, sometext varchar2(50),my_date date) tablespace data;

Now I will create a simple procedure to load bulk data.I will make commit out of loop to decrease frequent commit.
create or replace procedure manyinserts as
v_m number;
begin
for i in 1..10000000 loop
select round(dbms_random.value() * 44444444444) + 1 into v_m from dual t;
insert /*+ new2 */ into t values (v_m, ‘DOES THIS’||dbms_random.value(),sysdate);
–commit;
end loop;
commit;
end;
This insert will be executed in 10 parallel sessions using dbms_job.This will fictitiously increase load on database.

create or replace procedure manysessions as
v_jobno number:=0;
begin
FOR i in 1..10 LOOP
dbms_job.submit(v_jobno,’manyinserts;’, sysdate);
END LOOP;
commit;
end;
/

Now I will execute manysessions which will fork 10 parallel sessions.

exec manysessions;

I will execute the test case on server with low CPU (A) as well as high CPU (B).

2.I will use oratop tool to monitor the sessions

oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)
./oratop -f -d -i 10 / as sysdba
optionally you press x
Then you can put sql_id to see the execution plan as well.

For A.

For B

Section 1 - database
        Global Database information
   Version        : Oracle major version
   role           : database_role
   db name        : db_unique_name
   time        [s]: time as of the most recent stats (hh24:mi:ss)
   up          [T]: database uptime
   ins         [N]: total number of instance(s)
   sn        [c,N]: total user sessions (active/inactive)
   us        [c,N]: number of distinct users
   mt        [s,N]: global database memory total (sga+pga)
   fra         [N]: flashback recovery area %used, (red &gt; 90%)
   er          [N]: diag active problem count (faults)
   % db      [s,N]: database time as %(dbtime/cpu) (red if &gt; 99%)
Section 2 - instance
        Top 5 Instance(s) Activity
        o Ordered by Database time desc
   ID        [c,N]: inst_id (instance id)
   %CPU      [m,N]: host cpu busy %(busy/busy+idle). (red if &gt; 90%)
   LOAD      [m,N]: current os load. (red if &gt; 2*#cpu &amp; high cpu)
   %DCU      [m,N]: db cpu otusef as %host cpu. (red if &gt; 99% &amp; high AAS)
   AAS       [s,N]: Average Active Sessions. (red if &gt; #cpu)
   ASC       [c,N]: active Sessions on CPU
   ASI       [c,N]: active Sessions waiting on user I/O
   ASW       [c,N]: active Sessions Waiting, non-ASI (red if &gt; ASC+ASI)
   ASP       [m,N]: active parallel sessions (F/G)
   AST       [c,N]: Active user Sessions Total (ASC+ASI+ASW)
   UST       [c,N]: user Sessions Total (ACT/INA)
   MBPS      [m,N]: i/o megabytes per second (throughput)
   IOPS      [m,N]: i/o requests per second
   IORL      [m,T]: avg synchronous single-block read latency. (red &gt; 20ms)
   LOGR      [s,N]: logical reads per sec
   PHYR      [s,N]: physical reads per sec)
   PHYW      [s,N]: physical writes per sec
   %FR       [s,N]: shared pool free %
   PGA       [s,N]: total pga allocated
   TEMP      [s,N]: temp space used
   UTPS      [s,N]: user transactions per sec
   UCPS    [c,m,N]: user calls per sec
   SSRT    [c,m,T]: sql service response time (T/call)
   DCTR      [m,N]: database cpu time ratio
   DWTR      [m,N]: database wait time ratio. (red if &gt; 50 &amp; high ASW)
   %DBT      [s,N]: instance %Database Time (e.g. non-rac shows 100%)
Section 3 - db wait events
        Top 5 Timed Events
        o Cluster-wide, non-idle
        o Ordered by wait time desc
  EVENT      : wait event name. (red if active)
        (RT) : Real-Time mode
  WAITS      : total waits
  TIME(s)    : total wait time in seconds)
  AVG_MS     : average wait time in milliseconds
  PCT        : percent of wait time (all events)
  WAIT_CLASS : name of the wait class
Section 4 - process
        o Non-Idle processes
        o Ordered by event wait time desc
   ID          [N]: inst_id. (red if blocking)
   SID         [N]: session identifier. (red if blocking)
   SPID        [N]: server process os id
   USERNAME       : Oracle user name
   PROGRAM        : process program name
   SRV            : SERVER (dedicated, shared, etc.)
   SERVICE        : db service_name
   PGA         [N]: pga_used_mem. (red if continuously growing)
   SQL_ID/BLOCKER : sql_id or the final blocker's (inst:sid, in red)
   OPN            : operation name, e.g. select
   E/T         [T]: session elapsed time (active/inactive)
   STA            : ACTive|INActive|KILled|CAChed|SNIped
   STE            : process state, e.g. on CPU or user I/O or WAIting
   WAIT_CLASS     : wait_class for the named event
   EVENT/*LATCH   : session wait event name. Auto toggle with *latch name.
                    (red if process is hung/spin)
   W/T         [T]: event wait time. (red if &gt; 1s)

3.TOP output during activity

 For Case A:-
We had 4 CPU,so our 6 jobs (status :-S means Sleeping)  had to wait for CPU.Other 4 jobs are running (Status :-R means Running). Also note 86% CPU used because we are consuming 4 CPU out of 4 CPU)There is almost no idle CPU.

For Case B:-

We had 20 CPU,so our all 10 jobs are running (Status :-R means Running).Also note 44.4 % CPU is used because out of 20 CPU we are using 10 CPU.Hence there is 50% idle CPU.

4.VMSTAT report

 For Case A:-

For Case B:-

5.The table size after 10 jobs completed.

SQL> select bytes/1024/1024/1024  from dba_segments where segment_name='T';

BYTES/1024/1024/1024

-------------------

7.375

6.AWR report 360 degree analysis

A.Header Section IN AWR REPORT

For Case A:-

For Case B:-

Same load completed 5 times faster when we have more CPU.
Sessions:=No of active sessions
Cursors/Session:=No of open cursor per session.If this value is increasing or high indicates potential problem of cursor leaking.
Elapsed :=The elapsed time indicates the duration of the report between the 2 selected snapshots. Any other duration figures can be compared back to this. A 30-60 minute reporting period is usually recommended.
DB time:=db time is the time spent in the database.Ideally DB time=CPU time+Non-idle wait time.For example,If we have 10 parallel session each taking 20 minutes to execute,our DB time will be 200 minutes+other query execution time in 20 minutes snapshot period.The DB Time is the time spent in the database for the period of the report. If this is significantly higher than the Elapsed time then this is a good indicator of a heavily loaded system.

B.Load Profile IN AWR REPORT

For Case A:-

For Case B:-

DB Time:-No of active sessions average during snapshot period.This is derived by DB time/Elapsed time in previous section.If the number his high,then probably many active session are there in database on particular point which may indicate bottleneck or opportunity to deep analysis.
DB Cpu:-DB CPU is quite less than DB time ,so sessions are waiting not much working on cpu.This may indicate a problem if your DB cpu percent is quite less than DB time.
Redo size:-11 MB per second.An increase in Redo size and Block changes indicate increased DML(INSERT/UPDATE/DELETE) activity.
Logical reads:-Logical reads is simply the number of blocks read by the database, including physical (i.e. disk) reads, and block changes is fairly self-descriptive.If you find those number higher than expected (based on usual numbers for this database, current application workload etc.), then you can drill down to the “SQL by logical reads” and “SQL by physical reads” to see if you can identify specific SQL responsible.
Block Changes:-This indicates high block change means many  transactions going on.
User Calls:-In particular, when the database is executing many times per a user call, this could be an indication of excessive context switching (e.g. a PL/SQL function in a SQL statement called too often because of a bad plan). In such cases looking into “SQL ordered by executions” will be the logical next step.
Hard parse:-A hard parse occurs when a SQL statement is executed and is not currently in the shared pool.A hard parse rate greater than 100 per second could indicate that bind variables are not being used effectively; the CURSOR_SHARING initialization parameter should be used; or you have a shared pool–sizing problem.
Parse:-This is soft parse+hard parse.A soft parse occurs when a SQL statement is executed and it is currently in the shared pool. A very high soft parse rate could lead to problem of programmed application.
Logons:-Establishing a new database connection is also expensive (and even more expensive in case of audit or triggers). “Logon storms” are known to create very serious problem.In fact log off is more expensive.This indicates problem of not using proper connection pooling.
Executes:-High no of executes always indicates potential load to database.
Transactions:-High number of transactions indicates it is OLTP system.

C.Time Model Statistics IN AWR REPORT

for Case A:

For Case B:

For Case A:-

sql execute elapsed time is 60%.That means DB spent 60% time of DB_TIME is executing SQL query which is OK for now .
DB CPU is used 20% of DB_TIME.
Now We can see PL/SQL execution elapsed time is 11% means many PL/SQL procedure is being executed.
You can see which PL/SQL executed in SQL ordered by Elapsed Time.
Parse time elapsed and hard parse elapsed time is less which is good sign.If these are bigger,we need to check why query is taking more time to parse.

D.Instance Efficiency Percentages IN AWR REPORT

For Case A:-

For Case B:-

Buffer Nowait %:-Less than 99 percent may indicate a problem . This value is the ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then there are (hot) blocks being contented for that should be found in the Buffer Wait section.
Buffer Hit %:- Less than 95 percent may indicate a problem. This value is the ratio of hits on a request for a specific buffer when the buffer was in memory and no physical I/O was needed.A hit ratio that is steadily at 95 percent and then drops to 45 percent should be checked for bad SQL or a dropped index (check the top physical reads SQL) causing a surge in physical reads that are not using an index or an index that has been dropped
Library Hit % :-Less than 95 percent indicates problem. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small). A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case, a smaller shared pool may only be a bandage that potentially fixes a resulting library latch problem)
In-memory Sort % :- less than 95 percent in OLTP indicates problem. In an OLTP system, you really don’t want to do disk sorts. Setting the MEMORY_TARGET or PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE in previous versions) initialization parameter effectively eliminates this Problem.
Soft Parse %:- Less than 95 percent indicates problem. A soft parse ratio that is less than 80 percent indicates that SQL is not being reused and needs to be investigated.
Latch Hit %:- Less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue.

E.Foreground Events by total wait time IN AWR REPORT

For Case A:-

For Case B:-

This is one of the most important section.You must be concerned if any wait event take abnormally high %DB time apart for DB CPU.If DB CPU is high like 80%,then your application is CPU bound.

For case A,

DB CPU :-It is showing 19.5% means it is may be CPU bound but because we have only 4 CPU and 10 parallel sessions,CPU is not able to do much work and waiting for IO to be completed.Please check OS statistics for reviewing CPU related bottlenecks (% cpu utilization)

Resmgr:cpu quantum:-Indicates it may be problem of allocating sufficient number of CPU cycles.

F.Background wait events IN AWR REPORT

For Case A:-

For Case B:-

For case A:-

The log file parallel write shows LGWR is waiting for blocks to be written to all online redo log members in one group. LGWR will wait until all blocks have been written to all members.   So here we had 80% of background total time spent on log file parallel write.  The parameter of interest here is Avg wait (ms). In our case it is 1ms which is a perfectly respectable figure. Obviously larger average wait times point to slower I/O subsystem or poor I/O configurations.As a rule of thumb, an average time for ‘log file parallel write’ over 20 milliseconds suggests a problem with IO subsystem.
Another thing is log file parallel write is background wait event of log file sync.You need to check if large commit is happening in “user commits” section of “Instance Activity Stats”

G.Host CPU IN AWR REPORT

For Case A:-

For Case B:-

For Case A,

Host CPU

Here you can notice %user is 84%.This really gives you sign that your CPU are highly used.Load average 2.77 (after execution completed) means you are having average 3 sessions running at one time and other 7 sessions are waiting for CPU.But most of the time load average was around 10 (refer oratop and vmstat) as there was 10 sessions running all the time during snapshot period.

Instance CPU

%Total cpu indicates that Database is using 82.6 % cpu of total CPU of the database server. This indicates there are not many other database’s process running currently.If you see less value here it may indicates the database server total cpu may be used by other application or database instances.

H.IO Profile

For case A:-

For case B:-

Here Total requests:=Database requests + Redo requests=1046+322=1381(around) which is actually IOPS.
Total MB=Database (MB) + Redo (MB)=5.9+22.9=30.4 MB which denotes database is generating 30 MB data per second.

I.Memory Statistics

For case A:-

For case B:-

Host Mem(MB):-Total memory in the system
SGA use (MB):-Total SGA used
PGA use(MB):-Total PGA used.
Cache size mainly distributed Buffer cache(where data buffer stored in memory)+Shared pool size.

A low value for the % SQL with exec>1 statistic indicates databasee is not re-using shared SQL statements, usually because the SQL is not using bind variables.

J.OS Statistics

For A:-

For B:-

This is like vmstat report.Almost 83% cpu was used during execution which is quite high.%busy high is good means cpu are not using simultaneous multi threading.

 

Adaptive Cursor Sharing

Adaptive Cursor Sharing:

 

This document will explain step by step to analyze adaptive cursor sharing.

Let’s start with the basics. Let’s create a table as a copy of dba_objects.

 

SQL> create table t as select * from dba_objects;

Now, if we run a query like ‘select object_name from t where object_id=1027’, then it must go through the full process of hard parsing. Each time we put a literal as the object_id value, Oracle sees it as a new statement and does a hard parse. And as we know, a hard parse is an expensive operation, one that we must try to avoid as much as possible. Major problems that result from using literal values include the following:

  • There is a security threat as applications that concatenates literals are soft targets for SQL Injection attacks. Not using literals, i.e. using Bind Variables eliminates that threat.

  • For every statement that is hard parsed, cursors are not being shared, and more memory is being consumed.

  • Hard Parsing implicitly increases latch contention, as Oracle must latch the shared pool and library cache while doing a hard parse.

Okay, let’s first run the following three queries, each one using a different object_id, i.e. a new literal for each statement:

select object_name from t where object_id=1027;

select object_name from t where object_id=1028;

select object_name from t where object_id=1029;

So far, so good. So, let’s check how Oracle internally views each of these statements. For that let’s run the following statement:

SQL> select sql_text, sql_id, version_count, hash_value from v$sqlarea where sql_text like ‘select object_name from t%’;

And we can see the query of V$SQLAREA shows that the three statements require three different parent cursors. As shown by VERSION_COUNT, each parent cursor requires its own child cursor.

So, to overcome the problem of hard parsing every time, we can use bind variables to share the cursor.

Let’s have a look at the following example:

So, what we have done is that we have run three different statements with three different object_id values, but used a bind variable which we defined with the object_id, before running the statement. The expectation here is that Oracle will share the cursor, and define the object_id value at run time. Let’s see how it actually worked:

The VERSION_COUNT value of 1 indicates that the database reused the same child cursor rather than creating three separate child cursors. Using a bind variable made this reuse possible.

However, there is a catch. When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. What Oracle does is, at the outset it peeks at the bind variable value for the initial hard parse, and uses that information to generate an execution plan for all subsequent values of the bind variable, thus leaving itself open to working with sub optimal plans, because the plan produced during the initial hard parse may or may not be the best plan for all values of the bind variable.

Let’s see whether supposedly similar statements can produce different execution plans or not.

Let’s first create an index on the object_id column of the table t.

create index i1 on t(object_id)

Then let’s run the following three statements and see the execution plan for each one of them.

In the third case where we are selecting a significantly larger number of rows from the table, the optimizer considers a full table scan more efficient than an index scan, thus validating what we were guessing-that execution plans do change for ‘similar-looking statements’ but with very different literals.

But before we move ahead let’s check the same three statements using bind variables. As a preparatory step before actually running our queries, let us flush the shared pool and buffer cache, just so we are sure about the results we get.

alter system flush shared_pool;

alter system flush buffer_cache;

It is clear that Oracle continues to use the plan developed during the initial hard parse, even though we know the INDEX RANGE SCAN is not the most optimal plan for object_id<19000, as we already saw a FULL TABLE SCAN occurring when we gave the optimizer the benefit of literals in the earlier example.

So, although there are some obvious benefits of using bind variables, it is definitely not the solution for all our SQL tuning problems. With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values.

So herein comes the benefit of ADAPTIVE CURSOR SHARING. In adaptive cursor sharing, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. Let’s say the optimizer chooses a particular plan for bind value 1000, then it can very well choose a different plan for bind value 19000, which may be relatively the more optimal plan for that value of the bind variable.

Thus, cursor sharing is “adaptive” because the cursor adapts its behavior so that the optimizer does not always choose the same plan for each execution or bind variable value. So, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.

Adaptive cursor sharing works only if you are using bind variables and have a histogram on at least one of the columns that is being used in the WHERE clause of the query.

Let’s create a new table ‘S’ as a copy of dba_objects and gather statistics on it as well as create a histogram on the object_id column.

The next step would be to run a query on this table using a bind variable.

With Adaptive Cursor Sharing, two additional columns are there in V$SQL. IS_BND_SENSITIVE and IS_BIND_AWARE. A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.

The database has examined the bind value when computing cardinality, and considers the query “sensitive” to plan changes based on different bind values. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:

  • The optimizer has peeked at the bind values to generate cardinality estimates.

  • The bind is used in an equality or a range predicate.

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.

At this point, let us see what is the status of the columns IS_BIND_SENSITIVE and IS_BIND_AWARE for the query we ran. We notice that IS_BIND_SENSITIVE is flagged to ‘Y’ while IS_BIND_AWARE is flagged as ‘N’.

This means that Oracle is ‘sensitive’ to the fact that bind variable is being used and the optimal plan is predicated on the bind variable value, i.e. whatever value we pass in the bind variable subsequently will determine whether the plan is good, bad, or maybe somewhere in-between.

Let’s try one more time with a bind variable value not too different from what we used earlier:

The cursor continues to be bind sensitive. Now, let us do something different. Let us use a bind variable value which is significantly different, and let us see what we get:

  1. So Oracle, now in its wisdom decides to make the cursor bind aware as well. Because the cursor is bind-aware, the database does the following:

Determines whether the cardinality of the new values falls within the same range as the stored cardinality. If it falls within that range it reuses the execution plan in the existing child cursor.

What happens if it doesn’t fall in the range of stored cardinality? In that case, Oracle does a hard parse and creates a new child cursor with a different execution plan, stores metadata about the predicate, including the cardinality of the bound values, in the cursor and executes the cursor.

So, the essential point here is, we now have two child cursors catering to two different sets of bind variable values.

Great! What next? Here it starts getting really interesting. Let’s say we now use a bind variable value which doesn’t match the stored cardinality of the existing child cursors, so the optimizer goes through the painful process of hard parsing and creates another child cursor which it then executes. Assume this execution plan used an index. Oracle then checks and sees that one of the existing child cursors had also used an index scan. Oracle now tries to optimize things by merging the two child cursors containing index access plans. This implicitly involves storing the combined cardinality statistics into one child cursor and deleting the other one. This is really efficient, because instead of two child cursors, we now have one child cursor which can be reused, and it sores the combined cardinality of more than one bind variable value.

So, in conclusion it can be said that Adaptive Cursor Sharing is a really cool feature in Oracle that allows us to use to use Bind Variables in an unfettered way, leveraging the reuse of child cursors without having to compromise with living with suboptimal execution plans.

********************************************************************************************************************************************

 

 

Comparison between exadata and non-exadata executions test case

First we need to load data

SQL> alter session set tracefile_identifier=’ABC’;

Session altered.

Elapsed: 00:00:00.01
SQL> alter system set events ‘10046 trace name context forever, level 12′;

System altered.

Elapsed: 00:00:00.18
SQL> create table t1(c1 number,c2 char(2000));

Table created.

Elapsed: 00:00:00.03
SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> insert into t1 select rownum,’A’ from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:22.81

Exadata plan

 

Non-Exadata plan

Tkprof-exadata

 

tkprof-non-exadata

 

Statistics of how many blocks in table

SQL> select BLOCKS from user_tables where table_name=’T1′;

BLOCKS
———-
67217

 

Full table scan with count

SQL> select count(1) from t1;

Elapsed: 00:00:00.06

Exadata plan

 

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Full table scan with all data from table.Buffer fetched will be much higher than count

SQL> select * from t1;

199998 rows selected.

Elapsed: 00:00:01.62

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Create index in both exadata and non-exadata

SQL> create index i1_t1 on t1(c1);

Index created.

Elapsed: 00:00:00.22
SQL> exec dbms_stats.gather_table_stats(user,’T1′);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.34

Statistics of how many blocks in Index

SQL> select BLEVEL,LEAF_BLOCKS from user_indexes where index_name=’I1_T1′;

BLEVEL LEAF_BLOCKS
———- ———–
1 443

Index range scan

SQL> select * from t1 where c1 between 1 and 15;

30 rows selected.

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan with table access

 

SQL> select * from t1 where c1=1;

Elapsed: 00:00:00.00

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Retrieve single block for index scan without table access

 

SQL> select c1 from t1 where c1=1;

Elapsed: 00:00:00.01

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

Index fast scan to check behavior of index access

 

SQL> select count(c1) from t1;

Elapsed: 00:00:00.02

Exadata plan

Non-exadata plan

Tkprof-exadata

Tkprof-non-exadata

SPM oracle 12c test case

A.Enabling  SPM manually to stabilize plan for a particular query

For this test case I will create a table with skewed data so that for one bind value it will go for one execution plan and for other bind value,it will go for another execution plan.Ideally this should be good to go for different execution plan depending on the bind value.But to show SPM, I was intentionally picked this test case to show how SPM can stabilize the plan even our bind value gets changed.

Test case:-

1.Let us create the data first.

I will create 2 tables and create some skewed data for example 99999 rows for ‘A’ and 99 rows for ‘B’

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05


SQL> create table t1(c1 number,c2 char(1000));

Table created.

Elapsed: 00:00:00.14


SQL> insert into t1 select rownum,'A' from dual connect by rownum<100000;

99999 rows created.

Elapsed: 00:00:13.41


SQL> commit;

Commit complete.

Elapsed: 00:00:00.03


SQL> insert into t1 select rownum,'B' from dual connect by rownum<100;

99 rows created.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01


SQL> create index i1_t1 on t1(c2);


SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89

2.Now we will check the execution plan.It shows “INDEX FAST FULL SCAN” for below cases.

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

Elapsed: 00:00:02.13


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 0

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

-------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04


SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.07


SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 0

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 698100246

-------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      | | | | 3735 (100)|          |

|   1 | SORT AGGREGATE       | | 1 | 1001 |           | |

|*  2 |  INDEX FAST FULL SCAN| I1_T1 | 99998 |    95M| 3735 (1)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.04

3.From above output ,Both cases we get same cost but ideally cost should be different if we could have histograms.I expect second query with less number of rows should go for index range scan.

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'FOR COLUMNS C2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.37

SQL> exec :c1 := 'B';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

        99

Elapsed: 00:00:00.00

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 1

-------------------------------------

select count(1) from t1 where c2=:c1

Plan hash value: 2778874372

---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 20 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 |    98 | 98098 | 20 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("C2"=:C1)

19 rows selected.

Elapsed: 00:00:00.06


SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

4.Now We will check what was impact of plan change from AWR history snapshots.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col bind_value for a10
break on plan_hash_value on startup_time skip 1
select ss.snap_id,s.optimizer_cost cost ,ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
(s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio,sss.value_string bind_value
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS,dba_hist_sqlbind SSS
where s.sql_id = nvl('&sql_id','fb8r4b2qtw3p3')
and ss.snap_id = S.snap_id and s.snap_id=SSS.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 and s.sql_id=sss.sql_id
order by 1, 2, 3
/



5.Now I will create sql tuning set for the sql_id

BEGIN  
DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'MySTS01',    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>11336, end_snap=>11337,basic_filter=>'sql_id = ''fb8r4b2qtw3p3''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

6.Let us check sql tuning set result

-- List out SQL Tuning Set contents to check we got what we wanted

SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
)

7.Now let us pin the correct plan_hash_value for which I need my plan to be stabilized in spite of changing the bind value .

SQL> DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01',
    basic_filter=>'plan_hash_value = ''2778874372'''
    );
END;
/  

PL/SQL procedure successfully completed.

8.Let us check whether my sql is taking base lined plan or not.

SQL> variable c1 char;

SQL> exec :c1 := 'A';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

  COUNT(1)

----------

     99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 2

-------------------------------------

select count(1) from t1 where c2=:c1

 Plan hash value: 2778874372

---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

---------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------
   2 - access("C2"=:C1)

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

 


SQL> variable c1 char;

SQL>  exec :c1 := 'B';

PL/SQL procedure successfully completed.

SQL> select count(1) from t1 where c2=:c1;

   COUNT(1)

----------

        99

SQL> select * from table (dbms_xplan.display_cursor());

 PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

SQL_ID  fb8r4b2qtw3p3, child number 2

-------------------------------------

select count(1) from t1 where c2=:c1
Plan hash value: 2778874372
---------------------------------------------------------------------------

| Id  | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  | | |  | 14328 (100)| |

|   1 | SORT AGGREGATE   | | 1 | 1001 |            | |

|*  2 |  INDEX RANGE SCAN| I1_T1 | 99998 |    95M| 14328 (1)| 00:00:01 |

---------------------------------------------------------------------------

Note

-----

   - SQL plan baseline SQL_PLAN_8w2csch0r83kz7a01406e used for this statement

B.Influence plan to add hint without changing main sql query

In this test case I will first create a table with index on column C2. First I will check this query if it picks up index.Then I will force full table scan with hint.After that I will stabilize plan with SPM in such a way that oracle CBO will choose full table scan with out any hint.

1.Let us check the query is picking the index

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999



SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 0
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 698100246

-------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 3824 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | INDEX FAST FULL SCAN| I1_T1 | 50049 | 47M| 3824 (1)| 00:00:01 |
 -------------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

19 rows selected.

2.Now let me force full table scan hint.

SQL> select /*+ full(t1) */ count(*) from t1 where c2= 'A'

  COUNT(1)

----------

     99999

 
SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID cyxfgfy9bav2s, child number 0
 -------------------------------------
 select /*+ full(t1) */ count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

3.Let me load plan from cache to base line and will decide which plan we need to stabilize

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '43u8cjvr7ctcz');

variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'cyxfgfy9bav2s');

4.Let me check sql_handle now for both the query with hint and without hint

5.Now We need to disable undesirable plan.In this case,I do not want my query to go for index scan hence need to disable from sql plan baseline.

SQL> variable cnt number
exec :cnt := dbms_spm.alter_sql_plan_baseline (-
 SQL_HANDLE => 'SQL_0519f9d2859107e0',-
 PLAN_NAME => 'SQL_PLAN_0a6gtua2t21z0ecb116c3',-
 ATTRIBUTE_NAME => 'enabled',-
 ATTRIBUTE_VALUE => 'NO');SQL> > > > >

PL/SQL procedure successfully completed.

6.Now We need to load hinted plan into baseline

sql_id and plan_hash_value should be from hinted plan

sql_handle from non-hinted plan.

SQL> variable cnt number
 exec :cnt := dbms_spm.load_plans_from_cursor_cache(-
 sql_id => 'cyxfgfy9bav2s', -
 plan_hash_value => 3724264953, -
 sql_handle => 'SQL_0519f9d2859107e0');

7.Now we can see even we not used hint,first query is going for full table scan instead of index scan.

SQL> select count(*) from t1 where c2= 'A';

COUNT(*)
 ----------
 99999

SQL> select * from table (dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------
 SQL_ID 43u8cjvr7ctcz, child number 2
 -------------------------------------
 select count(*) from t1 where c2= 'A'

Plan hash value: 3724264953

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 4122 (100)| |
 | 1 | SORT AGGREGATE | | 1 | 1001 | | |

|* 2 | TABLE ACCESS FULL| T1 | 50049 | 47M| 4122 (1)| 00:00:01 |
 ---------------------------------------------------------------------------

Predicate Information (identified by operation id):
 ---------------------------------------------------

2 - filter("C2"='A')

Note
 -----
 - SQL plan baseline SQL_PLAN_0a6gtua2t21z0616acf47 used for this statement

PLAN_TABLE_OUTPUT
 --------------------------------------------------------------------------------

23 rows selected.

8.Now We will check what SQL plan is enabled

Logical IO vs Physical IO vs Consistent gets oracle

Logical IO vs Physical IO vs Consistent gets

I have created 1 GB table T1.
 
Let me check how many blocks are there in the table after I have analyzed it.
 
SQL> select BLOCKS from user_tables where table_name=’T1′;
   BLOCKS
———-
   139153
 

Autotrace report

Let me clean buffer cache and check the auto trace report.
 
SQL>set autotrace traceonly;
 
SQL> select * from sh.t1;
 
415584 rows selected.
 
Execution Plan
———————————————————-
Plan hash value: 3617692013
 
————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |   415K|   797M| 37716   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T1   |   415K|   797M| 37716   (1)| 00:00:02 |
————————————————————————–
 
Statistics
———————————————————-
         1  recursive calls
         0  db block gets
    166620  consistent gets
    138907  physical reads
         0  redo size
   9193927  bytes sent via SQL*Net to client
    305307  bytes received via SQL*Net from client
     27707  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
    415584  rows processed
 

BLOCKS READ ALGORITHM

 
A Concepual Knowledge on Oracle Blocks Read Algorithm is as under :
  1. User Issues a Query.
  2. Query is Parsed and Optimal Execution path is generated and stored in the Library Cache.
  3. Based on the Execution Path, required Index or Table block is searched in the the Cache.
  4. If the block is not found in the cache, a disk read request is made, and the block is read into the Cache.
  5. The block, from the Cache is then read into a private memory area (UGA) of the User.
  6. Once the Block is read into the private memory of the User, the required row is fetched.
The value of Consistent Read is incremented each time a new block is read into a private memory area of the User, which also means that, a single block, if read multiple times, affect the value of the Consistent Read. While an in-efficient SQL contributes largely to Consistent Read, but one factor that also have significant impact is ARRAY SIZE. I am not going to discuss about In-efficient Queries, but will write on the calculation that undergoes and impact Consistent Gets.
 

How consistent gets ORACLE calculated:-

 
Calculation:-No of physical block+total no of rows fetched/array size
By default array size is 15.
 
So In my case
138907+415584/15=166620
 
When you fetched 15 rows, Oracle paused, gave you the data. When you went back to get the next 15, it got the buffer again to resume your query
 

PHYSICAL IO and LOGICAL IO

 
db_block_gets + consistent_gets = LOGICAL IO=166620
physical_reads( typically no of block in case of full scan) = PHYSICAL IO=138907
 

TKPROF command

 
SQL> alter session set tracefile_identifier=’ABC’;
 
Session altered.
 
SQL> alter system set events ‘10046 trace name context forever, level  12’;
 
System altered.
 
SQL>select * from t1;
 
SQL>alter session set events ‘10046 trace name context off’
 
tkprof ERMAN_ora_26624_ABC.trc abc.txt sys=no sort='(prsela,exeela,fchela)’ explain=test/test
 

TKPROF output

 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse       22      0.00       0.01          1         63          2           0
Execute     20      0.11       0.67         15        417         22           3
Fetch    27727      0.20       0.97     138909     166680          0      415654
——- ——  ——– ———- ———- ———- ———-  ———-
total    27769      0.33       1.65     138925     167160         24      415657
 
Misses in library cache during parse: 13
Misses in library cache during execute: 6
 
Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 —————————————-   Waited  ———-  ————
 Disk file operations I/O                       30        0.00          0.00
 SQL*Net message to client                   27741        0.00          0.01
 SQL*Net message from client                 27741      154.26        345.66
 SGA: allocation forcing component growth      201        0.10         19.90
 SQL*Net break/reset to client                   6        0.00          0.00
 DLM cross inst call completion                  1        0.52          0.52
 db file sequential read                        57        0.00          0.04
 db file scattered read                          1        0.00          0.00
 log file sync                                   1        0.00          0.00
 direct path read                               68        0.00          0.16
 

In AWR

In the below part it is evident we have 46 IOPS.

Instance Activity Stats

physical read total IO requests 2,667 45.47 190.50
physical read total bytes 1,159,954,432 19,777,569.17 82,853,888.00
 
 
 

IOSTAT

SQL> show parameter multi;
NAME TYPE VALUE
———————————— ———– ——————————
db_file_multiblock_read_count integer 128
 
SO 128 Oracle blocks could be written in one sequential I/O (In case of Full table scan) or  individual “random” I/Os (In case of index scan)
 
 

 

 Device Status Output

Units Definition
rrqm/s The number of read requests merged per second queued to the device.
wrqm/s The number of write requests merged per second queued to the device.
r/s The number of read requests issued to the device per second.
w/s The number of write requests issued to the device per second.
rMB/s The number of megabytes read from the device per second. (I chose to used MB/s for the output.)
wMB/s The number of megabytes written to the device per second. (I chose to use MB/s for the output.)
avgrq-sz The average size (in sectors) of the requests issued to the device.
avgqu-sz The average queue length of the requests issued to the device.
await The average time (milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
r_await The average time (in milliseconds) for read requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
w_await The average time (in milliseconds) for write requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.
svctm The average service time (in milliseconds) for I/O requests issued to the device. Warning! Do not trust this field; it will be removed in a future version of sysstat.
%util Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this values is close to 100%.