Skip navigation.

Feed aggregator

Creating Real-Time Search Dashboards using Apache Solr, Hue, Flume and Cloudera Morphlines

Rittman Mead Consulting - Wed, 2015-03-04 01:19

Late last week Cloudera published a blog post on their developer site on building a real-time log analytics dashboard using Apache Kafka, Cloudera Search and Hue. As I’d recently been playing around with Oracle Big Data Discovery with our website log data as the data source, and as we’ve also been doing the same exercise in our development labs using ElasticSearch and Kibana I thought it’d be interesting to give it a go; partly out of curiosity around how Solr, Kafka and Hue search works and compares to Elasticsearch, but also to try and work out what extra benefit Big Data Discovery gives you above and beyond free and open-source tools.

NewImage

In the example, Apache web log data is read from the Linux server via a Flume syslog source, then fed into Apache Kafka as the transport mechanism before being loaded into Solr using a data transformation framework called “morphlines”. I’ve been looking at Kafka as an alternative to Flume for ingesting data into a Hadoop system for a while mainly because of the tireless advocacy of Cloudera’s Gwen Shapira (Oracle ACE, ex-Pythian, now at Cloudera) who I respect immensely and has a great background in Oracle database administration as well as Hadoop, and because it potentially offers some useful benefits if used instead of, or more likely alongside, Flume – a publish-subscribe model vs. push, the ability to have multiple consumers as well as publishers, and a more robust transport mechanism that should avoid data loss when an agent node goes down. Kafka is now available as a parcel and service descriptor that you can download and then install within CDH5, and so I set up a separate VM in my Hadoop cluster as a Kafka broker and also installed Solr at the same time.

NewImage

Working through the example, in the end I went with a slightly different and simplified approach that swapped the syslog Flume source for an Apache Server file tailing source, as our webserver was on a different host to the Flume agent and I’d previously set this up before for an earlier blog post. I also dropped the Kafka element as the Cloudera article wasn’t that clear to me whether it’d work in its published form or needed amending to use with Kafka (“To get data from Kafka, parse it with Morphlines, and index it into Solr, you can use an almost identical configuration”), and so I went with an architecture that looked like this:

NewImage

Compared to Big Data Discovery, this approach has got some drawbacks, but some interesting benefits. From a drawback perspective, Apache Solr (or Cloudera Search as it’s called in CDH5, where Cloudera have integrated Solr with HDFS storage) needs some quite fiddly manual setup that’s definitely an IT task, rather than the point-and-click dataset setup that you get with Big Data Discovery. In terms of benefits though, apart from being free it’s potentially more scalable than Big Data Discovery as BDD has to sample the full Hadoop dataset and fit that sample (typically 1m rows, or 1-5% of the full dataset) into BDD’s Endeca Server-based DGraph engine; Solr, however, indexes the whole Hadoop dataset and can store its indexes and log files within HDFS across the cluster – potentially very interesting if it works.

Back to drawbacks though, the first complication is that Solr’s configuration settings in this Cloudera Search incarnation are stored in Apache Zookeeper, so you first have to download a template copy of the collection files (schema, index etc) from Zookeeper using solrctl, the command-line tool for SolrCloud (Solr running on a distributed cluster, as it is with Cloudera Search)

solrctl --zk bda5node2:2181/solr instancedir --generate $HOME/accessCollection

Then – and this again is a tricky part compared to Big Data Discovery – you have to edit the schema.xml file that Solr uses to determine which fields to index, what their datatypes are and so on. The Cloudera blog post points to a Github repo with the required schema.xml file for Apache Combined Log Format input files, I found I had to add an extra entry for the “text” field name before Solr would index properly, added at the end of the file except here:

<field name="id" type="string" indexed="true" stored="true" required="true" multiValued="false" />
   <field name="time" type="tdate" indexed="true" stored="true" />
   <field name="record" type="text_general" indexed="true" stored="false" multiValued="true"/>
   <field name="client_ip" type="string" indexed="true" stored="true" />
   <field name="code" type="string" indexed="true" stored="true" />
   <field name="user_agent" type="string" indexed="true" stored="true" />
   <field name="protocol" type="string" indexed="true" stored="true" />   
   <field name="url" type="string" indexed="true" stored="true" />   
   <field name="request" type="string" indexed="true" stored="true" />
   <field name="referer" type="string" indexed="true" stored="true" />
   <field name="bytes" type="string" indexed="true" stored="true" />
   <field name="method" type="string" indexed="true" stored="true" />
   
   <field name="extension" type="string" indexed="true" stored="true" />   
   <field name="app" type="string" indexed="true" stored="true" />      
   <field name="subapp" type="string" indexed="true" stored="true" />
      
   <field name="device_family" type="string" indexed="true" stored="true" />
   <field name="user_agent_major" type="string" indexed="true" stored="true" />   
   <field name="user_agent_family" type="string" indexed="true" stored="true" />
   <field name="os_family" type="string" indexed="true" stored="true" />   
   <field name="os_major" type="string" indexed="true" stored="true" />
   
   <field name="region_code" type="string" indexed="true" stored="true" />
   <field name="country_code" type="string" indexed="true" stored="true" />
   <field name="city" type="string" indexed="true" stored="true" />
   <field name="latitude" type="float" indexed="true" stored="true" />
   <field name="longitude" type="float" indexed="true" stored="true" />
   <field name="country_name" type="string" indexed="true" stored="true" />
   <field name="country_code3" type="string" indexed="true" stored="true" />
 
   <field name="_version_" type="long" indexed="true" stored="true"/>
   <field name="text" type="text_general" indexed="true" stored="false" multiValued="true"/>
   <dynamicField name="ignored_*" type="ignored"/>

Then you have to upload the solr configuration settings to Zookeeper, and then configure Solr to use this particular set of Zookeeper Solr settings (note the “—create” before the accessCollection collection name in the second command, this was missing from the Cloudera steps but is needed to be a valid solrctl command)

solrctl --zk bda5node2:2181/solr instancedir --create accessCollection $HOME/accessCollection
solrctl --zk bda5node2:2181/solr --create accessCollection -s 1

At this point you should be able to go to the Solr web admin page within the CDH cluster (http://bda5node5.rittmandev.com:8983/solr/#/, in my case), and see the collection (a distributed Solr index) listed with the updated index schema.

NewImage

Next I configure the Flume source agent on the RM webserver, using this Flume conf file:

## SOURCE AGENT ##
## Local instalation: /etc/flume1.5.0
## configuration file location:  /etc/flume1.5.0/conf/conf
## bin file location: /etc/flume1.5.0/conf/bin
## START Agent: bin/flume-ng agent -c conf -f conf/flume-src-agent.conf -n source_agent
 
# http://flume.apache.org/FlumeUserGuide.html#exec-source
source_agent.sources = apache_server
source_agent.sources.apache_server.type = exec
source_agent.sources.apache_server.command = tail -f /etc/httpd/logs/access_log
source_agent.sources.apache_server.batchSize = 1
source_agent.sources.apache_server.channels = memoryChannel
source_agent.sources.apache_server.interceptors = itime ihost itype
 
# http://flume.apache.org/FlumeUserGuide.html#timestamp-interceptor
source_agent.sources.apache_server.interceptors.itime.type = timestamp
 
# http://flume.apache.org/FlumeUserGuide.html#host-interceptor
source_agent.sources.apache_server.interceptors.ihost.type = host
source_agent.sources.apache_server.interceptors.ihost.useIP = false
source_agent.sources.apache_server.interceptors.ihost.hostHeader = host
 
# http://flume.apache.org/FlumeUserGuide.html#static-interceptor
source_agent.sources.apache_server.interceptors.itype.type = static
source_agent.sources.apache_server.interceptors.itype.key = log_type
source_agent.sources.apache_server.interceptors.itype.value = apache_access_combined
 
# http://flume.apache.org/FlumeUserGuide.html#memory-channel
source_agent.channels = memoryChannel
source_agent.channels.memoryChannel.type = memory
source_agent.channels.memoryChannel.capacity = 100
 
## Send to Flume Collector on Hadoop Node
# http://flume.apache.org/FlumeUserGuide.html#avro-sink
source_agent.sinks = avro_sink
source_agent.sinks.avro_sink.type = avro
source_agent.sinks.avro_sink.channel = memoryChannel
source_agent.sinks.avro_sink.hostname = rittmandev.com
source_agent.sinks.avro_sink.port = 4545

and then I set up a Flume sink agent as part of the Flume service using Cloudera Manager, initially set as “stopped”.

NewImage

The Flume configuration file for this sink agent is where the clever stuff happens.

collector.sources = AvroIn
collector.sources.AvroIn.type = avro
collector.sources.AvroIn.bind = bda5node5
collector.sources.AvroIn.port = 4545
collector.sources.AvroIn.channels = mc1 mc2

collector.channels = mc1 mc2
collector.channels.mc1.type = memory
collector.channels.mc1.transactionCapacity = 1000
collector.channels.mc1.capacity = 100000
collector.channels.mc2.type = memory
collector.channels.mc2.capacity = 100000
collector.channels.mc2.transactionCapacity = 1000

collector.sinks = LocalOut MorphlineSolrSink

collector.sinks.LocalOut.type = file_roll
collector.sinks.LocalOut.sink.directory = /tmp/flume/website_logs
collector.sinks.LocalOut.sink.rollInterval = 0
collector.sinks.LocalOut.channel = mc1

collector.sinks.MorphlineSolrSink.type = org.apache.flume.sink.solr.morphline.MorphlineSolrSink
collector.sinks.MorphlineSolrSink.morphlineFile = /tmp/morphline.conf
collector.sinks.MorphlineSolrSink.channel = mc2

The interesting bit here is the MorphlineSolrSink flume sink. This Flume sink type routes flume events to a morphline script that in turn copies the log data into the HDFS storage area used by Solr, and passes it to Solr for immediate indexing. Cloudera Morphlines is a command-based lightweight ETL framework designed to transform streaming data from Flume, Spark and other sources and load it into HDFS, HBase or in our case, Solr. Morphlines config files define ETL routines that then call  extensible morphlines Kite SDK functions to perform transformations on incoming data streams such as

  • Split webserver request fields into HTTP protocol, method and URL requested
  • In conjunction with the Maxmind GeoIP database, generate the country, city and geocode for a given IP address
  • Converting dates and times in string format to a Solr-format date and timestamp

with the output then being passed to Solr in this instance, along with the UUID and other metadata Solr needs, for loading to the Solr index, or “collection” as its termed when it’s running across the cluster (note the full log files aren’t stored by this process into HDFS, just the Solr indexes and transaction logs). The morphlines config file I used is below, based on the one provided in the Github repo accompanying the Cloudera blog post – note though that you need to download and setup the Maxmind GeoIP database file, and install the Python pip utility and a couple of pip packages before this will work:

# Specify server locations in a SOLR_LOCATOR variable;
# used later in variable substitutions
# Change the zkHost to point to your own Zookeeper quorum
SOLR_LOCATOR : {
    # Name of solr collection
    collection : accessCollection
    # ZooKeeper ensemble
    zkHost : "bda5node2:2181/solr"
}
 
# Specify an array of one or more morphlines, each of which defines an ETL
# transformation chain. A morphline consists of one or more (potentially
# nested) commands. A morphline is a way to consume records (e.g. Flume events,
# HDFS files or blocks), turn them into a stream of records, and pipe the stream
# of records through a set of easily configurable transformations on it's way to
# Solr (or a MapReduceIndexerTool RecordWriter that feeds via a Reducer into Solr).
morphlines : [
{
    # Name used to identify a morphline. E.g. used if there are multiple morphlines in a
    # morphline config file
    id : morphline1
    # Import all morphline commands in these java packages and their subpackages.
    # Other commands that may be present on the classpath are not visible to this morphline.
    importCommands : ["org.kitesdk.**", "org.apache.solr.**"]
    commands : [
    {
        ## Read the email stream and break it up into individual messages.
        ## The beginning of a message is marked by regex clause below
        ## The reason we use this command is that one event can have multiple
        ## messages
        readCSV {
    separator:  " "
            columns:  [client_ip,C1,C2,time,dummy1,request,code,bytes,referer,user_agent,C3]
    ignoreFirstLine : false
            quoteChar : "\""
            commentPrefix : ""
            trim : true
            charset : UTF-8
        }
    }
    {
split { 
inputField : request
outputFields : [method, url, protocol]          
separator : " "        
isRegex : false      
#separator : """\s*,\s*"""        
#  #isRegex : true      
addEmptyStrings : false
trim : true          
          }
    }
     {
split { 
inputField : url 
outputFields : ["", app, subapp]          
separator : "\/"        
isRegex : false      
#separator : """\s*,\s*"""        
#  #isRegex : true      
addEmptyStrings : false
trim : true          
          }
    }
    {
userAgent {
inputField : user_agent
outputFields : {
user_agent_family : "@{ua_family}"
user_agent_major  : "@{ua_major}"
device_family     : "@{device_family}"
os_family         : "@{os_family}"
os_major  : "@{os_major}"
}          
}
    }
    {
#Extract GEO information
geoIP {
            inputField : client_ip
            database : "/tmp/GeoLite2-City.mmdb"
}
     }
     {
# extract parts of the geolocation info from the Jackson JsonNode Java 
# # object contained in the _attachment_body field and store the parts in
# # the given record output fields:      
extractJsonPaths {
flatten : false
paths : { 
country_code : /country/iso_code
country_name : /country/names/en
                region_code  : /continent/code
#"/subdivisions[]/names/en" : "/subdivisions[]/names/en"     
#"/subdivisions[]/iso_code" : "/subdivisions[]/iso_code"     
city : /city/names/en
#/postal/code : /postal/code
latitude : /location/latitude
longitude : /location/longitude
#/location/latitude_longitude : /location/latitude_longitude
#/location/longitude_latitude : /location/longitude_latitude
} 
}
      }
      #{logInfo { format : "BODY : {}", args : ["@{}"] } }
    # add Unique ID, in case our message_id field from above is not present
    {
        generateUUID {
            field:id
        }
    }
    # convert the timestamp field to "yyyy-MM-dd'T'HH:mm:ss.SSSZ" format
    {
       #  21/Nov/2014:22:08:27
        convertTimestamp {
            field : time 
            inputFormats : ["[dd/MMM/yyyy:HH:mm:ss", "EEE, d MMM yyyy HH:mm:ss Z", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "yyyy-MM-dd'T'HH:mm:ss", "yyyy-MM-dd"]
            inputTimezone : America/Los_Angeles
           outputFormat : "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
            outputTimezone : UTC
        }
    }
    # Consume the output record of the previous command and pipe another
    # record downstream.
    #
    # This command sanitizes record fields that are unknown to Solr schema.xml
    # by deleting them. Recall that Solr throws an exception on any attempt to
    # load a document that contains a field that isn't specified in schema.xml
    {
        sanitizeUnknownSolrFields {
            # Location from which to fetch Solr schema
            solrLocator : ${SOLR_LOCATOR}
        }
    }
    # load the record into a SolrServer or MapReduce SolrOutputFormat.
    {
        loadSolr {
            solrLocator : ${SOLR_LOCATOR}
        }
    }
    ]
}
]

Then it’s just a case of starting the target sink agent using Cloudera Manager, and the source agent on the RM webserver using the flume-ng command-line utility, and then (hopefully) watch the web activity log entries start to arrive as documents in the Solr index/collection – which, after a bit of fiddling around and correcting typos, it did:

NewImage

What’s neat here is that instead of having to use either an ETL tool such as ODI to process and parse the log entries (as I did here, in an earlier blog post series on ODI on Hadoop), or use the Hive-to-DGraph data reload feature in BDD, I’ve instead just got a Flume sink running this morphlines process and my data is added in real-time to my Solr index, and as you’ll see in a moment, a Hue Search dashboard.

To get Hue to work with my Solr service and new index, you first have to add the Solr service URL details to the Hue configuration settings using Cloudera Manager, like this:

NewImage

Then, you can select the index from the list presented by the Search application within Hue, and start creating your data discovery and faceted search dashboard.

NewImage

with the end result, after a few minutes of setup, looking like this for me:

NewImage

So how does Solr, Hue, Flume and Morphlines compare to Oracle Big Data Discovery as a potential search-and-discovery solution on Hadoop? What’s impressive is how little work, once I’d figured it out, it took to set this up including the real-time loading and indexing of data for the dashboard. Compared to a loading HDFS and Hive using ODI, and manually refreshing the BDD DGraph data store, it’s much more lightweight and pretty elegant. But, it’s clearly an IT / developer solution, and I spent a fair few late nights getting it all to work – getting the Solr schema.xml right was a tricky task, and the morphlines / Solr ingestion process was particularly hard to to debug and understand why it wasn’t working.

Oracle Big Data Discovery, by contrast, makes the data loading, transformation and enrichment process available to the business or data analyst, and provides much richer tools for cataloging and exploring the full universe of datasets on the Hadoop cluster. Morphlines compares well to the Groovy transformations provided by Big Data Discovery and Solr is extensible to add functionality such as sentiment analysis and text parsing, but again these are IT tasks and not something the average data analyst will want to do.

In summary then – Hue, Solr and the Morphlines transformation framework can be an excellent tool in the hands of IT professionals and can create surprisingly featureful and elegant solutions with just a bit of code and process configuration – but where Big Data Discovery comes into its own is putting significant parts of this capability in the hands of the business and the data analyst, and providing tools for data upload and wrangling, combining that data with other datasets, analyzing that whole dataset (or “data reservoir”) and then collaborating with others around the organization.

Categories: BI & Warehousing

Top 10 Reasons to Attend the Modern Sales Experience

Linda Fishman Hoyle - Tue, 2015-03-03 18:47

A Guest Post by Emily Creech, Oracle Product Marketing Manager

Sales organizations are under continuous pressure to meet targets and deliver results, but the environment has changed – customers are more informed, they have more choices, and their expectations are higher than ever.  This is why we’ve compiled the best and brightest for our Modern Sales Experience Conference taking place March 31 – April 2, 2015, and we couldn’t be more excited! The Modern Sales Experience brings together a prestigious group of thought leaders and innovative companies who are leading the industry and making impactful changes in the areas of Sales and Customer Experience.

You won’t want to miss this event – but in case you’re on the fence and need a few more reasons to attend, we’ve compiled our top 10 reasons. Here they are… in no particular order:

1. Hear from the Experts. This is an extraordinary opportunity to hear from a number of experts. Gartner Research, Constellation Research, Ventana Research, Deloitte Digital, Nucleus Research, Altimeter Group, and G2 Crowd will all be sharing their unique perspectives.

2. Participate in Engaging Workshops.  Choose from a number of workshop sessions and interact with others who are also exploring ways to meet the challenges of customer experience. Workshops include Next-Generation Analytics, Big Data to Inform Smarter Sales, User Adoption Tactics, and Leveraging Configure-Price-Quote (CPQ) to build better Customer Experiences.

3. Rock out with OneRepublic.  OneRepublic, the GRAMMY award winners you may know for their hit “Counting Stars,” will host a private concert for event attendees on Wednesday evening.  

4. Listen to Guest Keynote, Daniel Pink. Daniel Pink, bestselling author and current host of National Geographic's "Crowd Control," will share insights on human behavior, what drives and motivates people, and how a deeper knowledge of the habits, motivations, and expectations of your customers can translate into outstanding business benefits. Daniel Pink is an entertaining speaker. Don’t believe us? Just check out his TED Talk.

5. Learn About the Future of CX. Hosted by Rebecca Wettemann from Nucleus Research, a panel will be dedicated to the future with those who are already living it and helping to shape it. A panel of UNLV Lee Business School students will chat with us about their ideas, their opinions, and frank advice on how to accommodate the next generation.

6. Discover Complete Solutions. Product experts will outline considerations, best practices, and real-world solutions for your customer experience business challenges. Topics include Adding Marketing to the Mix, Integrating Sales with Configure-Price-Quote, and How CPQ and Commerce can Lead to Optimal Results.

7. Hear from leading CX Innovators. Don’t miss this opportunity to hear from some of the world’s brightest and most innovative companies including LinkedIn, Visa, Presence, and more. This select group will share what goes into a modern sales strategy, how they approach customer engagement, how they measure engagement and their sales teams, and what technologies and philosophies help to craft a modern CX strategy.

8. Network with Fellow Sales Leaders.  Networking opportunities abound at this event from meeting other attendees and industry influencers during receptions to the late-night Customer Appreciation Event.

9. Mingle with Celebrities. Enjoy dinner and drinks while hanging out with celebrities (as depicted as depicted in wax sculptures) at Madame Tussaud’s wax museum.

10. Las Vegas.  Need we say more?  The Modern Sales Experience conference will be held at the five-diamond Venetian Resort Hotel & Casino.  

Will we see you there?  More information and a detailed agenda can be found on the Modern Customer Experience Conferences Website. Don't delay registering! Register here.

Generic query for multicriteria search - part II: BIND_AWARE (Adaptive Cursor Sharing)

Yann Neuhaus - Tue, 2015-03-03 14:52

In the previous post I explained the performance issue encountered when using a generic query to deal with optional search criteria on multiple columns. The statement was shared by all executions, was marked as bind sensitive, but never became bind aware. Let's use the BIND_AWARE hint.

Generic query for multicriteria search - part I: USE_CONCAT (OR Expansion)

Yann Neuhaus - Tue, 2015-03-03 14:47

You have a multicriteria search screen on the EMPLOYEE table where you can enter an employee id, a department id, a manager id or a job id. Either you put the value you want to filter on, or you leave it null when you don't want to filter on it. How will you code that? You can build the query on the fly with dynamic SQL or use a generic query like this one:

       SELECT *
       FROM employees
       WHERE (job_id = NVL(:job_id, job_id))
       AND (department_id = NVL(:department_id, department_id))
       AND (manager_id = NVL(:manager_id, manager_id))
       AND (employee_id = NVL(:employee_id, employee_id))
This is good for the code maintainability, but having a one-fit-all query will not be optimial for each cases. Markus Winand (every database developer should read his book) describes the danger ot that in his website: Use The Index, Luke

RAC Attack! next month 12c in Las Vegas

Yann Neuhaus - Tue, 2015-03-03 14:39

 

b2ap3_thumbnail_5---Banner---Official-COLLABORATE-Speaker.jpg

 

RAC is the most complex installation you can have for an oracle database. A RAC DBA is involved not only on database, but storage, network, and system as well. It involves also the application in order to be sure that the application service can follow the database service high availability. It's also brings every database skills to the highest level: small contention on single instance database can become a big bottleneck in RAC.

But RAC is also fascinating. It's the highest service availability. When correctly configured you can stop a node without any impact on your users. It's the highest scalability: you are not limited by the number of CPU or the amount of memory that you can put in a server. Just add a node. RAC is not new. Oracle 6 was already able to open the same database from several instances. It was called parallel server.

Do you think it's impossible to learn and practices that king of infrastructure when you don't have already one in your data center? No. You can install and practice RAC on your laptop. This is what RAC Attack! is about: at various events, experienced RAC Attack volunteers (ninjas) will help you address any related issues and guide you through the setup process and you will have a RAC on your laptop. Next month in Las Vegas is the IOUG event: COLLABORATE15. I'll be there as a speaker and I'm also very happy to help as a RAC Attack! Nija. 

Here you can find all information about it:

http://collaborate.ioug.org/precon#rac

Hope to see you there.  

 

 

Little League, Big Data

Scott Spendolini - Tue, 2015-03-03 13:36
Last week, I participated in my first Little League draft for my son's baseball team.  This was new territory, as up until now, play has been non-competitive.  This year we will actually have to keep score, and there will be winners and losers.

In preparation for the draft, we had tryouts a few weeks ago where we evaluated the kids on a number of different criteria.  Never have I seen so many scared 7 and 8 year olds march through the cages as dozens of coaches with clipboards watched and recorded their every move.  I camped out and watched them pitch, as from what many veteran coaches told me, the key to keeping the game moving along is the pitcher.

In preparation for the draft, we were sent a couple of key spreadsheets.  The first one had an average rating of all of the kids tryouts assessments, done by the board members.  The second one contained coaches evaluations for some of the players from past seasons. Lots and lots of nothing more than raw data.

Time to fire up APEX.  I created a workspace on my laptop, as I was not sure if we would have WiFi at the draft.  From there, I imported both spreadsheets into tables, and got to work on creating a common key.  Luckily, the combination of first and last name produced no duplicates, so it was pretty easy to link the two tables.  Next, I created a simple IR based on the EVALS table - which was the master.  This report showed all of the tryout scores, and also ranked each player based on the total score.

Upon editing a row in EVALS, I had a second report that showed a summary of the coach's evaluation from prior seasons.  I could also make edits to the EVALS table, such as identify players that I was interested in, players that were already drafted, and any other comments that I wanted to track.

After about 20 minutes of reviewing the data, I noticed something.  I was using data collected while the player was under a lot of stress.  The data set was also small, as each player only got 5 pitches, 5 catches, 5 throws, etc.  The better indicator as to a player's talents was in the coach's evaluations, as that represents an entire season of interaction with the player, not just a 3-4 minute period.

Based on this, I was quickly able to change my IR on the first page to also include a summary of the coach's evaluations alongside the tryout evaluations.  I sorted my report based on that, and got a very different order.  This was the order that I was going to go with for my picks.

Once the draft started, it was very easy to mark each player as drafted, so that any drafted player would no longer show up in the report.  It was also trivial to toggle the "must draft" column on and off, ensuring that if there were any younger players that I wanted, I could get them in the early rounds before we had to only draft older players.

Each time it was my pick, I already knew which player that I was going to draft.  Meanwhile, the other coaches shuffled stacks of marked up papers and attempted to navigate multiple spreadsheets when it was theirs.  Even the coordinator commented on how I was always ready and kept things moving along.

Unless you're some sort of youth athletics coach that does a draft, this application will likely do you little good.  But the concept can go a long way.  In almost any role in any organization, you likely have data for something scattered across a few different sources or spreadsheets.  This data, when isolated, only paints a blurry part of the whole picture.  But when combined and analyzed, the data can start to tell a better story, as was the case in my draft.

The technical skills required to build this application were also quite minimal.  The bulk of what I used was built-in functionality of the Interactive Report in APEX.  Merging the data and linking the two tables was really the only true technical portion of this, and that's even something that can be done by a novice.

So the next time you have a stack of data that may be somehow related, resist the temptation to use old methods when trying to analyze it.  Get it into the database, merge it as best you can, and let APEX do the rest.

I'm In Love With My Car

Floyd Teter - Tue, 2015-03-03 09:52
I'm in love with my car
Gotta feel for my automobile
Get a grip on my boy racer rollbar
Such a thrill when your radials squeal.
                    -- From Queen's I'm In Love With My Car

As is typical with Queen, citing the lyrics fail to do the song justice.  You can hear the song and watch the official video here.  C'mon back when you're done.

I learned everything I needed to know about life by listening to what is now called "Classic Rock".  So, yes, I do believe it's possible to fall in love with inanimate objects.  Especially those things with simple, yet elegant, designs.  For example, my daily driver is a 12-year-old Toyota Tundra pickup truck.  It's nothing special...no bluetooth, no OnStar, no time warp technology.  But it runs and looks like the day I took it off the showroom floor.  And it's a simple design.  I love that truck...hope we never part.

Lately, I've been reacquainting myself with an inanimate object (in this case a product) that I really love:  Taleo.  Loved Taleo well before it was assimilated by Oracle and, after recently becoming reacquainted, I've discovered that I still love it.  That love exists for one reason - simplicity:
  1. Taleo only does four things: recruit new talent, bring that new talent onboard, manage talent through performance goals, and manage your team's learning.  Four things, nothing more - simple.
  2. You won't find feature bloat from over-engineering in Taleo.  There is no attempt to address the exception to the exception to the rule.  Somebody put in great deals of thought about what to leave out of Taleo.  They got it right.
  3. The user interface is simple.  Easy to navigate.  Not ground-breaking by any means, but very intuitive.  Users like it.
  4. Taleo is only offered through the Oracle Cloud.  No hardware to buy.  Patching, upgrading, maintenance...Oracle does that.  A subscription fee, a browser and an Internet connection...that's all you need to get going.  Simple.
  5. Know what happens when you stick to simplicity for your business process scope, feature set, your UI and your underlying architecture?  You end up with an amazing user experience.  Users want to use your product.  Users love your product.
Simple really is the new cool.  I love it when I find it.  And I find it in Taleo.


Parallel Execution -- 2 PX Servers

Hemant K Chitale - Tue, 2015-03-03 09:51
I've posted a couple of examples (here and here) of Parallel Execution servers for Parallel Query.

How do we identify usage of Parallel Execution ?

I will update this post (and, possibly, subsequent post(s)) with a few methods.

The first one (as I've shown in my previous posts) is to look at the column PX_SERVERS_EXECUTIONS in either V$SQLSTATS or V$SQL.  This can identify the number of PX Servers used for an SQL (Query or DML).  However, there is a caveat when the SQL undergoes multiple execution -- the statistic on PX_SERVERS_EXECUTIONS may be cumulative (i.e. additive) across all the executions of the SQL.  UPDATE 13-Mar-15 : See the new post here.

Another method is to look at the V$PX_PROCESS and V$PX_SESSION views.

Let me demonstrate this second method using the same SQL query from my previous blog post.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 3 23:34:37 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>select distinct sid from v$mystat;

SID
----------
197

HEMANT>select count(*) from v$px_process;

COUNT(*)
----------
0

HEMANT>select count(*) from v$px_session;

COUNT(*)
----------
0

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 4
resource_manager_cpu_allocation integer 4
HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select count(*) from v$px_process;

COUNT(*)
----------
16

SYS>select qcsid, req_degree, degree, count(*)
2 from v$px_session
3 group by qcsid, req_degree, degree
4 /

QCSID REQ_DEGREE DEGREE COUNT(*)
---------- ---------- ---------- ----------
197 1
197 16 16 16

SYS>

The query by the SYS user is from a different session while the "select /*+ PARALLEL */ count(*) from Large_Table;" is being executed by HEMANT.  This query is on V$PX_SESSION and shows only when the Parallel Query sessions are active -- i.e. running HEMANT's  parallel count(*) query.  (If I query V$PX_SESSION after the parallel count(*) completes, I won't get the information).

The above output demonstrates
(a) that there were no PX servers before I began the parallel count(*) query and there were 16 at the end -- 16 PX servers had been started and had not yet shutdown by the time I queried V$PX_PROCESS (They will shutdown after a while  ** note below).
(b) that my parallel count(*) query (executed by SID 197 which is the QueryCo-ordinator -- represented by QCSID) DID request and use 16 PX server sessions (as evidenced in the output from the query on V$PX_SESSION).  Thus, what I claimed on the basis of PX_SERVERS_EXECUTION in my previous post is correct.

** Note : A few minutes later, I can see that the PX Servers have shutdown.

HEMANT>select count(*) from v$px_process
2 /

COUNT(*)
----------
0

HEMANT>


Later, I will demonstrate how to join V$PX_PROCESS and V$PX_SESSION.
I will also demonstrate how you manage the number of PX Servers.

.
.
.

Categories: DBA Blogs

12c Parallel Execution New Features: Concurrent UNION ALL - Part 1

Randolf Geist - Tue, 2015-03-03 09:31
12c introduces the concurrent UNION ALL operator that allows multiple branches below the UNION ALL to become active concurrently - which is a significant change. Before the introduction of this feature Oracle never executed multiple branches of an execution plan concurrently (in terms of Parallel Execution) - the parallelism so far was about executing the same operations of a particular branch of the execution plan by multiple processes / sessions. When we later look at the actual runtime behaviour of the new feature we'll notice that it's actually not that much different from previous behaviour than it sounds here.

By default the feature gets automatically used when there is at least one branch of the UNION ALL making use of Parallel Execution features, like a parallel full table scan for example.

If the UNION ALL operator consists of serial branches only the concurrent execution of those serial branches can be explicitly activated by using the PQ_CONCURRENT_UNION hint. To suppress the feature usage, a corresponding NO_PQ_CONCURRENT_UNION hint can be used. Both hints are officially documented.

The concurrent execution of serial branches in my opinion could be particularly useful to overcome the still existing limitation of parallelism and operations via database links: If you want to copy larger segments of data via database links you can use Parallel Execution on the source to speed up data retrieval and you can use Parallel Execution on the target to speed up the DML process, but the data will always have to go through the query coordinator process on both sides, so a copy process is always limited by the CPU and network bandwidth / latency of a single process. If you need to copy multiple objects of similar size you could simply speed up the copy process via spawning multiple jobs, but if the objects are of significantly different size you would still have to wait for the single process to cope with the largest segment.

Before 12c the only way to overcome this limitation was to employ "do-it-yourself" parallelism per (large) segment, more recently supported via DBMS_PARALLEL_EXECUTE. This allows spreading such an operation across several processes / sessions concurrently and therefore allowing to overcome the described limitation. But this approach makes the overall handling more complex (chunking, error handling etc., although DBMS_PARALLEL_EXECUTE provides a standardized interface for such tasks) and isn't a simple SQL based solution any longer.

With the concurrent UNION ALL feature in principle a SQL solution should be possible where multiple branches of a concurrent UNION ALL query (different chunks) of the same remote segment concurrently. Of course this approach still requires some manual preparation since ideally the different branches should only query disjunct parts of the segment, so for non-partitioned segments ROWID ranges should be prepared and used inside the different remote queries, similar to the DBMS_PARALLEL_EXECUTE approach. If you don't care about the duplicated work you could of course simply use something like a ORA_HASH or MOD based filter on a column expression that comes up with a reasonably equal (hash) value distribution (like a primary key).

However, due to the way the concurrent UNION ALL is implemented internally at present, this approach might not work very well, at least in 12.1.0.1 and 12.1.0.2, as I'll show in a later part.

Some other questions I would like to address regarding this feature are the following:

- How is the work distributed at runtime if you end up with a mixture of parallel and serial branches?

- If you request concurrent execution with serial branches only and no other parallel execution in the plan, how is the parallel degree to be used determined?

In this part of the blog post I want to start with the latter question first: What parallel degree gets used if a UNION ALL consists only of serial branches?

And one follow-up question could be: What happens if a more complex execution plan mixes parts with Parallel Execution outside a UNION ALL with a concurrent UNION ALL that consists only of serial branches?

Here is the table setup I'm going to use for all my tests:

-- This is the Parallel table
create table t_2
compress
as
select
rownum as id
, rpad('x', 100) as filler
from
(select /*+ cardinality(1e5) */ * from dual
connect by
level <= 1e5) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't_2')

alter table t_2 parallel 8;

-- This is the serial table
create table t2
compress
as
select
(rownum * 2) + 1 as id
, mod(rownum, 2000) + 1 as id2
, rpad('x', 100) as filler
from
(select /*+ cardinality(10000) */ * from dual
connect by
level <= 10000) a, (select /*+ cardinality(20) */ * from dual connect by level <= 20) b
;

exec dbms_stats.gather_table_stats(null, 't2')

create database link loop connect to cbo_test identified by cbo_test using '//localhost:1521/orcl12'
The database link will become relevant in the later parts of this series where I look at the execution of remote branches.

So let's start with the question about the parallel degree used in case of serial branches only. I'll use the following simple query to determine that (repeat the query block inside the UNION ALL as many times as desired, I've used 20 serial branches):

-- Can I force a completely serial UNION ALL into concurrent execution using the hint? Yes, but isn't selected by the optimizer automatically
-- In 12.1.0.2 the PQ_CONCURRENT_UNION hint works as documented as statement level hint, whereas in 12.1.0.1 there is the bug that you need to specify a query block name (Bug 15851422 : PQ_CONCURRENT_HINT DOES NOT WORK WITHOUT QUERY BLOCK SPECIFICATION)
-- What about the degree chosen in such a case, is there an upper limit?
select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
.
.
.
select regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
);
I've performed all tests on 12.1.0.2.

If you do an EXPLAIN PLAN on above query without the PQ_CONCURRENT_UNION hint, then you'll see, as documented, the default behaviour, which is serial execution of one branch after the other (I've used 20 serial branches in my tests):

-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | VIEW | |
| 3 | UNION-ALL | |
|* 4 | TABLE ACCESS FULL| T2 |
|* 5 | TABLE ACCESS FULL| T2 |
|* 6 | TABLE ACCESS FULL| T2 |
|* 7 | TABLE ACCESS FULL| T2 |
|* 8 | TABLE ACCESS FULL| T2 |
|* 9 | TABLE ACCESS FULL| T2 |
|* 10 | TABLE ACCESS FULL| T2 |
|* 11 | TABLE ACCESS FULL| T2 |
|* 12 | TABLE ACCESS FULL| T2 |
|* 13 | TABLE ACCESS FULL| T2 |
|* 14 | TABLE ACCESS FULL| T2 |
|* 15 | TABLE ACCESS FULL| T2 |
|* 16 | TABLE ACCESS FULL| T2 |
|* 17 | TABLE ACCESS FULL| T2 |
|* 18 | TABLE ACCESS FULL| T2 |
|* 19 | TABLE ACCESS FULL| T2 |
|* 20 | TABLE ACCESS FULL| T2 |
|* 21 | TABLE ACCESS FULL| T2 |
|* 22 | TABLE ACCESS FULL| T2 |
|* 23 | TABLE ACCESS FULL| T2 |
-------------------------------------
With the hint in place the plan changes to this:

-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 5 | VIEW | | Q1,00 | PCWP | |
| 6 | UNION-ALL | | Q1,00 | PCWP | |
| 7 | PX SELECTOR | | Q1,00 | PCWP | |
|* 8 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 9 | PX SELECTOR | | Q1,00 | PCWP | |
|* 10 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 11 | PX SELECTOR | | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 13 | PX SELECTOR | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 15 | PX SELECTOR | | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 17 | PX SELECTOR | | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 19 | PX SELECTOR | | Q1,00 | PCWP | |
|* 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 21 | PX SELECTOR | | Q1,00 | PCWP | |
|* 22 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 23 | PX SELECTOR | | Q1,00 | PCWP | |
|* 24 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 25 | PX SELECTOR | | Q1,00 | PCWP | |
|* 26 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 27 | PX SELECTOR | | Q1,00 | PCWP | |
|* 28 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 29 | PX SELECTOR | | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 31 | PX SELECTOR | | Q1,00 | PCWP | |
|* 32 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 33 | PX SELECTOR | | Q1,00 | PCWP | |
|* 34 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 35 | PX SELECTOR | | Q1,00 | PCWP | |
|* 36 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 37 | PX SELECTOR | | Q1,00 | PCWP | |
|* 38 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 39 | PX SELECTOR | | Q1,00 | PCWP | |
|* 40 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 41 | PX SELECTOR | | Q1,00 | PCWP | |
|* 42 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 43 | PX SELECTOR | | Q1,00 | PCWP | |
|* 44 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 45 | PX SELECTOR | | Q1,00 | PCWP | |
|* 46 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
-------------------------------------------------------------------------
Starting with 12c you'll usually get a plan note about the parallel degree the optimizer has determined, for both the traditional DOP and the in 11.2 introduced Auto DOP way (for Auto DOP this note showed already up in 11.2). However, in this case, no note section shows up.

So what degree do we then get at runtime? (XPLAN_ASH output snippet)

Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 20| 20| 1| 20| 20|
| | | | | | | |
So that's interesting, in my case I tested this on a laptop with 2 CPUs and Resource Manager active, which means that insane degrees should be downgraded automatically (or even already limited by the optimizer), but I really got a degree of 20, which doesn't make a lot of sense in this environment. However, in 12c obviously the limits enforced by the Resource Manager have been raised. In 11.2 the highest degree the Resource Manager allowed in this particular environment was 16, in 12c the limit was 27 instead (maybe 28, because in 11.2 I sometimes got 15, sometimes 16).

So pretty straightforward the number of serial branches seem to determine the degree attempted to use at runtime. This also means you need to be careful how many branches you code into the UNION ALL if you want to make use of the concurrent feature.

What happens if I mix now a Parallel Execution with a UNION ALL that consists only of serial branches, for example like this:

select /*+ PQ_CONCURRENT_UNION(@"SET$1") */ count(*) from (
select id, regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t_2
where regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t_2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
) a,
(
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
union all
select id, regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') as result from t2
where regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'c') >= regexp_replace(t2.filler, '^\s+([[:alnum:]]+)\s+$', lpad('\1', 10), 1, 1, 'i')
) b
where a.id = b.id;
Again, if I don't use the PQ_CONCURRENT_UNION hint, I'll get the traditional plan shape for the UNION ALL - which gets executed serially and then gets distributed as part of the remaining parallel operations:

-------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | Q1,02 | PCWP | |
| 6 | BUFFER SORT | | Q1,02 | PCWC | |
| 7 | PX RECEIVE | | Q1,02 | PCWP | |
| 8 | PX SEND HYBRID HASH | :TQ10000 | | S->P | HYBRID HASH|
| 9 | STATISTICS COLLECTOR | | | | |
| 10 | VIEW | | | | |
| 11 | UNION-ALL | | | | |
|* 12 | TABLE ACCESS FULL | T2 | | | |
|* 13 | TABLE ACCESS FULL | T2 | | | |
|* 14 | TABLE ACCESS FULL | T2 | | | |
|* 15 | TABLE ACCESS FULL | T2 | | | |
|* 16 | TABLE ACCESS FULL | T2 | | | |
|* 17 | TABLE ACCESS FULL | T2 | | | |
|* 18 | TABLE ACCESS FULL | T2 | | | |
|* 19 | TABLE ACCESS FULL | T2 | | | |
|* 20 | TABLE ACCESS FULL | T2 | | | |
|* 21 | TABLE ACCESS FULL | T2 | | | |
| 22 | PX RECEIVE | | Q1,02 | PCWP | |
| 23 | PX SEND HYBRID HASH | :TQ10001 | Q1,01 | P->P | HYBRID HASH|
| 24 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 25 | TABLE ACCESS FULL | T_2 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------

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

5 - access("ID"="B"."ID")
12 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
13 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
14 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
15 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
16 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
17 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
18 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
19 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
20 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
21 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
25 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))

Note
-----
- Degree of Parallelism is 4 because of table property
Since I marked the table T_2 with PARALLEL 8 I would expect the overall plan to use a degree of 8, but look at the "note" section, which tells me the degree would be 4 due to a table property...

What happens at runtime:

Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 8| 8| 1| 16| 16|
| | | | | | | |
But at runtime I get a degree of 8, so you have to be careful with these plan notes as they quite often seem to report incorrect degrees for the traditional DOP way.

If I now use the PQ_CONCURRENT_UNION hint, I get the following plan:

------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | Q1,02 | PCWP | |
| 7 | PX SEND HYBRID HASH | :TQ10000 | Q1,00 | P->P | HYBRID HASH|
| 8 | STATISTICS COLLECTOR | | Q1,00 | PCWC | |
| 9 | VIEW | | Q1,00 | PCWP | |
| 10 | UNION-ALL | | Q1,00 | PCWP | |
| 11 | PX SELECTOR | | Q1,00 | PCWP | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 13 | PX SELECTOR | | Q1,00 | PCWP | |
|* 14 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 15 | PX SELECTOR | | Q1,00 | PCWP | |
|* 16 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 17 | PX SELECTOR | | Q1,00 | PCWP | |
|* 18 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 19 | PX SELECTOR | | Q1,00 | PCWP | |
|* 20 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 21 | PX SELECTOR | | Q1,00 | PCWP | |
|* 22 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 23 | PX SELECTOR | | Q1,00 | PCWP | |
|* 24 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 25 | PX SELECTOR | | Q1,00 | PCWP | |
|* 26 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 27 | PX SELECTOR | | Q1,00 | PCWP | |
|* 28 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 29 | PX SELECTOR | | Q1,00 | PCWP | |
|* 30 | TABLE ACCESS FULL| T2 | Q1,00 | PCWP | |
| 31 | PX RECEIVE | | Q1,02 | PCWP | |
| 32 | PX SEND HYBRID HASH | :TQ10001 | Q1,01 | P->P | HYBRID HASH|
| 33 | PX BLOCK ITERATOR | | Q1,01 | PCWC | |
|* 34 | TABLE ACCESS FULL | T_2 | Q1,01 | PCWP | |
------------------------------------------------------------------------------

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

5 - access("ID"="B"."ID")
12 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
14 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
16 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
18 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
20 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
22 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
24 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
26 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
28 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
30 - filter( REGEXP_REPLACE ("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))
34 - filter( REGEXP_REPLACE ("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'c')>= REGEXP_REPLACE
("T_2"."FILLER",'^\s+([[:alnum:]]+)\s+$',' \1',1,1,'i'))

Note
-----
- Degree of Parallelism is 4 because of table property
OK, this is the concurrent UNION ALL shape now, but the "note" section still tells me the parallel degree is 4.

What happens at runtime:

Real-Time SQL Monitoring Execution Summary
-----------------------------------------------

| |PX IS|PX |PX | |PX |PX |
| |CROSS|MIN |MAX |PX |SERVERS |SERVERS |
STATUS |USERNAME |INST |DOP |DOP |INSTANCES |REQUESTED |ALLOCATED |
-------------------|------------------------------|-----|----------|----------|----------|----------|----------|
EXECUTING |CBO_TEST |N | 10| 10| 1| 20| 20|
| | | | | | | |
Oops, at runtime I now get the degree determined by the PQ_CONCURRENT_UNION part, so the overall degree is then in my case 10, and not 4 (or 8 for that matter), since my UNION ALL had 10 serial branches in this example.

So it becomes obvious that using the new feature with serial branches you have to worry a bit about the parallel degree used at runtime.

In the next instalment we'll look at the actual runtime behaviour of the feature when it gets automatically triggered by a mixture of parallel and serial branches.

I’m William Reynolds and this is How I Work

Duncan Davies - Tue, 2015-03-03 09:00

William joined PeopleSoft in November of 1995 as a developer in the Inventory product. Since then he has worked in various roles within the FSCM pillar eventually landing in the Enterprise Architecture group where he straddles the world between PeopleTools and Applications. He currently works with other Architects in each of the pillars on various projects like security, accessibility and PUM.

William

Name: William Reynolds

Occupation: Senior Application Architect, Enterprise Architecture
Location: Home office, Chicago, IL
Current computer: ThinkPad W520 & T430
Current mobile devices: iPhone 6

What apps/software/tools can’t you live without?
Probably the two pieces of software I’d be lost without are LastPass and Xmarks. Keeping track of hundreds of bookmarks along with hundreds of passwords between work and home would be nearly impossible without them. Lately I’ve also been leaning heavily on Notepad++ for various projects.

Besides your phone and computer, what gadget can’t you live without?
When I’m not on my laptop I usually have my iPad handy for email, calendar and web access.

What’s your workspace like?
I’d like to say it’s a clean, modern, calm fortress of solitude but I work from home and have three kids under the age of 12 so its state of clutter really depends on the week. I built a treadmill desk a couple years ago but these days it’s more of a standing desk. Every week you read a new article about sitting versus standing so I try to stand as much as I can during the dozen or so conference calls throughout the week. Walking while working takes some getting used to. Email is pretty easy to get through while in motion but for something like SQL or coding I find I have to be at least standing still if not sitting to really concentrate.

My workspace

What do you listen to while you work?
My tastes run the gambit and usually it depends on what task I’m doing. For things that require some heads down concentration it’s either Brubeck or Sinatra. If I’m cranking through email or pulling together a slide deck then it’s something more peppy like top 40.

What PeopleSoft-related productivity apps do you use?
It really just depends on what project I’m working on at the moment.  I prefer Firefox as my browser at work, Notepad++ for text work, and SQL Developer.

What SQL/Code do you find yourself writing most often?
Lately SELECT * FROM PSPROJECTITEM;

What would be the one item you’d add to PeopleSoft if you could?
At the moment, my ideal change would be to get rid the Windows dependency in all our client tools.

What everyday thing are you better at than anyone else?
I would say I have a knack for looking at the big picture and being able to put myself in a customer’s shoes.

What’s the best advice you’ve ever received?
Not sure where it came from but “don’t build a Cadillac when what you need is a Honda.”


My Oracle Support Essentials Webcasts - March

Chris Warticki - Tue, 2015-03-03 07:26
Oracle Product Support Advisor Webcasts March 2015
shadow1

This Month:


Oracle Support Advisor Webcast Series for March 2015 shadow1 dial Dear Valued Support Customer,
We are pleased to invite you to our Advisor Webcast series for March 2015. Subject matter experts prepare these presentations and deliver them through WebEx. Topics include information about Oracle support services and products. To learn more about the program or to access archived recordings, please follow the links.

There are currently two types of Advisor Webcasts;
If you prefer to read about current events, Oracle Premier Support News provides you information, technical content, and technical updates from the various Oracle Support teams. For a full list of Premier Support News, go to My Oracle Support and enter Document ID 222.1 in Knowledge Base search.

Sincerely,
Oracle Support

shadow2 shadow3 pen March Featured Webcasts by Product Area: Database Materialized view basics,architecture and internal working March 24 Enroll E-Business Suite Overview of Accounting for Across Organization Transaction Cycles Part 3 of 3 March 10 Enroll E-Business Suite Demantra, Are you importing Legacy data in Ascii flat files or Oracle Tables? March 11 Enroll E-Business Suite Advanced Supply Chain Planning, Collaborative Planning, and iSupplier Flow and Demonstration March 12 Enroll E-Business Suite Inventory Sales Order Flow and Troubleshooting – R12 March 18 Enroll E-Business Suite Understanding &Troubleshooting Receipts APIs In Oracle Accounts Receivable March 19 Enroll E-Business Suite Use of Forecasting in Enterprise Asset Management (eAM) March 19 Enroll E-Business Suite Understanding Lead Times in Oracle Work In Process March 25 Enroll JD Edwards JD Edwards World: Understanding Multi-Currency Intercompany Entries in A/P a! nd A/R March 10 Enroll JD Edwards JD Edwards World A9.x Language Upgrade March 17 Enroll JD Edwards JD Edwards EnterpriseOne: Overview of Voucher Match Automation March 18 Enroll JD Edwards JD Edwards World: Introduction to Sleeper March 19 Enroll JD Edwards JD Edwards World A9.3 Security - Play it Safe. Don’t let your Environment be a Target. March 24 Enroll JD Edwards JD Edwards EnterpriseOne: Getting to know the NEW Distribution Mobile Applications (Introduction) March 31 Enroll JD Edwards EnterpriseOne EnterpriseOne Table Conversion information and Troubleshooting for Upgrades March 3 Enroll JD Edwards EnterpriseOne JD Edwards EnterpriseOne: Working with Change Requests March 4 Enroll JD Edwards EnterpriseOne Benefit Enrollment Fundamentals: Setting Up Enrollment Parameters, Dates Codes and Defining Eligibility March 11 Enroll JD Edwards EnterpriseOne JD Edwards EnterpriseOne:The 5 Essentials Keys To Understanding Fiscal/Anniversary Rollover (Leave Balance Rollover) March 25 Enroll JD Edwards EnterpriseOne JD Edwards EnterpriseOne Manufacturing Commitment Processing March 26 Enroll JD Edwards World JD Edwards World: Overview of Payroll Journal Entries March 5 Enroll Middleware WebLogic Server - リクエスト応答遅延の調査に必要な情報 (Japanese Only) March 11 Enroll Middleware WebCenter Content 11g Clusters March 11 Enroll PeopleSoft Enterprise PeopleSoft HCM Updates for the Affordable Care Act March 25 Enroll PeopleSoft Enterprise Using PeopleSoft Update Manager (PUM): Top Topics March 31 Enroll Hardware and Software Engineered to Work Together Copyright © 2015, Oracle Corporation and/or its affiliates.
All rights reserved.
Contact Us | ! Legal Notices | Privacy

In Enkitec We Trust

Jason Arneil - Tue, 2015-03-03 06:02

The 2nd of March 2015 was my first day as part of the Accenture Enkitec Group.

When I first started using Exadata back in 2011, the one thing I relied on more than anything else to get me up to speed was the original Expert Oracle Exadata book by Kerry Osborne, Randy Johnson, and Tanel Poder. I am equally sure the 2nd Edition will prove just as valuable.

e-dba years

I have thoroughly enjoyed my past 3 1/2 years with e-dba. Both myself and e-dba as a company have grown enormously in this time, and it has been a really positive experience being with a growing company.

At e-dba I had all the exposure to Exadata I could have wanted and they have many Exadata customers and a large number of exa racks under their care.

It was a wrench to leave.

Feeling gravity’s pull

Over the past couple of years I have come to know and appreciate the talents of several of the members of the Accenture Enkitec Group. Kerry expressed this well in that oak table world talk at OpenWorld 2014 as an effect like a “gravitational pull” when recruiting people.

It is certainly something I felt when weighing up my options. The prospect of working with such an outstanding collection of Oracle talent was too hard to ignore.

I would always have regretted not haven taken the chance to work in this team.

I can’t wait to get started.


Using Documents Cloud Service PaaS to Accelerate Product Development

WebCenter Team - Tue, 2015-03-03 05:52

by Victor Owuor, Senior Director, Oracle Documents Cloud Service Product Management Normal 0 false false false EN-US X-NONE X-NONE

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

Many applications have to manage documents or images in addition to solving the business problems that they target. For example, a marketing campaign manager may want to drop in the very latest marketing brochure in a campaign she is running; a document that is constantly being updated by the marketing team. In general, users have certain expectation about what they should be able to do with the documents. They may want to:

  • Upload the documents from their desktop by simply dragging to specific folder. They would expect some sync client to push the document to the relevant location associated with the application.
  • Access the documents from their mobile application or from a different computer workstation or via the Web.
  • Easily but securely share the documents with other users, either within or external to their organization for work collaboration.
  • Have the documents available even when offline

Whether you are using a Cloud, on-premise or a custom application, application developers do not have to develop these capabilities from scratch. Documents Cloud Service is a Platform-as-a-Service offering from Oracle that can be easily leveraged with Oracle SaaS, on-premise and any third party applications. Oracle Documents Cloud Service provides a platform that developers can use to expose those features quickly with minimal development effort. Developers have a choice between:

  • Iframe Embedding - Embedding the Document Cloud Service HTML interface within their application to surface the document capability. This approach does not require you to implement a user interface to the Document Cloud Service and automatically surfaces all features the Service in your application.
  • API - Surfacing Document Cloud Service within the user interface of their application by making web service calls to the Document Service API. This approach allows you to create a custom user interface that is tailored to your specific application.

Additionally, developers may use Applinks to leverage the same user credentials for Oracle Documents.

This article will focus on Iframe Embedding. We will address the API in a future article.

Iframe Embedding

You can embed the Oracle Documents Cloud Service interface in an inline frame (IFRAME tag). When you do that, you may want to remove the default branding and other aspects of the user interface. You can do that by adding “/embed” to the URL of the page that you would like to embed.

For example, the top level folder URL in the standard interface is as follows:

https://www.example.com/documents/home/nameasc

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

If you change the URL as follows, you will get a user interface that is better suited to embedding.

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

https://www.example.com/documents/embed/home/nameasc

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

You can embed that iframe within your application as follows:

<!doctype html>

<html>

<head>

<title>IFRAME DEMO</title>

</head>

<body>

<iframe src="http://docs.oracle.com/cloud/latest/documentcs_welcome/WCCCD/odcs-embed.htm#WCCCD3791" width="100%" height="300">

<p>Your browser does not support iframes.</p>

</iframe>

<iframe src="https://www.example.com/documents/embed/home/nameasc" width="100%" height="300">

<p>Your browser does not support iframes.</p>

</iframe>

</body>

</html>

Here is another example to show how you can embed the preview of a file.

The file preview URL

https://www.example.com/documents/fileview/D92DA147AC6B1F0698DFB7A2F6C3FF17C1177A968060/
_Getting_Started_with_PBCS.docx

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

Normal 0 false false false EN-US X-NONE X-NONE

To get to the embed view for Preview, just add “/embed” into the URL e.g.

https://www.example.com/documents/embed/fileview/
D92DA147AC6B1F0698DFB7A2F6C3FF17C1177A968060/_Getting_Started_with_PBCS.docx

Normal 0 false false false EN-US X-NONE X-NONE

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

You can find more help on the help on how to embed the Web UI here.

And for more information and materials on Documents Cloud Service, visit us here. Until the next time!

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-bidi-font-family:"Times New Roman";}

Benefit from Organizing Assets and Users with Support Identifier Groups

Joshua Solomin - Mon, 2015-03-02 19:43

Customer User Administrators (CUAs) are often tasked with managing employee access to Oracle support resources. Oracle uses Support Identifiers (SIs) to help CUAs identify which employees need access to support, and the available resources when they do.

If you have support users scattered across a large geographic area, or have to manage dozens (or possibly hundreds) of software and hardware assets, use Support Identifier Groups (SIGs) to simplify the process of giving your users access to support resources. SIGs can help you align privileges and service request flows to specific projects, locations, or assets.

To make the most of Support Identifier Groups, you will need to pre-plan how users and assets are best organized. Once defined you can set up your Groups, adding users and assets logically the way you need them. When your organization purchases new hardware or software assets (with an associated new SI), these can automatically be assigned to a default SIG that you establish.

NOTE: Be aware that the Default SI will apply to all new assets in all new orders across your Account/Organization. 

Simple. Easy. Maintainable. Visit the Support Identifier Groups Information Center to learn more, view videos and instructions on how to get started, and best practices.

Different plan_hash_value same plan

Bobby Durrett's DBA Blog - Mon, 2015-03-02 15:38

I mentioned this same effect in an earlier post about SQL profiles: link

I get a different plan_hash_value values for a query each time I run an explain plan or run the query.  I see this in queries whose plan includes a system generated temporary segment like this:

|   1 |  TEMP TABLE TRANSFORMATION   |                             |
...
|  72 |    TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D668C_764DD84C |

For some reason the system generated temporary table name gets included in the plan_hash_value calculation.  This makes plan_hash_value a less than perfect way to compare two plans to see if they are the same.

Last week I was using my testselect package to test the effect of applying a patch to fix bug 20061582.  I used testselect to grab 1160 select statements from production and got their plans with and without the patch applied on a development database.  I didn’t expect many if any plans to change based on what the patch does.  Surprisingly, 115 out of the 1160 select statements had a changed plan, but all the ones I looked at had the system generated temporary table names in their plan.

Now, I am going to take the queries that have different plans with and without the patch and execute them both ways.  I have a feeling that the plan differences are mainly due to system generated temp table names and their execution times will be the same with and without the patch.

I’ve run across other limitations of plan hash value as I mentioned in an earlier post: link

I’m still using plan_hash_value to compare plans but I have a list of things in my head that reminds me of cases where plan_hash_value fails to accurately compare two plans.

– Bobby

P.S. After posting this I realized that I didn’t know how many of the 115 select statements with plans that differed with and without the patch had system generated temp tables.  Now I know.  114 of the 115 have the string “TEMP TABLE TRANSFORMATION” in their plans.  So, really, there is only one select statement for which the patch may have actually changed its plan.

P.P.S. I reapplied the patch and verified that the one sql_id didn’t really change plans with the patch.  So, that means all the plan changes were due to the system generated name.  Also, all the executions times were the same except for one query that took 50 seconds to parse without the patch and 0 with the patch.  So, one of the queries with the system generated temp table name happened to benefit from the patch.  Very cool!

P.P.P.S This was all done on an 11.2.0.4 Exadata system.

Categories: DBA Blogs

Parallel Execution -- 1b The PARALLEL Hint and AutoDoP (contd)

Hemant K Chitale - Mon, 2015-03-02 09:38
Continuing the previous thread, having restarted the database again, with the same CPU_COUNT and missing I/O Calibration statistics  ....

The question this time is : What if the table level DoP is specifically 1 ?

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 2 23:22:28 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 4
SYS>show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 135
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SYS>
SYS>select * from dba_rsrc_io_calibrate;

no rows selected

SYS>
SYS>connect hemant/hemant
Connected.
HEMANT>set serveroutput off
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>alter table large_table parallel 1;

Table altered.

HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>select /*+ PARALLEL */ count(*) from LARGE_TABLE;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 47m7qyrj6uzqn, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from LARGE_TABLE

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2622 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 2622 (1)| 00:00:32 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

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

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select px_servers_executions from v$sqlstats where sql_id='47m7qyrj6uzqn';

PX_SERVERS_EXECUTIONS
---------------------
8

HEMANT>

Aaha ! Again ! The same SQL statement, the same SQL_ID, the same Execution Plan (Plan Hash Value) and Oracle chose to use 8 PX Servers for the query !  Again, ignoring the table level DoP (of 1)

So, once again, we see that Oracle actually computes a DoP that looks like it is CPU_COUNT x PARALLEL_THREADS_PER_CPU. Let's verify this.

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_threads_per_cpu=4;

System altered.

SYS>alter system flush shared_pool;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

COUNT(*)
----------
4802944

HEMANT>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8b0ybuspqu0mm, child number 0
-------------------------------------
select /*+ PARALLEL */ count(*) from Large_Table

Plan hash value: 2085386270

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1311 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 1311 (1)| 00:00:16 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------

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

6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select px_servers_executions from v$sqlstats where sql_id='8b0ybuspqu0mm';

PX_SERVERS_EXECUTIONS
---------------------
16

HEMANT>

YES SIR ! Oracle chose to use 16 PX Servers this time. So that does look like CPU_COUNT x PARALLEL_THREADS_PER_CPU.  Have you also noticed the COST ?  The COST has also dropped to half.  So, the COST is also computed based on the number of PX Servers that it expects to be able to grab and use.

.
.
.


Categories: DBA Blogs

Oracle Data Provider for .NET now on NuGet

Christian Shay - Mon, 2015-03-02 08:30

ODP.NET, Managed Driver is now on NuGet, meaning that you can add ODP.NET to your Visual Studio project with just a few clicks in the NuGet Package Manager. We've also published an Oracle By Example walkthrough to take you step by step through the process of using NuGet and ODP.NET.

Here we are in the NuGet Package Manager:




When searching for us in the package manager, make sure to get the official package - look for the word "Official" in the title.



There's actually two NuGet packages available:

ODP.NET, Managed Driver - Official
NuGet id: Oracle.ManagedDataAccess

This adds Oracle.ManagedDataAccess.dll to your project and also makes needed configuration entries in your app.config or web.config.


ODP.NET, Managed Entity Framework Driver - Official
NuGet id: Oracle.ManagedDataAccess.EntityFramework

This adds Oracle.ManagedDataAccess.EntityFramework.dll as well as config file configuration. It also has a dependency on the ODP.NET package above and will pull it into your project  as well as EF 6 if needed.

If you want to host this package on your local intranet, it is also available for download on the OTN .NET download page.

Please note that if you want to use Visual Studio integration features, such as browsing your Oracle Schema in Server Explorer, or using Entity Designer or Table Adapter Configuration wizard, you should still install Oracle Developer Tools for Visual Studio, as a NuGet package  does not provide any of the Visual Studio integration components needed to do design time work.

Query the Enterprise Manager collected metrics

Yann Neuhaus - Mon, 2015-03-02 08:10

Enterprise Manager (Cloud Control for example) gathers a lot of metrics. You can display them from the GUI, but you can also query the SYSMAN views directly. Today, I wanted to get the history of free space in an ASM disk group for the previous week. Here is how I got it.

IR Scrolling - With a Little Help From My Friends

Denes Kubicek - Mon, 2015-03-02 04:02
If you are working with interactive reports you will for sure be faced with a problem of wide reports. If you are taking care of the page layout and eventually have more than just an interactive report on the page, you will want to limit it's size to something making sense. The first problem will appear if you limit the width by setting the region attribute to something like this

style="width:830px"

and you will not see some of the columns:



If you add a scrolling by wrapping the region in a div and adding the following to the region header:

<div style="width:810px;overflow-x:scroll">

and closing it in the footer by adding:

</div>



you will be able to scroll with two ugly side effects:

  • The action bar will be included in the scrolling as well and disappear as you scroll to the right.
  • The sort widgets for the columns will appear on the wrong position the more you scroll.




  • You can solve this problem in the following way:

  • Remove the scrolling DIV from the region header / footer.
  • Use this java script in the page Function and Global Variable Declaration:

    function onload_ir(p_width, p_report_id){

    $('<div id="scroll_me" style="width:' + p_width + 'px;overflow-x:auto;display:inline-block"></div>').insertBefore('#apexir_DATA_PANEL'); $("#apexir_DATA_PANEL").appendTo("#scroll_me"); $("#apexir_DATA_PANEL").show();

    var or_Finished_Loading = gReport._Finished_Loading; gReport._Finished_Loading = function(){ or_Finished_Loading(); if(gReport.current_control=='SORT_WIDGET'){

    var offset_pos = $("#" + p_report_id ).position().left; var pos_left = $('#apexir_rollover').css('left'); pos_left = pos_left.replace('px',''); if (pos_left>p_width-100) {new_pos = parseFloat(pos_left) + parseFloat(offset_pos) - 25; $('#apexir_rollover').css('left', new_pos+'px');} }; }; };


  • Create a Dynamic Action which runs After Refresh (fire on page load option should be turned on) of the IR region and execute this script there:

    onload_ir(810, 7990109002761687)


  • 810 is the widht of the scolling region, which is a bit less then the total width of the region.

  • 7990109002761687 is the id of the data grid of the interactive report. You can find this id if you use firebug and scroll to the point where the data grid is placed.




  • What this script does is:

  • It will wrap the data grid into an additional div and add a scroll bar to it.
  • It will overwrite the IR onload function and add a sort widget positioning function to it in order to reposition the widget according to the scrolling.
  • The important part of the overloading function was done by Tom Petrus, who is a big help when it comes to tricky stuff like this.

    Now, once you have done that, your report will show up properly once you scroll it.



    Enjoy.
    Categories: Development

    Webcast - Oracle Database 12c High Availability New Features

    Organizations today are dependent on IT to run efficient operations, quickly analyze information and compete more effectively. Consequently, it is essential that their IT infrastructure and databases...

    We share our skills to maximize your revenue!
    Categories: DBA Blogs