Oracle Apps sales quote performance

Both our Sales configurator and sales quote are running very slow.  Too slow.  I need troubleshooting help.   New apps dba.  How can I find the tables, objects this is accessing and using.  We have hot block issues in our db and no ability to reorg at this time.  

What is best approach to trouble shoot?  Where can I look at how this process works?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
I wish you well.  I'm not a fan of Oracle Apps.  I've worked with Oracle databases and Oracle development tools for 20 years (and I consider them to be good products) but our management decided a couple years ago that we needed Oracle Apps.  We've been running it now for eight months. (Or should I say "walking" it or "crawling" it, since "running" seems too kind a term to describe its performance.)  Oracle Apps is designed to make money for the Oracle Corporation and it is designed to be flexible.  It is not designed to be efficient or user-friendly (nor DBA-friendly either, since it doesn't use referential integrity).

Oracle Enterprise Manager (OEM) can easily identify the SQL statements that are taking the most resources.  Do you have OEM set up and configured for this database?
mike1956Author Commented:
yes have OEM.   I agee with all statements you made.  I am a core dba by trade.   My big prob is just finding the concurrent mgr request so I can get a SID to find sql

If I could get a sid I could deal with it
Mark GeerlingsDatabase AdministratorCommented:
In OEM, from the Home page, go to the "Targets" tab, then "Databases" and click on the database you want.  From there, choose "Performance" (should be near the top of the screen).  After that opens, look for a link on the left side of the page toward the bottom named: "Top Activity" and click that.  That should open a page with a graph at the top and a list of individual SQL statements on the left, and a list of session IDs on the right.  The worst SQL statements and the session(s) that ran them should be visible.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mike1956Author Commented:
How can I tie that to a module?  V$session?  sometimes there sometimes not.
Mark GeerlingsDatabase AdministratorCommented:
"How can I tie that to a module [or program]?"  Excellent question!  OEM does not do that for us.  If the SQL statement comes from a PL\SQL function, procedure or package, you can do a select from all_dependencies to see which one(s) it could be from.  Here is a query I use to do that:

select substr(owner,1,12) "Owner", substr(name,1,50) "Name", type,
referenced_type "Referenced obj."
from all_dependencies
where referenced_name = upper('&referenced_object');

Of course, for common tables this could give you multiple packages.  And even if it only gives you one package, that still does not indentify which screen in the application might call the package.
mike1956Author Commented:
How can I find the version of this file?  

Mark GeerlingsDatabase AdministratorCommented:
One possibility: if you know which directory the file is in, navigate there, then try:
strings ozfvqpqb.pls
mike1956Author Commented:
thanks.  next question.  How do I figure out if "Buying Groups in Trade Management" are active?
Mark GeerlingsDatabase AdministratorCommented:
That sounds like a functional question to me, and I'm not a functional expert on Oracle Apps.
mike1956Author Commented:
I am finding I am not an expert on anything in APPS.  Opened an SR.   Be a year before they look at it.  Issue I think is patching has not been done.  A bunch of customization has been plugged in to the DB.  Cursor management on custom is horrible.   It is a mess.  

I will have a couple more questions today.  Thank you.

mike1956Author Commented:
How do I find the "setting of cz.activemodel"  ?
Mark GeerlingsDatabase AdministratorCommented:
"I am not an expert on anything in APPS".  That's pretty much true for me too.  And yes, waiting for a response to an SR on Oracle Apps issues is an exercise in patience. "A bunch of customization has been plugged in...".  Ouch!  That sounds about like us (and another company in our area that also went live on Oracle Apps R12 last July.)  The custom code that we have (from a major consulting firm) is about the same quality as the custom code at the other company in our area that went live on Oracle Apps last year.  They used a different, major consulting company, but got equally terrible custom code.  In both cases, the custom code seemed to work as intended with just a handful of test records in most tables.  But the custom code was *NOT* designed to be efficient with typical production data volumes, nor was there any planning for purging, archiving, compressing, etc. of the custom data to try to keep the custom code working somewhat close to acceptably as data accumulates in the Apps database.

The Oracle salespeople for the Oracle Apps product are very good at what they do.  They know how to make their product sound good to executives who will rarely use it.

Sorry, I don't recognize that particular setting: "cz.activemodel", so I don't know if it is a profile setting, an environment variable, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike1956Author Commented:
How do you check when was CZ purge last run?
Mark GeerlingsDatabase AdministratorCommented:
Sorry, I don't know.  Our Apps DBA is taking a vacation day today, so he isn't available for me to check with.
mike1956Author Commented:
ok thanks
mike1956Author Commented:
What would make the screen transitions so painfully slow on the front end of our configurator?  I have tried everything.  15 to 30 seconds for transitions through the navigation, not like that on other apps forms.
Mark GeerlingsDatabase AdministratorCommented:
We don't use the Configurator in Oracle Apps so I have no experience with it.  But I'm not surprised if you say it is slow, I understand that it is complex.
mike1956Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.