Oracle Rant

I wanted to pass on a tip to anyone doing ASM or CRS or both on Sun Solaris. When you install CRS, ASM, or both, you have to use raw devices. CRS needs the raw devices for the OCR and voting disks. ASM needs the raw devices so that it can control them and offer them in storage pools known as disk groups. Solaris uses a Virtual Table of Contents (VTOC) file that it uses to share devices between servers. When the disk is formatted by your Solaris SA, you need to make sure they do not use the first cylinder. Instead of using cylinder 0 to whatever, they need to use from cylinder 1 to whatever, leaving cylinder 0 alone. That way, when you dd the disks it will not cause the disks to “disappear”. My Solaris admin and I went through it about 4 times till we found the catch. The documentation on metalink is a little fuzzy so hopefully this helps some people out.

Also, with raw devices, I would suggest using an mknod command to label the disks so that it is much easier to support in the future.

Oracle has a table type of Global Temporay table that is unique to each session using it. You define the table as a Global Temporary table. What is useful about this type of table is that session A can insert rows into this table and session B can also insert rows into this table. Session A will never see Session B’s rows and vice versa. You can also define if the rows are preserved after a commit or deleted. Once a user disconnects they can never access those rows again. Temporary tables can work well in different types of environments where you want to code to use certain tables but not have the data mix with those of other sessions. The bad thing is that if you collect statistics, they may be wrong. For example, we found Oracle doing nested loops when it should have been doing a hash join.

What did we do to correct this? We deleted statistics on temporary tables and then locked the table stats. It is quite simple to do, and there are two commands that you can use.

exec dbms_stats.delete_table_stats('SCHEMA', 'TABLE_NAME');

exec dbms_stats.lock_table_stats('SCHEMA', 'TABLE_NAME);

That’s about all there is to it. Now when a query runs against this temporary table, Oracle will automatically do dynamic sampling. To change the level of the dynamic sampling, you can alter the parameter optimizer_dynamic_sampling. I believe the default is 2. For more information on the different levels of dynamic sampling, please visit the Oracle Performance Tuning Guide and the Oracle Database Reference.

ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [1], [1], [0] 

We got this error in Oracle 10g. A little about our environment. This is 10.2.0.3 and we are using Transparent Data Encryption (TDE). I found on metalink that there is a bug (see note Note:406958.1) with Complex View Merging and Transparent Data Encryption (TDE).

The fix was to set _complex_view_merging=false or upgrade to 11g. The problem with this approach is that Oracle is not able to efficiently rewrite queries using Complex View Merging and the upgrade to 11g will introduce some risks. Queries that should take seconds may now take minutes due to disabling Complex View Merging. I found a better work around. Do not set _complex_view_merging=false. If you did, set it back to true. Find the offending view that is causing the problem and build it with the /*+ no_merge */  hint.

For example:

CREATE OR REPLACE FORCE VIEW V_TEST_VIEW
AS
SELECT DISTINCT fi.provider_id as fi_id
, co.provider_id as co_id
, ag.provider_id as ag_id


Instead, do this:

CREATE OR REPLACE FORCE VIEW V_TEST_VIEW
AS
SELECT /*+ no_merge */ DISTINCT fi.provider_id          as fi_id
,      co.provider_id          as co_id
,      ag.provider_id          as ag_id

And there you go. You still get Complex View Merging except on views that contain columns that are encrypted by TDE.