We had a fair number of problems related to bind variable peeking in our 10.2.0.3 and 10.2.0.4 dbs. In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files.
Flash forward to Oracle 11g (11.2.0.2) and the optimizer has been enhanced to use bind variable peeking much better. After converting to 11g, one of my queries was running slower than before. I ran the slow query with hard-coded values and it finished in less than a second. I then tried the same thing with bind variables and it ran in 2400 seconds, with a different execution plan.
These were the exact symptoms that originally brought me to set _optim_peek_user_binds in the first place. My first thought was that I left the underscore parameters commented out after the upgrade. Nope, it was set "correctly", or at least how I thought it should be set.
I then set optimizer_features_enable back to 10.2.0.4 for my session and re-executed the query. Sure enough, it was back to sub-second response time. I switched the optimizer_features_enable back to 11.2.0.2 and it was slow again. Aha, something changed!
More research yeilded some documentation to digest:
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
http://www.pythian.com/news/820/bind-peeking-ad-hoc-queries-stable-performance-on-10g-you-can-only-pick-any-two/
After much reading, thought, and testing, I decided that _optim_peek_user_binds=false was not needed in 11g for my environment.
Thursday, April 07, 2011
Sunday, March 27, 2011
The Cost of Mediocre
Over the years I've written tons of scripts to monitor all sorts of things on the database. Some of those scripts turned out to be helpful on an ongoing basis and some ended up dying on the vine when new features of Oracle came out.
One of my scripts looks at the top processes in a database from the CPU's perspective. In other words, how much time did the process use on the CPU over it's entire lifetime. About a month ago I identified a process that was using a disproportionate amount of time as compared to everything else. It was a new process, so I was pretty sure something could be tuned. I assigned another DBA to trace it and make tuning suggestions. One particular query that was doing a Full Table Scan was identified as the major time consumer and suggestions were made on how to fix the query.
Over the last few weeks, the developer had been busy. When I enquired as to the status of the slow query the response was that the job runs in an acceptable time and nothing would be done on it until later in the month.
Flash forward to a couple days ago when the process showed up on my report again. We had burned about three hours of CPU time just doing Full Table Scans all day long. I created an index on some fields that I thought were appropriate and a single execution of the query when from 44+ seconds to .04 seconds. My report the next day showed the process down to about 10 minutes of CPU time.
The kicker: The developer said "Oh, I thought of that a month ago but never implemented it." Meanwhile, I burned about 84 hours of CPU time.
One of my scripts looks at the top processes in a database from the CPU's perspective. In other words, how much time did the process use on the CPU over it's entire lifetime. About a month ago I identified a process that was using a disproportionate amount of time as compared to everything else. It was a new process, so I was pretty sure something could be tuned. I assigned another DBA to trace it and make tuning suggestions. One particular query that was doing a Full Table Scan was identified as the major time consumer and suggestions were made on how to fix the query.
Over the last few weeks, the developer had been busy. When I enquired as to the status of the slow query the response was that the job runs in an acceptable time and nothing would be done on it until later in the month.
Flash forward to a couple days ago when the process showed up on my report again. We had burned about three hours of CPU time just doing Full Table Scans all day long. I created an index on some fields that I thought were appropriate and a single execution of the query when from 44+ seconds to .04 seconds. My report the next day showed the process down to about 10 minutes of CPU time.
The kicker: The developer said "Oh, I thought of that a month ago but never implemented it." Meanwhile, I burned about 84 hours of CPU time.
Friday, March 25, 2011
Upgrading to Oracle 11gR2
I am about half way through upgrading all my dbs to Oracle 11gR2 from various versions of 10gR2. Out of all the upgrades I've performed from 7.0 up to 10gR2, the 10g to 11g upgrade is by far the most hassle free of all.
I have abandoned the GUI upgrade tool this time. The GUI tool is fine, but internal issues prevented me from running the GUI remotely. This meant that somebody had to be on-site to push buttons for each and every 9i to 10g upgrade. I have moved to a command-line upgrade that I have automated through a series of ksh scripts. I feed the right parameters to the script and two hours later it's done. No fuss, no muss.
The most important thing I have learned so far is that utlu112i.sql is your friend. If utlu112i.sql even hints that something might go wrong during your upgrade, you better fix it. For example, it's a heck of a lot easier to fix JVM issues before the upgrade rather than during the upgrade. You have to trust the utlu112i.sql script. If it thinks you don't have enough space in your SYSTEM tablespace, you probably don't. utlu112i.sql is a little cautious on some stuff like the DBMS_LDAP warnings, but it's best to understand what all the warnings mean.
My general approach has been to upgrade what I have and make sure everything works on 11g before I start to exploit the 11g features.
Full Speed Ahead! To infinity and beyond!
I have abandoned the GUI upgrade tool this time. The GUI tool is fine, but internal issues prevented me from running the GUI remotely. This meant that somebody had to be on-site to push buttons for each and every 9i to 10g upgrade. I have moved to a command-line upgrade that I have automated through a series of ksh scripts. I feed the right parameters to the script and two hours later it's done. No fuss, no muss.
The most important thing I have learned so far is that utlu112i.sql is your friend. If utlu112i.sql even hints that something might go wrong during your upgrade, you better fix it. For example, it's a heck of a lot easier to fix JVM issues before the upgrade rather than during the upgrade. You have to trust the utlu112i.sql script. If it thinks you don't have enough space in your SYSTEM tablespace, you probably don't. utlu112i.sql is a little cautious on some stuff like the DBMS_LDAP warnings, but it's best to understand what all the warnings mean.
My general approach has been to upgrade what I have and make sure everything works on 11g before I start to exploit the 11g features.
Full Speed Ahead! To infinity and beyond!
Wednesday, March 23, 2011
Seriously?
I haven't done any research on this yet, so I don't know if it is fixed in a subsequent patch or a later version. But seriously, a space?
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SQL> select trigger_name, triggering_event from user_triggers; TRIGGER_NAME TRIGGERING_EVENT ------------------------------ ------------------------------ DDL1 DDL LT1 LOGON LT2 LOGON SQL> select trigger_name, triggering_event from user_triggers 2 where triggering_event = 'LOGON'; no rows selected SQL> select trigger_name, '^' || triggering_event || '^' triggering_event 2 from user_triggers; TRIGGER_NAME TRIGGERING_EVENT ------------------------------ ------------------------------ DDL1 ^DDL ^ LT1 ^LOGON ^ LT2 ^LOGON ^
Tuesday, January 11, 2011
My first 11g show stopper: Purging the Recyclebin
I've run into my first issue with 11.2.0.2. After one of my dbs was upgraded, every time I try to purge the recyclebin, I get an ORA-00600 error:
It doesn't matter if the recyclebin is on or off.
I have done about six upgrades and this is the only db with the problem. I can backup this db using RMAN and restore it on another host and the error still gets generated.
I have about 43 objects owned by SYS and named WRH$* left in the recyclebin after the upgrade, but these are normal after the upgrade. Or, at least, I have these objects in the recyclebin after every 10.2.0.5 -> 11.2.0.2 upgrade I've done so far.
I can purge the objects individually to get rid of the immediate problem, but I am most concerned that this will show up again in another db.
I couldn't find anything on metalink. I've had a TAR open with Oracle Support for well over a month and it's going nowhere.
SQL> purge dba_recyclebin; purge dba_recyclebin * ERROR at line 1: ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], []
It doesn't matter if the recyclebin is on or off.
I have done about six upgrades and this is the only db with the problem. I can backup this db using RMAN and restore it on another host and the error still gets generated.
I have about 43 objects owned by SYS and named WRH$* left in the recyclebin after the upgrade, but these are normal after the upgrade. Or, at least, I have these objects in the recyclebin after every 10.2.0.5 -> 11.2.0.2 upgrade I've done so far.
I can purge the objects individually to get rid of the immediate problem, but I am most concerned that this will show up again in another db.
I couldn't find anything on metalink. I've had a TAR open with Oracle Support for well over a month and it's going nowhere.
Monday, January 10, 2011
Listener Logging in 11g
By default, the listener logging is not turned on in 11r2. You have to turn it on by specifying:
in your listener.ora file.
LOGGING_listener_name=ON
in your listener.ora file.
Friday, December 10, 2010
Something Interesting about the 11g Client
If you're just beginning to implement Oracle 11g, you need to be aware of the Automatic Diagnostic Repository (ADR). It has nothing to do with AWR, ADDM, ASM, ASSM, or any of the other acronyms Oracle has come up with over the last two releases.
In terms of the database, ADR could be a great thing. It's integration with Enterprise Manager is probably one of the best parts about the feature. The database manages the retention policy of how long you want to keep these files around based on the scheduler, all things we know how to deal with.
However, you need to be aware that in a client-only configuration (such as a shared client over NFS) there is nothing to manage the logfiles generated by ADR. The logs get place in /var/tmp and could potentially be across every machine in your environment. That could be a nightmare if you're running with several hundred clients or multiple domains. Left unchecked, it could fill up your root disk if that's where you have /var/tmp. I'll leave it to your imagination how I found out about this new feature.
Instead, you might want to turn ADR off by setting
In terms of the database, ADR could be a great thing. It's integration with Enterprise Manager is probably one of the best parts about the feature. The database manages the retention policy of how long you want to keep these files around based on the scheduler, all things we know how to deal with.
However, you need to be aware that in a client-only configuration (such as a shared client over NFS) there is nothing to manage the logfiles generated by ADR. The logs get place in /var/tmp and could potentially be across every machine in your environment. That could be a nightmare if you're running with several hundred clients or multiple domains. Left unchecked, it could fill up your root disk if that's where you have /var/tmp. I'll leave it to your imagination how I found out about this new feature.
Instead, you might want to turn ADR off by setting
DIAG_ADR_ENABLED=off in your sqlnet.ora file.
Wednesday, December 08, 2010
Removing OLAP from 10g
There is a metalink note that describes how to remove the OLAP option if it was installed in your database incorrectly. See ML Note 739032.1 for details. Also know that the described procedure doesn't fully remove your OLAP object and that you have to drop some manually according to Note 1060023.1.
Friday, December 03, 2010
Working with Oracle 11g
As I continue to work on Oracle 11g, I am constantly reminded of a quote from Bertrand Russell:
In all affairs it's a healthy thing now and then to hang a question mark on the things you have long taken for advantage.
Wednesday, December 01, 2010
Most Ridiculous Password
I encountered a website yesterday that required a complex password. It's password rules were:
I am glad they have a "password reset" link.
- at least 9 characters
- must include a capitol letter
- must include three digits
- must not repeat a letter, but can repeat a number
- must include one "special" character above a number [!@#$%^&*()]
- can not include "special" characters that is not above a number
- can not start with a capitol letter
- can not start with a number
I am glad they have a "password reset" link.
Tuesday, November 23, 2010
Some Great New Features
I've been off exploring Oracle 11g for a little bit trying to figure out a strategy to upgrade my environment. Oracle 11g has a ton of new features, most of which are now "options" (in other words they cost more money).
When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL didn't exactly work the way you expected. Then I found out rows had to be added in a certain non-standard way and decided that while the feature was great in theory, in practice it was only about 80% of what I really wanted. Fast forward to 11g and we find out that now segment compression works the way you think it should; except now it costs you extra.
Another feature, Data Guard, is quite frankly awesome. Sure, you have to license both hosts in a data guard setup, but that's the penalty you have to pay to have data in two locations. One of the things that always bothered management was that the standby host is essentially idle just waiting for a disaster that may never come. Sure, you can open the standby in read-only mode, but you have to stop keeping it up-to-date in order to do that. Flash forward to 11g and now you have the ability to have the standby database applying logs but you can also run queries against it! Wow, this will make my management really happy...until they find out they have to pay to use an incremental enhancement to a basic EE feature.
I should not really be surprised. Oracle came up with a free statistics collection tool called "StatsPack" way back in 8i. Using the statistics you could do all sorts of historical trending and figure out why your database was slow after the fact. Flash forward to 10g and we have this new product called Automatic Workload Repository (AWR) ... but now you have to pay for it. It's really just StatsPack version 2, but now they need to garner extra revenue for it.
Meanwhile, my support bill is due this month so I can pay for "Software Updates and Support" for another year.
When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL didn't exactly work the way you expected. Then I found out rows had to be added in a certain non-standard way and decided that while the feature was great in theory, in practice it was only about 80% of what I really wanted. Fast forward to 11g and we find out that now segment compression works the way you think it should; except now it costs you extra.
Another feature, Data Guard, is quite frankly awesome. Sure, you have to license both hosts in a data guard setup, but that's the penalty you have to pay to have data in two locations. One of the things that always bothered management was that the standby host is essentially idle just waiting for a disaster that may never come. Sure, you can open the standby in read-only mode, but you have to stop keeping it up-to-date in order to do that. Flash forward to 11g and now you have the ability to have the standby database applying logs but you can also run queries against it! Wow, this will make my management really happy...until they find out they have to pay to use an incremental enhancement to a basic EE feature.
I should not really be surprised. Oracle came up with a free statistics collection tool called "StatsPack" way back in 8i. Using the statistics you could do all sorts of historical trending and figure out why your database was slow after the fact. Flash forward to 10g and we have this new product called Automatic Workload Repository (AWR) ... but now you have to pay for it. It's really just StatsPack version 2, but now they need to garner extra revenue for it.
Meanwhile, my support bill is due this month so I can pay for "Software Updates and Support" for another year.
Thursday, March 18, 2010
Job Market
The job market must be getting a little better. In the last weeks I've had three recruiter calls and they actually had jobs to fill.
Thursday, March 11, 2010
Upgrading to R12.1.2, Tip #1
While applying patch 7303033 on top of a 12.1.0 R12 installation, I got the following error in one of my workers:
The log from my worker showed:
I searched Metalink and didn't find an answer, so I created a TAR. Oracle Support came back and said this was covered in unpublished note 1066653.1 which suggests you can safely skip this script.
If you are unfamiliar with skipping a step from a failed worker, you can go into adctrl and choose 8 (although there are only 7 menu choices). That will ask you which worker you want to skip and restart.
FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @JTF:patch/115/import/US/jtfdiagresp.ldt -
Connecting to APPS......Connected successfully.
Calling FNDLOAD function.
Returned from FNDLOAD function.
Log file: /r12u/apps/apps_st/appl/admin/UPG/log/US_jtfdiagresp_ldt.log
Error calling FNDLOAD function.
Time when worker failed: Thu Mar 11 2010 13:45:18
The log from my worker showed:
Current system time is Thu Mar 11 13:45:16 2010
Uploading from the data file /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dumping from LCT/LDT files (/r12u/apps/apps_st/appl/fnd/12.0.0/patch/115/import/afscursp.lct(120.4.12010000.2), /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt) to staging tables
Dumping LCT file /r12u/apps/apps_st/appl/fnd/12.0.0/patch/115/import/afscursp.lct(120.4.12010000.2) into FND_SEED_STAGE_CONFIG
Dumping LDT file /r12u/apps/apps_st/appl/jtf/12.0.0/patch/115/import/US/jtfdiagresp.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (FND_APPLICATION JTF , FND_RESPONSIBILITY JTF DIAG_TOOL_RESP ) into FND_SEED_STAGE_ENTITY
Uploading from staging tables
Error loading seed data for FND_RESPONSIBILITY: APPLICATION_SHORT_NAME = JTF, RESP_KEY = DIAG_TOOL_RESP, ORA-20001: APP-FND-01572: Invalid foreign key data was found for this record.
Value DIAG_TOOL_MENU for column MENU_NAME does not exist in table FND_MENUS_VL.
Concurrent request completed
Current system time is Thu Mar 11 13:45:18 2010
I searched Metalink and didn't find an answer, so I created a TAR. Oracle Support came back and said this was covered in unpublished note 1066653.1 which suggests you can safely skip this script.
If you are unfamiliar with skipping a step from a failed worker, you can go into adctrl and choose 8 (although there are only 7 menu choices). That will ask you which worker you want to skip and restart.
Friday, February 26, 2010
Oracle Applications R12.1 Platform Migration, Tip #2
I've been battling with expdp and impdp trying to get my database moved to a different platform (more on that later). During the course of events, I decided that I had to apply the 11.1.0.7.2 patches to my $OH to make sure I was up to date. However, when I used opatch to apply, I got the following error:
Seems you have to be on the latest version of OPatch (patch 6880880) in order to apply it successfully.
ApplySession failed: Patch ID is null.
System intact, OPatch will not attempt to restore the system
OPatch failed with error code 73
System intact, OPatch will not attempt to restore the system
OPatch failed with error code 73
Seems you have to be on the latest version of OPatch (patch 6880880) in order to apply it successfully.
Wednesday, February 24, 2010
Malwarebytes
This is the best set of instructions I've seen for how to use Malwarebytes. I just used it to help me get rid of "Vista Internet Security". Bastards.
Tuesday, February 23, 2010
Oracle Applications R12.1 Platform Migration
I've mostly been following note 741818.1 to migrate my R12.1 db from 10gR2 on Solaris to 11gR1 on Linux. One gotcha, though, is that tablespace quotas must be re-assigned between step 4.3 and 4.4. I generated a little script off the source db using dba_ts_quotas.
Monday, February 22, 2010
Halleluiah
Learned from Niall Litchfield that there is a HTML version of Metalink called http://supporthtml.oracle.com/.
Friday, January 29, 2010
Metalink Out?
Yes, Metalink is down. It's been down for over an hour. I was told "No ETA, but we're hoping by noon."
Thursday, January 28, 2010
Installing Oracle Applications R12.1, Tip #5
While applying ademusr.sql, I ran into a couple errors similar to:
I researched the problem on metalink and found Doc 431067.1 which kind of suggested I could just grant permission to the object and move on. That's exactly what I did and restarted the worker with adctrl.
ORA-01720: grant option does not exist for 'SYS.DBA_USERS'
I researched the problem on metalink and found Doc 431067.1 which kind of suggested I could just grant permission to the object and move on. That's exactly what I did and restarted the worker with adctrl.
Wednesday, January 27, 2010
Installing Oracle Applications R12.1, Tip #4
Make sure you have your OLAP workspaces defined before you attempt the 12.1.1 upgrade portion. Otherwise, you will get an error similar the below when running MSDODPCODE.sql:
To fix:
Then you can restart your worker using adctrl.
ORA-37002: Oracle OLAP Failed to intiialize. Please contact Oracle OLAP technical Support ORA-33262: Analytic Workspace EXPRESS does not exist.
To fix:
sqlplus "/ as sysdba" @?/olap/admin/olap.sql SYSAUX TEMP
Then you can restart your worker using adctrl.
Subscribe to:
Posts (Atom)
