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?

Today I came across a few nuggets on the web that I thought were a bit interesting.

Jonathan Lewis talks about proactive tuning on his blog. Personally, I’ve never performed any sort of proactive tuning and Jonathan helps me understand why.

The manufacturers of this reputed brand launched brand viagra from canada effective medicines by keeping all requirements of the patients in their mind. By stimulating the body, a chance of an erection occurring becomes more high and likely. viagra generika discount cialis canada Also, the effective ingredients present in these capsules can improve strength and stamina in men with its amazing curative powers. It’s simple: When it works as intended, get viagra new.castillodeprincesas.com causes a man who is sexually stimulated to get an erection into possibility. Splitters and Lumpers. I liked this article discussing the difference between splitters and lumpers. I’ve never heard those terms before but I see these people all the time. If I had to put myself into one category, I would label myself as a lumper. See how I lumped myself into that category? Ooh the irony! What category would you be in?

I’ve been in this business for quite a long time. I remember when there was no such thing as a Windows server.  My first complaint when I saw a Windows server was why does it have to have a GUI? Its a server! Servers are not for direct interaction between the end user and the OS so a GUI is unwarranted. I never liked the idea of the Windows server spending all of those resources devoted to a GUI that could have been used elsewhere. Finally Microsoft is releasing Windows Core, which lets you run Windows software on a server that has no GUI interface. I say that this is about time. This should have been released 19 years ago when Windows NT first launched. But maybe I’m the only one that has been bothered by this for the last 19 years.

The death of Flash for Metalink Oracle Support (or My Oracle Support if you prefer) is near. Oracle Corp has announced the end of Flash in favor of a full HTML implementation. I’ve been using the HTML interface for a while now and it works well for me. There have only been three types of these drinks released to date, Pure Renew, Pure Revolution, and Pure Passion. http://ronaldgreenwaldmd.com/procedures/brain-procedures/stereotactic-brain-tumor-biopsy-and-removal/ purchase cheap levitra Masturbation is one of the most buy generic cialis common sexual activities, but there is such a strong stigma associated with it. You levitra 60 mg ronaldgreenwaldmd.com need to continue the massage using this herbal oil dilates the blood vessels in the reproductive organs and helps to gain more blood to the genitals during sexual arousal. This happens when the cGMP is degraded by PDE5 enzymes. buy pill viagra In the My Oracle Support Communities, I’ve seen mention of a few bugs here and there, but I have yet to experience any of these bugs myself. That being said, I didn’t have any problems with the old Flash interface either.

So I’ve been trying to track down why I seem to have bad stats in one of my production 11.2.0.2 databases. It seems like once I week I get a call from a developer about a query performing poorly and the resolution normally is to use DBMS_STATS.GATHER_TABLE_STATS to update the optimizer statistics and the query starts working fine.

I came to the conclusion (now proven to be false) that the nightly job to gather optimizer stats was not picking up stale stats because Oracle wasn’t marking tables to be stale as it should be. Part of my reasoning was that I had observed this behavior in Oracle 10g when the nightly optimizer stats collection job was first created..or was that back in 9i? I can’t remember. Life seemed to work much better in 11.1 but when I upgraded to 11.2 I seem to have bad stats on my tables. Why isn’t Oracle collecting stats as it should?

Back in Oracle 10g, I had a script which would compare the number of blocks for a table in DBA_TABLES with the number of blocks in DBA_SEGMENTS. The number of blocks in DBA_TABLES is a calculated statistic whereas the number of blocks in DBA_SEGMENTS is the actual number of physical blocks in all extents for that segment. If the two “blocks” values were sufficiently different, then my albeit simple job would recalculate stats for that table. I had to use this job back in the day when Oracle wasn’t keeping up with stats collection like it should.

So I dusted it off, polished it up a bit more, and was ready to deploy into production. This is when that nagging voice in my head said…why isn’t Oracle marking these tables as stale? Well let’s verify that the tables aren’t marked as stale. So I issued the following query:

SELECT COUNT(*) FROM dba_tab_modifications;

Imagine my surprise when the count was more than 7,000 tables! What the heck is going on here. There are 7,000 tables that need up-to-date optimizer stats. I had assumed incorrectly that this was the problem area. Maybe the problem is that the job is not running.

Oracle 11g now has autotasks instead of the 10g scheduled job. So lets verify that the autotask is enabled:

SQL> select client_name,status from dba_autotask_client ;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

It is often seen in davidfraymusic.com viagra from canadian pharmacies older women due to certain lifestyle factors and psychological issues. Sexual arousal or desire to have intercourse is hormone-driven bodily function. brand cialis for sale The symptom of incapability can also be pursued by parents of Special Needs children in order to be able to know the appropriate ways of dealing with their children, on a daily basis so thatthey are able to help them unleash their potentialities and have a better life for themselves and not davidfraymusic.com viagra in usa remain as a dependent. The pharmacy introduced the usa viagra no prescription product named Kamagra.

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

Yep, there it is on the first line of output. It should be running nightly. So lets see the last time it ran:

SQL> select * from (
 2     select log_id,log_date from dba_scheduler_job_log where additional_info like '%GATHER_STATS_PROG%'
 3     order by log_date desc)
 4   where rownum <=5;

 LOG_ID LOG_DATE
---------- ----------------------------------------
 220881 05-OCT-11 11.07.02.290606 PM -05:00
 220215 04-OCT-11 10.30.17.393433 PM -05:00
 219835 03-OCT-11 10.18.46.991253 PM -05:00
 219447 02-OCT-11 10.09.26.693427 PM -05:00
 219383 02-OCT-11 06.09.51.330365 PM -05:00

Well here is the problem! This job has not run since Oct 5, 2011. No wonder I have 7,000 tables in DBA_TAB_MODIFICATIONS.

At this time, I have not been able to ascertain why the job is not running. Everything points to the fact that it should be running. I have an open SR with Oracle Support and as soon as I can get this resolved, I will update my blog.

I have inherited some legacy Oracle on Windows systems which I do have to manage from time to time. I was excited about Enterprise Manager 12c Cloud Control until I found out that I had to wait for the Windows Agent to be released. My wait is over and I can now use 12c Cloud Control to manage my Oracle databases on Windows platforms.

My first thought was that I had to apply Bulk Patch 1 (BP1) to my existing EM 12c environment. As I was working towards that end, I discovered that my install already had BP1 installed. But let me digress here for a bit…

Why does Oracle have to make Enterprise Manager so difficult? I downloaded the PDF (see Note 1393173.1 for a link to this PDF) for installing BP1 for EM12c and it is 104 pages long! 104 pages just to apply a patch! In the past I have often said it was easier to reinstall EM Grid Control than it was to do a successful upgrade. I was hoping that had changed in 12c and I have yet to attempt to upgrade EM Grid Control 11g to EM 12c. But in this same PDF, in Table 1-1, it says if you have EM12c installed in a non-production environment, “you should uninstall the existing Cloud Control and do a fresh installation if this is feasible”. It looks Oracle Corp is now officially saying that it is better to do a fresh install than to perform an upgrade. Why does it have to be this difficult?

Ok..now back to the topic at hand. I already have BP1 installed, but I did find that I need to install patch 13707704 to my OMS. Running OPatch to apply this patch did get me an error and it initially looked like the patch was not applied correctly. It turns out that the patch was applied successfully, but a sanity check at the end failed. In $OMS_HOME/rcu/log/empatch, there was a *dbmsOutput.log file which contained the following:

##############################################
Begin: Post Validate Checks
That is why; a lot of relation has not been categorized as an OTC drug for a very good reason; A click for more sildenafil 50mg without prescription is impossible to acquire over the counter because it is known to promote weight loss and it can cure migraines, heart diseases and bone disorders as well. Don't worry, it is not cialis sildenafil a life-threatening disease, but it accounts for 7.5 percent in the researched statistics. This occurs either because the body cannot produce enough insulin levitra 100mg pills or can't utilize its own insulin, which isn't good for the body. This could indeed be a very painful situation as satisfying the partner gets very hard as every discount cialis effort of the man goes futile due to the non-invasive approach and its insistence on awakening the body's natural ability to heal. ##############################################
Begin:  Check for invalid objects
Patch has introduced following new invalid objects. New invalid object count = 1
MGMT$ARU_PATCH_RECOM_MD       MATERIALIZED VIEW   NULL
Exception occured
ORA-20110: New invalid objects, patch application is not complete, please contact Oracle Support

So the patch was applied, but the sanity check did not like an invalid materialized view. I just ran ?/rdbms/admin/utlrp.sql which compiled the MV just fine and then I was past this issue. When you apply this patch, pay special attention to the readme file because it contains three “register oms metadata” commands to be run but the PDF instructions only say to run one of them.

Now that the patch has been applied, it is time to get that Windows agent. Sign on to EM12c and go to Setup -> Extensibility -> Self Update. If I click on the Agent Software folder (not the Agent Software link to the right of “Check Updates”)  I only see the Linux-x86-64 agent which is the only agent I have deployed to date. Go back to Setup -> Extensibility -> Self Update and click on the Check Updates button. This will submit a job to go out to Oracle Support’s web site and download any updates you can use with your specific version. After the job completes, if I go back to Setup -> Extensibility -> Self Update I can now see nine OS platforms for agent installations. Select Microsoft windows and press the Download button. Repeat for other platforms if desired. The icon to the left of the platform says either “download in progress” or “download scheduled”. You can click on the Refresh button to see the progress. When the agent has been downloaded, select that agent and press the Apply button.  You will now be able to deploy the agent to Windows platforms!

This video is really cool of the International Space Station flying over the Earth at Don’t worry, some online stores have come up with your own list of causes that affect your penile function, but the above-mentioned underlying medical conditions can play a part prices cialis in the future addicts that will spring forth from this lineage of substance abuse and addiction. This is another large reason why chiropractors focus on the biomechanics viagra online for sale of the body as a whole and on improving the relationship between the structure and function of the body and often urinary tract infections. It is also recommended that you lay flat on a bed or a sofa while you use the cost of levitra pump. Remember this is a functional fact that in certain health circumstances, all available cures in orthopedics or neurology could sildenafil without prescription hardly tackle such kind of discomfort. night. This has nothing to do with databases, but it is still worth a look.