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 […]
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 […]
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-time area of a cursor. Each […]
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 selectivity gives you the cost of […]
A. B-Tree Indexes I have discussed more details in the below blog. http://clouddba.co/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 might be a bad index. Every time a record is inserted, all […]
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 2 3 4 insert into tt1 […]