Skip navigation.

Feed aggregator

Scheduling Processes on Oracle JavaCloud Service SaaS Extensions (JCSSX)

Angelo Santagata - Mon, 2015-04-13 17:38
In the past if we wanted to schedule some background processing in JCSSX we had to get help from the database's scheduler.. Using a clever feature of SchemaDB, that is the ability to call a REST Service from a PLSQL procedure, which in turn would execute our code on JCSSX we were able to effectively execute code on JCSSX at determined intervals/times etc. All this is now unnecessary! With the current version of JCSSX we now fully support a list of 3rd party frameworks (see link for a list) and Quartz is one of them. Jani, colleague from the Fusion Developer Relations team, has written up a really nice blog posting on the FADevrel blog summarising what you can do with Quartz and some code to get you started..You could say "Quartz in 10mins" blog! Check it out

Good News On The EBS Front

Floyd Teter - Mon, 2015-04-13 16:27
Most people who know me professionally know about my enthusiasm for enterprise applications delivered via the SaaS model.  In terms of adoption and agility, SaaS is a winner.  But, at the same time, I also recognized that SaaS is not for everybody.  Those who customize heavily and those who want to retain a higher level of control are probably better off with on-premise enterprise applications.

So I was happy to hear about Cliff Godwin, Oracle's Sr. VP of Applications Technology over the E-Business Suite, laying out a roadmap for the future of the E-Business Suite at the Collaborate 2015 conference.  Not only did Mr. Godwin lay out plans for more incremental releases of EBS 12.2.x, he also shared the news about a future 12.3 release.  One of the primary intents of the 12.3 release will be to take further advantage of in-memory technology.

I'm an EBS fan.  Rock solid database model.  Recently improved user experience.  Delivers high operational efficiency.  So I'm happy to hear that EBS will continue to evolve.

NOTE:  I'm not at Collaborate 15...wish I was, but I'm not...so big thanks to dear friend Karen Brownfield for clueing me in on the news.

Oracle Data Integrator Enterprise Edition Advanced Big Data Option Part 1- Overview and 12.1.3.0.1 install

Rittman Mead Consulting - Mon, 2015-04-13 14:54

Oracle recently announced Oracle Data Integrator Enterprise Edition Advanced Big Data Options as part of the new 12.1.3.0.1 release of ODI. It includes various great new functionalities to work on an Hadoop ecosystem. Let’s have a look at the new features and how to install it on Big Data Lite 4.1 Virtual Machine.

Note that some of these new features, for example Pig and Spark support and use of Oozie, requires the new ODI EE Advanced Big Data Option license on-top of base ODI EE.

Pig and Spark support

So far ODI12c allowed us to use Hive for any Hadoop-based transformation. With this new release, we can now use Pig and Spark as well. Depending on the use case, we can choose which technology will give better performance and switch from one to another with very few changes. That’s the beauty of ODI – all you need is to do is create the logical dataflow in your mapping and choose your technology. There is no need to be a Pig Latin expert or a PySpark ninja, all of this will be generated for you! These two technologies are now available in the Topology, along with the Hadoop Data Server to define where lies the Data. You can also see some Loading Knowledge Modules for Pig and Spark.

Pig and Spark in ODI

Pig, as Mark wrote before, is a dataflow language. It makes it really appropriate with the new “flow paradigm” introduced in ODI 12c. The idea is to write a data pipeline in Pig Latin. That code will undercover create MapReduce jobs that will be executed.

Quoting Mark one more time, Spark is a cluster processing framework that can be used in different programming languages, the two most common being Python and Scala. It allows to do operation like filters, joins and aggregates. All of this can be done in-memory which can provides way better performance over MapReduce. The ODI team choose to use Python as a programming language for Spark so the Knowledge Modules will use PySpark.

New Hive Driver and LKMs

This release also brings significant improvements to the existing Hive technology. A new driver as been introduced under the name DataDirect Apache Hive JDBC Driver. It is actually the Weblogic Hive JDBC driver which aims at improving the performance and the stability.

New Hive Driver

New Knowledges Modules are introduced to benefit from this new driver and they are LKMs instead multi-connections IKMs as it use to be. Thanks to that, it can be combined with other LKMs into the same mapping which was not the case before.

Oozie Agent

Oozie is another Apache project and they define it as “a workflow scheduler system to manage Apache Hadoop jobs”. We can create workflow of different jobs in the Hadoop stack, and then schedule it at a certain time or trigger it when data becomes available.

What Oozie does is similar to the role of the ODI agent, and it’s now possible to use directly an existing Oozie engine instead of deploying a standalone agent on the hadoop cluster.

Oozie Engine

The Oozie engine will do what your ODI agent usually does – execution, scheduling, monitoring – but it is integrated in the Hadoop ecosystem. So we will be able to schedule and monitor our ODI jobs at the same place as all our other Hadoop jobs that we use outside of ODI. Oozie can also automatically retrieve the Hadoop logs. Also we lower the footprint because it doesn’t requires to install an ODI-specific component on the cluster. However, according to the white paper (link below), it looks like Load Plans are not supported. So the idea would be to execute the Load Plans with a standalone or JEE agent that will delegate the execution of Big Data-related scenarios to the Oozie Engine.

HDFS support in file-related ODI Tools

Most of the ODI tools handling files can also do it on HDFS now. So you can delete, move, copy files and folders. You can also append files and transfer it to HDFS via FTP. It’s even possible to detect when a file is created on HDFS. All you need to do is to indicate your Hadoop Logical Schema for source, target or both. In the following example I’m copying a file from the Unix filesystem to HDFS.

odi_tools_hdfs

I think this is a huge step forward. If we want to use ODI 12c for our Hadoop data integration, it must be able to do everything end-to-end. The maintenance or administrative tasks such as archiving, deleting or copying should also be done using ODI. So far it was a bit tedious to created a shell script using hdfs dfs commands and then launch it using OdiOsCommand tool. Now we can directly use the file tools in a package or a procedure!

New mapping components : Jagged and Flatten

The two new components can be used in a Big Data context but also in your traditional data integration. The first one, Jagged, will pivot a set of key-value pairs into a columns with their values.

The Flatten components can be used with advanced files when you have nested attributes, like in JSON. Using a flatten component will generate more rows if needed to extract different values for a same attribute nested into another attribute.

 

You can see the detail of all the new features in the white paper “Advancing Big Data Integration” for ODI 12c.

 

How to install it?

This patch must be applied on top of an existing Oracle Data Integrator 12.1.3.0.0 installation. It is not a bundled patch and it’s only related to Big Data Options so there is no point to install it if you don’t need its functionalities. Also make sure you are licensed for ODIEE Advanced Big Data Option if you plan to use Spark or Pig technology/KMs or execute your jobs using the Oozie engine.

To showcase this, I used the excellent –and free! – Big Data Lite 4.1 VM which already has ODI 12.1.3 and all the Hadoop components we need. So this example will be on an Oracle Enterprise Linux environment.

The first step is to download it from the OTN or My Oracle Support. Also make sure you close ODI Studio and shut down the agents. Then the README recommends to update OPatch and check the OUI. So let’s do that and also set some environment variables and unzip the ODI patch.

[oracle@bigdatalite ~]$ mkdir /home/oracle/bck
[oracle@bigdatalite ~]$ ORACLE_HOME=/u01/ODI12c/
[oracle@bigdatalite ~]$ cd $ORACLE_HOME
[oracle@bigdatalite ODI12c]$ unzip /home/oracle/Desktop/p6880880_132000_Generic.zip -d $ORACLE_HOME 
[oracle@bigdatalite ODI12c]$ OPatch/opatch lsinventory -jre /usr/java/latest/
[oracle@bigdatalite ODI12c]$ export PATH=$PATH:/u01/ODI12c/OPatch/
[oracle@bigdatalite ODI12c]$ unzip -d /home/oracle/bck/ /home/oracle/Desktop/p20042369_121300_Generic.zip 
[oracle@bigdatalite ODI12c]$ cd /home/oracle/bck/

This patch is actually composed of three piece. One of them, the second one, is only needed if you have an enterprise installation. If you have a standalone install, you can just skip it. Note that I always specify the JRE to be used by OPatch to be sure everything works fine.

[oracle@bigdatalite bck]$ unzip p20042369_121300_Generic.zip
[oracle@bigdatalite ODI12c]$ cd 20042369/
[oracle@bigdatalite 20042369]$ opatch apply -jre /usr/java/latest/
[oracle@bigdatalite 20042369]$ cd /home/oracle/bck/

 // ONLY FOR ENTERPRISE INSTALL
 //[oracle@bigdatalite bck]$ unzip p20674616_121300_Generic.zip
 //[oracle@bigdatalite bck]$ cd 20674616/
 //[oracle@bigdatalite 20674616]$ opatch apply -jre /usr/java/latest/
 //[oracle@bigdatalite 20674616]$ cd /home/oracle/bck/

[oracle@bigdatalite bck]$ unzip p20562777_121300_Generic.zip 
[oracle@bigdatalite bck]$ cd 20562777/
[oracle@bigdatalite 20562777]$ opatch apply -jre /usr/java/latest/

Now we need to run the upgrade assistant that will execute some scripts to upgrade our repositories. But in Big Data Lite, the tables of the repository have been compressed, so we first need to uncompress them and rebuild the invalid indexes as David Allan pointed it out on twitter. Here are the SQL queries that will create the DDL statement you need to run if you are also using Big Data Lite VM :

select
 'alter table '||t.owner||'.'||t.table_name||' move nocompress;' q
 from all_tables t
 where owner = 'DEV_ODI_REPO'
 and table_name <> 'SNP_DATA';

select 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';'
 from all_indexes
 where owner = 'DEV_ODI_REPO'
 and status = 'UNUSABLE';

Once it’s done we can start the upgrade assistant :

[oracle@bigdatalite 20562777]$ cd /u01/ODI12c/oracle_common/upgrade/bin
[oracle@bigdatalite bin]$ ./ua

Upgrade Assistant

The steps are quite straightforward so I’ll leave it to you. Here I selected Schemas, but if you have a standalone agent you will have to run it again and select “Standalone System Component Configurations” to upgrade the domain as well.

Before opening ODI Studio we will clear the JDev cache so we are sure everything looks nice.

[oracle@bigdatalite bin]$ rm -rf /home/oracle/.odi/system12.1.3.0.0/

We can now open ODI Studio. Don’t worry the version mentioned there and in the upgrade assistant is still 12.1.3.0.0 but if you can see the new features it has been installed properly.

The last step is to go in the topology and change the driver used for all the Hive Data Server. As all the new LKMs use the new weblogic driver, we need to define the url instead of the existing one.  We simply select “DataDirect Apache Hive JDBC Driver” instead of the existing Apache driver.

And that’s it, we can now enjoy all the new Big Data features in ODI 12c! A big thanks to David Allan and Denis Gray for their technical and licensing help. Stay tuned as I will soon publish a second blog post detailing some features.

Categories: BI & Warehousing

April 21: iBasis Sales Cloud Reference Forum

Linda Fishman Hoyle - Mon, 2015-04-13 11:58
Join us for an Oracle Sales Cloud Customer Reference Forum on Wednesday, April 21, 2015, at 8:00 a.m. PT / 11:00 a.m. ET.

You will hear Katherine Doe, Vice President Commercial Operations at iBasis, talk about why and how the company migrated from Oracle CRM On Demand to Oracle Sales Cloud.

Doe will discuss how iBasis uses Oracle Sales Cloud’s simplified UI and mobile capability to access sales information, even when in remote locations. As a result, the company has been able to optimize sales effectiveness while enabling executive visibility to the pipeline.

iBasis is a global leader in international voice, mobile data and prepaid services for the communications industry.

You can register now to attend the live Forum on Wednesday, April 21, 2015, at 8:00 a.m. PT / 11:00 a.m. ET and learn more from iBasis directly.

Request Support Management Attention (SR Attention)

Chris Warticki - Mon, 2015-04-13 11:40

In today's world, everybody wants the answer yesterday.  Everything seems to require immediate attention.  But, what is requesting support management attention all about? Do you have a Support delivery concern? Are your expectations not being met nor understood? Request for Support Management Attention will help.  The shortest path to least resistance regarding any technical service request is Support Management Attention – first and always.   Until you’ve spoken to a manager from Oracle Support, how does Oracle Support know where/when the service gap is and how to remedy it? 

Side note: (Escalation, formerly known as the Duty Manager Process, has always been defined as speaking with the manager where the Service Request resides.  Escalation has never meant immediate resolution.  It's not a check-box.  Escalation is not the solution, it's a component of the conversation.)  Does that surprise you? Read on.

Here’s how it works;

Step 1 - Insert the template below into the Service Request, including all **** lines. This will ensure correct visibility and content.
******************* Management Attention Request *******************
Reason for request, including business impact of the problem that requires management attention
Business or implementation milestone, critical date(s) (milestone date or resolve by date), along with the type of business or implementation milestone
Name of the customer requesting callback; contact information: phone number, pager, email address
******************* Management Attention Request *******************
Step 2 – Call the Global Support 800#.  You may choose #1 for Existing SR or #2 for New SR.
Step 3 – Request Support Management Attention. "The magical phrase that pays":  Here is my existing Service Request #, I would like to speak with and receive a callback from the Support Manager. This used to be called the Escalation/Duty Manager 9+ years ago.
Step 4 – Your contact information will be verified and the Support Manager will be identified and notified for callback. It’s not a hot-transfer. Oracle Support strives that the Support Manager shall respond with a sense of urgency and contact you back in 30 minutes or less.

Here’s what NOT to do:

  • Don't log a Severity 1 SR and then immediately request to speak to a Support Manager.  Give Support a chance to begin work.
  • Don’t choose the option to speak to the support engineer if your intending to speak with a manager.  Choosing that option will route you to the support engineer, or their voicemail.
  • Don’t call the 800# and request a Severity 1 or Severity increase. Severity 1 is reserved for production down 99% of the time.
  • Don’t update your Service Request to initiate escalation. This is not recommended. Oracle doesn’t have a batch job running to look for keywords in SRs. Call us!  Then, update your Service Request for your own documentation purposes.
  • Don’t call your sales team, account team, or anyone else in your rolodex of Oracle business cards. They can’t solve your technical problem. You might as well try posting to your Facebook friends. There’s no backdoor to this process.
  • Don’t request your support engineer to jump on some bridge call. Bridge calls are for managers to keep busy. Keep the technical team troubleshooting. The motto for bridge calls is; “When all is done and said, a whole lot more is said than ever done!” 
    • If you have such a requirement for a bridge call, the best practice is to document the SR with the bridge call information and then initiate Request for Support Management Attention.  Remember, the engineer is multi-tasking, they aren't waiting around to join a bridge call.  The Support Manager will respond and most likely even join the bridge call and then resource the issue as appropriate.

Here’s how it plays out:  DON’T HANG UP with any manager until you know these 3 things:

  • Management Contact:
    • Once identified, the Support Manager, for the team where the SR resides, will call you back to discuss the technical details and review the Service Request with you.
      • Don't hang up until you have the manager's contact information. (name, email address, office phone)
  • Action Plan:
    • Work up an agreed upon action plan (WHO is going to do WHAT, by WHEN)
    • At this time, discuss any concerns you have. (time to respond, time to resolve, technical direction, key milestones at risk, etc.) Nobody is getting in trouble here. Let us know how we can serve you better.
    • Discuss current status and if the situation deems escalated or not.
    • Discuss appropriate severity
    • As a customer, document the action plan in the Service Request.
      Document, “I just spoke to so-n-so and we agreed to the following”
      The support manager should be doing the same.
  • Communication Plan:
    • Know when the next follow-up will be. Is follow up necessary? Discuss it.

There are two things the Support Manager can do that nobody else in Oracle can - Support Managers have control of the resources and time of the support engineers. They may reassign it to someone else on the team. They may free up the time of the current engineer to devote more time to your problem. They will review what can and can’t be accommodated. That’s why you shouldn’t call anyone else.

If your boss asks you about that Service Request and you tell them that you requested Support Management Attention, remember that they can reply with; Oh, you requested management attention, then who did you speak too? What’s their name? Better yet, let’s contact them and find out the status of what we need to know. Or, come into my office and let’s crack open the Service Request and see the details of what you spoke about.

What if the Service Request has a Bug Associated with it?

Good question. While discussing your Action Plan, request from the Support Manager to coordinate a call with Development Management. Remove the engineers and developers from the mix and go direct to the managers who control both of these resources. Let Development Management know how this bug is affecting your business. That Development Manager will give it to you straight if a fix is feasible or not. Please don't do this for enhancement requests.  Utilize the UserGroups, the ER Voting System and associated CABs and SIGs.

What if I’ve done what you’ve prescribed and it didn’t work?

Nobody is perfect. Oracle strives to achieve 100% satisfaction.

Simply call the 800# again, state either the support manager you’ve spoken too isn’t able to help you, or the request for callback wasn’t returned, then request to speak to the next level of management (Sr. Manager, Director and then VP level, if necessary)

It should be that simple. Really, it is. As a former manager from Database support, we take a lot of pride in, and hold ourselves accountable to this valuable process.

Chris Warticki is member of the Global Customer Management team for Oracle Support Services
Tweeting @cwarticki

Are We Ready for the Apple Watch?

Oracle AppsLab - Mon, 2015-04-13 10:48

So, apparently Apple is launching a watch soon, which has people asking us, i.e. Oracle Applications User Experience (@usableapps), what our strategy is for the watch of watches.

If you read here, you probably already know we’ve been looking at smart watchessuper watches, and wearables of all kinds for a few years. So, the strategy is already in place.

Build a framework that does most of the work and plug new wearables as they come, Google Glass, Android Wear watches, Pebble, Apple Watch, whatever. Then, create glanceable experiences that fit what users want from each device.

Maybe you saw the Glance demo at OpenWorld 2014 in Jeremy’s (@jrwashley) session or at the OAUX Exchange.

IMG_0098

Glance for Oracle Applications Cloud proof of concept apps on Android Wear Samsung Gear Live and Pebble

Ultan (@ultan) has an excellent writeup that will give you the whole scoop. I’ll cherry-pick the money quote:

This is not about designing for any one specific smartwatch. It’s a platform-agnostic approach to wearable technology that enables Oracle customers to get that awesome glanceable, cloud-enabled experience on their wearable of choice.

So, yeah, we have a strategy.

And boom goes the dynamite.

lead lead2 receipt2 receipt webclock webclock2

Find the comments.Possibly Related Posts:

Collaborate 2015 | April 12-16 – Las Vegas, NV

WebCenter Team - Mon, 2015-04-13 10:32


THE WORD IS OUT. There is ONE event that delivers the full spectrum of Oracle Applications and Technology education that you need to boost results all year long. Produced by three independent users groups, COLLABORATE 15: Technology and Applications Forum for the Oracle Community delivers:

  • Over 1000 sessions and panels packed with first-hand experiences, case studies and practical “how-to” content
  • Breaking product news and insider information from Oracle executive management and developers
  • Ample opportunities to build your personal and professional networks, including special interest group (SIG) meetings, geographic networking events and receptions
  • Informative and inspiring keynotes
  • An Exhibitor Showcase packed with products and solutions providers who can help you solve your specific challenges
  • Unique events such as the Women in Technology Forum — connect with key women who are examples of success in a fast-changing environment
  • Collaborate 2015 | April 12-16 – Las Vegas, NV 

Community dinner @ Pedro’s

Pythian Group - Mon, 2015-04-13 08:36

Folks, as usual Pythian is organizing the community dinner. After many years, food, (responsible) drinking and photos, this event has become an important moment for all of us, to know each other better, discuss and have fun.

This year is also the 20th year for MySQL so … YEAAAH let us celebrate, with more food, fun and responsible drinking.

If you had not done it yet … register yourself here: https://www.eventbrite.com/e/the-pythian-mysql-community-pay-your-own-way-dinner-tickets-15692805604

Info about the event:

When: Tuesday April 14, 2015 – 7:00 PM at Pedro’s (You are welcome to show up later, too!)
Where: Pedro’s Restaurant and Cantina – 3935 Freedom Circle, Santa Clara, CA 95054

 

I know, I know … we are that kind of people that decide where to go at the last minute, and every year we do the same, but if you could register, that will help us to organize it better … and c’mon the dinner is on Tuesday … so we are almost there!!!

 

Anyhow, hope to see all of you there, all of you!

Some reference: Menu Eventbrite Pedro

Categories: DBA Blogs

Technology for the Non-Technical

Pythian Group - Mon, 2015-04-13 08:33

I am potentially one of the least technical people in my generation. I’m 30 and I am afraid of my cellphone, my laptop, Netflix, the microwave…. Okay, afraid is maybe a strong word, but baffled by them at the very least.

In high school, while my classmates wrote most of their papers and assignments on the computer, I insisted on writing everything out by hand and only typed it out afterwards if absolutely required. It wasn’t that I had issues with typing – my mom who worked as an administrator for many years made sure that I learned to type from a very young age and I type quickly with a reasonable amount of accuracy. I just felt that writing by hand kept me more “connected” to the words I penned. Simply, my name is Sarah and I am a Luddite.

After high school I studied journalism for a couple of years and then entered the workforce into a number of different jobs, such as in sales and marketing and it became necessary for me to “engage” with technology a little more heavily. Typing articles and assignments slowly became second nature but grocery lists, thank you notes, birthday cards all continued to be written by hand.

For the last few years I’ve been working for technology and IT organizations, and for the last 14 months I’ve been working with Pythian, a leading IT services provider specializing in data infrastructure management. That was a big leap for me. Not only was I required to use technology constantly in my day-to-day (Smartphone, CRM system, soft phone, multiple email interfaces ACK!), but I also needed to do a lot more than dip my toes into some fairly intense technical knowledge to gain an understanding of our client base and what solutions would be most appropriate for the people I speak to every day. These people are Chief Information Officers, Chief Technology Officers’s and Vice Presidents of Information Technology for companies that are incredibly data-dependent. The quality and security of their data management directly affects their revenue and it’s critical that it is handled with a great amount of expertise and attention to detail. Kind of intimidating.

I have spent the last year wrapping myself in terms like NoSQL, non-relational database, Hadoop, MongoDB, SQL Server and Oracle. Do I have a perfect understanding of the benefits and draw-backs of each of these yet? No. What I do have is a great network of technical geniuses who work with me who have spent their careers becoming experts in their respective technologies. I know who the best resources are and how to connect with them to get the best answers and solutions. I’m very lucky to work at company that is incredibly transparent – questions are always welcomed and answered. I sit sandwiched between the offices of the Chief Revenue Officer and the CEO and Founder of our organization and while both are incredibly busy people, they are also happy to answer questions and share their insights and energy with anyone here.

All of our technical resources are just an instant message away and can often answer my questions in a few concise lines. So, while I am still monstrously uncomfortable with tasks like defragging (sounds like organized Fraggle removal to me) my computer or resetting my smartphone when it acts up, I am coming along slowly, in baby steps – an IT late-bloomer you could say – and it’s all much less painful than I ever feared it would be.

Categories: DBA Blogs

APEX 5.0: New Plug-in Attribute Types

Patrick Wolf - Mon, 2015-04-13 07:03
Besides the Plug-In Attribute Enhancements described in my previous posting, we have also added new Attribute Types in Oracle APEX 5.0 for increased usability in Page Designer. Link to Target Page/Url If your Plug-in had to exposed a Link attribute … Continue reading →
Categories: Development

My thoughts on the Resilience of Cassandra

Pythian Group - Mon, 2015-04-13 06:32

This blog is a part 1 of a 2 in a series. This will be different from my previous blogs, as this is more about some decisions you can make with Cassandra regarding the resilience of your system. I will talk deeply about this topic in the upcoming Datastax Days in London (https://cassandradaylondon2015.sched.org/), this is more of an introduction!

TL;DR: Cassandra is tough!

Cassandra presents itself as a “Cassandra delivers continuous availability, linear scalability, and operational simplicity across many commodity servers with no single point of failure, along with a powerful data model designed for maximum flexibility and fast response times.“ (http://docs.datastax.com/en/cassandra/2.0/cassandra/gettingStartedCassandraIntro.html). In a production system, having your persistence layer failure tolerant is a big thing. Even more so when you can make it resilient to full locations failure through geographic replication (and easily).

As in any production system you need to plan for failure. Should we blindly trust in Cassandra resilience and forget about the plan because “Cassandra can handle it”? By reading the documentation, some may think that by having several data centers and a high enough replication factor we are covered. In part this is true. Cassandra will handle servers down, even a full DC (or several!) down. But, anyway, you should always prepare for chaos! Failure will increase pressure on your remaining servers, latency will increase, etc. And when things get up again, will it just work? Getting all data in sync, are you ready for that? Did you forgot about gc_grace_seconds? There are lots of variables and small details that can be forgotten if you don’t plan ahead. And then in the middle of a problem, it will not help having those details forgotten!

My experience tells me that you must take Cassandra failures seriously, and plan for them! Having a B plan is never a bad thing, and a C even. Also, make sure those plans work! So for this short introduction I will leave a couple of recommendations:

  • Test your system against Cassandra delivering a bad service (timeouts, high latency, etc).
  • Set a “bare minimum” for your system to work (how low can we go on consistency, for example).
  • Test not only your system going down, but also prepare for the coming up!
  • Keep calm! Cassandra will help you!

Overall, Cassandra is a tough and robust system. I’ve had major problems with network, storage, Cassandra itself, etc. And in the end Cassandra not only survived, it gave me no downtime. But with every problem I had, it increased my knowledge and awareness of what I could expect. This lead to planning for major problems (which did happen) and this combined with the natural resilience of Cassandra made me go through those events without downtime.

Fell free to comment/discuss about it, in the comment section below! Juicy details will be left for London!

Categories: DBA Blogs

Not Exists

Jonathan Lewis - Mon, 2015-04-13 05:51

The following requirement appeared recently on OTN:


=========================================================================================================
I have a following query and want to get rid of the "NOT EXISTS' clause without changing the end results.

SELECT   A.c,
         A.d,
         A.e,
         A.f
  FROM   A
WHERE   NOT EXISTS (SELECT   1
                       FROM   B
                      WHERE   B.c = A.c AND B.d = A.d AND B.e = A.e);
===========================================================================================================

Inevitably this wasn’t the problem query, and almost inevitably the OP was asking us how to implement a solution which wasn’t appropriate for a problem that shouldn’t have existed. Despite this it’s worth spending a little time to take the request at its face value and look at the sort of thing that could be going on.

First, of course, you cannot get rid of the “not exists” clause, although you may be able to make it look different. If you want “all the rows in A that are not referenced in B” then you HAVE to examine all the rows in A, and you have to do some sort of check for each row to see whether or not it exists in B. The only option you’ve got for doing something about the “not exists” clause is to find a way of making it as a cheap as possible to implement.

A couple of people came up with suggestions for rewriting the query to make it more efficient. One suggested writing it as a “NOT IN” subquery, but it’s worth remembering that the optimizer may cheerfully transform a “NOT IN” subquery to a “NOT EXISTS” subquery if it’s legal and a manual rewrite may overlook the problem of NULLs; another suggested rewriting the query as an outer join, but again it’s worth remembering that the optimimzer may transform a “NOT EXISTS” subquery to an “ANTI-JOIN” – which is a bit like an outer join with filter, only more efficient. So, before suggesting a rewrite, it’s worth looking at the execution plan to see what the optimizer is doing just in case it’s doing something silly. There are two options – anti-join or filter subquery.

Here, with code I’ve run under 10.2.0.5 to match the OP, is a demonstration data set, with the two plans you might expect to see – first, some the data:


execute dbms_random.seed(0)

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        trunc(dbms_random.value(0,4))           c,
        trunc(dbms_random.value(0,5))           d,
        trunc(dbms_random.value(0,300))         e,
        rownum                                  f,
        rpad('x',100)                   padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6
;

create table t2
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        trunc(dbms_random.value(0,4))           c,
        trunc(dbms_random.value(0,5))           d,
        trunc(dbms_random.value(0,300))         e,
        rownum                                  f,
        rpad('x',100)                   padding
from
        generator       v1,
        generator       v2
where
        rownum <= 24000
;

create index t1_i1 on t1(c,d,e);
create index t2_i1 on t2(c,d,e);

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

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

The OP had followed up their original query with a claim that “Table A holds 100 million rows and table B holds 24,000″ – that’s a lot of checks (if true) and you ought to be asking how quickly the OP expects the query to run and how many of the 100 M rows are going to survive the check. I’ve set up just 1M rows with 6,000 distinct values for the column combination (c,d,e), and a reference table with 24,000 rows which are likely to include most, but not all, of those 6,000 combinations.

Rather than generate a very large output, I’ve written a query that generates the required data set, then counts it:


select
        max(f), count(*)
from (
        SELECT   /*+ no_merge */
                 A.c,
                 A.d,
                 A.e,
                 A.f
          FROM   t1 A
        WHERE   NOT EXISTS (SELECT   /* no_unnest */
                                      1
                               FROM   t2 B
                              WHERE   B.c = A.c AND B.d = A.d AND B.e = A.e)
)
;

This took about 0.35 seconds to run – aggregating roughly 14,500 rows from 1M. The plan was (as I had expected) based on a (right) hash anti join:


---------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |     1 |    13 |  2183   (5)| 00:00:11 |
|   1 |  SORT AGGREGATE         |       |     1 |    13 |            |          |
|   2 |   VIEW                  |       |   999K|    12M|  2183   (5)| 00:00:11 |
|*  3 |    HASH JOIN RIGHT ANTI |       |   999K|    23M|  2183   (5)| 00:00:11 |
|   4 |     INDEX FAST FULL SCAN| T2_I1 | 24000 |   234K|    11  (10)| 00:00:01 |
|   5 |     TABLE ACCESS FULL   | T1    |  1000K|    14M|  2151   (4)| 00:00:11 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."C"="A"."C" AND "B"."D"="A"."D" AND "B"."E"="A"."E")

Oracle has built an in-memory hash table from the 24,000 rows in t2, then scanned the t1 table, probing the hash table with each row in turn. That’s 1M probe in less than 0.35 seconds. You ought to infer from this that most of the time spent in the original query should have been spent scanning the 100M rows, and only a relatively small increment appear due to the “not exists” clause.

You’ll notice, though that there was a comment in my subquery with the /* no_unnest */ hint embedded – if I change this from a comment to a hint (/*+ */) I should get a plan with a filter subquery, and maybe that’s what’s happening to the OP for some odd reason. Here’s the plan:


------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |    13 | 15166   (1)| 00:01:16 |
|   1 |  SORT AGGREGATE      |       |     1 |    13 |            |          |
|   2 |   VIEW               |       |   999K|    12M| 15166   (1)| 00:01:16 |
|*  3 |    FILTER            |       |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1    |  1000K|    14M|  2155   (4)| 00:00:11 |
|*  5 |     INDEX RANGE SCAN | T2_I1 |     4 |    40 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "B"
              WHERE "B"."E"=:B1 AND "B"."D"=:B2 AND "B"."C"=:B3))
   5 - access("B"."C"=:B1 AND "B"."D"=:B2 AND "B"."E"=:B3)

The query took 1.65 seconds to complete. (And re-running with rowsource execution statistics enabled, I found that the subquery had executed roughly 914,000 times in that 1.65 seconds). Even if the original query had used the filter subquery plan the subquery shouldn’t have made much difference to the overall performance. Of course if T2 didn’t have that index on (c,d,e) then the filter subquery plan would have been much more expensive – but then, we would really have expected to see the hash anti-join.

If you’re wondering why the subquery ran 914,000 times instead of 1M times, you’ve forgotten “scalar subquery caching”.  The session caches a limited number of results from subquery execution as a query runs and may be able to use cached results (or simply a special “previous-execution” result) to minimise the number of executions of the subquery.

Did you notice the index I created on t1(c,d,e) ? If I drive the query through this index I’ll access all the rows for a given combination of (c,d,e) one after the other and only have to run the subquery once for the set. To make this happen, though, I’ll have to declare one of the columns to be NOT NULL, or add a suitable “column is not null” predicate to the query; and then I’ll probably have to hint the query anyway:


select
        max(f)
from (
        SELECT   /*+ no_merge index(a) */
                 A.c,
                 A.d,
                 A.e,
                 A.f
          FROM   t1 A
        WHERE   NOT EXISTS (SELECT   /*+ no_unnest */
                                      1
                               FROM   t2 B
                              WHERE   B.c = A.c AND B.d = A.d AND B.e = A.e)
        and     c is not null
)
;

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    13 | 65706   (1)| 00:05:29 |
|   1 |  SORT AGGREGATE               |       |     1 |    13 |            |          |
|   2 |   VIEW                        |       |   999K|    12M| 65706   (1)| 00:05:29 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    | 50000 |   732K| 52694   (1)| 00:04:24 |
|*  4 |     INDEX FULL SCAN           | T1_I1 | 50000 |       |  2869   (2)| 00:00:15 |
|*  5 |      INDEX RANGE SCAN         | T2_I1 |     4 |    40 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C" IS NOT NULL AND  NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM
              "T2" "B" WHERE "B"."E"=:B1 AND "B"."D"=:B2 AND "B"."C"=:B3))
   5 - access("B"."C"=:B1 AND "B"."D"=:B2 AND "B"."E"=:B3)

Re-running this code with rowsource execution statistics enabled showed that the subquery ran just 6,000 times (as expected) – for a total run time that was slightly faster than the hash anti-join method (0.17 seconds – but I do have a new laptop using SSD only, with a 3.5GHz CPU and lots of memory).

Every which way, if we can get reasonable performance from the underlying table access there’s no way that introducing a “NOT EXISTS” ought to be a disaster. The worst case scenario – for some reason Oracle chooses to run a filter subquery plan and the appropriate index hasn’t been created to support it.

Footnote:

Of course, table A didn’t really exist, it was a three table join; and it didn’t produce 100M rows, it produced anything between zero and 5 million rows, and the effect of the subquery (which correlated back to two of the joined tables) was to leave anything between 0 and 5 million rows. And (apparently) the query was quick enough in the absence of the subquery (producing, for example, 1 million rows in only 5 minutes), but too slow with the subquery in place.

But that’s okay. Because of our tests we know that once we’ve produced a few million rows it takes fractions of a second more to pass them through a hash table with an anti-join to deal with the “not exists” subquery; and I doubt if we have to play silly games to push the data through a filter subquery plan in the right order to squeeze a few extra hundredths of a second from the query.

If the OP is happy with the basic select statement before the “not exists” subquery, all he has to do is take advantage of a no_merge hint:


select  {list of columns}
from
        (
        select /*+ no_merge */ .... rest of original query
        )    v1
where
        not exists (
                select  null
                from    b
                where   b.c = v1.c and b.d = v1.d and b.e = v1.e
        )
;

You’re probably wondering why the OP currently sees a performance problem as the subquery is added. The best guess is that the subquery has introduce a “magic 5% fudge factor” to the arithmetic (did you notice the cardinality of t1 dropping to 50,000 from 1M in the plan above) and made it pick a worse execution plan for the rest of the query. We can’t tell, though, since the OP hasn’t yet given us the information that would allow us to see what’s going wrong.


Cloning a PDB from a standby database

Yann Neuhaus - Mon, 2015-04-13 05:14

Great events like IOUG Collaborate is a good way to meet experts we know through blogs, twitter,etc. Yesterday evening, with nice music in the background, I was talking with Leighton Nelson about cloning PDB databases. Don't miss his session today if you are in Las Vegas. The big problem with PDB cloning is that the source must be read-only. The reason is that it works like transportable tablespaces (except that it can transport the datafiles through database link and that we transport SYSTEM as well instead of having to import metadata). There is no redo shipping/apply here, so the datafiles must be consistent.

Obviously, being read-only is a problem when you want to clone from production.

But if you have a standby database, can you open it read-only and clone a pluggable database from there? From what we know, it should be possible, but better to test it.

Here is my source - a single tenant standby database opened in read-only:

SQL> connect sys/oracle@//192.168.78.105/STCDB as sysdba
Connected.
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
STCDB     READ ONLY            PHYSICAL STANDBY

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
STDB1                          MOUNTED

Then from the destination I define a database link to it:

SQL> connect sys/oracle@//192.168.78.113/CDB as sysdba
Connected.
SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CDB       READ WRITE

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB                            READ WRITE

SQL>
SQL> create database link DBLINK_TO_STCDB connect to system identified by oracle using '//192.168.78.105/STCDB';

Database link created.

and create a pluggable database from it:

SQL> create pluggable database STDB2 from STDB1@DBLINK_TO_STCDB;

Pluggable database created.

SQL> alter pluggable database STDB2 open;

Pluggable database altered.

So yes. This is possible. And you don't need Active Data Guard for that. As long as you can stop the apply for the time it takes to transfer the datafiles, then this is a solution for cloning. Of course, just do one clone and if you need others then you can do it from that first clone. And within the same PDB they can be thin clones if you can use snapshots.

Ok, It's 5 a.m here. As usual, the jetlag made me awake awake a bit early, so that was a good occasion to test what we have discussed yesterday...

Ask the Real Experts

Denes Kubicek - Mon, 2015-04-13 01:39
On Wednesday you will have a chance to ask the APEX developers anything you would like to know about APEX 5.0. See this posting from Patrick Wolf or go directly to the registration form in order to get a link.

Categories: Development

Combined ACCESS And FILTER Predicates - Excessive Throw-Away

Randolf Geist - Mon, 2015-04-13 00:00
Catchy title... Let's assume the following data setup:

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

create table t2
as
select
rownum as id
, 1 as id2
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e4
;

create table t3
as
select
rownum as id
, 1 as id2
, rpad('x', 100) as filler
from
dual
connect by
level <= 1e4
;

exec dbms_stats.gather_table_stats(null, 't1')

exec dbms_stats.gather_table_stats(null, 't2')

exec dbms_stats.gather_table_stats(null, 't3')

-- Deliberately wrong order (FBI after gather stats) - the virtual columns created for this FBI don't have statistics, see below
create index t2_idx on t2 (case when id2 = 1 then id2 else 1 end, case when id2 = 2 then id2 else 1 end, filler, id);

create index t3_idx on t3 (id, filler, id2);
And the following execution plan (all results are from 12.1.0.2 but should be applicable to other versions, too):

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 1416K| 132 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 10000 | 1416K| 132 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 292K| 44 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 |
----------------------------------------------------------------------------
How long would you expect it to run to return all rows (no tricks like expensive regular expressions or user-defined PL/SQL functions)?

Probably should take just a blink, given the tiny tables with just 10000 rows each.

However, these are the runtime statistics for a corresponding execution:

| | | |
| |DATABASE |CPU |
|DURATION |TIME |TIME |
|------------|------------|------------|
|+0 00:00:23 |+0 00:00:23 |+0 00:00:23 |
| | | |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | 6 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 | 0 | | | | | | |
|* 1 | 0 | 5 | HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 | 1401K | 2 | 23 | 22 | ##### ############## | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(22) |
| 2 | 1 | 1 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 3 | 1 | 4 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1930K | | | | | | |
| 4 | 3 | 2 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 5 | 3 | 3 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
How is it possible to burn more than 20 seconds of CPU time with that execution plan?

The actual rows produced correspond pretty much to the estimated cardinalities (except for the final hash join), so that doesn't look suspect at first glance.
What becomes obvious from the SQL Monitoring output is that all the time is spent on the hash join operation ID = 1.

Of course at that point (at the latest) you should tell me off for not having you shown the predicate section of the plan and the corresponding query in first place.

So here is the predicate section and the corresponding query:

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

1 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END =CASE
"T2"."ID2" WHEN 1 THEN "T2"."ID2" ELSE 1 END AND CASE "T1"."ID2" WHEN
2 THEN "T1"."ID2" ELSE 1 END =CASE "T2"."ID2" WHEN 2 THEN "T2"."ID2"
ELSE 1 END )
filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )

3 - access("T3"."ID"="T1"."ID")


select /*+
leading(t1 t3 t2)
full(t1)
full(t3)
use_hash(t3)
swap_join_inputs(t3)
full(t2)
use_hash(t2)
swap_join_inputs(t2)
*/
t1.*
--, t3.id2
--, case when t1.id > t2.id then t1.id else t2.id end
from
t1
, t2
, t3
where
1 = 1
--
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
--
and t3.id = t1.id
and t3.id2 = case when t1.id > t2.id then t1.id else t2.id end
;
There are two important aspects to this query and the plan: First, the join expression (without corresponding expression statistics) between T1 and T2 is sufficiently deceptive to hide from the optimizer that in fact this produces a cartesian product (mimicking real life multi table join expressions that lead to bad estimates) and second, the table T3 is joined to both T1 and an expression based on T1 and T2, which means that this expression can only be evaluated after the join to T1 and T2.
With the execution plan shape enforced via my hints (but could be a real life execution plan shape preferred by the optimizer) T3 and T1 are joined first, producing an innocent 10K rows row source, which is then joined to T2. And here the accident happens inside the hash join operation:

If you look closely at the predicate section you'll notice that the hash join operation has both, an ACCESS operation and a FILTER operation. The ACCESS operation performs based on the join between T1 and T2 a lookup into the hash table, which happens to be a cartesian product, so produces 10K times 10K rows, and only afterwards the FILTER (representing the T3 to T1/T2 join expression) is applied to these 100M rows, but matching only a single row in my example here, which is what the A-Rows shows for this operation.

So the point is that this excessive work and FILTER throwaway isn't very well represented in the row source statistics. Ideally you would need one of the following two modifications to get a better picture of what is going on:

Either the FILTER operator should be a separate step in the plan, which in theory would then look like this:

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 |
|* 1a| FILTER | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 |
|* 1b| HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 100M |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K |
---------------------------------------------------------------------------------------------

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

1a- filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )
1b- access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END =CASE
"T2"."ID2" WHEN 1 THEN "T2"."ID2" ELSE 1 END AND CASE "T1"."ID2" WHEN
2 THEN "T1"."ID2" ELSE 1 END =CASE "T2"."ID2" WHEN 2 THEN "T2"."ID2"
ELSE 1 END )
3 - access("T3"."ID"="T1"."ID")
Which would make the excess rows produced by the ACCESS part of the hash join very obvious, but is probably for performance reasons not a good solution, because then the data would have to flow from one operation to another one rather than being processed within the HASH JOIN operator, which means increased overhead.

Or an additional rowsource statistics should be made available:

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows|AE-Rows|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 132 (100)| | 1 | 1 | 1 |
|* 1 | HASH JOIN | | 10000 | 1191K| 132 (0)| 00:00:01 | 1 | 1 | 100M |
| 2 | TABLE ACCESS FULL | T2 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | 10K |
|* 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 10K |
| 4 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | 10K |
| 5 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | 10K |
----------------------------------------------------------------------------------------------------
Which I called here "Actually evaluated rows" and in addition to this case here of combined ACCESS and FILTER operations could also be helpful for other FILTER cases, for example even for simple full table scan to see how many rows were evaluated, and not only how many rows matched a possible filter (what A-Rows currently shows).

In a recent OTN thread this topic came up again, and since I also came across this phenomenon a couple of times recently I thought to put this note together. Note that Martin Preiss has submitted a corresponding database idea on the OTN forum.

Expanding on this idea a bit further, it could be useful to have an additional "Estimated evaluated rows (EE-Rows)" calculated by the optimizer and shown in the plan. This could also be used to improve the optimizer's cost model for such cases, because at present it looks like the optimizer doesn't consider additional FILTER predicates on top of ACCESS predicates when calculating the CPU cost of operations like HASH JOINs.

Note that this problem isn't specific to HASH JOIN operations, you can get similar effects with other join methods, like NESTED LOOP joins, or even simple INDEX lookup operations, where again the ACCESS part isn't very selective but only the FILTER applied afterwards filters matching rows.

Here are some examples with the given setup:

select /*+
leading(t1 t3 t2)
full(t1)
full(t3)
use_hash(t3)
swap_join_inputs(t3)
index(t2)
use_nl(t2)
*/
t1.*
--, t3.id2
--, case when t1.id > t2.id then t1.id else t2.id end
from
t1
, t2
, t3
where
1 = 1
--
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
--
and t3.id = t1.id
and t3.id2 = case when t1.id > t2.id then t1.id else t2.id end
;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | 6 | SELECT STATEMENT | | | | 10090 (100)| | 1 | 1 | | | | | | | |
| 1 | 0 | 5 | NESTED LOOPS | | 10000 | 1416K| 10090 (1)| 00:00:01 | 1 | 1 | | | | | | | |
|* 2 | 1 | 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1890K | | | | | | |
| 3 | 2 | 1 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 4 | 2 | 2 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 5 | 1 | 4 | INDEX RANGE SCAN | T2_IDX | 1 | 30 | 1 (0)| 00:00:01 | 10K | 1 | | 3 | 33 | 32 | ################### | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(32) |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("T3"."ID"="T1"."ID")
5 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END
="T2"."SYS_NC00004$" AND CASE "T1"."ID2" WHEN 2 THEN "T1"."ID2" ELSE 1
END ="T2"."SYS_NC00005$")
filter("T3"."ID2"=CASE WHEN ("T1"."ID">"T2"."ID") THEN
"T1"."ID" ELSE "T2"."ID" END )



select /*+
leading(t1 t3 t2)
full(t1)
full(t3)
use_hash(t3)
swap_join_inputs(t3)
index(t2)
use_nl(t2)
*/
max(t1.filler)
--, t3.id2
--, case when t1.id > t2.id then t1.id else t2.id end
from
t1
, t2
, t3
where
1 = 1
--
and case when t1.id2 = 1 then t1.id2 else 1 end = case when t2.id2 = 1 then t2.id2 else 1 end
and case when t1.id2 = 2 then t1.id2 else 1 end = case when t2.id2 = 2 then t2.id2 else 1 end
--
and t3.id = t1.id
and t2.filler >= t1.filler
and t2.id = case when t1.id2 > t3.id2 then t1.id2 else t3.id2 end

;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Pid | Ord | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Execs | A-Rows| PGA | Start | Dur(T)| Dur(A)| Time Active Graph | Activity Graph ASH | Top 5 Activity ASH |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | | 7 | SELECT STATEMENT | | | | 20092 (100)| | 1 | 1 | | | | | | | |
| 1 | 0 | 6 | SORT AGGREGATE | | 1 | 223 | | | 1 | 1 | | | | | | | |
| 2 | 1 | 5 | NESTED LOOPS | | 1 | 223 | 20092 (1)| 00:00:01 | 1 | 10K | | | | | | | |
|* 3 | 2 | 3 | HASH JOIN | | 10000 | 1123K| 88 (0)| 00:00:01 | 1 | 10K | 1900K | | | | | | |
| 4 | 3 | 1 | TABLE ACCESS FULL| T3 | 10000 | 70000 | 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
| 5 | 3 | 2 | TABLE ACCESS FULL| T1 | 10000 | 1054K| 44 (0)| 00:00:01 | 1 | 10K | | | | | | | |
|* 6 | 2 | 4 | INDEX RANGE SCAN | T2_IDX | 1 | 108 | 2 (0)| 00:00:01 | 10K | 10K | | 2 | 34 | 34 | #################### | @@@@@@@@@@@@@@@@@@@ (100%) | ON CPU(34) |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("T3"."ID"="T1"."ID")
6 - access(CASE "T1"."ID2" WHEN 1 THEN "T1"."ID2" ELSE 1 END
="T2"."SYS_NC00004$" AND CASE "T1"."ID2" WHEN 2 THEN "T1"."ID2" ELSE 1
END ="T2"."SYS_NC00005$" AND "T2"."FILLER">="T1"."FILLER" AND
"T2"."ID"=CASE WHEN ("T1"."ID2">"T3"."ID2") THEN "T1"."ID2" ELSE
"T3"."ID2" END AND "T2"."FILLER" IS NOT NULL)
filter("T2"."ID"=CASE WHEN ("T1"."ID2">"T3"."ID2") THEN
"T1"."ID2" ELSE "T3"."ID2" END )

The former one exhibits exactly the same problem as the HASH JOIN example, only that the FILTER is evaluated in the inner row source of a NESTED LOOP join after the index access operation.

The latter one shows as variation the classic partial "index access" due to a range comparison in between - although the entire expression can be evaluated on index level, the access part matches every index entry, so the range scan actually needs to walk the entire index at each loop iteration and the FILTER is then applied to all the index values evaluated.

Updated Technical Best Practices

Anthony Shorten - Sun, 2015-04-12 22:52

A minor update has been added to the Technical Best Practices in response to some customer feedback. There are some instructions on how to build a data model for data modelling tools such as SQL Developer Data Modeler and Enterprise Manager's Data Masking and Subsetting Pack (formerly known as Test Data Management Pack).

The SQL to build the model has been updated to include some additional constraints available in OUAF 4.2 and above.

Customers intending to build the data model using this technique should refer to the updated instructions in Technical Best Practices for Oracle Utilities Application Framework Based Products (Doc Id: 560367.1) available from My Oracle Support.

In what ways is buckthorn harmful?

FeuerThoughts - Sun, 2015-04-12 16:54
I spent 10-15 hours a week in various locations of still-wooded Chicago (and now nearby Lincolnwood) cutting down buckthorn. Some people have taken me to task for it ("Just let it be, let nature take it's course, etc.). So I thought I would share this excellent, concise sum up of the damage that can be wrought by buckthorn.
And if anyone lives on the north side of Chicago and would like to help out, there is both "heavy" work (cutting large trees and dragging them around) and now lots of "light" work (clipping the new growth from the stumps from last year's cutting - I don't use poison). 
It's great exercise and without a doubt you will be helping rescue native trees and ensure that the next generation of those trees will survive and thrive!
From The Landscape Guys
Buckthorn should be on America's "Most Wanted" list, with its picture hanging up in every US Post Office! Here are a few of the dangers of Buckthorn:
a) Buckthorn squeezes out native plants for nutrients, sunlight, and moisture. It literally chokes out surrounding healthy trees and makes it impossible for any new growth to take root under its cancerous canopy of dense vegetation.
b) Buckthorn degrades wildlife habitats and alters the natural food chain in and growth of an otherwise healthy forest. It disrupts the whole natural balance of the ecosystem.
c) Buckthorn can host pests like Crown Rust Fungus and Soybean Aphids. Crown Rust can devastate oat crops and a wide variety of other grasses. Soybean Aphids can have a devastating effect on the yield of soybean crops. Without buckthorn as host, these pests couldn't survive to blight crops.
d) Buckthorn contributes to erosion by overshadowing plants that grow on the forest floor, causing them to die and causing the soil to lose the integrity and structure created by such plants.
e) Buckthorn lacks "natural controls" like insects or diseases that would curb its growth. A Buckthorn-infested forest is too dense to walk through, and the thorns of Common Buckthorn will leave you bloodied.
f) Buckthorn attracts many species of birds (especially robins and cedar waxwings) that eat the berries and spread the seeds through excrement. Not only are the birds attracted to the plentiful berries, but because the buckthorn berries have a diuretic and cathartic effect, the birds pass the seeds very quickly to the surrounding areas of the forest. This makes Buckthorn spread even more widely and rapidly, making it harder for us to control and contain.
Categories: Development

RAC Attack! was another great success at C15LV

Yann Neuhaus - Sun, 2015-04-12 14:49

The RAC Attack  - install a RAC in your own laptop - is a great success at Las Vegas.

The idea is to help people follow the RAC Attack cookbook which is available at:

http://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_12c/Hardware_Requirements

It is a complex configuration and there is always problems to troubleshoot:

  • get Virtual Box be able to run a 64-bits guest, and that might involve some BIOS settings
  • be able to install VirtualBox, and we have people with their company laptop where some security policies makes things difficule
  • Network configuration is not simple and any misconfiguration will make things more difficult later

So it is a very good exercise for troubleshooting.

The organisation way excellent: Organisation by Ludovico Caldara, infrastructure by Erik Benner, food sponsored by OTN, and Oracle software made available on USB sticks thanks to Markus Michalewicz. Yes the RAC Product Manager did the racattack installation.

 It's also a very good networking event where people meet people around the technology, thanks to IOUG Collaborate.

More Ninjas graduating the Dojo! #racattack @ioug @racsig #c15lv @OracleDBDev @Mythics pic.twitter.com/M4pdb8AHf9

— Erik Benner (@Erik_Benner) April 12, 2015

When people manage to get a VM with the OS installed, they can get the red tee-shirt. Look at the timelapse of the full day and you will see more and more red T-shirts: https://www.youtube.com/watch?v=mqlhbR7dYm0

Do you wonder why we are so happy to see people having only the OS installed? Because it's the most difficult part. Creating a cluster on a laptop is not easy. You have to create the VM, you have to setup networking, DNS, etc.

Once this setup is good, then installing Grid Infrastructure and Database is straightforward with graphical installer.

GSV 2015 Review

Michael Feldstein - Sun, 2015-04-12 11:06

By Michael FeldsteinMore Posts (1024)

The basic underlying theme of the 2015 GSV Ed Innovation conference is “more is more.” There were more people, more presentations, more deal-making, more celebrities…more of everything, really. If you previously thought that the conference and the deal-making behind it was awesome, you would probably find this year to be awesomer. If you thought it was gross, you would probably think this year was grosser. Overall, it has gotten so big that there is just too much to wrap your head around. I really don’t know how to summarize the conference.

But I can give some observations and impressions.

More dumb money: Let’s start with a basic fact: There is more money coming into the market.

If there is more total money coming in, then it stands to reason that there is also more dumb money coming in. I definitely saw plenty of stupid products that were funded, acquired, and/or breathlessly covered. While it wasn’t directly conference-related, I found it apropos that Boundless was acquired right around the time of the conference. I have made my opinions about Boundless clear before. I have no opinion about Valore’s decision to acquire them, in large part because I don’t know the important details. It might make sense for a company like Valore to acquire Boundless for their platform—if the price is right. But this doesn’t appear to be a triumph for Boundless or their investors. To the contrary, it smells like a bailout of Boundless’ investors to me, although I admit that have no evidence to prove that. If the company were doing so awesomely, then I don’t think the investors would have sold at this point. (Boundless, in typical Boundless fashion, characterizes the transaction as a “merger” rather than an “acquisition.” #Winning.) Of course, you wouldn’t know that this is anything less than the total takeover of education from the breathless press coverage. Xconomy asks whether the combined company will be the “Netflix of educational publishing.”

Really?

So yeah, there’s plenty of dumb money funding dumb companies, aided and abetted by dumb press coverage. But is there proportionally more dumb money, or is there just more dumb money in absolute terms as part of the overall increase in investment? This is an important question, because it is a strong indicator of whether the idiocy is just part of what comes when an immature industry grows or whether we are in a bubble. This particular kind of market analysis is somewhat outside my wheelhouse, but my sense, based on my fragmented experience of the conference added to other recent experiences and observations, is that it’s a bit of both. Parts of the market have clearly gotten ahead of themselves, but there also are some real businesses emerging. Unsurprisingly, some of the biggest successes are not the ones that are out to “disrupt” education. Apparently the ed tech company that got the most money last year was Lynda.com which, in addition to being a good bet, doesn’t really compete head-on with colleges (and, in fact, sells to schools). Phil has written a fair bit about 2U; that company only exists because they have been able to get high-end schools to trust them with their prestige brands. This brings me to my next observation:

More smart money: 2U is a good example of a company that, if you had described it to me in advance, I probably would have told you that it never could work. The companies that do well are likely to be the ones that either figure out an angle that few people see coming or execute extremely well (or, in 2U’s case, both).[1] 2U is also one of very few ed tech that have made it to a successful IPO (although there are more that have been successfully sold to a textbook publisher, LMS vendor, or other large company). I am seeing more genuinely interesting companies getting funding and recognition. Three recent examples: Lumen Learning getting angel funding, Acrobatiq winning the ASU-GSV Return on Education Award, and Civitas closing Series C funding a couple of months ago. I also had more interesting and fewer eye-rolling conversations at the conference this year than in past years. Part of that is because my filters are getting better, but I also think that the median educational IQ of the conference attendees has risen a bit as at least some of the players learn from experience.

Textbooks are dead, dead, dead: McGraw Hill Education CEO David Levin was compelled to start his talk by saying, essentially, “Yeah yeah yeah, everybody hates textbooks and they are dying as a viable business. We get it. We’re going to have all digital products for much less money than the paper textbooks very soon, and students will be able to order the paper books for a nominal fee.” He then went on to announce a new platform where educators can develop their own content.

Pay heed, OER advocates.

I saw Mark Cuban: He has noticeably impressive pecs. Also,

Arizona is nicer than Massachusetts in early April.

  1. Corollary: Companies trying to be the “Netflix of education” or the “Uber of education” or the “Facebook of education” will usually turn out to be as ridiculous—meaning “worthy of ridicule”—as they sound.

The post GSV 2015 Review appeared first on e-Literate.

SQLCL – The New SQL*Plus

The Anti-Kyte - Sun, 2015-04-12 10:33

To borrow a well-known saying, One-Day International Cricket is a game played by two sides for 100 overs…and then the Aussies win the World Cup.
Something else that doesn’t seem to change much over time is SQL*Plus. The command line interface to Oracle Databases has been around, in it’s current guise, since 1985.
Whilst there have been some changes here and there, it’s basic functionality has remained largely unchanged over the intervening 30 years.
Now, however, it looks like things are about to change as Oracle lavish some attention on the noble CLI.

You may be wondering how this is in any way relevant in the modern world of GUI development.
Well, there are still some things that you need the command line for.
Interactive connection to a database from a server that’s not running a desktop environment would be one.

More common though, are those jobs that need to run unattended. These will include batch jobs managed by a scheduling tool external to the Oracle RDBMS, such as Autosys, or even good old CRON.
Increasingly, it will also include jobs that are initiated as part of Continuous Integration or Release Management testing.

SQL*Plus for the 21st Century is currently going by the name of SQLCL (SQL Command Line). It has also been known as SDSQL (SQLDeveloper SQL) and even SQL*Plus++ (my personal favourite).

Whilst the currently available versions of SQLCL are very much in the Early Adopter stage, there is enough there to show the direction in which things are moving.
Whilst the decision has been taken to ensure that SQLCL is fully backward-compatible with the current SQL*Plus, some of the new features may well have significant implications in the way that the Oracle CLI is used in the future.

What I’m going to cover here is :

  • How SQLCL differs “structurally” from SQL*Plus
  • Improvements in command-line interaction incorporating Linux-like and IDE features, including glorious technicolour!

All of which leaves the ageing geek in me unashamedly excited.

Before I go on, I should take this opportunity to say thanks to Jeff Smith and the team for answering the questions I had about some of the new features.

Download and Installation

The first thing you’ll notice about SDSQL is that, unlike the Oracle Client of which SQL*Plus is a component, it is a single file.
This is a pretty good start as you don’t have to figure out which files you need to download before you get going.

Instead, you simply need to head over to the SQLDeveloper Download Page and download Command Line SDSQL – All Platforms.
You are rewarded with posession of 11MB worth of :

sqlcl-4.1.0.15.067.0446-no-jre.zip

Once you’ve unzipped the tool, go to the bin directory. In my case :

cd /opt/sqlcl/bin

Here you will find the following files :

  • sql.bat – a Windows batch script
  • sql – a bash script
  • sql.exe – a Windows executable

On a Windows client, you can just run sql.exe and be on your way. The bash script provided ultimately executes this command :

java  -Djava.awt.headless=true -Dapple.awt.UIElement=true -jar /opt/sqlcl/sqlcl/lib/oracle.sqldeveloper.sqlcl.jar

In order for SQLCL to run whether on Windows or Linux, you need Java installed. To be more precise, you need a Java Runtime Environmnent (JRE) of version 1.7 or higher.

This in itself is not a major issue. However, it’s worth bearing this dependency in mind if you’re working in a large organization with a separate Server Admin team as you’ll probably need to negotiate some process hurdles to get a suitable JRE onto your servers if there isn’t one already in place.

Database Connections

As SQLCL isn’t part of the client, it does not assume that there is a tnsnames.ora hanging around for it to read (although it will happily read one that’s pointed to by the TNS_ADMIN environment variable).

I think it’s fair to say that the “native” connection method is to use the EZConnect syntax which has the format :

host:port/sid

So, connecting to my local XE database interactively can look something like this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Username? (''?) mike
Password? (**********?) ********
Database? (''?) localhost:1526/XE
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 

SQL> 

…where my database is on the current machine, the TNS Listener is on port 1526 and the SID is XE.

Of course, having an Oracle Client installed and a TNS entry for my database specified, I can also still do this :

SQLcl: Release 4.1.0 Beta on Fri Apr 10 15:49:47 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


Username? (''?) mike
Password? (**********?) ********
Database? (''?) XE
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production 

SQL> 

There are other connection methods available. Barry McGillin has an example of connecting via LDAP.

Of course, using the EZConnect syntax all the time, especially when you open another connection from within your session could involve a fair amount of typing. Fortunately, The developers have thought of this…

NET

The NET command allows you to “save network details and assign it a shortcut command”.
For example, to create an alias for the database running on a Developer Day VirtualBox image :

net alias dday=0.0.0.0:1521/orcl;

NOTE – you need to terminate the string with a semi-colon at the end of the line.

To test it, we first need to enable NET in our SQLCL session (it’s set to OFF by default) …

SQL> set net on
SQL> show net
net: ON

So, whilst connected to XE as mike, I can use the NET alias I’ve just created to connect to the Developer Day database :

SQL> conn hr@dday
Password? (**********?) ************
Connected

SQL> show user
USER is "HR"

SQL> select name from v$database;

NAME    
---------
CDB1     

SQL> 
The Command Line, but not as we know it

When it comes to command-line editing and scrollback, SQL*Plus is, well, a bit basic.
If you’re on Windows, you can scroll back through previous commands. Unfortunately, even this is not a feature on Linux.
There have been efforts to make the SQL*Plus a bit more friendly over the years, notably the RLWRAP project.
If you haven’t discovered the joys of that particular software, you are about to have a new experience at the SQL prompt…

Tab Completion

Type the following…

select * from hr.reg

when you hit the TAB key, the table name is automagically completed for you.
OK, it uppercases the table name to REGIONS but hey, if you’re that worried about code formatting…well, we’ll get to that in a bit.

In the meantime, just marvel at the fact that it’s almost like being at the $ prompt.

Multi-Line Console Editing

If you think that’s good…

select * 
from hr.regions
/

I run this and then realise I really should have added a predicate to avoid having to wade through a massive result set ( OK, it’s only 4 rows, but just bear with me here).
Rather than having to fiddle about with the arcane SQL*Plus editing commands, I can simply use the list command to show me what’s currently in the buffer ( i.e. the query I’ve just run) and then hit the up arrow.

I’m now magically transported to the end of the last line of the statement. I can add a new line, simply by hitting RETURN and then add my predicate (NOTE – it’s probably a good idea to hit back arrow, forward arrow if you’re doing this as otherwise SQLCL thinks you want to cycle through the previous commands you’ve issued).

SQL> select *
  2  from hr.regions
  3* where REGION_ID = 1
  4  /

 REGION_ID REGION_NAME             
---------- -------------------------
         1 Europe                   

SQL> 

The uppercase REGION_ID indicates that I got this column name using tab completion.
Barry McGillin has a demonstration of this feature here.

HISTORY

Rather than simply remembering the last SQL or PL/SQL that was executed, SQLCL retains a history of the last 100 commands executed. Note that this is the last 100 commands for any user connecting on the current client.
This includes SQL*Plus commands, such as describe.

You can scroll back and forward through the previous commands using the arrow keys – nothing new for Windows clients but it is for Linux.

Alternatively, you can get a listing by typing :

history

This will give you a numbered listing of the commands in the history.

Incidentally, if you use the built-in SQLCL help command for history, you’ll get this listing :

SQL>help history
history [<index> | FULL | USAGE | HELP ]

SQL>history full
1  select 1 from dual;
2  select 2
>  from dual;
3  select 3 from dual
>  where 1=1;

SQL>history usage
1  (2) select 1 from dual; 
2  (11) select 2 from dual; 
3  (2) select 3 from dual where 1=1; 

SQL>history 3
1  select 3 from dual
2* where 1=1;

However, if you try the help option of the history command, you get something slightly different …

SQL> history help 

history [<index> | FULL | USAGE | TIME | HELP | CLEAR]

SQL> 

The full option appears to simply list all of the commands in the history ( the same as simply issuing the HISTORY command on it’s own).

When we look at some of the other options, it gets a bit interesting :

SQL> history
  1  select name from v$database
  2  select sysdate from dual
SQL> history usage
  1  (1) select name from v$database
  2  (4) select sysdate from dual
SQL> history time
  1  (00.176) select name from v$database
  2  (05.415) select sysdate from dual
SQL> 

The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the total execution time for the statement (thanks Jeff).

In some circumstances, this could prove handy, especially when you realise that you need to set timing on…just after you’ve kicked off your script.

If you want to cleardown the history…

SQL> history clear
History Cleared

One point to note – as with it’s Linux counterpart, HISTORY retains any command executed using that client, not simply any command when connected as a particular user. This is something you may need to be aware of on servers where the Oracle “client” is shared by multiple users.

CD

You know how much fun it is when you have a master release script, either for deployment to an environment or, just as common these days, to refresh a Continuous Integration environment.

Such a script may well look something like this :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------

@sequences/locations_seq.sql

prompt Tables
prompt ------

@tables/countries_tab.sql
@tables/locations_tab.sql

prompt Deployment complete

With SQLCL, the script becomes somewhat simpler :

prompt Deploying HR_DEV...

prompt Sequences
prompt ---------

cd sequences
@locations_seq.sql

prompt Tables
prompt ------

cd ../tables
@countries_tab.sql
@locations_tab.sql

prompt Deployment complete

Yes, you can now change directory from SQLCL, in the same way that you would at the OS level. As well as being rather convenient, this should serve to save a fair bit of typing, especially for scripts such as this one.

If all that isn’t quite enough Linux-like goodness for you then you’d probably be quite interested in…

ALIAS

As you may guess from the name, ALIAS allows you to save and invoke SQL or PL/SQL blocks under a pre=defined name.
There are already some aliases defined. You can find them by simply typing :

SQL> alias
locks
tables
tables2
SQL> 

If you want to see the definition of the tables alias….

SQL> alias list tables
tables
------
 select table_name "TABLES"from user_tables
SQL> 

You can run this simply by typing the alias name at the prompt. For example, if you connect as the HR user …

SQL> tables
Command=tables

TABLES                       
------------------------------
REGIONS                       
LOCATIONS                     
DEPARTMENTS                   
JOBS                          
EMPLOYEES                     
JOB_HISTORY                   
COUNTRY_EXT                   
COUNTRIES                     

 8 rows selected 

SQL> 

Of course, you can add your own alias. If you’re an inveterate clock-watcher, for example….

SQL> alias current_time=select to_char(sysdate, 'HH24:MI') from dual;

The alias is now saved so, whenever I want to know if it’s nearly hometime….

SQL> current_time
Command=current_time

TO_CHAR(SYSDATE,'HH24:MI')
--------------------------
19:53                     

SQL> 

According to the help, you can define aliases for PL/SQL blocks, and even reference bind variables.

Choosing you’re output format with SQLFORMAT

The new SQLFORMAT parameter deserves particular attention. Before I get into some of it’s more obvious benefits, take a look at this :

set sqlformat ansiconsole
with ts_details as
(
    select rpad(tablespace_name,30, ' ')||' '||lpad(floor(used_percent), 3, ' ') as ts_line,
        used_percent
    from dba_tablespace_usage_metrics
)
select 
    case 
        when used_percent > 70 then '@|bg_red '||ts_line||'|@'
        when used_percent < 1 then '@|bg_green '||ts_line||'|@'
        else '@|bg_yellow '||ts_line||'|@' 
    end as ts_usage_percentage
from ts_details
/

Now at first glance this script looks rather odd. Have I had an attack of fat-fingers ? Well, let’s run it and see…

Contrived example to show traffic-lighting

Contrived example to show traffic-lighting

Yep, SQL*Plus now does colour. OK, so I set the threshold values to make sure I got records to show in all three colours, but you get the idea.

There’s a rather more comprehensive script by Kris Rice here, which details many colour options.

One of the neat features in SQLDeveloper has always been the ability to generate the output from a query in a pre-defined format.
This ability is still there in SQLCL. To use a fashionable example…

SQL> select /*json*/ * from hr.regions;

{"items":[
{"region_id":1,"region_name":"Europe"},{"region_id":2,"region_name":"Americas"},{"region_id":3,"region_name":"Asia"},{"region_id":4,"region_name":"Middle East and Africa"},{}]}

SQL> 

SQLFORMAT allows you to specify the output format separately from the code. For example :

SQL> set sqlformat csv
SQL> select * from hr.regions;

"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"

SQL> 

The fact that the output format is separated from the script means that you can use the same script to generate multiple formats.
Ultimately, this means less code, not to mention less complex (or, at least, fiddly) code.

For example, instead of :

select employee_id||','||first_name||','||last_name
    ||'.'||email||','||phone_number
from hr.employees
/

… you can now have…

set sqlformat csv
select employee_id, first_name, last_name,
    email, phone_number
from hr.employees
/

…and if you need to create an additional feed file in a different format, you simply need to type…

set sqlformat xml
...

…and execute exactly the same code again.

There are several SQLFORMAT options. The ones that I’ve tested are :

  • default
  • ansiconsole
  • csv
  • insert – lists resuts as an insert statement
  • loader – pipe-delimited
  • delimited – same as csv
  • xml
  • html
  • fixed – fixed width
  • text
  • json
INFORMATION = KNOWLEDGE = POWER = ENERGY = MATTER = MASS

This heading is a slight misquote of Terry Pratchett’s L-Space equation.
The theory of L-space is that a large collection of accumulated knowledge, such as a library, can act like a black hole.
If you want to have a go at bending the fabric of space and time armed with nothing more than a command line…

INFORMATION

The humble DESCRIBE command in SQL*Plus has been around for a while. I’d guess it’s probably as old as the tool itself.
In that time, things have changed a bit.
It’s now possible to have comments on columns. There’s also this new-fangled PL/SQL, together with it’s associated packages.
If, like me, you’ve hankered after a describe that’s just a bit more able to handle these more modern developments, well, you’re in luck.

First off, let’s issue a good old DESCRIBE :

SQL> desc employees
Try the new information command: info employees


Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    
SQL> 

Interesting message, let’s give it a go (note, it’s probably a good idea to set the SQLFORMAT to ansiconsole at this point) :

SQL> set sqlformat ansiconsole
SQL> info employees
Columns 
NAME             DATA TYPE           NULL  DEFAULT    COMMENTS
*EMPLOYEE_ID     NUMBER(6,0)         No                   Primary key of employees table.
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  First name of the employee. A not null column.
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Last name of the employee. A not null column.
 EMAIL           VARCHAR2(25 BYTE)   No                   Email id of the employee
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  Phone number of the employee; includes country code and area code
 HIRE_DATE       DATE                No                   Date when the employee started on this job. A not null column.
 JOB_ID          VARCHAR2(10 BYTE)   No                   Current job of the employee; foreign key to job_id column of the
                                                                    jobs table. A not null column.
 SALARY          NUMBER(8,2)         Yes                  Monthly salary of the employee. Must be greater
                                                                    than zero (enforced by constraint emp_salary_min)
 COMMISSION_PCT  NUMBER(2,2)         Yes                  Commission percentage of the employee; Only employees in sales
                                                                    department elgible for commission percentage
 MANAGER_ID      NUMBER(6,0)         Yes                  Manager id of the employee; has same domain as manager_id in
                                                                    departments table. Foreign key to employee_id column of employees table.
                                                                    (useful for reflexive joins and CONNECT BY query)
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  Department id where employee works; foreign key to department_id
                                                                    column of the departments table

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

SQL> 

INFORMATION can give you even more in-depth information:

SQL> info+ employees
Columns 
NAME             DATA TYPE           NULL  DEFAULT    LOW_VALUE             HIGH_VALUE            NUM_DISTINCT   HISTOGRAM  
*EMPLOYEE_ID     NUMBER(6,0)         No                   100                   206                   107            NONE       
 FIRST_NAME      VARCHAR2(20 BYTE)   Yes                  Adam                  Winston               91             NONE       
 LAST_NAME       VARCHAR2(25 BYTE)   No                   Abel                  Zlotkey               102            NONE       
 EMAIL           VARCHAR2(25 BYTE)   No                   ABANDA                WTAYLOR               107            NONE       
 PHONE_NUMBER    VARCHAR2(20 BYTE)   Yes                  011.44.1343.329268    650.509.4876          107            NONE       
 HIRE_DATE       DATE                No                   2001.01.13.00.00.00   2008.04.21.00.00.00   98             NONE       
 JOB_ID          VARCHAR2(10 BYTE)   No                   AC_ACCOUNT            ST_MAN                19             FREQUENCY  
 SALARY          NUMBER(8,2)         Yes                  2100                  24000                 58             NONE       
 COMMISSION_PCT  NUMBER(2,2)         Yes                  .1                    .4                    7              NONE       
 MANAGER_ID      NUMBER(6,0)         Yes                  100                   205                   18             FREQUENCY  
 DEPARTMENT_ID   NUMBER(4,0)         Yes                  10                    110                   11             FREQUENCY  

Indexes
INDEX_NAME            UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS                COLUMN_EXPRESSION  
HR.EMP_JOB_IX         NONUNIQUE   VALID                   JOB_ID                                    
HR.EMP_NAME_IX        NONUNIQUE   VALID                   LAST_NAME, FIRST_NAME                     
HR.EMP_EMAIL_UK       UNIQUE      VALID                   EMAIL                                     
HR.EMP_EMP_ID_PK      UNIQUE      VALID                   EMPLOYEE_ID                               
HR.EMP_MANAGER_IX     NONUNIQUE   VALID                   MANAGER_ID                                
HR.EMP_DEPARTMENT_IX  NONUNIQUE   VALID                   DEPARTMENT_ID                             


References
TABLE_NAME   CONSTRAINT_NAME  DELETE_RULE  STATUS   DEFERRABLE      VALIDATED  GENERATED  
DEPARTMENTS  DEPT_MGR_FK      NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
EMPLOYEES    EMP_MANAGER_FK   NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  
JOB_HISTORY  JHIST_EMP_FK     NO ACTION    ENABLED  NOT DEFERRABLE  VALIDATED  USER NAME  

SQL> 

Whilst all of this additional functionality is very welcome, the best bit, for me at least, is this…

SQL> info dbms_utility.get_time
Package

/* Package SYS.DBMS_UTILITY */
/*  FUNCTION  SYS.DBMS_UTILITY.GET_TIME  */
    /*   RETURN NUMBER   */
     v_ret := SYS.DBMS_UTILITY.GET_TIME( );


SQL> info dbms_metadata.get_ddl
Package

/* Package SYS.DBMS_METADATA */
/*  FUNCTION  SYS.DBMS_METADATA.GET_DDL  */
    /*   RETURN CLOB   */
     v_ret := SYS.DBMS_METADATA.GET_DDL(   OBJECT_TYPE   =>  p_IN_param0  /*   VARCHAR2   */,
                                           NAME          =>  p_IN_param1  /*   VARCHAR2   */,
                                           SCHEMA        =>  p_IN_param2  /*   VARCHAR2   */,
                                           VERSION       =>  p_IN_param3  /*   VARCHAR2   */,
                                           MODEL         =>  p_IN_param4  /*   VARCHAR2   */,
                                           TRANSFORM     =>  p_IN_param5  /*   VARCHAR2   */);


SQL> 

Yes, no longer will I have to scroll through the 86 members of DBMS_METADATA to find out the signature for GET_DDL.
I can now simply describe (OK, INFO) the package member directly. Just like going through a wormhole directly to the information I need (to stretch the L-space metaphor).

DDL

As the name suggests, DDL outputs the DDL for a given object. For example…

SQL> ddl regions

  CREATE TABLE "HR"."REGIONS" 
   (	"REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE, 
	"REGION_NAME" VARCHAR2(25), 
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

You also have the option of writing the output to a file. This command …

ddl regions regions_tab.sql

…writes the output to a file called regions_tab.sql, as well as the command line.

Whilst we’re on the subject of Time (well, saving some, at least)…

CTAS

CTAS – Create Table as Select – without all of that typing…

CTAS regions new_regions

… generates DDL to create a new table called new_regions…

  CREATE TABLE "HR"."NEW_REGIONS" 
   (	"REGION_ID",
	"REGION_NAME",
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 

as 
select * from REGIONS

As CTAS does use DBMS_METADATA, you can amend the output using that package’s SET_TRANFORM_PARAM procedure. For example :

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false)
anonymous block completed

SQL> ctas regions new_regions

  CREATE TABLE "HR"."NEW_REGIONS" 
   (	"REGION_ID",
	"REGION_NAME",
	 CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 
as 
select * from REGIONS
SQL> 

UPDATE – Jeff has posted a much better example of this feature here.

Remember, CTAS simply generates the DDL command to create the table and writes it to the buffer (and a file, if specified). It does not actually execute the command.

LOAD

The LOAD command offers an alternative way of populating a table from a CSV file.

Let’s try using a topical example…

create table icc_wc_winners
(
    year number(4),
    country varchar2(30)
)
/

The csv file is in a sub-directory called cricket :

YEAR,COUNTRY
1975,WEST INDIES
1979,WEST INDIES
1983,INDIA
1987,AUSTRALIA
1992,PAKISTAN
1996,SRI LANKA
1999,AUSTRALIA
2003,AUSTRALIA
2007,AUSTRALIA
2011,INDIA
2015,AUSTRALIA

At present, LOAD has a bit of a problem recognising that you’ve chnaged directory using the CD command. However, the developers have fixed this and it should be available in a future release.
In the meantime though, we can get this to work by specifying the relative path for the csv file :

SQL> load icc_wc_winners cricket/wc_winners.csv
--Number of rows processed: 11
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
SQL>

NOTE – you need to issue a commit for these records to be saved permanently.

FORMAT

Being a rather particular breed, developers tend to like their code formatted in a certain way. The trouble is, if you ask ten developers what that way should be, you’re likely to get ten different answers.
It is for this reason that Coding Standards documents tend to illicit an inordinate amount of controversy.
As I’ve noted previously, one possible solution to this is to let developers format their code in whatever way they see fit and then just run the code through a formatting tool that converts it to whatever the standard is.
All the main IDEs have their own formatting solutions. However, you can now do this at the command line.

For example, let’s try this query :

select dept.department_name, emp.first_name||' '||emp.last_name as empname
from employees emp
inner join departments dept
  on emp.department_id = dept.department_id
order by 1,2  
/

Using the new FORMAT command, we can, now apply the default SQLDeveloper formatting…

SQL> format buffer
  1  SELECT
  2    dept.department_name,
  3    emp.first_name
  4    ||' '
  5    ||emp.last_name AS empname
  6  FROM
  7    employees emp
  8  INNER JOIN departments dept
  9  ON
 10    emp.department_id = dept.department_id
 11  ORDER BY
 12*   1,2
SQL> 

Better still, we can even take the source file for the query, run it through the formatter and save the output to a new file :

SQL> format file hr.sql hr_yuk.sql
SQL> 

The resulting file looks like this :

SELECT
  dept.department_name,
  emp.first_name
  ||' '
  ||emp.last_name AS empname
FROM
  employees emp
INNER JOIN departments dept
ON
  emp.department_id = dept.department_id
ORDER BY
  1,2 /

As you can probably tell by the output filename I’ve chosen, I’m not particularly keen on the default formatting.
I’ll admit, this is largely subjective on my part. However, I would like the code to be formatted according to my preferences rather than the default.
Once again, the developers have a solution for this which should be available in the near future.
With this functionality in place, you could potentially leave the contentious business of formatting code to the standard until just before deployment. Running each program through a formatter setup to meet your coding standards may be an appealing option.

BRIDGE

One of the major advantages IDE’s have over the command line is the ability to compare two schemas in different databases.
Up until now, this has not been possible from SQL*Plus without the use of a database link.
The BRIDGE command may well change all that.

At present, it’s functionality is restricted to creating a table in your current connection based on the results of a query run against a second connection that you specify. For example, to create a table in my current schema with the details of the tables in a remote hr schema….

SQL> bridge hr_tabs as "jdbc:oracle:thin:hr/the_hr_password@0.0.0.0:1521/orcl"(select table_name from user_tables);
Table hr_tabs : insert succeeded 
SQL> select * from hr_tabs;

TABLE_NAME                                                                     
--------------------------------------------------------------------------------
JOB_HISTORY                                                                     
EMPLOYEES                                                                       
JOBS                                                                            
DEPARTMENTS                                                                     
LOCATIONS                                                                       
REGIONS                                                                         
COUNTRIES                                                                       

 7 rows selected 

SQL> 

…where the_hr_password is the password for the HR user on the target database.

One point to note here is that bridge executes the remote connection and the Create Table independently of each other.
Therefore, it’s quite possible for the connection to fail and the table to be created anyway.

NOTE – it should be possible to get around this behaviour by specifiying WHENEVER SQLERROR EXIT. I’ve not managed to get this to work as I’d expect, but this could be because I’m doing something silly.

Another thing to be aware of is that the BRIDGE command you’ve issued will be retained in the HISTORY, complete with uid/password.
This is currently on the developers’ to-do list.

APEX

The last of the new commands to cover is APEX.

If you simply issue this command without any arguments, it will list all of the APEX Applications where the user you are currently connected as is defined as the owning schema.
The exception to this is if you’re connected as SYS as SYSDBA where you’ll get a full list of all APEX applications.

So, connected as user OSSCA :

SQL> apex
WORKSPACE  APPLICATION_ID  APPLICATION_NAME             BUILD_STATUS     LAST_UPDATED_ON        
OSSCA2     100             Sample Database Application  Run and Develop  26-FEB-15              
OSSCA2     101             OSSCA UI                     Run Only         26-FEB-15              

APEX also provides the facility to export an application. By spooling the output, you can create the appropriate file :

spool my_apex_export.sql
apex export 100
spool off

…creates the APEX export file my_apex_export.sql, as well as outputting to the screen.

Conclusion

Whilst it’s still very early days for SQLCL, it’s potential impact is significant. I make no apologies for being excited about it.

Where to go for more SQLCL stuff

There’s a fair amount of information out there about SQLCL.

A good starting point would be Jeff’s presentation, which is available here.

Both Kris Rice and Barry McGillin are members of the development team and post regularly on the subject of SQLCL.

If you find any glitches, or have any questions, then the place to go is the SQLDeveloper OTN Forum.

Finally, as it’s around Easter Time, try entering the following at the SQLCL command line :

show sqldev2

Filed under: Oracle, SQL Tagged: alias, APEX, bridge, cd, colours in sqlcl, ctas, dbms_metadata.set_transform_param, ddl, format, history, information, load, multi-line editing in sqlcl, net, sdsql, sqlcl, sqlformat, tab completion in sqlcl