DBA Blogs
Analysis of Oracle password expiry
Oracle password expiry is managed by the profile limit PASSWORD_LIFE_TIME, but based on the user$ table attributes ASTATUS and EXPTIME.
SQL> connect a/a
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN 08 MAY 2010 10:22:52
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 09 NOV 2009 10:22:52
# So ASTATUS = O and EXPTIME are not set
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME 1;
Profile altered.
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_GRACE_TIME 7;
Profile altered.
SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11
# So after we login we find the ASTATUS=2 and EXPTIME is set
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
# But we see that after we remove the password lifetime, the account is still expired
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11
# and ASTATUS is still "2" and exptime is still set. So once expiry is set, changing the profile won't remove expiry.
SQL> alter user a identified by a;
User altered.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 20 NOV 2009 09:19:11 27 NOV 2009 09:17:11
# When we change the password the PTIME is updated, and the ASTATUS is reset to O, but EXPTIME is not changed.
# Conclusion - password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, depends on a combination of ASTATUS and EXPTIME, and is reset by a password change. Changing the profile will not change ASTATUS. Changing the password resets ASTATUS, PTIME, but not EXPTIME.
SQL> connect a/a
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN 08 MAY 2010 10:22:52
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 09 NOV 2009 10:22:52
# So ASTATUS = O and EXPTIME are not set
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME 1;
Profile altered.
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_GRACE_TIME 7;
Profile altered.
SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11
# So after we login we find the ASTATUS=2 and EXPTIME is set
SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
SQL> connect a/a
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
# But we see that after we remove the password lifetime, the account is still expired
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A EXPIRED(GRACE) 27 NOV 2009 09:17:11
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 2 09 NOV 2009 10:22:52 27 NOV 2009 09:17:11
# and ASTATUS is still "2" and exptime is still set. So once expiry is set, changing the profile won't remove expiry.
SQL> alter user a identified by a;
User altered.
SQL> select username, account_status, expiry_date from dba_users where username = 'A';
USERNAME ACCOUNT_STATUS EXPIRY_DATE
------------------------------ -------------------------------- --------------------
A OPEN
SQL> select name, astatus, ptime, exptime from sys.user$ where name = 'A';
NAME ASTATUS PTIME EXPTIME
------------------------------ ---------- -------------------- --------------------
A 0 20 NOV 2009 09:19:11 27 NOV 2009 09:17:11
# When we change the password the PTIME is updated, and the ASTATUS is reset to O, but EXPTIME is not changed.
# Conclusion - password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, depends on a combination of ASTATUS and EXPTIME, and is reset by a password change. Changing the profile will not change ASTATUS. Changing the password resets ASTATUS, PTIME, but not EXPTIME.
Categories: DBA Blogs
Upgrading to Fedora 12? You might need more /boot space!
Today, I had a spare Fedora 11 machine sitting next to me, so I thought I’d try the upgrade to the newly-released Fedora 12, aka “Constantine.” Fedora support cycles are rather short compared to Ubuntu, so Fedora 11 will likely be de-supported in 6 to 7 months. Normally I’d wait a little longer into the Fedora 12 cycle for others to find the fun upgrade bugs and have them fixed for me, but I didn’t mind having to re-install from scratch on this machine if I needed to.
Following the Fedora documentation, I decided to use the “preupgrade” tool. Everything was going smoothly until the machine restarted to begin installation of the new packages. I got a message that there wasn’t enough space in my /boot partition. Specifically, the message claimed that there was insufficient disk space in /mnt/sysimage/boot. I found this rather odd and troubling, since I had let the Fedora installer determine the /boot partition size when I originally installed Fedora 11.
Turns out that this is a known problem with the preupgrade tool. A kind soul in #fedora on IRC directed me to the list of common Fedora 12 bugs, in particular the preupgrade free space check. I installed the updated preupgrade package as directed, but again got the error. That’s when I followed the next link for additional tips to free up space in /boot. The first was to remove obsolete kernels, which I had already done. The next was to run tune2fs on /boot filesystem to free up reserved blocks, which aren’t needed for /boot. I strongly suggest you visit the links provided for helpful screenshots and commands to follow.
After making these changes, the upgrade worked and am I’m the proud owner of a Fedora 12 Constantine laptop, with a slightly brighter shade of blue desktop than that crusty old Fedora 11. ;)
Categories: DBA Blogs
Hiring 2 positions in Oracle Thailand
Oracle Functional Financials Lead
This candidate need to be a seasoned Oracle Applications Financials Lead with proven track of record of successful implementations. We (and client) will look for an accounting practitioner background, preferably CPA certified and working experience in multinational environment. In addition, candidate need to have 6+ years exposure in Oracle Financials implementations with 2-3 years in a Lead role. Automotive and/or Manufacturing will be a definite plus.
Sr. Project Manager
Candidate should have an ERP package project management background with proven success with large projects in multinational environments. Preferably including Oracle Financials implementations, team size 15+ with a budget of 2 M US$++, familiar with concepts of Program Management. Candidate must be fluent in English and excellent communication skills. Project Management experience minimum years of experience 10+
For more details contact sam.abraham@oracle.comWritten By: Surachart Opun http://surachartopun.com
This candidate need to be a seasoned Oracle Applications Financials Lead with proven track of record of successful implementations. We (and client) will look for an accounting practitioner background, preferably CPA certified and working experience in multinational environment. In addition, candidate need to have 6+ years exposure in Oracle Financials implementations with 2-3 years in a Lead role. Automotive and/or Manufacturing will be a definite plus.
Sr. Project Manager
Candidate should have an ERP package project management background with proven success with large projects in multinational environments. Preferably including Oracle Financials implementations, team size 15+ with a budget of 2 M US$++, familiar with concepts of Program Management. Candidate must be fluent in English and excellent communication skills. Project Management experience minimum years of experience 10+
For more details contact sam.abraham@oracle.comWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
News! Is it really?
Anyone who read my previous blog will realize I am not a fan of mandatory dashboards.
The main reason is that usually someone else gets to decide what information is good or important for me. Since I almost always work at the fringe of mainstream, usually in support and guidance of mainstream, what is important for me is rarely the same as what is important for mainstream administrators.
What makes me upset with mandatory dashboards is that some else is taking control of my time and my eyeballs. And usually for a reason that is not relevant, or indeed very useful.
Case in point: I logged on to My Oracle Support and was greeted by the Dashboard again. In addition to the non-removable 'offer' to teach me OCM and the duplicated and Mandatory regions of News that have not been updated with anything new in over a week, I now have a new Mandatory region added called 'Site Alerts'.
The new 'Site Alerts' region sports an attention-getting icon and a header saying it will tell me about upcoming outages for MOS. (Which is actually the kind of information I would call 'News'.)
And it has some content.
That content is a separate page that tells me to click on a link to get to another page to get some answers to Frequently Asked Questions about what to do if I had any issues with the migration.
Not, as far as I can tell, any advice about upcoming outages.
So now, when I want to get an answer in a hurry, I have the opportunity to wait for 4 regions to update (or at least bottleneck my download bandwidth) and divert my attention from my objective of solving a problem.
And I suspect that by the time they DO contain some important information, I will have been trained to ignore the content.
Come on guys - if you will not let me remove those regions, at least give me the opportunity to ack and hide the stuff that is is old. So that the important stuff you will tell me will not get buried.
The main reason is that usually someone else gets to decide what information is good or important for me. Since I almost always work at the fringe of mainstream, usually in support and guidance of mainstream, what is important for me is rarely the same as what is important for mainstream administrators.
What makes me upset with mandatory dashboards is that some else is taking control of my time and my eyeballs. And usually for a reason that is not relevant, or indeed very useful.
Case in point: I logged on to My Oracle Support and was greeted by the Dashboard again. In addition to the non-removable 'offer' to teach me OCM and the duplicated and Mandatory regions of News that have not been updated with anything new in over a week, I now have a new Mandatory region added called 'Site Alerts'.
The new 'Site Alerts' region sports an attention-getting icon and a header saying it will tell me about upcoming outages for MOS. (Which is actually the kind of information I would call 'News'.)
And it has some content.
That content is a separate page that tells me to click on a link to get to another page to get some answers to Frequently Asked Questions about what to do if I had any issues with the migration.
Not, as far as I can tell, any advice about upcoming outages.
So now, when I want to get an answer in a hurry, I have the opportunity to wait for 4 regions to update (or at least bottleneck my download bandwidth) and divert my attention from my objective of solving a problem.
And I suspect that by the time they DO contain some important information, I will have been trained to ignore the content.
Come on guys - if you will not let me remove those regions, at least give me the opportunity to ack and hide the stuff that is is old. So that the important stuff you will tell me will not get buried.
Categories: DBA Blogs
Knowledge for a dollar! Got a dollar, brother?
Got some pennies, brother? For just 33 cents—plus a little more for shipping and handling—you can have a used copy of the best book on Oracle internals that has ever been written: Oracle8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams. Yes it doesn’t have the word RAC in it, but what [...]
Categories: DBA Blogs
In search of My Oracle Support - Part 2
Continuing the search of the functionality of the new tool that Oracle Support has provided to the professional community, I think it's time to look at that Portal aspect of MOS. (In particular, I'm going to look at the dashboard.)
According to Wikipedia, "Portals provide a way for enterprises to provide a consistent look and feel with access control and procedures for multiple applications and databases, which otherwise would have been different entities altogether."
I'll try to work with this definition for now ...
Consistent look and feel? - Yup
Access control? - Yup (sort-of)
Procedures for multiple apps and database? - It's so consistent that I can't tell whether there are multiple apps and databases.
So that implies that MOS is a Portal.
It is kind-a cute, especially the way a message window slides across the upper right corner to tell me that 'I actually did what you asked me to do - please tell me to go away'. (For example, "region x removed from page - undo, hide".) Reminds me of Clippy.
But hopefully that portal should help me do my job. So let's look at that 'job' part for a bit.
The way I see it, the portal needs to address several classes of users:
So let's look at the combinations and what simply logging in shows me.
When I log in, I am immediately taken to a Dashboard. That Dashboard gives me News which I can not remove. (In fact, I have 2 occurrences of the News, neither one will disappear.)
By it's very definition, News is very important. It is new. It is supposed to distract me into paying attention. And I have seen the 'News' - a 'Welcome to the new Metalink users' several times a day for the past week. (I am already inured to the news, so truly new News will likely be ignored. Which is bad for News.)
And so I get the News (twice) at whatever the bandwidth requirement is to get the region and to get that very important information that I am likely never to consciously see in the future.
Combinations?
Proactive & Casual Manager
Get OCM working and they can configure their Dashboard to show the targets, the general health check, bugs, and so on. They can even get high priority 'knowledge articles' such as the 'ALERT: Oracle Database 11gR2 Support status and Alerts' (which says it is 'under construction' and tells me that '11g R2 is available')
The dashboard appears to be useful for this role. GREAT!
Reactive Manager
It's Crisis time. As a manager in crisis, my people have been entering SRs. I can get status of my critical Service Requests.
Not bad at all.
We're covering management fairly well in terms of functionality.
Performance will be an issue, as a manager needs to get that information very quickly. That somewhat implies that the manager is expected to keep the portal open pretty much during the day.
Given the attention span that a manager can afford to provide (other than one specifically charged with support), I'm not sure that the page will be open very long and refresh time is critical. Given that the same manager possibly also has BAM and other dashboards open, and I'm thinking we may have a collision of priorities. (It may also explain why managers need the fastest CPUs and newest machines.)
Hopefully those dashboard regions are JSR 268 compliant ...
Proactive & Casual Tech
Pretty much the same as a proactive manager. Possibly not responsible for a lot of systems but at least can see the articles, and peruse the news (again and again). Could have the SR summary available on the dashboard, IF this person actually creates SRs.
Not bad!
Reactive Tech
It's crisis time, and this tech is not an expert. There are possibly 1/2 dozen windows open, each with a 'wait for me to get all sorts of information across the net' timeout. And the boss is coming down the hall, looking for answers. Remember that OCM is probably running and this tech has already had the opportunity to review recommended patches. Unless the 'SR summary' region is available to monitor other SRs, calling for help (by opening a SR) is still several clicks away.
We need to switch to the Knowledge Tab or the SR tab before anything can be done. Ouch!
So, not quite so good for the regular tech. Again, the biggie here is 'how much time will this person have the portal open'? Most admin techs I know are busy researching, scripting, glancing at health checks, checking mail, and so on - outside of Support. Can that person afford to have a window dedicated to a support portal?
And this is assuming the technical person is in the admin area. One whole class of potential users is missing and that is the class of developers. Not quite sure what is available here at all to the developer.
Expert
So let's look at the last of my categories - the 'expert'.
This is a relatively small group in count, but remember that the expert is hired specifically for the ability to get things done fast and accurately. This is the person everyone else turns to as a walking/talking library. This is the person who is called upon when the proverbial fan gets dirty.
This person is the front line of support. If this person is worried, management and the others are probably frightened.
Proactive Expert
The proactive expert may want the dashboard for the occasional glance. (But probably already has other tools up and running, such as Grid Control, HP OpenView, or the like AND personalized scripts.) Total time spent on the dashboard in a week, probably 1 hour, 45 minutes of which is in configuring it.
Over time, the expert may learn to be comfortable with OCM and the dashboard for health checks. I'm just concerned about the overhead of the dashboard page, time to access it if it;s not open, and the duplication of effort.
Still, not a bad possibility.
Casual Expert
It is my belief that one of the things that creates experts is the drive to see new situations, learn and research.
If that is true, the expert, in casual mode, will be wanting to research in the knowledge base. I honestly do not see anything at all in the dashboard that will be useful at this time in the quest to learn more.
So for the expert in casual mode, the 'dashboard' is a wasted interception. (Unless he wants to see that old News again.)
Reactive Expert
And in reactive mode the expert is driven to research. The key here is getting to the information source as quickly as possible. That, as far as I can tell, is safely tucked away on the knowledge tab, a click plus 5 seconds of screen refresh away from login.
Unless the expert is expected to have the portal open at all times. Perhaps in conjunction with the company portal. I wonder if that would be a good business case justification for dual monitor?
Conclusion
So far I can see the dashboard has it place. One place appears to be on the manager's desktop, open at all times. There are definitely some interesting aspects. (Although they appear to compete with Grid Control. Hmmm ...)
For me ... I'd prefer to have a few configuration tweaks: allow me to clean the dashboard totally (no region displayed at all), especially the old News and overkill alerts; let me select the page on which I land at opening - in my case, the Knowledge page.
I'm sure that when I grow up and have a few more systems to manage, or when I'm in a customer site that allows me to monitor the health of their systems, I'll find the various capabilities of the dashboard very useful.
Since I learned the trick of how to use, it the ideal Portal landing page has been the Oracle Tech Net page. Fast!
Definitely a first glance at the dashboard. As time goes on I'll need to review it again. But the next one - the MOS Knowledge tab ...
Totally aside, I have no idea why getting to the community pages requires 3 steps in Internet Explorer 7 & 8:
1) click on the MOS 'Community tab', which presents the 'welcome page' with a button;
2) click on the 'Go to community' button, after reading the 'welcome to the community' verbiage for the nth time;
3) click on the 'MOS Community' IE tab that is opened in response to the button.
(And then I click on the Discussions tab because the community opens on a totally static (and traffic heavy) welcome page which I've now read n-3 times.)
I have to admit, all this 'welcome' is very ... bandwidth intensive?
According to Wikipedia, "Portals provide a way for enterprises to provide a consistent look and feel with access control and procedures for multiple applications and databases, which otherwise would have been different entities altogether."
I'll try to work with this definition for now ...
Consistent look and feel? - Yup
Access control? - Yup (sort-of)
Procedures for multiple apps and database? - It's so consistent that I can't tell whether there are multiple apps and databases.
So that implies that MOS is a Portal.
It is kind-a cute, especially the way a message window slides across the upper right corner to tell me that 'I actually did what you asked me to do - please tell me to go away'. (For example, "region x removed from page - undo, hide".) Reminds me of Clippy.
But hopefully that portal should help me do my job. So let's look at that 'job' part for a bit.
The way I see it, the portal needs to address several classes of users:
- manager and administrator (the ones who pay the bill)
- casual tech (the majority)
- expert in a focus area (the bread and butter folk)
- proactive
- casual (search for information)
- reactive/crisis
So let's look at the combinations and what simply logging in shows me.
When I log in, I am immediately taken to a Dashboard. That Dashboard gives me News which I can not remove. (In fact, I have 2 occurrences of the News, neither one will disappear.)
By it's very definition, News is very important. It is new. It is supposed to distract me into paying attention. And I have seen the 'News' - a 'Welcome to the new Metalink users' several times a day for the past week. (I am already inured to the news, so truly new News will likely be ignored. Which is bad for News.)
And so I get the News (twice) at whatever the bandwidth requirement is to get the region and to get that very important information that I am likely never to consciously see in the future.
Combinations?
Proactive & Casual Manager
Get OCM working and they can configure their Dashboard to show the targets, the general health check, bugs, and so on. They can even get high priority 'knowledge articles' such as the 'ALERT: Oracle Database 11gR2 Support status and Alerts' (which says it is 'under construction' and tells me that '11g R2 is available')
The dashboard appears to be useful for this role. GREAT!
Reactive Manager
It's Crisis time. As a manager in crisis, my people have been entering SRs. I can get status of my critical Service Requests.
Not bad at all.
We're covering management fairly well in terms of functionality.
Performance will be an issue, as a manager needs to get that information very quickly. That somewhat implies that the manager is expected to keep the portal open pretty much during the day.
Given the attention span that a manager can afford to provide (other than one specifically charged with support), I'm not sure that the page will be open very long and refresh time is critical. Given that the same manager possibly also has BAM and other dashboards open, and I'm thinking we may have a collision of priorities. (It may also explain why managers need the fastest CPUs and newest machines.)
Hopefully those dashboard regions are JSR 268 compliant ...
Proactive & Casual Tech
Pretty much the same as a proactive manager. Possibly not responsible for a lot of systems but at least can see the articles, and peruse the news (again and again). Could have the SR summary available on the dashboard, IF this person actually creates SRs.
Not bad!
Reactive Tech
It's crisis time, and this tech is not an expert. There are possibly 1/2 dozen windows open, each with a 'wait for me to get all sorts of information across the net' timeout. And the boss is coming down the hall, looking for answers. Remember that OCM is probably running and this tech has already had the opportunity to review recommended patches. Unless the 'SR summary' region is available to monitor other SRs, calling for help (by opening a SR) is still several clicks away.
We need to switch to the Knowledge Tab or the SR tab before anything can be done. Ouch!
So, not quite so good for the regular tech. Again, the biggie here is 'how much time will this person have the portal open'? Most admin techs I know are busy researching, scripting, glancing at health checks, checking mail, and so on - outside of Support. Can that person afford to have a window dedicated to a support portal?
And this is assuming the technical person is in the admin area. One whole class of potential users is missing and that is the class of developers. Not quite sure what is available here at all to the developer.
Expert
So let's look at the last of my categories - the 'expert'.
This is a relatively small group in count, but remember that the expert is hired specifically for the ability to get things done fast and accurately. This is the person everyone else turns to as a walking/talking library. This is the person who is called upon when the proverbial fan gets dirty.
This person is the front line of support. If this person is worried, management and the others are probably frightened.
Proactive Expert
The proactive expert may want the dashboard for the occasional glance. (But probably already has other tools up and running, such as Grid Control, HP OpenView, or the like AND personalized scripts.) Total time spent on the dashboard in a week, probably 1 hour, 45 minutes of which is in configuring it.
Over time, the expert may learn to be comfortable with OCM and the dashboard for health checks. I'm just concerned about the overhead of the dashboard page, time to access it if it;s not open, and the duplication of effort.
Still, not a bad possibility.
Casual Expert
It is my belief that one of the things that creates experts is the drive to see new situations, learn and research.
If that is true, the expert, in casual mode, will be wanting to research in the knowledge base. I honestly do not see anything at all in the dashboard that will be useful at this time in the quest to learn more.
So for the expert in casual mode, the 'dashboard' is a wasted interception. (Unless he wants to see that old News again.)
Reactive Expert
And in reactive mode the expert is driven to research. The key here is getting to the information source as quickly as possible. That, as far as I can tell, is safely tucked away on the knowledge tab, a click plus 5 seconds of screen refresh away from login.
Unless the expert is expected to have the portal open at all times. Perhaps in conjunction with the company portal. I wonder if that would be a good business case justification for dual monitor?
Conclusion
So far I can see the dashboard has it place. One place appears to be on the manager's desktop, open at all times. There are definitely some interesting aspects. (Although they appear to compete with Grid Control. Hmmm ...)
For me ... I'd prefer to have a few configuration tweaks: allow me to clean the dashboard totally (no region displayed at all), especially the old News and overkill alerts; let me select the page on which I land at opening - in my case, the Knowledge page.
I'm sure that when I grow up and have a few more systems to manage, or when I'm in a customer site that allows me to monitor the health of their systems, I'll find the various capabilities of the dashboard very useful.
Since I learned the trick of how to use, it the ideal Portal landing page has been the Oracle Tech Net page. Fast!
Definitely a first glance at the dashboard. As time goes on I'll need to review it again. But the next one - the MOS Knowledge tab ...
Totally aside, I have no idea why getting to the community pages requires 3 steps in Internet Explorer 7 & 8:
1) click on the MOS 'Community tab', which presents the 'welcome page' with a button;
2) click on the 'Go to community' button, after reading the 'welcome to the community' verbiage for the nth time;
3) click on the 'MOS Community' IE tab that is opened in response to the button.
(And then I click on the Discussions tab because the community opens on a totally static (and traffic heavy) welcome page which I've now read n-3 times.)
I have to admit, all this 'welcome' is very ... bandwidth intensive?
Categories: DBA Blogs
Single Sign On or Same Sign On?
Just thinking aloud ... one of the things Oracle has introduced is 'userid consistency' across their applications. This is called 'SSO'.
I thought that SSO stood for 'Single Sign On', and the functionality is 'sign on once and use all applications'.
I am finding it actually means 'Same Sign On' and the functionality is 'log in to each of the the different applications one at time, using the same userid & password'. However, signing out does efficiently log me off all applications.
Still trying to get my head around the reason, and the benefit, of this. I'm sure it'll come to me.
I thought that SSO stood for 'Single Sign On', and the functionality is 'sign on once and use all applications'.
I am finding it actually means 'Same Sign On' and the functionality is 'log in to each of the the different applications one at time, using the same userid & password'. However, signing out does efficiently log me off all applications.
Still trying to get my head around the reason, and the benefit, of this. I'm sure it'll come to me.
Categories: DBA Blogs
What should I do with old hints in my workload?
We promised in an earlier post to cover some of the questions from the Optimizer round table discussion at Oracle Open World. Here's our first in a series of posts that will address these questions.
Q: When moving from 10g to 11g, should hints in existing SQL be removed?
A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:
If you find that some of your queries really do still require hints to get an optimal plan, you should consider creating a SQL plan baseline, and allowing SPM to manage the plan for you. If you do this, then you will get the hinted plan for now, but if a better plan comes along later (for one of the reasons mentioned earlier), you will eventually get the benefits of the new plan, using the SPM evolve process. There are two ways that you can create a SQL plan baseline based on your hints that will be applied to the query without hints. The first way requires that a SQL plan baseline already exist for the unhinted query. That's described in an earlier post. Here are the steps to use if you don't already have a SQL plan baseline for the unhinted query.
1. Run the query with hints, and confirm that the plan is what you want:
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id and
s.time_id = t.time_id and p.prod_id < :pid
Plan hash value: 2290436051
2. Load SQL plan baseline from cursor cache, and associate it with the unhinted query:
Q: When moving from 10g to 11g, should hints in existing SQL be removed?
A: I was glad to see this question at the round table, since I think this is something people often overlook when adding hints to an application. Over time, hints can become stale. You may have added a hint to a query because of a weakness in an old version of the optimizer, but the hint might no longer be necessary to get the best plan. Even worse, the hint may force a plan which is suboptimal, for a couple of reasons:
- Depending on how your data change over time, the plan choice that the hint enforces might no longer be a good choice. This problem can occur even without a database upgrade. For example, if the distribution of values in a column change over time, an old access path hint may no longer be appropriate.
- The hint might prevent new optimizations from taking place. So after an upgrade, you will be stuck using the old hint-enforced plan, when the optimizer could have chosen something better. For example, you may have hinted a plan for a query with bind variables, to avoid a bad plan choice due to bind peeking in the pre-11g optimizer. In 11g, you will not get the benefits of adaptive cursor sharing because of the hints in the query.
If you find that some of your queries really do still require hints to get an optimal plan, you should consider creating a SQL plan baseline, and allowing SPM to manage the plan for you. If you do this, then you will get the hinted plan for now, but if a better plan comes along later (for one of the reasons mentioned earlier), you will eventually get the benefits of the new plan, using the SPM evolve process. There are two ways that you can create a SQL plan baseline based on your hints that will be applied to the query without hints. The first way requires that a SQL plan baseline already exist for the unhinted query. That's described in an earlier post. Here are the steps to use if you don't already have a SQL plan baseline for the unhinted query.
1. Run the query with hints, and confirm that the plan is what you want:
var pid number
exec :pid := 100;
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...
select sql_id from v$sql where sql_text like 'select /*+ leading(t) */ p.prod_name%';
SQL_ID
-------------
2qtu6hy4rf1j9
select * from table(dbms_xplan.display_cursor(sql_id=>'2qtu6hy4rf1j9',
format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id and
s.time_id = t.time_id and p.prod_id < :pid
Plan hash value: 2290436051
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | TIMES |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 | BITMAP CONVERSION TO ROWIDS | |
| 7 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 8 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 9 | INDEX RANGE SCAN | PRODUCTS_PK |
---------------------------------------------------------------
2. Load SQL plan baseline from cursor cache, and associate it with the unhinted query:
var sqltext clob;
begin
:sqltext := 'select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id > :pid';
end;
/
exec :pls := dbms_spm.load_plans_from_cursor_cache( -
sql_id => '2qtu6hy4rf1j9', -
plan_hash_value => 2290436051, -
sql_text => :sqltext);
3. Run the query without hints, and check that the SQL plan baseline was used.
select p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
-------------------------------------------------- ----------- -------------
...
select * from table(dbms_xplan.display_cursor(sql_id=>'a1ax3265pq8x7',
format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pidPlan hash value: 2290436051Note
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | TIMES |
| 4 | PARTITION RANGE ALL | |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 | BITMAP CONVERSION TO ROWIDS | |
| 7 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 8 | TABLE ACCESS BY INDEX ROWID | PRODUCTS |
| 9 | INDEX RANGE SCAN | PRODUCTS_PK |
---------------------------------------------------------------
-----
- SQL plan baseline SQL_PLAN_4rw2dhryc2w5h888547d3 used for this statement
Categories: DBA Blogs, Development
New Oracle ACE at Pythian
I recently nominated my colleague and good friend Christo Kutrovsky for the Oracle ACE program. Today, I’m excited to share that he has been accepted and is now part of the community of 200+ Oracle ACEs and Oracle ACE Directors.
Christo has been an active blogger on our blog and has made numerous top-class presentations at Oracle Conference world-wide. He has also been an active contributor on the Oracle-L list.
Christo has been always representing an independent voice in the community and it’s a pleasure to have him on board. I’m looking forward to his contributions to the Oracle ACE program and the Oracle community at large.
Welcome Christo!
Categories: DBA Blogs
LOCK_SGA can not use with AMM or ASMM
LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space.
On 11gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSESQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776MSQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set togetherOn 10gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memoryIf need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.Written By: Surachart Opun http://surachartopun.com
On 11gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSESQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776MSQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set togetherOn 10gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memoryIf need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Oracle Parallel Query Sorting and Index Creation Performance Problems
Ever wondered why recreating certain indexes takes forever, even when you do so in parallel? Ever wondered why certain PQ queries just don’t run that fast?
Here’s a serious performance bug that’s been in Oracle for a while, and finally there are hints of it been fixed, but only partially.
The bug happens when performing sorting operations in parallel, and the source data is already well sorted. The “ranger” doesn’t do a good job of properly assigning row ranges to sorter processes, and ~90% of the rows end up being sent to the same parallel process, regardless of the level of parallelism. So even if you have 256 CPUs, only about 10% performance improvement is achieve, instead of a factor of your parallelism when running the query in parallel.
For example, if the non parallel sort/index creation took 45 minutes, running with parallel 32 will take 41 minutes, instead of the possible 1.4 minutes (assuming you have sufficient horsepower).
When running a “Sort” operation in parallel, there are 2 sets of parallel processes. Producers and consumers. The number of producer/consumer pairs depends on your parallelism settings. So in the case of parallel 32 there are 32 producers and 32 consumers. This is well illustrated in the Oracle documentation HERE (Oracle web based documentation down, will update later). As each ‘producer’ is reading data it is sending it to the appropriate consumer for that “range”. For example, consumer 1 takes A-B, consumer 2 takes C-F, consumer 3 G-L and etc. The exact split is dynamically calculated by the “Ranger” process. Unfortunately it doesn’t work so good with sorted data.
The same applies to index creation. Index creation is basically a big sort, followed by writing out the result set into a B-Tree structure. Index creation suffers from the exact same ranging issues, at least until 11.2.
Here’s an example:
-- Create mini-sample table
create table mytest_s as select rownum r from dual connect by level <=400000;
-- Fetch only 1 row, no need to fetch all
begin
for c in ( select /*+PARALLEL(t,4)*/ * from mytest_s t order by 1) loop
exit;
end loop;
end;
/
select dfo_number "d", tq_id as "t", server_type, num_rows,rpad('x',round(num_rows*10/nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type),0)),'x') as "pr", round(bytes/1024/1024) mb, process, instance i,round(ratio_to_report (num_rows) over (partition by dfo_number, tq_id, server_type)*100) as "%", open_time, avg_latency, waits, timeouts,round(bytes/nullif(num_rows,0)) as "b/r"
from v$pq_tqstat order by dfo_number, tq_id, server_type desc, process;
d t SERVER_TYPE NUM_ROWS pr MB PROCESS I % OPEN_TIME AVG_LATENCY WAITS TIMEOUTS b/r
- - ----------- ---------- ----------- --- ------- - ---- ---------- ----------- ------- ---------- ----
1 0 Ranger 372 xxxxxxxxxx 0 QC 1 100 0 0 0 0 11
1 0 Producer 126144 xxxxxxxxxx 1 P012 1 32 0 0 19 2 6
1 0 Producer 47304 xxxx 0 P013 1 12 0 0 8 1 6
1 0 Producer 110376 xxxxxxxxx 1 P014 1 28 0 0 17 2 6
1 0 Producer 116176 xxxxxxxxx 1 P015 1 29 0 0 16 0 6
1 0 Consumer 7885 0 P008 1 2 0 0 6 1 5
1 0 Consumer 7884 0 P009 1 2 0 0 6 1 6
1 0 Consumer 7884 0 P010 1 2 0 0 7 2 6
1 0 Consumer 376347 xxxxxxxxxx 2 P011 1 94 0 0 16 4 6
1 1 Producer 5536 xxxxxxxxxx 0 P008 1 55 0 0 577 568 3
1 1 Producer 4508 xxxxxxxx 0 P011 1 45 0 0 587 571 4
1 1 Consumer 100 xxxxxxxxxx 0 QC 1 100 0 0 1 0 161
As you can see from this test case, the sorter processes (4 consumers) had a very uneven split, with 94% of the rows been sent to only one consumer. I tested this case with parallel 64, and in that case 90% gets sent to 1 consumer, with the other 10% evenly distributed on the remaining ones.
This essentially reduces your execution time by at most 10%.
A very similar thing happens if you create an index. I did, however, catch an anomaly in the test case. With parallel 4, the distribution is 66/34/0/0, while with parallel 8, it’s 100/0/0/0/0/0/0/0. I.e., terrible as all the work will be performed by 1 process, absolutely the same as Serial, only a little worse as there will be inter-process communication.
create index on mytest_s (r) parallel 4;
select dfo_number "d", tq_id as "t", server_type, num_rows,rpad('x',round(num_rows*10/nullif(max(num_rows) over (partition by dfo_number, tq_id, server_type),0)),'x') as "pr", round(bytes/1024/1024) mb, process, instance i,round(ratio_to_report (num_rows) over (partition by dfo_number, tq_id, server_type)*100) as "%", open_time, avg_latency, waits, timeouts,round(bytes/nullif(num_rows,0)) as "b/r"
from v$pq_tqstat order by dfo_number, tq_id, server_type desc, process;
SERVER_TYPE NUM_ROWS pr MB PROCESS I % OPEN_TIME AVG_LATENCY WAITS TIMEOUTS b/r
----------- ---------- ----------- -- -------- - --- ---------- ----------- ---------- ---------- ----------
Ranger 12 xxxxxxxxxx 0 QC 1 100 0 0 1 0 3974
Producer 132601 xxxxxxxxxx 2 P004 1 33 0 0 19 1 18
Producer 95265 xxxxxxx 2 P005 1 24 0 0 14 2 18
Producer 95265 xxxxxxx 2 P006 1 24 0 0 14 1 18
Producer 79497 xxxxxx 1 P007 1 20 0 0 11 0 18
Consumer 262308 xxxxxxxxxx 4 P000 1 66 0 0 76 73 18
Consumer 164 0 P001 1 0 0 0 77 74 19
Consumer 164 0 P002 1 0 0 0 76 73 19
Consumer 137364 xxxxx 2 P003 1 34 0 0 76 73 18
Producer 1 xxxxxxxxxx 0 P000 1 25 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P001 1 25 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P002 1 25 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P003 1 25 0 0 0 0 322
Consumer 4 xxxxxxxxxx 0 QC 1 100 0 0 1 0 322
create index on mytest_s (r) parallel 8;
SERVER_TYPE NUM_ROWS pr MB PROCESS I % OPEN_TIME AVG_LATENCY WAITS TIMEOUTS b/r
----------- ---------- ------------- -- -------- - --- ---------- ----------- ---------- ---------- ----------
Ranger 0 0 QC 1 0 0 6 2
Producer 47304 xxxxxxxx 1 P008 1 12 0 0 14 2 18
Producer 51246 xxxxxxxxx 1 P009 1 13 0 0 15 1 18
Producer 47304 xxxxxxxx 1 P010 1 12 0 0 15 2 18
Producer 45220 xxxxxxxx 1 P011 1 11 0 0 15 2 18
Producer 59130 xxxxxxxxxx 1 P012 1 15 0 0 17 3 18
Producer 35478 xxxxxx 1 P013 1 9 0 0 10 3 18
Producer 55188 xxxxxxxxx 1 P014 1 14 0 0 16 1 18
Producer 59130 xxxxxxxxxx 1 P015 1 15 0 0 17 1 18
Consumer 400000 xxxxxxxxxx 7 P000 1 100 0 0 52 49 18
Consumer 0 0 P001 1 0 0 0 52 49
Consumer 0 0 P002 1 0 0 0 52 49
Consumer 0 0 P003 1 0 0 0 52 49
Consumer 0 0 P004 1 0 0 0 52 49
Consumer 0 0 P005 1 0 0 0 52 49
Consumer 0 0 P006 1 0 0 0 52 49
Consumer 0 0 P007 1 0 0 0 52 49
Producer 1 xxxxxxxxxx 0 P000 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P001 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P002 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P003 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P004 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P005 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P006 1 13 0 0 0 0 322
Producer 1 xxxxxxxxxx 0 P007 1 13 0 0 0 0 322
Consumer 8 xxxxxxxxxx 0 QC 1 100 0 0 2 1 322
To further explore the implications of this bug, I created a more elaborate test case. I created several types of data, and tested ordering against each “class.”
create table mytest as
select rownum pk, trunc(dbms_random.value(0,400000)) rnd,
floor(rownum/1000) type_1000, floor(rownum/5) type_5,
mod(rownum,1000) type_1000mod,mod(rownum,5) type_5mod,mod(rownum,10009) type_10000mod,
sysdate-rownum/100 as dt,
rpad('x',10,'x') pad from dual connect by level <=400000;
To spare you some of the testing, here are my results. Query ran:
select /*+PARALLEL(t,[DEGREE])*/* from mytest order by [ORDER BY];
A few quick conclusions:
- The first column of order matters for distribution.
- If the Order by column has repeated values, PQ sort will be limited by number of distinct values, but only if they are not grouped together.
- Index creation on time series (log table, stock table) is slow, “type” indexes are slow.
- If you reorder the keys of an index, you may affect the time it takes to create it.
- Follow up on the preceding, especially true if you try to put low cardinality columns first to improve compression.
One could dispute in which cases data in the table is ordered, and it’s amazing how many cases this is:
- Time series data — ever-growing data. The PK is ordered; the “insert date” is somewhat ordered.
- Data warehouses — bulk load files are often ordered via some conditions.
- Sometimes it is good to reorder a table, to improve data locality and compression in data warehouses. This can, however, have negative effects on index build time.
- Sometimes one of the intermediate steps will return an ordered set for the final processing.
One example of the last type, is analytics. But that’s for a separate blog post.
And finally, to end on an optimistic note, it appears that 11.2 has the index creation issue resolved, but the order by in queries is still bad.
Categories: DBA Blogs
V$SGA_RESIZE_OPS.STATUS ERROR
In 10G version, the ASMM(Automatic Shared Memory Management) has been introduced to relieve DBAs from sizing some parts of the SGA by themselves.
ASMM can be configured by using the SGA_TARGET initialization parameter.when set > 0, the ASMM is enabledwhen set to 0, the ASMM is disabled
V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';SET PAGESIZE 900SET LINESIZE 255COL COMPONENT FORMAT A25COL INITIAL_SIZE FORMAT A10COL TARGET_SIZE FORMAT A10COL FINAL_SIZE FORMAT A10COL OPER_TYPE FORMAT A10select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIMEfrom v$sga_resize_opsorder by start_time, component;START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:2416/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:2416/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:3516/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35My result found "ERROR" (sizing operation was unable to complete) status.Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.
So, I checked v$sga_target_advice ...select * from v$sga_target_advice order by sga_size;SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads---------- --------------- ------------ ------------------- ---------------- 2560 .25 68538927 2.6068 5,648,312,410 5120 .5 34072278 1.2959 1,863,613,370 7680 .75 28290588 1.076 1,204,264,183 10240 1 26292359 1 1,020,822,398 12800 1.25 25737600 .9789 973,149,992 15360 1.5 25416834 .9667 973,149,992 17920 1.75 25377404 .9652 847,180,508 20480 2 25377406 .9652 767,045,950Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!
Written By: Surachart Opun http://surachartopun.com
ASMM can be configured by using the SGA_TARGET initialization parameter.when set > 0, the ASMM is enabledwhen set to 0, the ASMM is disabled
V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';SET PAGESIZE 900SET LINESIZE 255COL COMPONENT FORMAT A25COL INITIAL_SIZE FORMAT A10COL TARGET_SIZE FORMAT A10COL FINAL_SIZE FORMAT A10COL OPER_TYPE FORMAT A10select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIMEfrom v$sga_resize_opsorder by start_time, component;START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:2416/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:2416/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:3516/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35My result found "ERROR" (sizing operation was unable to complete) status.Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.
So, I checked v$sga_target_advice ...select * from v$sga_target_advice order by sga_size;SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads---------- --------------- ------------ ------------------- ---------------- 2560 .25 68538927 2.6068 5,648,312,410 5120 .5 34072278 1.2959 1,863,613,370 7680 .75 28290588 1.076 1,204,264,183 10240 1 26292359 1 1,020,822,398 12800 1.25 25737600 .9789 973,149,992 15360 1.5 25416834 .9667 973,149,992 17920 1.75 25377404 .9652 847,180,508 20480 2 25377406 .9652 767,045,950Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!
Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Changing NLS session parameters changes the plan
# Developer complained that performance was slow after setting NLS_SORT=CZECH_AI
# Confirmed that it changed the plan to run full table scans.
SQL> select name from emp where name = 'a';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2061206800
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_NAME | 1 | 12 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"=U'a')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> alter session set NLS_COMP=LINGUISTIC;
Session altered.
SQL> alter session set NLS_SORT=CZECH_AI;
Session altered.
SQL> select name from emp where name = 'a';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''CZECH_AI''')=HEXTORAW('14000100 ') )
Note
-----
- dynamic sampling used for this statement (level=2)
# Confirmed that it changed the plan to run full table scans.
SQL> select name from emp where name = 'a';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2061206800
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_NAME | 1 | 12 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"=U'a')
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> alter session set NLS_COMP=LINGUISTIC;
Session altered.
SQL> alter session set NLS_SORT=CZECH_AI;
Session altered.
SQL> select name from emp where name = 'a';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''CZECH_AI''')=HEXTORAW('14000100 ') )
Note
-----
- dynamic sampling used for this statement (level=2)
Categories: DBA Blogs
Oracle 11g DB on Windows 7 – Success
Short Version
Tried to install Oracle Database 11.1.0.7 32-bit on Windows 7 32-bit. Failed at Network Configuration section. I found that in my case, the issue was that I installed in a path with spaces in it (c:\program files\oracle\….). Un-installed. Reinstalled in c:\oracle\product\11.1.0.7. Success!!!
Longer Version
OK, I admit it, I’ve been running Windows [...]
Categories: DBA Blogs, Development
Comparative Window Functions...
I've been known as a huge fan of Analytic functions (as evidenced by the Rock and Roll linkability!)
And - they could be getting better in the near future. Read this document for a proposal to allow analytics to access the current row value to be compared against any other row value in a defined window.
I've already supplied them with my feedback (which started with "this is an awesome idea") - and you can too - by posting it here. They'll be checking back to see what you say.
Also, this is being proposed as well:
Another window function extension, not contained in the attached proposal, is the notion of VALUE based windows. Currently, we have ROW based (or physical) and RANGE based (logical) windows. RANGE window has limitation in that there can only be one sort key in window ORDER BY. On the other hand, ROW based window is agnostic to column value and can be non-deterministic.
The new VALUE based window allows one to include all rows with "n" values before or after the current row's value. For example, VALUE 2 PRECEDING and 3 FOLLOWING would include all rows with 2 values that are prior to current row's value and all rows with 3 values that come after the current row's value in sort order.
ticker txndate volume
orcl 1 10
orcl 2 10 <--------------------------- start of window for (orcl,6,12)
orcl 2 11
orcl 2 11
orcl 3 11
orcl 6 12 <=== assume this is current row
orcl 7 12
orcl 11 11
orcl 11 12
orcl 11 12
orcl 13 11 <------------------------- end of window for (orcl,6,12)
Similar RANGE window would have rows [orcl,6,12] through [orcl,7,12]. Similar
ROW window would include rows [orcl,3,1] through [orcl,11,11].
The VALUE based window would find usefulness when there are gaps in the dataset. For example, a query like "find the intra-day maximum for a stock in the past three trading days". Today, to do this one has to aggregate on trading date and then compute the moving max (in the past 3 days).
VALUE based window can have multiple keys in ORDER BY.
Thanks in advance for any feedback or ideas you might have on this.
And - they could be getting better in the near future. Read this document for a proposal to allow analytics to access the current row value to be compared against any other row value in a defined window.
I've already supplied them with my feedback (which started with "this is an awesome idea") - and you can too - by posting it here. They'll be checking back to see what you say.
Also, this is being proposed as well:
Another window function extension, not contained in the attached proposal, is the notion of VALUE based windows. Currently, we have ROW based (or physical) and RANGE based (logical) windows. RANGE window has limitation in that there can only be one sort key in window ORDER BY. On the other hand, ROW based window is agnostic to column value and can be non-deterministic.
The new VALUE based window allows one to include all rows with "n" values before or after the current row's value. For example, VALUE 2 PRECEDING and 3 FOLLOWING would include all rows with 2 values that are prior to current row's value and all rows with 3 values that come after the current row's value in sort order.
ticker txndate volume
orcl 1 10
orcl 2 10 <--------------------------- start of window for (orcl,6,12)
orcl 2 11
orcl 2 11
orcl 3 11
orcl 6 12 <=== assume this is current row
orcl 7 12
orcl 11 11
orcl 11 12
orcl 11 12
orcl 13 11 <------------------------- end of window for (orcl,6,12)
Similar RANGE window would have rows [orcl,6,12] through [orcl,7,12]. Similar
ROW window would include rows [orcl,3,1] through [orcl,11,11].
The VALUE based window would find usefulness when there are gaps in the dataset. For example, a query like "find the intra-day maximum for a stock in the past three trading days". Today, to do this one has to aggregate on trading date and then compute the moving max (in the past 3 days).
VALUE based window can have multiple keys in ORDER BY.
Thanks in advance for any feedback or ideas you might have on this.
Categories: DBA Blogs
In support of My Oracle Support? - Part 1
I have previously written about issues with My Oracle Support. I still have issues and will probably always have issues, with the new system.
However, there is another side to the same story. It's a tool. It's a tool that has been made available to me. I strongly believe that a professional (counting myself in that set) I need to understand when, where and why the tools might benefit me.
So here starts my process of discovering the tool and understanding where it might help.
(Note to self - Next parts: MOS is a Portal but do we need portals?; MOS has a lot of functionality and is based on user feedback; MOS provides automation & who benefits?)
In support for MOS - part #1 of the learning process
I suspect the reason for Flash is similar to the JInitiator in the early Forms days.
We wanted rich functionality that was provided by Java in the client but no client (browser) consistently supported the JVM requirements. The first few iterations of JInitiator included complete (and excruciatingly slow) downloads and many many concerns about automatically downloaded software. Indeed, Oracle lost some Forms customers as a result of that.
As time went on the JInitiator download was split to download only the functionality required to accomplish the next task, if it was not there yet.
I see that Richard Miller's blog and Chris Wartiki's blog indicate they are looking at that change using Flash. (Having learned from history :-) the MOS team starts this after production.)
Back in the JInitiator hey-day, Oracle was constantly hammered by customers and competition as imposing non-standard, proprietary, software on the users. I often heard "it isn't Microsoft", "it is non-standard" as the reason for not allowing JInitiator. So people moved to Visual Basic, or some competitors, many of which have now disappeared. (Although underneath is was frequently "if it's Oracle we have this convenient excuse".)
So Flash is a non-proprietary (read non-Oracle) pseudo standard that should be acceptable to many users.
The next generation (HTML 5) is not available universally yet. (Hopefully the MOS Team will have designed to support the transition in the future.)
Indeed, one excuse for selecting Flash was to support the IE 6 users out there. (Better to support the rather large community that is stuck with non-compliant environments than to have a well-designed ADF environment for the future? It's about the numbers!)
The security issues that are found nearly daily are probably not as worrisome to many users as the benefit in user experience. Users seem to be complacent about security anyway, and there is a non-Flash alternative (http://supporthtml.oracle.com) for those of us who are squeamish.
In support of MOS, I think Flash addresses a significant part of the user community and probably addresses it well. I will be looking at that seriously, when I have lots of time.
And they have provided me an HTML alternative which limps along to satisfy my needs.
However, there is another side to the same story. It's a tool. It's a tool that has been made available to me. I strongly believe that a professional (counting myself in that set) I need to understand when, where and why the tools might benefit me.
So here starts my process of discovering the tool and understanding where it might help.
(Note to self - Next parts: MOS is a Portal but do we need portals?; MOS has a lot of functionality and is based on user feedback; MOS provides automation & who benefits?)
In support for MOS - part #1 of the learning process
I suspect the reason for Flash is similar to the JInitiator in the early Forms days.
We wanted rich functionality that was provided by Java in the client but no client (browser) consistently supported the JVM requirements. The first few iterations of JInitiator included complete (and excruciatingly slow) downloads and many many concerns about automatically downloaded software. Indeed, Oracle lost some Forms customers as a result of that.
As time went on the JInitiator download was split to download only the functionality required to accomplish the next task, if it was not there yet.
I see that Richard Miller's blog and Chris Wartiki's blog indicate they are looking at that change using Flash. (Having learned from history :-) the MOS team starts this after production.)
Back in the JInitiator hey-day, Oracle was constantly hammered by customers and competition as imposing non-standard, proprietary, software on the users. I often heard "it isn't Microsoft", "it is non-standard" as the reason for not allowing JInitiator. So people moved to Visual Basic, or some competitors, many of which have now disappeared. (Although underneath is was frequently "if it's Oracle we have this convenient excuse".)
So Flash is a non-proprietary (read non-Oracle) pseudo standard that should be acceptable to many users.
The next generation (HTML 5) is not available universally yet. (Hopefully the MOS Team will have designed to support the transition in the future.)
Indeed, one excuse for selecting Flash was to support the IE 6 users out there. (Better to support the rather large community that is stuck with non-compliant environments than to have a well-designed ADF environment for the future? It's about the numbers!)
The security issues that are found nearly daily are probably not as worrisome to many users as the benefit in user experience. Users seem to be complacent about security anyway, and there is a non-Flash alternative (http://supporthtml.oracle.com) for those of us who are squeamish.
In support of MOS, I think Flash addresses a significant part of the user community and probably addresses it well. I will be looking at that seriously, when I have lots of time.
And they have provided me an HTML alternative which limps along to satisfy my needs.
Categories: DBA Blogs
My Oracle Support survey
schavali has the following excellent suggestion at http://forums.oracle.com/forums/thread.jspa?messageID=3902274#3902274
---------------------------
I would encourage all that are facing issues to provide feedback to Oracle in one or more of the following ways -
1. Use the "Contact Us" link in MOS (which creates a non-technical SR)
2. Respond to blog posts at the Support blog sites - http://blogs.oracle.com/support and http://blogs.oracle.com/supportportal
3. Respond to survey being run by Daniel Fink - http://www.misterpoll.com/polls/460968
4. If your organization has an Oracle Service Delivery Manager, pl send him/her an email requesting that your feedback be forwarded on to Support Management.
---------------------------
According to Richard Miller's response to me in the comments, those of us who do not use Flash are in the minority and the majority seem to be quite happy with the new MOS Web 2.0 user experience.
I would certainly encourage responding to the survey. (Daniel Fink's MOS survey)
---------------------------
I would encourage all that are facing issues to provide feedback to Oracle in one or more of the following ways -
1. Use the "Contact Us" link in MOS (which creates a non-technical SR)
2. Respond to blog posts at the Support blog sites - http://blogs.oracle.com/support and http://blogs.oracle.com/supportportal
3. Respond to survey being run by Daniel Fink - http://www.misterpoll.com/polls/460968
4. If your organization has an Oracle Service Delivery Manager, pl send him/her an email requesting that your feedback be forwarded on to Support Management.
---------------------------
According to Richard Miller's response to me in the comments, those of us who do not use Flash are in the minority and the majority seem to be quite happy with the new MOS Web 2.0 user experience.
I would certainly encourage responding to the survey. (Daniel Fink's MOS survey)
Categories: DBA Blogs
Blogrotate #6: The Weekly Roundup of News for System Administrators
Like good day and welcome to issue 6 eh. This is another light one as it’s been another busy week. I have not had any time of my own to crawl through the news, but luckily my cohort Bill has flagged some good stuff for this week. Let’s get to it.
InternetHappy Birthday FireFox! The browser turns 5 years old today. Ryan Paul has more in his article
Firefox turns five: half a decade of Web liberation.
The Google Chromium blog has an interesting post called A 2x Faster Web about their attempts to speed up your surfing. “The initial results are very encouraging: when we download the top 25 websites over simulated home network connections, we see a significant improvement in performance – pages loaded up to 55% faster”, says the blog. This is still in early testing phases but sounds very promising.
Steven J. Vaughan-Nichols at Computerworld reflects on his time using linux on the desktop in Opinion: Linux desktop turns 10; world yawns. The article needs to be taken with a grain of salt. He states in the preamble that “The credit for creating and marketing the first Linux desktop designed for ordinary users goes to Corel Corp., which launched Corel Linux OS 10 years ago, in November 1999″. I take umbrage with this statement because by 1999 Caldera and Mandrake were already making great strides with linux on the desktop, Caldera even had a pretty boxed edition out in stores 2 years ahead of Corel, and Mandrake had a box out in 1998 (if memory serves). Sure Corel had a big name and some marketing power, but they were not the first and certainly not the best (Corel Linux sucked IMHO).
SecurityThis next article is a combination of software, security, legal, patents and what the hell. Microsoft Patents Sudo?!! is a story with it all. Microsoft has taken something that has been in use in the unix community since the 80s (and on Mac since OSX) and patented it. I wonder if RBAC is also covered by this? The link to the patent and information on sudo are included in the article.
Help Net Security has a post about a recent Cenzic report on web server and browser vulnerabilities. Read all about it in Firefox most vulnerable browser, Safari close second.
SoftwareAccording to Ars Technica, Novell is “offering a new commercial add-in for Visual Studio that will allow software developers to test and package .NET applications for Linux without having to leave their Windows development environment”. The new add-in, called MonoVS is available now for a free 30 day trial.
According to the WithinWindows blog Microsoft may have used GPL code in it’s released Windows 7 USB/DVD Download tool without contacting the author or releasing the source code back to the community. The article has some evidence to support this claim. Microsoft has pulled the tool pending further investigation.
VirtualizationSlashdot has a note about The Remus project. This project adds live migration/failover functionality to the Xen hypervisor allowing virtual machines to (reportedly) seamlessly move to another host without losing data or active connections. This was one of the last things that VMWare ESX offered that I could not find in the open source (read free) world so I’ll be sure to check it out myself.
BusinessHP to Acquire 3Com for $2.7 Billion is a new release at the HP site. There’s a lot in there, read it for yourself. Hopefully this will be a good boost for 3Com which has not made anything outstanding since the 3c501.
A couple of items on the AMD front. Firstly AMD and Intel Announce Settlement of All Antitrust and IP Disputes which I hope will bury the hatchet once and for all and allow both companies to get back to innovation. Secondly, AMD has release some more news about their future roadmap and the “fusion” of CPU with GPU in AMD avoiding Larrabee route on road to CPU/GPU “Fusion” by Jon Stokes.
Well there was a couple more items on the list but I am out of time. Have a great weekend!
So like get lost eh! Hoser!
Brad
Categories: DBA Blogs
Log Buffer #169: a Carnival of the Vanities for DBAs
The 169th edition of Log Buffer, the weekly review of database blogs, has been published on Pakistan’s First Oracle Blog by Fahd Mirza.
This is the first Log Buffer published outside Pythian in quite a while, and we’d love to have more. Log Buffer has a regular readership, and so makes a great way to present yourself and your blog to the DBA community at large. To get started, just send an email to the Log Buffer coordinator.
And now, here is Log Buffer #169.
Categories: DBA Blogs
My Oracle Support is Web 2.0
My Oracle Support has been upgraded. There are pains in the upgrade.
The Oracle people are working very hard at getting over the pains. And they are blogging about it, much in the defense of the new system.
IN particular, Richard Millar writes a decent set of comments at http://blogs.oracle.com/supportportal/2009/10/part_three_-_-_special_areas_o.html
In the comments following the blog, he makes some very interesting statement about the technology and tech choices. Why Flash, for example.
Here is my response (also posted as a comment to his Blog - so let's keep the discussion over there ...):
You write that "Older ADF 10 and HTML DB are very limited (or impossible) for Web 2.0 type interactions (and if we can get past the production bugs, I think you might find these new features valuable!)."
There is a HUGE assumption about the way we work in that statement.
Web 2.0 has it's place. Even with Support.
But not all of us need, want, or can benefit from Web 2.0 The problem is that Web 2.0 makes assumptions about the way we want to, or need to, work. And that means the design team is pigeon-holing the user community.
Think of it this way - Web 1.0 is command line, web 2.0 is GUI. GUI is 'friendly'. Command line, once learned, is 'efficient'.
There is a significant amount that can be done with Web 1.0. And much of that can be done faster and at a MUCH lower costs to both your side and ours.
Web 1.0 and Web 2.0 can coexist, just as command line and GUI can coexist.
The Oracle people are working very hard at getting over the pains. And they are blogging about it, much in the defense of the new system.
IN particular, Richard Millar writes a decent set of comments at http://blogs.oracle.com/supportportal/2009/10/part_three_-_-_special_areas_o.html
In the comments following the blog, he makes some very interesting statement about the technology and tech choices. Why Flash, for example.
Here is my response (also posted as a comment to his Blog - so let's keep the discussion over there ...):
You write that "Older ADF 10 and HTML DB are very limited (or impossible) for Web 2.0 type interactions (and if we can get past the production bugs, I think you might find these new features valuable!)."
There is a HUGE assumption about the way we work in that statement.
Web 2.0 has it's place. Even with Support.
But not all of us need, want, or can benefit from Web 2.0 The problem is that Web 2.0 makes assumptions about the way we want to, or need to, work. And that means the design team is pigeon-holing the user community.
Think of it this way - Web 1.0 is command line, web 2.0 is GUI. GUI is 'friendly'. Command line, once learned, is 'efficient'.
There is a significant amount that can be done with Web 1.0. And much of that can be done faster and at a MUCH lower costs to both your side and ours.
Web 1.0 and Web 2.0 can coexist, just as command line and GUI can coexist.
Categories: DBA Blogs