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 […]
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 […]
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); […]
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.. Download Oracle Database Gateways CD if you have not already installed it Install Oracle […]
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 depth-part 1 H.SQL Ordered by Elapsed Time in AWR REPORT For A:- For B:- For A:-Total Elapsed Time = […]
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 […]
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 […]
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; […]
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 […]
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 […]