Thursday, August 29, 2013

Oracle Indexes



Why Indexes are not used and performance checks:

Is the table Indexed?
Check that the table which you believe should be accessed via an index actually has indexes defined on it. The indexes could have been dropped or could have failed to create
- For example, it is possible, after importing tables, or performing loads, that indexes were not created due to errors (either software or human).

SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;

Why SHOULD the index be used?

Oracle does not have to use an index simply because an index exists.
If a query requires every row in the table to be queried (in table joins, for example),
then why query all the rows in the index AND all the rows in the table?
Ignoring the index in this case results in better performance.
The optimizer makes decisions based on statistics regarding the suitability of various access methods,
including indexes, for all queries and chooses the best one.

Are the indexed columns/leading column of the index supplied in the where clause of the query (predicate list) as a single table (non-join) predicate?

If not, then remember that at least the leading column of an index is required in the predicate
list to use an index in a query (but see Skip Scan below.)

Example: You have defined index EMPNO_I1 on single column EMP.EMPNO, and defined concatenated index
EMPNO_DEPT_I2 on columns EMP.EMPNO and EMP.DEPT (EMP.EMPNO is leading column).
In order for the optimizer to consider either index, you must use the column EMP.EMPNO in the predicate list (WHERE clause)

Exceptions:

ü  CBO can use an Index Skip Scan (INDEX_SS). The leading column of an index is not required for an INDEX_SS to be performed.

ü  CBO can choose to use an index to avoid sorting. The indexed columns would need to be in the order by clause for this to happen

ü  CBO can use a Index Fast Full Scan (INDEX_FFS) as long as the index contains all the columns that are needed for the query,and at least one column in the index key has the NOT NULL constraint. The leading column of an index is not required for an INDEX_FFS to be performed. Note that the use of an INDEX_FFS does not necessarily return the rows in sorted order.Ordering is dependent on the order that the index blocks are read and rows are only guaranteed to be returned in a sorted order if an 'order by' clause is used





If the indexed columns ARE part of join predicates, then: What type of join method is used to implement the join when the query executes?

For example, the following join predicate defines the join between the emp and dept tables on the deptno column emp.deptno = dept.deptno

Hash / Sort Merge Join: With Hash joins and Sort Merge joins, information from the outer table is not available at join time to enable row look ups on the inner table; rather both tables are accessed separately and then the resultant data is joined. The inner table of a Hash or Sort Merge cannot be probed solely using an index based on the join columns . This is an inherent limitation of the implementation mechanisms used by these join types.
Nested Loops joins are different in as much as they allow index lookups on the join columns.

Nested Loops Join: Nested loop joins work by reading the outer table and then using the information gathered to probe the inner table. This algorithm allows index lookups to occur on the inner table.


Is implicit type conversion going on?

If the datatypes of two values being compared are different, then Oracle has to implement type conversion on one of the values to enable comparisons to be made. This is called implicit type conversion. Typically this causes problems when developers store numbers in character columns.
At runtime oracle is forced to convert one of the values and (due to fixed rules) places a
to_number around the indexed character column. Adding any function to an indexed column prevents use of the index. The fact that Oracle has to do this type conversion is an indication of a design problem with the application.

Is it semantically impossible to use an index?

Because of cost considerations on the query as a whole, a plan may have been chosen that means that the use of an index at a lower level is now not possible. The index may have been considered in other join orders/methods but the method with the lowest cost makes the index unusable.  Because of the way the query has been executed (i.e. join orders/methods) it is now 'semantically impossible' to use an index.


PRODUCTION issue and its resolution.

One of the critical heavly used business program encountered performance issue in Production. The program was executing couple of SQL’s.We put the program in trace mode and checked the explain plan for offending SQL.
As the program was running, it showed a SQL that was just looping and could not give much inputs why it was getting in loop. The I/O reads for this session was quickly growing to million records and so.
The explain looked good with very less cost and other parameters. But there were couple of full table scans and few full table scans were not avoidable. Their were many SQL executed by the program so the complexity became more We noticed that one full table scan was avoidable but the optimizer was not taking the Index..

Datatype was character type and predicate value was given numeric. Therefore, Optimizer was ignoring index
Advised to development to review the code,once they changed the code and this was mode to PROD.
The program came back to its life and completing as expected.


Due to data security of client am not giving the actual SQL of issue faced program.

I will do a small demo:


SQL> CREATE table test_jan (enum VARCHAR2(10),ename VARCHAR2(10));

Table created.

SQL> BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO test_jan VALUES (i,'A');
END LOOP;
END; 
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> CREATE INDEX edw_ind1 ON test_jan (enum);

Index created.

Gather Stats on this newly created table:

SQL> exec dbms_stats.gather_table_stats(ownname => 'APPS', tabname => 'test_jan', cascade => true);

PL/SQL procedure successfully completed.

SQL>



SQL> explain plan for
  2  select * from test_jan where enum>500;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  9500 |   129K|     9  (23)|
|   1 |  TABLE ACCESS FULL| TEST_JAN |  9500 |   129K|     9  (23)|

Here we can clearly see that though there is index on column enum,the optimizer ignored index.




SQL> explain plan for
  2  select * from test_jan where enum>to_char('500')
  3  /

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   500 |  3500 |     6   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_JAN |   500 |  3500 |     6   (0)|
|   2 |   INDEX RANGE SCAN          | EDW_IND1 |    90 |       |     2   (0)|
-----------------------------------------------------------------------------

Now  we can see the index is used!!!


 -Edwin.K

Wednesday, August 28, 2013

Secure way of starting and stopping oracle application services




         Oracle applications DBA needed to know the APPS username and password in order to start or stop the Oracle application services on an application tier that was running the Concurrent Manager. Considering the security concerns, because most Operating Systems allow all machine users to read a program’s command line arguments, the password may be intercepted.


Starting with Release 12.1.3 it is possible to create an applications user (FND User) with the responsibility
Concurrent Manager Operator” and use this user’s username and password to start and stop the Oracle application services.
This can implemented by following below steps

      ·  Creating a new application user, say CONCOPER and assign the ”Concurrent Manager Operator” responsibility to this user

       ·        On the application tier update the following 4 variables in the autoconfig context file

Autoconfig Variable
New Value
s_cp_user
CONCOPER (or the one you created)
s_cp_password_type
AppsUser
s_cp_resp_shortname
FND
s_cp_resp_name
Concurrent Manager Operator

       ·          Run autoconfig on the application tier(s)

Following this change the application tier services can be started and stopped by calling adstrtal.sh and adstpall.
sh with the -secureapps option and the script will prompt for the Application users username and password
rather than the APPS username and password.

For example
[applmgr@app01]$ adstrtal.sh -secureapps
 Enter the Applications username: CONCOPER
 Enter the Applications password:

thanks,
Edwin.K

Thursday, May 2, 2013

Apache Login Issue

Hi Friends,

Today we had Prod 12.1.3 oracle applications login issue.
This issue was only to few users and other users were working fine,and this bit challenge.
We had forms/apache 6 nodes.

1)check each nodes latest access_log and error_log files

cd $LOG_HOME\ora\10.1.3\Apache\ 

If thier is any indication of failure or error

2)check each nodes application.log and server.log to fine any errors in it.

cd $LOG_HOME\ora\10.1.3\j2ee\oacore\oacore_default_group_xxx\

As said i was checking step 1.
I found one the nodes error_log file had this error

mod_oc4j: request to OC4J mt-036.us.abc.com:21506 failed: Connect failed

This indicates the issue is on this specific mt-036 node.

Bounced Apache and Oacore services on this nodes,this fixed issue

thanks,
Edwin.K






Wednesday, January 16, 2013

oracle Alerts erroring with Signal 11 R12 oracle applications

Oracle Alerts started failing in Production environment(R12 12.1.3 applications) one fine day with error Signal 11.
I am sure Signal 11 error no one likes to see and its points to no where.
 To fix the issue was big nightmare and since it was production the pressure was too much.
This issue started after normal patching activity which went through without issue.

Thier is one metalink hit "What To Consider When Receiving A Signal 11 In Oracle Alerts? (Doc ID 1354644.1)"
but this gives 3 patches to applied

Patch 13728376 - Alert fails with signal 11 error after password case sensitivity fix.
Patch 9908378 - RELEASE 12 BUG FOR SIGNAL 11 WITH ALPPWF
Patch 8728439 - R12.ALR.B - CC AND BCC RECIPIENTS SHOWN IN TO FIELD IN ALERT MAILS


We clone Prod to test instance,reproduced the error and applied patch  "
Patch 13728376 - Alert fails with signal 11 error after password case sensitivity fix." and issue got fixed in test environment.

we move the patch to PROD and things started working fine.

thanks,
Edwin.K