Search

OakieTags

Who's online

There are currently 0 users and 27 guests online.

Recent comments

Oakies Blog Aggregator

Writing Tips : Copyright Theft

writingIf you’ve followed my blog you will know people try to steal my articles all the time. It happens to all bloggers, unless they are unpopular or rubbish. You don’t really have to look for it, because your readers will spot it and tell you. :)

Some things to remember about this stuff…

All material published on the internet has an implicit copyright, even if there is no explicit copyright notice on the site. This means, unless the copyright owner gives you explicit permission, republishing their content is an act of theft. When you are writing, make sure you are doing the work, not stealing other people’s work.

Google actively downgrades the search rankings of duplicate content. They know who published the content first. By stealing the content you haven’t really done yourself any favours and you’ve broken the law. Way to go!

If you find someone is stealing your content, you have options. All blogging platforms and hosting providers are duty bound to follow up on copyright theft claims. If they ignore it, they can be deemed culpable. You should:

  • Contact the owner of the blog and politely ask them to remove the content.
  • If they don’t do it in a timely manner, issue a DMCA (or geographical equivalent) takedown notice to their hosting company. Hosting companies get really twitchy about them, and tend to react really quickly.

There are movements like Copyleft and Creative Commons, where people actively encourage redistribution, but even then you have to follow the rules. It’s not a golden ticket to do anything you want with the content. The content producer still reserves some rights. Remember, unless explicitly stated differently, the default stance is the material on the internet is copyright, not copyleft!

Cheers

Tim…

 


Writing Tips : Copyright Theft was first posted on May 28, 2015 at 6:51 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

One year at Delphix

It’s been over a year since I leapt into the void.

OK, more than a little melodramatic.  In many respects, I was leaping from the void by joining a promising and exciting startup company like Delphix.

Business was still brisk as an independent consultant at EvDBT, but for the past several years, I was experiencing what I called “just-in-time engagements”.  That is, new consulting engagements were just mysteriously showing up at the right time just before the current one was ending.  Frankly, it was getting a bit spooky, and I had been on pins and needles for a couple years watching it happen, wondering like a farmer if the day would come when the rain did not appear on time.  That day had shown up previously, during the recession of 2001 – 2002, when I experienced about 2-3 weeks of no work early in 2002, but that was the only dry spell I encountered in almost 16 years at EvDBT.  However, I wasn’t eager to see another one…

So a little over twelve months ago, on 01-May 2014, I left the world of Oracle technology consulting that I had first entered on January 15, 1990.  Well, I haven’t really left Oracle technology, but I’m no longer making a living at it.  Oracle is a big part of Delphix, but only a part.

What have been the highlights during my first year at Delphix?

  • learning data virtualization
  • learning how to tune virtual databases for Oracle and SQL Server
  • learning VMware and earning my VCA certification
  • carving a personal niche within a small company
  • became proficient at Windows Powershell    #ff0000;"><- no kidding!  true!
  • continuing to present at Oracle conferences, often traveling and presenting with my brilliant wife, Kellyn Gorman

Yes, Kellyn and I got married during the past year!  It took us each a few tries at marriage, but we each hung in there, and got it right this time.  They say that remarriage is the triumph of optimism over experience, and I’m delighted to say that optimism trumps experience.  We did the deed in grand style, spending the week at Oracle Open World in San Francisco, coming home on Friday and getting married that Sunday in a tiny ceremony with immediate family only.  We finished up the RMOUG TD2015 call-for-papers on Wednesday and Kellyn publshed the agenda on Thursday, and then on Saturday we flew to Europe to do a joint-keynote and present at the Slovenian Oracle Users Group in Ljubljana and the Croatian Oracle Users Group in Rovinj.  After spending time with Joze and his lovely wife Lili, we blasted out of Croatia and scooted over to beautiful Venice for a quiet, blissful week-long honeymoon, meeting with Lothar and his lovely wife Birgit during the week as well.

Then, it was back to reality through the end of the year, getting swept up in the preparations for the RMOUG conference.

Delphix had a spectacular Q4 ending on 31-January 2015, where that financial quarter alone equaled the earnings from the entire previous fiscal year.  Lots of celebrations, victory laps, and high fives at the company all-hands meeting in early February, but what none of us in the Professional Services division saw was the looming tsunami of freshly-sold new deployments cresting just over our heads.  That wave crested and crashed down on us, and I found myself buried in work.  Just now, four months later in the new fiscal year, I’m finally able to look up and look around to find that winter and spring have passed and summer has arrived.My second year at Delphix has begun, and I’m curious as to what it will bring.

I’m continuing to heed the advice of Tim Minchin, who counsels against pursuing one’s Big Dream and instead suggests passionate dedication to short-term goals, in short that one be “micro-ambitious”.  That is say, that thing that is right in front of you right now?  Do it the very best you can, and put your back into it.  Whether it is a blog post, cutting the lawn, an email, or a new wax ring for the toilet in the basement.  Especially the new wax ring – don’t bugger that up!  And then do the next thing the same way.  And the next.  And the next.  Before you know it, you’ve climbed a mountain and have made a pretty good career besides.

Not unexpectedly at a small fast-growing company, hints have been made of a transition from the technical track to the managerial track.  This would finally reverse the track switch I made over 20 years ago while at Oracle, when I stepped off the managerial track in favor of the technical track.  I’ve never looked back on that decision, but should I be “micro-ambitious” here as well, take the new task right in front of me, and work to excel?  Or stick to my guns, stay with the technical track?

I’ve learned that it is a mistake to just “go with the flow” and bob along with the prevailing current.  If one is to succeed at something new, it must be a whole-hearted plunge accompanied by a full-throated war cry.

So, if you hear a strange noise rather like a cross between a “Tarzan yell” and someone choking on an avocado pit, don’t be alarmed.  Just listen a little longer to find whether you hear the “splat” of my corpse hitting pavement, or the “whoosh” as I learn to fly again.

And rest assured, that wax ring in the downstairs toilet?  Nailed it.

With Modern Storage the Oracle Buffer Cache is Not So Important.

With Oracle’s move towards engineered systems we all know that “more” is being done down at the storage layer and modern storage arrays have hundreds of spindles and massive caches. Does it really matter if data is kept in the Database Buffer Cache anymore?

Yes. Yes it does.

Time for a cool beer

Time for a cool beer

With much larger data sets and the still-real issue of less disk spindles per GB of data, the Oracle database buffer cache is not so important as it was. It is even more important.

I could give you some figures but let’s put this in a context most of us can easily understand.

You are sitting in the living room and you want a beer. You are the oracle database, the beer is the block you want. Going to the fridge in the kitchen to get your beer is like you going to the Buffer Cache to get your block.

It takes 5 seconds to get to the fridge, 2 seconds to pop it open with the always-to-hand bottle opener and 5 seconds to get back to your chair. 12 seconds in total. Ahhhhh, beer!!!!

But – what if there is no beer in the fridge? The block is not in the cache. So now you have to get your car keys, open the garage, get the car out and drive to the shop to get your beer. And then come back, pop the beer in the fridge for half an hour and now you can drink it. That is like going to storage to get your block. It is that much slower.

It is only that much slower if you live 6 hours drive from your beer shop. Think taking the scenic route from New York to Washington DC.

The difference in speed really is that large. If your data happens to be in the memory cache in the storage array, that’s like the beer already being in a fridge – in that shop 6 hours away. Your storage is SSD-based? OK, you’ve moved house to Philadelphia, 2 hours closer.

Let's go get beer from the shop

Let’s go get beer from the shop

To back this up, some rough (and I mean really rough) figures. Access time to memory is measured in Microseconds (“us” – millionths of a second) to hundreds of Nanoseconds (“ns” – billionths of a second). Somewhere around 500ns seems to be an acceptable figure. Access to disc storage is more like Milliseconds (“ms” – thousandths of a second). Go check an AWR report or statspack or OEM or whatever you use, you will see that db file scattered reads are anywhere from low teens to say 2 or 3 ms, depending on what your storage and network is. For most sites, that speed has hardly altered in years as, though hard discs get bigger, they have not got much faster – and often you end up with fewer spindles holding your data as you get allocated space not spindles from storage (and the total sustainable speed of hard disc storage is limited to the total speed of all the spindles involved). Oh, the storage guys tell you that your data is spread over all those spindles? So is the data for every system then, you have maximum contention.

However, memory speed has increased over that time, and so has CPU speed (though CPU speed has really stopped improving now, it is more down to More CPUs).

Even allowing for latching and pinning and messing around, accessing a block in memory is going to be at the very least 1,000 times faster than going to disc, maybe 10,000 times. Sticking to a conservative 2,000 times faster for memory than disc , that 12 seconds trip to the fridge equates to 24,000 seconds driving. That’s 6.66 hours.

This is why you want to avoid physical IO in your database if you possibly can. You want to maximise the use of the database buffer cache as much as you can, even with all the new Exadata-like tricks. If you can’t keep all your working data in memory, in the database buffer cache (or in-memory or use the results cache) then you will have to do that achingly slow physical IO and then the intelligence-at-the-hardware comes into it’s own, true Data Warehouse territory.

So the take-home message is – avoid physical IO, design your database and apps to keep as much as you can in the database buffer cache. That way your beer is always to hand.

Cheers.

Update. Kevin Fries commented to mention this wonderful little latency table. Thanks Kevin.

“Here’s something I’ve used before in a presentation. It’s from Brendan Gregg’s book – Systems Performance: Enterprise and the Cloud”

Lab Report: Oracle Database on EMC XtremIO. A Compression Technology Case Study.

If you are interested in array-level data reduction services and how such technology mixes with Oracle Database application-level compression (such as Advanced Compression Option), I offer the link below to an EMC Lab Report on this very topic.

To read the entire Lab Report please click the following link:   Click Here.

The following is an excerpt from the Lab Report:

Executive Summary
EMC XtremIO storage array offers powerful data reduction features. In addition to thin provisioning, XtremIO applies both deduplication and compression algorithms to blocks of data when they are ingested into the array. These features are always on and intrinsic to the array. There is no added licensing, no tuning nor configuration involved when it comes to XtremIO data reduction.

Oracle Database also supports compression. The most common form of Oracle Database compression is the Advanced Compression Option—commonly referred to as ACO. With Oracle Database most “options” are separately licensed features and ACO is one such option. As of the publication date of this Lab Report, ACO is licensed at $11,000 per processor core on the database host1. Compressing Oracle Database blocks with ACO can offer benefits beyond simple storage savings. Blocks compressed with ACO remain compressed as they pass through the database host. In short, blocks compressed with ACO will hold more rows of data per block. This can be either a blessing or a curse. Allowing Oracle to store more rows per block has the positive benefit of caching more application data in main memory (i.e., the Oracle SGA buffer pool). On the other hand, compacting more data into each block often results in increased block-contention.

Oracle offers tuning advice to address this contention in My Oracle Support note 1223705.12. However, the tuning recommendations for reducing block contention with ACO also lower the compression ratios. Oracle also warns users to expect higher CPU overhead with ACO as per the following statement in the Oracle Database product documentation:

Compression technology uses CPU. Ensure that you have enough available CPU to handle the additional load.

Application vendors, such as SAP, also produce literature to further assist database administrators in making sensible choices about how and when to employ Advanced Compression Option. The importance of understanding the possible performance impact of ACO are made quite clear in such publications as SAP Note 14363524 which states the following about SAP performance with ACO:

Overall system throughput is not negatively impacted and may improve. Should you experience very long runtimes (i.e. 5-10 times slower) for certain operations (like mass inserts in BW PSA or ODS tables/partitions) then you should set the event 10447 level 50 in the spfile/init.ora. This will reduce the overhead for insertion into compressed tables/partitions.

The SAP note offers further words of caution regarding transaction logging (a.k.a., redo) in the following quote:

Amount of redo data generated can be up to 30% higher

Oracle Database Administrators, with prior ACO experience, are largely aware of the trade-offs where ACO is concerned. Database Administrators who have customarily used ACO in their Oracle Database deployments may wish to continue to use ACO after adopting EMC XtremIO. For this reason Database Administrators are interested in learning how XtremIO compression and Advanced Compression Option interact.

This Lab Report offers an analysis of space savings with and without ACO on XtremIO. In addition, a performance characterization of an OLTP workload manipulating the same application data in ACO and non-ACO tablespaces will be covered…please click the link above to continue reading…

 

Filed under: oracle

Smart Scan enhancements in Oracle Exadata 12c-inline LOBs

As part of the 12c release of the database and cellos Oracle introduced the ability to perform Smart Scans on inline LOBs. The enhancement is certainly for Secure Files only. And as always, if you would like to read up about Secure Files you can head over to Tim Hall’s website for the details.

To demonstrate the new behaviour I have used an 11.2.0.3 BP 22 database on Exadata 11.2.3.3.1 and 12.1.0.2.2 database on Exadata 12.1.2.1.0. The test case evolves around the following table:

CREATE TABLE loboffload (
 id number primary key,
 comments clob)
enable row movement 
tablespace users
lob (comments) store as securefile (
 enable storage in row
);

This is the short version, Oracle fills in the blanks and converts the table DDL to

SQL> select dbms_metadata.get_ddl('TABLE','LOBOFFLOAD') from dual;

DBMS_METADATA.GET_DDL('TABLE','LOBOFFLOAD')
--------------------------------------------------------------------------------

  CREATE TABLE "MARTIN"."LOBOFFLOAD"
   (    "ID" NUMBER,
        "COMMENTS" CLOB,
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 LOB ("COMMENTS") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))  ENABLE ROW
 MOVEMENT

The idea is that I am using the comments lob column to simulate a common approach in application design where you define a free text field on a GUI screen for the user to provide additional information about the record to be stored in the database. The design is not very practical though.

The important information is in the section about the LOB. Here you find the request to store the LOB as a SECUREFILE in-line with the table. Out of line LOBs are not offloadable as of 12.1.2.1.0 as far as I know.

To feed some data into the table I copied added a bit of data and then used the insert … append technique to populate it:

alter session enable parallel dml;

-- with nods to Jonathan Lewis who published this in one of his presentations on generating data
insert /*+ append parallel(4) */ into loboffload
with v1 as (
 SELECT rownum n FROM dual CONNECT BY level <= 10000
)
SELECT  rownum id,
  CASE
    WHEN mod(rownum,100000) = 0     THEN 'THIS WAS EXCEPTIONAL' 
    WHEN mod(rownum,10000) = 0      THEN 'THIS WAS VERY GOOD'
    WHEN mod(rownum,1000) = 0       THEN 'THIS WAS GOOD'
    WHEN mod(rownum,100) = 0        THEN 'OK'
    ELSE 'NO COMPLAINTS'
  END 
FROM v1,
  v1
WHERE rownum <= 1e6;

create sequence loboffload_s cache 10000 start with 1000001;


insert /*+ append parallel(4) */ into loboffload
select /*+ parallel(4) */ loboffload_s.nextval, comments from loboffload;

I ended up with 16 million rows in the end. I used impdp over a network link to move the table to the other database.

Test with 11.2.0.3

The test in 11.2.0.3 is used to confirm that inline LOBs are not offloadable. Here is the query and result:

MARTIN@ora11203> select /*+ monitor noparallel full(t) */ count(*) from loboffload t where comments like '%GOOD%';

  COUNT(*)
----------
     15840

Elapsed: 00:01:33.48

MARTIN@ora11203> -- removing irrelevant statistics from the output
MARTIN@ora11203> @mystats stop t=1
==========================================================================================
MyStats report : 25-MAY-2015 02:33:24
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                  95.28
TIMER   CPU time used (seconds)                                                      93.00


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    CPU used by this session                                                     9,303
...
STAT    DB time                                                                      9,353
...
STAT    cell flash cache read hits                                                   9,454
STAT    cell physical IO interconnect bytes                                  1,233,436,672
...
STAT    consistent gets                                                            150,794
STAT    consistent gets - examination                                                    3
STAT    consistent gets direct                                                     150,558
STAT    consistent gets from cache                                                     236
STAT    consistent gets from cache (fastpath)                                          231
STAT    cursor authentications                                                           3
...
STAT    execute count                                                                   13
...
STAT    logical read bytes from cache                                           14,393,344
STAT    no work - consistent read gets                                             150,558
...
STAT    parse count (hard)                                                               1
STAT    parse count (total)                                                             13
STAT    parse time cpu                                                                   1
STAT    parse time elapsed                                                               1
STAT    physical read IO requests                                                    9,459
STAT    physical read bytes                                                  1,233,436,672
STAT    physical read requests optimized                                             9,454
STAT    physical read total IO requests                                              9,459
STAT    physical read total bytes                                            1,233,436,672
STAT    physical read total bytes optimized                                  1,233,395,712
STAT    physical read total multi block requests                                     9,255
STAT    physical reads                                                             150,566
STAT    physical reads cache                                                             8
STAT    physical reads direct                                                      150,558
...
STAT    table scan blocks gotten                                                   150,558
STAT    table scan rows gotten                                                  16,000,000
STAT    table scans (direct read)                                                        1
STAT    table scans (long tables)                                                        1
...

------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis

==========================================================================================
End of report
==========================================================================================

So none of the essential cell% events recorded except for Flash Cache read hits. Without the cell scans statistics incremented by 1 (for the table segment) you can conclude that no Smart Scan happened here.

Test on 12.1.0.2/12.1.2.1.0

The first result was not too surprising, as it is the documented behaviour. The second test should hopefully reveal some offloading.

MARTIN@ORA12c> select /*+ monitor noparallel full(t) */ count(*) from loboffload t where comments like '%GOOD%';

  COUNT(*)
----------
     15840

Elapsed: 00:00:01.65

MARTIN@ORA12c> @mystats stop t=1
==========================================================================================
MyStats report : 25-MAY-2015 02:29:46
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                  14.61
TIMER   CPU time used (seconds)                                                       0.03


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
STAT    CPU used by this session                                                         9
...
STAT    cell IO uncompressed bytes                                           1,234,296,832
STAT    cell blocks helped by minscn optimization                                  150,666
STAT    cell blocks processed by cache layer                                       150,671
STAT    cell blocks processed by data layer                                        150,671
STAT    cell blocks processed by txn layer                                         150,671
STAT    cell flash cache read hits                                                   1,143
STAT    cell num smartio automem buffer allocation attempts                              1
STAT    cell physical IO bytes eligible for predicate offload                1,234,296,832
STAT    cell physical IO interconnect bytes                                      6,273,368
STAT    cell physical IO interconnect bytes returned by smart scan               6,273,368
STAT    cell scans                                                                       1
...
STAT    consistent gets                                                            151,053
STAT    consistent gets direct                                                     150,671
STAT    consistent gets examination                                                      3
STAT    consistent gets examination (fastpath)                                           3
STAT    consistent gets from cache                                                     382
STAT    consistent gets pin                                                            379
STAT    consistent gets pin (fastpath)                                                 379
...
STAT    parse count (total)                                                             13
STAT    physical read IO requests                                                    1,205
STAT    physical read bytes                                                  1,234,296,832
STAT    physical read requests optimized                                             1,143
STAT    physical read total IO requests                                              1,205
STAT    physical read total bytes                                            1,234,296,832
STAT    physical read total bytes optimized                                  1,171,423,232
STAT    physical read total multi block requests                                     1,189
STAT    physical reads                                                             150,671
STAT    physical reads direct                                                      150,671
...
STAT    table scan blocks gotten                                                    15,744
STAT    table scan disk non-IMC rows gotten                                      1,670,692
STAT    table scan rows gotten                                                   1,670,692
STAT    table scans (direct read)                                                        1
STAT    table scans (short tables)                                                       1
...

------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis

==========================================================================================
End of report
==========================================================================================

Good news, what Oracle said is true. The same query that did not offload in 11.2.0.3 is now offloaded in 12.1.0.2 with Exadata 12.1.2.1.0. You find proof in the existence of all the cell% statistics, especially cell scans plus the … processed by … layer statistic.

Temp Table Transformation Cardinality Estimates - 1

Having published recently two notes about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:


create table t1
as
select
rownum as id
, mod(rownum, 10) + 1 as id2
, rpad('x', 100) as filler
from
dual
connect by
level <= 1000
;

exec dbms_stats.gather_table_stats(null, 't1')

alter session set tracefile_identifier = 'temp_trans_join_card';

alter session set events '10053 trace name context forever, level 1';

explain plan for
with
cte as (
select /* inline */ id + 1 as id from t1 t
where 1 = 1
)
select /*+
--opt_estimate(@"SEL$2" join("A"@"SEL$2" "B"@"SEL$2") rows=1000)
no_merge(a) no_merge(b)
*/ * from cte a, cte b
where a.id = b.id
;

alter session set events '10053 trace name context off';

-- 11.2.0.x Plan with TEMP transformation
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 |
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660C_27269C | | |
| 3 | TABLE ACCESS FULL | T1 | 1000 | 4000 |
|* 4 | HASH JOIN | | 1 | 26 |
| 5 | VIEW | | 1000 | 13000 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_27269C | 1000 | 4000 |
| 7 | VIEW | | 1000 | 13000 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_27269C | 1000 | 4000 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."ID"="B"."ID")

-- 11.2.0.x Plan with INLINE hint
----------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K|
|* 1 | HASH JOIN | | 10000 | 253K|
| 2 | VIEW | | 1000 | 13000 |
| 3 | TABLE ACCESS FULL| T1 | 1000 | 4000 |
| 4 | VIEW | | 1000 | 13000 |
| 5 | TABLE ACCESS FULL| T1 | 1000 | 4000 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"="B"."ID")

the following becomes obvious:- There are vastly different cardinality estimates possible depending on whether the transformation gets used or not. In principle due to the NO_MERGE hints used, the transformation shouldn't have any impact on the estimates, but it does- Looking at the optimizer trace file some information seems to get lost when the transformation gets used, in particular column related statistics- This information loss, like in the example here, can lead to join cardinality estimates of 0 (rounded to 1 in the plan output)- And even worse, at least in pre-12c versions, such a 0 cardinality estimate can't be corrected via OPT_ESTIMATE hints, since somehow the correction gets ignored/lost although being mentioned in the optimizer trace:


11.2.0.1:
Column (#1): ID(
AvgLen: 22 NDV: 1 Nulls: 0 Density: 0.000000
Column (#1): ID(
AvgLen: 22 NDV: 1 Nulls: 0 Density: 0.000000
Join Card: 0.000000 = = outer (1000.000000) * inner (1000.000000) * sel (0.000000)
>> Join Card adjusted from 0.000000 to: 1000.000000, prelen=2
Adjusted Join Cards: adjRatio=1.00 cardHjSmj=0.000000 cardHjSmjNPF=0.000000 cardNlj=0.000000 cardNSQ=1000.000000 cardNSQ_na=0.000000
Join Card - Rounded: 1 Computed: 0.00

The behaviour regarding the OPT_ESTIMATE hint changes in 12c, but then there are other oddities introduced in 12c that are not there in pre-12c - have a look at the "Query Block" section when using the INLINE variant of the query - there are two identical fully qualified object names, clearly a bug, making hinting using global hint syntax impossible for that query block.Although my simple example here can be corrected via extended statistics on the join column expression used in the CTE query my point here is that depending on whether the transformation gets used or not vastly different and extreme cardinality estimates are possible - and those extreme cases even can't be corrected in pre-12c versions.For example I recently had a real life case where two columns were joined that had a significant number of NULL values, one coming from a temp table transformation row source. Without the transformation the join cardinality estimates were reasonable, but the transformation again lead to such a 0 cardinality estimate (that couldn't be corrected via a (correctly specified) OPT_ESTIMATE hint), ruining the whole plan.

Datapump TTS quick tip

I was trying to transport a large number of tablespace datafiles from one database to another, and impdp kept crashing with “PL/SQL: numeric or value error: character string buffer too small.”

It turns out there is a limit on what you can put on a single line in a parameter file.  Hence if you have something like:

transport_datafiles=/long/path/file1,/long/path/file2,/long/path/file3,....

then you might run into trouble. It’s easily fixed however – just put the entries one per line

transport_datafiles=/long/path/file1
transport_datafiles=/long/path/file2
transport_datafiles=/long/path/file3
....

Oracle 12c – PL/SQL Implicit Result Sets

Implicit Result Sets

Beginning with Oracle 12c it is possible for SELECT statements to send results back to a client from PL/SQL without need for REF CURSOR definitions. Previously, PL/SQL required definition of a REF CURSOR as an OUT parameter adding complexity (and precision) not found in other products (argh… the older I get the more I believe that a module should do exactly one thing (and do it well)). However, it is common in some database products to use a Stored Procedure to return multiple result sets.

Using Oracle 12c’s newly implemented implicit result sets will allow reuse of SQL Server (and other database) stored procedures that return implicit result sets. Implicit result sets were originally targeted at JDBC clients but will probably work well in other environments like C#.NET or SQL Server TSQL.

Oracle 12c PL/SQL’s DBMS_SQL.RETURN_RESULT procedure makes result cursors available to the client easily (procedures do not need out parameter for procedure to return cursor results). The general process is as follows:

  • In PL/SQL Procedure
    • Open cursorname FOR SELECT
    • Call DBMS_SQL.SQL_RETURN_RESULT(cursorname)
  • In Java JDBC
    • Call stored procedure returning implicit result sets
    • Locate next result set from procedure with getMoreResults() or getMoreResults(nn)
    • Retrieve result set using getResultSet()
  • In PL/SQL
    • Supported in PL/SQL with DBMS_SQL.GET_NEXT_RESULT

Example PL/SQL Procedure using Implicit Result Set

The procedure below creates two implicit result sets that may be read by client software such as Java, C#, TSQL, or PL/SQL:

create or replace PROCEDURE MY_IMPLICIT_TABLES
(in_deptid in emp.deptno%type)
as
mycursor sys_refcursor;
mycursor2 sys_refcursor;
begin
if in_deptid is not null
then
open mycursor for
select empno,ename,job,sal
from emp
where deptno = in_deptid
order by ename;
 dbms_sql.return_result(mycursor);
/* now for 2nd cursor */
open mycursor2 for
select employee_id,last_name,job_id,salary
from hr.employees
where department_id = in_deptid
order by last_name;
dbms_sql.return_result(mycursor2);
end if;
END MY_IMPLICIT_TABLES;

 The following JDBC code calls the stored procedure, then processes each of the result sets returned. Note that returning two or more result sets means that the client-side code must have some intelligence about how to process each result set in turn; this is pretty fragile design so be careful.

The resulting output from the sample Java program is shown below:

package samples;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;

/** Sample use of Oracle Procedure with Implicit Results
*

* Note: Exception handling omitted to simplify example
*  *
* @version 1.01 04/01/2015
* @author  John Jay King
* */
public class OraThinJDBCImplicitResults {
public OraThinJDBCImplicitResults() throws SQLException  {
// Load the Oracle JDBC driver — not needed for JDBC 4 Direct Connection
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

// Direct connection to database; “real applications” should
// probably use a Connection Pool for best system performance
Connection conn =
DriverManager.getConnection (“jdbc:oracle:thin:@192.168.0.61:1539/pdb1.localdomain”,
“john”, “king”);

// Create a Statement
CallableStatement stmt1 = conn.prepareCall(“{CALL my_implicit_tables(?)}”);
ResultSet rs1 = null;
int rsetCount = 0;
stmt1.setInt(1,20);
stmt1.executeQuery();
while (stmt1.getMoreResults()) {
System.out.println(“Processing Data From Result Set ” + ++rsetCount);
rs1= stmt1.getResultSet();
processResults(rs1);
}

if (rs1 != null) {
rs1.close();
}
if (stmt1 != null) {
stmt1.close();
}
if (conn != null) {
conn.close();
}
}

private void processResults(ResultSet rset) throws SQLException {
ResultSetMetaData myRSMetaData = rset.getMetaData();
System.out.println(“ResultSet”);
System.out.println(“\tColumn Count:” + myRSMetaData.getColumnCount());
int ctr = 0;
int coltype;
int colcount = myRSMetaData.getColumnCount();
while (rset.next()) {
for (ctr = 1; ctr<(colcount+1); ctr++) {
System.out.println(“\t** Column ” + ctr + ” ****************”);
System.out.println(“\t\tName: ” + myRSMetaData.getColumnName(ctr));
System.out.println(“\t\tColtype: ” + myRSMetaData.getColumnType(ctr));
coltype = myRSMetaData.getColumnType(ctr);
System.out.print(“\t\tValue is “);
switch (coltype) {
case     Types.VARCHAR :
System.out.println(rset.getString(ctr));
break;
case     Types.DOUBLE :
System.out.println(rset.getDouble(ctr));
break;
case     Types.INTEGER:
System.out.println(rset.getInt(ctr));
break;
case     Types.NUMERIC:
System.out.println(rset.getDouble(ctr));
break;
case Types.TIMESTAMP:
System.out.println(rset.getDate(ctr));
break;
default :
System.out.println(rset.getString(ctr));
}
}
}
}

public static void main (String args [])
throws SQLException
{
OraThinJDBCImplicitResults myir = new OraThinJDBCImplicitResults();

} // end main
} // end OraThinJDBCStoredProc

Processing Data From Result Set 1

ResultSet
Column Count:4
** Column 1 ****************
Name: EMPNO
Coltype: 2
Value is 7876.0
** Column 2 ****************
Name: ENAME
Coltype: 12
Value is ADAMS
** Column 3 ****************
Name: JOB
Coltype: 12
Value is CLERK
** Column 4 ****************
Name: SAL
Coltype: 2
Value is 1100.0
** Column 1 ****************
Name: EMPNO
Coltype: 2
Value is 7902.0
** Column 2 ****************
Name: ENAME
Coltype: 12
Value is FORD
** Column 3 ****************
Name: JOB
Coltype: 12
Value is ANALYST
** Column 4 ****************
Name: SAL
Coltype: 2
Value is 3000.0
** Column 1 ****************
Name: EMPNO
Coltype: 2
Value is 7566.0
** Column 2 ****************
Name: ENAME
Coltype: 12
Value is JONES
** Column 3 ****************
Name: JOB
Coltype: 12
Value is MANAGER
** Column 4 ****************
Name: SAL
Coltype: 2
Value is 2975.0
v** Column 1 ****************
Name: EMPNO
Coltype: 2
Value is 7788.0
** Column 2 ****************
Name: ENAME
Coltype: 12
Value is SCOTT
** Column 3 ****************
Name: JOB
Coltype: 12
Value is ANALYST
** Column 4 ****************
Name: SAL
Coltype: 2
Value is 3000.0
** Column 1 ****************
Name: EMPNO
Coltype: 2
Value is 7369.0
** Column 2 ****************
Name: ENAME
Coltype: 12
Value is SMITH
** Column 3 ****************
Name: JOB
Coltype: 12
Value is CLERK
** Column 4 ****************
Name: SAL
Coltype: 2
Value is 800.0

Processing Data From Result Set 2

 ResultSet
Column Count:4
** Column 1 ****************
Name: EMPLOYEE_ID
Coltype: 2
Value is 202.0
** Column 2 ****************
Name: LAST_NAME
Coltype: 12
Value is Fay
** Column 3 ****************
Name: JOB_ID
Coltype: 12
Value is MK_REP
** Column 4 ****************
Name: SALARY
Coltype: 2
Value is 6000.0
** Column 1 ****************
Name: EMPLOYEE_ID
Coltype: 2
Value is 201.0
** Column 2 ****************
Name: LAST_NAME
Coltype: 12
Value is Hartstein
** Column 3 ****************
Name: JOB_ID
Coltype: 12
Value is MK_MAN
** Column 4 ****************
Name: SALARY
Coltype: 2
Value is 13000.0

Oracle’s Implicit Result set capability will allow reuse of existing stored procedure designs (if desired); be prepared for the complexity of code needing to know about the number of result sets and their contents.

From Product X to SQL Developer

I recently worked at a company that used "Product X" for all of it SQL and PL/SQL activities.  There’s no real need to reveal what "Product X" is, because this isn’t a post about whether as a product it was good, bad or somewhere in between.  "Product X" met the needs of the Oracle developers in the company, and that’s all that matters.  There was just one issue – for Product X, the company was paying a little under $100k annually in license fees.

Enter SQL Developer.  Similarly, it’s not particularly relevant whether I think, or anyone else at the company thinks its better or worse than "Product X".  What does matter is – can it do the required tasks of the developers.  And the answer to that is a resounding yes.  And once that question is answered, then paying any kind of annual license fee for Product X becomes not an intelligent business choice.  So the organisation is in the process of moving to SQL Developer.  The trick now is how to enforce assist :-) the developers with moving to SQL Developer.  And the reality is – once you show developers how to re-map the shortcut keys in SQL Developer to match what they were used to in Product X, then the task is virtually done.

But some developers still clung to Product X…and hence were threatening a violation of a the no-longer current license agreement.  So the task then became – how to stop dissuade :-) them from still using Product X.  This post gives you a few ideas on how you might want to tackle it.

Version 1

I assumed the best of our developers :-) and put a simple login trigger to check if the program they’d connected with was "ProductX.exe".  If it was, then a job was submitted (via dbms_job) to pop an email to them to let them know that Product X was no longer the company standard. 

Version 2

But old habits die hard…and within a few days, some developers had renamed ProductX.exe to SQL Developer.exe and were back on the database thrilled with their ingenuity :-) So the trigger was adjusted to also check the MODULE, as ProductX also set its session module value to "Product X".  But, you can’t keep a good developer down, and with a little assistance from hex editor, the module was now cleared.

Version 3

Most development tools, when they connect to the database, run a series of queries to check things like privileges and the like.  So by tracing a connection by ProductX, a list of cursors that were executed could be identified.  So the login trigger was altered to always submit a job.  The job would check the program and module were checked as before, but it would also check V$OPEN_CURSOR for the new session. If it contained a selection of the statements that Product X would execute, then the trap has been sprung :-)

 

So Version 3 has been active for a couple of weeks now, and plenty of emails have been sent.  It will soon be amended to terminate connections, and thus, the migration to SQL Developer will be complete :-)

EM Express versus EM Cloud Control

Recently I was asked the question “What is the real difference between EM Cloud Control [NOTE: I’ll refer to this as EM12c through the rest of this post] and EM Database Express in 12c?” It was (for me) a pretty easy question to answer, but I wanted to provide the questioner with a link to the place in the Enterprise Manager documentation where it covers that in detail. Somewhat to my surprise, I wasn’t able to find such a link – well, not quickly anyway. I think the reason for that is the documentation for EM Express (as it’s more commonly abbreviated to) falls under the database documentation which is owned by a different group in Oracle than the Enterprise Manager documentation. Well, that’s my speculation anyway. It may just be there in the documentation and I couldn’t find it in my quick search. :) Be that as it may, if I couldn’t find it in a quick search then I suspect others would have the same issue, so I thought it was worthwhile to bash out a quick post on the subject. And of course, what was originally going to be a quick post turned into something much bigger, so if you want, use the links below to move through the post:

What is EM Express?
What is EM12c?
What are the Differences Between the Two?

What is EM Express?

Before I start on what the differences are, it’s probably worthwhile to spend a little time explaining what EM Express actually is. The first thing to note is what it replaces, and that’s the Database Control product. Database Control is no longer available as of Oracle Database 12c. In its place, the tool to monitor a single database is EM Express. EM Express – or to give it its full name, Oracle Enterprise Manager Database Express 12c (now you can see why it’s normally called just EM Express!) – is an integrated GUI management tool for database administration of a single Oracle Database 12c database. Let’s explain that in a bit more detail.

Integrated Installation

EM Express is preconfigured and installed when the database kernel is installed, so when you install your ORACLE_HOME, you have also installed EM Express. If you have used DBCA to create a database, one of the questions you will be asked is whether you want to manage this database with EM Express or EM12c. You can actually select both checkboxes, though I found the last time I did that (admittedly quite some time ago when Oracle Database 12c was relatively new) that the installer got a tad confused by selecting both and it was better to choose one and then set up the other later. EM Express runs inside the database and has no extra middleware components (this is one area of differentiation between EM Express and the earlier Database Control product). EM Express leverages the XDB server inside the database. It support both single instance and Real Application Clusters (RAC) databases.

Small Footprint

EM Express has a fairly small footprint in terms of disk storage, memory and CPU usage. The disk storage added to the kernel by EM Express is only around 20 Mb. It requires minimal CPU and memory – in fact, none until you actually connect to it. All the UI rendering is done in the browser being used, reducing the footprint even more, and the database server is only used to run the SQL needed to retrieve the information being displayed in the GUI.

Comprehensive Administration

OK, comprehensive may be optimistic. :) This is one area where the tool doesn’t quite measure up as yet compared to Database Control, in that EM Express provides only basic administration support for configuration management, storage management, and security management. No doubt as new releases of the tool come out, this coverage will expand. Currently, the tool provides the following functionality:

  • Configuration Management – initialization parameters, memory, database feature usage, and current database properties
  • Storage Management – tablespaces, undo management, redo log groups, archive logs, and control files
  • Security Management – users, roles, and profiles
  • Performance Management – Performance Hub and SQL Tuning Advisor

One area where EM Express far outstrips Database Control (and in fact, even surpasses similar areas in EM12c) is the performance side. EM Express has quite advanced performance diagnostics and tuning capabilities. However, you do need to be licensed for the Diagnostics and Tuning packs to use these pages.

You can find a little bit more on managing Oracle Database 12c with EM Express in my presentation from the APAC OTN Tour back in 2013, located here.

What is Enterprise Manager Cloud Control 12c?

OK, so that’s what EM Express handles. Now let’s have a quick overview of the EM12c product.

To start with, EM12c is more of a product line or product family than a single product, but it still allows you to manage your entire Oracle infrastructure from a single pane of glass, from application to disk. It even allows you to manage your non-Oracle infrastructure, with a variety of plugins and connectors to manage everything from non-Oracle databases (like SQL Server and DB2) to non-Oracle middleware (like WebSphere, Microsoft Active Directory and JBoss Application Server) and so on. And if there’s something it doesn’t yet have a plugin or connector for, you can use the Extensibility capabilities to write your own.

If you look at the key capabilities of EM12c, it really boils down to three main areas:

  • Cloud Lifecycle Management – EM12c helps enterprises discover their IT assets and plan their cloud by providing consolidation recommendations, and has a guided cloud setup wizard to help you define services such as Infrastructure as a Service, Middleware as a Service, and Database as a Service. With later releases, you also have Schema as a Service, Pluggable Database as a Service, Testing as a Service, and Snap Clone functionality. It also includes an out of the box self service portal capability, along with API’s that can be used to create your own self service portal. Finally, it has chargeback capability that allows you to provide business users a comprehensive report of how much computing resources they are using, and even to invoice them for the costs of those resources if you want to.
  • Complete Application to Disk Management – EM12c can be used to monitor and manage every layer of your infrastructure from the application all the way down to the storage, including:
    • Application Management – EM12c provides application management solution for packaged, third party and custom applications, including E-Business Suite, Siebel, PeopeSoft, JD Edwards, Fusion Apps, and Java or SOA based custom and third party apps.
    • Middleware Management – EM12c also manages the many Fusion Middleware components, such as WebLogic Server, SOA Suits, Coherence, Identity Management, Business Intelligence, Glass Fish, Jolt and WebCenter
    • Databases Management – this is the area EM12c has been well known for over many years. Indeed, the first version of Enterprise Manager I ever saw, the 0.76 beta release, already contained a lot of database management capabilities. Obviously, those have all expanded over the years as the database capabilities also have, but now includes additional functionality on top of the native database capabilities in database lifecycle management areas like automating mass database upgrades and patching.
    • Hardware and Virtualization Management – Traditionally, Enterprise Manager has been focused on software management. EM12c expands that to provide management capabilities like provisioning, patching, monitoring, administration and configuration management for both hardware and virtualization layers too. This is particularly true in the case of Oracle’s Sun hardware line.
    • Heterogeneous Management – Even though Oracle’s footprint has been expanding into so many different areas over the years, there are still going to be some non-Oracle products at most customer sites that need managing as well. Oracle has a number of plugins and management connectors that can be used to monitor and manage these third-party products. EM12c also includes an Extensibility Development Kit (EDK) to allow you to write your own management plugins. Many of these are available for other customers to use via the Extensibility Exchange.
    • Engineered Systems Management – EM12c is tightly integrated with a range of Oracle’s Engineered Systems, such as Exadata, Exalogic, Exalytics, SuperCluster, Virtual Compute Appliance (VCA), Big Data Appliance, and Oracle Database Appliance (ODA). That list just keeps expanding as more engineered systems are released, so customers can take advantage of integrated hardware, software and management capabilities that are engineered together to form a complete solution.
    • Application Quality Management – EM’s AQM products provide an end-to-end testing solution, including Application Testing Suite (OATS) which has test management, functional testing and load testing capabilities, Application Replay and Real Application Testing to provide production-scale testing of the application and database infrastructure, and test data management capabilities like data masking and subsetting
    • Application Performance Management – APM delivers business driven application management with end to end monitoring that includes user experience management (which includes both real user monitoring via RUEI and synthetic transaction monitoring via SLM beacons), business transaction management (which allows you to monitor and trace transactions from a business perspective), Java and database monitoring and diagnostics, and application performance analytics.
  • Enterprise-grade management – Not only is it important that you have the ability to manage your Oracle infrastructure with EM12c, but it’s also important that EM12c itself can support mission critical workloads. Way back when it really didn’t matter most of the time if your management tool went down every so often, but now people are realizing it’s as important to have mission critical management for your mission critical workloads. It’s not unusual to find Enterprise Manager installations protected by RAC databases with Data Guard as well, along with software load balancers and the whole enchilada. EM12c has been thoroughly tested for scalability (see here for one of my very early posts on this, and just recently we saw that very same environment hit 2 million targets!). EM12c’s Self Update framework ensures you can easily incorporate management of the latest and greatest products from Oracle. And in addition, the security model introduced with EM12c allows tightly controlled access to the EM12c product itself. So all of these points allow you to be sure EM12c can manage the needs of even the largest environments
  • So What are the Differences Between the Two?

    Now you know what the two products are capable of, you can probably pick out some of the differences. :) But let’s be 100% clear on these.

    The most obvious difference is that EM Express is a management tool that manages a single database, while EM12c manages your entire Oracle (and non-Oracle) data center. If all you are needing to manage is a few Oracle databases, then EM Express will be more than capable of meeting your needs. You will have to connect to each database separately to manage it though. However, as soon as you need to manage more than a few databases, or you want to manage more than just databases (i.e. you want to manage middleware, apps and so on), EM12c is your tool of choice. We have customers with literally thousands of database targets they need to manage. Try doing that with EM Express! :)

    The second difference is that EM Express is an agentless architecture. Everything you need to connect to EM Express is built into the database kernel. You just need to point a browser to the correct page, and away you go. EM12c, on the other hand, uses an agent to both discover and then manage targets. For each host that you add to an EM12c environment, you add an agent to that host and it then discovers and promotes all the targets (such as databases, listeners, WLS environments and so on) that are on that host. When you need to perform a task on those targets (such as shutting down a database), EM12c communicates with the agent on the host containing that database and it sends the message to the database to shut down. If you think about it, using the agents like this is really the only way to ensure a scalable solution in large environments.

    The third difference is functionality. As I mentioned above, EM Express provides a specific set of functionality, and if you want more you need to step outside of EM Express to either a tool like SQL Developer (I haven’t touched on that here because the article would be even longer, but if you want more on that go and visit Jeff Smith’s site for more details) or EM12c. Just a couple of examples – metric extensions, Data Guard management, and chargeback are all only available from EM12c rather than EM Express.

    The fourth difference is security. To log in to EM Express, you need to be a user that has the DBA, EM_EXPRESS_BASIC or EM_EXPRESS_ALL roles. The EM_EXPRESS_BASIC role provides read-only access to EM Express, so users with that role can view the UI but not make any changes. This role also includes the SELECT_CATALOG_ROLE role. The EM_EXPRESS_ALL role obviously grants a lot more access. :) It has full read / write access to all EM Express features, and includes the EM_EXPRESS_BASIC role. Compare that to the security model for EM12c, where there are quite a number of roles and over 100 fine-grained privileges, and you can see that EM12c has a much more granular security model.

    There are probably other more minor differences between the products that I haven’t covered here, but to my mind these are the main differences. Hope that helps explain the differences between the two!