Oakies Blog Aggregator

I’ve just got back from watching Man of Steel at the cinema.

I went into this film with extremely low expectations. For people of my age, this is the third time round for this story, so I expected to be pretty bored from a plot perspective.

I’m going to split the film into three parts.

1. The first part of the story concerned the birth of Kal-El and him getting sent to earth. I expected this to be really dull and a bit annoying because of Russell Crowe’s presence. Actually is turned out to be completely brilliant. If the whole film had been similar to this first section it would probably have been the greatest Sci-Fi movie I had ever seen. If all you do is go in, watch this first sequence then leave, you will have had your money’s worth, especially since it was only £3 to get in on Tuesday night.
2. The second part involved Kal-El growing up and becoming Superman. I also expected this to be a little dull, but actually is was really neat. They approached this part of the story in a different way to the previous films. It worked really well and I actually felt myself starting to care about the lead character.
3. The third part of the film was just disaster porn. I found it really dull and generic. In parts it felt like a rip-off of the last fight scene in The Matrix Revolutions, mixed in with anything Michael Bay has ever done. I found myself hoping everyone would just hurry up and die so I could go home. Sometimes I find this stuff mildly amusing, but most of the time I just zone out and wonder what I am going to get to eat on the way home…

On my way out I was listening to a few conversations. One woman said, “The acting was terrible and I am so bored with seeing buildings get blown up!” I heard a group of guys talking in the car park and their conversation distilled down to, “He just didn’t do anything for the last half of the film!”

As it stands, I enjoyed it a lot more than I expected, but after a fantastic start it degenerated into mediocrity.

Cheers

Tim…

---

Man of Steel was first posted on June 19, 2013 at 12:04 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

I just realised this week that I haven’t really detailed anything about policy managed RAC databases. I remembered having done some research about server pools way back when 11.2.0.1 came out. I promised to spend some time looking at the new type of database that comes with server pools: policy managed databases but somehow didn’t get around to doing it. Since I’m lazy I’ll refer to these databases as PMDs from now on as it saves a fair bit of typing.

So how are PMDs different from Administrator Managed Databases?

First of all you can have PMDs with RAC only, i.e. in a multi-instance active/active configuration. Before 11.2 RAC you had to tie an Oracle instance to a cluster node. This is why you see instance prefixes in a RAC spfile. Here is an example from my lab 11.2.0.3.6 cluster:

DEMO1.__db_cache_size=1073741824
[...]
DEMO2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/DEMO/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='...'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DEMO'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
DEMO2.instance_number=2
DEMO1.instance_number=1
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=310378496
*.processes=300
*.remote_listener='rac11gr2scan.example.com:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=1610612736
DEMO1.thread=1
DEMO2.thread=2
DEMO1.undo_tablespace='UNDOTBS2'
DEMO2.undo_tablespace='UNDOTBS1'

Note that the instance_number, thread and undo tablespace are manually (=administrator) managed. If these aren’t set or configured incorrectly you will run into all sorts of fun.

Enter the Policy Managed Database

In the never ending effort to make the DBA’s life easier Oracle decided to automate the process of RAC database creation and management. So instead of manually mapping instances to nodes in policy managed databases you map databases to server pools. Please refer to my earlier post about server pool management

http://martincarstenbach.wordpress.com/2010/02/12/server-pool-experiments-in-rac-11-2/

(this is the reason I blog by the way-to be able to look up things I don’t do every day!)

Basing myself on the post I checked my 2 node lab environment. Since all I ever ran on it are administrator managed databases I was not surprised to find the basic configuration:

[oracle@rac11gr2node1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Candidate server names: rac11gr2node1,rac11gr2node2
[oracle@rac11gr2node1 ~]$

As you can see there are a free and a generic pool. SRVCTL is a great utility when you are unsure how to do something, simply add a -h to it and you will see the syntax:

[oracle@rac11gr2node1 ~]$ srvctl add srvpool -h

Adds a server pool to the Oracle Clusterware.

Usage: srvctl add srvpool -g  [-l ] [-u ] [-i ] [-n ""] [-f]
    -g            Server pool name
    -l                  Minimum size of the server pool (Default value is 0)
    -u                  Maximum size of the server pool (Default value is -1 for unlimited maximum size)
    -i           Importance of the server pool (Default value is 0)
    -n ""       Comma separated list of candidate server names
    -f                       Force the operation even though some resource(s) will be stopped
    -h                       Print usage
[oracle@rac11gr2node1 ~]$

Easy enough! I am now adding a new server pool across both nodes with high importance. Note that min=max=1 here. This is done on purpose to demonstrate how to extend the database to another node.

[oracle@rac11gr2node1 ~]$ srvctl add srvpool -g blog -l 1 -u 1 -n rac11gr2node1,rac11gr2node2

That was simple! But creating the pool is only half the job done. Does it have any servers assigned to it?

[oracle@rac11gr2node1 ~]$ srvctl status srvpool
Server pool name: Free
Active servers count: 0
Server pool name: Generic
Active servers count: 2
Server pool name: blog
Active servers count: 0

There are NO servers assigned. Remember when I said I was using admin managed databases? Their instances are using the GENERIC pool by default. So for the purpose of this blog post I had to remove their configuration. In real life you would of course not do this!

[oracle@rac11gr2node1 ~]$ srvctl config database
DEMO
RON

Both of these are admin managed. Let’s remove them (again for the sake of demonstration only)

[oracle@rac11gr2node1 ~]$ srvctl remove database -d RON
Remove the database RON? (y/[n]) y

[oracle@rac11gr2node1 ~]$ srvctl remove database -d DEMO
Remove the database DEMO? (y/[n]) y

Now it’s looking better, or so I hope:

[oracle@rac11gr2node1 ~]$ srvctl config srvpool -g blog
Server pool name: blog
Importance: 999, Min: 1, Max: 1
Candidate server names: rac11gr2node1,rac11gr2node2
[oracle@rac11gr2node1 ~]$

[oracle@rac11gr2node1 ~]$ srvctl status srvpool -g blog
Server pool name: blog
Active servers count: 1
[oracle@rac11gr2node1 ~]$

Hurray, I have my server!

Now over to dbca to create the policy managed database. This is not different at all from creating the admin managed database except for 1 step, shown in the figure below:

Note how I have chosen the new server pool with 1 active server. The database type is set to policy managed as well. After the usual series of “next”, “next”, “next” the database will eventually be ready for use:

Now let’s have a look at the resource definition for a policy managed database:

[oracle@rac11gr2node1 ~]$ srvctl config database -d poldb
Database unique name: poldb
Database name: poldb
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/poldb/spfilepoldb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: blog
Database instances:
Disk Groups: DATA,RECO
Mount point paths:
Services:
Type: RAC
Database is policy managed

In this case the notion of “server pools” makes a lot of sense. Interestingly no instance is recorded, but there is one:

[oracle@rac11gr2node1 ~]$ srvctl status database -d poldb
Instance poldb_1 is running on node rac11gr2node2

For those who used RAC One before the $ORACLE_SID will look familiar. As with RON, you get the DB_NAME plus instance number suffix.

Adding an instance

Now let’s increase the size of the pool by another node and see what happens. This in the only command I typed:

[oracle@rac11gr2node1 ~]$ srvctl modify srvpool -g blog -u 2

It takes a little while to complete though, but here’s the magic:

[oracle@rac11gr2node1 ~]$ srvctl status database -d poldb
Instance poldb_2 is running on node rac11gr2node1
Instance poldb_1 is running on node rac11gr2node2
[oracle@rac11gr2node1 ~]$

Now is that cool or isn’t it? So without me doing anything Oracle created a new online redo thread, another undo tablespace etc. I found this in the alert.log of instance 1:

2013-06-16 21:57:16.082000 +01:00
Reconfiguration started (old inc 2, new inc 4)
List of instances:
 1 2 (myinst: 1)
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete
alter database add logfile thread 2 SIZE 52428800 , SIZE 52428800 , SIZE 52428800
minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:4 errcnt:0
2013-06-16 21:57:57.487000 +01:00
Completed: alter database add logfile thread 2 SIZE 52428800 , SIZE 52428800 , SIZE 52428800
alter database enable public thread 2
2013-06-16 21:57:58.649000 +01:00
Completed: alter database enable public thread 2
create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 83886080 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2013-06-16 21:58:22.055000 +01:00
Completed: create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 83886080 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2013-06-16 22:00:49.614000 +01:00

Services

Services are different in policy managed databases too. Two different models exist: singleton and uniform services. The singleton is active on a single instance only (which you don’t get to choose). A uniform service is active on all instances. Forget about preferred and available nodes, that’s for admin managed databases only! Oracle is slightly misleading here:

[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s uni
PRKO-3116 : '-g' or '-r' option should be provided
[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s uni -r poldb_1
PRKO-3114 : Policy-managed database poldb can not support administrator-managed service single.
[oracle@rac11gr2node2 ~]$ srvctl add service -d poldb -s single -g blog

So again-no preferred instance, but only a server pool to specify. Once the services are created, start them as usual. The status message will be similar to this one:

[oracle@rac11gr2node2 ~]$ srvctl status service -d poldb
Service single is running on nodes: rac11gr2node1
Service uni is running on nodes: rac11gr2node1,rac11gr2node2

Summary

That’s it for an introduction to policy managed databases. These are cool things, especially if you think about the possibilities to create nested server pools or automatically draw in servers from lower priority pools to those with high priority to compensate a failed node. I don’t have enough horse power on my lab server to create a 4 node cluster but I’d love to play around with these features one day.

Having said that I don’t really know of anyone who uses PMDs and server pools in production. If you happen to be one, please comment and let me know!

The TIMESTAMP WITH TIME ZONE data type that got introduced a long time ago is known for some oddities, for example Tony Hasler has a nice summary of some of them here.Here is another oddity that shows up when trying to aggregate on such a data type. Have a look at the following simple example:

create table t
as
select
        rownum as id
      , date '2000-01-01' + rownum - 1 as some_date
      , cast(date '2000-01-01' + rownum - 1 as timestamp) as some_timestamp
      , cast(date '2000-01-01' + rownum - 1 as timestamp with local time zone) as some_timestamp_with_local_tz
      , cast(date '2000-01-01' + rownum - 1 as timestamp with time zone) as some_timestamp_with_timezone
from
        dual
connect by
        level <= 1e6
;

exec dbms_stats.gather_table_stats(null, 't')

explain plan for
select count(*), some_date from t group by some_date;

explain plan for
select count(*), some_timestamp from t group by some_timestamp;

explain plan for
select count(*), some_timestamp_with_local_tz from t group by some_timestamp_with_local_tz;

explain plan for
select count(*), some_timestamp_with_timezone from t group by some_timestamp_with_timezone;

The first three all will return the same execution plan:

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  HASH GROUP BY     |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------
 

Notice the HASH GROUP BY operation selected by default by the optimizer (which can be influenced using the [NO_]USE_HASH_AGGREGATION hint to switch between a SORT and HASH GROUP BY).But for the TIMESTAMP WITH TIME ZONE column, the following execution plan will be shown:

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT GROUP BY     |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Notice the SORT GROUP BY instead - and this cannot be influenced using the above mentioned hint. So when using TIMESTAMP WITH TIME ZONE, the hash aggregation obviously isn't supported, but for all other TIMEZONE data types it is.Depending on the scenario this might already influence the performance as the HASH based aggregation in many cases is more efficient than the sort based aggregation (bugs aside).Things however get really bad when using Parallel Execution:

explain plan for
select /*+ parallel(t 4) */ count(*), some_date from t group by some_date;

explain plan for
select /*+ parallel(t 4) */ count(*), some_timestamp from t group by some_timestamp;

explain plan for
select /*+ parallel(t 4) */ count(*), some_timestamp_with_local_tz from t group by some_timestamp_with_local_tz;

explain plan for
select /*+ parallel(t 4) */ count(*), some_timestamp_with_timezone from t group by some_timestamp_with_timezone;

Again for the first three, we get the same execution plan:

-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY        |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

Notice how the (hash) aggregation is performed as a parallel operation (and you might even see a so called GROUP BY PUSHDOWN from 11g on represented by a second GROUP BY operation before re-distributing the data depending on the cardinalities or the usage of the [NO_]GPY_PUSHDOWN hint).Now look closely at the execution plan of the last statement using the TIMESTAMP WITH TIME ZONE data type:

-----------------------------------------------------------------------
| Id  | Operation             | Name     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |      |            |
|   1 |  SORT GROUP BY        |          |        |      |            |
|   2 |   PX COORDINATOR      |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------

So the (sort) aggregation is no longer performed in parallel and this means that the single Query Coordinator process has to perform the aggregation on its own, which clearly can be a threat when dealing with larger data sets that need to be aggregated.

Footnote

Since internally the DISTINCT / UNIQUE operator uses the same implementation as the GROUP BY operator, exactly the same limitations apply when trying to do a DISTINCT / UNIQUE on a TIMESTAMP WITH TIME ZONE data type.I could reproduce the described behaviour on all versions tested, starting from 10.2.0.4 and including the latest 11.2.0.3.

Following the webinars about 11g stats that I presented on Monday John Goodhue emailed me a few questions that had come through the chat line while I was speaking, but hadn’t appeared on my screen. He’s emailed them to me, so here are the questions and answers.

1. I’d like to know what parameter to use for faster results on dbms_stats.gather_dictionary_stats

I don’t think I’ve come across any complaints about gathering dictionary stats being particularly slow, and you haven’t mentioned a specific version so I can’t make any comment about possible bugs. If you can, you first need to find out where most of the time is going, so tracing the call would be a good idea to start with. If you can’t trace it you could look at the “standard” types of problem such as: AWR history tables getting very large, optimizer stats history tables (particularly the histogram one) getting very large; synopsis tables getting very large. Histograms can be very expensive to collect, especially on partitioned tables, and many of the 11g dictionary tables are partitioned – so you might want to changed the method_opt for the collection to “for all columns size 1″. It is possible to get Oracle to dispatch multiple jobs to collect stats (the global preference CONCURRENT) if the issue is elapsed time rather than workload – check dbms_stats.set_global_prefs(), parameter CONCURRENT.

2.  Do incremental statistics also work with sub-partitions?

No. Even when you have a composite partitioned table, Oracle only uses the approximate NDV method to create synopses at the partition level. Given that a synopsis is up to 16,384 rows for each column for each partition, the volume of synopsis information that could be generated by extending the mechanism down to the subpartition level could be catastrophic.

3. I have always been benefiting from block_sample=>TRUE .. I do not see you using it – is there any reason to not use it {while having in mind of course, that the accuracy may suffer a bit, with heap tables, at the cost of faster gathered stats}

The only good reason not to use it is the reason you’ve given - there are patterns of skewed data that could make a block sample even less accurate on the number of distinct values than a row sample. The particular benefit of the block sample (for “safe” distributions) is that even a small percentage row sample may require Oracle to read every block of the table as it picks pseudo-random rows – perhaps even doing “db file scattered read” requests to scan the whole table and discarding lots of blocks (one of reasons behind statistic “prefetched blocks aged out before use”) because it didn’t need rows from them. With the 11g approximate NDV, of course, the question is moot since the new mechanism examines every row in the table.

4. is this #rows * #columns sorting for count distinct as well happening for any distinct? (as SELECT DISTINCT is generally a group by every selected column)

You’re referring to my comment about “select count(distinct n1) , count(distinct n2). count(distinct n3) from table;” seems to turn each row into N rows (where N = 3 in this case) of (column id, column value) and then sort the entire set in one operation rather than N concurrent operations. I don’t think you can generalise this, you’d have to check each SQL pattern separately – initially just checking the execution plan. For example, something like ‘select distinct n1, n2, n3 from table” is finding distinct combinations, and it will do a hash unique on the number of (non-null) rows in the table irrespective of the number of columns selected.

5. what is your view on using table preferences to explicitly specified required histograms?

I made a brief statement about collecting histograms for anything but your explicitly chosen columns; following up with the suggestion that your default action should be “for all columns size 1″ followed by explicit code to generated histograms for specific tables. I would be perfectly happy to set the global prefs to “for all columns size 1″ and then set table prefs to something specific for the columns I really wanted; the only reason that I don’t put this forward as a standard practice is that it does conceal what’s going on, and it’s easy to forget that some tables have preferences set. If your organisation is really good about documentation of systems then it makes perfect sense.  Having said that, though, don’t forget that I also pointed out that sometimes you may still need to create “fake” histograms to get the best possible results.

6.  (paraphrased)  I think Jonathan should have acknowledged that his presentation was based on the work of Amit Poddar.

I agree. This was an error of omission that I shouldn’t committed. It has been several years since Amit first described how Oracle uses a hash table for the approximate NDV and keeps halving it when the number of hash values reaches 16,384. When he closed his website he allowed me to publish the original presentation and white paper (64 pages) on my blog so I really should have remembered that the algorithm is still so little-known that he still merited attribution. As far as I am aware his work is still the most thorough and detailed description of how the algorithm works, although Oracle has extended it in 12c (as explained in the presentation I did about histograms for OOW 2012).