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’Continue reading Comparison between exadata and non-exadata executions test case

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 autoContinue reading Logical IO vs Physical IO vs Consistent gets oracle

Oracle 12c gather statistics oracle deep dive

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 aContinue reading Oracle 12c gather statistics oracle deep dive

Oracle PGA (Program Global Area) in 12c

What is PGA The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. An example of such information is the run-timeContinue reading Oracle PGA (Program Global Area) in 12c

Clustering Factor and it’s effect on non-assm and assm tablespace

Clustering Factor and it’s effect on non-assm and assm tablespace. The clustering factor is a measure of the ordernesss of an index in comparison to the table that it is based on. It is used to check the cost of a table lookup following an index access (multiplying the clustering factor by the index’s selectivityContinue reading Clustering Factor and it’s effect on non-assm and assm tablespace

Index in 12c by example test case

A. B-Tree Indexes I have discussed more details in the below blog. Index internal oracle using simple test case B.Invisible Indexes Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that mightContinue reading Index in 12c by example test case

How histogram can affect query plan change hence performance

How histogram can affect query plan change hence performance. 1.Let me create a test case.I am loading the table with following distribution.Obviously it means data is somewhat skewed. Value:-1 Range:-10 Value:-2 Range:-100 Value:-3 Range:-1000 Value:-4 Range:-10000 Value:-5 Range:-100000 C##TEST@TESTDB1> create table tt1(c1 number,c2 char(10)); Table created. C##TEST@TESTDB1> declare begin for j in 1..10 loop 2Continue reading How histogram can affect query plan change hence performance