Oracle database Query Plan and Siebel performance

Before we dig into details of second performance issue. I will like to discuss a bit about feature of oracle datbase called Query Plan.

Now, I am not a DBA and I worked with query plan for the first time during these performance issues so I can only provide you basic details and that too from Siebel perspective only.

In simple words query plan is path taken by database to execute the query and return the results. Now, normally in SQL query there are lot of tables, columns and conditions are involved and there can number of ways in which this SQL might be executed.

The path to be taken to execute the query is determined by the optimization mode of database. For oracle database there are 2 types of optimizer modes are available

• Rule based Optimizer (RBO)
• Cost based Optimizer (CBO)

Cost based optimizer was introduced in Oracle 7 and it is the preferred mode when it comes to Siebel – Oracle combination. The reason being

“The CBO chooses the best path for your queries, based on what it knows about your data and by leveraging Oracle database features such as bitmap indexes, function-based indexes, hash joins, index-organized tables, and partitioning, whereas the RBO just follows established rules (heuristics).“

As I mentioned in my first post of this series that we need to issue an Alter session command before the query executed on toad starts behaving like Siebel UI session is because setting that parameter makes oracle choose a different path to execute the same query, hence there is difference in response time.

Oracle gives us the ability to see the path taken to execute the query with the help of query plan.

The SQL statement to generate query plan is

EXPLAIN PLAN FOR
your SQL statement here


for example:

explain plan for
select * from s_evt_act


The above statement will generate the query plan for SQL and store it in table called plan_table. The above statement will not give you any output.

To view the query plan that has been generated you need to execute the following SQL statement


select * from plan_table


all the query plans that are generated are stored in same table so generaly you would want to delete the old records before generating the query plan so that you have latest plan. You can use the statement given below to delete the old rows from table

delete from plan_table

this will delete all the old rows from plan_table and you can generate a new query plan.

Now that you know how the generate and see the query plan we are ready to start with second performance issue.