Now that I've shown in the previous post in general that sometimes Parallel Execution plans might end up with unnecessary BUFFER SORT operations, let's have a look what particular side effects are possible due to this.
What would you say if someone tells you that (s)he just did a simple, straightforward "SELECT * FROM TABLE" that took several minutes to execute without returning, only to then error out with "ORA-01652 unable to extend temp segment", and the TABLE in question is actually nothing but a simple, partitioned heap table, so no special tricks, no views, synonyms, VPD etc. involved, it's really just a plain simple table?
I’m in the Leadership Circle again :-) That is a quarterly Corporate Award for the best instructors worldwide according to customer feedback.
Oracle University has generally high quality standards, so it is hard to stand out individually. The more I am proud to be listed together with these great colleagues:
This is a short post to recommend some recent blog posts by Nikolay Manchev and Bertrand Drouvot on the topic of Oracle Database 12c NUMA awareness.
Nikolay provides a very helpful overview on Linux Control Groups and how they are leveraged by Oracle Database 12c. Bertrand Drouvot carried the topic a bit further by leveraging SLOB to assess the impact of NUMA remote memory on a cached Oracle Database workload. Yes, SLOB is very useful for more than physical I/O! Good job, Bertrand!
These are good studies and good posts!
Also, one can refer to MOS 1585184.1 for more information on Control Groups and a helpful script to configure CGROUPS.
The following links will take you to Nikolay and Bertrand’s writings on the topic:
There’s a thread on the OTN database forum at present asking for advice on optimising a query that’s trying to find “the most recent price” for a transaction given that each transaction is for a stock item on a given date, and each item has a history of prices where each historic price has an effective start date. This means the price for a transaction is the price as at the most recent date prior to the transaction date.
Yesterday I posted notification of the latest plug-in releases for Enterprise Manager 12.1.0.4. There have been quite a number of enhancements in these new plug-ins, so let’s have a quick look at each plug-in and what’s new.
PDB as a Service Custom Placement Option
Customers often want to place PDBs that host data of similar applications inside a single CDB. Doing this ensures a flavor of consolidation with intelligent resource utilization. PDB as a Service (PDBaaS) now provides this capability through a specific service template option. In this mode, at request submission time SSA users can specify a particular CDB from the pool members to host the PDB being created.
Data Lifecycle Management
The old chestnut about comparing speeds of count(*), count(1), count(non_null_column) and count(pk_column) has come up in the OTN database forum (at least) twice in the last couple of months. The standard answer is to point out that they will all execute the same code, and that the corroborating evidence for that claim is that, for a long time, the 10053 trace files have had a rubric reporting: CNT – count(col) to count(*) transformation or, for an even longer time, that the error message file (oraus.msg for the English Language version) has had an error code 10122 which produced (from at least Oracle 8i, if not 7.3):
Data virtualization solutions also known as Copy Data Management (CDM), Copy Data Virtualization (CDV) and Data #222222;">Virtualization Appliances (DVA) are rising rapidly as over 100 of the Fortune 500 have adopted data virtualization solutions between 2010 and end of 2014.
When using Parallel Execution, depending on the plan shape and the operations used, Oracle sometimes needs to turn non-blocking operations into blocking operations, which means in this case that the row source no longer passes its output data directly to the parent operation but buffers some data temporarily in PGA memory / TEMP. This is either accomplished via the special HASH JOIN BUFFERED operation, or simply by adding BUFFER SORT operations to the plan.The reason for such a behaviour in parallel plans is the limitation of Oracle Parallel Execution that allows only a single data redistribution to be active concurrently.
The latest plug-in release has just been announced. There are new versions for the following plug-ins:
Please note the framework version has not been updated, it’s still at 12.1.0.4.0. The plug-in versions alone have changed.
This release has key offerings as Cloud Maintenance and Data Refresh in the DBaaS area. It also has new MWaaS offerings like SOA as a Service and OSB as a Service, along with other key features in the Storage Management Framework, Database and Audit Vault / Database Firewall areas.
The spelling is with a Z rather than an S because it’s an Oracle thing.
Tim Hall has just published a set of notes on Adaptive Query Optimization, so I thought I’d throw in one extra little detail.
When the optimizer decides that a query execution plan involves some guesswork the run-time engine can monitor the execution of the query and collect some information that may allow the optimizer to produce a better execution plan. The interaction between all the re-optimization mechanisms can get very messy, so I’m not going to try to cover all the possibilities – read Tim’s notes for that – but one of the ways in which this type of information can be kept is now visible in a dynamic performance view.
Recent comments
12 weeks 6 days ago
25 weeks 9 hours ago
29 weeks 2 days ago
30 weeks 1 day ago
34 weeks 5 days ago
1 year 3 weeks ago
1 year 24 weeks ago
2 years 1 week ago
2 years 38 weeks ago
2 years 38 weeks ago