Daily Archives: October 21, 2009

Oracle STATS and Oprimization

Many Java programmers share this experience, you have reviewed your code, probably re-reviewed … everything is solid ( at least you think it is) and you hand over your precious code to the performance engineer for a test drive and he comes back with numbers which you would like to keep between the two of you. There is a fair chance you are not the culprit here, rather a victim of bad Oracle DB sucking up all the time your code is so dearly saving for the rainy day ( i.e. a expensive GC run etc). So how can you prove your innocence when you are up against a pile of performance charts and number screaming “Your application/code Sucks”.

After you have dived into your code, or may be before you dig too deep into your code, there is a good chance you can find out culprit faster than you think,

As we all know we have this thing called “Indexing” in any Database and Oracle is no exception, if the tables are indexed Oracle has no need to run a full scan on the table for every SQL query you throw at it, and you might want to start looking at it first to make sure your database is indexed.

Here are the steps for your investigation. you can find details and concept in this article by Burleson Consulting, a MUST READ – http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm

Step -1 : log in as the SysDBA (root) and turn AUTO TRACE ON using command – SET AUTOT ON;

Step – 2 : execute the SQL query that is under question, or the one your app is calling – if AUTOTRACE is turned on, Oracle will generate a report after displaying the result for the query. You might need to use SQLPLUS for this, as this might not work from other client, the report part might look like this. ( This is the execution plan that CBO would provide Oracle to execute, you can find more details about this in the article above )

Example :

SQL> select emp_id,empname,sal,loc,dept from EMP

emp_id empname sal  loc dept


01       Hari      10      SJ      ENG

Execution Plan
Plan hash value: 745008044

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time |
|   0 | SELECT STATEMENT            |         |     1 |    63 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP   |     1 |    63 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         |EMP_PK |     1 |       |     1   (0)| 00:00:01 |
Predicate Information (identified by operation id):

2 – access(“EMP_ID”=73)

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


What is important here is the  “TABLE ACCESS BY INDEX ROWID” thingy which says weather your table is indexed or not and weather your query is using it. if its “FULL” than its bad news and your Oracle needs to be made to use INDEX.

So in case you see FULL, than you can use the following command/SQL statement to make Oracle use Index defined for your table, you can either do it at the TABLE level or at SCHEMA level, by the following command.

1. for TABLE : EXEC dbms_stats.gather_table_stats(‘SCHEMA_NAME’,’TABLE_NAME’,cascade=>TRUE);



More details in the article provided as a link before.

Step – 2 A : The Other Way of investigation – EXPLAIN PLAN

As any investigator we also have couple of tools at our disposal for our investigation, and one such tool for the same job is the “EXPLAIN PLAN” command.

Whenever you read or write data in Oracle, you do so by issuing an SQL statement. One of Oracle’s task when it receives such a statement is to build a query execution plan. An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.
The general syntax of EXPLAIN PLAN is:
explain plan for your-precious-sql-statement;
If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:
explain plan into table_name for your-precious-sql-statement;
If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.
You can view the results in the PLAN_TABLE with this SQL command ( more in this article – http://www.adp-gmbh.ch/ora/explainplan.html )
Displaying Execution Plan –
  substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",
  object_name                                                              "Object"
start with id = 0
connect by prior id=parent_id;

Note : Make sure they are single quotes when you copy and paste, some editors change it to something else.

Leave a comment

Filed under Uncategorized