Gather Schema Statistics on EBS 11.5.8 with Gather Auto

Recently a client of ours who’s on EBS 11.5.8 and 9i came up with  a request – to run Gather Schema Statistics with the Gather Auto Option “without setting statistics_level as typical”. Yes, you heard me right.

I met with many road blocks in this course and now finally, I have overcome all these. Below is a very brief pointers on how I went about it. The reason I am posting this is that I see may hits which come from Google with the Search Item EBS 11.5.8 Gather Stats in my hit counter. So there are people out there who are looking for similar solutions.

I analyzed the FND_STATS package which is called by the program “Gather Schema Statistics (GSS)”. Upon this, I found that FND_STATS is actually a wrapper for DBMS_STATS package. DBMS_STATS package is the db package which enables statistics gathering. FND_STATS adds extra validation and a few important functionalities to the DBMS_STATS. As per Oracle; we require the parameter statistics_level to be set to “typical” in order to use “GATHER AUTO”.

Analyzing the FND_STATS showed that whenever Gather Schema Statistics is run with GATHER AUTO parameter, the package actually gets a list of tables which do not have MONITORING enabled and then it proceeds to gather stats for all tables in this list @ an estimate percent 10 and option as GATHER. It will then execute and ALTER SESSION statement which will enable monitoring for this table. This means from that point of time, data will be gathered in all_tab_modifications table on the INSERT/UPDATE/DELETE action on the said table.

What this means for us: We can run GSS with GATHER AUTO option and it will in the first run do two things:

a. Gather stats at 10% for ALL tables satisfying certain criteria.

b. Enable MONITORING for all tables in above step.

I tried running the normal GSS with GATHER AUTO and the some request completed while some errored out. Upon analyzing the errored SCHEMAS, it was found that they errored out because the session could not get a LOCK with NOWAIT clause – which meant that certain tables in those schemas were being accessed & locked by another process when this happened.

I went back to FND_STATS wrapper and deduced the logic which it used to get the list of tables and enable MONITORING. I then wrote a script which would do this for me schema by schema. I was able to update all tables except those in APPLSYS schema. This is because once the EBS Application is up, it will continuously access certain tables like FND_CONCURRENT_PROCESSES, FND_CONCURRENT_QUEUES, FND_CONCURRENT_REQUESTS etc. I ran this script with the application (incl. CCM) down and all tables was updated.

I ran the request set with LIST AUTO parameter (this will list out all the tables for which stats would be gathered if we use GATHER AUTO). To my surprise, it did not list a single table whereas ideally it should have listed a number of tables. Then, a search on metalink pointed me to a Bug which caused oracle to ignore the default value of 10 for the parameter “modifications threshold”. This parameter is responsible for determining the cut off %change in a table after which stats would be gathered for it. There are not patches as of now; however a workaround exists and I implemented it. (you just have to manually enter the value instead of leaving it blank for Oracle to default it ; neat ha?) I ran the GSS request set with LIST AUTO again and it picked up many tables. Then I submitted the GSS with GATHER AUTO option. The program completed successfully.

Conclusion:

Here I have run GSS with GATHER AUTO at a default modifications threshold of 10%. In different environments we might need to have different values for this parameter for a set of tables which are very dynamic. This list can be deduced by monitoring the performance of the Application over a period of time.

This post is supposed to be a pointer for people out there who are trying to achieve something similar. Also also a note: FND_STATS can be can be customized to reflect a customer’s own needs. Though this is not supported by Oracle, no harm in doing this in an already de-sup version. Let me know if any of you are interested.

All you need to know about Execution Plans -I

This article will cover all that you need to know about execution plans in order to do SQL tuning. As this is a vast topic (believe me), I will cover this in a series of articles.

Whenever I have a SQL which is running long, the first step I do is to obtain an execution plan. An Execution plan lists out the steps taken by Oracle to execute a SQL statement. It will give you details about ordering, access method, join methods, data operation involved, optimization, cardinalities. This is one torch which will show you the path 95% of times.

There are 3 main ways in which once can get an execution plan for a SQL. They are given below:

  1. Using “explain plan” statement.
  2. Using the oracle provided dynamic performance views.
  3. Enable Trace / Autotrace.

Explain Plan statement

The Explain Plan statement shows the execution plan chosen by oracle for SQL statements (SELECT, UPDATE, INSERT or DELETE).

Pre-reqs

  1. You should have a plan table which will hold the o/p of this statement. You can create one using the script $ORACLE_HOME/rdbms/admin/utlxplan.sql if you wish to create separate plan table for the resources, you can edit this and create multiple tables.
  2. You should be able to execute the SQL for which you are trying to obtain the explain plan. If your SQL contains views, then you must have sufficient privileges in the underlying tables and view. In many cases you may be able to execute a select on a view but not an explain plan. This is because your user id does not have the execute privileges on the underlying tables.

Syntax

The syntax for this is shown below (ref: Oracle Document)

Eg:

  1. Using all the above cases:
EXPLAIN PLAN
SET STATEMENT_ID =’plan1’
INTO my_plan_table
FOR
SELECT employee_name, department, salary FROM employee WHERE salary > 10000;
  1. You may use the default plan table in Oracle; In that case you can do away with the INTO clause.
  2. SET STATEMENT_ID is requires when many people are using the same plan table. The statement ids will help you differentiate your plan from others.

In my case, I am the only guy working on tuning and I use

EXPLAIN PLAN FOR
SELECT employee_name, department, salary FROM employee WHERE salary > 10000;

Displaying the plan

  1. To retrieve the plan from the plan table you can use a simple query like
Select * from plan_table where statement_id = ‘plan1’;
  1. You may also make use of the display function in the  dbms_xplan package to display the plan. This will retrieve the latest plan in the plan table:
Select * from table(dbms_xplan.display);
  1. You may also construct a SQL to retrieve rows from your custom plan table. I use the one shown below:
SELECT     ID, parent_id,
LPAD (' ', LEVEL - 1) || operation || ' ' || options operation,
object_name, NVL (BYTES, 0) BYTES,
   CASE
        WHEN cardinality > 1000000
        THEN    TO_CHAR (TRUNC (cardinality / 1000000))|| 'M'
        WHEN cardinality > 1000
        THEN TO_CHAR (TRUNC (cardinality / 1000)) || 'K'
        ELSE cardinality || ''
    END no_of_rows,
   CASE
        WHEN COST > 1000000
        THEN TO_CHAR (TRUNC (COST / 1000000)) || 'M'
        WHEN COST > 1000
        THEN TO_CHAR (TRUNC (COST / 1000)) || 'K'   
        ELSE COST || ''
    END COST
   FROM table_plan_8508
  WHERE STATEMENT_ID = 'plan1'
  START WITH ID = 0 AND STATEMENT_ID = 'plan1'
CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = 'plan1';

Here, table_plan_8508 is my custom plan table and plan1 is the statement_id of the execution plan I require. This query can also be used against performance views with appropriate modifications.

Using the oracle provided dynamic performance views

We can make use of the oracle views v$sql_plan and v$sql_plan_statistics to determine the execution plan. Of these, v$sql_plan is almost the same as the plan_table mentioned above. The only difference would be a few extra columns for cursor identification etc. v$sql_plan_statistics view gives us the execution stats (time, # of rows etc.) for each operation in the v$sql_plan. You can write a query joining these two views to retrieve the runtime execution plan of a query.

There is another view, v$sql_plan_statistics_all which gives all the combined information in the above two views. I’d rather query this view than write a complex query. Note that the initialization parameter statistics_level needs to be set to “ALL” OR the query hinted to collect the execution statistics so that the execution stats are gathered. Also note that they cause an overhead which cannot be ignored. Given below is a sample query which I use to get the plan from the dynamic performance views.

 SELECT   ID, parent_id,
          LPAD (' ', LEVEL - 1) || operation || ' ' || options operation,
          object_name, NVL (BYTES, 0) BYTES,
   CASE
          WHEN cardinality > 1000000
          THEN    TO_CHAR (TRUNC (cardinality / 1000000))|| 'M'
          WHEN cardinality > 1000
          THEN TO_CHAR (TRUNC (cardinality / 1000)) || 'K'
          ELSE cardinality || ''
    END no_of_rows,
   CASE
          WHEN COST > 1000000
          THEN TO_CHAR (TRUNC (COST / 1000000)) || 'M'
          WHEN COST > 1000
          THEN TO_CHAR (TRUNC (COST / 1000)) || 'K'
          ELSE COST || ''
    END COST
   FROM v$sql_plan
  WHERE hash_value  = &&hash_value
  START WITH ID     = 0 AND hash_value         = &&hash_value
CONNECT BY PRIOR ID = parent_id AND hash_value = &&hash_value;

Using Trace

There are many types of traces. However, from the performance point of view, the most important ones are those associated with Event 10053and Event 10054.

Event 10046

This is the one I use extensively when any of the EBS programs are running long. This can also be used to capture what is going on in an SQL*Plus(or TOAD etc.) session. This is also may be the only officially oracle supported trace.

Syntax

1)      If you want to trace the current session, you may use:

ALTER SESSION SET events ‘10046 trace name context forever;

ALTER SESSION SET events ‘10046 trace name context off’;

2) If you want to trace a concurrent program session OR another session which already exists, then use:

EXECUTE DBMS_STATS.SET_EV(sid,serial#,10054,,’’);

Event 10053

The event 10053 trace actually traces the optimizer actions. Please note that this should be used as a last resort – basically because the data contained in the trace can be very difficult to comprehend in most of the cases. Have you ever wondered why the optimizer chose a particular index and not the other? Well this trace will give you an answer – at lease guide you to it.

The trace file will give you information like peaked values of the binds in the SQL, initialization parameter values, stored statistics, query blocks, single tables, sanity checks, join orders etc. To explain how to interpret this is beyond the scope of this article, I plan to cover it later in a series. Those in a hurry can refer chapter 12 of Jonathan Lewis’ book “Cost-Based Oracle Fundamentals”. It’s a great read.

Syntax

ALTER SESSION SET events '10053 tracename context forever;

ALTER SESSION SET events '10053 tracename context off';

The rest will come in part II – how to make use of the execution plan that you just obtained. Stay tuned.