Skip navigation.

Feed aggregator

The First PASS Summit Bloggers’ Meetup

Pythian Group - Fri, 2014-10-31 13:02

We are stoked to announce the first ever PASS Summit Bloggers Meetup!

What: PASS Summit Bloggers Meetup 2014
When: Thursday, November 6th, 5pm – 7pm
Where: Sports Restaurant & Bar, 140 4th Ave North, Suite 130, Seattle, WA 98109
How: Please comment with “COUNT ME IN” if coming — we need to know attendance numbers.

We’re excited to meet old friends, and make new ones in Seattle this year. Pythian will sponsor munchies and drinks. There will be a networking contest with some cool prizes, plus everyone will get their very own PASS Summit Bloggers Meetup shirt! Meetups are a great way to make new friends in the community, so come on out — all bloggers are welcome!

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us ensure we have the attendance numbers right. Please provide your blog URL with your comment — it’s a Bloggers Meetup after all! Make sure you comment here if you are attending so that we have enough room, food, and drinks.

The location is perfect to get ready for the appreciation event — minutes walk from EMP Museum! Snacks and drinks before the big event and mingle with fellow bloggers. What can be better?

Of course, do not forget to blog and tweet about this year’s bloggers meetup using #Summit14 #sqlpass. See you there!

Categories: DBA Blogs

first_rows(10)

Jonathan Lewis - Fri, 2014-10-31 11:31

No, not the 10th posting about first_rows() this week – whatever it may seem like – just an example that happens to use the “calculate costs for fetching the first 10 rows” optimizer strategy and does it badly. I think it’s a bug, but it’s certainly a defect that is a poster case for the inherent risk of using anything other than all_rows optimisation.  Here’s some code to build a couple of sample tables:


begin
	dbms_stats.set_system_stats('MBRC',16);
	dbms_stats.set_system_stats('MREADTIM',12);
	dbms_stats.set_system_stats('SREADTIM',5);
	dbms_stats.set_system_stats('CPUSPEED',1000);
end;
/

create table t1
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
)
select
	rownum					id,
	trunc(dbms_random.value(1,1000))	n1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6
;

create index t1_n1 on t1(id, n1);

create table t2
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
)
select
	rownum					id,
	trunc(dbms_random.value(10001,20001))	x1,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6
;

create index t2_i1 on t2(x1);

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		estimate_percent => 100,
		method_opt	 => 'for all columns size 1'
	);

end;
/


create or replace view  v1
as
select 
	id, n1, small_vc, padding
from	t1 
where	n1 between 101 and 300
union all
select 
	id, n1, small_vc, padding
from	t1 
where	n1 between 501 and 700
;

The key feature of this demonstration is the UNION ALL view and what the optimizer does with it when we have first_rows_N optimisation – this is a simplified model of a production problem I was shown a couple of years ago, so nothing special, nothing invented. Here’s a query that behaves badly:


select
	/*+ gather_plan_statistics */
	v1.small_vc,
	v1.n1
from
	v1,
	t2
where
	t2.id = v1.id
and	t2.x1 = 15000
;

I’m going to execute this query in three different ways – as is, using all_rows optimisation; as is, using first_rows_10 optimisation, then using all_rows optimisation but with the necessary hints to make it follow the first_rows_10 execution path. Here are the resulting plans from an instance of 12.1.0.2 (the same thing happens in 11.2.0.4):


first_rows_10 plan
----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |     1 |    35 |   107   (0)| 00:00:01 |
|*  1 |  HASH JOIN                           |       |     1 |    35 |   107   (0)| 00:00:01 |
|   2 |   VIEW                               | V1    |    24 |   600 |     4   (0)| 00:00:01 |
|   3 |    UNION-ALL                         |       |       |       |            |          |
|*  4 |     TABLE ACCESS FULL                | T1    |    12 |   240 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL                | T1    |    12 |   240 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN                  | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

all_rows plan
------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |    40 |  1400 |   904   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                          |       |    40 |  1400 |   904   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |   100 |  1000 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                    | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |   VIEW                                 | V1    |     1 |    25 |     8   (0)| 00:00:01 |
|   5 |    UNION ALL PUSHED PREDICATE          |       |       |       |            |          |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    20 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | T1_N1 |     1 |       |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    20 |     4   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN                  | T1_N1 |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

first_rows_10 plan hinted under all_rows optimisation
---------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |       |   200 |  8600 |       |  6124   (3)| 00:00:01 | 
|*  1 |  HASH JOIN                   |       |   200 |  8600 |    17M|  6124   (3)| 00:00:01 |
|   2 |   VIEW                       | V1    |   402K|    12M|       |  5464   (3)| 00:00:01 | 
|   3 |    UNION-ALL                 |       |       |       |       |            |          | 
|*  4 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2731   (3)| 00:00:01 | 
|*  5 |     TABLE ACCESS FULL        | T1    |   201K|  3933K|       |  2733   (3)| 00:00:01 | 
|   6 |   TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |       |   103   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

I’m not sure why the first_rows_10 plan uses “table access by rowid batched”, but I’d guess it’s because the optimiser calculates that sorting the index rowids before visiting the table may have a small benefit on the speed of getting the first 10 rows – eventually I’ll get around to checking the 10053 trace file. The important thing, though, is the big mistake in the strategy, not the little difference in table access.

In the first_rows_10 plan the optimizer has decided building an in-memory hash table from the UNION ALL of the rows fetched from the two copies of the t1 table will be fast and efficient; but it’s made that decision based on the assumption that it will only get 10 rows from each copy of the table – and at run-time it HAS to get all the relevant t1 rows to build the hash table before it can get any t2 rows. We can get some idea of the scale of this error when we look at the hinted plan under all_rows optimisation – it’s a lot of redundant data and a very expensive hash table build.

In contrast the all_rows plan does an efficient indexed access into the t2 table then, for each row, does a join predicate pushdown into the union all view using an indexed access path. If we only wanted to fetch 10 rows we could stop after doing a minimum amount of work. To demonstrate the error more clearly I’ve re-run the experiment for the first two plans from SQL*PLus, setting the arraysize to 11, the pagesize to 5, and stopping after the first 10 rows. Here are the plans showing the rowsource execution stats:


first_rows_10 plan
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |        |   107 (100)|     12 |00:00:00.43 |   35150 |       |       |          |
|*  1 |  HASH JOIN                           |       |      1 |      1 |   107   (0)|     12 |00:00:00.43 |   35150 |    24M|  3582K|   23M (0)|
|   2 |   VIEW                               | V1    |      1 |     24 |     4   (0)|    400K|00:00:06.48 |   35118 |       |       |          |
|   3 |    UNION-ALL                         |       |      1 |        |            |    400K|00:00:04.20 |   35118 |       |       |          |
|*  4 |     TABLE ACCESS FULL                | T1    |      1 |     12 |     2   (0)|    200K|00:00:00.71 |   17559 |       |       |          |
|*  5 |     TABLE ACCESS FULL                | T1    |      1 |     12 |     2   (0)|    200K|00:00:00.63 |   17559 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |      1 |    100 |   103   (0)|     28 |00:00:00.01 |      32 |       |       |          |
|*  7 |    INDEX RANGE SCAN                  | T2_I1 |      1 |    100 |     3   (0)|     28 |00:00:00.01 |       4 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

all_rows plan
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |      1 |        |   904 (100)|     12 |00:00:00.01 |     213 |
|   1 |  NESTED LOOPS                          |       |      1 |     43 |   904   (1)|     12 |00:00:00.01 |     213 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T2    |      1 |    100 |   103   (0)|     28 |00:00:00.01 |      32 |
|*  3 |    INDEX RANGE SCAN                    | T2_I1 |      1 |    100 |     3   (0)|     28 |00:00:00.01 |       4 |
|   4 |   VIEW                                 | V1    |     28 |      1 |     8   (0)|     12 |00:00:00.01 |     181 |
|   5 |    UNION ALL PUSHED PREDICATE          |       |     28 |        |            |     12 |00:00:00.01 |     181 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     28 |    212K|     4   (0)|      8 |00:00:00.01 |      93 |
|*  7 |      INDEX RANGE SCAN                  | T1_N1 |     28 |      1 |     3   (0)|      8 |00:00:00.01 |      85 |
|   8 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     28 |    213K|     4   (0)|      4 |00:00:00.01 |      88 |
|*  9 |      INDEX RANGE SCAN                  | T1_N1 |     28 |      1 |     3   (0)|      4 |00:00:00.01 |      84 |
-----------------------------------------------------------------------------------------------------------------------

If I had set the optimizer_mode to first_rows_10 because I really only wanted to fetch (about) 10 rows then I’ve managed to pay a huge overhead in buffer visits, memory and CPU for the privilege – the all_rows plan was much more efficient.

Remember – we often see cases where the first_rows(n) plan will do more work to get the whole data set in order to be able to get the first few rows more quickly (the simplest example is when the optimizer uses a particular index to get the first few rows of a result set in order without sorting rather than doing a (faster) full tablescan with sort. This case, though, is different: the optimizer is choosing to build a hash table as if it only has to put 10 rows into that hash table when it actually HAS to build the whole has table before it can take any further steps – we don’t get 10 rows quicker and the rest more slowly; we just get 10 very slow rows.

Footnote:

It’s possible that this is an example of bug 9633142: (FIRST_ROWS OPTIMIZER DOES NOT PUSH PREDICATES INTO UNION INLINE VIEW) but that’s reported as fixed in 12c, with a couple of patches for 11.2.0.2/3. However, setting “_fix_control”=’4887636:off’, does bypass the problem. (The fix control, introduced in 11.1.0.6 has description: “remove restriction from first K row optimization”)


Data Warehouse Appliance Offerings

Chris Foot - Fri, 2014-10-31 11:15

Introduction

Information Technology units will continue to be challenged by the unbridled growth of their organization’s data stores. An ever-increasing amount of data needs to be extracted, cleansed, analyzed and presented to the end user community. Data volumes that were unheard of a year ago are now commonplace. Day-to-day operational systems are now storing such large amounts of data that they rival data warehouses in disk storage and administrative complexity. New trends, products, and strategies, guaranteed by vendors and industry pundits to solve large data store challenges, are unveiled on a seemingly endless basis.

Choosing the Large Data Store Ecosystem

Choosing the correct large data store ecosystem (server, storage architecture, OS, database) is critical to the success of any application that is required to store and process large volumes of data. This decision was simple when the number of alternatives available was limited. With the seemingly endless array of architectures available, that choice is no longer as clear cut. Database administrators now have more choices available to them than ever before. In order to correctly design and implement the most appropriate architecture for their organization, DBAs must evaluate and compare large data store ecosystems and not the individual products.

Traditional Large Data Store Technologies

Before we begin our discussion on the various advanced vendor offerings, we need to review the database features that are the foundation of the customized architectures we will be discussing later in this article. It is important to note that although each vendor offering certainly leverages the latest technologies available, the traditional data storage and processing features that DBAs have been utilizing for years remain critical components of the newer architectures.

Partitioning

Partitioning data into smaller disk storage subsets allows the data to be viewed as a single entity while overcoming many of the challenges associated with the management of large data objects stored on disk.

Major database vendor products offer optimizers that are partition aware and will create query plans that access only those partitioned objects needed to satisfy the query’s data request (partition pruning). This feature allows administrators to create large data stores and still provide fast access to the data.

Partitioning allows applications to take advantage of “rolling window” data operations. Rolling windows allow administrators to roll off what is no longer needed. For example, a DBA may roll off the data in the data store containing last July’s data as they add this year’s data for July. If the data is ever needed again, administrators are able to pull the data from auxiliary or offline storage devices and plug the data back into the database.

Query Parallelism

Query parallelism improves data access performance by splitting work among multiple CPUs. Most database optimizers are also parallel aware and are able to break up a single query into sub queries that access the data simultaneously.

Without parallelism, a SQL statement’s work is performed by a single process. Parallel processing allows multiple processes to work together to simultaneously process a single SQL statement or utility execution. By dividing the work necessary to process a statement among multiple CPUs, the database can execute the statement more quickly than if the work was single-threaded.

The parallel query option can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from the parallel query option because query processing can be effectively split up among many CPUs on a single system.

Advanced Hardware and Software Technologies

Let’s continue our discussion by taking a high-level look at the advanced data warehouse offerings from the three major database competitors, Oracle, Microsoft and IBM. Each of the vendors’ offerings are proprietary data warehouse ecosystems, often called appliances, that consist of hardware, OS and database components. We’ll complete our review by learning more about Hadoop’s features and benefits.

Oracle Exadata

Oracle’s Exadata Machine combines their Oracle database with intelligent data storage servers to deliver very high performance benchmarks for large data store applications. Exadata is a purpose-built warehouse ecosystem consisting of hardware, operating system and database components.

Oracle Exadata Storage Servers leverage high speed interconnects, data compression and intelligent filtering and caching features to increase data transfer performance between the database server and intelligent storage servers. In addition, the Exadata architecture is able to offload data intensive SQL statements to the storage servers to filter the results before the relevant data is returned to the database server for final processing.

Exadata uses PCI flash technology rather than flash disks. Oracle places the flash memory directly on the high speed PCI bus rather than behind slow disk controllers and directors. Each Exadata Storage Server includes 4 PCI flash cards that combine for a total of 3.2 TB of flash memory. Although the PCI flash can be utilized as traditional flash disk storage, it provides better performance when it is configured as a flash cache that sits in front of the disks. Exadata’s Smart Flash Cache will automatically cache frequently accessed data in the PCI cache, much like its traditional database buffer cache counterpart. Less popular data will continue to remain on disk. Data being sent to the PCI Flash cache is also compressed to increase storage capacity.

Exadata also offers an advanced compression feature called Hybrid Columnar Compression (HCC) to reduce storage requirements for large databases. Exadata offloads the compression/decompression workload to the processors contained in the Exadata storage servers.

These technologies enable Exadata to deliver high performance for large data stores accessed by both decision support and online operational systems. The Exadata machine runs an Oracle database which allows Oracle-based applications to be easily migrated. Oracle describes the Exadata architecture as “scale out” capable meaning multiple Exadata servers can be lashed together to increase computing and data access horsepower . Oracle RAC, as well as Oracle’s Automatic Storage Management (ASM), can be leveraged to dynamically add more processing power and disk storage.

Microsoft SQL Server PDW

SQL Server Parallel Data Warehouse (PDW) is a massively parallel processing (MPP) data warehousing appliance designed to support very large data stores. Like Oracle’s Exadata implementation, the PDW appliance’s components consist of the entire database ecosystem including hardware, operating system and database.

Database MPP architectures use a “shared-nothing” architecture, where there are multiple physical servers (nodes), with each node running an instance of the database and having its own dedicated CPU, memory and storage.

Microsoft PDW’s architecture consists of:

  • The MPP Engine
    • Responsible for generating parallel query execution plans and coordinating the workloads across the system’s compute nodes
    • Uses a SQL Server database to store metadata and configuration data for all of the databases in the architecture
    • In essence, it acts as the traffic cop and the “brain” of the PDW system
  • Computer Nodes
    • Each compute node also runs an instance of the SQL Server database
    • The compute nodes’ databases are responsible for managing the user data

As T-SQL is executed in the PDW system, the queries are broken up to run simultaneously over multiple physical nodes, which utilizes parallel execution to provide high performance data access. The key to the success when using PDW is to select the appropriate distribution columns that are used to intelligently distribute the data amongst the nodes. The ideal distribution column is one that is accessed frequently, is able to evenly distribute data based on the column’s values and has low volatility (doesn’t change a lot).

Microsoft Analytics Platform (APS)- Hadoop and SQL Server Integration

Microsoft’s Analytics Platform System (APS) combines massively parallel processing offering (PDW) with HDInsight, their version of Apache Hadoop. Microsoft has partnered with Hortonworks, a commercial Hadoop software vendor that provides a Windows-based, 100% Apache Hadoop distribution. Please see section below for more detailed information on the Hadoop engine.

Integrating a Hadoop engine into SQL Server allows Microsoft to capture, store, process and present both structured (relational) and unstructured (non-relational) data within the same logical framework. Organizations wanting to process unstructured data often turned to Apache Hadoop environments which required them to learn new data storage technologies, languages and an entirely new processing architecture.

Microsoft’s Polybase provides APS users with the ability to query both structured and non-structured data with a single T-SQL based query. APS application programmers are not required to learn MapReduce or HiveQL to access data stored in the APS platform. Organizations using APS do not incur the additional costs associated with to re-training their existing staff or hiring personnel with experience in Hadoop access methods.

IBM PureData Systems for Analytics

Not to be outdone by their database rivals, IBM also provides a proprietary appliance called IBM PureData System for Analytics. The system, powered by Netezza, once again, combines the hardware, database and storage into a single platform offering. Pure Data Analytics is an MPP system utilizing IBM Blade Servers and dedicated disk storage servers that, like its competitors, is able to intelligently distribute workloads amongst the processing nodes.

IBM leverages field-programmable gate arrays (FPGAs) which are used in their FAST engines. IBM runs the FAST engine on each node to provide compression, data filtering and ACID compliance on the Netezza systems. The real benefit of FAST is that the FPGA technology allows the engines to be custom tailored to the instructions that are being sent to them for processing. The compiler divides the query plan into executable code segments, called snippets, which are sent in parallel to the Snippet Processors for execution. The FAST engine is able to customize the filtering according to the snippet being processed.

IBM’s Cognos, Data Stage, and InfoSphere Big Insights software products are included in the offering. IBM’s goal is to provide a total warehouse solution, from ETL to final data presentation, to Pure Data Analytics users.

In addition, IBM also provides industry-specific warehouse offerings for banking, healthcare, insurance, retail and telecommunications verticals. IBM’s “industry models” are designed to reduce the time and effort needed to design data warehousing systems for the organizations in these selected business sectors. IBM provides the data warehouse design and analysis templates to accelerate the data warehouse build process. IBM consulting assists the customer to tailor the architecture to their organization’s unique business needs.

Non-Database Vendor Technologies

New “disruptive” products that compete with the traditional database vendor offerings continue to capture the market’s attention. The products range the spectrum from No-SQL products that provide easy access to unstructured data to entirely new architectures like Apache’s Hadoop.

Major database vendors will make every effort to ensure that disruptive technologies gaining market traction become an enhancement, not a replacement, for their traditional database offerings. Microsoft’s APS platform is an excellent example of this approach.

Apache Hadoop

Apache’s Hadoop is a software framework that supports data-intensive distributed applications under a free license. The Hadoop software clusters’ commodity servers offer scalable and affordable large-data storage and distributed processing features in a single architecture.

A Hadoop cluster consists of a single master and multiple worker nodes. The master provides job control and scheduling services to the worker nodes. Worker nodes provide storage and computing services. The architecture is distributed, in that the nodes do not share memory or disk.

A distributed architecture allows computing horsepower and storage capacity to be added without disrupting on-going operations. Hadoop’s controlling programs keep track of the data located on the distributed servers. In addition, Hadoop provides multiple copies of the data to ensure data accessibility and fault tolerance.

Hadoop connects seamlessly to every major RDBMS through open-standard connectors providing developers and analysts with transparent access through tools they are familiar with. When used simply as a large-data storage location, it is accessible through a variety of standards-based methods such as FUSE or HTTP. Hadoop also offers an integrated stack of analytical tools, file system management and administration software to allow for native exploration and mining of data.

The Hadoop architecture is able to efficiently distribute processing workloads amongst dozens and hundreds of cost-effective worker nodes. This capability dramatically improves the performance of applications accessing large data stores. Hadoop support professionals view hundreds of gigabytes as small data stores and regularly build Hadoop architectures that access terabytes and petabytes of structured and unstructured data.

One of Hadoop’s biggest advantages is speed. Hadoop is able to generate reports in a fraction of the time required by traditional database processing engines. The reductions can be measured by orders of magnitude. Because of this access speed, Hadoop is quickly gaining acceptance in the IT community as a leading alternative to traditional database systems when large data store technologies are being evaluated.

Wrap-up

As stated previously, there is an endless array of offerings that focus on addressing large data store challenges. Large data store architecture selection is the most important decision that is made during the warehouse development project. A correctly chosen architecture will allow the application to perform to expectations, have the desired functionality and be easily monitored and administered. Incorrect architecture decisions may cause one or more of the following problems to occur: poor performance, limited functionality, high total cost of ownership, complex administration and tuning, lack of scalability, poor vendor support, poor reliability/availability and so on. All market- leading database vendors understand the importance of addressing the challenges inherent with large data stores and have released new products and product enhancements designed to simplify administration and improve performance.

The post Data Warehouse Appliance Offerings appeared first on Remote DBA Experts.

USA War Casualties

Nilesh Jethwa - Fri, 2014-10-31 10:35

iCasualties.org maintains documented list of all fatalities for Iraq and Afghanistan wars.

Analysing the dataset for Afghanistan, we summarize the results by the year

NOTE: This contains only Afghanistan metrics. We will later update the visuals to reflect Iraq war.

 

Image

USA war fatalities by year

We are approaching the levels of 2002 and hope for the best that we don’t have to suffer another wars.

Here is another view by year and month

 

InfoCaptor : Analytics & dashboards

 

The dataset contains the age of each person died in the war so summarizing by Age

Image

War Deaths by Age

Checking it against the year

Image

Why so many young deaths between age 20 and 30 for the year 2014?

Image

Where did most of the deaths occur?

Image

 

Where were the soldiers from?

Image

Deaths by Rank

 

InfoCaptor : Analytics & dashboards

 

Cause of Death

Attack Types

Image

Image

Image

Helicopter Crash is the one of the top death cause in Non Hostile situations

ShareThis

Speaking at the Spanish Virtual PASS Chapter

Pythian Group - Fri, 2014-10-31 10:09

Title : Recuperación de desastres y soluciones de alta disponibilidad con SQL Server
Event link: http://globalspanish.sqlpass.org/Inicio.aspx?EventID=1846
Event description: “Esta presentación presenta las soluciones de recuperacion de desastres (Disaster Recovery) y alta disponibilidad (High Availability) con SQL Server y ofrece escenarios creativos por usar las soluciones para reportages (Reporting), BI y almacen de datos (Datawarehouse). ”

Please feel free to register!

Categories: DBA Blogs

Log Buffer #395, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-10-31 07:44

This Log Buffer Edition ventures out in the fields of Oracle, SQL Server and MySQL and picks some of the coolest blog posts for this week.

Oracle:

In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows.

There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy.

Work-around Instance Migration Limits of BPM Suite 11g.

Oracle Event Processing 12c: java errors when deploying a new OEP project.

Creating a WebLogic 12c Data Source Connection to Pivotal GemFireXD 1.3.

SQL Server:

It sounds simple enough. Either your column will always have a value or it may not. Yet somehow such a seemingly simple decision can become a never-ending debate where database schema begins to resemble superstition and designing effective tables seems more contentious than you expected it to be.

There’s a joke doing the rounds at SQL conferences and seminars: three DBAs walk into a NoSQL bar and leave when they can’t find a table.

Stairway to AlwaysOn Level 3: Infrastructure 101.

How to compare two databases and email the results to every one who needs to know.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA.

MySQL:

Set up an SSL-encrypted connection between Sphinx and MySQL.

Sometimes users ask for something that doesn’t really make sense. On the first glance. But then you start asking and realize that the user was right, you were wrong, and it is, actually, a perfectly logical and valid use case.

PECL/mysqlnd_ms needs updates for MySQL Group Replication.

Why should you migrate from MySQL to MariaDB?

The Perfect Server – CentOS 7 (Apache2, Dovecot, ISPConfig 3).

Categories: DBA Blogs

Open Source Virtualization Project at Risk [VIDEO]

Chris Foot - Fri, 2014-10-31 04:02

Transcript

Hi, welcome to RDX. Virtualization and cloud technology pretty much go hand-in-hand.

Many popular cloud providers, such as Amazon and Rackspace, use Xen, an open-source virtualization platform to optimize their environments.

According to Ars Technica, those behind the Xen Project recently released a warning to those using its platform. Apparently, a flaw within the program's hypervisor allows cybercriminals to corrupt a Xen virtual machine, or VM. From there, perpetrators could read information stored on the VM, or cause the server hosting it to crash. Monitoring databases hosted on Xen VMs is just one necessary step companies should take. Reevaluating access permissions and reinforcing encryption should also be priorities. 

Thanks for watching! Be sure to visit us next time for any other advice on security vulnerabilities. 

The post Open Source Virtualization Project at Risk [VIDEO] appeared first on Remote DBA Experts.

OSB12c: Errorhandling in REST

Darwin IT - Fri, 2014-10-31 03:39
Yesterday, I had an OSB consulting day at a customer. We looked into a REST service that was to be extended with update functionality. Since calling an update service of an EIS (Enterprise Information System) can go wrong with all sorts of errors, it is important to be able to return a fault-message with the errors, jason format.

Now in OSB12c it's very apparent how you define possible fault-messages and even how the should be formatted in JSON:

In this sample case we created a more or less simple xsd for faults (dutch: fouten). To test with different fault messages we simply duplicated the 'fouten' element in the xsd to 'fouten2'. You can assign different HTTP-status codes to the different fault.

So this is configuration is pretty simple and straight forward. But it is not quite clear in the documents how you would return a specific fault within your error-handlers in the pipeline.

Internally OSB works not only 'XML'-based but actually SOAP-based. So the trick in the end is to replace the body with a soap-fault message and the selection of the REST/JSON errormessage is done based on the structure of the document in the details-section of the SOAP-Fault. In the screen above, you would define for each fault message an xsd-element and apparently it validates the soap-fault-details content against each XSD defined, and the xsd against which the detail-content is valid points to the returned fault, with the corresponding HTTP Status.

So we created a XQuery transformation as follows:
xquery version "1.0" encoding "utf-8";

(:: OracleAnnotationVersion "1.0" ::)

declare namespace ns2="http://darwin-it.nl/doe/mobile/fouten";
(:: import schema at "../PS/Schemas/fouten.xsd" ::)
declare namespace ns1="http://xmlns.oracle.com/adf/svc/errors/";
(:: import schema at "../BS/Schemas/XMLSchema_-130439696.xsd" ::)
declare namespace soap-env="http://schemas.xmlsoap.org/soap/envelope/";

declare variable $input as element() (:: schema-element(ns1:ServiceErrorMessage) ::) external;

declare function local:func($input as element() (:: schema-element(ns1:ServiceErrorMessage) ::)) as element() (:: schema-element(ns2:fouten) ::) {
<soap-env:Fault>
<faultcode>fault</faultcode>
<faultstring></faultstring>
<detail>
<ns2:fouten>
{
for $detail in $input/ns1:detail
return
<ns2:ErrorMessages>
<ns2:ErrorLevel>{fn:data($detail/ns1:severity)}</ns2:ErrorLevel>
<ns2:ErrorMessage>{fn:concat("ERROR: ", fn:data($detail/ns1:message))}</ns2:ErrorMessage></ns2:ErrorMessages>
}
</ns2:fouten>
</detail>
</soap-env:Fault>
};

local:func($input)
Of course the actual fault detail must follow the xsd for that particular fault. We tested but the faultcode or fault string does not have any affect in selection of the REST-fault or HTTP statuscode.
With the xquery above we got the 'fault' returned as defined in the REST definition, as shown in the screendump above.
 In our example, if we would changed the contents of this xquery and replace the tag <ns2:fouten> to <ns2:fouten2> then we got the other fault (fault2), with the corresponding HTTP-status.
A detail with contents that does not correspond to any of the defined fault-xsd's would result in HTTP-status 500: internal server error. So it is important to have a proper transformation where the returning fault-detail is valid to at least one of the fault-xsd's.

Another conclusion is that since the fault selection is apparently based on the detail-contents against the registered fault-xsd-elements, you apperently can't have different faults with the same xsd. Since JSON is 'namespace-less', you probably can solve this by defining several copies of the same xsd with a different namespace, one for each fault. The choosen namespace in the xquery would then be the selector for the fault. But since the underlying element names are the same, it would not differ in the resulting JSON-message. Of course in an XML result it would differ.

MySQL : What management tools do you use?

Tim Hall - Fri, 2014-10-31 02:35

A quick question out to the world. What management tools do you use for MySQL?

We currently have:

  • MySQL Workbench : It’s OK, but I don’t really like it. It feels like half a product compared to tools I’ve used for other database engines…
  • phpMyAdmin : I’ve used this on and off for over a decade for my own website. While I’m typing this sentence, they’ve probably released 4 new versions. :) We have an installation of this which we use to access our MySQL databases should the need arise.
  • mysql Command Line : I use the command line and a variety of scripts for the vast majority of the things I do.

When I’m working with Oracle, my first port of call for any situation is to use SQL*Plus along with a variety of scripts I’ve created over the years. The performance stuff in Cloud Control (if you’ve paid for the Diagnostics and Tuning option) is the big exception to that of course.

I still consider myself a newbie MySQL administrator, but I’ve found myself spending more and more time at the command line, to the point where I rarely launch MySQL Workbench or phpMyAdmin these days. I’m wondering if that is common to other MySQL administrators, or if it is a carry over from my Oracle background…

Enquiring minds need to know!

Cheers

Tim…

MySQL : What management tools do you use? was first posted on October 31, 2014 at 9:35 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.

Index Advanced Compression vs. Bitmap Indexes (Candidate)

Richard Foote - Thu, 2014-10-30 23:59
A good question from Robert Thorneycroft I thought warranted its own post. He asked: “I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 […]
Categories: DBA Blogs

Better Communication

Michael Dinh - Thu, 2014-10-30 19:09

Sometimes I get excited over simple things. Today, I learned something new, something about human behaviors.

Basically, there are 4 quadrants for human behaviors.

If one does not understand the other person’s behavior, then communication can be very difficult.

To find more about human behaviors, read The DISC Model of Human Behavior – A Quick Overview.

Which quadrant are you in, versus your manager?


Flipped Classrooms: Annotated list of resources

Michael Feldstein - Thu, 2014-10-30 17:03

I was recently asked by a colleague if I knew of a useful article or two on flipped classrooms – what they are, what they aren’t, and when did they start. I was not looking for any simple advocacy or rejection posts, but explainers that can allow other people to understand the subject and make up their own mind on the value of flipping.

While I had a few in mind, I put out a bleg on Google+ and got some great responses from Laura Gibbs, George Station, and Bryan Alexander. Once mentioned, Robert Talbert and Michelle Pacansky-Brock jumped into the conversation with additional material. It seemed like a useful exercise to compile the results and share a list here at e-Literate. This list is not meant to be comprehensive, but a top level of the articles that I have found useful.

There are other useful article out there, but this list is a good starting place for balanced, non-hyped descriptions of the flipped classroom concept.[1] Let me know in the comments if there are others to include in this list.

  1. I did not include any directly commercial sites or articles in the list above. Michelle’s book was included as the introduction is freely available.

The post Flipped Classrooms: Annotated list of resources appeared first on e-Literate.

PeopleSoft's paths to the Cloud - Part II

Javier Delgado - Thu, 2014-10-30 16:42
In my previous blog post, I've covered some ways in which cloud computing features could be used with PeopleSoft, particularly around Infrastructure as a Service (IaaS) and non-Production environments. Now, I'm going to discuss how cloud technologies bring value to PeopleSoft Production environments.

Gain Flexibility



Some of the advantages of hosting PeopleSoft Production environments using an IaaS provider were also mentioned in the my past article as they are also valid for Non Production environments:

  • Ability to adjust processing power (CPU) and memory according to peak usage.
  • Storage may be enlarged at any time to cope with increasing requirements.
  • Possibility of replicating the existing servers for contingency purposes.

In terms of cost, hosting the Production environment in IaaS may not always be cheaper than the on premise alternative (this needs to be analyzed on a case by case basis). However, the possibility to add more CPU, memory and storage on the run gives IaaS solutions an unprecedented flexibility. It is true that you can obtain similar flexibility with in house virtualized environments, but not many in-house data centers have the available horsepower of Amazon, IBM or Oracle data centers, to name a few.

Be Elastic



Adding additional power to the existing servers may not be the best way to scale up. An alternative way is to add a new server to the PeopleSoft architecture. This type of architecture is called elastic (actually, Amazon EC2 stands for Elastic Computing), as the architecture can elastically grow or shrink in order to adapt to the user load.

Many PeopleSoft customers use Production environments with multiple servers for high availability purposes. You may have two web servers, two application servers, two process schedulers, and so on. This architecture guarantees a better system availability in case one of the nodes fails. Using an elastic architecture means that we can add, for instance, a third application server not only to increase redundancy, but also the application performance.

In order to implement an elastic architecture, you need to fulfill two requirements:

  1. You should be able to quickly deploy an additional instance of any part of the architecture. 
  2. Once the instance is created, it should be plugged in the rest of the components, without disrupting the system availability.

The first point is easily covered by creating an Amazon AMI which can be instantiated at any moment. I've discussed the basics about AMIs in my previous post, but there is plenty of information from Amazon.

The second point is a bit trickier. Let's assume we are adding a new application server instance. If you do not declare this application server in the web servers configuration.properties file, it will not be used.

Of course you can do this manually, but my suggestion is that you try to automate these tasks, as it is this automation which will eventually bring elasticity to your architecture. You need to plan the automation not only for enlarging the architecture, but also for potential reduction (in case you covered a usage peak by increasing the instances and then you want to go back to the original situation).

At BNB we have built a generic elastic architecture, covering all layers of a normal PeopleSoft architecture. If you are planning to move to a cloud infrastructure and you need assistance, we would be happy to help.

Coming Next...

In my next post on this topic, I will cover how Database as a Service could be used to host PeopleSoft databases and what value it brings to PeopleSoft customers.

Oracle Priority Support Infogram for 30-OCT-2014

Oracle Infogram - Thu, 2014-10-30 15:38

RDBMS
From Upgrade your Database - NOW!: ORA-20000 Unable to gather statistics concurrently: Resource Manager is not enabled ORA-06512: at "SYS.DBMS_STATS"
MySQL
November's events with MySQL, from Oracle’s MySQL Blog.
MySQL for Excel 1.3.3 has been released, from the MySQL on Windows blog.
BI
2 Minute Tech Tip: Passing Essbase Security to OBIEE, from Arch Beat.
Updated Statement of Direction for Oracle Business Intelligence Analytics (OBIA), from the Data Integration blog.
ODI
ODI 12c and DBaaS in the Oracle Public Cloud, from Data Integration.
SOA
From SOA & BPM Partner Community Blog: What’s New in Oracle SOA Suite 12c?
ADF
By the Poolside: An Introduction to ADF BC Application Module Pooling , from WebLogic Partner Community EMEA
Cloud
Heating Up Your OpenStack Cloud, from the Fore!blog.
ECB
From Insights from an Oracle University Classroom: Resolving Enterprise Routing & Dialing Plan complexity with Enterprise Communications Broker (ECB)
Demantra
From the Oracle Demantra blog: Oracle Demantra 12.2.4 RDBMS 12C Important Updates to Consider
MAF
APEX and MAF: seeing is believing, from the Blueberry Coder.
Mobile Persistence Extension for Oracle MAF, from WebLogic Partner Community EMEA
EBS
From Oracle E-Business Suite Technology:
New OA Framework 12.1 Release Update Pack 4 Now Available
October Updates to AD and TXK for EBS 12.2
Updated Case Study: Oracle's Own E-Business Suite 12 Environment
Migrating EBS Environments to Unicode
New Simplified Home Page in EBS 12.2.4
From Oracle E-Business Suite Technology:
PDOI New Features and Enhancements!
Webcast: Inventory Period Close - Stuck Transactions
CRM Service: New Consolidated RUP Patch Released
Webcast: Demo on 12.2.4 Demantra - APCC New Features
Using the Concurrent Request "Transfer Journals to GL - Assets"
How to Customize Your Field Service Wireless Application
Oracle Cost Management - Upgrade 11i to Release 12 Assistant
ORAchk adds Order Management and Process Manufacturing
To PIM or not to PIM - What is that Abbreviation?

patches:Oracle Forms Object POXOPROC.pll did not generate successfully: wrong number or types of arguments in call to 'UPDATE_WO_ADD_DES_DIR_ITEM'

Webcast Q&A: Next-Generation Accounts Payable Process Automation

WebCenter Team - Thu, 2014-10-30 14:41
On October 16, Nilay Banker, Founder & CEO, Inspyrus and Chris Preston, Sr. Director Customer Strategies, Oracle presented on a webcast "Next-Generation Accounts Payable Process Automation." We had a ton of questions come in during the webcast, and Nilay has graciously answered and addressed them for us in this blog post Q&A.
A lot of your competitors claim they can provide 80% automation. How is your offering different? When our competitor’s talk about 80% automation, they are talking about what you could potentially get with OCR. They provide really poor integration with your ERP system and that is where the real problem is. That is the traditional approach where after OCR, about 82% of invoices end up with exceptions in your ERP system and so your AP personnel have to manually resolve those invoices one-by-one. o Our next generation approach provides you end-to-end automation. Not only do we provide best-in-class OCR, but we have cracked the code on how we integrate real-time with your ERP systems and provide exception-free creation of invoices and 2-way and 3-way matching.
Can your cloud offering integrate with our on-premise ERP systems?  We have Oracle E-Business Suite and JD Edwards. Yes, our cloud offering can integrate with your on-premise of cloud ERP systems. A lot of our customers have different ERP systems. We can integrate with multiple ERP systems seamlessly and provide real-time integration, and unified Application, Workflow and Analytics across all your multiple ERP systems.
We receive invoices via mail, email and EDI. Can your offering provide us a consistent process for all these? Yes. Irrespective of how you receive your invoices, we provide a consistent Application, Workflow and Analytics for all of these.
We have Oracle E-Business Suite and SAP for our ERP systems. Will your solution integrate with both our ERP systems? Yes, our solutions comes pre-integrated with Oracle E-Business Suite and SAP and if you have both ERP systems, a single instance of our application will be integrated with both.
What is your typical implementation timeline? Our standard implementation takes between 8 to 10 weeks.
Is the solution set specific to Oracle's eBusiness suite or can this solution bolt on to something like MS Dynamics to replace the AP transactions processing? The solution is available for most major ERP systems including MS Dynamics. Also available for SAP, PeopleSoft & JD Edwards.
Can you talk more about how Suppliers can be included? Suppliers can be included in a couple of ways:
  1. As part of the invoice processing workflow if you want to reject invoices with missing information and send it back to the suppliers for them to resubmit.
  2. Provide suppliers self-service access to view status of invoices, payments and to create invoices online including PO flips.
How about list of values within the tool - master data and transactional data ? Will you draw from the ERP? The application is integrated real-time with Oracle EBS, SAP, PeopleSoft and JD Edwards. All master and transactional data are drawn from the ERP system.
How does this solution compare to BPM? BPM is a technology. What we are presenting here is a specialized pre-built Solution that is based on (leverages) Oracles BPM technology, along with Imaging, Content Management, OCR/OFR and SOA integration.
Would this solution work if we have a mix of invoices where some are match to po and some are match to receipt? Yes, that is very common.
And is this an Inspyrus solution or out of the box with WebCenter Imaging? This solution from Inspyrus comes out-of-the-box with Oracle WebCenter Imaging, Forms Recognition and Capture.
Does the application also integrate with some of the cloud based application like SAP's Business By Design? Yes, it does.
I wasn't clear about how the system can create the GL code without PO matching? Business rules can be applied to automate GL-coding for non-PO invoices.
Does this support Project related invoice coding for those that use E Business Suite - Project Accounting? Yes, project coding is supported out-of-the-box.
Does this application have an integration to Oracle Fusion? Integration with Oracle Fusion will be available in Feb 2015.
What kind of validations happens when it hits the unified workflow? Whatever is required for the successful creation of the invoice in the ERP system. Basically, validation against every setup, rule, config of the ERP system.
What is the backbone (Content Storage, Workflow, etc.) of the application? Underlying technology is Oracle WebCenter Content & Oracle BPM.
Need to know if the application has its own database to store scanned copies of invoices or we need to have content management tool? This solution from Inspyrus and Oracle comes out-of-the-box with Oracle WebCenter Imaging, Forms Recognition and Capture - includes WebCenter Content which is the content management repository.
Does this solution utilize Oracle IPM? This is built on top of Oracle IPM. You can reach out to Nancy Tiano. We have been talking to Land O'Lakes recently.
For invoices emailed - e.g. a .pdf file, can they be automatically sent for OCR? Yes, every attachment in the email is automatically sent for OCR.
Does OCR support all language invoices? There is support for about 24 languages. Most major ones.
Is SOA Suite a requirement for delivery to the downstream ERP systems? Including Oracle EBS, MS Dynamics, SAP, etc? WebCenter Imaging includes run-time licenses of BPM Suite and SOA Suite. SOA Suite is used as the integration tool for integration with all the mentioned ERP systems.
Can this be integrated into other ERP systems besides the ones mentioned? We develop integrations to other ERP systems all the time. Yes, we can build new integrations as needed.
Is this replacing workspace and IPM application? This leverages Oracle WebCenter Capture, Forms Recognition and Imaging.
Do you have the capability of electronic signatures on invoices? Yes.
How do you handle disputes or discrepencies for invoices with exceptions? These are handled as part of the invoice processing workflow.
How does the application "standardize" emailed invoice files for ingestion into capture? WebCenter Capture can monitor email inboxes. It extracts invoices that are attached to these email inboxes. These attachments could come into a variety of different formats eg. PDF, Word, Excel, PNG, JPG etc. Capture converts that into a standard TIFF format.
Would we need to configure our matching rules within the tool? No, we use matching rules that are setup in your ERP system.
The solution does consolidate the number of applications and move the matching upstream, but how will this assist in reducing the 82% exceptions? Are the exceptions just being moved upstreamed? We enrich the data prior to validation and scrub it.
How much does this cost in terms of software license if we want to implement? This is a packaged offering and some customers have unique requirements. SO best if you can reach out to your Oracle sales rep or Inspyrus (inspyrus.com).
Can you throw some more light on Unified workflow, in other words are we using oracle workflow tool , ability to customize workflow? Workflows are built using Oracle BPM Suite. Plenty of capabilities to customize that.
Does this solution use SOA Suite 12c? Still under certification.
What's involved to get to automated GL coding? If there are specific business rules that you can tell us to automate GL coding - say for a particular vendor or for certain descriptions on invoice lines, we can automate GL coding.
Can you override GL coding on Invoices matched to a PO? Or is this functionality only for non-PO matched invoices? Yes, we can do that.
Can suppliers submit their invoices directly in this case or it provides only status access to their invoices? Yes, suppliers can submit their invoices online.
Is the integration to the ERP systems unidirectional or bidirectional? Our integration is real-time with the ERP system. We don't need to store any ERP information in our system. We do bring in information about payments back into our system - thus making it bidirectional.
There are different formats used by suppliers, how does OCR recognize content of invoices and processs them? WebCenter Forms Recognition is a Pattern-Recognition based OCR engine. It does not rely on the use of templates or anchors or tags as other OCR engines do. It understands and recognizes patterns of information and so has a much higher rate of extraction than other template-based OCR engines.
Is complex approval rules able to be used with this application? Yes, we can handle all kinds of complex approval rules.
100% of our invoices are coded to a Project/Task/Expenditure Type in E Business Suite. Does this support full coding validation against Project related invoices?
Yes, it does.
Can vendors integrate into this solution as well, as in submitting invoices EDI to the cloud offereing (instead of emailing to customer who then turns around and uploaded into AP solution)? Absolutely. Then can send EDI invoices to our Cloud.
Will the 3 way match verify the Project number, from the Oracle Projects module? Yes, it can.
Can we self-host? Yes, this can be hosted in your data center.
Is Oracle SOA required for downstream integration to other ERP, including SAP, etc? Oracle SOA comes embedded in this solution. That is the integration framework we use to integrate with all the ERP systems.
Do we have to buy a particular machine for imaging? If you can reach out to your Oracle sales rep or Inspyrus (inspyrus.com) - they can provide you details.
Do you have the capability for invoices t obe signed off electronically by an authorized signer? Yes, all approvals are electronic.
Is one of the 24 languages covered by OCR Chinese? Simplified Chinese - yes
Is there a charge for suppliers submitting directly? No additional charge to suppliers for submitting directly.
Does it provide invoice attachments?. Yes, it does.
Who provides the OCR? Supplier? We do the OCR as part of the solution.
Does Inspryus handle non PO invoices that require multiple approvers? Yes.
How is this exception free? There would not be any exceptions when the invoices are pushed to the ERP system.
Do we need to have separate server for attachments? No, the solution comes with a content management system where attachments/images are stored - WebCenter Content.
Do suppliers submit invoices directly to Inspyrus or EBS? They can do that via email or send EDI invoices.
How much does the whole implementation cost? This is a packaged offering and some customers have unique requirements. SO best if you can reach out to your Oracle sales rep or Inspyrus (inspyrus.com).
Will it integrate with an ancient PO/RO system that is not Oracle? Yes, we have the ability to integrate with 3rd party PO systems.
Can you briefly explain how this is based on Oracle WebCenter? We have WebCenter right now and we want to know how we can utilize it. Yes, it is built on Oracle WebCenter. You can reach out to Inspyrus for more information. www.inspyrus.com
After the OCR data extraction, if there are any errors/mistakes, how are they corrected before pushing into the ERP? Inspyrus provides a unified application where these are corrected - as part of the workflow.
You replied that all your approvals are electronic - can they be visible like a digital signature in pdf? We do not touch the pdf - for compliance reasons. The electronic approvals are available as a separate document tied to the invoice record.
What is the cost of the system? This is a packaged offering and some customers have unique requirements. So best if you can reach out to your Oracle sales rep or Inspyrus (inspyrus.com).
Automatic GL coding. What criteria\invoices should satisfy for Automatic GL coding proper work?

If there are specific business rules that you can tell us to automate GL coding - say for a particular vendor or for certain descriptions on invoice lines, we can automate GL coding.

In case you missed the webcast, you can view the on demand version here

Work-around Instance Migration Limits of BPM Suite 11g

Jan Kettenis - Thu, 2014-10-30 12:46
The following describes a work-around for 2 situations for which instance patching and migration is not supported, being changing the level of an activity, and removal of an embedded sub-process. In short this work-around consists of re-implementation of the activities to move, and emptying the reusable sub-process.

There are a couple of restrictions for the Oracle BPM Suite that can make that process instances cannot be patched (deployment using same revision number) or migrated (deployment using new revision number, and then move from old to new revision). Two of them are that you cannot change the scope of an activity (like moving it in our out of an embedded sub-process) or removal of an embedded sub-process. For both situations there is a work-around, that I can demonstrate with one case.

More information about instance patching and migration can be found here for BPM 11g (11.1.1.7) and here for BPM 12c (12.1.3).
The Work-Around
Suppose you have a process (A) like this:





And you want to change it to this (in real life you may want to move the activity inside to a totally different location, or even remove the embedded sub-process altogether) to this model (D):
As migration of running instances is not supported:
  1. Because of changing the scope of the activity, and
  2. Because of removal of the embedded sub-process
you will get an error like this when trying to do so:

 However, what you can do, is change the model like this (C):

The trick is that the Say Goodbye activity has not been moved outside, but re-implemented. In this case I created a new activity with the same name, and reused the existing task definition. So I only had to redo the data mappings.

You won't win any prize for most beautiful process model with this but it works. In practice you want to collapse the embedded sub-process and rename it to something like "Empty".
The Proof of the PuddingTo make sure it actually works for running processes I used an extra step between (A) and (C), being this model (B):
 First I started with the first model, created 2 instances with one in Say Hello, and the other in Say Goodbye. I then deployed the last model with "Keep running instances" checked (instance patching). The result was that both instances were automatically migrated (i.e. just kept on running).

Then I created a start situation of 3 instances, each of them being in a different activity. The most interesting is the one in Say Goodbye in the re-usable sub-process, as in this case the token is in an activity that is going to be removed.
 In line with the documentation, when deploying model (C) all instances were put I status Pending Migration. Using Alter Flow I was able to migrate the instances in the first and last activity as-is. After that I could successfully complete them.

The interesting one though, is in the second activity:

I was able to migrate that with Alter Flow by moving the token from the embedded sub-process (not the activity inside the embedded sub-process!) to the last activity:

The engine never knew what hit it ;-)

Quiz night

Jonathan Lewis - Thu, 2014-10-30 12:43

Here’s a little puzzle that came up on OTN recently.  (No prizes for following the URL to find the answer) (Actually, no prizes anyway). There’s more in the original code sample than was really needed, so although I’ve done a basic cut and paste from the original I’ve also eliminated a few lines of the text:


execute dbms_random.seed(0)

create table t
as
select rownum as id,
       100+round(ln(rownum/3.25+2)) aS val2,
       dbms_random.string('p',250) aS pad
from dual
connect by level <= 1000
order by dbms_random.value;

begin
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'T',
                                method_opt       => 'for all columns size 254'
  );
end;
/

column endpoint_value format 9999
column endpoint_number format 999999
column frequency format 999999

select endpoint_value, endpoint_number,
       endpoint_number - lag(endpoint_number,1,0)
                  OVER (ORDER BY endpoint_number) AS frequency
from user_tab_histograms
where table_name = 'T'
and column_name = 'VAL2'
order by endpoint_number
;

alter session set optimizer_mode = first_rows_100;

explain plan set statement_id '101' for select * from t where val2 = 101;
explain plan set statement_id '102' for select * from t where val2 = 102;
explain plan set statement_id '103' for select * from t where val2 = 103;
explain plan set statement_id '104' for select * from t where val2 = 104;
explain plan set statement_id '105' for select * from t where val2 = 105;
explain plan set statement_id '106' for select * from t where val2 = 106;

select statement_id, cardinality from plan_table where id = 0;

The purpose of the method_opt in the gather_table_stats() call is to ensure we get a frequency histogram on val2; and the query against the user_tab_columns view should give the following result:


ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
           101               8         8
           102              33        25
           103             101        68
           104             286       185
           105             788       502
           106            1000       212

Given the perfect frequency histogram, the question then arises why the optimizer seems to calculate incorrect cardinalities for some of the queries; the output from the last query is as follows:


STATEMENT_ID                   CARDINALITY
------------------------------ -----------
101                                      8
102                                     25
103                                     68
104                                    100           -- expected prediction 185
105                                    100           -- expected prediction 502
106                                    100           -- expected prediction 212

I’ve disabled comments so that you can read the answer at OTN if you want to – but see if you can figure out the reason before reading it. (This reproduces on 11g and 12c – and probably on earlier versions all the way back to 9i).

I haven’t done anything extremely cunning with hidden parameters, materialized views, query rewrite, hidden function calls, virtual columns or any other very dirty tricks, by the way.


Pythian at PASS 2014

Pythian Group - Thu, 2014-10-30 11:40

Join us in Seattle for SQL PASS Summit 2014—planned by and for the Microsoft SQL Server community—where some of our very own will be presenting. You’ll notice that PASS has a funny way of scheduling speaking sessions, so both of our experts are presenting at the same time.

Also be sure to visit us at booth #226 for a chance to win a Sonos Play: 1. The Exhibit Hall will be open all day Wednesday, Thursday, and Friday morning.

IMPORTANT UPDATE: We are proud to announce that we’re hosting the first ever PASS Summit Bloggers Meetup! Join us Thursday night to meet with old friends, and make new ones. RSVP here.

 

Edwin Sarmiento Configuring SharePoint 2013 as a Business Intelligence Platform by Edwin Sarmiento
Wednesday November 5 — 1:30-2:45 PM
Room 608

Edwin Sarmiento, a Microsoft MVP, Certified Master, and Principal Consultant in Pythian’s Advanced Technology Group, will be presenting a session called Configuring SharePoint 2013 as a Business Intelligence Platform.

With SharePoint as the business collaboration platform of choice for the enterprise, it has become an important piece in the overall Microsoft Business Intelligence (BI) stack. However, deploying and configuring it to support Microsoft BI solutions has become a cumbersome task for IT experts.

In this session, Edwin will demonstrate what it takes to successfully architect and design SharePoint 2013 as a BI platform. He will cover multiple configuration scenarios and the different technologies necessary to build the infrastructure. Attendees will walk away with the confidence to run Power Pivot, Power View, and Reporting Services in their SharePoint 2013 farms.

 

Warner ChavesThe Use Cases for In-Memory OLTP by Warner Chaves
Wednesday November 5 — 1:30-2:45 PM
Room 3AB

Warner Chaves, a Microsoft Certified Master and Principal Consultant in Pythian’s SQL Server practice, will be presenting a session called The Use Cases for In-Memory OLTP.

In this session, he will do a short introduction to the In-Memory OLTP feature before diving straight into the use cases where the new lockless/latchless concurrency control and native compilation really shine.

Demos will cover PAGELATCH contention (or lack thereof), use of non-persistent in-memory tables for ETL, and in-memory tables as “shock absorbers” for high throughput environments.

For each case, Warner will do a comparison of “classic” versus in-memory, what gains the audience can expect, and what patterns will yield the biggest benefits.

 

BONUS MATERIAL! Since its release back in April, SQL Server experts across the globe are becoming familiar with the top features in Microsoft SQL Server 2014—the In-Memory OLTP engine, the AlwaysOn enhancements, and more. Warner couldn’t help but notice that there were a few features that weren’t getting the same attention and filmed a video series sharing the most underrated features of SQL Server 2014. We’ll be publishing that series during PASS, so follow @Pythian on Twitter to receive our updates.

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise.

Categories: DBA Blogs

Partner Webcast – Oracle Endeca Information Discovery: Unlocking Insights from any source

Businesses increasingly need to make quick decisions. Organizations need timely and actionable data, which will enable them to uncover opportunities faster and engage with customers better....

We share our skills to maximize your revenue!
Categories: DBA Blogs

Announcement: Singapore Oracle Sessions

Doug Burns - Thu, 2014-10-30 08:20
When I knew that the ACE Director, Bjoern Rost of Portrix Systems was coming to Singapore on his way to begin the OTN APAC tour, I suggested he stay at mine for a few days and sample all that Singapore has to offer.

Then a thought occurred to me. While he was here, why not setup an informal Oracle users meetup, much like the various ones at cities around the world like Sydney, Birmingham and London (to name but three I'm aware of). Morten Egan, my new colleague and Oak Table luminary had already suggested to me months ago that we should get something going in Singapore, so why not start now?

Well, in a matter of a few days, we've put together an agenda, a room, we will be having pizza and beer and other drinks and three hopefully useful sessions from experienced speakers. 

Here is the agenda (SingaporeOracleSessions.pdf) and a map (SOSMap.pdf) to help you get to the venue which is very handily placed near Bugis MRT. All that's required to register is to email me at dougburns at Yahoo. There are currently 21 people registered but the room holds (believe it or not) 42, so spread the word!

Hopefully, it's just the beginning ....