Search

OakieTags

Who's online

There are currently 0 users and 25 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Digital Learning – LVC: It’s the attitude, stupid!

The single most important factor for successful digital learning is the attitude both of the instructor as well as of the attendees towards the course format. Delivery of countless Live Virtual Classes (LVCs) for Oracle University made me realize that. There are technical prerequisites of course: A reliable and fast network connection and the usage of a good headset is mandatory, else the participant is doomed from the start. Other prerequisites are the same as for traditional courses: Good course material, working lab environment for hands on practices and last not least knowledgeable instructors. For that part notice that we have the very same courseware, lab environments and instructors like for our classroom courses at Oracle University education centers also for LVCs. The major difference is in your head :-)

Delivering my first couple of LVCs, I felt quite uncomfortable with that new format. Accordingly, my performance was not as good as usual. Meanwhile, I consider the LVC format as totally adequate for my courses and that attitude enables me to deliver them with the same performance as my classroom courses. Actually, they are even better to some degree: I always struggle producing clean sketches with readable handwriting on the whiteboard. Now look at this MS paint sketch from one of my Data Guard LVCs:

Data Guard Real-Time Apply

Data Guard Real-Time Apply

Attendees get all my sketches per email if they like afterwards.

In short: Because I’m happy delivering through LVC today, I’m now able to do it with high quality. The attitude defines the outcome.

Did you ever have a teacher in school that you just disliked for some reason? It was hard to learn anything from that teacher, right? Even if that person was competent.

So this is also true on the side of the attendee: The attitude defines the outcome. If you take an LVC thinking “This cannot work!”, chances are that you are right just because of your mindset. When you attend an LVC with an open mind – even after some initial trouble because you need to familiarize yourself with the learning platform and the way things are presented there – it is much more likely that you will benefit from it. You may even like it better than classroom courses because you can attend from home without the time and expenses it takes to travel :-)

Some common objections against LVC I have heard from customers and my usual responses:

An LVC doesn’t deliver the same amount of interaction like a classroom course!

That is not necessarily so: You are in a small group (mostly less than 10) that is constantly in an audio conference. Unmute yourself and say anything you like, just like in a classroom. Additionally, you have a chatbox available. This is sometimes extremely helpful, especially with non-native speakers in the class :-) You can easily exchange email addresses using the chatbox as well and stay in touch even after the LVC.

I have no appropriate working place to attend an LVC!

You have no appropriate working place at all, then, for something that requires a certain amount of concentration. Talk to your manager about it – maybe there is something like a quiet room available during the LVC.

I cannot keep up the attention when starring the whole day on the computer screen!

Of course not, that is why we have breaks and practices in between the lessons.

Finally, I would love to hear about your thoughts and experiences with online courses! What is your attitude towards Digital Learning?

Tagged: Digital Learning, LVC

Managing your Database in a Zombie Apocalypse

Zombie Server Room

Only two things are really certain: network latency over long distances, and the fact that humanity will soon rapidly degenerate into undead brain-eaters.

When that day comes, when the dead are crowding at your door and the windows are busted out and ripped up rotted arms are clawing at the inside of your home, I know what you’ll be thinking: is my database protected?

Don’t worry, my friends. The Oracle Alchemist has you covered. We just need to zombie-proof your DR plan. Let’s get started.

Getting the Power Back

Hopefully you did the smart thing and figured out how much battery and generator power you’d need to survive multiple years of failing power systems due to zombies. I know I did.

However, if you didn’t get this critical task done you may still have some options. Statistics show that the demand for U.S. gasoline was 8.73 million barrels in 2012. That comes out to 23,917.80821917808219 barrels per day of fuel that’s out there just waiting for you to snatch it up. The problem is going to be getting it. You’ll need to load yourself down with lots of weaponry and strike out in a fuel truck a few times a week, which will definitely take away from your database administration time. It’s a smart idea to enable a lot of automation and monitoring to take care of things while you’re out.

Zombie Gas StationYou’re going to need to fight other groups of surviving IT for fuel. This means you’re going to need friends. The way I see it, you have two choices: SysAdmins and Developers. They’re the two groups you work closest with as a DBA, so they’re the most likely to have your back when the dead walk. Start your planning now. If you want to get the developers on your side, tune some queries for them. Seriously, nothing will convince a developer to slice through the brain base of a walker like adding some key indexes when a query goes south during testing. However, if you think the SysAdmins are more likely to fight off rival gangs of resource hogs on the prowl for food and fuel, you can make them feel good by keeping all your filesystems cleaned up and RAM usage at a minimum.

The Problem with Zombies

Remember, the walking dead are tenacious. You remember before the apocalypse when a bunch of reporting users would all log into the database and run huge ad hoc queries against PROD without thinking about what they were doing? That was nothing. Zombies are the real deal. They will tear through a database faster than a multi-terabyte cartesian product. You can deploy outside the box now increase your chances of having a clone out there somewhere, just in case. If you want that database to survive, you’re going to need standbys. Lots of them.

I’d recommend a hub and spoke configuration. One central production database, at least 5 standby databases. As everybody knows, the chances of a zombie bringing down a database are roughly 89.375%. With 5 standby environments, you can drastically reduce the odds of being left without a standby system. On the plus side, zombies are completely brainless. What this means is that you don’t have to worry about masking or obfuscating your backup data in any way. Even on the off chance one of them kicks off a query (even zombies can figure out SQL Developer), they won’t be able to comprehend your users’ personal data, and with the complete downfall of the dollar it won’t matter if they see any credit information. So rest easy.

When All Else Fails

At some point, the zombies are going to come for you. Sorry, but it’s a statistical fact and there’s not much we can do about that. At that moment, when all hope is lost, you’re really going to need to protect your database because once you become a zombie too there really won’t be anyone left and you won’t be focused on maintaining it anymore; you’ll be focused on acquiring copious amounts of human flesh.

Zombie Server RoomSo make your last stand count. You’re a soon-to-be-undead DBA, act like it! Remember how we tune. Eliminate the wait, punch through the bottlenecks, make efficient use of processing power. Don’t get trapped between a rack and a hard place. If you have to play a game of circle-the-Exadata in order to get away, go for it, but don’t let them corner you. And whatever you do, make sure you keep your badge with you. The last thing you need is to hit a door you can’t get through without the proper credentials. Above all else: remember to kick off a backup before they finally take you. I’d recommend having the script ready and running on the console just in case you have to hit a quick key.

Good luck. You’re going to need it.

The post Managing your Database in a Zombie Apocalypse appeared first on Oracle Alchemist.

VirtualBox 4.3.18

VirtualBox 4.3.18 has been released. The downloads and changelog are in the usual places.

It’s a maintenance release, so mostly bug fixes and the odd little addition.

Happy upgrading… :)

Cheers

Tim…


VirtualBox 4.3.18 was first posted on October 12, 2014 at 2:00 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Changing my focus? (Update)

The day before I left for OpenWorld 2014 I wrote a post called “Chaning my focus?” where talked about the possibility of dropping out of the forum scene and focusing more on writing. It’s now nearly 3 weeks later, so I thought I would follow it up to let people know what is going on…

Pretty soon after I left for OOW14 I locked the forums on my site. I had a touch of guilt, but also felt a massive sense of relief. At that point I was working on the basis I would leave them locked for OOW, then reassess when I got back.

While I was at OOW a lot of people came and spoke to me about that specific post and it seemed to be universally met with positive feedback. In some cases with messages like, “It’s about bloody time!” Everyone seemed to reflect my own opinion that the main value I can add to the community is to keep writing articles, rather than act like a “Let me Google that for you” service. Thank you to everyone for your support, especially those of you who gave me this advice several years ago! You  know who you are. All I can say is you are much wiser than me. It takes a while to get into the right mindset to you can see the bloody obvious. :)

So what have I decided?

  • The forums are still locked and will remain so indefinitely. They are still available to view via the “Misc” tab, but I have removed the tab to them from the main website.
  • Blog comments are locked after 30 days. This alone has *massively* reduced the amount of spam I have to deal with.
  • I’m going to be very hard-nosed about dealing with people asking for help via other methods, like email and social media. The delete key is going to be used very extensively!

One of the things that really swung the balance was a comment made by someone who said, “The Oracle community is very selfish”. I won’t attribute that comment for fear of starting a flame war. :) That is not to say the people contributing to the community are selfish. Far from it. But it does seem a section of the community is incredibly demanding, yet give nothing in return. Very soon after this comment was made, I received an email asking me what was wrong with the forum. The person in question hadn’t bothered to read the big red text on the page saying the forum was locked and hadn’t bothered to click the “read more” link that explained why. That kind of did it for me. I can’t deal with these zero-effort people any more…

I’m sorry if you are annoyed by this, but that’s the way it is going to be…

On the positive side, the time I’ve freed up has been put to very good use. As well as the new articles that are appearing on the front page of the website, I’ve written a couple of backfill articles on old features that somehow I’ve never written about before. I’ve also started to update the Oracle 12c installation articles so they are in line with 12.1.0.2. The OL5OL6 and OL6+RAC installation articles are complete. In all cases, there are hardly any changes, but the paths and images have been brought in line with the new version so people don’t get confused. The others installation articles are on the to-do list. :)

I’m also planning to start some beginner-style articles, like the one I did for PL/SQL. Quite light and fluffy, even by my standards. I guess this is really to appease my guilty conscience. :) There are a number of areas I can think of that need this sort of introductory stuff before people can graduate on to doing the cool stuff…

So there it is. Onwards and upwards!

Cheers

Tim…


Changing my focus? (Update) was first posted on October 12, 2014 at 11:50 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Delphix and Oracle 12c partnership

More and more people have been asking me how Delphix compares with Oracle EM 12c Cloud Control with Snap Clone.

Delphix compliments Oracle. Oracle and Delphix are partners.  Delphix is an Oracle Gold ISV partner and we collaborated at Oracle Open World on hosting hands on Delphix labs.

Delphix adds value to Oracle by enabling Oracle customers to more efficiently and easily make copies of there databases. Databases are typically copied from a production system to development, QA, backups, reporting, sandboxes, UAT, forensics etc. Across these copies 90-99% of the data blocks are duplicate. Instead of making duplicate copies, Delphix enables the duplicate blocks to be shared across all copies.  Blocks are shared across the copies which are from the same point in time but more importantly blocks are shared even by copies coming for any point in time thanks to a technology called time flow that collects and tracks datablock changes from a source database. No other technology  in the industry has time flow technology. Time flow, data block version management, source database syncing and provisioning of thin clones, aka virtual databases is handled seamlessly and automatically by Delphix and interacting with Delphix is as simple as clicking some buttons in a beautiful UI.

One of the most complementary value adds is enabling the cloning of PDBs in the same container or remote container in minutes for almost no storage no matter the size. The size of PDBs can be a impediment to making copies of PDBs but with Delphix all duplicate blocks are shared on disk and in storage cache thanks to the Delphix technology. The shared block storage and the shared blocks in page caching complements the SGA and processes sharing that PDB technology enables reducing memory usage and allow more databases per given set of hardware.

#222222;">

Oracle EM 12c Snap Clone is a nice feature that allows Oracle customers to leverage existing Netapp or ZFS storage hardware more efficiently but Delphix is software that installs on commodity hardware, allows customers to sync with source data be it Oracle, other databases, application stacks, software distributions, track changes in those sources and provision our space efficient copies in minutes for almost no storage usage to target machines through out customers IT centers or even into the cloud including Amazon AWS.

The bottom line is that most of the functionality that Delphix brings is not available with any other technology in the industry such as

  • Data Protection - 30 days of backups and recovery down to the second stored in the same storage size as the original database and accessible in minutes. Fine grain RPO and fast RTO. (see graphic at bottom of post)
  • Live Archive - archiving and providing point in time versions of a database in minutes for a fraction of the storage otherwise required. Important for supporting audits like SOX, CCAR, Dodd Frank etc
  • Business Intelligence - 24×7 ETL windows & fast refresh of new data in minutes.
  • Synchronized Cloning - cloning multiple related databases at the same exact point in time.
  • Branching - clones can be cloned themselves in minutes, thus a development team can clone their database and give a copy in minutes to QA
  • EBS cloning - push button cloning of the whole EBS stack, binaries, application stack and database
  • Clone data protection - clones by default have data protection and if a developer messes up his copy, a new copy can be made in minutes just before the bad operation happened
  • Cross platform thin cloning - thin cloning of Unix sources to Linux VDBs
  • Data Center Migration - Delphix supports active replication between Delphix appliances making cloud migration simple, efficient and continually synced between in house and cloud.
  • Super caching – sharing of duplicate data blocks not only on disk but also in cache on Delphix which looks and acts just like a NAS, aka an NFS file server, from the point of view of the the virtual databases

Not to mention that Delphix supports full automated source syncing and virtual database provisioning other databases beside Oracle such as SQL Server, Sybase, Postgres. Other databases such as DB2 can be virtualized as well with some additional manual steps.

Delphix is software that installs anywhere where as Oracle EM 12c Snap Clone is a  feature that is a simple and nice enhancement for the usage of specialized existing hardware, either Netapp or ZFS storage to make static thin clones at one point in time.

Features such as in-memory option are supported by Delphix.  In-memory is transparent to Delphix whether the virtual database uses in-memory. In-memory is a memory feature and Delphix is only at the storage level.

On the other hand pluggable database support changes a lot of things.  Cloning a single PDB in a CDB involves some complexities and Delphix fully supports PDB cloning. Here is a video I made demonstrating this

Those are all black and white features that Delphix supports but just as a demonstration of this power the following video shows linking to a RAC database and provisioning a RAC clone in minutes:

#1155cc;" href="http://www.oraclerealworld.com/cloning-oracle-rac-in-5-minutes/" target="_blank">http://www.oraclerealworld.com/cloning-oracle-rac-in-5-minutes/

 

Another example, mentioned in point one above is the power of Delphix as  backup & data protection. The graph below shows the classic Oracle backup routine of a full backup on the weekends and increment backups. The chart below represents at 9TB database with 1TB change rate per day (a huge change rate). At such a change rate, an Oracle backup would be 9TB on the weekend and a daily backup of 1TB of changes. Recovery of such a database on a Friday for example would be  entail copying the full 9TB database to the recovery location, then replaying 5 incremental backups each of 1TB on top of the full backup (starting with a full Sunday backup and applying incremental from Mon,Tues,Wed, Thurs, Fri). Such a restore would be a massive operation.

With Delphix, the first link to the source would only be 3TB thanks to compression. Each incremental backup would only be 1/3 TB. Incremental backups are immediately applied and available. Recovering from an incremental on any day would only take minutes with no data movement. A full backup is NEVER taken again. Only incrementals are taken. Thanks to this time flow technology and compression, one can store almost 4 weeks of backups in the size of the original database! and one can access those database version in minutes at the push of a button with no data movement. Recovering is just a matter of pushing a button and Delphix handles mounting the data at that point in time over NFS to a target machine and Delphix starts up and finishes the recovery of that database point in time.

Screen Shot 2014-10-09 at 4.31.12 PM

Girls with Heads Together Hugging

Another Great OpenWorld

Steve at the Delphix Booth

Last week I attended Oracle OpenWorld 2014, and it was an outstanding event filled with great people, awesome sessions, and a few outstanding notable experiences.

Personally I thought the messaging behind the conference itself wasn’t as amazing and upbeat as OpenWorld 2013, but that’s almost to be expected. Last year there was a ton of buzz around the introduction of Oracle 12c, Big Data was a buzzword that people were totally excited and not too horribly burnt out on, and there was barely a cloud in the sky. This year cloud it was cloud all about cloud the Cloud cloud (Spoiler alert: it was the Cloud all along) which just didn’t have that same excitement factor.

But it’s still OpenWorld, set in the heart of San Francisco with tens of thousands of buzzing Oracle faithful. And therefore it was still a pretty awesome time.

Jonathan Lewis at the Delphix BoothThis year I went representing Delphix, and man did we represent. The enthusiasm and technical curiosity were evident as our booth filled up for three days straight with folks eager to hear the good news of the data virtualization. I have to say, the DBA in me finds the promise of syncing databases to a software platform that can provision full-size, read/write clones in a couple minutes with no additional disk usage quite alluring. But there was more to the message than the technology behind the platform; there were also a plethora of use cases that captured people’s attention. Faster and more on-time business intelligence and analytics, application and database testing, regulatory compliance, and more. If that wasn’t enough, we also had Jonathan Lewis, Tim Gorman, Kyle Hailey, Ben Prusinski, and yours truly speaking at the booth which was a great bit of fun and drew a lot of folks that wanted to learn more.

On Monday I was honored to be invited back on SiliconAngle’s conference web show theCUBE to talk about copy data, Delphix, the Cloud (that should be fun for people running Cloud to Butt), Oracle’s strategy, and more. They had not one but two booths at OpenWorld this year. The always charismatic and ever savvy Dave Vellante and I had an outstanding chat, which you can see right here!

Another fantastic part of the conference was OakTable World, which is technically not part of OpenWorld…rather, it is a “secret” conference-within-a-conference. Held at the Children’s Museum nestled in the bosom of the Moscone Center (yay visuals), this conference features a lineup of incredibly technical folks talking about incredibly technical things to the wonder and amazement of all. This year was no different, with a great assortment of no-nonsense presentations. On the 2nd day of OakTable World there was also something I liked to call the Attack of the Attacks: #CloneAttack, #RepAttack, and #MonitorAttack. This event featured Delphix, DBVisit, and SolarWinds Confio and allowed people to get the software installed on their own laptops for tinkering, learning, and testing.

Pythian put on a couple exciting events as always, with the Friends of Pythian party on Monday night and the OTN Blogger Meetup on Wednesday. Both events were a blast as always, with a huge assortment of members of the Oracle community and beyond. Honestly, it’s worth going just for the good food and to see Alex Gorbachev stand up on a booth bench and try to hush a crowd of buzzing datafiends.

All in all it was an outstanding OpenWorld and it was great catching up with some amazing and brilliant people. I can’t wait to see you all again next year!

The post Another Great OpenWorld appeared first on Oracle Alchemist.

Little things worth knowing-troubleshooting parallel statement queueing

This is yet another one of these posts that hopefully help you as much as they are going to help me in the future. Recently I enjoyed troubleshooting a problem related to parallel execution. Since I have never really written down how to tackle such a problem I thought it might be nice to do that now.

This is 12.1.0.2.0 on Exadata, but the platform doesn’t really matter for the troubleshooting technique.

What is parallel statement queueing

Statement queueing is a pretty nifty tool new with 11.2. Instead of downgrading the requested Degree of Parallelism (DOP) as it was the case with parallel_adaptive_multi_user your session will queue until the requested number of parallel servers becomes available. The rationale behind this feature is that you might end up queueing for 1 minute until all the requested PX servers become available, but once they are available your session can finish in 2 minutes with a DOP of 16 rather than 8 minutes with a (old behaviour) downgraded DOP of 2 for example. The 1 minute was well spent waiting. If you want to fine tune statement queueing, database resource manager allows you to.

The downside is that you don’t get statement queueing without enabling automatic DOP unless you play with underscore parameters. Automatic DOP is quite often the reason why you see statement queueing in the first place.

How does this statement queueing work?

With parallel_degree_policy set to AUTO (warning again-this can be dangerous!) the optimiser estimates the time it takes to execute a SQL statement during the hard parse. If the estimated execution time exceeds parallel_min_time_threshold (default is AUTO which is 10 seconds) then the optimiser will try to work out the “optimal” DOP which is calculated as min(parallel_degree_limit,  calculated ideal DOP). The default parallel_degree_limit is set to CPU, which is documented as follows: “The maximum degree of parallelism is limited by the number of CPUs in the system”. Ooops-that can be a lot. It might be better to set the parallel_degree_limit to an integer, signifying the maximum allowed DOP for a statement executing in parallel. On Exadata you don’t normally need very high degrees of parallelism, so a more conservative settings might be in order.

Queueing

When does the queueing mechanism kick in? Let’s imagine that you have parallel_max_servers = parallel_servers_target = 48 defined in your system, in my case an Exadata X2 with 2s12c24t Westmere EP processors. This isn’t the default by the way. This setting allows you to execute queries 48-way parallel per instance. Let’s assume that 40 query slaves are in use already, and another user wants to execute a query 12-way parallel. The sum of currently executing parallel slaves + the requested number of slaves exceeds parallel_servers_target, triggering statement queueing for your session.

Wolfgang it!

To create a reproducible test case I set parallel_max_servers to 48 and parallel_servers_target to 24. I also enabled automatic DOP in my session. My test queries are directed at a 256 million rows table of approximately 80GB in size, non partitioned. Before executing the first SQL statement I enabled event 10053 to Wolfgang my query.

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

Session altered.

Elapsed: 00:00:00.02
SQL> desc bigtab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 V1                                                 VARCHAR2(40)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N_256K                                             NUMBER
 N_128K                                             NUMBER
 N_8K                                               NUMBER
 PADDING                                            VARCHAR2(960)

SQL> select 1 + sum(n1) + sum (n2) - sum(n_8k) from bigtab where id > 1;

1+SUM(N1)+SUM(N2)-SUM(N_8K)
---------------------------
                 1.0471E+13

Elapsed: 00:00:35.59

The 10053 trace should give me some idea about the DOP. Let’s have a look at it:

     36 **************************
     37 Automatic degree of parallelism (AUTODOP)
     38 **************************
     39 Automatic degree of parallelism is enabled for this statement in auto mode.
     40 kkopqSetForceParallelProperties: Hint:no
     41 Query: compute:yes forced:no forceDop:0
     42 Calibration statistics is enabled.
     43 Start with a serial pass, cost the DOP to use

   1935 AUTO DOP PLANS EVALUATION
   1936 ***************************************
   1937 Compilation completed with Dop: 1.
   1938   Cost_io:  2776823.000000  Cost_cpu: 154935265907
   1939   Card:     1.000000  Bytes:    19.000000
   1940   Cost:     2781428.613003  Est_time:  401168ms
   1941 kkopqCombineDop: Dop:40 Hint:no
   1942 Query: compute:yes forced:no  scanDop:40 cpuDop:1 forceDop:0
   1943 Serial plan is expensive enough to be a candidate for parallelism (2781429)
   1944 AutoDOP: kkopqAdjustDop: dop is 40 after affinity normalization
   1945 Signal reparse with DOP 40.
   1946 *****************************
   1947 Number of Compilations tried: 1
   1948 *****************************

   3853 AUTO DOP PLANS EVALUATION
   3854 ***************************************
   3855 Compilation completed with Dop: 40.
   3856   Cost_io:  77133.972222  Cost_cpu: 2387701756
   3857   Card:     1.000000  Bytes:    19.000000
   3858   Cost:     77204.949157  Est_time:  11135ms
   3859 kkopqCombineDop: Dop:80 Hint:no
   3860 Query: compute:yes forced:no  scanDop:40 cpuDop:80 forceDop:0
   3861 AutoDOP: kkopqAdjustDop: computed dop before applying degree limit was 80 and now it is 48
   3862 AutoDOP: kkopqAdjustDop: dop is 48 after affinity normalization
   3863 Adopt new plan with dop=40
   3864 Signal reparse with DOP 48.
   3865 *****************************
   3866 Number of Compilations tried: 2
   3867 *****************************

   5772 AUTO DOP PLANS EVALUATION
   5773 ***************************************
   5774 Compilation completed with Dop: 48.
   5775   Cost_io:  64278.310185  Cost_cpu: 1989751463
   5776   Card:     1.000000  Bytes:    19.000000
   5777   Cost:     64337.457631  Est_time:  9279ms
   5778 kkopqCombineDop: Dop:80 Hint:no
   5779 Query: compute:yes forced:no  scanDop:40 cpuDop:80 forceDop:0
   5780 Adopt new plan with dop=48
   5781 kkopqSetDopReason: Reason why we chose this DOP is: degree limit.
   5782 Costing completed. DOP chosen: 48.
   5783 AutoDop: kkopqSetMaxDopInCursorEnv:In the Cursor Session Env, max DOP is set to 48
   5784 *****************************
   5785 Number of Compilations tried: 3
   5786 *****************************

   5811 ============
   5812 Plan Table
   5813 ============
   5814 --------------------------------------------------+-----------------------------------+-------------------------+
   5815 | Id  | Operation                       | Name    | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
   5816 --------------------------------------------------+-----------------------------------+-------------------------+
   5817 | 0   | SELECT STATEMENT                |         |       |       |   63K |           |      |      |           |
   5818 | 1   |  SORT AGGREGATE                 |         |     1 |    19 |       |           |      |      |           |
   5819 | 2   |   PX COORDINATOR                |         |       |       |       |           |      |      |           |
   5820 | 3   |    PX SEND QC (RANDOM)          | :TQ10000|     1 |    19 |       |           |:Q1000| P->S |QC (RANDOM)|
   5821 | 4   |     SORT AGGREGATE              |         |     1 |    19 |       |           |:Q1000| PCWP |           |
   5822 | 5   |      PX BLOCK ITERATOR          |         |  276M | 5248M |   63K |  00:02:09 |:Q1000| PCWC |           |
   5823 | 6   |       TABLE ACCESS STORAGE FULL | BIGTAB  |  276M | 5248M |   63K |  00:02:09 |:Q1000| PCWP |           |
   5824 --------------------------------------------------+-----------------------------------+-------------------------+
   5825 Predicate Information:
   5826 ----------------------
   5827 6 - storage(:Z>=:Z AND :Z<=:Z AND "ID">1)
   5828 6 - filter("ID">1)
   5829
   5830 Content of other_xml column
   5831 ===========================
   5832   derived_cpu_dop: 80
   5833   derived_io_dop : 40
   5834   dop_reason     : degree limit
   5835   dop            : 48
   5836   px_in_memory_imc: no
   5837   px_in_memory   : no
   5838   io_rate        : 200
   5839   derived_io_dop : 40
   5840   cpu_rate       : 1000
   5841   derived_cpu_dop: 52
   5842   cpu_rate       : 1000
   5843   derived_cpu_dop: 29
   5844   cpu_rate       : 1000
   5845   derived_cpu_dop: 29
   5846   db_version     : 12.1.0.2
   5847   parse_schema   : MARTIN
   5848   dynamic_sampling: 11
   5849   plan_hash_full : 1216263238
   5850   plan_hash      : 4050997022
   5851   plan_hash_2    : 1216263238
   5852   Outline Data:
   5853   /*+
   5854     BEGIN_OUTLINE_DATA
   5855       IGNORE_OPTIM_EMBEDDED_HINTS
   5856       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
   5857       DB_VERSION('12.1.0.2')
   5858       OPT_PARAM('optimizer_dynamic_sampling' 11)
   5859       ALL_ROWS
   5860       SHARED(48)
   5861       OUTLINE_LEAF(@"SEL$1")
   5862       FULL(@"SEL$1" "BIGTAB"@"SEL$1")
   5863     END_OUTLINE_DATA
   5864   */

There you have it! The v$sql_plan.other_xml column contains all the necessary information-no need for a 10053 trace :). The fanciest way I can come up with to get that information is to use this statement:

select t.*
 from v$sql_plan v,
  xmltable(
	'/other_xml/info'
	passing xmltype(v.other_xml)
	columns
		info_type varchar2(30) path '@type',
		info_value varchar2(30) path '/info'
  ) t
where v.sql_id = '&sql_id'
  and v.child_number = &child_number
  and other_xml is not null;

INFO_TYPE                      INFO_VALUE
------------------------------ ------------------------------
derived_cpu_dop                51
derived_io_dop                 40
dop_reason                     degree limit
dop                            48
px_in_memory_imc               no
px_in_memory                   no
io_rate                        200
derived_io_dop                 40
cpu_rate                       1000
derived_cpu_dop                26
cpu_rate                       1000
derived_cpu_dop                26
cpu_rate                       1000
derived_cpu_dop                26
db_version                     12.1.0.2
parse_schema                   "MARTIN"
plan_hash_full                 1216263238
plan_hash                      4050997022
plan_hash_2                    1216263238

19 rows selected.

But I digress… By the way-this statement used 48 PX server processes because it was the only one active at the time.

Causing trouble

After hitting publish I noticed that I copied the wrong example…

An example working better for the purpose of demonstrating the effect of queueing uses a more realistic case where statements use different DOPs. Two sessions are enough, one that will use 12 slaves, and another that requests 36. The statement with a DOP of 12 is started first, the one with a DOP of 36 a few seconds later. In a third session I gathered some diagnostic information:

SYS:dbm011> select px.sid, px.serial#,px.qcsid,px.qcserial#,px.qcinst_id,px.degree,px.req_degree,
  2  s.username, s.sql_id, s.sql_child_number, s.event, s.state
  3  from v$px_session px, v$session s
  4  where s.sid = px.sid
  5    and s.serial# = px.serial#
  6  order by px.qcsid;

        SID     SERIAL#       QCSID   QCSERIAL#   QCINST_ID      DEGREE  REQ_DEGREE USERNAME             SQL_ID        SQL_CHILD_NUMBER EVENT                                                            STATE
----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------- ------------- ---------------- ---------------------------------------------------------------- --------------------
        265       14828         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITED SHORT TIME
        330       21719         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        395       59827         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITED SHORT TIME
        461       17363         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        525        4955         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        592        2687         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        526       60805         526                                                 MARTIN               9md3ukhghgj58                0 PX Deq: Execute Reply                                            WAITING
        720       27626         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        786       47540         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITED KNOWN TIME
        850       41110         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITED SHORT TIME
        918       46836         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        983       12699         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING
        657       22654         526       60805           1          12          12 MARTIN               9md3ukhghgj58                0 cell smart table scan                                            WAITING

13 rows selected.

SYS:dbm011> select count(qcsid),qcsid from v$px_session group by qcsid;

COUNT(QCSID)       QCSID
------------ -----------
          13         526

SYS:dbm011>
SYS:dbm011> select count(event),event from v$session where username = 'MARTIN' group by event;

COUNT(EVENT) EVENT
------------ ----------------------------------------------------------------
          12 cell smart table scan
           1 PX Deq: Execute Reply
           1 resmgr:pq queued

SYS:dbm011>

So my first session is well underway, 12 way parallel as requested. The other session had to wait but kicked into motion as soon as my first session finished.

SYS:dbm011> select px.sid, px.serial#,px.qcsid,px.qcserial#,px.qcinst_id,px.degree,px.req_degree,
  2  s.username, s.sql_id, s.sql_child_number, s.event, s.state
  3  from v$px_session px, v$session s
  4  where s.sid = px.sid
  5    and s.serial# = px.serial#
  6  order by px.qcsid;

        SID     SERIAL#       QCSID   QCSERIAL#   QCINST_ID      DEGREE  REQ_DEGREE USERNAME             SQL_ID        SQL_CHILD_NUMBER EVENT                                                            STATE
----------- ----------- ----------- ----------- ----------- ----------- ----------- -------------------- ------------- ---------------- ---------------------------------------------------------------- --------------------
        265       43575         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        525       17778         525                                                 MARTIN               4dz3dapm4uy8q                0 PX Deq: Execute Reply                                            WAITING
        395       10504         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        462       57813         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        526       12648         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        591       42643         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        657        5236         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        720       19243         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        786       61636         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        850       17734         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED KNOWN TIME
        918       60016         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        983       24489         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
       1044       41056         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
       1108        8936         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
       1178       52303         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED KNOWN TIME
       1242       37008         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
       1307       28798         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED SHORT TIME
       1372       22172         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED SHORT TIME
       1437       59990         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED SHORT TIME
       1501        9307         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
          6       39400         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
         72       28569         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        137       18368         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        202       56832         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        333        6482         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        397       28334         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        461       17550         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        524         958         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        593       41235         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        656       51686         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED KNOWN TIME
        719       40346         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED SHORT TIME
        785        5157         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        851       35243         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITED KNOWN TIME
        917       45635         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        980       18504         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
       1046       27779         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING
        330       10724         525       17778           1          36          36 MARTIN               4dz3dapm4uy8q                0 cell smart table scan                                            WAITING

37 rows selected.

SYS:dbm011>
SYS:dbm011> select count(qcsid),qcsid from v$px_session group by qcsid;

COUNT(QCSID)       QCSID
------------ -----------
          37         525

SYS:dbm011>
SYS:dbm011> select count(event),event from v$session where username = 'MARTIN' group by event;

COUNT(EVENT) EVENT
------------ ----------------------------------------------------------------
          36 cell smart table scan
           1 PX Deq: Execute Reply

The effect of queueing can be seen in the extended execution time:

  • 12 way parallel-no queueing: Elapsed: 00:00:07.16
  • 36 way parallel-with a bit of queueing: Elapsed: 00:00:12.92

V$PX_SESSION allows you to identify the culprit causing other sessions to queue-have a look at it and see if you can optimise the statement or run it later. This should unblock you. Also have a look at parallel_servers_target- you might have limited yourself too much. I personally don’t exceed the core/thread count on a box by too much to avoid scheduling problems.

Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl)

Sometimes, a few pictures (or in this case index block dumps) is better than a whole bunch of words :) In my previous post, I introduced the new Advanced Index Compression feature, whereby Oracle automatically determines how to best compress an index. I showed a simple example of an indexed column that had sections of index entries that were […]

KeePass 2.28 Released

KeePass 2.28 has just been released.

I’ve just upgraded at home (Fedora 20 and OS X both on Wine) and at work (Windows 7) and everything looks fine.

Read about my Adventures with DropBox and KeePass to see how I make use of it.

Cheers

Tim…

 


KeePass 2.28 Released was first posted on October 8, 2014 at 9:58 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Oracle Recertification Requirement

I saw this post about the policy change this morning.

There is also a comment about it here.

You can be cynical about this and assume it’s a money thing, but I’m actually in favour of it. Red Hat have a recertification policy also. If you get your RHCE, you need to do another certification, even if it is just a one-off specialism, within 3 years or you lose your status.

In Oracle’s case, the recertification is based around retired exams, so it can be quite an extended time. If you take the 11g DBA certification, that’s been around for about 5 years and has still not been retired, so it will probably be around a 7 year recertification cycle for that exam. I don’t feel that is particularly excessive. Oracle’s release cycles seem to be slowing, so I don’t see this recertification being too much of an issue…

What this does mean is that people who did the 7, 8, 8i, 9i, 10g OCP and have never bothered to upgrade it should take OCP off their CV, but I doubt that is going to happen… :)

Cheers

Tim…


Oracle Recertification Requirement was first posted on October 8, 2014 at 10:06 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.