Author: George Mcfield

Page: 1 2

Gathering Optimizer Statistics:

Being an Oracle database administrators most of us are familiar with the DBMS_STATS package. Oracle has been advising us for years to use the DBMS_STATS package instead of the ANALYZE command as it to gather statistics to be used by the Oracle optimizer. DBMS_STATS is much more powerful and offers a number of benefits over the ANALYZE command.

Optimal Execution Plans:

The DBMS_STATS is a great utility to improve SQL execution speed. It enables us to collect top-quality statistics and hence CBO will make intelligent decision about the fastest way to execute any SQL query. You can use DBMS_STATS package for deleting statistics and also for exporting and importing statistics from one database to another thus aids us in reproducing specific performance problem.

It is the responsibility of database administrators to make sure that the query uses optimal execution plan while they perform load tests or benchmark tests. After that volume data is created for the benchmark to take place. It is recommended to perform automated benchmark tests by using load runner or other load test suites.

Testing Code for Performance:

Sometimes the developer has requirement to test the code for performance while development. In general it is not possible to create volume data every time when the query is changed or you write a new code.

SET_TABLE_STATS procedure proves beneficial in such scenarios as it gives you an insight into how your query execution plan has changed with the increase in data set.

SET_TABLE_STATS procedure allows us to set number of rows and number of blocks with some large number. This makes optimizer think that the data distribution is different and there are large numbers of rows in the table. Now if you run query against these new values then optimizer changes the query plan based on the available data. Similar procedures exists that can help you set the column level and index level statistics as well.

Create Table:

SQL> CREATE TABLE MYTABLE
(
MYID NUMBER(4),
MYNAME VARCHAR(45),
CONSTRAINT PK_MYTABLE PRIMARY KEY(MYID)
)
/
Table created.

Create Index:

SQL>
SQL> CREATE INDEX MYINDEX ON MYTABLE(MYNAME)
2 /
Index created. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of PL/SQL Performance Problems!!
   Troubleshooting Oracle Performance Problems!!
   Let’s reveal the magic of Oracle Database with Web Services!!
   DBA Tips for Speeding Up Large Table Updates!!