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 […]
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 […]
Sarat 1) What are pre-checks you do before doing point in time recovery? 2) There is no previous level zero backup available, Can we take a Level 1 backup? 3) My database have 500+ datafiles, In that I lost 89 files. How you will identify which files to recover? 4) Can you explain what are internal mechanism of Transportable tablespace […]
Heman Sahi 1. Your RMAN backup triggered, you deleted snapshot controlfile at backend, what will happen to your running backup ? 2. what happens internally when we issue ‘ RESYNC CATELOG’ for recovery catelog ? 3. You enabled Block change tracking and then immediately performed level 1 backup, will BCT be used ? 4. Your RMAN backup triggered, you added […]
I referred some reference from my favorite Tim Hall blog (ORACLE_BASE) A.Basic of optimizer statistics: – The optimizer cost model relies on statistics collected about the objects involved in a query, and the database and host where the query runs. Statistics are critical to the optimizer’s ability to pick the best execution plan for a SQL statement. Object What influences […]
Thanks Akhil Mohan. Debasis Maity: 1.Why controlfile and redolog file multiplexed? Control file : A control file is a small binary file that records the physical structure of the database and includes: The database name. Names and locations of associated datafiles and online redo log files. The timestamp of the database creation. The current log sequence number. Checkpoint information. Oracle […]