Feed aggregator

Database as a Service

Pat Shuff - Wed, 2016-05-04 16:38
Today we are going to dive into Database as a Service offered from Oracle. This product is the same product offered by Oracle as a perpetual processor license or perpetual named user license for running database software in your data center. The key different is that the database is provisioned onto a Linux server in the cloud and rather than paying $47,500 for a processor license and 22% annually after that, you pay for the database services on an hourly or monthly basis. If you have a problem that needs only a few weeks, you pay for the service for a few weeks. If you have a problem that takes a very large number of processors but for a very short period of time, you can effectively lease the large number of processors in the cloud and purchase a much smaller number of processors in your data center. Think of a student registration system. If you have 20K-30K students that need to log into a class registration system, you need to size this server for the peak number of students going through the system. In our example, we might need an 8 core system to handle the load during class registration. Outside the two or three weeks for registration, this system sits idle at less than 10% utilization because it is used to record and report grades during the semester. Rather than paying $47.5K times 8 cores times 0.5 for an x86 or Sparc server ($190K), we only have to pay $47.5K times 2 cores times 0.5 for x86 or Sparc cores ($47.5K) and lease the additional processors in the cloud for a month at $3K/core/month ($24K). We effectively reduced the cost from $190K to $71.5K by using the cloud for the peak period. Even if we do this three times during the year the price is $119.5K which is a cost savings of $70.5K. The second year we would be required to pay $41.8K in support cost for the larger server. By using the smaller server we drop the support cost to $10.5K. This effectively pays for leasing a third of the cloud resources by using a smaller server and bursting to the cloud for high peak utilization.

Now that we have looked at one of our use cases and the cost savings associated with using the cloud for peak utilization and reducing the cost of on servers and software in our data center, let's dive into the pricing and configuration of Database as a Service (DBaaS) offered by Oracle in the public cloud services. If we click on the Platform -> Database menu we see the following page.

If we scroll down to the bottom we see that there are effectively three services that we can use in the public cloud. The first is Database Schema as a Service. This allows you to access a database through a web interface and write programs to read and present data to the users. This is the traditional Application Express interface or APEX interface that was introduced in Oracle 9. This is a shared service where you are given a database instance that is shared with other users. The second service is Database as a Service. This is the 11g or 12c database installed on a Linux installation in the cloud. This is a full installation of the database with ssh access to the operating system and sqlplus access to the database from a client system. The third service is Exadata as a Service. This is the Oracle database on dedicated hardware that is optimized to run the Oracle database.

The Schema as a Service is also known as Application Express. If you have never played with apex.oracle.com, click on the link and register for a free account. You can create an instance, a database schema, and store upto 10 MB or 25 MB of data for free. If you want to purchase a larger storage amount it is sold in 5 GB, 20 GB, or 50 GB increments.

The 10 or 25 MB instance is free. The 5 GB instance is $175/month. The 20 GB is $900/month, and the 50 GB is $2,000/month.

Tomorrow we will dive a little deeper into Schema as a Service. In summary, this is a database instance that can contain multiple tables and has an application development/application web front end allowing you to access the database. You can not attach with sqlplus. You can not attach with port 1521. You can not put a Java or PHP front end in front of your database and use it as a back end repository. You can expose database data through applications and REST api interfaces. This instance is shared on a single computer with other instances. You can have multiple instances on the same computer and the login give you access to your applications and your data in your instance.

The Database as a Service (DBaaS) is slightly different. With this you are getting a Linux instance that has been provisioned with a database. It is a fully deployed, fully provisioned database based on your selection criteria. There are many options when you provision DBaaS. Some of the options are virtual vs full instance, 11g vs 12c, standard edition vs enterprise edition vs enterprise edition high performance vs enterprise edition extreme performance. You need to provide an expected data size and if you plan on backing up the data and a cloud object repository if you do. You need to provide ssh keys to login as oracle or opc/root to manage the database and operating system. You also need to pick a password for the sys/system user inside the database. Finally, you need to pick the processor and memory shape that will run the database. All of these options have a pricing impact. All of these options effect functionality. It is important to know what each of these options means.

Let's dive into some of these options. First, virtual vs full instance. If you pick a full instance you will get an Oracle Enterprise Linux installation that has the version of the database that you requested fully installed and operational. For standard installations the file system is the logical volume manager and the file system is provisioned across four file systems. The /u01 file system is the ORACLE_HOME. This is where the database binary is installed. The /u02 file system is the +DATA area. This is where table extents and table data is located. The /u03 file system is the +FRA area. This is where backups are dropped using the RMAN command which should run automatically every night for incremental backups and 2am on Sunday morning for a full backup. You can change the times and backup configurations with command line options. The /u04 area is teh +RECO area. This is where change logs and other log files are dropped. If you are using Data Guard to replicate data to another database or from another database, this is where the change logs are found.

If you pick a virtual instance you basically get a root file system running Oracle Enterprise Linux with a tar ball that contains the oracle database. You can mount file systems as desired and install the database as you have it installed in your data center. This configuration is intended to mirror what you have on-premise to test patches and new features. If you put everything into /u01 then install everything that way. If you put everything in the root file system, you have the freedom to do so even though this is not the recommended best practice.

The question that you are not asked when you try to create a DBaaS is if this service is metered or non-metered. This question is asked when you create your identity domain. If you request a metered service, you have the flexibility to select the shapes that you want and if you are billed hourly or monthly. The rates are determined by the processor shape, amount of memory, and what database option you select (standard, enterprise, high performance, or extreme performance). More on that later. With the metered option you are free to stop the database (but not delete it) and retain your data. You suspend the consumption of the database license but not the compute and storage. This is a good way of saving a configuration for later testing and not getting charged for using it. Think of it as having an Uber driver sit outside the store but not charge you to sit there. When you get back in the car the charge starts. A better analogy would be the Cars2Go. You can reserve a car for a few hours and drive it from Houston to Austin. You park the car in the Cars2Go parking slot next to the convention center and don't pay for parking. You come out at the end of your conference, swipe your credit card and drive the car back to Houston. You only get charged for the car when it is between parking lots. You don't get charged for it while it is parked in the reserved slot. You pay a monthly charge for the service (think of compute and storage) at a much lower rate. If you think of a non-metered service as renting a car from a car rental place, you pay for the car that they give you and it is your until you return it to the car rental place. You can't not pay for the car while you are in your convention as with Card2Go. You have to pay for parking at the hotel or convention center. You can't decide half way into your trip that you really need a truck instead of a car or a mini-van to hold more people and change out cars. The rental company will end your current agreement and start a new one with the new vehicle. Non-metered services are similar. If you select an OC3M shape then you can't upgrade it to an OC5 to get more cores. You can't decide that you need to use the diagnostics and tuning and upgrade from enterprise edition to enterprise edition high performance. You get what you started with and have 12 months to consume the services reserved for you.

The choice of 11g or 12c is a relatively simple one. You get 11.2.0.4 running on Oracle Enterprise Linux 6.6 or you get 12.1.0.2 running on Oracle Enterprise Linux 6.6. This is one of those binary questions. You get 11g or 12c. It really does not effect any other question. It does effect features because 12c has more features available to it but this choice is simple. Unfortunately, you can't select 11.2.0.3 or 10.whatever or 9.whatever. You get the latest running version of the database and have an option to upgrade to the next release when it is available or not upgrade. Upgrades and patches are applied after you approve them.

The next choice is the type of database. We will dive into this deeper in a couple of days. The basic is that you pick Standard Edition or Enterprise Edition. You have the option of picking just the base Enterprise Edition with encryption only, with most of the options in the High Performance Option, or all of the options with Extreme Performance Option. The difference between High Performance and Exterme Performance is the Extreme included Active DataGuard, In-Memory options, and Real Application Clustering options. Again, we will dive into this deeper in a later blog entry.

The final option is the configuration of the database. I wanted to include a screen shot here but the main options that we look at are the CPU and memory shape which dictates the database consumption cost as well as the amount of storage for table space (/u02) and backup space (/u03 and /u04). There are additional charges above 128 GB for table storage and for backups. We will not go into the other options on this screen in this blog entry.

In summary, DBaaS is charged on a metered or un-metered basis. The un-metered is a lower cost option but less flexible. If you know exactly what you need and the time that it is needed, this is a better option. Costs are fixed. Expenses are predictable. If you don't know what you need, metered service might be better. It gives you the option of starting and stopping different processor counts, shutting off the database to save money, and select different options to test out different features. Look at the cost option and a blog that we will do in a few days analyzing the details on cost. Basically, the database can be mentally budgeted as $3K/OCPU/month for Enterprise Edition, $4K/OCPU/month for High Performance, and $5K/OCPU/month for Extreme Performance. Metered options typically cross over at 21 days. If you use metered service for more than 21 days your charges will exceed this amount. If you use it for less, it will cost less.

The Exadata as a Service is a special use case of Database as a Service. In this service you are getting a quarter, half, or full rack of hardware that is running the database. You get dedicated hardware that is tuned and optimized to run the Oracle database. Storage is dedicated to your compute nodes and not one else can use these components. You get 16, 56, or 112 processors dedicated to your database. You can add additional processors to get more database power. This service is available in a metered or non-metered option. All of the database options are available with this product. All of the processors are clustered into one database and you can run one or many instances of a database in this hardware. With the 12c option you get multi-tenant features so that you can run multiple instances and manage them with the same management tools but give users full access to their instance but not other instances running on the same database.

Exadata cost for metered services

Exadata cost for non-metered services

In summary, there are two options for database as a service. You can get a web based front end to a database and access all of your data through http and https calls. You can get a full database running on a Linux server or Linux cluster that is dedicated to you. You can consume these services on a an hourly, monthly, or yearly basis. You can decide on less expensive or more expensive options as well as how much processor, memory, and storage that you want to allocate to these services. Tomorrow, we will dive a little deeper into APEX or Schema as a Service and look at how it compares to services offered by Amazon and Azure.

Enjoy these Snappy Video Clips from Oracle's Modern Customer Experience

Linda Fishman Hoyle - Wed, 2016-05-04 13:39
Oracle's Modern Customer Experience held in Las Vegas, NV, April 26-28, 2016, proved to be an amazing gathering of more than 4,200 CX professionals, including marketers, sales people, customer service executives, and e-commerce professionals. It was a high-energy conference powered by masterful presentations from Troy Carter, founder/CEO of Atom Factory, entrepreneur, investor, and manager, and Mark Hurd, Oracle co-CEO.

The vibe was felt all over the MGM compound, but even more so in the demo and exhibit area. This is where video mavens Ruth Kale-Fok, Maureen Boctor, Brian Mock, David Hope-Ross, Martin Taylor, and Kathryn Perry were working their camera and interview magic.

You know, some people freeze when they are asked to speak on camera.

Generally, analysts and influencers don't fall into that camp.

True to form, the analysts and influencers at the conference were more than happy to share their viewpoints on the importance of customer experience and the role Oracle is playing with its end-to-end solution.

Watch this short video starring Dr. Natalie Petouhoff from Constellation Research, Keith Dawson and Aphrodite Brinsmead from Ovum, Rebecca Wetterman from Nucleus Research, and Mark Smith from Ventana Research.

They also captured some great quotes from conference attendees in these video clips:

Want to grow your revenue? Ask our CEO how …

Linda Fishman Hoyle - Wed, 2016-05-04 12:02
At the Modern CX Experience last week in Las Vegas, Oracle's co-CEO Mark Hurd told the audience that CEOs have about 18 quarters (4.5 years) to prove themselves before activist shareholders get involved. In an environment where global economic growth is anemic at best and rapidly declining at worst (consider Russia, Brazil, etc.), most CEOs look for quick ways to gain upside on earnings vs. revenue.

Many turn to slashing expenses as a sure way to deliver desired earnings results. Others try to build new products or lower prices to lure customers from the competition. But what they miss is that their success, and that of their company, lies with their existing customers. The American Customer Satisfaction Index says:

Companies with happier customers give five times the return than companies with unhappy customers.

A 5x return would validate any CEO―and thrill his or her board members and shareholders. But pushing good customer service to get happier customers isn’t exactly jazzy or unique for a nervous CEO who needs to show results quickly.

However, the research doesn’t lie; nor do our personal experiences. In his keynote, Mark shared his frustration about his bank and his US-based cell phone service provider. Both companies have siloed processes which translate into unnecessary and irritating interactions―and plenty of lost opportunities to engage and delight customers like him.

So Mark’s advice to the CEOs and professionals in the audience was to step up their customer experience games with a suite of integrated applications. “There are lots of CX providers so it’s easy to go down the path of more silos; you’ll get some benefits, innovation, but the suite is the real benefit,” said Mark.  The recipe for success is really pretty simple―get to know your customers’ wants and needs and serve them well.

Takeaways:

Oracle is the only vendor with an integrated suite.

Oracle is the leader.

Oracle is committed to providing best-of-breed integrated solutions in each area.

Oracle can help every CEO move closer to that 5x return.

Go Mobile and Upgrade your Oracle Forms with Auraplayer

WebCenter Team - Wed, 2016-05-04 10:38

Author: Mitchell Palski – Oracle Fusion Middleware Specialist

Let’s start off with this? Why would you upgrade your Oracle Forms applications? Oracle Forms Builder and Oracle Forms Services have been upgraded to simplify the development and deployment of Forms applications on the Web. In a world where very few applications stand-alone and need to integrate into their Enterprise, Oracle recognizes the importance of being able to persist your legacy Forms applications into the future while enabling your Service-Oriented Architecture to leverage their capabilities. The future of Forms includes improving the Java-based Web user interface and extending product "openness" by allowing Java integration on all three tiers

Aside from improving your traditional Oracle Forms offering, there is a new consideration that many of us have never had to deal with in the past – mobile access. Oracle Forms and Reports are such convenient tools for developing applications for our mobile workforce, but yet they’ve been restricted to the use of laptops and desktop machines. Here’s the good news – Oracle partner Auraplayer has the skillsets and the software to help your organization quickly and efficiently overcome that obstacle.

Why did I say “skillset”? Well, Auraplayer has some really talented people in their management ranks that have been working with Oracle’s Fusion Middleware technology for a long time. One of the challenges that I had when I first heard about Auraplayer was understanding their value for my customers, so I talked to CEO Mia Urman and she helped explain to me that Auraplayer isn’t just selling a product (you’ll hear about that in a minute), they’re also helping customers upgrade. Oracle Forms has been around for so long that there aren’t a ton of projects out there that have the same personnel on staff as when they first started. Auraplayer brings the industry expertise to mitigate your risks and deliver a successful Forms upgrade so your team can start taking advantage of all the new features 12c has to offer.

Upgrades aren’t the most exciting projects in the world, so woopty-doo right? Well, the reason that I’m excited is because if you’re working with Auraplayer to upgrade your Forms application why not let them tell you about their Mobile offering while they’re their? I won’t try to go through all of their features in this post today, but let me give you the highlights (from my perspective):
  1. You don’t have to change your Forms applications. Re-writing code to enable a mobile UI can be costly, it can take a long time to complete, and it requires support from your development team after deployment.
  2. Auraplayer will REST-enable your Forms application so you can consume services across the Enterprise, making integration easy and mobile development fast and efficient.
  3. Couple Auraplayer with Oracle Mobile Cloud Service and you have a complete project plan from start to finish.
    • Define and enable processes
    • Automatically generate services
    • Develop UI and consume services – works on any mobile device

So, do your end users have smart phones and tablets? Yes they do. Do you need up upgrade your legacy Forms apps and get those users actively using them in the field? You tell me!

New York City Metropolitan Transportation Authority (NYC MTA) figured out a way to use Auraplayer to “work smarter, not harder” by leveraging Auraplayer. Read about their success here: https://blogs.oracle.com/mobile/entry/new_york_mta_mobile_cloud 

If you want to learn more, check out these videos by Grant Ronald:

What is Oracle Cloud Management Pack in Enterprise Manager Cloud Control?

Arun Bavera - Wed, 2016-05-04 10:28
Cloud Management pack basically consists of these features:
These packs are basically used to setup your private in-house Cloud Services ( MWaaS, DBaaS, IaaS, PaaS, SCHaaS, SnapClone etc…)
Without these packs we cannot use any self-service provisioning features.
With DBLM you can use Deployment Procedure to create/clone new DB’s but you cannot use self-service portal.
The traditional DB LifeCycle Management provides all the management of DB but there are some exception of features like below:
Note: The ability to create 'new' full clones is licensed under Database Lifecycle Management Pack for Oracle Database, but the additional 'refresh' function requires licensing the Cloud Management Pack for Oracle Database.
clip_image001
clip_image002

Let us discuss a Scenario where Customer may require Hybrid Cloud Management capabilities:
Customer OMS is running on-premise and they have some cloud services subscriptions also.
If they just want to manage their cloud service targets they can use Hybrid Agent to manage making sure they have the traditional license pack for those targets to have single pane of view of all their on-premise traditional targets and Cloud targets.
They can also use the REST API to manage and view their cloud services which comes with cloud service offerings.
If the customer is not going to build any in-house private cloud service ( MWaaS, DBaaS, IaaS, PaaS, SCHaaS, SnapClone etc…) using OEM, they may not require Cloud Management packs.
Cloud Management pack provides top up features to their traditional packs.
Oracle Cloud Management Pack for Oracle DatabasePrerequisitesThe Oracle Cloud Management Pack for Oracle Database requires the Database Lifecycle Management Pack for Oracle Database.
 
As of now to to raise a request to create an instance of DBCS or JCS  on Oracle Cloud  you have to use REST API or  Cloud UI.
Hope we will soon have integration in OEM cloud management pack capability to raise service request directly against Oracle Cloud Service ( Cloud service hosted at  Oracle Data center or at customer as Private Cloud Service)
which will create an instance in cloud and will discover the assets and manage in OEM .






Categories: Development

Transforming Analytics and Reporting

PeopleSoft Technology Blog - Wed, 2016-05-04 10:12

Content contributed by Balaji Pattabhiraman

A few months ago, I wrote in this blog about a feature in PeopleTools called Simplified Analytics.  This is really transformative technology that puts tremendous power in the hands of end users, enabling them to perform ad-hoc analytics right in context of their transactions and business processes.  It makes reporting timely, secure, and relevant.

Let's expand on that concept and take advantage of some other PeopleTools featues.  In this case, imagine I am a manager or subject area expert.  I've created several simplified analytics that I want to make available to my team for use in their daily work.  Simplified analytics enables me to create a tile from any analytic I create.  Those tiles can then be placed on any Fluid home page or Dashboard, which is another new PeopleTools feature.  Lets see an example of this.

As a business administrator I've created several simplifed analytics for the My Team page.  These are available in the My Analytics tab in Related Information.

Note that each tab on this page--Summary, Performance, Compensation, and Leave Balances--contains different analytics relevant to each topic.  I've created these analytics  to provide better decision support for these processes.

This calls for a quick review of an important part of the Simplified Analytics process.  When creating or editing the analytic, I have the option of publishing the analytic so it can be used by others.  (See this post for more info on creating a Simplified Analytic.)



When I choose to publish, I have the option of publishing to the My Analytics section of the Related Actions frame or publishing to the Tile Repository. Since I'll be creating an analytic dashboard, I'll save to the repository.



Now I can go to the Personalize option from the menu in any home page.  From here I can create my new analytic home page.  (This could be created as a Dashboard as well.)



In this case, I'll create a new home page called Team Analytics.  This will provide a single access point for our group of analytics, making them available in a convenient form to all team members.



Now that I've created the new home page, I can add tiles representing the analytics that were created previously.



I'll add several analytic tiles and make this home page a robust and valuable analytic tool.



Once I save the page it is displayed.  Here you see the home page with all the tiles I've added.



Now let's publish the home page and make it available to the team so all can benefit from it.  (Note: If I don't publish the home page to other users I can still use it as a personal home page.  One might do this for analytics that are used exclusively by me.) 



Enter a name and label and any other field data necessary. In this case I'm making it public, but in many cases you will want to make a page like this available only to a particular role or permission list.

Now when someone on our team logs in, (in this case Rosanna) she will have access to the new Team Analytics page.

Note that the data from the analytics correspond to Rosanna's team.  If the same analytics were published to a different group, the data would be relevant to that group.  The data, context, and security are determined by the PS Query that forms the basis of the analytic.

This illustrates how you can extend the power of Simplified Analytics to make valuable decision support available to teams, not just individuals.

About Oracle: DBA_DEPENDENCY_COLUMNS

Jeff Moss - Wed, 2016-05-04 08:17

A colleague asked if there was a way to do column level dependency tracking recently. He wanted to know for a given view, which tables and the columns on those tables, it was dependent upon, without, of course, reading through the code.

I was vaguely aware that since 11gR1 Oracle has been tracking fine grained (column) dependencies, but couldn’t find a way of seeing the details stored, until I found this interesting article from Rob Van Wijk:

About Oracle: DBA_DEPENDENCY_COLUMNS

I passed the details on to our DBA who implemented it and it seemed to work, for us. Your mileage may vary, of course.

Some comments on Rob’s blog post, bearing in mind, of course, that it was written in 2008 and refers to 11gR1:

  1. D_ATTRS contains values other than “000100000A”. I’ve observed this in a basic 12c install and a production 11gR2 install
  2. D_ATTRS is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as “/* Finer grain attr. numbers if finer grained */”
  3. D_REASON is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as “/* Reason mask of attrs causing invalidation */”.  On my basic 12c installation, all rows contain NULL for this value although on a production 11gR2 database I observed a handful of rows with values in this column.
  4. Noted from the comments against Rob’s article is the opportunity to vote for this feature on OTN here

 

Enable Copy n Paste in SQL*Plus command line on Windows

Jeff Moss - Wed, 2016-05-04 01:04

My colleague asked me yesterday how to enable copy and paste in the command line SQL*Plus window on Windows 7 – a simple enough task…

On the shortcut that starts the command line version of SQL*Plus, right click and bring up the Properties dialog. Nagivate to the Options tab and make sure the QuickEdit mode is checked on, as below:

image

Now start SQL*Plus and you’ll find that you can hold the left mouse button down whilst dragging a selection area and then pressing return copies the selected text, whilst pressing the right mouse button pastes the copied text.

If you’d prefer to read this from a Microsoft source, try here, where other methods of setting this up are detailed as well as enabling the Autocomplete facility.

Samsung Developers Conference 2016: A More Connected Future

Oracle AppsLab - Tue, 2016-05-03 20:31

After a whirlwind day at Modern CX, I hurried my way back up to San Francisco for the last day of the Samsung Developers Conference 2016. The morning started out exciting with a giveaway gift of the Samsung Gear 360 Camera.

Oh i have plans for you

Partner Webcast - Oracle Database Cloud Service: Cloud Migration Options

Oracle’s Public Cloud leverages existing technologies and best practices to deliver Oracle Database Technologies utilizing Oracle’s secure and optimized cloud-computing platform. At cloud.oracle.com...

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

Intro to PaaS

Pat Shuff - Tue, 2016-05-03 16:44
Today we are going to move up the stack. We will first focus on the Oracle solutions talking about the different platform as a service offerings. It is important to spend a little time reviewing this layer because what one company calls PaaS, another calls SaaS. The best way to get started is to go to cloud.oracle.com and look at the pull downs at the top of the screen. We see Infrastructure, Platform, and Applications.

When we pull down the Platform menu we see that there are different areas that we can dive into.

Data management is the first area that we will review. This is basically a way to aggregate and look at data. We can store data in a database, store on-premise databases into the cloud, store data in NoSQL repositories, and do analytics on a variety of data with Big Data Preparation and Big Data services. All of these involve pulling data into a repository of some type and performing queries against the repository. The key difference is the way that the data is stored, how we can ask questions, and the results that we get back. At this point we will not dive into any of these deeply but at a later point dive deep into the database and database backup.

The Application Development is moving farther away from the technology of storing data and moving closer to how we present data to users. The Java platform, for example, allows us to do things like create a shopping cart or hosting more complex applications in a Java repository or container. The Mobile Cloud Service allows us to dive into existing applications and present a user interface to iPhones, Android Phones, and tablets. The idea is to customize existing web and fat clients into a mobile format that can be consumed on mobile devices. The Messaging Cloud Service is a messaging protocol that allows for transactions in the cloud. If you are looking at connecting different cloud services together it allows you to serialize the communication between vendors for a true transactional experience. The Application Container Cloud is a lightweight Java container allowing you to upload and run java applications but without access to the operating system. This is a shared multi-tenant version of a WebLogic server. The Developer Cloud Service is a DevOps integration for the Java and Database services. This service is an aggregation of public domain components used to develop microservices at the database or java layer. The Application Builder Cloud Service is a cloud based REST api development interface allowing you to integrate with Application software in the Oracle Cloud as well as other Clouds. The API Catalog is a way of publishing the REST apis that you have and expose them to your customers.

The Content and Process Cloud Services are an aggregation of services that address group communications as well as business process flow. The Documents Cloud Service is a way of file sharing on the web. The Process Cloud Service is an extension that allows you to launch business processes (think Business Process Manager or BPM) in the cloud. The Sites Cloud Service is a web portal interface that takes documents and processes and aggregates them into a single cloud site allowing you to take a wiki like presentation but put business processes into the presentation. The Social Network Cloud Service allows you to integrate social network services like Facebook and Twitter into your web presence. It allows you to integrate these services as well as search these repositories for information relating to your company.

The Business Analytics part of Platform services provides data visualization and analytic tools as well as data aggregation utilities. The Business Intelligence component is the traditional BI package that allows users to create custom queries into your database. The Big Data Preparation allows you to aggregate data from a variety of sources into a Big Data repository. The Big Data Discovery allows you to look at your data in a variety of ways and generate reports based on your data and views of data. The Data Visualization Cloud Service allows you to view and analyze your data from different perspectives. This is similar to the BI and Big Data but looks at data slightly differently. The Internet of Things Cloud Service allows you to aggregate monitoring and measuring devices into a repository.

The Cloud Integration part of Platform services is the traditional data aggregation tools from other repositories. The Integration Cloud Service allows you to aggregate traditional SaaS vendors to unify fields like how a customer is defined or what data elements are incorporated into a purchase order. The SOA Cloud Service is implementation of the Oracle SOA Suite in the cloud. The GoldenGate Cloud Service is an implementation of the Oracle Golden Gate software that allows you to take data from different databases and synchronize the different repositories independent of the database vendor. The Internet of Things Cloud Service is the same listed in the Business Analytics section mentioned before.

The Cloud Management part of Platform services allows you to take the log files that you have inside your data center and analyze them for a variety of things. You can aggregate your log files into the Log Analytics Cloud Services to look for patterns, intrusion attempts, and problems or issues with services. The IT Analytics Cloud Service looks at log files and looks for trends like disks filling up, processors being used or not used appropriately. The Application Performance Cloud Service looks at log files to look at how systems and applications are operating rather than how systems are working rather than how components are working.

In Summary, we looked at an overview of the Platform as a Services offered by Oracle. Unfortunately, the variety of topics are too great for one blog. We did a high level overview of these services. In upcoming blogs we will dive deeper into each of these services and look at not only what they are but how they work and how to provision these services. We will also compare and contrast how these services compare to services offered by Amazon and Azure as we dive into each service.

Red Samurai ADF Performance Audit Tool Major Update v 5.0

Andrejus Baranovski - Tue, 2016-05-03 14:42
Our ADF performance audit tool is growing and getting more advanced with each release. Current major update v 5.0 brings ADF Click History integration and allows to track ADF UI client request time. This allows to understand, how long it takes to execute action from user perspective. Combined with ADF BC performance monitoring, we could give precise answer about performance bottlenecks from top to bottom.

New features in v 5.0:

1. Redesigned UI with ADF 12.2.1 Responsive template. UI is aligned with Alta UI best practices

2. Group by ECID functionality. We can track request action from top to bottom. This includes client request time (time needed to complete action on UI, including network traffic time). Executed SQL queries and any issues in ADF BC performance for the given request

3. ADF Click history logging. Click history data about UI performance is being intercepted and logged into Red Samurai DB, for analysis. This gives a database of all user requests and time for each request

4. User request statistics visualization dashboard

Here you can see sample data from ADF demo application. User request statistics are presented to help in understanding system performance. Time for each user request is visualized, along with detail information about request (component ID, name, type, etc.). Average times are presented, along with user statistics and top actions. We display total requests count vs. recent requests, to visualize the load on the system:


Each of the logged requests can be tracked down by ECID. Here we can see a list of VO's invoked during UI request and any slow performance behavior happening in ADF BC:


In the next updated v 6.0, we are going to implement UI behavior analysis, to extract most common UI usage patterns in ADF application.

Transparent Data Encryption for SQL Server in an Availability Group

Pythian Group - Tue, 2016-05-03 13:24

With the all new features in SQL Server 2016 always on, which you can read up on here, it’s easy to forget about Transparent Data Encryption (TDE). This blog post will focus on TDE.

TDE encrypts database files at rest. What this means is your .MDF and .NDF Files, and consequently your backups, will be encrypted, meaning you will not be able to detach the database files and restore them on another server unless that server has the same certificate that was used to encrypt the database.

In this blog post I am using SQL Server 2014 and will explain how to enable TDE on an existing AG Group database

  1. The first thing we need to check is if the server has a master encryption key on all replica in the AG Group
USE MASTER
GO
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

The Screenshot below shows I don’t have a key so I need to create one

No Master Encryption Key

  1. Create a Database Master Encryption Key on each of the replicas in the AG Group. It is important to use a complex password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd'
GO

  1. Run the code in step 1 and this time you should see the below

Master Encryption Key

  1. Now we need to create a certificate to use for the encryption of the database on the primary replica. This can be accomplished by using the below

CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'SQL Server 2014 AdventureWorks2012 Encryption Certificate';
GO

  1. Validate the Certificate

SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates

Validate Encryption Key

The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key.  A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed

  1. Next We need to backup the certificate on the Primary Replica

BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = ‘C:\BackupCertificates\BackupEncryptionCert.bak’
WITH PRIVATE KEY ( FILE = ‘C:\BackupCertificates\BackupEncryptionCertKey.bak’ ,
ENCRYPTION BY PASSWORD = ‘Certi%yC&mpl£xP@$$Wrd’)

Encryption Files

The BACKUP CERTIFICATE command will create two files.  The first file is the server certificate itself.  The second file is a “private key” file, protected by a password. Both files and the password will be used to restore the certificate onto other instances.

  1. The Files created in step 6 needs to be copied to each of the other replicas and created in SQL Server. After the files are copied the below command can be used to create the certificates

CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = ‘C:\BackupCertificates\BackupEncryptionCert.bak’
WITH PRIVATE KEY (FILE = ‘C:\BackupCertificates\BackupEncryptionCertKey.bak’,
DECRYPTION BY PASSWORD = ‘Certi%yC&mpl£xP@$$Wrd’);

  1. That’s all the configuration needed for each instance now we are ready to start encrypting the database. We now need to tell SQL Server which Encryption Type we want to use and which certificate to use. This can be done using the following code on the Primary Replica

Use Adventureworks2012
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE BackupEncryptionCert

  1. Finally, the last step is to enable TDE by executing the below command on the Primary Replica

ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON

 

And that’s it, I hope you enjoyed this tutorial and found it informative. If you have any questions, please comment below.

Categories: DBA Blogs

Compression -- 6 : Advanced Index Compression

Hemant K Chitale - Tue, 2016-05-03 09:23
Earlier, I had covered Index (Key) Compression which is included in the Enterprise Edition.

In Key Compression, the DBA must specify the Prefix (i.e. number of leading columns in a composite index) that must be used as the compression key.

12.1.0.2 Advanced Index Compression does not require the DBA to manually identify the prefix key length.  Advanced Index Compression auto(magically) identifies the optimal prefix columns.
(Note : Advanced Index Compression requires the Advanced Compression licence Option and is available only in 12.1.0.2 and higher)

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL>


Creating a Key Compression Index by specifying the Prefix size (the leading 2 columns) :

SQL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 2;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
5508

SQL>


Note how I specified "2" as the Prefix size as I want to compress on repeated values of (OWNER, OBJECT_NAME).

Using Advanced Index Compression by specifying "COMPRESS ADVANCED LOW" and letting Oracle decide on the compression strategy in each leaf block :

SQL> drop index target_data_ndx_1_comp;

Index dropped.

SQL> create index target_data_ndx_2_advcomp on
2 target_data (owner, object_type, object_name)
3 compress advanced low;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_ADVCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_2_ADVCOMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


Wow, that's significantly smaller.  What's more, I did not have to spend time analyzing the data and the index definition to identify the "correct" Prefix size.

However, it is now possible to specify the entire composite key as the Prefix, although that is not what I would have done in earlier versions.  Identifying the Prefix size requires analyzing the data.

SQL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 3
3 /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


So, the Advanced option allows me to let Oracle automatically decide the appropriate mechanism to compress the Index keys.

UPDATE : Also see the subsequent test with a reversal of the columns in the composite index.

Just for comparison, here is a regular index :

SQL> drop index target_data_ndx_2_advcomp;

Index dropped.

SQL> create index target_data_ndx_3_nocomp on
2 target_data (owner, object_type, object_name)
3 /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_NOCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_NOCOMP'
4 /

LEAF_BLOCKS
-----------
7289

SQL>


That is a much larger regular index !
.
.
.

Categories: DBA Blogs

Thanks Oracle, for making my life easier!

Jeff Moss - Tue, 2016-05-03 06:11

So, I’m really pleased to see that Oracle has acquired Opower. Why? Well, for the last few months I’ve occasionally had to do an extract for Opower….and another for Oracle on the same stuff…..hopefully I’ll only need to do the one extract from now on…yay!

Oracle’s New Data Visualization Desktop

Rittman Mead Consulting - Tue, 2016-05-03 05:00

A recent addition to the Oracle lineup of visualization tools is the Oracle Data Visualization Desktop. Described by Oracle as a “single user desktop application that provides Oracle Data Visualization functionality to business users,” DVD is an easy-to-install data visualization tool for Windows 7, 8 or 10 that packs some very powerful features.

I recently had a chance to sit down and explore DVD and wanted to share some of my first impressions.

At its core, DVD is a stand-alone version of Oracle’s DVCS. If you are at all familiar with Visual Analyzer, you will feel right at home.

Screen Shot 2016-04-29 at 9.34.25 AM

Installation was a breeze on my Windows 10 VM and only took about 5 minutes and required no additional software or plugins for the standard VA functionality.

After installation, launching DVD is as easy as clicking on the desktop icon like any other stand-alone application.

Screen Shot 2016-04-29 at 2.05.26 PM

Screen Shot 2016-04-29 at 10.02.43 AM

After the ODV startup, I was presented with a home screen which contains a search field for finding projects, a list of user folders and a main window to select individual visualizations that have been created.

Screen Shot 2016-05-02 at 11.15.59 AM

Clicking on the hamburger Screen Shot 2016-04-29 at 2.24.46 PM icon in the top left corner brings up a menu where I can choose to start Visual Analyzer with the last data source selected, select new Data Sources or create a new VA Project.

Screen Shot 2016-04-29 at 2.02.58 PM

I chose to create a new VA project and selected the sample data from Oracle (the sample data is an optional install chosen during the DVD install process). Creating a dashboard was a fairly straightforward process. Following Visual Analyzer’s functionality of dragging and dropping columns, I was able to put together a simple sales and profit dashboard in a few minutes.

Screen Shot 2016-04-29 at 2.39.44 PM

While creating my dashboard, I noticed that Oracle has included some new visualization types. You can now choose Scatter (Cat.), Stacked Scatter (Cat.), Donut or Sunburst visualizations.

Screen Shot 2016-04-29 at 1.50.12 PM

Screen Shot 2016-04-29 at 10.15.46 AM

One other feature that Oracle added to DVD is the ability to insert images onto the dashboards. You can choose to upload your own image or link to a URL to pull images from the web.

Screen Shot 2016-05-02 at 8.21.19 AM

Screen Shot 2016-05-02 at 8.25.25 AM

I uploaded an image and changed the canvas layout to freeform, which allowed me to move the image anywhere on the dashboard. By adjusting the transparency it is possible to have the image underlay the entire dashboard and still be able to see the visualizations. This example is pretty extreme, and in a real world scenario, caution should be used as to not obstruct the visualizations.

Screen Shot 2016-05-02 at 8.33.38 AM

Next I decided to try to connect to my Oracle 12c sample database to pull in some new data to work with. Selecting “Create New Datasource” from the menu prompted me with three options: create from a file, from an existing app or from a database.

Screen Shot 2016-04-29 at 11.24.23 AM

Clicking on the “From Database” option, I was presented with a connection screen.

Screen Shot 2016-05-01 at 1.15.42 PM

On this screen I discovered one of the most impressive things about DVD. Clicking on “Database Type” reveals a dropdown menu which you can choose from a variety of database formats, including Spark, Hive and Mongo DB, among others.
Screen Shot 2016-05-01 at 1.16.15 PM

That’s awesome.

Because I already had 12c DB installed, I selected the Oracle Database Type and entered all my connection information.

Once a connection to the database is made, it shows up in the available connections list. Clicking on my sample database brought up a list of available schemas to choose from. In this case, I chose the sample HR schema which then brings up a list of tables available to add as data sources for visualizations.

Screen Shot 2016-04-29 at 3.11.22 PM

Screen Shot 2016-04-29 at 3.04.32 PM

I chose to add EMPLOYEES, JOBS and LOCATIONS and then started a new VA project. The HR tables now show up in the list of available data sources.

Screen Shot 2016-04-29 at 3.17.22 PM

I selected EMPLOYEES and JOBS and, within seconds, was able to create a simple table showing a list of employee names, their job titles, salaries and commission percentages.

Screen Shot 2016-04-29 at 11.35.09 AM

As you can see, adding new data sources is quick and easy and allows users to explore their data and create meaningful visualizations from that data in a very short amount of time.

Another feature is the Advanced Analytics portion of Oracle Data Visualization Desktop. This feature, which uses R, gives users the ability to do things like highlight outliers or show trend lines with a click of a button.

Screen Shot 2016-04-29 at 3.43.43 PM

Screen Shot 2016-04-29 at 3.48.50 PM

This feature does require an optional install located within the DVD application folder. The install process proved once again to be very quick and easy and completed in about 5 minutes.

Screen Shot 2016-04-29 at 10.02.23 AM

Screen Shot 2016-04-29 at 9.58.38 AM

After the installation was complete, I created a new VA project. Choosing the sample data provided by Oracle for DVD, I created a quick scatter chart and then, by right clicking anywhere on the visualization, clicked “Add Outliers.”

Screen Shot 2016-05-02 at 9.29.19 AM

Screen Shot 2016-05-02 at 9.29.39 AM

As you can see, outliers and non-outliers are easily distinguishable by the color key that DVD assigned automatically.

Next, I wanted to see how if I could change some of the colors in my visualization. DVD allows you to do this under the visualization menu.

Screen Shot 2016-05-02 at 9.35.17 AM

Screen Shot 2016-05-02 at 9.38.43 AM

Screen Shot 2016-05-02 at 9.39.33 AM

As with OBIEE, entering specific hex values is supported as well as selecting from pre-made color pallets is possible with DVD.

Using the same right-click functionality that I used for adding outliers, I was able to additionally add a polynomial trend line to show a gains and losses.

Screen Shot 2016-05-02 at 9.43.58 AM

Next, I decided to see if I could export this data and import it into Excel. Choosing export from the visualization menu, I was able to easily export the data as a .CSV and upload it into Excel.

Screen Shot 2016-05-02 at 9.54.35 AM

Overall, Oracle Data Visualization Desktop is a very impressive new addition to the to the DVCS lineup. The ability to collect data from multiple sources, its native adaptors for a variety of popular databases, and the ability to manipulate visualizations to convey the data in creative ways make it a strong contender against Tableau and Wave. It requires no remote server infrastructure and is a solid business solution for users Oracle Data Visualization functionality in a small and easily accessible package.

I feel as though I have just cracked the surface of everything this tool can do. Check back for future blogs and articles as we at Rittman Mead continue to explore the possibilities of DVD. The future of data visualization may be closer than we think.

If you would like more information about Visual Analyzer or the Oracle Cloud Service, see this blog post by Mark Rittman.

If you would like to watch the official Tech Demo of DVD, you can find it here.

Rittman Mead also offers in depth professional training courses for OBIEE 12c and Visual Analyzer.

The post Oracle’s New Data Visualization Desktop appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Debugging

Jonathan Lewis - Tue, 2016-05-03 02:11

The OTN database forum supplied a little puzzle a few days ago – starting with the old, old, question: “Why is the plan with the higher cost taking less time to run?”

The standard (usually correct) answer to this question is that the optimizer doesn’t know all it needs to know to predict what’s going to happen, and even if it had perfect information about your data the model used isn’t perfect anyway. This was the correct answer in this case, but with a little twist in the tail that made it a little more entertaining. Here’s the query, with the two execution plans and the execution statistics from autotrace:


SELECT  /* INDEX(D XPKCLIENT_ACCOUNT) */ 
        E.ECID,A.acct_nb
FROM    
        client_account d, 
        client         e, 
        account        a
where
        A.acct_nb ='00000000000000722616216'</li>


AND     D.CLNT_ID = E.CLNT_ID
AND     D.ACCT_ID=A.ACCT_ID;

Plan (A) with a full tablescan of client_account – cost 808, runtime 1.38 seconds, buffer gets 17,955


-------------------------------------------------------------------------------------------------
| Id | Operation                      | Name           | Rows  | Bytes  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                |     1 |    59  |   808 (14) | 00:00:10 |
|  1 |  NESTED LOOPS                  |                |     1 |    59  |   808 (14) | 00:00:10 |
|  2 |   NESTED LOOPS                 |                |     1 |    59  |   808 (14) | 00:00:10 |
|* 3 |    HASH JOIN                   |                |     1 |    42  |   806 (14) | 00:00:10 |
|  4 |     TABLE ACCESS BY INDEX ROWID| ACCOUNT        |     1 |    30  |     5  (0) | 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | XAK1ACCOUNT    |     1 |        |     4  (0) | 00:00:01 |
|  6 |     TABLE ACCESS FULL          | CLIENT_ACCOUNT |  9479K|   108M |   763 (10) | 00:00:09 |
|* 7 |    INDEX UNIQUE SCAN           | XPKCLIENT      |     1 |        |     1  (0) | 00:00:01 |
|  8 |   TABLE ACCESS BY INDEX ROWID  | CLIENT         |     1 |    17  |     2  (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
 17955  consistent gets
     0  physical reads
     0  redo size
   623  bytes sent via SQL*Net to client
   524  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Plan (B) with an index fast full scan on a client_account index – cost 1,190, runtime 0.86 seconds, buffer gets 28696


----------------------------------------------------------------------------------------------------
| Id | Operation                      | Name              | Rows  | Bytes  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                   |     1 |    59  |  1190  (8) | 00:00:14 |
|  1 |  NESTED LOOPS                  |                   |     1 |    59  |  1190  (8) | 00:00:14 |
|  2 |   NESTED LOOPS                 |                   |     1 |    59  |  1190  (8) | 00:00:14 |
|* 3 |    HASH JOIN                   |                   |     1 |    42  |  1188  (8) | 00:00:14 |
|  4 |     TABLE ACCESS BY INDEX ROWID| ACCOUNT           |     1 |    30  |     5  (0) | 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | XAK1ACCOUNT       |     1 |        |     4  (0) | 00:00:01 |
|  6 |     INDEX FAST FULL SCAN       | XPKCLIENT_ACCOUNT | 9479K |   108M |  1145  (5) | 00:00:13 |
|* 7 |    INDEX UNIQUE SCAN           | XPKCLIENT         |     1 |        |     1  (0) | 00:00:01 |
|  8 |   TABLE ACCESS BY INDEX ROWID  | CLIENT            |     1 |    17  |     2  (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
     0  recursive calls
     0  db block gets
 28696  consistent gets
     0  physical reads
     0  redo size
   623  bytes sent via SQL*Net to client
   524  bytes received via SQL*Net from client
     2  SQL*Net roundtrips to/from client
     0  sorts (memory)
     0  sorts (disk)
     1  rows processed

Note, particularly, that the two plans are the same apart from operation 6 where a full tablescan changes to an index fast full scan, predicting the same number of rows but with an increase of 50% in the cost; the increase in cost is matched by an increase in the reported workload – a 60% increase in the number of consistent reads and no disk reads or recursive SQL in either case. Yet the execution time (on multiple repeated executions) dropped by nearly 40%.

So what’s interesting and informative about the plan ?

The cost of a tablescan or an index fast full scan is easy to calculate; broadly speaking it’s “size of object” / “multiblock read count” * k, where k is some constant relating to the hardware capability. The costs in these plans and the autotrace statistics seem to be telling us that the index is bigger than the table, while the actual run times seem to be telling us that the index has to be smaller than the table.

It’s easy for an index to be bigger than its underlying table, of course; for example, if this table consisted of nothing but two short columns the index could easily be bigger (even after a rebuild) because it would be two short columns plus a rowid. If that were the case here, though, we would expect the time to fast full scan the index to be higher than the time to scan the table.

So two thoughts crossed my mind as I looked at operation 6:

  • Mixing block sizes in a database really messes up the optimizer costing, particularly for tablescans and index fast full scans. Maybe the table had been built in a tablespace using 32KB  blocks while the index had been built in a tablespace using the more common 8KB blocksize – I didn’t want to start working out the arithmetic but that might be just enough to produce the contradiction.
  • Maybe the table was both bigger AND smaller than the index – bigger because it held more data, smaller because it had been compressed. If so then the difference in run-time would be the overhead of decompressing the rows before projecting and comparing the data.

Conveniently the OP has included an extract from the 10053 trace:


Table Stats::
  Table: CLIENT_ACCOUNT  Alias:  D
    #Rows: 9479811  #Blks:  18110  AvgRowLen:  71.00  ChainCnt:  0.00
  Column (#1): CLNT_ID(
    AvgLen: 6 NDV: 1261035 Nulls: 0 Density: 0.000001 Min: 0 Max: 4244786
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 239
  Column (#2): ACCT_ID(
    AvgLen: 6 NDV: 9479811 Nulls: 0 Density: 0.000000 Min: 1 Max: 22028568
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255

Index Stats::
  Index: XPKCLIENT_ACCOUNT  Col#: 1 2
    LVLS: 2  #LB: 28543  #DK: 9479811  LB/K: 1.00  DB/K: 1.00  CLUF: 1809449.00

Note that the index is called xpclient_account – which suggests “primary key” –  and the number of distinct keys in the index (#DK) matches the number of rows in the table(#Rows). The index and table stats seem to be consistent so we’re not looking at a problem of bad statistics.

Now to do some simple (ballpark) arithmetic: for the table can we check if  “rows * average row length / 8K =  blocks”. We can read the numbers directly from the trace file:  9,500,000 * 71 / 8,000 = 84,000.  It’s wrong by a factor of about 4 (so maybe it’s a 32K block, and maybe I could rule out that possibility by including more detail in the arithmetic – like allowing properly for the block header, row overheads, pctfree etc).

For the index – we believe it’s the primary key, so we know the number of rows in the index – it’s the same as the number of distinct keys. As for the length of an index entry, we have the index definition (col#: 1 2) and we happen to have the column stats about those columns so we know their average length. Allowing for the rowid and length bytes we can say that the average index entry is (6 +1) + (6 + 1) + 6 = 20 bytes.  So the number of leaf blocks should be roughy 9,500,000 * 20 / 8,000 = 23,750. That’s close enough given the reported 28,543 and the fact that I haven’t bothered to worry about row overheads, block overheads and pctfree.

The aritmetic provides an obvious guess – which turned out to be correct: the table is compressed, the index isn’t. The optimizer hasn’t allowed for the CPU cost of decompressing the compressed rows, so the time required to decompress 9.5M rows doesn’t appear in the execution plan.

Footnote.

Looking at the column stats, it looks like there are roughly 8 acct_ids for each clnt_id, so it would probably be sensible to compress the primary key index (clnt_id, acct_id) on the first column as this would probably reduce the size of the index by about 20%.

Better still – the client_account table has very short rows – it looks like a typical intersection table with a little extra data carried. Perhaps this is a table that should be an index-organized table with no overflow. It looks like there should also be an index (acct_id, clnt_id) on this table to optimse the path from account to client and this would become a secondary index – interestingly being one of those rare cases where the secondary index on an IOT might actually be a tiny bit smaller than the equivalent index on a heap table because (in recent versions of Oracle) primary key columns that are included in the secondary key are not repeated in the index structure. (It’s a little strange that this index doesn’t seem to exist already – you might have expected it to be there given the OP’s query, and given that it’s an “obvious” requirement as an index to protect the foreign key.)

The only argument against the IOT strategy is that the table clearly compresses very well as a heap table, so a compressed heap table plus two B-tree indexes might be more cost-effective than an IOT with a single secondary index.

 


Links for 2016-05-02 [del.icio.us]

Categories: DBA Blogs

SQL vs. Excel – Subgroup medians

RDBMS Insight - Mon, 2016-05-02 19:34

Recently I ran across this post on how to do subgroup medians in Excel 2010. First you need to create a pivot table, then “do some copying and pasting and use a formula to make it happen”. In SQL you can do this with one command.

Suppose that you have the same table as the Excel article, something like this:

CREATE TABLE sampletab
(arrest_day_of_week varchar2(10), 
arrest_ts TIMESTAMP, 
fingerprint_ts TIMESTAMP, 
days_between NUMBER);

and you want to get the median value of days_between for each day of the week.

The steps in Excel apparently go like this:

  1. Create pivot table to get the means
  2. Copy and paste the column values from the pivot table
  3. For Sunday, create an IF formula to include a cell’s days_between in the median calculation only if the arrest_day_of_week for that row is Sunday
  4. Repeat for other six days

Ouch!

In SQL, it’s one simple statement:

SELECT arrest_day_of_week, median(days_between) AS median_days_between FROM sampletab
GROUP BY arrest_day_of_week;

Conclusion – if you’re into data analysis, SQL can be a big time-saver!

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator