Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Highlighted
mike_dieb
Acclaimed Contributor

We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

When searching a request, the query is timing out.  I retrieved the SQL from OEM and modified the hint to rule from first_rows_n, which decreases the search time by 15 fold, however, I am unable to locate this script on the file system for PPM.  excuse my ignorance, I am a DBA, not PPM admin.  where are the portal query scripts located?  Below is the script I pulled fro OEM:SELECT
        /*+ FIRST_ROWS(200) */
        rv.STATUS_NAME H_STATUS_ID             ,
        rv.REQUEST_TYPE_NAME H_REQUEST_TYPE_ID ,
        rv.REQUEST_ID H_REQUEST_ID             ,
        rv.DESCRIPTION H_DESCRIPTION
FROM    kcrt_requests_v rv
WHERE
        (
                1=1
                AND
                (
                        rv.batch_number     = 1
                        OR rv.batch_number IS NULL
                )
                AND rv.REQUEST_TYPE_ID IN (:1)
                AND rv.REQUEST_TYPE_ID IN (:2)
                AND
                (
                        rv.STATUS_CODE NOT LIKE 'CLOSED%'
                        AND rv.STATUS_CODE NOT LIKE 'CANCEL%'
                )
                AND rv.STATUS_CODE = 'IN_PROGRESS'
                AND EXISTS
                (
                        SELECT
                                /*+ no_push_subq NO_UNNEST */
                                parent_id
                        FROM    knta_eligibility_check_v
                        WHERE   parent_id                     = rv.request_id
                               AND user_id                   = 30014
                                AND instance_source_type_code = 'IR'
                )
        )
        AND ROWNUM <= 200
ORDER BY rv.REQUEST_ID DESC

 

where is the source for this and other portal queries located?

9 REPLIES
jsalass
Frequent Visitor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Hi MIke,

I have seen this problem before. The script is located in a jsp file in the PPM file system. The script is formed after several functions in several jsp files collect the information and send the former trigger to be executed into the database.

I would recommend you to try these steps in order:

1. These indexes definetly improve performance:

CREATE INDEX kwfl_workflow_steps_nls_u2 ON kwfl_workflow_steps_nls (workflow_step_id, enabled_flag) COMPUTE STATISTICS;

CREATE INDEX kwfl_workflow_steps_nls_n3 ON kwfl_workflow_steps_nls (workflow_id, enabled_flag, workflow_step_id) COMPUTE STATISTICS;

CREATE UNIQUE INDEX kwfl_workflows_nls_u3 ON kwfl_workflows_nls (workflow_id, sub_workflow_flag) COMPUTE STATISTICS;

CREATE INDEX kwfl_resolved_sec_tokens_n6 ON kwfl_resolved_sec_tokens (source_set_id, entity_id, user_id) COMPUTE STATISTICS;

CREATE INDEX kcrt_request_type_security_n6 ON kcrt_request_type_security (request_type_id, security_group_id) COMPUTE STATISTICS;

2. Try rebuilding database statistics. I saw a similar issue. After rebuilding the statistics, the issue has been solved.

3. Please set this settings in server.conf (PPM side) as following:
com.kintana.core.server.PORTLET_MAX_ROWS_RETURNED=200
com.kintana.core.server.PORTLET_EXEC_TIMEOUT=240

Then, I attached a hotfix for 9.11 or 9.12
1. Unzip the zip file(42693.zip attached). There is a folder named "com".
2. You need to copy the folder "com" to /server/<your server>/deploy/itg.war/WEB-INF/classes/
3. Restart PPM instance.
4.If they you are using a PPM cluster, please deploy it on every node.

 I would recommend you to open a ticket with PPM support and based on your PPM version (if it is not 9.11 or 9.12) they will be able to provide a fix for that specific version.

Hope this helps!

Regards,

Jonathan

mike_dieb
Acclaimed Contributor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Like I said, I am the DBA, I have forwarded your suggestion about the ticket to the admin.  we are on 9.14, would the .class files in the zip you attached be applicable to 9.14 as well?  I really appreciate your help, we have managers and directors screaming bloody murder over the performance of request searches since we upgraded in December.  do the class files supply the hints for the derived sql scripts?  as I said, changing the hint resulted in decreasing the query time from 4 minutes (which caused a time out) to 1 second when run from a command line, so along with the index creation, we would really like to change the hints.

 

again, really appreciate your help

jsalass
Frequent Visitor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Yes Mike, the .class file will replace the current script by one using a more accurate hint, this have been tested before and have improve the request search a lot!!!

I'm not sure if the file can be used in 9.14 that's why I recommended to open a ticket.

However, the PPM admin can backup any affected file and then apply the hotfix to check if the problem is resolved in 9.14. If the problem persists so they can go ahead and remove the fix or use the backup to restore the files and you will have your PPM instance as in the beginning.

If you have any other concern please let me know.

Regards,
Jonathan

jsalass
Frequent Visitor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

By the way, the fix provided just modify some class files in the PPM filesystem and won't change anything in the database side. That's why changes can be reverted back without problems and quickly.
mike_dieb
Acclaimed Contributor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

All of the indexes you suggested already exist.  I will rebuild all indexes and see if that helps, but I really need to know how to change the hints.

 

Thanks again for your help.

mike_dieb
Acclaimed Contributor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

I have forwarded all of your information to the admin, who is located in India, where there is a holiday today, and will follow up with him tomorrow about the ticket,.

 

 

Thanks again, Jonathan

jsalass
Frequent Visitor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Sure Mike,

If you have any other concern please let me know :)

Regards,
Jonathan
hyllplan
Frequent Visitor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

Ppm doesnt like 11.2.0.1, it doesnt perform well, portlets takes forever to load etc, performance issues

 

http://h30499.www3.hp.com/t5/Project-and-Portfolio-Management/Slowness-in-loading-portlet-with-Oracle11g/m-p/5393717/highlight/true#M14624

 

Utkarsh_Mishra
Honored Contributor

Re: We upgraded Oracle Db 10.2.0.4 to 11.2.0.1 ; experiencing performance degrade in portal

This is a bug/defect, for this HP has given me a pacth and performance issue is resolved.

 

The pacth uses the cost based optimization on portlet/request search and it drastically improves the performance.

 

In addition to this Oracle is planning to un-support cost based optimization in future release, so you can ask HP to provide seprate pacth which does not uses cost based optimization, this patch is also working fine for me.

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
//Add this to "OnDomLoad" event