Skip navigation.

Feed aggregator

From Cupcakes to the Cloud: Oracle ERP User Experience Bakes In Exceedingly Better Business*

Usable Apps - Sun, 2015-06-21 07:58

Robots—software, really—continue to revolutionize enterprise finance departments. Automation is replacing traditional financial roles and transforming others, offering even more innovative opportunities in the enterprise.

Accounts payable clerks, accounts receivable specialists, inventory control admins, and more enterprise positions, are being automated completely, freeing up headcount for new roles to deliver more business effectiveness. The Oracle Applications Cloud user experience (UX) strategy reflects these kinds of trends and innovates accordingly.

And yet, the birth of such financial innovation was in the least likely, most un-sci-fi place, you might imagine: the teashops of post-World War II Great Britain. In 1951, British catering giant J Lyons & Co. kick-started ERP as we now know it by introducing the first enterprise computer: LEO (Lyons Electronic Office).

Computing was not new, of course. What was innovative was how Lyons used it in business. Starting with the replacement of dull, repetitive tasks performed by clerks collecting and entering data, LEO went on to manage the Lyons payroll, the catering supply chain, and more.

LEO led the world in business computing at the time, and gave rise to today’s systems engineering. And all because of a dream of one day being able to add up the receipts for Lyons's iced buns in the teashops of Great Britain.

Enterprise Resource Planning (ERP) user experience (UX) is no longer just icing on the cake. It's central to user happiness and business productivity, satisfying that ever-ravenous appetite for consumer-like apps in the enterprise. UX is now that must-have item on the menu of enterprise cloud software adoption.

David Haimes glances across the Oracle ERP Cloud

Number-munching: David Haimes glances across the Oracle ERP Cloud UX over the best of Oracle HQ catering.

I chatted with David Haimes (@dhaimes), Senior Director of Oracle Applications Product Development and all-things Financials Cloud UX champ, about financial departments moving from cupcakes to computers and now to the cloud.

Over the best of Oracle HQ Building 300 bakery cakes, David explained:

“That mundane, day-to-day work of calculations has been transformed by enterprise software. Now finance departments do things automatically, faster, and with fewer errors. Oracle ERP Cloud enhances daily activities, enabling departments to analyze data for profit, revenue, and cash flow insights for business planning and financial forecasting, and to manage by exception.

For example, with the Oracle ERP Cloud visualizations of financial data, finance departments can easily spot trends and opportunities to run the business better. At a glance, a department can see those outliers and exceptions that may be threats and deal with them before they become real problems.”

What's going on here? Glance at Oracle ERP Cloud and the Finance Department's questions are answered

What's going on here? At a glance, period close, revenue, expenses, and more questions are answered for the finance department using Oracle ERP Cloud.

David demonstrated how the Oracle ERP Cloud user experience enables higher-value finance department activities using the Glance, Scan, Commit design philosophy. At a glance, from one page an analyst can see what’s going on with a company’s finances, what needs attention, and then scan for more detail and commit to act on it easily, if necessary.

Oracle ERP Cloud user experience is no amuse-bouche, but the main course for CIOs and decision makers, saving their businesses time and labor costs. With the median number of full-time financial employees falling in big companies by about 40% since 2004, there are now about 70 finance people needed for every $1 billion of revenue, according to consultants, the Hackett Group. It's all about ROI. Oracle ERP Cloud serves up a great recipe for user satisfaction (key ingredients: zero training, talent attraction, and retention) too. 

Oracle ERP Cloud watchface

Oracle ERP Cloud watchface on Android Wear smartwatch. With financial data in the cloud, the glance design philosophy enables finance departments to work seamlessly across devices.

We’ve already busted that myth that financial departments are far from social, and now another stereotypical view of accountancy is up-ended. Emerging financial roles have become sexy, the analysts required for today’s financial departments are hot talent demanding higher remuneration.

This is one sweet story about Oracle Cloud ERP and an awesome baked-in UX: automating the routine; enabling a eminently more interesting world of work for real people previously dismissed as being disinterested in such things; delivering benefits across the entire business; and being easily customized to suit any business taste.

Now, who wouldn’t want a slice of that action?

Time for Tea?

David Haimes takes the time to glance at Oracle ERP Cloud

Take some time to explore the Oracle ERP Cloud user experience more with our online resources.

Find out more about the Oracle Applications Cloud User Experience and Oracle ERP Cloud on YouTube and the Oracle VOX blog.

And, watch out for some tasty ERP Cloud sessions at Oracle OpenWorld 2015.

David Haimes blogs on the Oracle Intercompany Financials Blog.

* With apologies to Mr. K

GoldenGate Initial Load by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:42
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate



Index
GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

GoldenGate Use Cases by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:41
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate



Index
GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

GoldenGate Monitoring, Troubleshooting and Tuning by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:41
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate



Index
GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

GoldenGate Implementations Essential by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:38
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate



Index
GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

GoldenGate Fundamentals Session by Senthil Rajendran

Senthil Rajendran - Sun, 2015-06-21 02:36
Welcome to the world of replication. Recently have been deep into GoldenGate and here are the series of presentation that can help you to start with GoldenGate




Index
GoldenGate Fundamentals Session
GoldenGate Implementations Essential
GoldenGate Monitoring, Troubleshooting and Tuning
GoldenGate Use Cases
GoldenGate Initial Load

SQL Server 2016 : availability groups and the new clustered columnstore index support

Yann Neuhaus - Sat, 2015-06-20 13:25

This blog post is focused on the new supportability of clustered columnstore index on the high- availability read-only replicas.

Others studies are available here:

After reading some improvements from the columnstore index feature side, I was very surprised to see the following section:

 

blog_53_-_1-_cci_new_features

 

Clustered columnstore indexes are now supported on AlwaysOn readable secondary replicas! Wonderful! And I guess that the new support of both SI and RCSI transaction isolation level have something to do with this improvement.

So let’s create a clustered columnstore index on my lab environment. I will use for my tests the AdventureWorksDW2012 database and the FactProductInventory fact table.

A first look at this fact table tells us that we’ll probably face an error message because this table contains some foreign keys and one primary key that are not supported on SQL Server 2014. Fortunately, SQL Server 2016 has no such limitations and we’ll able to create the clustered columnstore index.

 

sp_helpconstraint 'dbo.FactProductInventory'; go

 

blog_53_-_2-_cci_fk_pk

 

We can notice two foreign keys and one primary key. The latter is clustered so before creating the clustered columnstore, I will have to change the primary key to a unique constraint.

 

-- drop primary key alter table dbo.FactProductInventory drop constraint PK_FactProductInventory; go   -- create CCI create clustered columnstore index idx_cci_FactProductInventory on FactProductInventory   -- create unique constraint on ProductKey, DateKey columns alter table dbo.FactProductInventory add constraint PK_FactProductInventory unique (ProductKey, DateKey); go

 

Let’s add 2 rows in the dbo.FactProductInventory

 

insert [AdventureWorksDW2012].[dbo].[FactProductInventory] values (167, '20090101', '2009-01-01', 0.19, 0, 0, 875)   insert [AdventureWorksDW2012].[dbo].[FactProductInventory] values (167, '20091002', '2009-01-02', 0.19, 0, 0, 875)

 

… and let’s take a look at this columstore index configuration:

 

select        partition_number,        state_description,        count(*) as nb_row_groups,        sum(total_rows) as total_rows,        sum(size_in_bytes) / 1024 / 1024 as size_mb from sys.column_store_row_groups where object_id = object_id('dbo.FactProductInventory') group by partition_number, state_description order by partition_number, state_description; go

 

blog_53_-_3-_cci_rowgroups

 

Finally let’s add this AdventureWorks2012DW database to my availability group named 2016Grp:

 

blog_53_-_4-_cci_aag

 

Now, let’s try to query my columnstore index on the SQL162 replica configured as secondary read-only replica:

 

select @@servername as server_name; go   select ProductKey, max(DateKey) from [AdventureWorksDW2012].[dbo].[FactProductInventory] where ProductKey = 167 group by ProductKey; go

 

blog_53_-_5-_cci_query

 

 

Do you remember this error message with SQL Server 2014?

Msg 35371, Level 16, State 1, Line 120 SNAPSHOT isolation level is not supported on a table which has a clustered columnstore index.

 

With SQL Server 2016 it seems to be another story and it appears to be working properly and this is because snapshot is now supported for clustered columnstore indexes. Let’s have a look at the result of the following query:

 

select        st.session_id,        st.is_user_transaction,        tat.transaction_id,        tat.transaction_sequence_num,        tat.is_snapshot from sys.dm_tran_active_snapshot_database_transactions as tat join sys.dm_tran_session_transactions as st        on tat.transaction_id = st.transaction_id

 

blog_53_-_6-_cci_tran

 

The session with id = 65 concerns my query here. We may notice that it is using snapshot without specifying anything transaction isolation level parameter from my side. As a reminder all read-only queries on a secondary replica are automatically overridden to snapshot isolation and row version mechanism to avoid blocking contention.

But I’m also curious to know if we will face the same blocked redo thread issue in this case? As you know, snapshot isolation prevents read-only queries on the secondary replicas from taking locks and preventing other DML statements against the database from executing, but it doesn’t prevent the read-only queries from taking schema stability locks and blocking DDL statements.

So I wondered if operations issued by the tuple mover in order to switch data from delta store to a row group could stuck the redo thread from executing correctly. To create such situation we may use a long running query on the columnstore index from the secondary replica and in the same time we may insert sufficient data to close a delta store from the primary replica.

Here my horrible and inefficient query that I executed from the secondary. The idea is to hold a schema stability lock during my test.

 

-- from the secondary replica select c.ProductKey, max(c.DateKey) from [AdventureWorksDW2012].[dbo].[FactProductInventory] as c cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c2 cross join [AdventureWorksDW2012].[dbo].[FactProductInventory] as c3 group by c.ProductKey; go

 

And the locks hold by my query:

 

blog_53_-_7-_ro_query_locks

 

Then I inserted sufficient data to close my delta store:

 

blog_53_-_8-_cci_delta_store_closed

 

After calling manually the tuple mover by using the ALTER INDEX REORGANIZE command, I didn’t face a blocking redo thread situation.

 

-- from secondary replica use AdventureWorksDW2012 go   select        r.replica_server_name,        g.name as aag_name,        drs.synchronization_state_desc as replica_state,        drs.synchronization_health_desc as synchro_state,        drs.redo_queue_size from sys.dm_hadr_database_replica_states as drs join sys.availability_replicas as r        on r.replica_id = drs.replica_id join sys.availability_groups as g        on g.group_id = drs.group_id where g.name = '2016Grp'        and drs.database_id = DB_ID();

 

blog_53_-_9-_cci_redo_size_queue

 

In fact, we can expect to this result because according to the Microsoft documentation, ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during this operation.

So the new situation is the following on both replicas:

 

blog_53_-_10-_cci_reorganize

 

Note the new delta store state TOMBSTONE here. I got some information from the Niko Neugebauer (@NikoNeugebauer) blog post here. In fact row group in TOMBSTONE state are delta stores that got compressed (row_group_id = 16 to row_group_id = 18 in my context). This unused row group will be dropped asynchronously by the garbage collector by the tuple mover.

Finally, let’s try to rebuild my columnstore index. In this case we may expect to be in a situation where the redo thread will be blocked by this DDL operation.

 

-- from the primary replica alter index [idx_cci_FactProductInventory] on [dbo].[FactProductInventory]

 

Let’s take a look at the redo queue from my secondary replica:

 

-- from secondary replica use AdventureWorks2012DW go   select        r.replica_server_name,        g.name as aag_name,        drs.synchronization_state_desc as replica_state,        drs.synchronization_health_desc as synchro_state,        drs.redo_queue_size from sys.dm_hadr_database_replica_states as drs join sys.availability_replicas as r        on r.replica_id = drs.replica_id join sys.availability_groups as g        on g.group_id = drs.group_id where g.name = '2016Grp'        and drs.database_id = DB_ID();

 

blog_53_-_11-_ro_blocked_redo_thread

 

This time, the ALTER INDEX REBUILD operation has a negative effect on the redo thread which is now stuck while the reporting query execution.

 

-- from the secondary replica select session_id,        command,        blocking_session_id,        wait_time,        wait_type,        wait_resource from sys.dm_exec_requests where command = 'DB STARTUP'

 

blog_53_-_12-_ro_blocked_redo_thread_2

 

The blocking session concerns my reporting query here:

 

The bottom line

Introducing the new clustered columnstore index in AlwaysOn availability groups will be definitively a good improvement in several aspects. Indeed, even if clustered columnstore indexes are designed to save a lot of resources, it is always interesting to benefit this feature to offload reporting activities in some scenarios. Moreover, it will be also a good answer to the existing lack of both SI and RCSI transaction isolation levels and the reporting data consistency without locking. I’m looking forward to see these both features in action in production environments soon!

 

When a function returns a collection: can you directly reference the index of the returned collection?

Yann Neuhaus - Sat, 2015-06-20 13:00

As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case:

Aporias

Greg Pavlik - Sat, 2015-06-20 10:03
1.ActuallyHe likes word games    DiffidentIn the way they circle about    Starlings in flight    Or Seraphim.
2.With sweeping gesture    Left to rightHands hang with headUnder the pressing of the sun:    Weight of doubt    Or will.
3.Against open air, tumultuous sea    Turtle greenThe division is nowhere more evidentWhere sand meets froth    Stark, blinding glareWind balmed    Until night.
4.Cleanliness,    Next to godlinessThe echo of countless schoolmarmsChiding, chilling - without regret    Yeah, rather,    Motherhood.

Does the block size matter?

Yann Neuhaus - Sat, 2015-06-20 04:57

The default block size is 8k in Oracle. This is the most common. Sometimes, people create the database with 16k block size for datawarehouses. You can also find some 32k block size, but less common which means more bugs. In our tuning workshop we recommend the default, 8k, for most of the cases. I'll show here 3 tests to show what different block size change for full table scan and index access.

Test case

I have defined a cache size for the non default block size I want to use:

SQL> show parameter db%_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_cache_size big integer 0
db_2k_cache_size                     big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_16k_cache_size big integer 112M
db_32k_cache_size big integer 112M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

I'm creating 3 tablespaces with 8k, 16k and 32k block size. I create them as uniform with a 1M extent size because we are supposed to use large block size for large tables and I don't want the side effects of smaller first extents in auto extent size.

SQL> create tablespace DEMO08K datafile '/oracle/u01/oradata/DEMO08K.dbf' size 1024M extent management local uniform size 1M blocksize 8k;
Tablespace created.

SQL> create tablespace DEMO16K datafile '/oracle/u01/oradata/DEMO16K.dbf' size 1024M extent management local uniform size 1M blocksize 16k;
Tablespace created.

SQL> create tablespace DEMO32K datafile '/oracle/u01/oradata/DEMO32K.dbf' size 1024M extent management local uniform size 1M blocksize 32k;
Tablespace created.

and then create 3 identical tables in each tablespace:

SQL> create table TAB08K (id constraint PK_08K primary key,n,x) tablespace DEMO08K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

SQL> create table TAB16K (id constraint PK_16K primary key,n,x) tablespace DEMO16K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

SQL> create table TAB32K (id constraint PK_32K primary key,n,x) tablespace DEMO32K as
     select rownum id , ora_hash(rownum,10) n , lpad(ora_hash(rownum,100),20,'0') x from xmltable('1 to 10000000');
Table created.

My tables have 10 million rows, two number column and one larger varchar2:

SQL> select table_name,num_rows,avg_row_len,blocks from user_tables where table_name like 'TAB__K';

TABLE_NAME   NUM_ROWS AVG_ROW_LEN     BLOCKS
---------- ---------- ----------- ----------
TAB08K       10000000          30      48459
TAB16K       10000000          30      23997
TAB32K       10000000          30      11933

Of course, larger block size need smaller number of blocks, but the total size is roughly the same. Here I have small rows so this is where the fixed size of block header can make the most difference.

 

Full Table Scan

So, the common idea is that larger block size helps to do larger i/o calls when doing full table scan...

SQL> set timing on arraysize 5000 autotrace trace

SQL> select * from TAB08K;
10000000 rows selected.

Elapsed: 00:00:32.53

Execution Plan
----------------------------------------------------------
Plan hash value: 1209268626

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  8462   (1)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB08K |    10M|   286M|  8462   (1)|
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      50174  consistent gets
      48175  physical reads
          0  redo size
  348174002  bytes sent via SQL*Net to client
      22489  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         403
physical read total multi block requests                                379
physical read total bytes                                         394821632
physical reads                                                        48196
physical reads cache                                                     23
physical reads direct                                                 48173
physical read IO requests                                               403
physical read bytes                                               394821632
physical reads direct temporary tablespace                                1

I've read 48175 8k blocks with 403 i/o calls.

 

Now doing the same from the table stored in the 16k blocksize tablespace:

SQL> select * from TAB16K;

10000000 rows selected.

Elapsed: 00:00:31.04

Execution Plan
----------------------------------------------------------
Plan hash value: 2288178481

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  4378   (2)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB16K |    10M|   286M|  4378   (2)|
-------------------------------------------------------------------------

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         397
physical read total multi block requests                                375
physical read total bytes                                         391012352
physical reads                                                        23876
physical reads cache                                                     21
physical reads direct                                                 23855
physical read IO requests                                               397
physical read bytes                                               391012352
physical reads direct temporary tablespace                                1

I've read 23855 16k blocks with 397 i/o calls. It's not a lot better.

SQL> select * from TAB32K;

10000000 rows selected.

Elapsed: 00:00:29.61

Execution Plan
----------------------------------------------------------
Plan hash value: 1240330363

-------------------------------------------------------------------------
| Id  | Operation                 | Name   | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |    10M|   286M|  2364   (3)|
|   1 |  TABLE ACCESS STORAGE FULL| TAB32K |    10M|   286M|  2364   (3)|
-------------------------------------------------------------------------

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         398
physical read total multi block requests                                373
physical read total bytes                                         388890624
physical reads                                                        11886
physical reads cache                                                     24
physical reads direct                                                 11862
physical read IO requests                                               398
physical read bytes                                               388890624
physical reads direct temporary tablespace                                1

I've read 11892 32k blocks with 398 i/o calls.

 

Conclusion: we do roughly the same amount of i/o when doing a full table scan. This is because Oracle is reading in multiblock. Note that the db_file_multiblock_read_count is defined as the number of blocks of default block size, but the i/o size is adapted for tablespace with non default block size. For example, db_file_multiblock_read_count=128 when default block size is 8k will always read 1MB.

Indexes

I already have an index on the primary key. Let's add some more indexes:

SQL> create index ID_08K on TAB08K(x) tablespace DEMO08K ;
Index created.
SQL> create index ID_16K on TAB16K(x) tablespace DEMO16K ;
Index created.
SQL> create index ID_32K on TAB32K(x) tablespace DEMO32K ;
Index created.
SQL> create bitmap index BI_08K on TAB08K(n) tablespace DEMO08K ;
Index created.
SQL> create bitmap index BI_16K on TAB16K(n) tablespace DEMO16K ;
Index created.
SQL> create bitmap index BI_32K on TAB32K(n) tablespace DEMO32K ;
Index created.

and check their size:

SQL> select index_name,num_rows,blevel,leaf_blocks from user_indexes where table_name like 'TAB__K' order by 1;

INDEX_NAME   NUM_ROWS     BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -----------
BI_08K           3211          2        1606
BI_16K           1562          1         781
BI_32K            759          1         380
ID_08K       10000000          2       44643
ID_16K       10000000          2       22027
ID_32K       10000000          2       10929
PK_08K       10000000          2       22132
PK_16K       10000000          2       10921
PK_32K       10000000          2        5425

Of course the number of blocks is lower when the block size is bigger. And because branches are larger, then you may have a smaller depth. But look: on my 10000000 rows table the depth is the same for the regular indexes: 2 branch levels. Only for the bitmap indexes, because they are very small, we need one less branch level here.

 

But think about it. Index depth mostly matter for OLTP where you get rows by their primary key. But people say that smaller blocks are better for OLTP... Datawarehouses often have bitmap indexes, but do you care to have smaller bitmap indexes?

Index access (lot of rows, good clustering factor)

Anyway, let's test a large range scan:

SQL> select * from TAB08K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2790916815

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   707   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB08K |   100K|  2929K|   707   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_08K |   100K|       |   225   (1)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID" v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        878  consistent gets
        679  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         705
physical read total bytes                                           5775360
physical reads                                                          705
physical reads cache                                                    705
physical read IO requests                                               705
physical read bytes                                                 5775360

We have read 100000 rows through index. The index is very well clustered. I've done 705 i/o calls to get those rows from 8k blocks.

 

Now with 16k blocks:

SQL> select * from TAB16K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
Plan hash value: 1432239150

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   352   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB16K |   100K|  2929K|   352   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_16K |   100K|       |   113   (1)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID"v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        537  consistent gets
        337  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         363
physical read total bytes                                           5734400
physical reads                                                          363
physical reads cache                                                    363
physical read IO requests                                               363
physical read bytes                                                 5734400

the number of i/o calls have been divided by two.

 

 

SQL> select * from TAB32K where id between 1 and 100000;

100000 rows selected.

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3074346038

-----------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |   100K|  2929K|   177   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TAB32K |   100K|  2929K|   177   (1)|
|*  2 |   INDEX RANGE SCAN                  | PK_32K |   100K|       |    58   (2)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=1 AND "ID"v=100000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        369  consistent gets
        169  physical reads
          0  redo size
    3389860  bytes sent via SQL*Net to client
       1589  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL> set autotrace off
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'physical read%' and value>0;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                         195
physical read total bytes                                           5750784
physical reads                                                          195
physical reads cache                                                    195
physical read IO requests                                               195
physical read bytes                                                 5750784

with 32k blocks, it's once again divided by two.

 

Conclusion: when doing single block reads, coming from a well clustered index, we do less i/o calls with larger blocks. The fact is that because we need contiguous rows (because we are using a well clustered index) having large blocks makes more rows physically contiguous.

Index access (few rows, bad clustering factor)

Here is a query WHERE X='00000000000000000000'. The index on N - that I've populated with a hash value on rownum - has a bad clustering factor. I fetch only 30 rows.

----------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    30 |   900 |    16   (0)|
|   1 |  COUNT STOPKEY               |        |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB08K |    30 |   900 |    16   (0)|
|*  3 |    INDEX RANGE SCAN          | ID_08K | 99010 |       |     3   (0)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("X"='00000000000000000000')

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                          16
physical read total bytes                                            131072
physical reads                                                           16
physical reads cache                                                     16
physical read IO requests                                                16
physical read bytes                                                  131072

The query returned about 30 rows. Because of the bad clustering factor we had to read a block every two rows on average. Let's see the same with 16k blocks.

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    30 |   900 |     7   (0)| 00:00:01 |
|   1 |  COUNT STOPKEY               |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB32K |    30 |   900 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ID_32K | 99010 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("X"='00000000000000000000')

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total IO requests                                          33
physical read total bytes                                            442368
physical reads                                                           33
physical reads cache                                                     33
physical read IO requests                                                33
physical read bytes                                                  442368

More i/o calls here and higher block size.
Conclusion: larger block size is bad when we need only few rows, especially from a badly clustered index. More i/o calls, larger i/o size, and the large blocks takes more space in the buffer cache.

 

So, which block size suits your workload?

Question is: do you get better performance in datawarhouse with larger block size? For full table scans, no it's the same and has always been the same. For index range scans retrieving large number of rows, then yes, the access by index may be faster. But think about it. Retrieving lot of rows by index is always bad, - whatever the block size is. If you want do to something about it, look at the design: define better indexes (covering all selected columns), partition the table, compress the table, use Parallel Query,... Lot of tuning to do before thinking about block size.

With smaller block size the optimizer will favor a full table scan for those cases, and today the gap between full table scan and index access is not so large. There is Exadata SmartScan, In-Memory. Even without options, multiblock read is faster since 11g when doing serial direct read. All that makes full table scan faster. And index access has also been improved: you have the batched access by rowid to compensate bad clustring factor.

And if you think about having a non default block size for some tablespaces, are you sure that the gain you expect will not be ruined by a bad sizing of buffer cache? When having non default blocksize tablespace you have to manage their buffer cache manually.

Other considerations

Larger block size may have been recommended for very large tablespaces in order to avoid to have too many datafiles (their maximum size is in number of block) but today you can create bigfile tablespaces for them, so it is not a reason anymore.

There is one reason to have larger block size. When you have very large rows, you can avoid row chaining. But once again, are you sure it is a problem (i.e do you select often the columns at the end)? And maybe you should review the design first.

There was another reason to have a larger block size for tablespace containing large LOB (I know that the 'L' is already for 'Large' but I mean LOBs larger than the default block size). Today you should use SecureFiles and we get better performance with them. But that's for another blog post.

Conclusion

When people come with those kinds of rules of thumbs, I usually try to see if it is something they thought about, or just a blind idea. For example, when they want to rebuild indexes, I ask them which PCTFREE they set for that. Because rebuilding without knowing the PCTFREE we want to achieve is pointless.
And when they want to create a tablespace with non default block size, I ask them how they have calculated the buffer cache to allocate for that blocksize. The whole size of the table? Then why do you want to do less i/o calls if everything is in cache. A small size? Then are you sure that the lower number of i/o calls will compensate the cache you can have in the default buffer pool?

In most of the cases, just keep the default block size, and you have probably lot of other things to tune. I'll now tell you a short story. I was a junior not-yet-DBA in 1998, implementing a banking software (COBOL, tuxedo, HP-UX, Oracle 7). Application design had a lot to review. Things like comments stored in CHAR(2000) for example. Then I've seen a senior consultant recommending 'The' solution: increase the block size. The customer accepted that, we did it and everything was immediately better. Of course, in order to do that you have to export/import the database, everything was reorganized, lot of design problems were hidden for a few days. After a while, the performance issues came back, and we had to continue the optimization tasks. Being on a larger block size did not change anything about that. This is where I learned exactly which kind of consultant I don't want to be.

What The World Needs More Of

Greg Pavlik - Fri, 2015-06-19 14:07
The interview with these two kids - Chris and Camryn Singleton - is available on BBC, but I wanted to pull out this remarkable commentary in a related article:

"People are hurting in Charleston. But for the hundreds who packed into the gymnasium at the Goose Creek High School, it was also a reminder of the importance of love.

Sharonda Singleton coached the girls' athletics team here. As her photo rested on an easel on the polished floors in the vast sports hall, her friends and family paid tribute. Speaking for the first time since the deadly attack on the AME church where she worshipped, Sharonda's two children, Chris and Camryn, told me they forgive the man who killed her.

 "We already forgive him and there's nothing but love from our side of the family," Chris told me.

Many will find this incomprehensible. Charleston is often called the Holy City for the number of churches it is home to, and the role religion plays here. For some, like Chris and Camryn, unwavering faith is the only way to turn such a devastating loss into something positive."

This immediately brought to mind the sayings of Fr Zosima in Dostoevsky's Brothers Karamazov, which not to many years ago were the source of a kind of epiphany for me that in a sense reoriented by own thinking:

"Strive to love your neighbor actively and indefatigably. In as far as you advance in love you will grow surer of the reality of God and of the immortality of your soul. If you attain to perfect self-forgetfulness in the love of your neighbor, then you will believe without doubt, and no doubt can possibly enter your soul. This has been tried. This is certain.

Above all, avoid falsehood, every kind of falsehood, especially falseness to yourself. Watch over your own deceitfulness and look into it every hour, every minute. Avoid being scornful, both to others and to yourself. What seems to you bad within you will grow purer from the very fact of your observing it in yourself. Avoid fear, too, though fear is only the consequence of every sort of falsehood. Never be frightened at your own faint-heartedness in attaining love. Don't be frightened overmuch even at your evil actions. I am sorry I can say nothing more consoling to you, for love in action is a harsh and dreadful thing compared with love in dreams. Love in dreams is greedy for immediate action, rapidly performed and in the sight of all. Men will even give their lives if only the ordeal does not last long but is soon over, with all looking on and applauding as though on the stage. But active love is labor and fortitude, and for some people too, perhaps, a complete science. But I predict that just when you see with horror that in spite of all your efforts you are getting farther from your goal instead of nearer to it—at that very moment I predict that you will reach it and behold clearly the miraculous power of the Lord who has been all the time loving and mysteriously guiding you."

....

"At some thoughts one stands perplexed, especially at the sight of men's sin, and wonders whether one should use force or humble love. Always decide to use humble love. If you resolve on that once for all, you may subdue the whole world. Loving humility is marvelously strong, the strongest of all things, and there is nothing else like it." 

....
"“Remember particularly that you cannot be a judge of anyone. For no one can judge a criminal until he recognizes that he is just such a criminal as the man standing before him, and that he perhaps is more than all men to blame for that crime. When he understands that, he will be able to be a judge. Though that sounds absurd, it is true. If I had been righteous myself, perhaps there would have been no criminal standing before me. If you can take upon yourself the crime of the criminal your heart is judging, take it at once, suffer for him yourself, and let him go without reproach. And even if the law itself makes you his judge, act in the same spirit so far as possible, for he will go away and condemn himself more bitterly than you have done. If, after your kiss, he goes away untouched, mocking at you, do not let that be a stumbling-block to you. It shows his time has not yet come, but it will come in due course. And if it come not, no matter; if not he, then another in his place will understand and suffer, and judge and condemn himself, and the truth will be fulfilled. Believe that, believe it without doubt; for in that lies all the hope and faith of the saints.”

This time, Chris and Camryn have moved me beyond words by living this reality.

Addendum/edit: more of this humbling love on display

Rittman Mead at ODTUG KScope’15, Hollywood Florida

Rittman Mead Consulting - Fri, 2015-06-19 05:50

NewImage

ODTUG KScope’15 is running in Hollywood, Florida next week and Rittman Mead are running a number of sessions during the week on OBIEE, Essbase, ODI and Big Data. I’ve personally been attending ODTUG KScope (or “Kaleidoscope”, as it used to be known) for many years now and it’s the best developer-centric conference we go to, coupled with amazing venues and a great community atmosphere.

Sessions we’re running over the week include:

  • Gianni Ceresa : 2-in-1: RPD Magic and Hyperion Planning “Adapter”
  • Jerome : Manage Your Oracle Data Integrator Development Lifecycle
  • Michael Rainey : Practical Tips for Oracle Business Intelligence Applications 11g Implementations
  • Michael Rainey : GoldenGate and Oracle Data Integrator: A Perfect Match
  • Mark Rittman : Bringing Oracle Big Data SQL to OBIEE and ODI
  • Mark Rittman : End-to-End Hadoop Development Using OBIEE, ODI, and Oracle Big Data
  • Mark Rittman : Thursday Deep Dive – Business Intelligence: Bringing Oracle Tools to Big Data
  • Andy Rocha & Pete Tamisin : OBIEE Can Help You Achieve Your GOOOOOOOOOALS!

We’ll also be taking part in various “Lunch and Learn” sessions, community and ACE/ACE Director events, and you can also talk to us about our new OBIEE “User Engagement” initiative and how you can get involved as an early adopter. Details and agenda for KScope’15 can be found on the event website, and if you’re coming we’ll look forward to seeing you in sunny Hollywood, Florida!

Categories: BI & Warehousing

Just found

Gerd Volberg - Fri, 2015-06-19 05:26

now I need a floppy drive for 3,5"

:-)
Gerd

Old ventures and new adventures

Tanel Poder - Fri, 2015-06-19 00:08

I have some news, two items actually.

First, today (it’s still 18th June in California) is my blog’s 8th anniversary!

I wrote my first blog post, about Advanced Oracle Troubleshooting, exactly 8 years ago, on 18th June 2007 and have written 229 blog posts since. I had started writing and accumulating my TPT script collection a couple of years earlier and now it has over 1000 files in it! And no, I don’t remember what all of them do and even why I had written them. Also I haven’t yet created an index/documentation for all of them (maybe on the 10th anniversary? ;)

Thanks everyone for your support, reading, commenting and the ideas we’ve exchanged over all these years, it’s been awesome to learn something new every single day!

You may have noticed that I haven’t been too active in online forums nor blogging much in the last couple of years, which brings me to the second news item(s):

I’ve been heavily focusing on Hadoop. It is the future. It will win, for the same reasons Linux won. I moved to US over a year ago and am currently in San Francisco. The big data hype is the biggest here. Except it’s not hype anymore; and Hadoop is getting enterprise-ready.

I am working on a new startup. I am the CEO who still occasionally troubleshoots stuff (must learn something new every day!). We officially incorporated some months ago, but our first developers in Dallas and London have been busy in the background for over a year. By now we are beta testing with our most progressive customers ;-) We are going to be close partners with old and new friends in modern data management space and especially the awesome folks in Accenture Enkitec Group.

The name is Gluent. We glue together the old and new worlds in enterprise IT. Relational databases vs. Hadoop. Legacy ETL vs. Spark. SAN storage vs. the cloud. Jungles of data feeds vs. a data lake. I’m not going to tell you any more as we are still in stealth mode ;-)

Now, where does this leave Oracle technology? Well, I think it still kicks ass and it ain’t going away! In fact we are betting on it. Hadoop is here to stay, but your existing systems aren’t going away any time soon.

I wouldn’t want to run my critical ERP or complex transactional systems on anything other than Oracle. Want real time in-memory reporting on your existing Oracle OLTP system – with immediate consistency, not a multi-second lag: Oracle. Oracle is the king of complex OLTP and I don’t see it changing soon.

So, thanks for reading all the way to the end – and expect to hear much more about Gluent in the future! You can follow @GluentInc Twitter handle to be the first to hear any further news :-)

 

Flushing Shared Pool Does Not Slow Its Growth

Bobby Durrett's DBA Blog - Thu, 2015-06-18 17:14

I’m still working on resolving the issues caused by bug 13914613.

Oracle support recommended that we apply a parameter change to resolve the issue but that change requires us to bounce the database  and I was looking for a resolution that does not need a bounce.  The bug caused very bad shared pool latch waits when the automatic memory management feature of our 11.2.0.3 database expanded the shared pool.  Oracle support recommending setting _enable_shared_pool_durations=false and I verified that changing this parameter requires a bounce.  It is a big hassle to bounce this database because of the application so I thought that I might try flushing the shared pool on a regular basis so the automatic memory management would not need to keep increasing the size of the shared pool.  The shared pool was growing in size because we have a lot of SQL statements without bind variables.  So, I did a test and in my test flushing the shared pool did not slow the growth of the shared pool.

Here is a zip of the scripts I used for this test and their outputs: zip

I set the shared pool to a small value so it was more likely to grow and I created a script to run many different sql statements that don’t use bind variables:

spool runselects.sql

select 'select * from dual where dummy=''s'
||to_char(sysdate,'HHMISS')||rownum||''';'
from dba_objects;

spool off

@runselects

So, the queries looked like this:

select * from dual where dummy='s0818111';
select * from dual where dummy='s0818112';
select * from dual where dummy='s0818113';
select * from dual where dummy='s0818114';
select * from dual where dummy='s0818115';
select * from dual where dummy='s0818116';
select * from dual where dummy='s0818117';

I ran these for an hour and tested three different configurations.  The first two did not use the _enable_shared_pool_durations=false setting and the last did.  The first test was a baseline that showed the growth of the shared pool without flushing the shared pool.  The second test including a flush of the shared pool every minute.  The last run included the parameter change and no flush of the shared pool.  I queried V$SGA_RESIZE_OPS after each test to see how many times the shared pool grew.  Here is the query:

SELECT OPER_TYPE,FINAL_SIZE Final,
to_char(start_time,'dd-mon hh24:mi:ss') Started, 
to_char(end_time,'dd-mon hh24:mi:ss') Ended 
FROM V$SGA_RESIZE_OPS
where component='shared pool'
order by start_time,end_time;

Here are the results.

Baseline – no flush, no parameter change:

OPER_TYPE       FINAL STARTED         ENDED
--------- ----------- --------------- ---------------
GROW      150,994,944 18-jun 05:03:54 18-jun 05:03:54
GROW      134,217,728 18-jun 05:03:54 18-jun 05:03:54
STATIC    117,440,512 18-jun 05:03:54 18-jun 05:03:54
GROW      167,772,160 18-jun 05:04:36 18-jun 05:04:36
GROW      184,549,376 18-jun 05:47:38 18-jun 05:47:38

Flush every minute, no parameter change:

OPER_TYPE       FINAL STARTED         ENDED
--------- ----------- --------------- ---------------
GROW      134,217,728 18-jun 06:09:15 18-jun 06:09:15
GROW      150,994,944 18-jun 06:09:15 18-jun 06:09:15
STATIC    117,440,512 18-jun 06:09:15 18-jun 06:09:15
GROW      167,772,160 18-jun 06:09:59 18-jun 06:09:59
GROW      184,549,376 18-jun 06:22:26 18-jun 06:22:26
GROW      201,326,592 18-jun 06:42:29 18-jun 06:42:29
GROW      218,103,808 18-jun 06:47:29 18-jun 06:47:29

Parameter change, no flush:

OPER_TYPE        FINAL STARTED         ENDED
--------- ------------ --------------- ---------------
STATIC     117,440,512 18-jun 07:16:09 18-jun 07:16:09
GROW       134,217,728 18-jun 07:16:18 18-jun 07:16:18

So, at least in this test – which I have run only twice – flushing the shared pool if anything makes the growth of the shared pool worse.  But, changing the parameter seems to lock it in.

– Bobby

Categories: DBA Blogs

Oracle Priority Support Infogram for 18-JUN-2015

Oracle Infogram - Thu, 2015-06-18 17:05

RDBMS
How does Oracle reuse the Expired and Unexpired undo extents? from Toad World.
12c new feature [PDB save state], from MAA/HA Solutions.
Big Data
In what has to be the most focused name for a blog I’ve ever seen, the Adding Location and Graph Analysis to Big Data blog lets us know that you may: Want to try out Oracle Big Data Spatial and Graph?
Big Data Spatial and Graph Analytics for Hadoop, from BI & EPM Partner Community EMEA.

WebLogic
Managing Logs in WebLogic, from The WebLogic Server Blog.
Diagnosing performance issues front to back-end in WebLogic Server applications with Java Flight Recorder, from WebLogic Partner Community EMEA.
Java
What's Coming with JSF 2.3?, from The Aquarium.
Java Stored Procedures and SQLJUTL2.INVOKE, from Jeff Taylor’s Weblog.
SOA
A couple of goodies from Capgemini:
5 Best practices for SoapUI Pro and Oracle SOA Suite.
and
Oracle SOA Suite Metadata Repository Performance Management
And from the Oracle SOA Suite - Team Blog: Using Oracle Service Bus with Apache Kafka
Security
From Security Inside Out: Database Administrators –the Undercover Security Superheroes. The DBA and SysAdmin are the two pillars of security in most organizations. It pays to have DBAs not leave the main security burden on the SysAdmin’s shoulders. It’s an increasingly dangerous world out there. Keep your digital powder dry.
Supply Chain
OTM Best Practices Pages, from the Oracle Supply Chain Management Blog.
Demantra
Demantra Certification Study Guides, Exam Preparation, from the Demantra blog.
EBS
From the Oracle E-Business Suite Support blog:
Webcast: Outside Processing (OSP) for OPM in 12.2.4
From the Oracle E-Business Suite Technology blog:
EBS 12.2 Certified on Oracle Linux 7 and Red Hat Enterprise Linux 7
DSTv24 Timezone Patches Available for E-Business Suite
Are We Replacing Workflow in EBS with BPEL Process Manager?


Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBengaliBelarusianBulgarianCatalanChinese (Simp)Chinese (Trad)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKannadaKoreanLaoLatinLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTamilTeluguThaiTurkishUkrainianUrduVietnameseWelshYiddishAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBengaliBelarusianBulgarianCatalanChinese (Simp)Chinese (Trad)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKannadaKoreanLaoLatinLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTamilTeluguThaiTurkishUkrainianUrduVietnameseWelshYiddish Text-to-speech function is limited to 100 charactersOptions : History : Help : Feedback

Register Now - OTN Free Virtual Technology Summit!

OTN TechBlog - Thu, 2015-06-18 16:55
The Oracle Technology Network (OTN) is excited to invite you to the next Virtual Technology Summit. Learn firsthand from Oracle ACEs, Java Champions, and Oracle product experts, as they share their insight and expertise on using Oracle technologies to meet today’s IT challenges. Learn through Hands on Labs and Technical Presentations / Demo’s. 

Register now!

•    July 14, 2015 – 9am to 12:30 PT  

•    July 15, 2015 - 9am to 12:30pm BST 

•    July 23, 2015 - 9:30am to 1:00pm IST 

View Agenda/Abstracts HERE


This interactive, online event offers four highly technical tracks, each with a unique focus on specific tools, technologies, best practices and tips:


Java - It's All about Innovation: In its 20th year, Java is used by over 9 million developers world-wide in every major industry. Learn all about Java innovation. You will discover how to program a parallel application with Java 8 lambdas, build a robot with 3D printed parts and use Docker, a best-in-class platform to test and manage releases.


Database - Mastering Oracle Database Technologies: Oracle Database 12c delivers market-leading security, high performance, availability and scalability for Cloud Application deployments. This event offers two Database tracks; one focused on Cloud Application development and deployment practices and the other on developing and deploying .Net applications on the Oracle platform. Sessions focus on Oracle Database Cloud Services, Oracle .Net development tools and technologies and more.

Middleware - It's All About Oracle WebLogic!: The Middleware track brings together three experts on Oracle Fusion Middleware to present how-to technical sessions on WebLogic Server's role in today's middleware architectures. The sessions will focus on security and authentication, service monitoring and exploration, and on WebLogic 12c's new APIs and tools for application development. Other products and technologies covered include Oracle SOA Suite, Service Bus, JMX, JAX-RS, JSON, WebSocket and more.

Operating Systems, Virtualization Technologies, and Hardware - Systems Security: Oracle Systems software is engineered for deployment in the most demanding IT settings. From Oracle Solaris, to Open Stack to Oracle Linux, Oracle has engineered their systems platforms with security as an essential element. Learn from Oracle security experts about the secure by default features and layers of defense built into Oracle Systems and defense-in-depth best practices.


Become a member of the OTN Community: Register here to start participating in our online community. Share your expertise with other community members!


NEW REWARDS! If you attend this virtual technology summit and are a member of the Oracle Technology Network Community you will earn 150 points towards our new Rewards and Recognition program (use the same email for both). Read all about it in our FAQ: Oracle Community – Rewards & Recognition FAQ.

Replicating Hive Data Into Oracle BI Cloud Service for Visual Analyzer using BICS Data Sync

Rittman Mead Consulting - Thu, 2015-06-18 15:26

In yesterday’s post on using Oracle Big Data Discovery with Oracle Visual Analyzer in Oracle BI Cloud Service, I said mid-way through the article that I had to copy the Hadoop data into BI Cloud Service so that Visual Analyzer could use it; at present Oracle Visual Analyzer is only available as part of Oracle BI Cloud Service (BICS) so at some point the data prepared by Big Data Discovery had to be moved into BICS so that Visual Analyzer (VA) could access it. In the future once Visual Analyzer is available on-premise as part of Oracle Business Intelligence 12c we’ll be able to connect the on-premise RPD directly to Hadoop via the Cloudera Impala ODBC driver, but for now to get this early access to VA features we’re going to have to copy the data up to BICS and report on it from there. So how does this work?

With this second release of BICS there are actually a number of ways to get on-premise data up into BICS’s accompanying database service:

  • As before, you can export data as CSV or an Excel Spreadsheet, and upload it manually into BICS using the Data Load feature (the approach I took in this recent Oracle Magazine article)
  • You can use SQL*Developer to SFTP “carts” of Oracle database data up into BICS, where it’ll then be unpacked and used to create Oracle tables in the accompanying database
  • You can now also connect BICS to the full Oracle Database-as-a-Service, a full database rather than a single schema that also provides a SQL*Net connection that ETL tools can connect to, for example ODI or Informatica
  • And there’s now a new utility called “Data Sync” that we’ll use in this example, to replicate tables or files up into BICS’s database store with options for incremental refresh, drop-and-reload and so forth

In our case the situation is a bit more complicated in that our data sits in a Hadoop cluster, as Hive tables that we’re accessing through the Cloudera Impala MPP engine. OBIEE 11.1.1.9 can actually connect directly to Impala and if we were just using Answers and Dashboards we wouldn’t have any more work to do, but as we’re using VA through BICS and BICS can’t access on-premise data sources, we need some way of copying the data up into BICS so VA can access it. Again, there’s many ways you can get data out of Hive on Hadoop and into databases and files, but the approach I took is this:

  1. First export each of the Hive tables I accessed through the on-premise RPD into CSV files, in my case using the Hue web-based user interface in CDH5
  2. Then use the Data Sync to upload the contents of those CSV files to BICS’s database store, selecting the correct Oracle datatypes for each of the columns
  3. Do any modeling on those tables to add any sequences or keys that I’m going to need when working with BICS’s more simplistic RPD modeller
  4. Then create a replica (or as close to replica) RPD model in BICS to support the work I’m going to want to do with VA

Again, there are also other ways to do this – another option is to just lift-and-shift the current 11.1.1.9 RPD up into BICS, and replicate the Hive/CSV data into Oracle Database-as-a-Service and then repoint the uploaded RPD to this service, but I haven’t got a DBaaS instance to-hand and I think it’d be more useful to replicate using BICS and recreate the RPD manually – as that’s what most customers using BICS will end-up doing. So the first step then is to export the Hive data out into CSV files using Hue, by first running a SELECT * FROM … for each table, then using the menu option to export the query results to a CSV file on my workstation.

NewImage

Then it’s a case of setting up BICS Data Sync to first connect to my BICS cloud instance, and then selecting one-by-one the CSV files that I’ll be uploading into BICS via this tool.

NewImage

Of course anyone who’s been around Oracle BI for a while will recognise Data Sync as being built on the DAC, the ETL orchestration tool that came with the 7.9.x releases of BI Apps and worked in-conjunction with Informatica PowerCenter to load data into the BI Apps data warehouse. The DAC is actually a pretty flexible tool (disclaimer – I know the development PMs at Redwood Shores and think they’re a pretty cool bunch of people) and more recently it gained the ability to replicate BI Apps DW data into TimesTen for use with Exalytics, so it’s pluggable architecture and active development team meant it provided a useful platform to deliver something in-between BICS’s ApEX data uploader and fully-fledged ODI loading into Oracle DBaaS. The downside of using something built on the DAC is that the DAC had some UI “quirks”, but equally the upside is that if you know the DAC, you can pretty much pick up Data Sync and guess how it works.

As part of uploading each CSV file, I also get to sample the file contents and confirm the datatype choices that Data Sync has suggested; these can of course be amended, and if I’m bringing in data from Oracle, for example, I wouldn’t need to go through such an involved process. 

NewImage

Then it’s a case of uploading the data. In my case one of the tables uploaded OK first time, but an issue I hit was where Hive tables had numeric columns containing NULLs that got exported as the text “NULL” and then caused the load to fail when trying to insert them into numeric columns. Again, a bit of knowledge of how the DAC worked came in useful as I went through the log files and then restarted parts of the load – in the end I replaced the word NULL with an empty string and the loads then succeeded. 

NewImage

Now the data should be uploaded to BICS, you can check out the new tables and their contents either from within BICSs Data Modeller function, or from within the ApEx console that comes with BICS’s database part.

NewImage

One thing I did know I’d have to give some thought to was how to do the types of RPD modelling I’d done in the on-premise RPD, within the more constrained environment of the BICS data modeller. Looking back at the on-premise RPD I’ve made a lot of use of aliases to create fact and dimension versions of key log tables (posts, tweets) and multiple versions of the date dimensions, whereas in BICS you don’t get aliases but you can create database views. What was more worrying was that I’d used columns from the main webserver log table to populate both the main logical fact table and another dimension whilst still keeping a single source table as their physical source, but in BICS I’d have to create these two sources as views and then join them on a common key, which would be tricky as the log table in Hive didn’t have an obvious primary key. In the end I “cheated” a bit and created a derived copy of the incoming log file table with a sequence number added to it, so that I could then join both the derived fact table and dimension table on this synthetic unique key column.

NewImage

Now it’s a case of modelling out the various incoming tables uploaded via Data Sync into the facts and dimensions that the BICS data model will use; again something to be aware of is that each of these tables will need to join to its relevant dimensions or facts, so you need to leave the joining keys in the fact table rather than remove them as you’d do when creating logical fact tables in on-premise OBIEE.

NewImage

Tables that only perform one role, for example the IMP_RM_POSTS_VA table that contains details of all blog posts and web pages on our site, can be brought into the model as they are without creating views. For the second time when I add in the time dimension table, this time to create a time dimension role table for the Tweets fact table, I have to create a view over the table that performs a similar role to alias tables in on-premise OBIEE, and I’m then directed to create a fact or dimension object in the model from that view.

NewImage

Once this is all done, I end up with a basic BICS data model that’s starting to look like the one I had with the on-premise OBIEE install.

NewImage

Then finally, once I’d amended all the column names, brought in all of the additional columns and lookup tables to provide for example lists of Twitter user handles, I could then view the model in BICS’s Visual Analyzer and start produce data visualisation projects off of it.

NewImage

So – it’s admittedly a bit convoluted in the first iteration but once you’ve set up the BICS data model and the Data Sync upload process, you can use DataSync to refresh the replicated Hive table data in the BICS database instance and keep the two systems in-sync. As I said, OBIEE12c will come with Visual Analyzer as part of the on-premise install, but until then this is the way we link VA to Big Data Discovery on Hadoop to enable Visual Analyzer access to BDD’s enriched datasets.

Categories: BI & Warehousing

SQL Server 2016 : availability groups and load balancing features

Yann Neuhaus - Thu, 2015-06-18 14:53

Let’s continue with this third post about SQL Server AlwaysOn and availability groups.

Others studies are available here:

 

This time I’ll talk about read-only secondaries and the new load-balancing support that will be introduced by SQL Server 2016.

First of all, SQL Server 2014 improved the read-only secondary availability by solving the issue related to secondary accessibility when the primary is offline. However, the redirection to a readable secondary was still basic because it concerned only the first secondary replica defined in the configured priority list. So, unless using a third-party tool it was not possible to use very efficiently all of the resources available from secondaries. Fortunately, the next SQL Server version will change the game by introducing native load-balancing capabilities.

In order to be able to use this new feature, you must define:

  • The list of possible secondary replicas
  • A read-only route for each concerned replica
  • A routing list that include read-only replicas and load-balancing rules

At this point I admit to expect a GUI for configuring both read-only routes and the routing list rules in a user friendly fashion even if I prefer using T-SQL to be honest. But anyway, let’s try to configure secondary replicas in round-robin fashion as follows:

 

/* enable read-only secondary replicas */ ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL161.dbi-services.test:1433') ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL162.dbi-services.test:1433') ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQL163.dbi-services.test:1433') ); GO   /* configure replicas priority list */ ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL161' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL162','SQL163'))) );   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL162' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL161','SQL163'))) ); GO   ALTER AVAILABILITY GROUP [2016Grp] MODIFY REPLICA ON N'SQL163' WITH (        PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('SQL162','SQL161'))) ); GO

 

My test lab includes 3 replicas (SQL161, SQL162 and SQL163). The secondaries will be used as read-only replicas with the new load-balancing feature.

Note the double brackets around the replicas list that defines the load-balancing mechanism for the concerned replicas. In my context, I have only two read-only replicas but rules are defined as follows:

  • (replica1, replica2, replica3): no load-balancing capabilities in this case. The first replica will be used, then the second and finally the third.
  • ((replica1, replica2), replica3): replica1 and replica will be used in a round-robin fashion. The replica3 will be used only if both replica1 and replica2 are not available.

Now let’s play with this new infrastructure by using sqlcmd command as follows:

 

blog_52_-_1-_sqlcmd_readonly

 

As reminder, you have to meet some others requirements in order to use correctly the transparent redirection to a secondary replica as using TCP protocol, referencing directly the availability group listener and the concerned database as well and setting the application intent attribute as readonly. So in my case, I reference directly the LST-2016 listener and the killerdb. I use also the –K parameter with READONLY attribute. Finally, I run the query SELECT @@SERVERNAME in order to know which replica I am after login.

I ran this command several times and I can state that the load-balancing feature plays its full role.

 

blog_52_-_2-_sqlcmd_tests

 

However, let’s play now with the following PowerShell script:

 

Clear-Host;   $dataSource = “LST-2016"; $database = "killerdb"; $connectionString = "Server=tcp:$dataSource;Integrated Security=SSPI;Database=$database;ApplicationIntent=ReadOnly”;    $i = 0;   while ($i -le 3) {        Write-Host "Test connexion initial server nb : $i - $dataSource " -NoNewline;    Write-Host "";    Write-Host "";      Try    {        $connection = New-Object System.Data.SqlClient.SqlConnection;        $connection.ConnectionString = $connectionString;          $connection.Open();          $sqlCommandText="SELECT 'Current server : ' + @@SERVERNAME as server_name";        $sqlCommand = New-Object system.Data.sqlclient.SqlCommand($sqlCommandText,$connection);        $sqlCommand.ExecuteScalar();          $connection.Close();          $sqlCommand.Dispose();        $connection.Dispose();      }    Catch [Exception]    {        Write-Host "KO" -ForegroundColor Red;        Write-Host $_.Exception.Message;    }      Write-Host "";      Start-Sleep 3;      $i++; }

 

The result is not the same. The redirection to a read-only replica works perfectly but there was not load-balancing mechanism in action this time as shown below:

 

blog_52_-_3-_powershell_tests

 

 

What’s going on in the case? In fact and to be honest, I didn’t remember that PowerShell uses connection pooling by default (thanks to Brent Ozar - @BrentO to put me on the right track).

Let’s take a look at the output of an extended event session that includes the following events:

  • sqlserver.login
  • sqlserver.logout
  • sqlserver.read_only_route_complete
  • sqlserver.rpc_completed

 

blog_52_-_4-_xe_sqlcmd

 

You can notice that sqlcmd tool doesn’t use connection pooling (is_cached column = false). In this case for each run, SQL Server will calculate the read-only route.

However for my PowerShell script the story is not the same as shown below:

 

blog_52_-_5-_xe_pw

 

The first connection is not pooled and we can noticed only one read-only route calculation from SQL Server. All of the next connections are pooled and technically they are still alive on the SQL Server instance. This is why the load balancing mechanism is not performed in this case. So this is an important point to keep in mind if you want to plan to benefit to this new feature.

I also had a dream: Having a real load-balancing feature based on resource scheduling algorithm… maybe the next step? :)

See you