Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

January 2015

"SELECT * FROM TABLE" Runs Out Of TEMP Space

Submitted by randolf.geist on Thu, 2015-01-08 18:49

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?

Oracle University Leadership Circle 2015

Submitted by Uwe Hesse on Thu, 2015-01-08 10:56

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:

Oracle University Leadership Circle 2015

Recommended Reading: Oracle Database 12c NUMA-Related Topics

Submitted by kevinclosson on Wed, 2015-01-07 21:42

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:

Most Recent

Submitted by Jonathan Lewis on Wed, 2015-01-07 18:21

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.

What’s New with the Latest Release

Submitted by pete.sharman on Tue, 2015-01-06 18:32

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.

Enterprise Manager for Cloud (version 12.1.0.9.0)

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

Count (*)

Submitted by Jonathan Lewis on Tue, 2015-01-06 18:04

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):

Top 3 criteria to choose a data virtualization solution 

Submitted by khailey on Tue, 2015-01-06 15:44
mirror_reflection_ copy
Photo: Martin #000000;">Beardy (CC BY 2.0)

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.

Unnecessary BUFFER SORT Operations - Parallel Concatenation Transformation

Submitted by randolf.geist on Mon, 2015-01-05 21:47

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.

New Plug-ins Released

Submitted by pete.sharman on Mon, 2015-01-05 20:13

The latest plug-in release has just been announced. There are new versions for the following plug-ins:

  • Enterprise Manager for Cloud – version 12.1.0.9.0
  • Enterprise Manager for Oracle Cloud Framework – version 12.1.0.2.0
  • Enterprise Manager for Storage Management – version 12.1.0.5.0
  • Enterprise Manager for Oracle Database – version 12.1.0.7.0
  • Enterprise Manager for Oracle Fusion Middleware – version 12.1.0.7.0
  • Enterprise Manager for Oracle Audit Vault and Database Firewall – version 12.1.0.3.0

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.

Re-optimization

Submitted by Jonathan Lewis on Mon, 2015-01-05 12:54

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.