Skip navigation.

Feed aggregator

Update multiple columns

Patrick Barel - Wed, 2015-12-23 05:03

.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: #ffff00; } .code .html { color: #800000; } .code .attr { color: #ff0000; } .code .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .code .lnum { color: #606060; }

This is something I knew somewhere in the back of my head, but had forgotten about until now.

When you want to update multiple columns in a single SQL statement based on a sub query you can of course duplicate this query for every column you want to update. But this violates the SPOD (Single Point Of Definition) ‘rule’.

As an example I have the following requirement:

Add two columns to the EMP table containing the name and job of the manager and fill these columns with the right values.

First of all I need to add the columns to the table, with is easy:

alter table emp add (mgr_name varchar2(10)
                    ,mgr_job varchar2(9)
                    )

Then comes the ‘tricky’ part. I can of course fill up these columns in separate statements, like this:

update emp e
   set e.mgr_name = (select m.ename
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/
update emp e
   set e.mgr_job = (select m.job
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

But this implies two roundtrips from the client to the database. These statements can be combined into a single one:

update emp e
   set e.mgr_name = (select m.ename
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
     , e.mgr_job = (select m.job
                       from emp m
                      where 1=1
                        and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

But there is an easier, and more elegant way to do this:

update emp e
   set (e.mgr_name, e.mgr_job) = (select m.ename, m.job
                                   from emp m
                                  where 1=1
                                    and m.empno = e.mgr)
 where 1=1
   and e.mgr is not null
/

This is of course a pretty simple example, but you can imagine what would happen if you want to update more columns, create a complex sub query or worse, make modifications to the predicates. You are more than likely going to forget one or more sub queries giving you an undesired result.

update December 25th 2015: Find a demonstration script on LiveSQL

Update multiple columns

Bar Solutions - Wed, 2015-12-23 05:03
.code, .code pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .code pre { margin: 0em; } .code .rem { color: #ff0000; } .code .kwrd { color: #008080; } .code .str { color: #0000ff; } .code .op { color: #0000c0; } .code .preproc { color: #cc6633; } .code .asp { background-color: [...]

Github Repository

Bobby Durrett's DBA Blog - Tue, 2015-12-22 10:33

I am experimenting with Github. I have created a repository for my Oracle database related scripts. Here is my Github URL: https://github.com/bobbydurrett/dba.git

You can clone this repository locally if you have git installed using this command:

git clone https://github.com/bobbydurrett/dba.git

I’ve had challenges before when I write a blog post about a script and then revise the script later.  It seems weird to update the post with links to the new version.  So, I’m thinking of using github to expose the scripts that I want to share with the Oracle community and then I can update them over time and the version history will be visible.

Let me know if you have any questions or suggestions.  This is just my first attempt at using Github for this purpose.

Bobby

Categories: DBA Blogs

Calling all Apps DBAs doing 11i to R12.x upgrades

Vikram Das - Tue, 2015-12-22 09:02
At this time of the year during holidays, the Apps DBA community is busy doing upgrades as longer downtimes are possible.  In case you are facing any issues, please feel free to write to me at my email: oracleappstechnology@gmail.com .  I will be glad to hear from you and help you.
Categories: APPS Blogs

Action Links in OBIEE 12c – Part 2

Rittman Mead Consulting - Tue, 2015-12-22 07:40

Introduction and Scenario

Part 1 of this series on Action Links had us creating a simple navigate action and left us in suspense with the promise of a new means by which to filter one report from another. Now time to deliver! The scenario: say we’ve got a company dashboard that is pretty locked down in terms of adding any new user-facing content, and as a developer you occupy more of an analyst role where you can build analyses all day, but dashboards are a no go. So how then can we drive some of our own detail analyses from dashboard selections? The answer? Hidden driving documents! In a nutshell, these documents store selected dashboad values and act as a filter for our detail analyses, utilizing OBIEE’s “filter on the results of another analysis” filter option. It should be noted that we could simply set the target analysis’s filters to be equal to the presentation variables generated by the source dashboard prompt. However, the following technique is simply to illustrate a possible technique, say when you’re going between two different subject areas, that might be applicable in your current situation or at least spark an idea to get you headed in the right direction towards a possible solution. So, let’s start by getting the pieces to our puzzle together.

Start with the dashboard and prompt. We are going to navigate from a monthly sales trend to a detail report displaying product sales detail.

The Solution

Now that we’ve determined the dashboard analysis from which we want to navigate, we can set up our driving document. Again, the overall concept of a driving document is to act as an intermediary container for selected values that then get passed from one report to another. So let’s set this up. Given that our trend is prompted on Year, Company, Region, we need to place three dummy columns on our driving report to store the presentation variables generated by each prompt. I used the same three columns in my driving report for consistency, however any column will do. Note that the columns in the picture have custom names already are not the native columns from their respective tables.

Edit each column to store the presentation variables generated by your dashboard prompt. If the dashboard prompt does not have a presentation variable for each column prompt, go ahead and get those set up before completing this step. My year column variable in this example is appropriately titled SALES_YEAR. Be sure to put single quotes around your variable as in ‘@{SALES_YEAR}’ when entering it in the Edit Column dialogue.

You can now save your driving report and place it on your dashboard. Right away you should see that your driving report picks up the values in each column prompt. Note the use of a default value {2012}. Using these is ideal so that if we want to edit our driving document, we at least have some values to pass it, as opposed to it throwing an error on the Results tab.

 

Now for some bad news. You might have noticed an obvious limitation to this approach, which is the driving report’s limited selection of only one column value from each column prompt. Nevertheless, let’s run this thing through to the end and see what happens. The next piece of the puzzle is setting up your target analysis.

Our analyst in this scenario wants to set up two reports to be driven off of our dashboard selection. The first will be a top 10 report for products based on the selected month. The second will be another top 10, but for sales reps instead. Navigating to our desired subject area, let’s bring over the needed columns, formatting as needed. In this example, we’re going to include a custom calc which gives us the percent variance to the prior period which has also been conditionally formatted to indicate positive or negative growth.
We have placed the columns necessary to build each report and also added filters, as seen in the proceeding picture.

Now comes the crux of this neat trick. We need to add the proper filters to the analysis so that any values we pick from both the dashboard prompt and the source analysis itself will drive our target analysis.

In the target report, we added a filter for each column that we’d like our analysis filtered on and likewise, comes from our source report.

To set this filter, simply apply the ‘is based on results of another analysis’ filter to each column. Then, browse for the driving report we made and apply the ‘is equal to any’ Relationship option. As our driving report values will only have one value for each of the filtered columns, our target report columns should filter on each of these. Lastly, use the drop down to choose the corresponding column from the source analysis. In the following pic we are selecting the  Region column from our driving report so that our target report will be filtered on whatever Region is selected on our source dashboard.

As the last steps in our target analysis, make sure you’ve set all the filter columns to respond to the driver analysis and that you select the ‘is prompted’ filter for any additional dimension columns on your report that are not driven by a dashboard prompt. In the example, this is going to be the Month column. This will ensure that our target analysis “listens” to whatever month is selected in our source analysis, that is, our sales trend.

Don’t worry if your Results tab shows no values as this is expected behavior (we haven’t sent any values over yet!). If you’d like to set it up so that your target report actually yields results without clicking on the action link, simply apply default values to the presentation variables in your driving document, as described above in the Year example. The last piece of this puzzle is finally assigning the action link to our respective column. Follow the instructions outlined in the first part of this post in order to navigate to our target analysis. In our example, this is going to be our Sales column.

Now that we’ve got all the pieces of the puzzle, let’s put it all together and see what happens. Clicking on any sales point on our trend line, we should be taken to the target analysis which should be filtered on both our dashboard prompt values as well as our Month. In this example, I’ve applied a filters object to the target analysis to make sure the target analysis responded to all applied filters.

Looks like everything works great! At this point you can hide the driver report on your dashboard and get rid of the filters object on your target analysis. I’d like to hear about some neat ways people have used this technique or modified it to suit their particular business/use case. Feel free to post in the comments! Stay tuned for part three of this series on Action Links where we go in depth about the GoURL syntax and how it can be a powerful and customizable tool in navigating to web pages and OBIEE 12c content.

The post Action Links in OBIEE 12c – Part 2 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Predicates

Jonathan Lewis - Tue, 2015-12-22 06:58

I received an email recently that started with the sort of opening sentence that I see far more often than I want to:

I have come across an interesting scenario that I would like to run by you, for your opinion.

It’s not that I object to being sent interesting scenarios, it’s just that they are rarely interesting – and this wasn’t one of those rare interesting ones. On the plus side it reminded me that I hadn’t vented one of my popular rants for some time.

Here’s the problem – see if you can work out the error before you get to the rant:

“I’ve got a table and a view on that table; and I’ve got a query that is supposed to use the view. Whether I use the table or the view in query the optimizer uses the primary key on the table to access the table – but when I use the table the query takes about 30 ms, when I use the view the query takes about 903 ms”.

The email included a stripped-down version of the problem (which I’ve stripped even further) – so score some brownie points on that one.  Here, in order, are the table, the view, and two variations of the query:


create table table_a (
	col_1  varchar2(20)	not null,
	col_2  number(10)	not null,
	col_3  varchar2(20)	not null,
	col_4  varchar2(100)
);

insert /*+ append */ into table_a
select
	lpad(mod(rownum-1,1000),10), mod(rownum-1,1000), lpad(rownum,20), rpad(rownum,100)
from
	all_objects
where
	rownum <= 10000
;
commit; 

alter table table_a add constraint ta_pk primary key(col_1, col_2, col_3); 
execute dbms_stats.gather_table_stats(user,'table_a',method_opt=>'for all columns size 1')

create or replace view view_a (
	col1,
	col2,
	col3,
	col4
)
as
select 
	col_1 as col1,
	cast(col_2 as number(9)) as col2,
	col_3 as col3,
	col_4 as col4
from
	table_a
;


variable b1 varchar2(10)
variable b2 number

exec :b1 := lpad(0,10)
exec :b2 := 0

select /*+ index(table_a) tracking_t2 */
	 *
from	table_a
where 
	col_1 = :b1
and	col_2 = :b2
;

select /*+ index(view_a.table_a) tracking_v2 */
	*
from	view_a
where 
	col1 = :b1
and	col2 = :b2
;

Question 1 (for no points): Why would there be a difference (though very small in this example) in performance ?

Question 2 (for a virtual pat on the head): What did the author of the email not do that made him think this was an interesting problem ?

Just to muddy the water for those who need a hint (that’s a hint hint, not an Oracle hint) – here are the two execution plans reprted from v$sql in version 12.1.0.2:


SQL_ID  514syc2mcb1wp, child number 0
-------------------------------------
select /*+ index(table_a) tracking_t2 */   * from table_a where  col_1
= :b1 and col_2 = :b2

Plan hash value: 3313752691

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |     10 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A |      1 |      1 |     10 |00:00:00.01 |      13 |
|*  2 |   INDEX RANGE SCAN                  | TA_PK   |      1 |      1 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------


SQL_ID  ck0y3v9833wrh, child number 0
-------------------------------------
select /*+ index(view_a.table_a) tracking_v2 */  * from view_a where
col1 = :b1 and col2 = :b2

Plan hash value: 3313752691

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |     10 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A |      1 |      1 |     10 |00:00:00.01 |      13 |
|*  2 |   INDEX RANGE SCAN                  | TA_PK   |      1 |      1 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

I’ve even shown you the Plan Hash Values for the two queries so you can check that the execution plans were the same.

So what have I just NOT done in my attempt to make it harder for you to understand what is going on ?

Give yourself a pat on the head if you’ve been thinking “Where’s the predicate section for these plans ?”  (9 years old today).

Here are the two predicate sections (in the same order as the plans above):


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL_1"=:B1 AND "COL_2"=:B2)


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL_1"=:B1)
       filter(CAST("COL_2" AS number(9))=:B2)

Notice how the optimizer can use both predicates to probe the index when we query the table but, thanks to the function applied to the column in the view, can only probe the index on the first column of the view and has to check every index entry for the first input value to see of the result of the cast matches the second input value. The size of the range scan in the second case could be much larger than the size of the range scan in the first case – the difference in performance could simply be a reflection that col_1 is very repetitive with many different values of col_2 for every value of col_1.

Interesting

While the problem itself isn’t interesting – it does raise a couple of points worth mentioning (and I’m not going to ask why the view has that surprising cast() in it – but if pushed I could invent a reason)

First, what steps have been taken to ensure that a query against the view won’t crash with Oracle error 1438:

SQL> insert into table_a values(:b1, 1e9,'x','x');

1 row created.

SQL> select * from view_a where col1 = :b1;
ERROR:
ORA-01438: value larger than specified precision allowed for this column

Possibly there’s a check constraint on the column restricting it to values that can survive the cast to number(9).

Secondly, it’s often possible to use constraints or virtual columns (or both together) that allow the optimizer to get clever with expression substitution and come up with optimal execution plans even when there are traps like this put in the way. In this case I couldn’t manage to make the usual tricks work. Possibly the only way to get the hoped-for performance is to create a second index on (col_1, cast(col_2) as number(9), col_3).


Enterprise Manager Cloud Control 13c : First Steps

Tim Hall - Tue, 2015-12-22 06:18

o-enterprisemgr-13c-clr-2769481Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0.0) was released a few days ago. Does that have the acronym “oemcc13cr1”? :)

As usual, my first steps are to do some basic installations. The approach is pretty similar to the 12c installations, but it’s a little greedier now. :)

My first attempt was a bit of a disaster. I was trying to install it on a VM with 8G of memory, about all I can spare on my work PC) and it was running like a dog. It got nearly to the end of the configuration section and I ran out of disk space on the physical host. That would have been OK if the installer were running on the VM itself, as the VM would have paused and resumed once I had cleared some space. Unfortunately, I was doing from an X session, which got killed and took my installer with it. :( Rather than trying to continue on my piece of shit work PC, I waited until I got home to do it on my server.

Once home, I kicked off two installation simultaneously. One on OL6 and one on OL7. Each VM had 10G of memory and their virtual disks were on different spindles to the OS disk. As a result, they ran through at a reasonable pace. Not mega fast, but OK.

Over the Christmas break I’ll have a go at some upgrades, then decide if we should be doing this in production at work. If you’ve followed the discussion on Twitter, you’ll know some of the basic requirements.

  • Oracle 12c (12.1.0.2) Enterprise Edition for the repository database. Patched to latest security patch.
  • You can use a Non-CDB or a PDB for the management repository. The template database is still non-CDB.
  • OPTIMIZER_ADAPTIVE_FEATURES=FALSE

That means we will need an upgrade of our repository database from 11.2.0.4 to 12.1.0.2. That’s no big drama, but another thing to do. :)

Cheers

Tim…

Update: Thanks to Seth Miller for pointing out my mistake about the PDB support for the OMR.

Enterprise Manager Cloud Control 13c : First Steps was first posted on December 22, 2015 at 1:18 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.

Stat Wars – using the Force (DBMS_STATS) for Incremental Stats Gathering

The Anti-Kyte - Tue, 2015-12-22 06:03

We haven’t been queuing for days to see that film.
Darth Debbie was quite insistent that she really did have better things to do than
queue up outside a cinema behind someone dressed as a Wookie.
Even the potential opportunity of uttering the line “will someone get this walking carpet out of my way ?!”, has not moved her on this point.
All things Star Wars are not to be mentioned in our house at the moment. So, any resemblance to a certain Space Opera in what follows is purely coincidental.

Anyway, a Long Time Ago in a Database far, far away….

It wasn’t easy being a DBA on the Jedi Archives Database.
Strong with the Force they may have been, but the users weren’t particularly patient.
On top of that, there was still some muttering going on about that unfortunate data loss incident with Obi Wan Kenobi and the missing planetary system.

All in all then, when complaints began coming in about the nightly batch overrunning, it was with a sense of dread that the Geeki began to investigate….

It was one of those batch jobs in which data was loaded into a table partition on the Oracle 11g R2 Database via a standard ETL process.
The table was partitioned daily so each load was into a new table partition.
That data was then used immediately by a subsequent report.

This required a call to DBMS_STATS to ensure that the Optimizer stats are up to date.
The problem was that it was taking several hours to gather stats, despite the partition name and the degree of parallelism being specified in the call.

It was at this point that our hard-pressed DBA recalled the wise words of his master – “when all else fails, read the manual you must!”

Incremental Stats Gathering – what the Manual saya

Whilst it’s fair to say that there were one or two issues with Incremental Stats when they were first introduced in 11g, these kinks are ironed out by version 11.2.03.

To start with then, this is what the Oracle Documentation has to say on when to gather Manual Statistics :

“For tables that are bulk-loaded, run the statistics-gathering procedures on the tables immediately following the load process.
Preferably, run the procedures as part of the same script or job that is running the bulk load.”

This confirms that the stats gathering step in the batch was appropriate and necessary.

There was also something quite interesting about the option of gathering Incremental Stats :

“An alternative to mandatory full table scans is gathering incremental statistics. When the following criteria are met, the database updates global statistics incrementally by scanning only the partitions that have changed:

  • The INCREMENTAL value for the partitioned table is true.
  • The PUBLISH value for the partitioned table is true.
  • The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

“Gathering table statistics incrementally has the following consequences:

  • The SYSAUX tablespace consumes additional space to maintain global statistics for partitioned tables.
  • If a table uses composite partitioning, then the database only gathers statistics for modified subpartitions. The database does not gather statistics at the subpartition level for unmodified subpartitions. In this way, the database reduces work by skipping unmodified partitions.
  • If a table uses incremental statistics, and if this table has a locally partitioned index, then the database gathers index statistics at the global level and for modified (not unmodified) index partitions. The database does not generate global index statistics from the partition-level index statistics. Rather, the database gathers global index statistics by performing a full index scan.”

In summary, if you specify the appropriate parameters, Oracle will work out what stats to gather and not waste time refreshing statistics on data that has not changed.

Making sure that the target table has INCREMENTAL and PUBLISH set to TRUE is fairly straightforward and can be achieved as simply as :

dbms_stats.set_table_prefs(my_table_owner, my_table, 'INCREMENTAL', 'TRUE');
dbms_stats.set_table_prefs(my_table_owner, my_table, 'PUBLISH', 'TRUE');

… where my_table_owner is the owner and my_table is the name of the table you want to gather stats on.

That rest should be simple enough for our Jedi DBA. After all, the Recommended parameter values for DBMS_STATS to perform Incremental Stats Gathering should be the defaults, right ?
I’m sure someone once said something along the lines of “beware the quick and easy path…”

The Parameter values for DBMS_STATS

The fact is that DBMS_STATS.GATHER_TABLE_STATS has a number of parameters, not all of which are mandatory. However, the defaults used for some of them are not necessarily what is required for Incremental Stats Gathering to take place.

You can find the default values that this procedure uses here.

The recommended settings to enable incremental stats gathering are subtly different :

Parameter Name Recommended Value estimate_percent DBMS_STATS.AUTO_SAMPLE_SIZE method_opt FOR ALL COLUMNS SIZE AUTO degree DBMS_STATS.AUTO_DEGREE granularity AUTO cascade DBMS_STATS.AUTO_CASCADE no_invalidate DBMS_STATS.AUTO_INVALIDATE

It may be that most of the default values match up to those recommended. However this is at least one parameter that definitely doesn’t have the same default value as that recommended.

The DEGREE parameter, which determines the degree of parallelism to be used in the Stats Gathering operation is defaulted to NULL.
This may seem strange at first, until you realise that this is because Parallel Stats Gathering is only available in the Enterprise Edition of the Oracle 11g R2 database.

If your running a Data Warehouse application, you will almost certainly be on Enterprise Edition.
If you have partitioned tables, Enterprise Edition is a pre-requisite as partitioning is an option for EE.
If you want to make sure, you can always check by running :

select banner
from v$version
where banner like 'Oracle Database %'
/

The output should be something like :

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production.

Assuming this is the case, you should be able to specify the recommended DBMS_STATS.AUTO_DEGREE as the value for the DEGREE parameter without fear of any licensing reprucussions later on.

One other point for consideration is whether or not you want to collect Histograms.
It’s not unheard in Data Warehouses for bind variable peeking to have been disabled. If your database is set up this way then the optimizer will never look at histograms. Therefore, it seems a bit pointless to collect and store them.

If you want to know if this is the case, you can find out easily enough….

select value
from v$parameter
where name = '_optim_peek_user_binds'
/

If this query returns ‘TRUE’, or does not return any rows, then bind variable peeking is enabled and histograms are required. Otherwise, it isn’t and Histograms will not be used by the Optimizer.

Incremental Stats – the code

Now that we know what the optimal parameter values are likely to be, we can knock up something like this to gather stats on our partitioned tables :

create or replace package incremental_stats
as
	procedure gather_table_stats
	(
		i_owner in user_users.username%type default null,
		i_table in user_tables.table_name%type
	);
end incremental_stats;
/

create or replace package body incremental_stats
as
    -- Private 
	function bind_peeking_enabled_fn
		return boolean
	is
		l_value v$parameter.value%type;
	begin
		select value
		into l_value
		from v$parameter
		where name = '_optim_peek_user_binds';

		if l_value = 'TRUE' then
			return true;
		else
			return false;
		end if;
	exception when no_data_found then
	    -- parameter not set...
		return true;
	end bind_peeking_enabled_fn;
	
	procedure gather_table_stats
	(
		i_owner in user_users.username%type default null,
		i_table in user_tables.table_name%type
	)
	is
	--
	-- Gather table stats using Oracle recommended settings (as at 11G R2).
	-- See http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#i13546
	-- The one exception to this is opt_method - if we're not using bind-variable peeking at the moment
	-- there's no point in collectiong histograms. 
	--
			
		l_method_opt varchar2(30);
	begin
		
		-- Check to see if bind_variable peeking is enabled
		if bind_peeking_enabled_fn then
			l_method_opt := 'FOR ALL COLUMNS SIZE AUTO';
		else
			l_method_opt := 'FOR ALL COLUMNS SIZE 1';
		end if;
		
		-- Ensure that table prefs are set to facilitate incremental stats gathering for partitions
		dbms_stats.set_table_prefs(i_owner, i_table, 'INCREMENTAL', 'TRUE');
		dbms_stats.set_table_prefs(i_owner, i_table, 'PUBLISH', 'TRUE');
		
		--
		-- NOTE - apart from the parameters specified here, all other parameters
		-- for DBMS_STATS.GATHER_TABLE_STATS derive their default value from the existing preferences.
		-- These can be set by a call to DBMS_STATS.SET_TABLE_PREFS. 
		--
		dbms_stats.gather_table_stats
		(
			ownname =&gt; i_owner,
			tabname =&gt; i_table,
			degree =&gt; dbms_stats.auto_degree,
			method_opt =&gt; l_method_opt
		);
	end gather_table_stats;
	
end incremental_stats;
/

All we need now is a test case to show that table stats are only gathered on the partition(s) that have changed.

A Simple Test

To start with, we need a partitioned table….

create table star_wars_characters
(
    film_name varchar2(100) not null,
    character_name varchar2(100)
)
partition by list(film_name)
(
    partition originals values ( 'A NEW HOPE', 'THE EMPIRE STRIKES BACK', 'RETURN OF THE JEDI'),
    partition prequels values ('THE PHANTOM MENACE', 'ATTACK OF THE CLONES', 'REVENGE OF THE SITH'),
    partition sequels values ('THE FORCE AWAKENS')
)
/

…with some data already in it…

-- Phantom Menace

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'ANAKIN SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'DARTH MAUL')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'PADME AMIDALLA')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'SENATOR PLAPATINE')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'YODA')
/

insert into star_wars_characters( film_name, character_name)
values('THE PHANTOM MENACE', 'MACE WINDU')
/

-- Attack of the Clones

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'ANAKIN SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'DARTH MAUL')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'PADME AMIDALLA')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'SENATOR PLAPATINE')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'YODA')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'MACE WINDU')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'COUNT DOOKU')
/

insert into star_wars_characters( film_name, character_name)
values('ATTACK OF THE CLONES', 'JANGO FETT')
/

-- Revenge of the Sith

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'ANAKIN SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'DARTH MAUL')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'PADME AMIDALLA')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'SENATOR PLAPATINE')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'YODA')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'MACE WINDU')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'COUNT DOOKU')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'JANGO FETT')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'ANAKIN SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'DARTH MAUL')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'PADME AMIDALLA')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'SENATOR PLAPATINE')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'YODA')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'MACE WINDU')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'COUNT DOOKU')
/

insert into star_wars_characters( film_name, character_name)
values('REVENGE OF THE SITH', 'SENATOR ORGANA')
/

-- A New Hope
insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'LUKE SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'DARTH VADER')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'HAN SOLO')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'PRINCESS LEIA')
/

insert into star_wars_characters( film_name, character_name)
values('A NEW HOPE', 'GRAND MOFF TARKIN')
/

-- Empire Strikes Back
insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'LUKE SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'DARTH VADER')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'HAN SOLO')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'PRINCESS LEIA')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'GRAND MOFF TARKIN')
/

insert into star_wars_characters( film_name, character_name)
values('THE EMPIRE STRIKES BACK', 'LANDO CALRISSIAN')
/

-- Return of the Jedi
insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'LUKE SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'DARTH VADER')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'OBI WAN KENOBI')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'R2-D2')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'C3PO')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'HAN SOLO')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'PRINCESS LEIA')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'GRAND MOFF TARKIN')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'LANDO CALRISSIAN')
/

insert into star_wars_characters( film_name, character_name)
values('RETURN OF THE JEDI', 'ADMIRAL ACKBAR')
/ 

-- Force Awakens

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'KYLO RENN')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'FINN')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'REY')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'BB8')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'HAN SOLO')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'PRINCESS LEIA')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'LUKE SKYWALKER')
/

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'CHEWBACCA')
/

commit;

…and gather stats.

begin
    dbms_stats.gather_table_stats('MIKE', 'STAR_WARS_CHARACTERS');
end;
/

We can see when the stats were last gathered on each partition…

select partition_name, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI')
from user_tab_partitions
where table_name = 'STAR_WARS_CHARACTERS'
/

PARTITION_NAME	     TO_CHAR(LAST_ANALYZED,'DD-
-------------------- --------------------------
ORIGINALS	     19-DEC-2015 21:36
PREQUELS	     19-DEC-2015 21:36
SEQUELS 	     19-DEC-2015 21:36

Now, if we insert data into a single parition…

insert into star_wars_characters( film_name, character_name)
values('THE FORCE AWAKENS', 'CAPTAIN PHASMA')
/

commit;

… and run our stats gathering package…

begin
    incremental_stats.gather_table_stats('MIKE', 'STAR_WARS_CHARACTERS');
end;
/

… we can see that stats have only been gathered on the partitions that have changed…

select partition_name, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI')
from user_tab_partitions
where table_name = 'STAR_WARS_CHARACTERS'
/

PARTITION_NAME	     TO_CHAR(LAST_ANALYZED,'DD-
-------------------- --------------------------
ORIGINALS	     19-DEC-2015 21:36
PREQUELS	     19-DEC-2015 21:36
SEQUELS 	     19-DEC-2015 21:44

So, is this the panacea for all of your Stats Gathering woes ?
The Geeki reckon that they’ve observed some significant improvements in Stats gathering execution times. One example was where a job went from running in over 4 hours to under 18 minutes.
They’ve also noticed that there is less of a tendency for batch jobs to serialize because some stats job is hogging all of the parallel servers on the database.

That said, you need to remember that this is a Galaxy far, far away and there’s no knowing how the behaviour of Midi-Chlorians may affect runtime.
The best thing to do would be to test it on your application with your partitioned tables ( which will probably be a bit bigger than the one in my test case).

Planning our trip to finally see the film has hit a bit of a snag. When I suggested that we might go in fancy dress, Deb told me that she’d rather kiss a Wookie.
Sometimes I suspect that, despite her protestations to the contrary, she’s more of a fan than she lets on.


Filed under: Oracle, PL/SQL, SQL Tagged: dbms_stats, dbms_stats.auto_stats_degree, dbms_stats.gather_table_stats, histograms, incremental stats, _optim_peek_user_binds

Remove Blanks from Formated Date Column

Denes Kubicek - Tue, 2015-12-22 02:31
This small example is showing how powerful jQuery is and how easy it is to use it in APEX. In this case, I needed to correct the oracle formatting, which adds blanks to the name of the day prior to comma. Probably oracle RPAD's the day names to the longest (Wednesday). Those are showing in APEX as one blank - see the left column. The only good way to go around that annoying side effect is to use jquery and basically one line of code.

Categories: Development

Simplified Analytics: Putting Power in the Hands of End Users

PeopleSoft Technology Blog - Mon, 2015-12-21 16:19

PeopleTools 8.55 is an important release with lots of exciting features.  We recently held a Partner Summit, where PeopleSoft Development and Strategy met with many of our implementation partners to describe and discuss the new capabilities of our products and the value they provide to customers.  One feature that garnered quite a bit of interest is our new Simplified Analytics feature.

Simplified Analytics is important because it puts reporting and analytic power in the hands of end users.  It enables users to generate ad hoc analytics in the context of transactions.  This can be tremendously valuable for a myriad of business processes.  For example, when a manager wants to give an employee a raise, they may want to analyze how that employee is currently compensated compared to peers in the same role and performance class.  Or suppose a person is working with suppliers and they want to determine which suppliers to use.  They may want to analyze suppliers’ performance, price, etc.  Simplified analytics can be valuable in so many transactions, the list is almost endless.  And again, end users can employ this feature without having to turn to technical experts to generate reports for them.

Let’s look at how this would work from the end user perspective for one of the use cases I mentioned above:  I am a manager who is planning to give one of my employees a raise, and need additional information to provide the appropriate amount.

As a manager, I use Related Actions to initiate a Salary Change for Rosanna, one of my subordinates.

This takes me to the transaction where I can provide the change, but I’m not sure how much to give Rosanna, so I create a new analytic in the related information section on the right side of the page.

This initiates the simple process for generating a Simplified Analytic.  Note that the analytic is contextually bound, so the results are generated for me as the user of the transaction in this context.  

It’s quite easy for me to create the analytic I want for my purpose with this guided process.  First, I’ll give my new analytic a name and determine how I want the data visualized.

In this case I’ve chosen a chart to render the output.  Next I’ll choose the data that I want.  Notice that the Manager name is already determined.  (Betty is the person I logged in as.) 

I can select or deselect fields until I have what I want.  

When I save the Analytic, it is presented to me using the visualization I chose earlier; in this case, a chart.

Note that I have row and column data, and other data is represented as facets.  Facets can be selected to filter the results, or I can drag data items to or from the Row and Column areas to slice and dice the analytic any way I wish.  It’s flexible and powerful.

In this case, I’ve dragged the Department field to the Row area, which changes the Analytic and gives me a new perspective on the information.

Now when I save the analytic, it appears in the related information area so I can run this any time I’m in this transaction.  The data is fresh and secure. 

If I’ve created an analytic that I want to use frequently and have at hand regardless of context, I can easily create a tile for it and add it to any of my home pages. 

Now the analytic appears as a tile, and I can run it easily any time from my home page. 

As you can see, creating simplified analytics is easy and fast for end users.  It is wonderfully useful, because users can get the information they need when they need it, without having to seek assitance from IT or reporting experts.  

Our applications teams will be delivering some simplified analytics with our products, so you will see some when you simply take upcoming PeopleSoft Update Images.  However, simplified analytics are easy to create, so you can provide them to your users wherever you see a need.  

Look for more posts on this topic.  We’ll also cover how to publish reports to make them available to other users, and we’ll show you how to set up simplified analytics to make them available for your end users. 

Virtual Technology Summit Presentation PDF's Now Available

OTN TechBlog - Mon, 2015-12-21 12:24

It's almost time for the OTN team to take a break for the Holidays, but not just yet.  We were busy elves last week and posted all the presentations for the videos in each VTS Replay Group as an additional resource.  Check them out and the highlighted sessions for this week below!

Automate Your Oracle Solaris 11 and Linux Deployments with Puppet - By Bjoern Rost 

Puppet is a popular open source configuration management tool that is used by many organizations to automate the setup and configuration of servers and virtual machines. Solaris 11.2 includes native support for puppet and extends the resources that can be managed to Solaris specific things like zones and ZFS. This presentation will give system administrators that are new to puppet an introduction and a way to get started with automating the configuration of Oracle Linux and Oracle Solaris systems, talk about how puppet integrates with version control and other projects and look at the Solaris specific resource types.

Mobile by Design: Developing with the Oracle Mobile Cloud Service  - By Joe Huang and Geoff Poremba

In this video you will learn how to use Oracle Mobile Cloud Service to enable your enterprise applications and existing services for simpler, faster, more transparent and more secure mobile access. You'll learn how to build a robust Mobile Backend as a Service (MBaaS) using Oracle Mobile Suite, and how to customize that MBaaS using one of many thousands of available Node.js modules.

Using Oracle SQL Developer and Oracle REST Data Services to Enable the Oracle Cloud - By Jeff Smith, Principal Product Director, Oracle SQL Developer Product

This session presents the latest capabilities in Oracle's popular SQL Developer and ORDS tools for database application development and deployment in the cloud. See how to clone and migrate data between Oracle cloud and on-premise implementations and other powerful techniques for developing applications for the cloud, in the cloud.

Connecting Devices to the Cloud: Healthcare for the Elderly - By Gerrit Grunwald  
 Taking healthcare as an example, this session will demonstrate using a mobile phone and a smart watch in combination with a Java based gateway, iBeacons and other sensors to monitor the activity of elderly people. With the help of an IoT cloud service this data can be analyzed to detect situations that might be critical (illness, bone fracture etc.). If such a case was detected, the cloud service can trigger enterprise applications. With this approach it might be possible to connect such a system to existing healthcare applications.This session will give you an idea how you can combine existing technologies to do something useful and help elderly people in case of an emergency.

Season's Greetings!

WebCenter Team - Mon, 2015-12-21 12:10

We want to wish you Season's Greetings and thank you for your continued partnership! We'll be back in 2016 with new assets, programs and education on Oracle WebCenter, BPM and Oracle Cloud Solutions. 

Happy Holidays! - The WebCenter Team

OGh DBA / SQL Celebration Day 2016

Marco Gralike - Mon, 2015-12-21 08:14
During next year’s annual OGh DBA day (the 7th of June 2016), additional presentations will…

node-oracledb 1.5.0 is on NPM (Node.js add-on for Oracle Database)

Christopher Jones - Mon, 2015-12-21 06:56
Node-oracledb 1.5.0, the Node.js add-on for Oracle Database, is on NPM.

A number of bugs have been squashed in this release.

  • We now treat Oracle Database 'Success With Info' warnings as success.

    Thanks to Francisco Trevino for his pull request. After investigating and discussing, we decided for 1.5 to pick up the straightforward fix proposed. In a future release we will revisit allowing these warnings to be caught and handled.

  • Extended rollback-on-connection-release with 11g Oracle Clients to occur for all non-query executions.

    The natural behavior of OCI is to commit when a connection is released. This is the opposite of node-oracledb, which therefore has to determine whether to rollback or not.

    When node-oracledb is linked with 11g client a heuristic is used to guess whether to rollback when a connection is released. This heuristic needed to be changed to cover more cases. The result is that there will be sometimes be some unnecessary rollbacks issued.

    The bug didn't occur node-oracledb was linked with 12c client libraries due to this code that uses a new API available in 12c to indicate whether a connection has a transaction open.

    Bottom line: use Oracle 12c client libraries if possible to get optimal behavior.

  • Updated OS X install instructions to work on El Capitan.

    The instructions now use symbolic links in /usr/local/lib for the Oracle client libraries. This removes the need to set DYLD_LIBRARY_PATH, which has some restrictions on it introduced in El Capitan.

  • Display an error and prevent connection release while database calls are in progress.

    This was a bigger transaction, that 'fixed' a number of seemingly random crashes which were occurring when applications released connections that were in fact still in use. Node-oracledb will now print an error and not release the connection, thus preventing a crash. Note that since the release fails, connection pools can max out in this scenario. If you experience the errors NJS-030, NJS-031 or NJS-032 you should fix your app so the connection release occurs after all database operations have concluded.

    The new messages are:

    "NJS-030: Connection cannot be released because Lob operations are in  progress"
    "NJS-031: Connection cannot be released because ResultSet operations are in progress"
    "NJS-032: Connection cannot be released because a database call is in progress"
    
  • Fixed an intermittent crash while selecting data from CLOB column.

    We had an incorrect buffer expansion ratio in use. This has been fixed.

  • Fixed crash when trying to set invalid values for connection properties.

    Enough said.

Work for node-oracledb 1.6 will begin. We are trying to reproduce and understand some reported LOB issues and memory growth reports. We're also looking forward to evaluating a big pull request from Dieter Oberkofler that adds PL/SQL bind support.

Issues and questions about node-oracledb can be posted on GitHub. We value your input to help prioritize work on the add-on. Drop us a line!

node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Using Linux Control Groups to Constrain Process Memory

Rittman Mead Consulting - Mon, 2015-12-21 03:00

Linux Control Groups (cgroups) are a nifty way to limit the amount of resource, such as CPU, memory, or IO throughput, that a process or group of processes may use. Frits Hoogland wrote a great blog demonstrating how to use it to constrain the I/O a particular process could use, and was the inspiration for this one. I have been doing some digging into the performance characteristics of OBIEE in certain conditions, including how it behaves under memory pressure. I’ll write more about that in a future blog, but wanted to write this short blog to demonstrate how cgroups can be used to constrain the memory that a given Linux process can be allocated.

This was done on Amazon EC2 running an image imported originally from Oracle’s OBIEE SampleApp, built on Oracle Linux 6.5.

$ uname -a  
Linux demo.us.oracle.com 2.6.32-431.5.1.el6.x86_64 #1 SMP Tue Feb 11 11:09:04 PST 2014 x86_64 x86_64 x86_64 GNU/Linux

First off, install the necessary package in order to use them, and start the service. Throughout this blog where I quote shell commands those prefixed with # are run as root and $ as non-root:

# yum install libcgroup  
# service cgconfig start

Create a cgroup (I’m shamelessly ripping off Frits’ code here, hence the same cgroup name ;-) ):

# cgcreate -g memory:/myGroup

You can use cgget to view the current limits, usage, & high watermarks of the cgroup:

# cgget -g memory:/myGroup|grep bytes  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 0  
memory.memsw.usage_in_bytes: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 0  
memory.usage_in_bytes: 0

For more information about the field meaning see the doc here.

To test out the cgroup ability to limit memory used by a process we’re going to use the tool stress, which can be used to generate CPU, memory, or IO load on a server. It’s great for testing what happens to a server under resource pressure, and also for testing memory allocation capabilities of a process which is what we’re using it for here.

We’re going to configure cgroups to add stress to the myGroup group whenever it runs

$ cat /etc/cgrules.conf  
*:stress memory myGroup

[Re-]start the cg rules engine service:

# service cgred restart

Now we’ll use the watch command to re-issue the cgget command every second enabling us to watch cgroup’s metrics in realtime:

# watch --interval 1 cgget -g memory:/myGroup  
/myGroup:  
memory.memsw.failcnt: 0  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 0  
memory.memsw.usage_in_bytes: 0  
memory.oom_control: oom_kill_disable 0  
        under_oom 0  
memory.move_charge_at_immigrate: 0  
memory.swappiness: 60  
memory.use_hierarchy: 0  
memory.stat: cache 0  
        rss 0  
        mapped_file 0  
        pgpgin 0  
        pgpgout 0  
        swap 0  
        inactive_anon 0  
        active_anon 0  
        inactive_file 0  
        active_file 0  
        unevictable 0  
        hierarchical_memory_limit 9223372036854775807  
        hierarchical_memsw_limit 9223372036854775807  
        total_cache 0  
        total_rss 0  
        total_mapped_file 0  
        total_pgpgin 0  
        total_pgpgout 0  
        total_swap 0  
        total_inactive_anon 0  
        total_active_anon 0  
        total_inactive_file 0  
        total_active_file 0  
        total_unevictable 0  
memory.failcnt: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 0  
memory.usage_in_bytes: 0

In a separate terminal (or even better, use screen!) run stress, telling it to grab 150MB of memory:

$ stress --vm-bytes 150M --vm-keep -m 1

Review the cgroup, and note that the usage fields have increased:

/myGroup:  
memory.memsw.failcnt: 0  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.memsw.max_usage_in_bytes: 157548544  
memory.memsw.usage_in_bytes: 157548544  
memory.oom_control: oom_kill_disable 0  
        under_oom 0  
memory.move_charge_at_immigrate: 0  
memory.swappiness: 60  
memory.use_hierarchy: 0  
memory.stat: cache 0  
        rss 157343744  
        mapped_file 0  
        pgpgin 38414  
        pgpgout 0  
        swap 0  
        inactive_anon 0  
        active_anon 157343744  
        inactive_file 0  
        active_file 0  
        unevictable 0  
        hierarchical_memory_limit 9223372036854775807  
        hierarchical_memsw_limit 9223372036854775807  
        total_cache 0  
        total_rss 157343744  
        total_mapped_file 0  
        total_pgpgin 38414  
        total_pgpgout 0  
        total_swap 0  
        total_inactive_anon 0  
        total_active_anon 157343744  
        total_inactive_file 0  
        total_active_file 0  
        total_unevictable 0  
memory.failcnt: 0  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 9223372036854775807  
memory.max_usage_in_bytes: 157548544  
memory.usage_in_bytes: 157548544

Both memory.memsw.usage_in_bytes and memory.usage_in_bytes are 157548544 = 150.25MB

Having a look at the process stats for stress shows us:

$ ps -ef|grep stress  
oracle   15296  9023  0 11:57 pts/12   00:00:00 stress --vm-bytes 150M --vm-keep -m 1  
oracle   15297 15296 96 11:57 pts/12   00:06:23 stress --vm-bytes 150M --vm-keep -m 1  
oracle   20365 29403  0 12:04 pts/10   00:00:00 grep stress

$ cat /proc/15297/status

Name:   stress  
State:  R (running)  
[...]  
VmPeak:   160124 kB  
VmSize:   160124 kB  
VmLck:         0 kB  
VmHWM:    153860 kB  
VmRSS:    153860 kB  
VmData:   153652 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       328 kB  
VmSwap:        0 kB  
[...]

The man page for proc gives us more information about these fields, but of particular note are:

  • VmSize: Virtual memory size.
  • VmRSS: Resident set size.
  • VmSwap: Swapped-out virtual memory size by anonymous private pages

Our stress process has a VmSize of 156MB, VmRSS of 150MB, and zero swap.

Kill the stress process, and set a memory limit of 100MB for any process in this cgroup:

# cgset -r memory.limit_in_bytes=100m myGroup

Run cgset and you should see the see new limit. Note that at this stage we’re just setting memory.limit_in_bytes and leaving memory.memsw.limit_in_bytes unchanged.

# cgget -g memory:/myGroup|grep limit|grep bytes  
memory.memsw.limit_in_bytes: 9223372036854775807  
memory.soft_limit_in_bytes: 9223372036854775807  
memory.limit_in_bytes: 104857600

Let’s see what happens when we try to allocate the memory, observing the cgroup and process Virtual Memory process information at each point:

  • 15MB:

    $ stress --vm-bytes 15M --vm-keep -m 1  
    stress: info: [31942] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd
    
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 15990784  
    memory.usage_in_bytes: 15990784
    
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep VmVmPeak:    21884 kB  
    VmSize:    21884 kB  
    VmLck:         0 kB  
    VmHWM:     15616 kB  
    VmRSS:     15616 kB  
    VmData:    15412 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:        60 kB  
    VmSwap:        0 kB

  • 50MB:

    $ stress --vm-bytes 50M --vm-keep -m 1  
    stress: info: [32419] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd
    
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 52748288  
    memory.usage_in_bytes: 52748288     
    
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
    VmPeak:    57724 kB  
    VmSize:    57724 kB  
    VmLck:         0 kB  
    VmHWM:     51456 kB  
    VmRSS:     51456 kB  
    VmData:    51252 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:       128 kB  
    VmSwap:        0 kB

  • 100MB:

    $ stress --vm-bytes 100M --vm-keep -m 1  
    stress: info: [20379] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd        
    # cgget -g memory:/myGroup|grep usage|grep -v max  
    memory.memsw.usage_in_bytes: 105197568  
    memory.usage_in_bytes: 104738816
    
    $ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
    VmPeak:   108924 kB  
    VmSize:   108924 kB  
    VmLck:         0 kB  
    VmHWM:    102588 kB  
    VmRSS:    101448 kB  
    VmData:   102452 kB  
    VmStk:        92 kB  
    VmExe:        20 kB  
    VmLib:      2232 kB  
    VmPTE:       232 kB  
    VmSwap:     1212 kB

Note that VmSwap has now gone above zero, despite the machine having plenty of usable memory:

# vmstat -s  
     16330912  total memory  
     14849864  used memory  
     10583040  active memory  
      3410892  inactive memory  
      1481048  free memory  
       149416  buffer memory  
      8204108  swap cache  
      6143992  total swap  
      1212184  used swap  
      4931808  free swap

So it looks like the memory cap has kicked in and the stress process is being forced to get the additional memory that it needs from swap.

Let’s tighten the screw a bit further:

$ stress --vm-bytes 200M --vm-keep -m 1  
stress: info: [21945] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd

The process is now using 100MB of swap (since we’ve asked it to grab 200MB but cgroup is constraining it to 100MB real):

$ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
VmPeak:   211324 kB  
VmSize:   211324 kB  
VmLck:         0 kB  
VmHWM:    102616 kB  
VmRSS:    102600 kB  
VmData:   204852 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       432 kB  
VmSwap:   102460 kB

The cgget command confirms that we’re using swap, as the memsw value shows:

# cgget -g memory:/myGroup|grep usage|grep -v max  
memory.memsw.usage_in_bytes: 209788928  
memory.usage_in_bytes: 104759296

So now what happens if we curtail the use of all memory, including swap? To do this we’ll set the memory.memsw.limit_in_bytes parameter. Note that running cgset whilst a task under the cgroup is executing seems to get ignored if it is below that currently in use (per the usage_in_bytes field). If it is above this then the change is instantaneous:

  • Current state

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 9223372036854775807  
    memory.memsw.max_usage_in_bytes: 209915904  
    memory.memsw.usage_in_bytes: 209784832  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104775680

  • Set the limit below what is currently in use (150m limit vs 200m in use)

    # cgset -r memory.memsw.limit_in_bytes=150m myGroup

  • Check the limit – it remains unchanged

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 9223372036854775807  
    memory.memsw.max_usage_in_bytes: 209993728  
    memory.memsw.usage_in_bytes: 209784832  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104751104

  • Set the limit above what is currently in use (250m limit vs 200m in use)

    # cgset -r memory.memsw.limit_in_bytes=250m myGroup

  • Check the limit – it’s taken effect

    # cgget -g memory:/myGroup|grep bytes  
    memory.memsw.limit_in_bytes: 262144000  
    memory.memsw.max_usage_in_bytes: 210006016  
    memory.memsw.usage_in_bytes: 209846272  
    memory.soft_limit_in_bytes: 9223372036854775807  
    memory.limit_in_bytes: 104857600  
    memory.max_usage_in_bytes: 104857600  
    memory.usage_in_bytes: 104816640

So now we’ve got limits in place of 100MB real memory and 250MB total (real + swap). What happens when we test that out?

$ stress --vm-bytes 245M --vm-keep -m 1  
stress: info: [25927] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd

The process is using 245MB total (VmData), of which 95MB is resident (VmRSS) and 150MB is swapped out (VmSwap)

$ cat /proc/$(pgrep stress|tail -n1)/status|grep Vm  
VmPeak:   257404 kB  
VmSize:   257404 kB  
VmLck:         0 kB  
VmHWM:    102548 kB  
VmRSS:     97280 kB  
VmData:   250932 kB  
VmStk:        92 kB  
VmExe:        20 kB  
VmLib:      2232 kB  
VmPTE:       520 kB  
VmSwap:   153860 kB

The cgroup stats reflect this:

# cgget -g memory:/myGroup|grep bytes  
memory.memsw.limit_in_bytes: 262144000  
memory.memsw.max_usage_in_bytes: 257159168  
memory.memsw.usage_in_bytes: 257007616  
[...]  
memory.limit_in_bytes: 104857600  
memory.max_usage_in_bytes: 104857600  
memory.usage_in_bytes: 104849408

If we try to go above this absolute limit (memory.memsw.max_usage_in_bytes) then the cgroup kicks in a stops the process getting the memory, which in turn causes stress to fail:

$ stress --vm-bytes 250M --vm-keep -m 1  
stress: info: [27356] dispatching hogs: 0 cpu, 0 io, 1 vm, 0 hdd  
stress: FAIL: [27356] (415) <-- worker 27357 got signal 9  
stress: WARN: [27356] (417) now reaping child worker processes  
stress: FAIL: [27356] (451) failed run completed in 3s

This gives you an indication of how careful you need to be using this type of low-level process control. Most tools will not be happy if they are starved of resource, including memory, and may well behave in unstable ways.

Thanks to Frits Hoogland for reading a draft of this post and providing valuable feedback.

The post Using Linux Control Groups to Constrain Process Memory appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Parallel Plans

Jonathan Lewis - Mon, 2015-12-21 02:40

This article was prompted by a pair of articles by Yasin Baskan of Oracle Corporation: PX Server Sets etc. and Multiple Parallelizers, plus a little extra prompting from a mistake that I made when reading the second of those two articles. The fact that I made a mistake is significant because, without it, I wouldn’t have created a model to check Yasin’s description of the parallel activity.

I want to examine the following query to find out the order of activity:


select
        distinct t1.n1
from
        t1
where 
        t1.n1 >= 500
and     t1.n2 > (select avg(t2.n2) from t2)
and     t1.n3 > (select avg(t3.n3) from t3)
;

I’m going to be using 12.1.0.2 for this demonstration (lots of changes appear in parallel execution as you move from 11g to 12c, so the version is most significant), with all default settings for the parallel-related parameters.

To get things started, here’s the code to generate the data that I used, and the serial execution plan:


create table t1 nologging as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e4
)
select
        rownum                  id,
        mod(rownum,1000)        n1,
        rownum                  n2,
        rownum                  n3,
        lpad(rownum,10)         small_vc,
        rpad('x',100,'x')       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 2e4
;

create table t2 nologging as
with generator as ( 
        select  --+ materialize
                rownum id
        from dual 
        connect by 
                level <= 1e4
)
select
        rownum                  n1,
        rownum                  n2,
        rownum                  n3,
        lpad(rownum,10)         small_vc,
        rpad('x',100,'x')       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 2e4
;

create table t3 nologging as
with generator as ( 
        select  --+ materialize
                rownum id
        from dual 
        connect by 
                level <= 1e4
)
select
        rownum                  n1,
        rownum                  n2,
        rownum                  n3,
        lpad(rownum,10)         small_vc,
        rpad('x',100,'x')       padding
from
        generator       v1,
        generator       v2
where
        rownum <= 2e4 ;

-- don't need to gather stats as they are generated on CTAS in 12c.
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |   150 (100)|          |
|   1 |  HASH UNIQUE         |      |    24 |   336 |   150   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | T1   |    25 |   350 |    51   (4)| 00:00:01 |
|   3 |    SORT AGGREGATE    |      |     1 |     5 |            |          |
|   4 |     TABLE ACCESS FULL| T2   | 20000 |    97K|    49   (3)| 00:00:01 |
|   5 |    SORT AGGREGATE    |      |     1 |     5 |            |          |
|   6 |     TABLE ACCESS FULL| T3   | 20000 |    97K|    49   (3)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1">=500 AND "T1"."N2"> AND "T1"."N3">))

I’ve pulled the execution plan from memory using dbms_xplan.display_cursor(), which is why the predicate section shows the odd comparison of “t2.n2 > {nothing there}” and “t3.n3 > {nothing there}”. The predicate section produced by a simple call to explain plan would help us to fill in the details:


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N1">=500 AND "T1"."N2"> (SELECT AVG("T2"."N2") FROM
              "T2" "T2") AND "T1"."N3"> (SELECT AVG("T3"."N3") FROM "T3" "T3"))

We can infer from the predicate section that Oracle executes the t2 subquery first and the t3 subquery second; but there is a slight oddity in the shape of the execution plan if we apply the “first child first / recursive descent” approach to reading the plan. It looks as if operations 3 and 5 are children of line 2 and should be run before child 2. This is the consequence of “subquery pushing”. In fact the subqueries are both filter subqueries and, in principle (and ignoring scalar subquery caching for the moment), they will be run once for each row produced from the tablescan of operation 2.

The activity is as follows:

  • start scanning table t1
  • when the first row appears where n1 >= 500 run the subquery against t2 to derive the average of t2.n2 (10000.5) and check to see if the n2 value of the current t1 row exceeds that value (it doesn’t);
  • continue scanning t1 checking for rows where n1 >= 500 and n2 >= 10000.5 (no need to rerun the subquery thanks to scalar subquery caching),
  • on finding the first row matching both predicates run the subquery against t3 to derive the average of t3.n3 (10000.5) and check to see if the n3 value of the current t1 row exceeds that value.
  • continue the scan of t1 checking for rows where n1 >= 500 and n2 >= 10000.5 and n3 >= 10000.5 (again scalar subquery caching means no further executions of either subquery).

We can add weight to the argument that this is the order of activity by flushing the buffer cache and enabled the extended trace before running the query.  Here’s a section of a trace file that demonstrates the point – with no edits beyond the insertion of a few blank lines:


PARSE #140345652175312:c=1000,e=1813,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=115251305,tim=134216063372
EXEC #140345652175312:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=115251305,tim=134216063544
WAIT #140345652175312: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=134216063569
WAIT #140345652175312: nam='db file sequential read' ela= 9 file#=5 block#=131 blocks=1 obj#=133974 tim=134216063627
WAIT #140345652175312: nam='db file scattered read' ela= 315 file#=5 block#=132 blocks=112 obj#=133974 tim=134216064187

WAIT #140345652175312: nam='db file sequential read' ela= 9 file#=5 block#=515 blocks=1 obj#=133975 tim=134216064438
WAIT #140345652175312: nam='db file scattered read' ela= 115 file#=5 block#=516 blocks=112 obj#=133975 tim=134216064732
WAIT #140345652175312: nam='db file scattered read' ela= 18 file#=5 block#=628 blocks=12 obj#=133975 tim=134216066046
WAIT #140345652175312: nam='db file scattered read' ela= 123 file#=5 block#=642 blocks=112 obj#=133975 tim=134216066548
WAIT #140345652175312: nam='db file scattered read' ela= 20 file#=5 block#=754 blocks=14 obj#=133975 tim=134216067760
WAIT #140345652175312: nam='db file scattered read' ela= 124 file#=5 block#=770 blocks=112 obj#=133975 tim=134216068153
WAIT #140345652175312: nam='db file scattered read' ela= 8 file#=5 block#=882 blocks=2 obj#=133975 tim=134216069677

WAIT #140345652175312: nam='db file scattered read' ela= 18 file#=5 block#=244 blocks=12 obj#=133974 tim=134216070049
WAIT #140345652175312: nam='db file scattered read' ela= 127 file#=5 block#=258 blocks=112 obj#=133974 tim=134216070382

WAIT #140345652175312: nam='db file sequential read' ela= 6 file#=5 block#=899 blocks=1 obj#=133976 tim=134216070812
WAIT #140345652175312: nam='db file scattered read' ela= 41 file#=5 block#=900 blocks=112 obj#=133976 tim=134216071028
WAIT #140345652175312: nam='db file scattered read' ela= 42 file#=5 block#=1012 blocks=12 obj#=133976 tim=134216072956
WAIT #140345652175312: nam='db file scattered read' ela= 215 file#=5 block#=1026 blocks=112 obj#=133976 tim=134216073642
WAIT #140345652175312: nam='db file scattered read' ela= 29 file#=5 block#=1138 blocks=14 obj#=133976 tim=134216076334
WAIT #140345652175312: nam='db file scattered read' ela= 154 file#=5 block#=1154 blocks=112 obj#=133976 tim=134216076825
WAIT #140345652175312: nam='db file scattered read' ela= 14 file#=5 block#=1266 blocks=2 obj#=133976 tim=134216081673

WAIT #140345652175312: nam='db file scattered read' ela= 24 file#=5 block#=370 blocks=14 obj#=133974 tim=134216082815
WAIT #140345652175312: nam='db file scattered read' ela= 144 file#=5 block#=386 blocks=112 obj#=133974 tim=134216083232
WAIT #140345652175312: nam='db file scattered read' ela= 20 file#=5 block#=498 blocks=12 obj#=133974 tim=134216084494

FETCH #140345652175312:c=16998,e=21096,p=1105,cr=1111,cu=0,mis=0,r=1,dep=0,og=1,plh=115251305,tim=134216084683

Object 133974 is table t1, object 133975 is table t2, and object 133976 is table t3.

As you can see, we start by scanning t1, then we scan t2 once, then we scan more of t1, then we scan t3 once, then we finish off scanning t1.

We could chase the detail a little further, of course – we could check the session statistics to make sure that we haven’t been using the buffer cache to scan t2 and t3 repeatedly, or we could enable rowsource execution statistics to report the number of times each operation started, or we could check the STAT# lines dumped a little later in the trace files and infer that the t2 and t3 tablescans happened just once each.

At this point I’ll mention my mistake: when I originally read Yasin’s version of the parallel equivalent of this plan my first thought was that the subquery sections of the plan operate from the bottom upwards (i.e. another one of those examples where “first child first” doesn’t apply) whereas Yasin described them as running top down. I was wrong – I was thinking of the case where the optimizer analyses the query from the bottom up, but it still produces a plan that then runs from the top down – but I didn’t realize my mistake until after I’d been poking around with the parallel version of the query and had made a surprising discovery.

Parallel Execution

So what happens when we manipulate this query into parallelism ? To make it a little easier to see some of the details I’ve added hints to each query block to use a different degree of parallelism; and then, because it all happens so fast, I’ve introduced a function call to slow down the progress of the query. Here’s the function I’ve created (you’ll find it elsewhere on blog) and the final query I started testing with:


create or replace function wait_row (
        i_secs  number,
        i_return        number
) return number
parallel_enable
is
begin
        dbms_lock.sleep(i_secs);
        return i_return;
end;
/


select
        /*+ parallel(t1,5) tracking */ 
        distinct t1.n1
from
        t1
where   
        wait_row(0.01,t1.n2) > (select /*+ parallel(t2,3) */ avg(wait_row(0.01,t2.n2)) from t2)
and     wait_row(0.01,t1.n3) > (select /*+ parallel(t3,2) */ avg(wait_row(0.01,t3.n3)) from t3)
and     t1.n1 >= 1000
--      and     wait_row(0.01, t1.n1) >= 500
;

The wait_row() function simply waits for the number of seconds given in the first parameter then returns the (numeric) value of the second parameter. It calls the dbms_lock.sleep() function which has a granularity of 1/100th of a second, and since I’m planning to use it in a parallel query I’ve included the parallel_enable declaration.

Since the function has not been declared deterministic, or assigned to the pl/sql result cache, and since there are 20,000 rows in the t2 table the time to run the t2 subquery will be roughly 20,000 * 0.01 seconds, which (ignoring parallelism) would be about 200 seconds. I’ve actually hinted DOP = 3 for that subquery so I hope it will complete in about 70 seconds, and the subquery against t3 with hinted DOP = 2 should complete in about 100 seconds.

You’ll notice I have two possible predicates against t1 – the one shown uncommented here doesn’t use call the wait_row() function, the other does. Here’s an interesting point, though, about the example that doesn’t call wait_row() – the simple predicate is going to eliminate ALL the data from t1, there are no rows where n1 >= 1000 and that’s an important point to remember.

Here’s the execution plan:


------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |    57 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ30001 |     1 |    14 |    57   (4)| 00:00:01 |  Q3,01 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE                 |          |     1 |    14 |    57   (4)| 00:00:01 |  Q3,01 | PCWP |            |
|   4 |     PX RECEIVE                 |          |     1 |    14 |    57   (4)| 00:00:01 |  Q3,01 | PCWP |            |
|   5 |      PX SEND HASH              | :TQ30000 |     1 |    14 |    57   (4)| 00:00:01 |  Q3,00 | P->P | HASH       |
|   6 |       HASH UNIQUE              |          |     1 |    14 |    57   (4)| 00:00:01 |  Q3,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR       |          |     1 |    14 |    11   (0)| 00:00:01 |  Q3,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL      | T1       |     1 |    14 |    11   (0)| 00:00:01 |  Q3,00 | PCWP |            |
|   9 |          SORT AGGREGATE        |          |     1 |     5 |            |          |  Q3,00 | PCWP |            |
|  10 |           PX COORDINATOR       |          |       |       |            |          |        |      |            |
|  11 |            PX SEND QC (RANDOM) | :TQ10000 |     1 |     5 |            |          |  Q1,00 | P->S | QC (RAND)  |
|  12 |             SORT AGGREGATE     |          |     1 |     5 |            |          |  Q1,00 | PCWP |            |
|  13 |              PX BLOCK ITERATOR |          | 20000 |    97K|    18   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|* 14 |               TABLE ACCESS FULL| T2       | 20000 |    97K|    18   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  15 |          SORT AGGREGATE        |          |     1 |     5 |            |          |        |      |            |
|  16 |           PX COORDINATOR       |          |       |       |            |          |        |      |            |
|  17 |            PX SEND QC (RANDOM) | :TQ20000 |     1 |     5 |            |          |  Q2,00 | P->S | QC (RAND)  |
|  18 |             SORT AGGREGATE     |          |     1 |     5 |            |          |  Q2,00 | PCWP |            |
|  19 |              PX BLOCK ITERATOR |          | 20000 |    97K|    27   (0)| 00:00:01 |  Q2,00 | PCWC |            |
|* 20 |               TABLE ACCESS FULL| T3       | 20000 |    97K|    27   (0)| 00:00:01 |  Q2,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access(:Z>=:Z AND :Z<=:Z)
       filter(("T1"."N1">=1000 AND "WAIT_ROW"(.01,"T1"."N2")> AND "WAIT_ROW"(.01,"T1"."N3")>))
  14 - access(:Z>=:Z AND :Z<=:Z) 20 - access(:Z>=:Z AND :Z<=:Z)

The odd “:Z>=:Z AND :Z <=:Z” predicate is an artefact of rowid-range scans in parallel tablescans, and we’ve seen the “disappearing subquery” problem with predicates pulled from memory so that’s ignorable. The interesting point is that the “T1.N1 >= 1000” predicate will eliminate all the data from t1 very quickly – so why did it take roughly 3 minutes and 30 seconds to run the query ?

Quick Answer – Oracle ran the two subqueries before it started the t1 tablescan !

Slow Answer – I need to start by filling in a lot of background information.

Notice how we have multiple DFO Trees in this execution plan. In the TQ column these are captured as (Q1,xx) (Q2,xx) and (Q3,xx).  In previous blogs I’ve explained how you can follow the order of activity by following the table queues (effectively the “xx” – also known as virtual tables, also known as Data Flow Operations) in order, but I haven’t explained how you deal with multiple DFO trees. It would be nice to think that DFO Trees implicitly told you the order of operation – unfortunately that’s not true (at least in current versions of Oracle), and this query demonstrates the point.

Table Queues, DFOs (data flow operations), and DFO Trees

The first of Yasin Baskan’s two articles describes the terminology to use when describing the movement of data through a parallel execution plan, but for the sake of familiarity I’ll walk through the plan above using the appropriate terms.  (Note: once upon a time the term “DFO tree” didn’t exist and the term DFO was used to describe what is now called a DFO tree, and a number of articles I’ve written in the past echo this out-dated usage).

Lines 1 – 8 show a DFO tree consisting of two DFOs – the tree is labelled as Q3. The first DFO in the tree (DFO 0) is described in lines 5 – 8 where one set of parallel execution slaves scans real table t1 and sends a data set to the other set parallel execution slaves by writing into a virtual table (:TQ30000); the second DFO in the tree (DFO 1) is described in lines 1 – 4 where a set of parallel execution slaves reads from virtual table :TQ30000, does some work and sends a data set to the query co-ordinator by writing to virtual table :TQ30001.

Lines 9 – 14 show a second DFO tree consisting of a single DFO – this tree is labelled Q1;

Lines 15 – 20 show us a third DFO tree, labelled Q2, also consisting of a single DFO.

Essentially (as described by Yasin Baskan) the focal point of a single DFO (data flow operation) is a PX SEND, and the top of a DFO tree is a PX Coordinator.

The key to performance problems and interpreting tricky parallel execution plans is the fact that in the extreme case every DFO tree may operate at a different degree of parallelism, and all the DFO trees may be operating concurrently – an unlucky execution plan may acquire far more parallel execution slaves than you expect.

So what’s the order of execution in this query, how do we find out, how many parallel query slaves will we start (and stop) as the query runs, and what’s the maximum number of parallel query slaves that we will be holding concurrently ? There are lots of places you could look:

  • v$pq_tqstat might give you some idea of processes used and data moved – after you’ve run the query, but it’s a bit broken for multiple DFO trees.
  • v$px_sesstat will give you some information about the work done by currently live slave processes if you can query it while your query is still running (“set pause on” may help).
  • v$px_session joined to v$session_event will give you some information about the time lost to waits for currently live slave processes if you can query them while your query is still running (it would be nice if Oracle were to introduce a “v$px_sesevent”)
  • The SQL Monitor screen in OEM (or the dbms_sql_tune.report_sql_monitor() procedure is fantastic (but a little broken) if you’ve paid the licences for the diagnostic and performance packs.

Here’s part of the text output from a call to dbms_sql_tune.report_sql_monitor() for a slightly different query – one that doesn’t include any predicate at all on the t1 table:


SQL Plan Monitoring Details (Plan Hash Value=3828285674)
==================================================================================================================================================
| Id |            Operation             |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                  |          | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
==================================================================================================================================================
|  0 | SELECT STATEMENT                 |          |         |      |         1 |   +256 |    11 |     1000 |       |          |                 |
|  1 |   PX COORDINATOR                 |          |         |      |         1 |   +256 |    11 |     1000 |       |          |                 |
|  2 |    PX SEND QC (RANDOM)           | :TQ30001 |      49 |   60 |         1 |   +256 |     5 |     1000 |       |          |                 |
|  3 |     HASH UNIQUE                  |          |      49 |   60 |         1 |   +256 |     5 |     1000 |    7M |          |                 |
|  4 |      PX RECEIVE                  |          |      49 |   60 |         1 |   +256 |     5 |     4506 |       |          |                 |
|  5 |       PX SEND HASH               | :TQ30000 |      49 |   60 |         1 |   +256 |     5 |     4506 |       |          |                 |
|  6 |        HASH UNIQUE               |          |      49 |   60 |        68 |   +189 |     5 |     4506 |    7M |          |                 |
|  7 |         PX BLOCK ITERATOR        |          |      50 |   14 |        68 |   +189 |     5 |    10000 |       |          |                 |
|  8 |          TABLE ACCESS FULL       | T1       |      50 |   14 |        68 |   +189 |    63 |    10000 |       |          |                 |
|  9 |           SORT AGGREGATE         |          |       1 |      |         1 |   +187 |     1 |        1 |       |          |                 |
| 10 |            PX COORDINATOR        |          |         |      |         1 |   +187 |     4 |        3 |       |          |                 |
| 11 |             PX SEND QC (RANDOM)  | :TQ10000 |       1 |      |         3 |   +185 |     3 |        3 |       |          |                 |
| 12 |              SORT AGGREGATE      |          |       1 |      |        74 |   +114 |     3 |        3 |       |          |                 |
| 13 |               PX BLOCK ITERATOR  |          |   20000 |   18 |        74 |   +114 |     3 |    20000 |       |          |                 |
| 14 |                TABLE ACCESS FULL | T2       |   20000 |   18 |        74 |   +114 |    37 |    20000 |       |          |                 |
| 15 |           SORT AGGREGATE         |          |       1 |      |         1 |   +118 |     1 |        1 |       |          |                 |
| 16 |            PX COORDINATOR        |          |         |      |         1 |   +118 |     3 |        2 |       |          |                 |
| 17 |             PX SEND QC (RANDOM)  | :TQ20000 |       1 |      |         2 |   +111 |     2 |        2 |       |          |                 |
| 18 |              SORT AGGREGATE      |          |       1 |      |       111 |     +2 |     2 |        2 |       |          |                 |
| 19 |               PX BLOCK ITERATOR  |          |   20000 |   27 |       111 |     +2 |     2 |    20000 |       |          |                 |
| 20 |                TABLE ACCESS FULL | T3       |   20000 |   27 |       111 |     +2 |    26 |    20000 |       |          |                 |
==================================================================================================================================================

There’s a little inaccuracy in timings – in the order of the odd second or two – probably because of the granularity used, but the columns “Start Active” and “Time Active (s)” are, nevertheless, very revealing. The first one tells you when a line in the plan first did some work, the second one tells you how long that line has been active. As I walk through the plan remember that the timing granularity is fairly crude, so the numbers don’t quite add up as nicely as we might like.

If you check lines 17 – 20 you can see that DFO tree 2 (:TQ2xxxx) was the first thing to go active with its PX slaves starting at time t = 2 seconds and running for 111 seconds to do that scan and initial aggregation; the PX send line started at time t = 111 seconds and ran for 2 seconds to pass the data from the slaves to the coordinator.

There’s a little glitch (or hand waving moment) around lines15 and 16 where the PX Coordinator receives and aggregates data from the PX slaves – the co-ordinator seems to start doing this several seconds later than it should.

In lines 11 – 14 you can see that DFO tree 1 (:TQ1xxxx) was the second thing to go active with its PX slaves starting at time t = 114 (i.e. just after DFO tree 2 completes);  the full scan and initial aggregate ran for 74 seconds (taking us to time t = 188). The PX send started (a little early, this time) at t = 185 and ran for 3 seconds, with the co-ordinator starting at t = 187 and taking one second to receive and do the final aggregation.

In lines 2 – 8 we can see DFO tree 3 (:TQ3xxxx) starting up, but this DFO tree includes two DFOs. One set of slaves scans table t1 applying the predicates to filter the data then does the initial hash distinct before hash distributing the intermediate result to the next set of slaves that finish off the distinct aggregation.

When DFO :TQ30000 starts at t = 189, the previous DFO trees have completed and the PX coordinator has the results of the two subqueries which it passes to the slave set which can now scan and check for: “n1 >= 500 and  n2 > {derived constant} and n3 > {derived constant}”. The scan and initial aggregation takes 68 seconds completing at t = 255, and at t = 256 the results are distributed to the second set of PX slaves. The second set of slaves has virtually nothing to do and reports only 1 second of execution time before passing the data (DFO :TQ30001) to the query coordinator which does the final aggregation and report.

A key point to note is that this timing information tells us that (a) the subqueries both completed before the tablescan of t1 started, and – perhaps more importantly – that we cannot rely on the TQXnnnn numbers to tell us the order of operation of the DFO trees. In this example DFO tree 2 ran first, DFO tree 1 ran second, and DFO tree 3 ran third – some queries might happen to show the names and the order in synch, that would be a coincidence, not a deliberate design feature.

A little reminder, though – even though we cannot rely on the DFO tree names to tell us the ordering in which the DFO trees operate, when we look at the data flow operations inside the trees (i.e. the last few digits of a TQ name) the order of operation withing a given tree matches the sequence indicated by the TQ name.

Counting Slaves.

There’s more in the Monitor report that can help us understand the workload – and in this case I’ve got a lucky coincidence to help, and a little surprise to go with it.  For parallel queries the report includes a summary of parallel execution activity, and this is what I got from my example:

Parallel Execution Details (DOP=5 , Servers Allocated=15)
===========================================================================================================================
|      Name      | Type  | Group# | Server# | Elapsed |   Cpu   | Concurrency | PL/SQL  |  Other   | Buffer | Wait Events |
|                |       |        |         | Time(s) | Time(s) |  Waits(s)   | Time(s) | Waits(s) |  Gets  | (sample #)  |
===========================================================================================================================
| PX Coordinator | QC    |        |         |    0.03 |    0.02 |             |         |     0.02 |     15 |             |
| p00a           | Set 1 |      1 |       1 |    0.27 |    0.27 |             |    0.10 |          |    163 |             |
| p00b           | Set 1 |      1 |       2 |    0.25 |    0.25 |             |    0.09 |          |    156 |             |
| p00c           | Set 1 |      1 |       3 |    0.26 |    0.26 |             |    0.09 |          |    156 |             |

| p00a           | Set 1 |      2 |       1 |    0.43 |    0.43 |             |    0.15 |          |    221 |             |
| p00b           | Set 1 |      2 |       2 |    0.44 |    0.44 |             |    0.15 |          |    221 |             |

| p000           | Set 1 |      3 |       1 |    0.00 |    0.00 |             |         |          |        |             |
| p001           | Set 1 |      3 |       2 |    0.00 |    0.00 |             |         |          |        |             |
| p002           | Set 1 |      3 |       3 |    0.01 |    0.01 |             |         |          |        |             |
| p003           | Set 1 |      3 |       4 |    0.01 |    0.01 |             |         |          |        |             |
| p004           | Set 1 |      3 |       5 |    0.01 |    0.01 |             |         |          |        |             |
| p005           | Set 2 |      3 |       1 |    0.26 |    0.26 |             |    0.09 |          |    113 |             |
| p006           | Set 2 |      3 |       2 |    0.24 |    0.24 |        0.00 |    0.08 |          |    108 |             |
| p007           | Set 2 |      3 |       3 |    0.25 |    0.25 |             |    0.09 |          |    117 |             |
| p008           | Set 2 |      3 |       4 |    0.25 |    0.25 |             |    0.09 |          |    108 |             |
| p009           | Set 2 |      3 |       5 |    0.25 |    0.25 |        0.00 |    0.09 |          |    117 |             |
===========================================================================================================================

I’ve inserted two blank lines breaking the summary down into three separate groups, which you can identify by the heading “Group#”. In this table we see Group 1 has one slave set of three slaves – which corresponds to the tablescan of t2; Group 2 consists of one slave set of two slaves – which corresponds to the tablescan of t3; and Group 1 has two slave sets of 5 slaves each – which correspond to the tablescan and aggregation of t1. The Group numbers appear to align correctly with the DFO tree numbers.

Another detail that stands out from this list is that slaves p00a and p00b are used in the tablescan of t3, and in the tablescan of t2 (where they are joined by slave p00c). In this example Oracle has reused the slaves from one subquery to run the next. However we can also see that the slaves p000p009 that are used for the tablescan of t1 don’t have names that overlap with the slaves used to scan t2 and t3 – which tells use that we have some (in this case two) DFO trees running concurrently.

Another key point in this article is that not only does a DOP of N mean we could run 2*N slaves concurrently in a parallel query, if we have a query that breaks into a plan that uses multiple DFO trees we might 2 * N * {number of DFO trees) allocated and running concurrently. (And, as we see here, it’s even possible that different DFO trees run with different DOPs, leaving Oracle very confused about what to report as the DOP of the query – dbms_xplan actually reported this one as DOP = 4 !)

A final implementation detail that is not necessarily obvious from this table, but which I can infer because I know the history of what was going on: although, as described above, the tablescan of t3 was the first activity that generated a row source, Oracle started up the 10 slaves for the parallel tablescan and aggregate of t1 before it started the two slaves it needed to scan t3. I can infer this because there were no slave processes active when I started running the query, so it is reasonable to assume that the slave names tell me something about the order in which they were allocated. In general you would not be able to notice this in a busy system where slaves were pre-allocated (parallel_min_servers != 0) and constantly being acquired and released. (I was also able to corroborate some of the details above by looking at v$px_stat and v$session_event for the relevant slave processes shortly after the query started.)

Summary

There are a number of key points this example shows us about complex parallel queries:

  • A single query can produce multiple “DFO trees”
  • Each tree can run at a different degree of parallelism
  • The “tree number” (the X in TQXnnnn) may not reveal the order in which the trees are operated
  • The order of operation of the DFOs within a tree is revealed by the nnnn in TQXnnnn
  • It is not easy to see in a basic execution plan which DFO tree might be executing concurrently and which consecutively
  • PX server processes may be acquired by the query co-ordinator a long time before they actually become active
Footnote:

If you happen to have looked closely at the time information in the Parallel Execution Details you’ll note that the times are total rubbish when compared to the monitoring times. It looks as if the time spent in dbms_lock.sleep() has been ignored (perhaps as an “idle” wait).

 


Brazil

Greg Pavlik - Sun, 2015-12-20 23:11
Blown away to get my purple belt in Brazilian Jiu Jitsu from 10th Planet black belt Alex Canders.


ORAMEX Tech Day 2015 Guadalajara: Global Cloud UX Goes Local

Usable Apps - Sun, 2015-12-20 22:59

You asked. We came. We were already there.

In November, the Oracle México Development Center (MDC) in Guadalajara hosted the ORAMEX Tech Day 2015 event. This great location gave the Grupo de Usuarios Oracle de México (the Oracle User Group in México) (@oramexico) access to the very strong technical community in the region, and attendees from Guadalajara and surrounding cities such as Colima, León, and Morelia heard MDC General Manager Erik Peterson kick off the proceedings with a timely keynote on the important role that MDC (now celebrating its 5th year) plays in delivering Oracle products and services.

Erik Peterson delivers the MDC keynote

Erik Peterson delivers the MDC keynote at the ORAMEX Tech Day.

Naturally, Tech Day was also a perfect opportunity for the Oracle Applications User Experience (UX) team to support our ORAMEX friends with the latest and greatest UX outreach.

UX team at ORAMEX Tech Day 2015

Oracle Applications UX staffers at ORAMEX Tech Day (Left to right): Sarahi Mireles, Tim Dubois, Rafael (Rafa) Belloni, and Noel Portugal (image courtesy of ORAMEX) 

UX team members from the U.S., Senior Manager UX Product and Program Management Tim Dubois (@timdubis) and Senior Manager Emerging Tech Development Noel Portugal (@noelportugal), joined local staffers Senior UX Design Developer Rafa Belloni (@rafabelloni) and UX Developer Sarahi Mireles (@sarahimireles) to demo the latest UX technical resources for the community, to bring everyone up to speed on the latest UX cloud strategy and messages, and to take the pulse of the local market for our cloud UX and innovation enablement and outreach.

Tim and Sarahi demoed the latest from the Release 10 Simplified UI PaaS4SaaS Rapid Development Kit (RDK) and Rafa and Noel showed off cool Internet of Things proof of concept innovations; all seamlessly part of the same Oracle UX cloud strategy.

Sarahi Mireles introduces the RDK

Sarahi leading and winning with the RDK 

Tim and Sarahi provided a dual-language (in English and Spanish), real-time, exploration of what the RDK is, why you need it, what it contains, and how you get started.

Tim Dubois deep dives into the RDK for ORAMEX audience

The long view: Tim explains that the RDK is part of an overall enablement strategy for the Oracle Cloud UX: Simple to use, simple to build, simple to sell solutions. 

You can get started by grabbing technology-neutral Simplified UI UX Design Patterns for Release 10 eBook. It's free. And, watch out for updates to the RDK on the "Build a Simplified UI" page on the Usable Apps website. Bookmark it now! 

Simplified UI UX Design Patterns eBook

Your FREE Simplified UI UX Design Patterns eBook for PaaS and SaaS is now available

ORAMEX Tech Day 2015 was a great success, representing an opportunity for OAUX to collaborate with, and enable, a local technical community and Oracle User Group, to demonstrate, in practical ways, our commitment to bringing that must-have cloud UX message and resources to partners and customers worldwide, and of course, to show examples of the awesome role the MDC UX team plays within Oracle.

 UX Team Ready to Fly

Where will we go next? I wonder…

What's next? Stay tuned to the Usable Apps website for event details and how you can participate in our outreach and follow us on Twitter at @usableapps for up-to-the-minute happenings!

Special thanks goes to Plinio Arbizu (@parbizu) and Rolando Carrasco (@borland_c) and to the rest of the ORAMEX team for inviting us and for organizing such a great event.