Opatch quick tip

I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error:


C:\oracle\stage\22581007>opatch apply
Oracle Interim Patch Installer version 12.1.0.1.10
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\1210~1.2
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 12.1.0.1.10
OUI version       : 12.1.0.2.0
Log file location : C:\oracle\product\1210~1.2\cfgtoollogs\opatch\22581007_Mar_31_2016_18_32_02\apply2016-03-31_18-32-01 PM_1.log

Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]

My first thought was this was due probably to the fact that I’ve got countless installations on this laptop, some running, some stopped, etc etc.

Turns out to be a much simpler issue. When running OPatch on Windows, make sure you are running it with the “As Administrator”.

Simple Smile

Median in SQL

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial Smile query can determine the median salary from the EMP table


SQL> select avg(sal) from
  2  ( select x.sal
  3   from scott.EMP x, scott.EMP y
  4   group by x.sal
  5   having sum(sign(1-sign(y.sal-x.sal))) in
  6          ( trunc((count(*)+1)/2), trunc((count(*)+2)/2) ) );

  AVG(SAL)
----------
      1550

Of course, if that is basically gobbledygook to you (and it certainly is to me!)…well, you should check out Oracle’s cool Analytic SQL features here

Are your SQL Plus scripts going to ‘ell ?

Excuse the hideous pun in the title.  All will become clear shortly.

Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database.

Here’s a simple script I want to run:

image

and here’s my very sophisticated Smile batch file (which could just as easily be a Unix shell script) to run it:

image

In my case, I’m planning on using a wallet to not reveal the password (see here for details ).

But what if my wallet is wrong or missing?  Or if I’m explicitly coding a username / password and either are incorrect ?

Then this happens when I run my batch file:

image

Yes my connection failed…but notice that the script did not complete.

It…..just….sat….there.

That’s no good…from an observer’s perspective, it is still “running”.

So here’s a quick tip.  Whenever you launch SQL Plus from within a shell or script, always include the -L parameter (“minus elle”)

image

If you can’t connect, SQL Plus will exit and at least you’ll get some feedback that all is not OK.

So use minus elle, to stop your scripts from going to hell Smile

Up to date text indexes

If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example,


SQL> create table t1 (
  2    x int,
  3    y varchar2(1000) )
  4  ;

Table created.

SQL>
SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('sync (on commit)');

Index created.

SQL>
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1
  2  where contains(y,'quick') > 0 ;

         X
----------
Y
----------------------------------------------------------------------------------------------------
         1
The quick brown fox jumped over the lazy dog

         3
Several fabulous dixieland jazz groups played with quick tempo

But don’t forget. There is no such thing as a free lunch. As has always been the case, the more you can index “in batch” the less fragmentation you get with your text index, and obviously, if you’re planning on doing a stack of activities when you commit…then your commit performance will be impacted. Let’s compare the insert’s above, with and without a text index in place.


--
-- With index
--
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

-- 
-- Without index 
-- 
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Not too shabby…but noticeable.

India bound !

As part of the activities organised by the Oracle Ace Program and the All India Oracle User Group, the OTN Yathra tour is coming up in a few weeks.

OTNYathra2016

I’ll be speaking on 12c developer features and advanced SQL techniques at the following locations:

  • 23-Apr-16 Saturday,  Chennai
  • 24-Apr-16 Sunday, Bangalore
  • 26-Apr-16 Tuesday, Hyderabad
  • 28-Apr-16 Thursday, Pune

If you’re in any of these places, come along for some great education, good fun and don’t be shy – come up and say Hello.

Full details are here

(Special thanks to Sai for his organizational efforts in getting this all together).

The death of the demo script

On AskTom, one of the things we always strive to do is provide demo scripts in our answers where appropriate.  That way, people can see our line of thought, and hopefully get an understanding as to how we arrived at a solution.  And there’s two very important concepts in that last sentence:

  • our line of thought
  • how we arrived

The demo script itself, is a representation of that, not necessarily a cast iron guarantee that you should be cut/pasting it into your Production system!  If you are honestly expecting that a demo script that we run, will be exactly how it will run on your system, you’re perhaps being a little naive.

Why ? Because for a long time in the speaker community, there’s been a humorous theme about how the concept of demo scripts has required more and more “terms and conditions” as each version of Oracle got more sophisticated.  Namely, the evolution of  what a demo script might be impacted by :

(Now before anyone takes me to task here on versions and features etc, just like demo scripts themselves, I’m using this as an example of what you need be aware of, not a definitive or exhaustive list)

Oracle 7:

“Here’s my script…and chances are, it will just run like this on your Oracle 7 installation.”

Oracle 8: (multiple blocksizes)

“Here’s my script…it was built an 8k blocksize, your results may differ if you are not.”

Oracle 8i: (locally managed tablespace, and auto segment space management introduced)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, your results may differ if you are not.”

Oracle 9i: (system stats, cpu costing introduced)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with no system stats calculated or stored, your results may differ if you are not.”

Oracle 10g: (NOWORKLOAD system stats always on, dynamic sampling defaults)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with NOWORKLOAD system stats gathered, dynamic sampling set to default, your results may differ if you are not.”

Oracle 10.2: (auto histogram, ‘default’ system stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, your results may differ if you are not.”

Oracle 11: (auto multiblock read count)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, and a file system that supports 1meg physical I/O, your results may differ if you are not”

Oracle 11.2: (serial direct read, exadata-specific system stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, and a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, your results may differ if you are not”

Oracle 12: (auto-stats, clustering factor optimization, automatic extended stats)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, or a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, with clustering factor TABLE_CACHED_BLOCKS left unchanged, and no hidden columns automatically added to your table by extended stats, your results may differ if you are not”

Oracle 12.1.0.2: (in-memory, large table caching, full database caching, on-chip functionality)

“Here’s my script… it was built an 8k blocksize, on a locally managed tablespace, with uniform extent size 1m, in segment space auto-managed, with installation default system stats gathered, dynamic sampling set to default, stats gathered with default parameters, and no previous queries run against your table other than the one in the demo, with multiblock read count explicitly set to 128, or a file system that supports 1meg physical I/O, with serial direct enabled, and a buffer cache size of exactly 2G, and on a non-exadata system, with clustering factor TABLE_CACHED_BLOCKS left unchanged, and no hidden columns automatically added to your table by extended stats, on an Intel chipset, without large table caching activated, and no auto capture of sql plans, your results may differ if you are not”

And I’ve not even mentioned the myriad of platform differences (things might run slightly different on Windows versus Linux versus AIX ), and of course, the question may have come from someone running Oracle Apps, which depending on the version being used, these parameters such as those below may be set to non-default values:

  • _b_tree_bitmap_plans
  • _fast_full_scan_enabled
  • _like_with_bind_as_equality
  • _sort_elimination_cost_ratio
  • optimizer_secure_view_merging

which changes how queries are optimized and even how they may be transformed before optimization.

But what if we eliminate the version discrepancy ? … Rest assured, even if you have the exact same version of software as us, the same potential for difference applies.  We’re typically on our VM inside our laptop on a private network, and you are on a server with different CPU, different RAM, different parameters, different storage, different platform.  What patches have you applied ? What parameters have you set ? There are just so many variables in play here.

So are demo scripts are waste of time?… Of course not.  Just remember what they are – an addenda to a line of thought, an application of logic, a glimpse into a potential useful process.  Your job is then to take advantage of that thought and logic and discover for yourself the possible benefits on your system.

Happy scripting ! Smile

Views as “stored text”

You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines.

This is by and large true, but don’t forget, this is not  the same as saying we simply take whatever text you give us, and store it.

Let’s take a look at a simple example proving that this is not the case.


SQL> create table t as select * from all_objects;

Table created.

SQL>
SQL> create or replace
  2  view V as select * from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
------------------------------------------------------------
select "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","D
ATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIME
STAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPA
CE","EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINE
D" from T

Notice that “*” has been expanded out to the full list of columns.

Sidebar: Notice also the nice column TEXT_VC which is the varchar2 equivalent of the older style LONG column TEXT. You’ll get that on 12c.

Interestingly, aliases will be preserved in the view definition if provided


SQL> create or replace
  2  view V as select t.* from T;

View created.

SQL>
SQL> select text_vc from user_views
  2  where view_name = 'V';

TEXT_VC
------------------------------------------------------------
select t."OWNER",t."OBJECT_NAME",t."SUBOBJECT_NAME",t."OBJEC
T_ID",t."DATA_OBJECT_ID",t."OBJECT_TYPE",t."CREATED",t."LAST
_DDL_TIME",t."TIMESTAMP",t."STATUS",t."TEMPORARY",t."GENERAT
ED",t."SECONDARY",t."NAMESPACE",t."EDITION_NAME",t."SHARING"
,t."EDITIONABLE",t."ORACLE_MAINTAINED" from T

One important takeaway from this, is that since the “*” is not preserved in the view definition, if you add or drop columns to the base table, the view needs to be recreated to correctly reflect that change.