Search

OakieTags

Who's online

There are currently 0 users and 21 guests online.

Recent comments

Oakies Blog Aggregator

Why Automate Target Patching with Enterprise Manager 12c

Every job comes with tasks that no one likes to perform and database administration is no exception.  Patching is one of those necessary tasks that must be performed and when we are expected to do more with less everyday, the demands of patching another host, another agent, another application is often a task that no one looks forward to.  It’s not that it goes wrong, but that it’s just tedious and many DBAs know there are a lot of other tasks that could be better use of their time.   Patching is still an essential and important task that must be performed, we all know that. OPatch and other patching utilities from Oracle make patching easy, but it can still remove a lot of time from a resource’s day.

Enterprise Manager 12c’s automated patching and provisioning, using the Database Lifecycle Management Pack is gaining more appreciation from the IT community as it assists the DBA with features to search recommended patches, create patch plans, review for conflicts and allow sharing and re-use of patch plans.

Configuring a Database for Online or Offline Patching

After logging into a target database, you can click on Setup and go to the Offline Patching setup:

patching22

You can then choose to use Online patching with MOS credentials:

patching1

or use Offline Credentials and configure the patching catalog and ensure you upload all the XML’s for the catalog, which will now be stored locally to a workstation.  Once the upload is complete, run the Refresh From My Oracle Support job.

patching2

The Online configuration is recommended and works with the software library.  It’s what we’ll be talking about today.

Also ensure that you’ve set up correct privileges to perform patching. Provisioning and patching require steps to be performed that will require privileges to run root scripts, so ensure that the credentials that are used for the patching allow to sudo to root or PBrun.

Database Patch Plans

To set up a patch plan for a database, there are a number of steps, but the patch plan wizard makes this very easy to do.  For our example, we’ll choose to patch 11.2.0.4 databases to the latest recommended patches.

First, let’s do a search to find out what patches we’ll need to apply to our 11.2.0.4 databases in our EM environment.

patching3

Our Enterprise menu takes us to the Provisioning and Patching, Patches and Updates.

From this console page, we can view what patch plans are already created in case we can reuse one:

patching4

As there isn’t an existing plan that fits what we need to do, we are going to first search for what patches are recommended with the Recommended Patch Advisor:

patching10

We’ve chosen to perform a search for recommended patches for 11.2.0.4.0 databases on Linux x86-64.  This will return the following four patches:

patching11

We can click on the first Patch Name, which will take us to the patch information, including what bugs are addressed in this patch, along with the option to download or create a patch plan.  For the purpose of this post, we’ll choose to create a patch plan:

patching12

We’ll create a new patch plan for this, as our existing ones currently do not include an 11g database patch plan that would be feasible to add to.  We can see our list of patches on the left, too, so this helps as we proceed to build onto our patch plans.

After clicking on the Add to New, we come to the following:

patching13

Name your patch plan something meaningful, (I choose to name the patch for a single instance, “SI”, the patch number and that it’s for 11.2.0.4) and then choose the database from the list you wish to apply the patch to.  You can hold down the CTRL key and choose more than one database and when finished, click on Create Plan.

The patch plan wizard will then check to see if any other targets monitored by Cloud Control will be impacted and asks you to either add them to the patch plan or to cancel the patch plan for further investigation:

patching14

If you are satisfied to with the additions, you can click on Add All to Plan to proceed.  The wizard then checks for any conflicts by the additions and will report them:

patching15

In our example above, I’ve added an 11.2.0.3 instance home to show that the wizard notes it and offers to either ignore the warnings and add it or (more appropriately) cancel the patch plan and correct the mistake.

Adding to Patch Plans

In our recommended patch list, we had four recommended patches.  Once we’ve created our first patch plan, we can now choose to add to it with the subsequent patches from the list:

patching16

This allows us to create one patch plan for all four patches and EM will apply them in the proper order as part of the patch deployment process.

Patch Plan Review and Deploy

One a patch plan is created, the next step is to review and deploy it.  Choose the patch plan from the list that we created earlier:

patching18

Double clicking on it will bring up the validation warning if any exist:

patching17

We can then analyze the validations required and correct any open issues as we review the patch plan and correct them before deploying:

patching29

We can see in the above checks, that we are missing credentials required for our patches to be successful.  These can now be set by clicking to the right of the “Not Set” and proceed with the review of our patch plan.

patching20

Next we add any special scripts that are required, (none here…) any notification on the patching process so we aren’t in the dark while the patch is being applied, rollback options and conflicts checks.

These steps give the database administrator a true sense of comfort that allows them to automate, yet have notifications and options that they would choose if they were running the patch interactively.

Once satisfied with the plan, choose the Deploy button and your patch is now ready to scheduled.

patching21

Once the patching job completes or if it experiences an issue and results in executing the logic placed in the above conflict/rollback steps, the DBA can view the output log to see what issues have occurred before correcting and rescheduling.

Output Log 
Step is being run by operating system user : 'ptch_em_user' 
 
Run privilege of the step is : Normal  

This is Provisioning Executor Script
…
Directive Type is SUB_Perl
…
The output of the directive is:
…
Tue Jan 6 00:15:40 2015 - Found the metadata files; '19121551' is an patch
…
Tue Jan 6 00:15:40 2015 - OPatch from '/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch.pl' 
  will be used to apply the Interim Patch.
…
Tue Jan 6 00:15:52 2015 - Invoking OPatch 11.2.0.4.7
…
Following patches will be rolled back from Oracle Home on application of the patches in the given list :
   4612895
…
Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
OPatch continues with these patches:  6458921  

Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y

Running prerequisite checks...

This is high level, but really, it’s quite easy and the more you automate provisioning and patching, the easier it’ll get and you’ll wonder why you waited so long!

 

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Why Automate Target Patching with Enterprise Manager 12c], All Right Reserved. 2015.

Free Version of Delphix !

Delphix is now available for  30 day trial direct download ! ( if you would like a longer version please contact me at kyle@delphix.com year trials and even indefinite trials  potential possible for partners, bloggers, Oracle Aces etc)

Just got to the

The Delphix download trial consists of 3 pre-configured virtual machines downloadable as OVA files

  • source machine with Oracle XE and Postgres  (1.3 GB)
  • Delphix engine (1.2 GB)
  • target machine with Oracle XE and Postgres binaries (1.9 GB)

Just startup the source, target and Delphix VM and you are ready to go.  After starting up the source, target and Delphix the lab will automatically link Delphix to the source databases. After a few minutes the source databases will show up in the the Delphix console in a browser. Access Delphix via the browser by simply typing in the IP address of Delphix. Once the sources are visible in the Delphix console you can start creating thin clones on the target machine. The thin clones only take a couple minutes to make and take up almost no space.

#555555;">
The Delphix download trial consists of 3 pre-configured virtual machines downloadable as OVA files

    #555555;">
  • source machine with Oracle XE and Postgres  databases (1.3 GB)
  • Delphix engine (1.2 GB)
  • target machine with Oracle XE and Postgres binaries (1.9 GB)

#555555;">Just startup the source, target and Delphix VM and you are ready to go.  After starting up the source, target and Delphix the lab will automatically link Delphix to the source databases. After a few minutes the source databases will show up in the the Delphix console in a browser. Access Delphix console by simply typing in the IP address of Delphix into a browser. Once the sources are visible in the Delphix console you can start creating thin clones, i.e. virtual databases, on the target machine. The thin clones only take a couple minutes to make and take up almost no space.

#555555;">Prerequisites on the machine where the lab is installed:

    #767676;">
  • Mac, Linux or windows #555555;">(laptop or desktop or workstation)
  • O/S virtualization #555555;"> either
    • Virtualbox or
    • VMware
      • Mac or Linux: VMware Fusion (free trial version #24890d;" title="download" href="https://www.google.com/url?q=https://www.vmware.com/products/fusion/fusion-evaluation.html&sa=D&sntz=1&usg=AFrqEzeedP_fYzx0peKjyOfLLQklcEOmuA" target="_blank">download)
      • Win: VMware Workstation (free trial version #24890d;" title="download" href="http://www.google.com/url?q=http://www.vmware.com/products/workstation/workstation-evaluation&sa=D&sntz=1&usg=AFrqEzfwURMexoK4vPN1mmmlLXYLY0GCSg" target="_blank">download)
  • at least 8 GB RAM
  • at least 50 GB free disk space, but preferably 100 GB free
  • at least 2 Ghz CPU, preferably dual-core or better

#555555;">There is a Vimeo channel for videos of the lab at   #990000;" href="https://vimeo.com/channels/landshark" target="_blank" rel="nofollow">https://vimeo.com/channels/landshark

  • #222222;">The “Quick install” for VMWare Workstation: #1155cc;" href="http://vimeo.com/channels/landshark/113624718" target="_blank">http://vimeo.com/channels/landshark/113624718
  • The “Quick install” for VMWare Fusion: #1155cc;" href="http://vimeo.com/channels/landshark/113627544" target="_blank">http://vimeo.com/channels/landshark/113627544
  • #222222;">The “Quick install” for VirtualBox: #1155cc;" href="http://vimeo.com/channels/landshark/113633016" target="_blank">http://vimeo.com/channels/landshark/113633016
  • #222222;">Lab exercise to provision a virtual application and virtual database: #1155cc;" href="http://vimeo.com/channels/landshark/113850959" target="_blank">http://vimeo.com/channels/landshark/113850959

For example the lab setup video for VMware Workstation:

Example of provisioning a virtual database and a virtual application

There is also a full online community where you can find answers and ask questions

#000000;"> #990000;" href="https://community.delphix.com/delphix/categories/delphix_landshark" target="_blank" rel="nofollow">https://community.delphix.com/delphix/categories/delphix_landshark

The Delphix demo lab is nicknamed “landshark” and the hands-on labs given at conferences is called “#CloneAttack”.

Get one-on-one help with installing and running the demo at the nearest Oracle conference to you that has a Clone Attack event such as RMOUG, Collaborate, UKOUG, DOAG, OUGN etc

#2970a6;" src="http://www.oraclerealworld.com/wp-content/uploads/2014/09/Screen-Shot-2014-09-24-at-9.41.34-AM-1024x658.png" alt="Screen Shot 2014-09-24 at 9.41.34 AM" width="464" height="299" />

Inappropriate behaviour

You pick up little funny things in the day to day with Oracle.

Like this one when you try to drop a tablespace with a queue table in it:

drop tablespace MY_TSPACE including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table
SCOTT.QUEUE_TABLE

You would think that since you’ve asked to drop everything, that well…everything could be dropped, but no :-)

The solution here is to use DBMS_AQADM.DROP_QUEUE_TABLE (with force=true if necessary) to clean it out, and the re-issue your command.

Data Pump import makes me crabby

I’m sitting here watching the import of a moderately sized database via transportable tablespaces.  You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast.

And fast it is.. until it reaches the following step:

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Now understandably, there’s plenty of stats to import, so its fair that it should take a little while.  But a quick look at the SQL that’s being run, reveals something truly hideous.  You’ll see a succession of giant PL/SQL blocks, chock full of literals and row-by-row (slow by slow) processing.  Things like this:

DECLARE   c varchar2(60)
   nv varchar2(1)
   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'
   s varchar2(60) := 'MY_SCHEMA'
   t varchar2(60) := 'MY_TABLE'
   p varchar2(60) := 'MY_PARTITION'
   sp varchar2(1)
   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,r1,r2,r3,flags,cl1) VALUES (:1,6,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24)'
BEGIN  
   DELETE FROM "SYS"."IMPDP_STATS"
   INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n9,n10,n11,n12,d1) VALUES ('T',6,2,t,p,sp,s,795877,2232,61,795877,0,NULL,NULL,NULL,TO_DATE('2015-01-11 01:13:44',df))  
   c := 'COL1'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,1639,2456710,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,4073,2456711,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,5520,2456712,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,6305,2456713,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,7183,2456714,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,8431,2456715,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,7,6.40253651379786E-07,7,9627,0,2456710,2456716,8,1,9627,2456716,0,TO_DATE('2015-01-11 01:13:44',df),'78720215010101','7872021B010101',nv,2,nv
  c := 'COL2'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,2610,3.64799419131279E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,2650,3.65042808046523E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,9626,4.25768342399856E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,4,6.40253651379786E-07,4,9627,0,3.64799419131279E+35,4.32664361665097E+35,3,1,9627,4.32664361665097E+35,0,TO_DATE('2015-01-11 01:13:44',df),'4642','5354',nv,2,nv
  c := 'COL3'
   EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,0,9325,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,1,51741,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,2,51742,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,3,51743,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,4,51745,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,6,51746,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,7,51747,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,8,51748,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,9,51749,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,11,51750,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,12,51751,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,14,51752,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,15,51753,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,16,51754,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,17,51755,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,18,51757,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,19,51758,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,21,51760,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,23,51761,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,24,51762,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,25,51765,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,26,51766,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,27,51767,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,33,51770,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,36,51771,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,38,51772,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,40,51773,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,41,51777,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,42,51778,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,43,51779,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,45,51780,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,46,51782,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,47,51783,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,48,51786,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,49,51787,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,50,51788,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,51,51789,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,52,51790,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,53,51791,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,54,51792,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,55,51793,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,56,51794,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,57,51795,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,58,51796,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,59,51797,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,60,51798,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,61,51799,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,62,51800,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,63,51801,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,64,51802,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,65,51803,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,67,51804,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,68,51806,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,69,51807,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,70,51810,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,72,51811,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,73,51815,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,74,51819,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,75,51820,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,76,51821,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,77,51824,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,78,51825,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,79,51827,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,80,51829,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,81,51830,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,82,51833,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,83,51834,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,84,51835,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,85,51837,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,86,51840,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,87,51841,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,88,51842,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,89,51843,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,90,51844,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,91,51846,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,92,51847,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,93,51849,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,94,51851,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,95,51852,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,96,51853,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,97,51855,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,99,51856,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,100,51858,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,101,51859,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,102,51861,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,103,51862,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,104,51863,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,105,51864,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,106,51866,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,107,51867,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,108,51869,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,109,51870,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,110,51871,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,111,51873,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,113,51874,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,114,51875,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,115,51876,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,116,51877,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,117,51878,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,119,51879,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,120,51880,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,121,51881,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,122,51882,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,123,51884,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,124,51885,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,125,51886,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,126,51887,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,127,51888,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,128,51889,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,129,51891,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,130,51892,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,131,51894,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,132,51896,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,133,51901,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,134,51902,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,135,51903,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,136,51904,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,137,51905,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,139,51910,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,140,51911,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,141,51912,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,142,51914,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,143,51915,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,144,51917,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,145,51918,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,146,51919,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,147,51920,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,148,51921,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,149,51922,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,150,51924,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,151,51925,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,152,51926,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,153,51997,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,155,51999,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,156,52001,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,157,52004,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,163,52005,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,164,52006,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,165,52007,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,166,52008,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,170,52012,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,171,52014,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,172,52015,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,173,52016,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,175,52017,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,177,52018,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,178,52031,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,179,52040,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,180,52138,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,181,52146,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,182,52533,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,183,53076,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,184,6770894,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,185,34964939,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,186,34964971,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,187,35013110,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,188,35013117,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,189,35013120,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,190,35013127,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,191,35016806,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,192,35017376,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,193,35017433,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,194,35017486,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,195,35017703,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,196,35018421,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,197,35018426,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,198,35018438,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,199,35018450,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
  EXECUTE IMMEDIATE stmt USING 'C',t,p,sp,c,s,5543,.00249376558603491,5543,9627,0,9325,35029221,6,1,200,35018503,0,TO_DATE('2015-01-11 01:13:44',df),'C25E1A','C424035D16',nv,2,nv
...
...

And it just goes on and on and on.

My advice would be:

Never let data pump import manage you statistics, ie, use EXCLUDE=TABLE_STATISTICS for any job with a large amount of objects.

Either recalculate them yourself, or export / import them via DBMS_STATS.  Anything to avoid the drivel above :-(

Adaptive plans and v$sql_plan and related views

Adaptive plans are one of the coolest new optimiser features in Oracle 12c. If you haven’t seen or heard about them in detail I recommend the following resources:

There is a caveat with this though: if your tuning script relies on pulling information from v$sql_plan and related views, you get more information than you might want. I found out about this while working on our 12c New Features training class. This, and a lot more, will be part of it. Stay tuned :)

Consider the following example. I will use the following query in this article:

SELECT
  /* statement002 */
  /*+ gather_plan_statistics monitor */
  TRUNC(order_date, 'mm'),
  COUNT(TRUNC(order_date, 'mm'))
FROM orders o,
  order_items oi
WHERE oi.ORDER_ID    = o.order_id
AND o.CUSTOMER_CLASS = 'Prime'
AND o.WAREHOUSE_ID   = 501
AND o.ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-07-01'
GROUP BY TRUNC(order_date, 'mm')
ORDER BY 1;

These tables are part of the Swingbench SOE (order entry) schema. I have inflated the order_items table to twice its size for a total of 171,579,632 rows.

When executing this query on the x4-2 half rack in the lab I get this (sorry for the wide output!):

TRUNC(ORDER_DATE, COUNT(TRUNC(ORDER_DATE,'MM'))
----------------- -----------------------------
20120101 00:00:00                           472
20120201 00:00:00                           580
20120301 00:00:00                           614
20120401 00:00:00                           578

SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                  |      1 |        |       | 29766 (100)|       |       |      4 |00:00:00.06 |   29354 |     17 |       |       |          |
|   1 |  SORT ORDER BY                                 |                  |      1 |    404 | 14948 | 29766   (1)|       |       |      4 |00:00:00.06 |   29354 |     17 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY                                |                  |      1 |    404 | 14948 | 29766   (1)|       |       |      4 |00:00:00.06 |   29354 |     17 |  1394K|  1394K|  634K (0)|
|*  3 |    FILTER                                      |                  |      1 |        |       |            |       |       |   2244 |00:00:00.05 |   29354 |     17 |       |       |          |
|   4 |     NESTED LOOPS                               |                  |      1 |   2996 |   108K| 29764   (1)|       |       |   2244 |00:00:00.05 |   29354 |     17 |       |       |          |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |      1 |    434 | 13454 | 28462   (1)| ROWID | ROWID |    362 |00:00:00.03 |   28261 |     17 |       |       |          |
|*  6 |       INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX |      1 |  28624 |       |    90   (0)|       |       |  28441 |00:00:00.02 |      90 |      0 |  1025K|  1025K|          |
|*  7 |      INDEX RANGE SCAN                          | ITEM_ORDER_IX    |    362 |      7 |    42 |     3   (0)|       |       |   2244 |00:00:00.01 |    1093 |      0 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(TIMESTAMP' 2012-07-01 00:00:00'>=TIMESTAMP' 2012-01-01 00:00:00')
   5 - filter(("O"."CUSTOMER_CLASS"='Prime' AND "O"."ORDER_DATE">=TIMESTAMP' 2012-01-01 00:00:00' AND "O"."ORDER_DATE"<=TIMESTAMP' 2012-07-01 00:00:00'))
   6 - access("O"."WAREHOUSE_ID"=501)
   7 - access("OI"."ORDER_ID"="O"."ORDER_ID")

   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 2 Sql Plan Directives used for this statement

@x is one of the many useful scripts written by Tanel Poder. I recommend you download the tpt_public.zip and get familiar with what they do-they are just great.

The Execution Plan

Please take a note at the output of the execution plan. The query is an adaptive plan (see notes). The join between orders and order_items is performed using a nested loop (step 4 and the following ones). You can also use v$sql to show that this plan is an adaptive plan:

SQL> select sql_id, child_number, executions, is_reoptimizable, is_resolved_adaptive_plan
  2  from v$sql where sql_id = '3wb68w2kj6gdd';

SQL_ID        CHILD_NUMBER EXECUTIONS I I
------------- ------------ ---------- - -
3wb68w2kj6gdd            0          1 Y Y

It is indeed already resolved. Using Jonathan Lewis’s recent notes on reoptimisation I checked what the optimiser worked out:

SQL> select sql_id, child_number, hint_id, hint_text, reparse
  2  from v$sql_reoptimization_hints where sql_id = '3wb68w2kj6gdd' and child_number = 0;

SQL_ID        CHILD_NUMBER    HINT_ID HINT_TEXT                                             REPARSE
------------- ------------ ---------- -------------------------------------------------- ----------
3wb68w2kj6gdd            0          1 OPT_ESTIMATE (@"SEL$1" GROUP_BY ROWS=4.000000 )             1
3wb68w2kj6gdd            0          2 OPT_ESTIMATE (@"SEL$1" JOIN ("OI"@"SEL$1" "O"@"SEL          1
                                      $1") ROWS=1.000000 )

Nice! That also provides some insights that could be useful. You can map the hint_text to v$sql_plan.object_alias, it is the query block name. I have actually executed slight variations of the query in preparation …

Back to my problem

In the past I have used v$sql_plan (indirectly, via scripts written by far more clever people than me) to work each step in the plan. v$sql_plan has an ID, PARENT_ID and DEPTH that make it easier to work out where in the plan you are. Using my old approach I got stuck, consider this:

SQL> r
  1  select
  2    lpad(' ',sp.depth*1,' ')
  3    || sp.operation AS operation,
  4    sp.OPTIONS,
  5    sp.object#,
  6    sp.object_name,
  7    sp.object_alias,
  8    sp.object_type
  9  FROM v$sql_plan sp
 10* where sql_id = '3wb68w2kj6gdd' and child_number = 0

OPERATION                                OPTIONS                                OBJECT# OBJECT_NAME               OBJECT_ALI OBJECT_TYP
---------------------------------------- ----------------------------------- ---------- ------------------------- ---------- ----------
SELECT STATEMENT
 SORT                                    ORDER BY
  HASH                                   GROUP BY
   FILTER
    HASH JOIN
     NESTED LOOPS
      STATISTICS COLLECTOR
       TABLE ACCESS                      BY GLOBAL INDEX ROWID BATCHED            28865 ORDERS                    O@SEL$1    TABLE
        INDEX                            RANGE SCAN                               29329 ORD_WAREHOUSE_IX          O@SEL$1    INDEX
      INDEX                              RANGE SCAN                               29302 ITEM_ORDER_IX             OI@SEL$1   INDEX
     INDEX                               FAST FULL SCAN                           29302 ITEM_ORDER_IX             OI@SEL$1   INDEX

11 rows selected.

If you compare this with the DBMS_XPLAN-output from above then you notice there is a lot more information in the query against v$sql_plan …In fact, that’s the same output as what you get when calling DBMS_XPLAN.DISPLAY_CURSOR(… format => ‘ADAPTIVE’):

SQL> select * from table(dbms_xplan.display_cursor('3wb68w2kj6gdd',0,'+ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

---------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                 |                  |       |       | 29766 (100)|          |       |       |
|     1 |  SORT ORDER BY                                   |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|     2 |   HASH GROUP BY                                  |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|  *  3 |    FILTER                                        |                  |       |       |            |          |       |       |
|- *  4 |     HASH JOIN                                    |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|     5 |      NESTED LOOPS                                |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|-    6 |       STATISTICS COLLECTOR                       |                  |       |       |            |          |       |       |
|  *  7 |        TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |   434 | 13454 | 28462   (1)| 00:00:02 | ROWID | ROWID |
|  *  8 |         INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX | 28624 |       |    90   (0)| 00:00:01 |       |       |
|  *  9 |       INDEX RANGE SCAN                           | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
|-   10 |      INDEX FAST FULL SCAN                        | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)
   - 2 Sql Plan Directives used for this statement

Note the line: this is an adaptive plan (rows marked ‘-‘ are inactive). But how does DBMS_XPLAN know that these lines are hidden? There doesn’t seem to be a view v$sql_plan_hidden_lines. I tried a few things and eventually traced the call to DBMS_XPLAN.DISPLAY_CURSOR. In the trace I found the trick Oracle uses:

=====================
PARSING IN CURSOR #140127730386096 len=298 dep=2 uid=75 oct=3 lid=75 tim=767822822383 hv=580989905 ad='e9e6f7570' sqlid='fg4skgcja2cyj'
SELECT EXTRACTVALUE(VALUE(D), '/row/@op'), EXTRACTVALUE(VALUE(D), '/row/@dis'), EXTRACTVALUE(VALUE(D), '/row/@par'), EXTRACTVALUE(VALUE(D),
'/row/@prt'), EXTRACTVALUE(VALUE(D), '/row/@dep'), EXTRACTVALUE(VALUE(D), '/row/@skp') FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:B1 ), 
'/*/display_map/row'))) D
END OF STMT
...
STAT #140127732564416 id=1 cnt=11 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=691 us cost=1 size=8935 card=1)'
STAT #140127732564416 id=2 cnt=11 pid=1 pos=1 obj=0 op='VIEW  (cr=0 pr=0 pw=0 time=1223 us cost=0 size=8935 card=1)'
STAT #140127732564416 id=3 cnt=11 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=0 pr=0 pw=0 time=1116 us cost=0 size=957 card=1)'
STAT #140127732564416 id=4 cnt=11 pid=3 pos=1 obj=0 op='FIXED TABLE FIXED INDEX X$KQLFXPL (ind:4) (cr=0 pr=0 pw=0 time=902 us cost=0 size=853 card=1)'
STAT #140127732564416 id=5 cnt=11 pid=3 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KGLCURSOR_CHILD (ind:2) (cr=0 pr=0 pw=0 time=134 us cost=0 size=104 card=1)'

So that’s it! The X$ views map to v$sql and v$sql_plan unless I am very mistaken. V$SQL_PLAN only has 1 column that contains XML-other_xml. Using this information I thought there has to be something in there … and indeed, there is:

SQL> select xmltype(other_xml)
  2  from v$sql_plan
  3  where sql_id = '3wb68w2kj6gdd' and child_number = 0
  4  and other_xml is not null;

XMLTYPE(OTHER_XML)
---------------------------------------------------------------------------------------------------------------------------

  12.1.0.2
  
  2
  166760258
  812470616
  3729130925
  yes
  
    0
    2
  
  
    
    
    
    
    
    
    
    
    
    
    
  
  
    
    
    
    
    
    
    
    
    
    
  

The SQL statement from the trace was not much use to me, the following seemed better suited to work out what was happening. I added what I think the abbreviations stand for:

WITH display_map AS
  (SELECT X.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS 
      op  NUMBER PATH '@op',    -- operation
      dis NUMBER PATH '@dis',   -- display
      par NUMBER PATH '@par',   -- parent
      prt NUMBER PATH '@prt',   -- ?
      dep NUMBER PATH '@dep',   -- depth
      skp NUMBER PATH '@skp' )  -- skip
  AS X
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
SELECT * from display_map;

Enter value for sql_id: 3wb68w2kj6gdd
Enter value for sql_child: 0

        OP        DIS        PAR        PRT        DEP        SKP
---------- ---------- ---------- ---------- ---------- ----------
         1          1          0          0          1          0
         2          2          1          0          2          0
         3          3          2          0          3          0
         4          3          3          0          3          1
         5          4          3          0          4          0
         6          4          4          0          4          1
         7          5          4          5          5          0
         8          6          5          0          6          0
         9          7          4          0          5          0
        10          7          3          0          3          1

10 rows selected.

Well-that’s a starting point. Now all I have to do is join the display map to v$sql_plan cleverly. After a little bit of fiddling with the query this seems to work:

WITH display_map AS
  (SELECT X.*
  FROM v$sql_plan,
    XMLTABLE ( '/other_xml/display_map/row' passing XMLTYPE(other_xml ) COLUMNS 
      op  NUMBER PATH '@op',    -- operation
      dis NUMBER PATH '@dis',   -- display
      par NUMBER PATH '@par',   -- parent
      prt NUMBER PATH '@prt',   -- ?
      dep NUMBER PATH '@dep',   -- depth
      skp NUMBER PATH '@skp' )  -- skip
  AS X
  WHERE sql_id     = '&sql_id'
  AND child_number = &sql_child
  AND other_xml   IS NOT NULL
  )
SELECT 
  -- new ID, depth, parent etc from display_map
  NVL(m.dis, 0) AS new_id,
  m.par         AS new_parent,
  m.dep         AS new_depth,
  -- plan formatting, as usual
  lpad(' ',m.dep*1,' ')
  || sp.operation AS operation,
  sp.OPTIONS,
  sp.object#,
  sp.object_name,
  sp.object_alias,
  sp.object_type
FROM v$sql_plan sp
LEFT OUTER JOIN display_map m
ON (sp.id = m.op)
WHERE sp.sql_Id        = '&sql_id'
AND sp.child_number    = &sql_child
AND NVL(m.skp,0)      <> 1
ORDER BY NVL(dis,0);

    NEW_ID NEW_PARENT  NEW_DEPTH OPERATION                      OPTIONS                                OBJECT# OBJECT_NAME               OBJECT_ALI OBJECT_TYP
---------- ---------- ---------- ------------------------------ ----------------------------------- ---------- ------------------------- ---------- ----------
         0                       SELECT STATEMENT
         1          0          1  SORT                          ORDER BY
         2          1          2   HASH                         GROUP BY
         3          2          3    FILTER
         4          3          4     NESTED LOOPS
         5          4          5      TABLE ACCESS              BY GLOBAL INDEX ROWID BATCHED            28865 ORDERS                    O@SEL$1    TABLE
         6          5          6       INDEX                    RANGE SCAN                               29329 ORD_WAREHOUSE_IX          O@SEL$1    INDEX
         7          4          5      INDEX                     RANGE SCAN                               29302 ITEM_ORDER_IX             OI@SEL$1   INDEX

8 rows selected.

This seems to match what Oracle produces:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3wb68w2kj6gdd, child number 0
-------------------------------------
SELECT   /* statement002 */   /*+ gather_plan_statistics monitor */
TRUNC(order_date, 'mm'),   COUNT(TRUNC(order_date, 'mm')) FROM orders
o,   order_items oi WHERE oi.ORDER_ID    = o.order_id AND
o.CUSTOMER_CLASS = 'Prime' AND o.WAREHOUSE_ID   = 501 AND o.ORDER_DATE
BETWEEN DATE '2012-01-01' AND DATE '2012-07-01' GROUP BY
TRUNC(order_date, 'mm') ORDER BY 1

Plan hash value: 812470616

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                  |       |       | 29766 (100)|          |       |       |
|   1 |  SORT ORDER BY                                 |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|   2 |   HASH GROUP BY                                |                  |   404 | 14948 | 29766   (1)| 00:00:02 |       |       |
|*  3 |    FILTER                                      |                  |       |       |            |          |       |       |
|   4 |     NESTED LOOPS                               |                  |  2996 |   108K| 29764   (1)| 00:00:02 |       |       |
|*  5 |      TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| ORDERS           |   434 | 13454 | 28462   (1)| 00:00:02 | ROWID | ROWID |
|*  6 |       INDEX RANGE SCAN                         | ORD_WAREHOUSE_IX | 28624 |       |    90   (0)| 00:00:01 |       |       |
|*  7 |      INDEX RANGE SCAN                          | ITEM_ORDER_IX    |     7 |    42 |     3   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------------------------

With this done it should be possible to add other diagnostic information too, just join the additional views and add the relevant columns. Hope this helps! I haven’t performed extensive testing on the approach but wanted to put it out here for the more clever people to tell me where I’m wrong.

FBI Bug reprise

I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 9.2.0.8 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases.  Here’s some code to create a table and two indexes:


create table t1
nologging
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum			id,
	cast(dbms_random.string('U',2) as char(2))	c1,
	cast(dbms_random.string('U',2) as char(2))	c2,
	cast(dbms_random.string('U',2) as char(2))	c3,
	cast(dbms_random.string('U',2) as char(2))	c4,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e5
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);
end;
/

create index t1_iasc  on t1(c1, c2,      c3, c4) nologging;
create index t1_idesc on t1(c1, c2 desc, c3, c4) nologging;

I’ve designed the table to model the way a problem was presented on OTN, it’s possible that the anomaly would appear in simpler circumstance; note that I have two indexes on the same four columns, but the second column of one of the indexes is declared as descending. To identify the indexes easily in execution plans the latter index has the text “desc” in its name. So here’s a query, with execution plan, that should use one of these indexes:


select
        *
from t1
where
        (C1 = 'DE' and C2 >  'AB')
or      (C1 = 'DE' and C2 >= 'AB' and C3 > 'AA' )
or      (C1 = 'DE' and C2 >= 'AB' and C3 >= 'AA' and C4 >= 'BB')
order by
        C1, C2, C3, C4
;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    21 |  2478 |     4  (25)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |    21 |       |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter(((SYS_OP_DESCEND("C2")'AB') OR
              (SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C3">'AA' AND "C2">='AB') OR
              (SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C4">='BB' AND "C2">='AB' AND
              "C3">='AA')))

Sure enough – the query has used the t1_iasc index – but why has the optimizer introduced all those predicates with the sys_op_descend() function calls in them when we’re not using an index with a descending column ? Somewhere in the code path the optimizer has picked up the other index, and decided to use some information from it that is totally redundant. One side effect of this is that the cardinality prediction is 21 – if I drop the index t1_idesc the sys_op_descend() calls disappear and the cardinality changes to 148.

Oracle 12c behaves differently – it uses concatenation to turn the query into three separate index probes unless I add the hint /*+ first_rows */ (which I shouldn’t be using, but it’s the hint supplied by the OP on OTN). With the hint in place we get an example of the actual execution plan differing from the prediction made through explain plan:


12c execution plan unhinted (concatenation, and lots of sys_op_descend()):

--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |       |       |    12 (100)|          |
|   1 |  SORT ORDER BY                        |          |   149 | 17582 |    12   (9)| 00:00:01 |
|   2 |   CONCATENATION                       |          |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |   142 | 16756 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T1_IDESC |     1 |       |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     6 |   708 |     4   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | T1_IDESC |     1 |       |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     1 |   118 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                  | T1_IASC  |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"='DE' AND "C3">='AA' AND "C4">='BB' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))
       filter("C4">='BB' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB' AND "C3">='AA')
   6 - access("C1"='DE' AND "C3">'AA' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))
       filter("C3">'AA' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB' AND
              (LNNVL("C4">='BB') OR LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR
              LNNVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB') OR LNNVL("C3">='AA')))
   8 - access("C1"='DE' AND "C2">'AB' AND "C2" IS NOT NULL)
       filter((LNNVL("C3">'AA') OR LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR
              LNNVL("C2">='AB')) AND (LNNVL("C4">='BB') OR
              LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR LNNVL("C2">='AB') OR
              LNNVL("C3">='AA')))

12c Execution plan with first_rows hint (and the sys_op_descend have gone)
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   150 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   148 | 17464 |   150   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |   148 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter(("C2">'AB' OR ("C3">'AA' AND "C2">='AB') OR ("C4">='BB' AND
              "C2">='AB' AND "C3">='AA')))

12c Execution plan with first_rows according to Explain Plan (and they're back again)
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   148 | 17464 |   150   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   148 | 17464 |   150   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |   148 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter("C2">'AB' AND SYS_OP_DESCEND("C2")'AA' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C2">='AB' OR
              "C4">='BB' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C2">='AB' AND
              "C3">='AA')

On the plus side the upgrade to 12c has removed some of the sys_op_descend() appearances and seems to have fixed the cardinality anomaly when the sys_op_descend() calls do appear – but things are still going wrong in odd ways. (And this looks like another case where you could drop an index that you’re not using and seeing an execution plan change.)

Footnote:

When playing around with 12c, I had to be very careful to avoid confusing the issue as SQL Plan Directives were appearing from time to time, and giving me unexpected results as I repeated some of the experiments.

Footnote 2:

I haven’t actually addressed the question originally posed on OTN, but that can wait for another day.

Installing Oracle 12.1.0.2 RAC on Oracle Linux 7-part 2

In the first part of the article series you could read how a kickstart file made the installation of Oracle Linux 7 a lot more bearable. In this part of the series it’s all about configuring the operating system. The installation of Grid Infrastructure and the Oracle database is for another set of posts.

There are quite some differences between Oracle Linux 6 and 7

To me the transition from Oracle Linux 6 to 7 feels like the step from Solaris 9 to 10 at the time. Personally I think that a lot has changed. Although, it’s fair to say that it has been quite some time it has been announced that the network stack commands we know and love are deprecated and might go… Even with Oracle Linux 6 there was a threat that network manager would now be the only tool to modify your network settings (which thankfully was not the case). A lot of efforts of the Linux community have now come to fruition, and it’s time to adjust to the future. Even when it’s painful (and it is, at least a bit).

Configuring the network

The warning has been out there quite a while but now it seems to be true-no more system-config-network-tui to configure the network! No more ifconfig! Oh dear-quite a bit of learning to be done. Luckily someone else has done all the legwork and documented the changes. A good example is this one:

https://dougvitale.wordpress.com/2011/12/21/deprecated-linux-networking-commands-and-their-replacements/

So first of all-don’t fear: although all network interfaces are configured using network manager now, you can still use a command line tool: nmtui. After trying it out I have to say I’m not really convinced about its usability. What appears better is the use of the nmcli, network manager command line tool. It’s use is quite confusing, and it appears to me as if the whole network manager toolset was developed for laptop users, not servers. But I digress. I have a few interfaces in my RAC VM, the first was configured during the installation, eth[1-3] aren’t configured yet.

[root@localhost ~]# nmcli connection show
NAME         UUID                                  TYPE            DEVICE
System eth0  77e3f8a9-76d0-4051-a8f2-cbbe39dab089  802-3-ethernet  eth0
[root@localhost ~]# nmcli device status
DEVICE  TYPE      STATE         CONNECTION
eth0    ethernet  connected     System eth0
eth1    ethernet  disconnected  --
eth2    ethernet  disconnected  --
eth3    ethernet  disconnected  --
lo      loopback  unmanaged     --
[root@localhost ~]#

At this point I have used eth0 as the management network (similar to the way Exadata does) and will use the other networks for the database. eth1 will act as the public network, eth2 and eth3 will be private.

Although the network interfaces can be named differently for device name persistence I stick with the old naming for now. I don’t want to run into trouble with the installer just yet. On physical hardware you are very likely to see very different network interface names, the kernel uses a naming scheme identifying where the cards are (on the main board, or in extension cards for example). I’ll write another post about that soon.

Using dnsmasq (on the host) I configure my hosts for these addresses:

[root@ol62 ~]# grep rac12pri /etc/hosts
192.168.100.107	rac12pri1.example.com		rac12pri1
192.168.100.108	rac12pri1-vip.example.com	rac12pri1-vip
192.168.100.109	rac12pri2.example.com		rac12pri2
192.168.100.110	rac12pri2-vip.example.com	rac12pri2-vip
192.168.100.111	rac12pri-scan.example.com	rac12pri-scan
192.168.100.112	rac12pri-scan.example.com	rac12pri-scan
192.168.100.113	rac12pri-scan.example.com	rac12pri-scan

Configuring the interface is actually not too hard once you got the hang of it. It took me a little while to get it though… It almost appears as if something that was simple and easy to use was made difficult to use.

[root@localhost ~]# nmcli con add con-name eth1 ifname eth1 type ethernet ip4 192.168.100.107/24 gw4 192.168.100.1
[root@localhost ~]# nmcli con add con-name eth2 ifname eth2 type ethernet ip4 192.168.101.107/24
[root@localhost ~]# nmcli con add con-name eth3 ifname eth3 type ethernet ip4 192.168.102.107/24 

[root@localhost ~]# nmcli con show
NAME         UUID                                  TYPE            DEVICE
eth2         ccc7f592-b563-4b9d-a36b-2b45809e4643  802-3-ethernet  eth2
eth1         ae897dee-42ff-4ccd-843b-7c97ba0d5315  802-3-ethernet  eth1
System eth0  77e3f8a9-76d0-4051-a8f2-cbbe39dab089  802-3-ethernet  eth0
eth3         b6074c9a-dcc4-4487-9a8a-052e4c60bbca  802-3-ethernet  eth3

I can now verify the IP addresses using the “ip” tool (ifconfig was not installed, I haven’t yet checked if there was a compatibility package though)

[root@localhost ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:6e:6f:67 brd ff:ff:ff:ff:ff:ff
    inet 192.168.150.111/24 brd 192.168.150.255 scope global eth0
    inet6 fe80::5054:ff:fe6e:6f67/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:96:ad:88 brd ff:ff:ff:ff:ff:ff
    inet 192.168.100.107/24 brd 192.168.100.255 scope global eth1
    inet6 fe80::5054:ff:fe96:ad88/64 scope link
       valid_lft forever preferred_lft forever
4: eth2: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:c1:cc:8e brd ff:ff:ff:ff:ff:ff
    inet 192.168.101.107/24 brd 192.168.101.255 scope global eth2
    inet6 fe80::5054:ff:fec1:cc8e/64 scope link
       valid_lft forever preferred_lft forever
5: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:7e:59:45 brd ff:ff:ff:ff:ff:ff
    inet 192.168.102.107/24 brd 192.168.102.255 scope global eth3
    inet6 fe80::5054:ff:fe7e:5945/64 scope link
       valid_lft forever preferred_lft forever

Now what’s left is setting the hostname-which is a simple call to hostnamectl –static set-hostname rac12pri1. nmcli gives you an interface to changing the hostname as well. I repeated the steps for node 2, they are identical except for the network IP addresses of course.

So that concludes the network setup.

Managing linux daemons

If you are curious about setting services at runlevel, then there’ll be another surprise:

[root@rac12pri2 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

iprdump        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
iprinit        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
iprupdate      	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
pmcd           	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmie           	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmlogger       	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmmgr          	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmproxy        	0:off	1:off	2:off	3:off	4:off	5:off	6:off
pmwebd         	0:off	1:off	2:off	3:off	4:off	5:off	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:off
[root@rac12pri2 ~]#

If you just got familiar with upstart then there are some bad news: upstart is now replaced with systemd… This might be the right time to read up on that if you aren’t familiar with it yet:

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/System_Administrators_Guide/chap-Managing_Services_with_systemd.html

Things are a little different with that, so here is an example how to enable and start the NTP service. It has to be installed first if that hasn’t been the case. You also should add the -x flag in /etc/sysconfig/ntpd. First I would like to see if the service is available. You use systemctl for this-so instead of a chkconfig ntpd –list you call systemctl as shown:

[root@rac12pri ~]# systemctl list-units --type service --all | grep ntpd
ntpd.service                                                                              loaded inactive dead    Network Time Service
ntpdate.service                                                                           loaded inactive dead    Set time via NTP

I have to get used to the new syntax: previously you used “service status” and then, if you needed, typed backspace a few times and changed status to start. The new syntax is closer to human language but less practical: systemctl status . Changing status to start requires more typing.

The check proved that the service exists (i.e. the NTP package is installed), but it is not started. We can change this:

[root@rac12pri ~]# systemctl enable ntpd.service
[root@rac12pri ~]# systemctl start ntpd.service
[root@rac12pri ~]# systemctl status ntpd.service
ntpd.service - Network Time Service
Loaded: loaded (/usr/lib/systemd/system/ntpd.service; enabled)
Active: active (running) since Tue 2014-12-16 15:38:47 GMT; 1s ago
Process: 5179 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 5180 (ntpd)
CGroup: /system.slice/ntpd.service
└─5180 /usr/sbin/ntpd -u ntp:ntp -g -x

Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 8 eth1 fe80::5054:ff:fe96:ad88 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 9 eth2 fe80::5054:ff:fec1:cc8e UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 10 eth3 fe80::5054:ff:fe7e:5945 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listen normally on 11 eth0 fe80::5054:ff:fe6e:6f67 UDP 123
Dec 16 15:38:47 rac12pri ntpd[5180]: Listening on routing socket on fd #28 for interface updates
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c016 06 restart
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c012 02 freq_set ntpd 0.000 PPM
Dec 16 15:38:47 rac12pri ntpd[5180]: 0.0.0.0 c011 01 freq_not_set
Dec 16 15:38:47 rac12pri systemd[1]: Started Network Time Service.
Dec 16 15:38:48 rac12pri ntpd[5180]: 0.0.0.0 c614 04 freq_mode
[root@rac12pri ~]#

The call to “systemctl enable” replaces an invocation of chkconfig to automatically start ntpd as a service (chkconfig ntpd on). Starting the service does not produce any output, hence the need to check the status.

There is a slight caveat with the use of NTP: it is not the default time keeping service. Another tool, named chronyd is used instead.

This causes a problem after the next reboot: chronyd will be started, NTPd won’t be. The Red Hat documentation therefore has a section on how to switch:

[root@rac12pri ~]# systemctl stop chronyd
[root@rac12pri ~]# systemctl disable chronyd
[root@rac12pri ~]# systemctl status chronyd

Storage

Shared storage is provided by KVM. I am using my SSDs in the lab from where I create a few “LUNs”. These must explicitly be made “shareable” to be accessible by more than one guest. Since 12.1.0.2.0 Oracle installs a database for the cluster health monitor by default. Currently I use the following setup for my lab 12.1.0.2 clusters:

  1. +CHM (external redundancy) – 1x 15GB
  2. +OCR (normal redundancy) – 3x 2 GB
  3. +DATA (external redundancy) – 1 x 15GB
  4. +RECO (external redundancy) – 1 x 10 GB

If you use the guided installation of Grid Infrastructure the installer will prompt you for a single disk group only. This means that the CHM database as well as the OCR and voting files be installed in that disk group. I prefer to separate them though, which is why I create a second disk group OCR after the installation has completed and move the voting files and OCR out of +CHM.

DATA and RECO are standard Exadata disk groups and I like to keep things consistent for myself.

I use fdisk to partition the future ASM disks with 1 partition spanning the whole LUN.

Other tasks

A lot of the other pre-installation tasks can actually be performed during the kickstart installation. I still like to use SELinux in permissive mode even though-according to Requirements for Installing Oracle Database 12.1 on RHEL6 or OL6 64-bit (x86-64) (Doc ID 1529864.1)-selinux can be in “enforcing”. The directive in the kickstart file is

selinux –permissive

You shouldn’t have to install additional packages-all packages to be installed should go into the %packages section of the file. Simply copy the package names from the official documentation and paste below the last package in the section. There is one exception to the rule: cvuqdisk must be installed from the Oracle installation media.

Settings for /etc/sysctl.conf and /etc/security/limits.conf can also be made in the kickstart file as shown in the first part of this series.

Storage to be made available to RAC must have permissions set. Since there isn’t an ASMLib in Oracle Linux 7 to my knowledge UDEV will have to be used, and my udev configuration file, too, is in the first part.

To make sure my user and group IDs for the oracle and grid account are the same I create the accounts in the kickstart file as well. Passwords are deliberately not set-they may evolve and I can’t possibly remember them all :)

User equivalence can be set up using a technique I have already described in an earlier blog post. Although the user equivalence setup can be deferred to when you install Grid Infrastructure I still perform it before to allow me to run the cluster verification tool with the -fixup option.

Video Tutorial: XPLAN_ASH Active Session History - Introduction

I finally got around preparing another part of the XPLAN_ASH video tutorial.

This part is about the main funcationality of XPLAN_ASH: SQL statement execution analysis using Active Session History and Real-Time SQL Monitoring.

In this video tutorial I'll explain what the output of XPLAN_ASH is supposed to mean when using the Active Session History functionality of the script. Before diving into the details of the script output using sample reports I provide some overview and introduction in this part that hopefully makes it simpler to understand how the output is organized and what it is supposed to mean.

This is the initial, general introduction part. More parts to follow.

count(*) – again !

Because you can never have enough of a good thing.

Here’s a thought – The optimizer doesn’t treat all constants equally.  No explanations, just read the code – execution plans at the end:


SQL> drop table t1 purge;
SQL> create table t1 nologging as select * from all_objects;
SQL> create bitmap index t1_b1 on t1(owner);

SQL> alter session set statistics_level = all;

SQL> set serveroutput off
SQL> select count(*) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> select count(1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> select count(-1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> alter session set cursor_sharing = force;
SQL> alter system flush shared_pool;

SQL> select count(1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

So, are you expecting to see the same results and performance from every single one of those queries ?


select count(*) from t1
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.01 |       9 |      5 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.01 |       9 |      5 |
|   2 |   BITMAP CONVERSION COUNT     |       |      1 |  84499 |     31 |00:00:00.01 |       9 |      5 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |      5 |
----------------------------------------------------------------------------------------------------------

select count(1) from t1
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.01 |       9 |
|   2 |   BITMAP CONVERSION COUNT     |       |      1 |  84499 |     31 |00:00:00.01 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

select count(-1) from t1
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.43 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.43 |       9 |
|   2 |   BITMAP CONVERSION TO ROWIDS |       |      1 |  84499 |  84499 |00:00:00.22 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

SQL> alter session set cursor_sharing = force;
SQL> alter system flush shared_pool;

select count(1) from t1
select count(:"SYS_B_0") from t1    -- effect of cursor-sharing
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.46 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.46 |       9 |
|   2 |   BITMAP CONVERSION TO ROWIDS |       |      1 |  84499 |  84499 |00:00:00.23 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

Check operation 2 in each plan – with the bitmap index in place there are two possible ways to count the rows referenced in the index – and one of them converts to rowids and does a lot more work.

The only “real” threat in this set of examples, of course, is the bind variable one – there are times when count(*) WILL be faster than count(1). Having said that, there is a case where a redundant “conversion to rowids” IS a threat – and I’ll write that up some time in the near future.

Trick question: when is 1+1 != 2 ?
Silly answer: compare the plan for: “select count (2) from t1″ with the plan for “select count(1+1) from t1″

Note: All tests above run on 12.1.0.2

"SELECT * FROM TABLE" Runs Out Of TEMP Space

Now that I've shown in the previous post in general that sometimes Parallel Execution plans might end up with unnecessary BUFFER SORT operations, let's have a look what particular side effects are possible due to this.

What would you say if someone tells you that (s)he just did a simple, straightforward "SELECT * FROM TABLE" that took several minutes to execute without returning, only to then error out with "ORA-01652 unable to extend temp segment", and the TABLE in question is actually nothing but a simple, partitioned heap table, so no special tricks, no views, synonyms, VPD etc. involved, it's really just a plain simple table?

Some time ago I was confronted with such a case at a client. Of course, the first question is, why would someone run a plain SELECT * FROM TABLE, but nowadays with power users and developers using GUI based tools like TOAD or SQLDeveloper, this is probably the GUI approach of a table describe command. Since these tools by default show the results in a grid that only fetches the first n rows, this typically isn't really a threat even in case of large tables, besides the common problems with allocated PX servers in case the table is queried using Parallel Execution, and the users simply keep the grid/cursor open and hence don't allow re-using the PX servers for different executions.

But have a look at the following output, in this case taken from 12.1.0.2, but assuming the partitioned table T_PART in question is marked parallel, resides on Exadata, has many partitions that are compressed via HCC, that uncompressed represent several TB of data (11.2.0.4 on Exadata produces a similar plan):


SQL> explain plan for
2 select * from t_part p;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display(format => 'BASIC PARTITION PARALLEL'));
Plan hash value: 2545275170

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | Q1,02 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | | | Q1,02 | PCWP | |
| 4 | VIEW | VW_TE_2 | | | Q1,02 | PCWP | |
| 5 | UNION-ALL | | | | Q1,02 | PCWP | |
| 6 | CONCATENATION | | | | Q1,02 | PCWP | |
| 7 | BUFFER SORT | | | | Q1,02 | PCWC | |
| 8 | PX RECEIVE | | | | Q1,02 | PCWP | |
| 9 | PX SEND ROUND-ROBIN | :TQ10000 | | | | S->P | RND-ROBIN |
| 10 | BUFFER SORT | | | | | | |
| 11 | PARTITION RANGE SINGLE | | 2 | 2 | | | |
| 12 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_PART | 2 | 2 | | | |
|* 13 | INDEX RANGE SCAN | T_PART_IDX | 2 | 2 | | | |
| 14 | BUFFER SORT | | | | Q1,02 | PCWC | |
| 15 | PX RECEIVE | | | | Q1,02 | PCWP | |
| 16 | PX SEND ROUND-ROBIN | :TQ10001 | | | | S->P | RND-ROBIN |
| 17 | BUFFER SORT | | | | | | |
| 18 | PARTITION RANGE SINGLE | | 4 | 4 | | | |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_PART | 4 | 4 | | | |
|* 20 | INDEX RANGE SCAN | T_PART_IDX | 4 | 4 | | | |
| 21 | PX BLOCK ITERATOR | | 6 | 20 | Q1,02 | PCWC | |
|* 22 | TABLE ACCESS FULL | T_PART | 6 | 20 | Q1,02 | PCWP | |
| 23 | PX BLOCK ITERATOR | |KEY(OR)|KEY(OR)| Q1,02 | PCWC | |
|* 24 | TABLE ACCESS FULL | T_PART |KEY(OR)|KEY(OR)| Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------

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

13 - access("P"."DT">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
20 - access("P"."DT">=TO_DATE(' 2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
filter(LNNVL("P"."DT"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
22 - filter("P"."DT">=TO_DATE(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss') AND (LNNVL("P"."DT"=TO_DATE(' 2003-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))) AND (LNNVL("P"."DT"=TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
24 - filter("P"."DT"=TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "P"."DT" hh24:mi:ss') AND "P"."DT">=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Can you spot the problem? It's again the "unnecessary BUFFER SORTS" problem introduced in the previous post. In particular the operation ID = 3 BUFFER SORT is "deadly" if the table T_PART is large, because it needs to buffer the whole table data before any row will be returned to the client. This explains why this simple SELECT * FROM T_PART will potentially run out of TEMP space, assuming the uncompressed table data is larger in size than the available TEMP space. Even if it doesn't run out of TEMP space it will be a totally inefficient operation, copying all table data to PGA (unlikely sufficient) respectively TEMP before returning any rows to the client.

But why does a simple SELECT * FROM TABLE come up with such an execution plan? A hint is the VW_TE_2 alias shown in the NAME column of the plan: It's the result of the "table expansion" transformation that was introduced in 11.2 allowing to set some partition's local indexes to unusable but still make use of the usable index partitions of other partitions. It takes a bit of effort to bring the table into a state where such a plan will be produced for a plain SELECT * FROM TABLE, but as you can see, it is possible. And as you can see from the CONCATENATION operation in the plan, the transformed query produced by the "table expansion" then triggered another transformation, the "concatenation" transformation mentioned in the previous post, that then results in the addition of unnecessary BUFFER SORT operations when combined with Parallel Execution.

Here is a manual rewrite that corresponds to the query that is the result of both, the "table expansion" and the "concatenation" transformation:


select * from (
select /*+ opt_param('_optimizer_table_expansion', 'false') */ * from t_part p where
("P"."DT">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
union all
select * from t_part p where
("P"."DT">=TO_DATE(' 2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
and
(LNNVL("P"."DT"=TO_DATE(' 2001-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
union all
select * from t_part p where
("P"."DT">=TO_DATE(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss') AND (LNNVL("P"."DT"=TO_DATE(' 2003-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))) AND (LNNVL("P"."DT"=TO_DATE('
2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
)
union all
select * from t_part p where
("P"."DT"=TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "P"."DT" hh24:mi:ss') AND "P"."DT">=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
;

But if you run an EXPLAIN PLAN on above manual rewrite, then 12.1.0.2 produces the following simple and elegant plan:


--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | Q1,00 | P->S | QC (RAND) |
| 3 | UNION-ALL | | | | Q1,00 | PCWP | |
| 4 | VIEW | | | | Q1,00 | PCWP | |
| 5 | UNION-ALL | | | | Q1,00 | PCWP | |
| 6 | PX SELECTOR | | | | Q1,00 | PCWP | |
| 7 | PARTITION RANGE SINGLE | | 2 | 2 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_PART | 2 | 2 | Q1,00 | PCWP | |
|* 9 | INDEX RANGE SCAN | T_PART_IDX | 2 | 2 | Q1,00 | PCWP | |
| 10 | PX SELECTOR | | | | Q1,00 | PCWP | |
| 11 | PARTITION RANGE SINGLE | | 4 | 4 | Q1,00 | PCWP | |
| 12 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_PART | 4 | 4 | Q1,00 | PCWP | |
|* 13 | INDEX RANGE SCAN | T_PART_IDX | 4 | 4 | Q1,00 | PCWP | |
| 14 | PX BLOCK ITERATOR | | 6 | 20 | Q1,00 | PCWC | |
|* 15 | TABLE ACCESS FULL | T_PART | 6 | 20 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | |KEY(OR)|KEY(OR)| Q1,00 | PCWC | |
|* 17 | TABLE ACCESS FULL | T_PART |KEY(OR)|KEY(OR)| Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------

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

9 - access("P"."DT">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
13 - access("P"."DT">=TO_DATE(' 2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."DT" hh24:mi:ss'))
filter(LNNVL("P"."DT"=TO_DATE(' 2001-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
15 - filter((LNNVL("P"."DT"=TO_DATE(' 2003-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))) AND (LNNVL("P"."DT"=TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
17 - filter("P"."DT"=TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "P"."DT" hh24:mi:ss') AND "P"."DT">=TO_DATE(' 2002-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

I've disabled the "table expansion" transformation in this case, because it kicks in again when optimizing this query and just adds some harmless (and useless) branches to the plan that confuse the issue. Without those additional, useless branches it is very similar to the above plan, but without any BUFFER SORT operations, hence it doesn't cause any overhead and should return the first rows rather quickly, no matter how large the table is.

The 11.2.0.4 optimizer unfortunately again adds unnecessary BUFFER SORT operations even to the manual rewrite above, so as mentioned in the previous post the problem of those spurious BUFFER SORTs isn't limited to the CONCATENATION transformation.

Of course, since all this is related to Parallel Execution, a simple workaround to the problem is to run the SELECT * FROM TABLE using a NO_PARALLEL hint, and all those strange side effects of BUFFER SORTS will be gone. And not having unusable local indexes will also prevent the problem, because then the "table expansion" transformation won't kick in.

Interestingly, if the optimizer is told about the true intention of initially fetching only the first n rows from the SELECT * FROM TABLE - for example simply by adding a corresponding FIRST_ROWS(n) hint - at least in my tests using 12.1.0.2 all the complex transformations were rejected and a plain (parallel) FULL TABLE SCAN was preferred instead, simply because it is now differently costed, which would allow working around the problem, too.

If you want to reproduce the issue, here's a sample table definition, along with some comments what I had to do to bring it into the state required to reproduce:


-- The following things have to come together to turn a simple SELECT * from partitioned table into a complex execution plan
-- including Table Expansion and Concatenation:
--
-- - Unusable index partitions to trigger Table Expansion
-- - Partitions with usable indexes that are surrounded by partitions with unusable indexes
-- - And such a partition needs to have an index access path that is cheaper than a corresponding FTS, typically by deleting the vast majority of rows without resetting the HWM
-- - All this also needs to be reflected properly in the statistics
--
-- If this scenario is combined with Parallel Execution the "Parallel Concatenation" bug that plasters the plan with superfluous BUFFER SORT will lead to the fact
-- that the whole table will have to be kept in memory / TEMP space when running SELECT * from the table, because the bug adds, among many other BUFFER SORTs, one deadly BUFFER SORT
-- on top level before returning data to the coordinator, typically operation ID = 3
--
create table t_part (dt not null, id not null, filler)
partition by range (dt)
(
partition p_1 values less than (date '2001-01-01'),
partition p_2 values less than (date '2002-01-01'),
partition p_3 values less than (date '2003-01-01'),
partition p_4 values less than (date '2004-01-01'),
partition p_5 values less than (date '2005-01-01'),
partition p_6 values less than (date '2006-01-01'),
partition p_7 values less than (date '2007-01-01'),
partition p_8 values less than (date '2008-01-01'),
partition p_9 values less than (date '2009-01-01'),
partition p_10 values less than (date '2010-01-01'),
partition p_11 values less than (date '2011-01-01'),
partition p_12 values less than (date '2012-01-01'),
partition p_13 values less than (date '2013-01-01'),
partition p_14 values less than (date '2014-01-01'),
partition p_15 values less than (date '2015-01-01'),
partition p_16 values less than (date '2016-01-01'),
partition p_17 values less than (date '2017-01-01'),
partition p_18 values less than (date '2018-01-01'),
partition p_19 values less than (date '2019-01-01'),
partition p_20 values less than (date '2020-01-01')
)
as
with generator as
(
select /*+ cardinality(1000) */ rownum as id, rpad('x', 100) as filler from dual connect by level <= 1e3
)
select
add_months(date '2000-01-01', trunc(
case
when id >= 300000 and id < 700000 then id + 100000
when id >= 700000 then id + 200000
else id
end / 100000) * 12) as dt
, id
, filler
from (
select
(a.id + (b.id - 1) * 1e3) - 1 + 100000 as id
, rpad('x', 100) as filler
from
generator a,
generator b
)
;

delete from t_part partition (p_2);

commit;

exec dbms_stats.gather_table_stats(null, 't_part')

create unique index t_part_idx on t_part (dt, id) local;

alter index t_part_idx modify partition p_1 unusable;

alter index t_part_idx modify partition p_3 unusable;

alter index t_part_idx modify partition p_5 unusable;

alter table t_part parallel;

alter index t_part_idx parallel;

set echo on pagesize 0 linesize 200

explain plan for
select * from t_part p;

select * from table(dbms_xplan.display(format => 'BASIC PARTITION PARALLEL'));