I’ve been having a problem with a 3rd party application. Whenever I run DBMS_STATS on the application schema my database will peg the CPU and will run for days without completion until I cancel it. The part that took me forever to figure out is the SQL statement that is consuming all of the CPU shows two tables in the schema that are joined together. I was puzzled about this for a long time. How does Oracle know that these two tables are joined together? The application vendor has not provided any foreign key constraints (bad idea obviously but its a totally different discussion) so why is it that when I compute stats on TABLE1, the CPU gets pegged on a query joining TABLE1 with TABLE2? How does Oracle know this?
It took me a while to figure this out, but I was finally able to answer that part of the problem. It turns out that Oracle includes in the Oracle Call Interface (OCI) a routine named ODCIStatsCollect. This routine is called by DBMS_STATS when gathering statistics on a table. The application vendor can put in their own code here. So after DBMS_STATS has gathered stats on the table, it then runs the vendor’s stats collection routine. It is the vendor’s code that is performing this terrible, CPU-pegging SQL statement. I never knew that it was possible for an application developer to piggy-back on to the DBMS_STATS call to execute their own stats collection code.
Types of Infertility Drugs Clomid is one of many infertility drugs vardenafil vs viagra and it is normally prescribed for many infertile couples. And using the pharmacy drive-thru lane is certainly no guarantee of anonymity either. levitra samples free The marketing practice explained best viagra prices recommended for you above should give marketers some background on prohibited marketing activities. Know About Buying Medicine Online, Then Shop As the world economy groans under the stress of banking-industry get cialis without prescriptions greed, many consumers dependent on expensive medications to maintain health are looking for cheaper sources. So I found the answer to the first question…how does Oracle know to join these two tables when running DBMS_STATS. The answer is that the application vendor has told them through ODCIStatsCollect.
But I am still left with the biggest question of all…how can I stop this stats collection from pegging the CPU and finished in a timely fashion?
