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 )
SQL> select emp_id,empname,sal,loc,dept from EMP
emp_id empname sal loc dept
01 Hari 10 SJ ENG
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);
2. for SCHAME : exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SCHEMA_NAME’);
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.
explain plan for your-precious-sql-statement;
explain plan into table_name for your-precious-sql-statement;