ignoring index in aggregate query

  1* create table t7(c1 date,c2 number,c3 varchar2(100))
SQL> /
Table created.
SQL> insert into t7 select sysdate,mod(level,5),mod(level,10000) from dual connect by level<=100000;

100000 rows created.
SQL> insert into t7 select sysdate-1,mod(level,5),mod(level,10000) from dual connect by level<=10000
;
10000 rows created. 

create index i_idx on t7(c1);
index created

select TRUNC(c1, 'HH') c1 from t7
  where c1  between sysdate-5 and sysdate-1
  group by TRUNC(c1, 'HH')
   
SQL> /
C1
---------
08-JUL-13
09-JUL-13

  1  create view v1 as
  2  select TRUNC(c1, 'HH') vc1 ,count(*) vc2 from t7
  3  --where c1  between sysdate-2 and sysdate-1
  4* group by TRUNC(c1, 'HH')
SQL> /
View created.

SQL> conn scott/tiger@orcl11g
Connected.
SQL> set autot on
SQL> select * from v1 where vc1 between sysdate-2 and sysdate-1;
VC1              VC2
--------- ----------
09-JUL-13      11000

Execution Plan
----------------------------------------------------------
Plan hash value: 1490029489
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 12762 |   112K|   289   (6)| 00:00:04 |
|   1 |  HASH GROUP BY      |      | 12762 |   112K|   289   (6)| 00:00:04 |
|*  2 |   FILTER            |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| T7   | 12762 |   112K|   288   (6)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   3 - filter(TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')>=SYSDATE@!-2 AND
              TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')<=SYSDATE@!-1)
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        931  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> exec dbms_stats.gather_table_stats(user,'t7',estimate_percent=>null,method_opt=>'for all column
s size 254',cascade=>true);
PL/SQL procedure successfully completed.


  1   select TRUNC(c1, 'HH') vc1 ,count(*) vc2 from t7
  2   where c1  between sysdate-2 and sysdate-1
  3*  group by TRUNC(c1, 'HH')
SQL> /
VC1              VC2
--------- ----------
09-JUL-13      11000

Execution Plan
----------------------------------------------------------
Plan hash value: 3216186273
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     8 |    45   (3)| 00:00:01 |
|   1 |  HASH GROUP BY     |       |     1 |     8 |    45   (3)| 00:00:01 |
|*  2 |   FILTER           |       |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| I_IDX | 11000 | 88000 |    44   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   3 - access("C1">=SYSDATE@!-2 AND "C1"<=SYSDATE@!-1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         51  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

REMEDY:

SQL> create index i_idx2 on t7(TRUNC(c1, 'HH'));
Index created.
Elapsed: 00:00:08.69
SQL> select * from v1 where vc1 between sysdate-2 and sysdate-1;
VC1              VC2
--------- ----------
09-JUL-13      11000
Elapsed: 00:00:00.19
SQL> set autot on
SQL> set lines 300 pages 80
SQL> /
VC1              VC2
--------- ----------
09-JUL-13      11000
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 3507030160
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     5 |    40 |    12   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                |        |     5 |    40 |    12   (9)| 00:00:01 |
|*  2 |   FILTER                      |        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T7     |   778 |  6224 |    11   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | I_IDX2 |  1400 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!-2<=SYSDATE@!-1)
   4 - access(TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')>=SYSDATE@!-2 AND
              TRUNC(INTERNAL_FUNCTION("C1"),'fmhh')<=SYSDATE@!-1)

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         66  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Column Order in Oracle Composite Index and indexing strategy

You have queries like:

select *
from table1
where c1=10 and c2=30 and c3=100

select *
from table1
where c1=10 and c2=30

If the columns used in filter conditions are not selective i.e c1, c2 and c3 have high cardinality then these columns are not good candidate for indexing. In this case their combination can be tried to see  if whole filter condition is more selective or say cardinality of whole filter,the combination of these individual filters is low.  So in this case composite index on combinations can be created. Combination of these columns can be unique or not unique

Now question #1 is what should be the combination of these columns in composite index. In oracle we have index skip scan so answer may be slighly different from other rdbms which uses composite indexes only when leading column is used.

Suppose that we have a composite unique index on this table on the combination of these columns (in that order):

FirstName, LastName, ContactID
Let’s look at the data distribution:
select count(*) as total_records,
count(distinct FirstName) as Distinct_FirstName,
count(distinct LastName) as Distinct_LastName,
count(distinct ContactID) as Distinct_ContactID
from Employees

total_records Distinct_FirstName Distinct_LastName Distinct_ContactID
------------- ------------------ ----------------- ------------------
2336724       1018               1206              2336724

you can see, the most selective column is CONTACTID for which a given value will qualify an exact 1 record. Next most selective is LastName
and the least selective is FirstName.

Now, let’s look at Question #1 :

Query :select * from Employees where ContactID = 2 and FirstName = ‘Catherine’ and see its cost

Now, let us also include the leading column of the index:

Query : select * from Employees where ContactID = 2 and FirstName = ‘Catherine’ and see its cost

Its cost will come down. This tells us that not only is the column order important, it is very important for the leading column of the index to be part of the filter condition in order to make good use of the index and to have a good execution plan. This immediately brings another question to mind – now, we know that we need the leading column of the index to be present in the filter condition (WHERE clause) but does it make a difference if we have the least selective vs the most selective column as the leading column of that index?

In our example, we have established that the ContactID column is the most selective – every value qualifies for a single record and the FirstName column is the least selective. The index that we currently have on the table is: test and it is formed on the combination of the columns in this order:

FirstName, LastName and then ContactID

Now, let’s go ahead and create another index and this time, we will make ContactID as the first column:

create index test_2 on Employees(ContactID, LastName, FirstName)

Now, let us execute the same query but force it to use different indexes and see whether the execution plan and the query cost changes at all. The query cost will be  different in the two cases – the test index usage yielded higher cost vs lower cost in case of test_2. So the column order as well as the selectivity of the columns in an index does matter a lot . If it is SQL Server which does not have index skip scan then having the most selective column as the leading column of the index is a good design assuming that that column is going to be used in your filter criteria.

Bottom line is that the order of the columns in a composite index is very important and it also depends upon how your queries are written. Let’s take one more example:

Say, I have a table TAB1 which has columns A, B, C and suppose I have two index:
Index1 on (a, b) and Index2 on (b,a).
If I have these queries:

Select * from TAB1 where A = @a and B = @b;
Select * from TAB1 where A = @a
then, Index1 is useful for these queries. If I have:

Select * from TAB1 where A = @a and B = @b;
Select * from TAB1 where B = @b
then, Index2 is more useful since it covers both the queries.  Beginning Oracle 9i,if cost based optimizer is choosen for execution theen there is  a new feature called “Index Skip Scan” was introduced using which both Index1 or Index2 can be useful in the above case but it depends upon the selectivity of those columns – we will cover index skip scan in a future post but you can read more over here  if you are interested.

Key things to take away from this post are:
1) Order of the columns in an index is important but the most important thing is to first understand your queries and see how the index is going to be used. Say, I have three columns in a table with million records with this data distribution for the columns:
C: 2 distinct values
B: 5 distinct values
A: Million distinct values
As you can see from above, A is the most selective and C is the least so is A, B, C the right order? Answer is it depends. If you are using only equality operators in your filter condition, then definitely the answer is Yes. If however, say, the filter criteria is: WHERE C = ‘X’ and B = 2 and A > getdate(), then we would scan a lot of C=’Y’ and B != 2 values while we are scanning the A > getdate() values (this criteria of A > the value that we used in the example below qualified nearly 200,000 records). A better thing would be to use B,C,A in that case to reach the place where B and C equality matches are done and scan the rest of the records using the A > getdate() criteria.

So, understand your queries and how you are going to be accessing the data and then decide how the composite index needs to be formed

Question 2: Is order of column always important:
Answer is :  If I have all the columns of the composite index in the filter condition (WHERE clause), then the order in which they are specified in the filter criteria does not matter.

So strategy could be start indexing columns from highly selective to least but consider cases of queries where there is no equality conditions also.

Another strategy if index skip scan has to be kept in mind is:

1. If a and b both have 1000 distinct values and they are always queried together then the order of columns in the index doesn't really matter. But a has only 10 distinct values or you have queries which use just one of the columns then it does matter; in these scenarios the index may not be used if the column ordering does not suit the query.
2. The column with the least distinct values ought to be first and the column with the most distinct values last. This not only maximises the utility of the index it also increases the potential gains from index compression.
3. The datatype and length of the column have an impact on the return we can get from index compression but not on the best order of columns in an index.
4. Arrange the columns with the least selective column first and the most selective column last. In the case of a tie lead with the column which is more likely to be used on its own.

The two main reasons for leading with the least selective column are

1. Index compression
2. Index Skip reads

Both these work their magic from knowing that the value in the current slot is the same as the value in the previous slot. Consequently we can maximize the return from these techniques by minimsing the number of times the value changes. In the following example, A has four distinct values and B has six. The dittos represent a compressible value or a skippable index block.

Least selective column leads ...

A          B
---------  -
AARDVARK   1
"          2
"          3
"          4
"          5
"          6
DIFFVAL    1
"          2
"          3
"          4
"          5
"          6
OTHERVAL   1
"          2
"          3
"          4
"          5
"          6
WHATEVER   1
"          2
"          3
"          4
"          5
"          6

Most selective column leads ...

B  A
-  --------
1  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
2  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
3  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
4  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
5  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER
6  AARDVARK
"  DIFFVAL
"  OTHERVAL
"  WHATEVER

Even in this trival example, (A, B) has 20 skippable slots compared to the 18 of (B, A). A wider disparity would generate greater ROI on index compression or better utility from Index Skip reads.


Question 4:

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order you set columns?

It does, but perhaps not in the way you might expect.

Given col1 and col2, people's initial instinct is to put the more selective column first.

But, that's almost always irrelevant.

First, you need to consider how the index will be used in your SQL statements. If some SQLs only specify col1, then col1 should probably be first. If all SQLs will always specify both columns in the where predicate, then it doesn't matter which comes first.

But, what if most of the SQLs specify both col1 and col2, and a few provide only col1 or col2?

Well, in that case, the least selective column should (probably) go first. There are two reasons for this. First, if the least selective goes first, then, for those queries where only the second column is specified in the predicate, Oracle will be more likely to do an INDEX SKIP SCAN, if the leading column is not very selective. Second, by putting the less selective column first, you'll be more likely to be able to take advantage of index compression.

Choosing Composite Indexes

A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:

• Improved selectivity
  
  Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.
• Reduced I/O
  
  If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind 
ON table1(x, y, z);

• x, xy, and xyz combinations of columns are leading portions of the index
• yz, y, and z combinations of columns are not leading portions of the index

Choosing Keys for Composite Indexes

Follow these guidelines for choosing keys for composite indexes:

• Consider creating a composite index on keys that are used together frequently in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.
• If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.

Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections.

Ordering Keys for Composite Indexes

Follow these guidelines for ordering keys in composite indexes:

• Create the index so the keys used in WHERE clauses make up a leading portion.
• If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.
• If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.
• If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.

 References:
 http://decipherinfosys.wordpress.com/2008/05/13/column-order-in-a-composite-index/
 http://stackoverflow.com/questions/2196484/oracle-does-the-column-order-matter-in-an-index
http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_overview.htm

http://dba.stackexchange.com/questions/21017/does-order-matter-for-composite-primary-key-in-oracle-or-any-database

DBA TYPE: which one you fall in...

I noticed I had below on a edited but never published post one year ago:

... and I'm back again ...

and back again after more than two years

Cervical pain still running but I cannot resist temptation to write..

Which DBA you are :

Spartan DBA,
little bit (not much) Diplomatic DBA,
Scientific DBA,

or have all traits


Or say are you Nut Bolt DBA or are you Tool DBA.

Or say are you Development DBA or a Production DBA.

or are you Developer and DBA both.

It all depends what needs your organization and you.

I believe DBA shouuld have scientific approach and very much spartan to be brave.
Needs to know how to handle clients when client does not try to understand why DBA needs more time to work on issue or resources. A good DBA if is able to code is great combination. DBA should be familar with chgallenges of both DEV and Prod DB environments.

deciding execution plan when query is transformed.

Oracle 10g brought cost based query transformation. Consider two below cases and decide what plan of COST based optimizer is good.  Is transformation it is considering is good.

INDEX IDX_HIST_ID ON HISTDATA(ID)

exec :b1:=1

SELECT  /*+ use_nl (A B ) */  MAX (id)
                         FROM histdata A ,  sustained_feed b
                      WHERE A.id=b.id
                        AND b. data_bucket = :B1;

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   1779                    
  SORT AGGREGATE 1   10                    
    NESTED LOOPS 610 K 5 M 1779                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      FIRST ROW 35 K 105 K 104                    
        INDEX RANGE SCAN (MIN/MAX) MT_RPT.IDX_HIST_ID 35 K 105 K 104                    


SELECT    MAX (id)
                          FROM histdata
                         WHERE id IN (SELECT  id
                                                   FROM sustained_feed
                                                  WHERE data_bucket = :B1);


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   872                    
  SORT AGGREGATE 1   10                    
    HASH JOIN 610 K 5 M 872                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      INDEX FAST FULL SCAN MT_RPT.IDX_HIST_ID 933 K 2 M 759                

corrupt indexes key-rowid value mismatch

Short Circuit caused power Down=>SAN Down=>All databases servers using this storage  Crashed=>Oracle 9i DB started fine=>but…OOPS! SAP Application not working properly

Once a User complained about some modules having issues :
There was a Database Alert Log recorded ORA-600 internal error with different first actual code argument. A search into Meta link showed that DB had problems in executing DMLs on some tables, as some of  the indices on these tables had logical corruption. So it was thought, when DB crashed, DMLS on these tables were in progress,causing update of index entries. so BAD row mismatch occurred as key-row information was not consistent. So  queries and DMLs which would involve these indexes were failing.
Restart of the database, automatic Crash Recovery of Database[without intervention of DBA] was successful, there was no corruption shown in logs nor in any datafile block.  So when DB started,  SAN was made up again. After restoring power supply, it was opened successfully. But it had a logical corruption hidden inside bad leaf blocks of indices.  Automatic crash recovery was not able to fix index logical corruption. This is obvious behavior. Some  won’t expect such issue and blame RDBMS some won't. I won't.


Solution:


1. Identify such indices which are in bad shape and fix problematic indexes
a) drop and recreate such indexes  OR
b) rebuild them  online so that it read key values/rowed from table (normal rebuild  reads existing index entries so corruption does not go away)


2. Restore from backup and recover
The latter  was not feasible as DB had size 1TB and prod database was on RAID 5. It would  have taken more than 2 days to restore it!!!

Another case:
I seen some people killing their application had corrupt indexes. It means when they killed they had some running DMLs on tables.

oracle education content

Oracle has changed a lot the look and feel of its tutorials. They have changed links to reach viewlets. You need good at search. below is URL:

http://apex.oracle.com/pls/apex/f?p=44785:2:1734844480451698::NO:2,CIR,RIR:P2_PRODUCT_ID,P2_RELEASE_ID:2014,85

litle bit on jdbc & oracle when every milisecond matters

when every milisecond matters

1. use OCI JDBC driver , undoubetdly they are fastest JDBC driver , especially when there is lot of fetch. I found them performing 10% faster for a bulk fetch.

2. if java application has to fecth large data then use larger fetch size .

3. When DB server and Java application server are on same box, use OCI JDBC driver. In this case db connection will be using IPC and gives best performance. And ovbvious as states in 1. above , JDBC OCI are fastest even when used for remote connection to DB.

4. if using connection pooling,there should be good number of connections available otherwise there will be more opened cursors per session.

5. perform analysis for use of preparestatement vs statement. Don't go blindly for preparestatement

6. use batch update but just be wise to choose batch size. If keep it is small no benefit and if it is large again it will need more undo in database. Similarly guys when you add statements in batch be wise loop does not iterate very much other wise you will need good memory for your java program.

some tail info not related actually to performance:

-- use the JDBC drivers matching DB server jdbc drivers version. I seen people running  oracle 10.2.0.4 but their jdbc driver was version 10.2.0.1. They had issue in fetching from result set sometimes. Fix suggested was to use matching jdbc driver and it did .

--  if using connection pool,take extreme care to check result sets are closed properly and opened cursors does not exceed limit.

text indexes in comparison query ( tune like query using text index for large tables)

consider text comparison query:
select e.empno,e.ename,d.loc,d.dname from emp e ,dept d
where e.ename like '%'||d.dname||'%'

This is suitable case of use of text indexes if table emp is quite big.

steps:

1 .FROM DBA ---------

GRANT SELECT ON ctxsys.dr$ths_phrase to scott;
GRANT EXECUTE ON CTX_DDL TO scott;

FROM CTXSYS USER ----------------
exec ctx_ddl.create_stoplist('empty_stoplist', 'BASIC_STOPLIST');
exec ctx_ddl.create_preference('matching_lexer', 'BASIC_LEXER');

2.FROM application USER scott ------

CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

in order to take benefit of this text index rewrite above query as below :

select e.*
from emp2 e, dept d
where CONTAINS(e.ename, '%'||d.dname||'%')>0


***** Spool outputs along with execution plans *****

SYSTEM session:

1* alter user ctxsys identified by sys
SQL> /

User altered.

SQL> GRANT SELECT ON ctxsys.dr$ths_phrase to scott;

Grant succeeded.

SQL> GRANT EXECUTE ON CTX_DDL TO scott;

Grant succeeded.

SQL> exec ctx_ddl.create_stoplist('empty_stoplist', 'BASIC_STOPLIST');

PL/SQL procedure successfully completed.

SQL> exec ctx_ddl.create_preference('matching_lexer', 'BASIC_LEXER');

PL/SQL procedure successfully completed.


--SCOTT session --

SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 modify empno number(7);

Table altered.

SQL> alter table emp2 modify ename varchar2(200);

Table altered.

SQL> create sequence s1 ;

Sequence created.

SQL> insert into emp2(empno,ename) select s1.nextval,'SALES' from emp2;

15 rows created.

insert repeated for

30720 rows created.

SQL> commit;

Commit complete.

SQL> insert into emp2(empno,ename) select s1.nextval,'ABCD SALES HOLA' from emp2 where rownum<=1000; 1000 rows created. SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into emp2(empno,ename) select s1.nextval,'ABCDACCOUNTINGEXYZ' from emp2;

62440 rows created.

SQL> commit;

Commit complete.

SQL> CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

Index created.

SQL> set autotrace traceonly explain
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 224 | 45696 | 66 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 224 | 45696 | 66 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 56 | 10864 | 66 (0)| 00:00:01 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> insert into emp2(empno,ename) select s1.nextval,'bola' from emp2 ;

124880 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly explain statistics timing on
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0 ;

124866 rows selected.

Elapsed: 00:00:01.23

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 682 | 135K| 146 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 682 | 135K| 146 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 170 | 32980 | 146 (0)| 00:00:02 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
750 recursive calls
0 db block gets
10354 consistent gets
5 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:00.84

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 682 | 135K| 146 (0)| 00:00:02 |
| 1 | NESTED LOOPS | | 682 | 135K| 146 (0)| 00:00:02 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 170 | 32980 | 146 (0)| 00:00:02 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
9682 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> drop index EMP_ENAME2;

Index dropped.

Elapsed: 00:00:02.93
SQL> ed
Wrote file afiedt.buf

1 select e.*
2 from emp2 e, dept d
3* where e.ename like '%'||d.dname||'%'
SQL>
SQL> /

124866 rows selected.

Elapsed: 00:00:01.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 1 | NESTED LOOPS | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 10783 | 1916K| 207 (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
11239 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:01.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 1 | NESTED LOOPS | | 43131 | 8087K| 831 (2)| 00:00:10 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 10783 | 1916K| 207 (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11157 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> insert into emp2(empno,ename) select s1.nextval,'z' from emp2;

249760 rows created.

Elapsed: 00:00:02.75


SQL> commit;

Commit complete.

Elapsed: 00:00:00.10
SQL> set autotrace off
SQL> insert into emp2(empno,ename) select s1.nextval,null from emp2;

499520 rows created.

Elapsed: 00:00:06.32
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> CREATE INDEX emp_ename2 ON emp2 (ename) INDEXTYPE IS CTXSYS.CONTEXT parameters ('lexer CTXSYS.matching_lexer');

Index created.

Elapsed: 00:00:22.06
SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0
4
SQL>
SQL> set autotrace traceonly explain statistics timing on
SQL>
SQL> /

124866 rows selected.

Elapsed: 00:00:00.86

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1852 | 368K| 356 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | 1852 | 368K| 356 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 463 | 89822 | 356 (0)| 00:00:05 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
392 recursive calls
0 db block gets
10671 consistent gets
2 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> select e.*
2 from emp2 e, dept d
3 where e.ename like '%'||d.dname||'%' ;

124866 rows selected.

Elapsed: 00:00:01.42

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 185K| 33M| 2103 (3)| 00:00:26 |
| 1 | NESTED LOOPS | | 185K| 33M| 2103 (3)| 00:00:26 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 46308 | 8230K| 525 (3)| 00:00:07 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15761 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> /

124866 rows selected.

Elapsed: 00:00:01.42

Execution Plan
----------------------------------------------------------
Plan hash value: 4088618096

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 185K| 33M| 2103 (3)| 00:00:26 |
| 1 | NESTED LOOPS | | 185K| 33M| 2103 (3)| 00:00:26 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP2 | 46308 | 8230K| 525 (3)| 00:00:07 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."ENAME" LIKE '%'||"D"."DNAME"||'%')

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15693 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

SQL> select e.*
2 from emp2 e, dept d
3 where CONTAINS(e.ename, '%'||d.dname||'%')>0;

124866 rows selected.

Elapsed: 00:00:00.82

Execution Plan
----------------------------------------------------------
Plan hash value: 4225699595

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1852 | 368K| 356 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | 1852 | 368K| 356 (0)| 00:00:05 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP2 | 463 | 89822 | 356 (0)| 00:00:05 |
|* 4 | DOMAIN INDEX | EMP_ENAME2 | | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CTXSYS"."CONTAINS"("E"."ENAME",'%'||"D"."DNAME"||'%')>0)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
52 recursive calls
0 db block gets
10242 consistent gets
0 physical reads
0 redo size
2295427 bytes sent via SQL*Net to client
91959 bytes received via SQL*Net from client
8326 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
124866 rows processed

thoughts on wheel

I wrote this post from mobile phone while coming from office so it is thoughts coming on wheel.

Setting up database connectivity from oracle(10.2.0.4 on RHEL 5.3 64bit) to foreign data source(any odbc compliant database mysql, mssql server, postgres, netezzza )

What softwares you need to have:

-11g Gateway (if connecting to mysql needs patched to 11.1.0.7) or 11.2.0.1 gateway w
-ODBC Driver Manager (download from unixodbc.org)
-ODBC driver [provided by DB vendor or third party like Data Direct technologies]
- 10g software should have interim patch ******* installed for some bugs for mysql.

Here is steps outline:

1. install ODBC Driver Manager [ need check it is right version]
2. install mysql connector [ need check it is right version]
3. prepare /etc/odbc.ini [location given by env variable
and /etc/odbcinst.ini [location given by odbcinst -j -q]
export NZ_ODBC_INI_PATH=/etc
export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini

4. prepare DSN file
5. check connect from isql [dsn_name] -v
6. install oracle gateway
7. preparare initINSTANCENAME.ora in gateway/hs/admin
8. prepare gateway listener with librarries specified in envs
9. start gateway listener
10. create tnsname which addresses to gateway listener
11. create dblink using tnsname name created in 9.
12. test dblink with various cases

Config files you need to create:

1. Odbc.ini and odbcinst.ini for odbc connectivitt.
2. initDG4ODBC.ora in 11g gateway home/hs/admin
3.

NetApp - You will love too

Much overlooked : UAT and DEV DB standard

Much over looked thing while building UAT and DEV databases is it does not model Production database. I'v seen people tend to ignoring unless they are pushed hard to keep UAT and DEV databases as much as close to Production database design, Data distribution and Hawrdware/Software environment.

for e.g. There was an Java Application running on Tomcat - Apache on Solaris 10 on on 64 bit SPARC machines.

1. UAT database was refreshed from export dump instead from Physical Hot/Cold/RMAN backup of Prod. Extent size on UAT and Prod was different. Prod had large fragmentation in some tables,indexes. While UAT did not have as it was refreshed from export dump

2. Statistics were gathered in different way than Production.

3. All database were placed on single Disk array on UAT database. Production has three mirrored copies of Redo logs while UAT has no mirrored redo log.

4. Application was using Connection pooling implemented through java developed code in application only (instead using Oracle's default connection pooling or connection pooling of Weblogic etc)

5. UAT middle tier was using different JDBC driver than Production.

Basic questions to understand Oracle 10g RAC

My Friend and old Pupil Jamshed went through couple of interview questions in RAC in Interviews. To help guys like him here I thought of  more possible questions compiled by me on Oracle 10g RAC to hackle your mind for good. It is not Boggling. It will help you better understand the concept of RAC basics(except GCS and GES role)

1. what is node eviction(gud to start with simple question)
2. what is split brain
3. who do your client connect to VIP or public IP? or is it your choice!
4. how can you change VIP
5. can private IP be changed.
6. what does root.sh do when you install 10g RAC.
7. how is virtual IP configured,what is done behind the VIP configuration assistant.
--some simple questions
8. what is client balancing and server side balancing.
9. how does listener handles requests in RAC
10. Have you ever set TAF. If yes ,expalin how does fail over happens
11. how can cache fusion improve or degreade performance.
12. Have you ever faced any performance issue due RAC
13. what is the background process for cache fusion. Does it have anything to do with log writer process.
14. will you increase parallelism if you have RAC, to gain inter instance parallelism. what are the considerations to decide.
15. what is single point of failure in RAC
16. how do you backup voting disk and how do you recover.
17. what information is stored in OCR, what if you loose it. How can you recover it.
18. how many voting disks and OCRs you can have. Why voting disks can be in odd numbers only.
19. A query running fast on one node is very slow on other node. All the nodes have same configurations. What could be the reasons.
20. Does RMAN behave differently in RAC?
21. Can archive logs be placed on ASM disk. what about on RAW.
22. have you ever used OCFS, can you place OCR and voting disks on OCFS.

Don't assume!

We can not assume in same way we don't believe in rule of thumbs!

consider query:

select c1, sum(c2)

from t1

group by c1

this query returns result set in sorted order of c1 but it changes in 10g R2 as 10g used HASH GROUP BY Operation to implement grouping,rather than using SORT GROUP BY as it would do in earlier versions. So Here if sorting is desired there must be explicit order by query.

Similary There can be some join queries in which users might be getting sorted result set , but they can not rely on it always, may be if execution plan changes it can not sort the result set, so if sorting is required, developers need explicit specify order by clause in query.

I remember a case in which a junior developer wrote a query to dump the table data to asciii csv file, Here was obviously clear columns data in csv need in same order as in table. But as Developer came to know about view user_tab_columns I told him, he used query on this view to estimate the maximum record length of table in csv file(rather than manully summing the all columns widths of table) what he could have done alternate way is set large linesize along with trimspool on, but he wamted to cut short work of typing select c1||','|| c2||','||c3||','||... from table. So he generated this select query from user_tab_columns. But he assumed columns orders would be same as in table name. Result was wrong columns order in csv file. So please don't assume - it was view- so not guaranteed.

 

I/O how much you have - mind it!

Rules of thumb are never advised by me but some can be taken as part of check list one by one while tunning I/O:

rule 1: I/O how much you have - mind it! so rule 1 is minimize I/O

rule 2: maximize cached I/O

rule 3: minimize I/O contention.

how to cut I/O:

1. cut unnecessary fetch. Be restrictive about columns in selected list. Make sure all columns fetched in explicit/implicit cursor are used some where in code. try take benefit of 'fast full index scan' .

2. check usefullness of indexed columns. They may be slowing DMLs and not yielding any query performance gain at all. So identify such indexes and drop.

3. avoid triggers which performs lot of queries/transactions and auditing from inside - these may actually be slowing DMLs especially when dmls in bulks are issued .

4. check all tables/indexes have appropriate values ser for PCTFREE and PCTUSED .

PCTFREE has default 10% so you may be wasting not only 10% extra disk/cache memory but also causing more I/O for objecting not undergoing future updates.

similarly setting PCTUSED quite higher means taking block more frequent on/off from free list.

trick: setting PCTFREE higher can reduce hot block contention.

5. If CPU resources are available some tables can be compressed. this will not ony minimize the I/O at the expense of CPU but also meets the objective "maximize cache" - how ? Because table now needs less buffers, you have more free buffers where other objects can be assigned. This is very useful in case when there is no shortage of CPU but scarcity of memory is.

Remember 10g has compress feature for only CTAS and insert into select queries. It is 11g with which comes OLTP table compression.

6. If using materialized views for replication or reporting then, try their refresh possible by FAST method. And if using FULL mechanism , think twice. What about rfresh by truncate/insert.

7. Optimize query execution plan. It is subjective topic in itself.

- tables are indexed appropriately and indexes have good selectivity. If index is not unique it will be good to have indexes with low clustering factor.

- check all tables have accurate statistics and statistics must have been gathered when tables had representative data.

-You need check instance optimizer parameters have been set correctly. If it is RAC instances they should have same values on all instances.

- Trade off of saving CPU versus good execution plan.

cursor_sharing =exact may be far better than cursor_sharing=similar or cursor_sharing=force

 

 

Maximize cached I/O

1. explore if you need configure KEEP and RECYCEL pools in your database for frequently accessed(small in size) and least accessed(bigger) tables and the set and size them appropriately. Assign the related objects to these pools.

2. set the buffer cache appropriately enough high to minimize physical reads.

3. if using bigger SGA > 16GB, in linux use huge pages memory.

4. set the PGA_AGGREGATE_TARGET appropriately. remember higher value for PGA can favour sort merge join over nested loop join.

.

.

.

 

Minimize I/O contention:

 

Balance the I/O across multiple disks array if possible.

take care of all I/O source redo logs, undo tablespace, temporay tablespaces , index tablespaces and DATA tablespaces and archive log too if DB is running in archivelog mode. SPREAD these across Disks, depending on their concurrent usage. you can check statspack/AWR report for I/O usage on tablespace/datafile wise.

.

.

.

Love Me, Love The Life I Lead Lyrics

(words & music by macauley - greenaway)
I am not a wise man neither am I a fool
But what I am the way the good lord made me
Though I need you more than you may ever understand
I cant wear a face that will betray me

Oh, if youre gonna love me, love the life I lead
Need the things I need, dont try to change me
If youre gonna take me, take me for what I am
I cant be another man, I cant be free

full lyrics

more Elvis Presley lyrics

basics of connectivity - connect Oracle Database Server and easy connect from 10g

go to oracle installation directory,like C:\oracle\product\10.2.0\db_1\network\ADMIN\

check for listener.ora and tnsnames.ora file at DB server.
if there is IP/port change make sure to reflect same here.

if OS is windows : Net Manager tool can be run from:

windows > start > oracle entry in

to check what system level DB privilege have been granted:sqlplus /nolog
SQL>conn username/pwd
SQL> select * from session_privs;

using SQLPLUS login from sys or system user:

sqlplus /nolog
SQL> conn sys/pwd as sysdba
drop user username cascade;

grant connect,resource to username identified by pwd;

From 11g onward connect role has only the create session privilege.

then from cmd line:

imp username/pwd file=filename.dmp fromuser=scott touser=username log=impuser.log

Oracle easy connect feature from 10g onward:

sqlplus username/pwd@IP_ADDRESS_OF_DB_SERVER:PORT_NUMBER/SERVIVCE_NAME

sqlplus scott/tiger@192.168.90.100:1521/DEVDB


 

tracking DDL,while DB is in noarchivelog mode

You want to tack DDL and your DB is in noarchivelog , attempted as below,[no way but to use catalog in flat file]
SQL> conn / as as sysdba
Connected.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '13-apr-2009 12:42:00', -
> ENDTIME => '15-apr-2009 11:55:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);
BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => '13-apr-2009 12:42:00', ENDTIME => '15-apr-2009 11:55:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE); END;

*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream

And again below you get error when utry use redo logs for building dict
SQL> EXECUTE DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
BEGIN DBMS_LOGMNR_D.BUILD ( options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS); END;
*
ERROR at line 1:
ORA-01325: archive log mode must be enabled to build into the logstream

if DB bouce is affordable go ahead as follow:
SQL> alter system set utl_file_dir='c:\ora';
SQL> shutdown immediate
SQL> startup

SQL> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', 'c:\dict', OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING); END;
*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner sessionORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

seems in much hurry!!

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO01.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO02.LOG', OPTIONS => DBMS_LOGMNR.NEW);

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\CORPENH\REDO03.LOG', OPTIONS => DBMS_LOGMNR.NEW);

[file 3 was current log group file]

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DDL_DICT_TRACKING);

SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
48050

SQL> create table t1 as select * from v$logmnr_contents;
Table created.

SQL> create table t2(c1 number);
Table created.

SQL> truncate table t2;
Table truncated.

SQL> create table t1_log as select * from v$logmnr_contents;
Table created.

query from, another session SELECT t.session_info,t.sql_redo, t.* FROM t1_log t WHERE UPPER(sql_redo) LIKE UPPER('%truncate%') OR operation LIKE 'DDL'
gives: DDL operations above in SQL>prompt are also tracked

tracking down resource intensive queries - made easy 10g

from 10g u dont need continuously query v$sesion_wait to track wait events and log in the data to table to aggregate them: follows ASH


top wait events and total time waited :

select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2

top sessions id,username and total time waited :

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3



top user id/name,sql_text and total time waited:

SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

top segments name/type ,events and total wait on them:


SELECT dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
SUM(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.current_obj# = dba_objects.object_id
GROUP BY dba_objects.object_name, dba_objects.object_type, active_session_history.event
ORDER BY 4 DESC

top 10 completed queries:

SELECT *
FROM
(SELECT sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time,
elapsed_Time/executions,executions
FROM sys.v_$sqlarea
WHERE executions>0
ORDER BY 6 DESC)
WHERE ROWNUM < 11


executions=0 means query are currently in progress

extracting execution plan for library cache:


SELECT LPAD(' ', 2*(LEVEL-1))||operation||' '||
DECODE(ID, 0, 'Cost = '||position) "OPERATION",
options, object_name
FROM v$sql_plan
START WITH (sql_id='&sql_idof_query'
AND child_number = 0
AND ID=0 )
CONNECT BY PRIOR ID = parent_id
AND PRIOR address = address
AND PRIOR hash_value = hash_value
AND PRIOR child_number = child_number
ORDER BY ID, position

query taken: merge join

SELECT st.sql_id,operation,options,object_name,
FROM v$sqltext st , v$sql_plan sp
WHERE st.sql_id=sp.sql_id AND
operation LIKE '%MERGE JOIN%' AND options IS NULL
ORDER BY st.sql_id,piece


query to find sqls which have text 'INDX_IND1':

SELECT /*+ hola */ * FROM v$sqlarea WHERE sql_text LIKE '%INDX_IND1%' AND sql_text NOT LIKE '%hola%'

Parallel Query in Oracle !! Beware!!

Run query in parallel only if you have idle CPUs and Table is distributed across disk but again be careful to choose degree of parallelism. Over Parallelization can kill performance

just imagine of 8 CPUs and parallel_threads_per_cpu parameter default value 2

you fired a query with degree 4 but its taking cpu_counts*parallel_threads_per_cpu =16! You can hit few moere such a query and see PX idle wait evens and helplessly watch terrific slow down!!

query session wait details for parallel query(PX wait events)-

SELECT * FROM v$Session_wait
WHERE sid IN ( SELECT sid FROM v$session
WHERE
username='TEST_USER' AND
status='ACTIVE'
)
AND wait_time=0
AND sid IN ( SELECT sid FROM v$px_session)
ORDER BY 1


351 1699 direct path read file number 5 0000000000000005 first dba 2588307 0000000000277E93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
416 1498 direct path read file number 5 0000000000000005 first dba 2573843 0000000000274613 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
419 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
445 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
575 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
618 996 PX Deq: Execute Reply sleeptime/senderid 200 00000000000000C8 passes 1 0000000000000001 0 00 2723168908 6 Idle 0 21 WAITING
684 26 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 26 000000000000001A 0 00 2723168908 6 Idle 0 3 WAITING
693 1616 direct path read file number 5 0000000000000005 first dba 2566803 0000000000272A93 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING
702 1329 direct path read file number 5 0000000000000005 first dba 2599955 000000000027AC13 block cnt 126 000000000000007E 1740759767 8 User I/O 0 0 WAITING

------------

345 194 PX Deq Credit: send blkd sleeptime/senderid 268566527 000000001001FFFF passes 175 00000000000000AF qref 0 00 1893977003 0 Other 0 0 WAITING
356 1248 PX Deq: Execution Msg sleeptime/senderid 268566527 000000001001FFFF passes 1244 00000000000004DC 0 00 2723168908 6 Idle 0 0 WAITING

So better you diable parallelization of tables by statement alter table noparallel

Oracle Bugs and Life!!I I'm amused to see.

Its been 2 months!!! Much has since in life and seems life will never be same again. oh.. and Im back again to Post but little.

Although once I did it already I faced issueses again in installation of Oracle (bit) version on RHEL 5.2 (64 bit ) and it took again few more hours to delay installation. But caused more panicked than before.

Issue was same again that Oracle Universal Installer was still requiring 32 bit libraries to start with. That is known bug but Oracle Documents is missing this point even in lates version of docs.

RPMS default installation includes those libraries but system administrator did not do default RPMSs installation rather he customized and so dependency call of 32 library was unresolved.


One another day I was awaked in middle of night by call: error was 4031 but that was not so simple so obvious here culprit was new bug in 10g ERROR 4031 was misleading.
Oh what the life has become !!!

And more: one day when I stayed home to take care of some my dear one at same time I had to monitor DB from home. But much more than this some important query used Graphical User Interface hanged and major work suffered. Many users sat idle!! They had no option but to call me to fix Oh what a life has become!! while I did not have remote access to that Prod DB I asked Junior DBA to send me execution Plan and I guessed query can be fixed to get the same old execution plan by setting hidden parameters!! Parameter turned off using B+ indices as Bitmap Index and it worked!! oh what a life is!!! It simply rocked. Ah but what a life has become. But I'm amused to think what I did and I did it my way!!!

adieu !! till Im back

extracting SQLS from redo logs 10g to tracks all/commited changes

I -to track all(commited+non commited changes)

since v$logmmr_contents data is only persistent to session in which log miner is run a table logmined_commited is created to store all extracted information.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

In below options parameters is saving the time of adding individual redo logs/archive logs.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-jan-2008 01:00:00', -
> ENDTIME => '03-jan-2008 21:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.

SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
98558

SQL> create table extlogs as select * from v$logmnr_contents;

Table created.
------

OR

II -to track commited changes only

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> STARTTIME => '01-JAN-2008 01:00:00', -
> ENDTIME => '03-JAN-2008 23:00:00', -
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
> DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.


SQL> select count(*) from v$logmnr_contents;

COUNT(*)
----------
54994


SQL> create table extlogs as select * from v$logmnr_contents ;

Table created.

SQL> exec DBMS_LOGMNR.END_LOGMNR( );

PL/SQL procedure successfully completed.

sample query from extracted logs:
SELECT SEG_OWNER, SEG_NAME,operation, COUNT(*) AS Hits FROM
sys.logmined_commited
WHERE SEG_OWNER NOT IN('SYS','SYSTEM','WKSYS')
AND TRUNC(timestamp)='01-jan-07'
GROUP BY SEG_OWNER, SEG_NAME,operation
ORDER BY 1,2,3,4

above shows tables which had undergone DMLS

below shows DML type,corresponding DML statement and equivalent UNDO statement to undo DML for above affected objects.

SELECT OPERATION, SQL_REDO, SQL_UNDO
FROM sys.logmined_commited
WHERE SEG_OWNER = 'SEGOWNER'
AND SEG_NAME = 'segment_name'
AND OPERATION = 'UPDATE'
AND USERNAME = 'username';

Performance Tuning considerations for developing new Database system

Over all tuning = Oracle Tuning + OS Tuning (including Network Tuning)
Oracle tuning = DB Instance tuning + Application tuning

DB Instance tuning =Tuning Instance initialization parameters

Application Tuning=SQL Tuning + PL/SQL tuning

OS Tuning= Tuning Kernel Parameters+use of large page size of virtual memory+tuning of swap space+tuning of network kernel parameter+tuning of I/O kernel parameters

 

Whenever we have tuning issues I found to tune at system levevl is best approach efforts to gain higher benefit in short time. This is the area where lies the maximum scope of return of efforts as mostly its ignored. So Let us first begin with this.

 

1. Resources under utilized

 

a) SGA+PGA not configured to make best use of available memory.

 

b) Database files not distributed to balanced I/O. Most of the cases they are not following SAME methods.

 

c) Database code is not using partitioned tables or parallelization benefit

 

So even finest tuned aplication would be slow if they wait/content too much for memory/cpu/IO.

 

2. Poor hardware configuration

 

a) very few CPUs or underpower CPU cores

 

b) less memory so more physical I/O and swapping

 

c) not using appropriate RAID level, for e.g. using RAID for write intensive DB or using same for storing redo log files

 

d) very few diks array or physical drives

 

I found many servers having 4GB RAM, RAID 5 and too with only single logical array drive. To make it more slow I found less RPMS of individual hdds in Disk Array

 

3.Poor indexing

• indices missing
• indices unselective
• indices not used
• In other cases I found over indexing was kill for slow DMLs
• too high clustering factor

 4. Statistics not gathered in way to provide  accurate statistics or they are stale

 

database configuration parameters

 

Poor rdbms version / Poor operating system - One should be wise to choose RDBMS software and OS.

 

5. extra load[unnessary work] I found one database project where there were too much redundant materialized views.

 

6. Too often gathering statistics

 

7. bugs(in case some specific sqls are slow) mostly these re fixed in patches.
not using hidden parameters to counter these

 

8. not using latest upgrade may also be one issue for performance slow down as new versions and patches generally have fixes for the known performance bugs and

 

9. Not using the latest features is also one concern for performance tuning one should go for features such as analytial functions,Bulk loading, Query Subfactoring [with clause ], partitioned tables can be used if required.