The Oracle AWR lists some Identity Manager application-specific SQL statements that are run on the object12_5 table and connection pool issues.Transactions are not processing
Article ID: 137624
Updated On:
Products
Issue/Introduction
The AWR points to some Identity Manager application-specific SQL statements that are run on the object12_5 table:
Action
Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
"<exampleID>".
Related Object
SQL statement with SQL_ID <exampleID>.
update object12_5 set object=:1 where objectid=:2 and objecttype=:3
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"<exampleID2>".
Related Object
SQL statement with SQL_ID <exampleID2>.
insert into object12_5 (objectid,objecttype,object) values (:1,:2,:3)
Action
Investigate application logic for possible reduction of connect and
disconnect calls. For example, you might use a connection pool scheme in
the middle tier.
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Action
Increase the size of the SGA by setting the parameter "sga_target" to
12672 M.
Action
Investigate the cause for high "SQL*Net more data to client" waits.
Refer to Oracle's "Database Reference" for the description of this wait
event.
Cause
Oracle AWR reports potential performance issues.
Resolution
Explanations and recommendations are below:
Action
Run SQL Tuning Advisor on the UPDATE statement with SQL_ID
"<exampleID>".
Related Object
SQL statement with SQL_ID <exampleID>.
update object12_5 set object=:1 where objectid=:2 and objecttype=:3
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"<exampleID2>".
Related Object
SQL statement with SQL_ID <exampleID2>.
insert into object12_5 (objectid,objecttype,object) values (:1,:2,:3)
The above SQL is part of our Identity Manager task persistence code that tracks the states of tasks and events from beginning to completion. We fully expect to see extremely large amounts of activity as pointed out by the AWR report. This SQL activity is normal and is already optimized.
------------------
The following entry refers to connection pooling, which is already configured on your application server. The WAS logs you previously provided did not show any issues with connection pooling. If you have concerns about connection pooling make sure each IDM datasource has at least 200 max pool connections. The max sessions can be increased as needed.
Action
Investigate application logic for possible reduction of connect and
disconnect calls. For example, you might use a connection pool scheme in
the middle tier.
--------
The following three items reported in the AWR should be examined by your DBA to determine whether any additional DB tuning is required.
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Action
Increase the size of the SGA by setting the parameter "sga_target" to
12672 M.
Action
Investigate the cause for high "SQL*Net more data to client" waits.
Refer to Oracle's "Database Reference" for the description of this wait
event.
Feedback
