The tool was devised by Craig from ORAPUB which is excellent to monitor what happens in session real time. 1.First I need to down OSM tool kit from ORAPUB. First we need to run the following scripts to create some pre-requisite tables under sys/system schema. a)osmprep.sql — ****************************************************** — * Copyright Notice : (c)1998-2014,2015 OraPub, Inc. — * Filename : […]
For deep understanding on CBC,please refer to below links:- http://www.proligence.com/pres/ioug14/2014_431_Nanda_ppr.pdf and Video tutorial from ORAPUB. I am just going to mention some useful queries to investigate or identify CBC latch Step 1:-You as user fire a sql query from client. select * from cbctest; Step 2:-Now oracle will search metadata and determine what is your file_id and block_id to locate […]
Performance monitoring or tuning tool for SQL query taking long time using various tools In that post,I will explain using various method or tool to investigate performance problem of particular sql query. Let me create a test case first create table test_c(id number,name char(100),address char(100),create_date date); create table test_p(id number,name char(100)); declare v_m number; begin for i in […]
1.General Information Memory versions of statistics regularly transferred to disk by MMON. AWR stores base statistics:-counters and value statistics. ASH data is firstly captured first to memory in 1 second interval for active sessions only.ASH data is reduced by 10 times sample in memory data.ASH data is used by ADDM. The advisor report produced by ADDM reports are used by […]
You determine the level of statistics collection on the database by setting the value of the STATISTICS_LEVEL parameter. The values for this parameter are: • BASIC: No advisory or other statistical data is collected. You can manually set other statistic collection parameters such as TIMED_STATISTICS and DB_CACHE_ADVICE.Many of the statistics required for a performance baseline are not collected. Oracle strongly […]
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); […]
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:- http://clouddba.co/awr-report-analysis-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. […]
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 […]