Recently I have reviewed an Exadata implementation (about 66TB data warehouse with multiple marts running on different services on a single database of full-rack Exadata V2) for performance improvements. This post tries to summarize the key points application developers / designers / DBAs should be aware of while deploying the applications on to Oracle Exadata V2.
1. "Smart Scan" is a Cell Offloading feature that the selection / projection of an SQL is offloaded to the storage cell instead of doing that operation on the compute node after reading all the required blocks to the buffer cache. This works with Full Table Scans (FTS) and Full Index Scans when using the direct path reads. This can dramatically improve the performance of a FTS but that does not mean all the processing need to happen over FTS and all the indexes to be removed! When the single row look up need to happen or very small amount of records are read from a large table, still the index based look up is much faster than the FTS even with smart scan.
Smart Scan is more a run-time decision rather than an optimizer time decision. Smart Scan depends on the number of sessions requesting for the data, number of dirty blocks, size of the table (_small_table_threshold - by default Oracle considers 2% of buffer cache as small table threshold; this may not be good in some times! This parameter may be tweaked at session level as needed.)
On the explain plan one can see the "STORAGE" keyword on action like "TABLE ACCESS STORAGE FULL" and the statistic value in V$ views is "cell physical IO bytes eligible for predicate offload".
To force the direct read on serial operations at a session level "_serial_direct_read" can be set to TRUE.
2. "Storage Index" is another feature that each cell builds a dynamic negative index of what data is surely not there on the cell for each column value by making a min and max value ranges that are stored on the cell for a given column. This structure is a in-memory index dynamically built after seeing multiple queries that are offloaded to the storage. This feature gives performance improvement similar to "partition pruning" on partitioned tables. To take best advantage of this feature, an ordered load of data into the table based on the most used where clause predicate columns is recommended. The ETL processes should use a serial loading of data using "APPEND" hint into the table such that the best advantage of storage index can be achieved on SELECT statements.
3. In a data warehouse type environment, when the most of the times all the rows are accessed but every time only a subset of columns are accessed from the table, Hybrid Columnar Compression improves the performance. Using a COMPRESS FOR QUERY HIGH mode of HCC all the queries that use few columns of the table would only read the required column blocks and perform better.
It is important to consider these features during design of an application and building the application to take advantage of these features will tremendously reduce the resource consumption on the platform at the same time giving best throughput.
But,
It is important to have correct indexing strategy, correct partitioning strategy in place even with these features to absolutely make sure the performance is predictable. Just leaving the tables to grow large with all the history data without the right partitioning strategy will leave the performance degrade over time even with smart scan, storage indexes and HCC!
Tuesday, June 19, 2012
Monday, April 23, 2012
Identifying Bottlenecks
Recently, I have been asked to review performance of an ETL workflow of Informatica provided by Oracle as part of OBIEE upgrade. The OLAP schema upgrade which has several transformations to upgrade Siebel Analytics 7.7 to OBIA 7.9.6.2
The problem:
An innocent looking "Insert" load is reading a source table, applying a set of look up transformations, then generating a sequence number as a unique key and inserting to a target table in bulk mode is only able to give a throughput of about 65 records per second on a 4 core, 8GB RAM server. The source table is having over 20 million records.
There are other work flows which are running at a throughput of >2000 records per second. So, I have started investigating into this "Performance Problem"
1. Looked at the AWR reports from the source and target databases as well as the Informatica repository database. There is no indication any bottleneck on the database end.
2. Looked at the Session Log of Informatica PowerCenter. The session log shows the Writer thread 99% busy.
3. Taken a subset of 100K records using the source qualifier started running multiple scenarios to identify the bottleneck.
4. Looking at the sequence generator is reusable and set to cache only one value. This is causing a round trip from Informatica powercenter server to Informatica repository for the each sequence number to be generated. This could only do a maximum 65 round trips in a second which is causing the bottleneck in this workflow.
Setting appropriate caching on the sequence generator, we finally achieved a throughput of 2400 records per second and completed the load in less than 2.5 hours; It is an improvement of throughput by around 37 times!
Spending few hours in identifying the bottleneck and removing it worth its effort......
The problem:
An innocent looking "Insert" load is reading a source table, applying a set of look up transformations, then generating a sequence number as a unique key and inserting to a target table in bulk mode is only able to give a throughput of about 65 records per second on a 4 core, 8GB RAM server. The source table is having over 20 million records.
There are other work flows which are running at a throughput of >2000 records per second. So, I have started investigating into this "Performance Problem"
1. Looked at the AWR reports from the source and target databases as well as the Informatica repository database. There is no indication any bottleneck on the database end.
2. Looked at the Session Log of Informatica PowerCenter. The session log shows the Writer thread 99% busy.
3. Taken a subset of 100K records using the source qualifier started running multiple scenarios to identify the bottleneck.
- First step is to remove the target database; converted the target to a local flat file; so the workflow is now just reading the source; applying all the transformations & look-ups and writing to the local flat file. The throughput did not improve.
- Next step is to remove the large table look-ups. One of the look-up was on a table of more than 20 million records. Removed all the look-ups in the workflow mapping. Still the throughput is only 65 records per second.
- As the third step, removed the Sequence Number generation; Now the workflow is just reading from the source table and writing to a flat file after applying few compute transformations. The throughput reached 2400 records per second.
- By now we have identified the probable bottleneck with the "Sequence Generator"; to confirm that re-ran the work flow with all the look-up transformations and only disabling the sequence generator. The throughput was 2400 records per second.
4. Looking at the sequence generator is reusable and set to cache only one value. This is causing a round trip from Informatica powercenter server to Informatica repository for the each sequence number to be generated. This could only do a maximum 65 round trips in a second which is causing the bottleneck in this workflow.
Setting appropriate caching on the sequence generator, we finally achieved a throughput of 2400 records per second and completed the load in less than 2.5 hours; It is an improvement of throughput by around 37 times!
Spending few hours in identifying the bottleneck and removing it worth its effort......
Thursday, April 12, 2012
Data Replication
The need for data replication is evergreen. The use-case may vary from "High Availability" to "Operational Reporting (BI)" to "Real-time Analytics"; whatever may be the case, the need for replicating the data exists in the information systems and solutions.
I try to summarize the evolution of data replication technology from a Oracle Database standpoint in this post.
Depending on the need one should choose the right technology to achieve the needed data replication....
I try to summarize the evolution of data replication technology from a Oracle Database standpoint in this post.
- Looking back at the Oracle based data replication technologies the first one is "Database Link". One can create a database link and pull or push the data directly into a remote database starting Oracle 5 or 6. This is the very first method of replication where the application need to push or pull the data from a remote database and apply necessary logic to identify what data has changed and what to do with those changes.....
- The next improvement in the replication is around Oracle 8 - one can setup a trigger based replication. That means whenever a transaction changes the data in a table one can trigger a function that can handle the replication without changing the application. So, database started giving a method to replicate data using triggers....
- Then the next improvement has come around 9.2 with streams and log-based replication. The capability is to mine the redo logs and move the committed transactions to the target systems. (DBMS_CDC_PUBLISH and DBMS_CDC_SUBSCRIBE packages were introduced)
- Oracle Advanced Queuing enhanced the streams to have robust publish and subscribe model replication that has enqueue and dequeue based communication. I was involved in a very large project that set up a custom Changed Data Capture to migrate data from a legacy system into SAP ERP involving large tables having 100Milion records....
- Later the log-mining technology was used for physical and logical standby dataabses and evolved to a Active Data Guard technology......
- With GoldenGate, the heterogeneous log-based data replication solution is complete from Oracle that has capabilities to extract, replicate, synchronize data in bi-directional movement.
Depending on the need one should choose the right technology to achieve the needed data replication....
Tuesday, February 14, 2012
in memory computing
Approximately two years back I made a post on Enterprise Data Fabric technology. The aim of the data grid or "in memory data store" is to remove the movement of data in and out of slower disk storage for processing. Instead the data in kept in "pooled main memory" during the processing.
To get above the physical limitations on the amount of main memory, the data grid technologies will create a pooled memory cluster with data distributed over multiple nodes connected using a high bandwidth network.
With SAP bringing HANA, an in memory database that has option to store data in traditional row store and column store (read storing data in rows and columns) within an in-memory technology and Oracle bringing the Exaletics appliance, the in-memory computing is getting more attention.
So, the claims are that the in memory technology will boost the performance by multiple degrees. But the truth is it can only remove the time taken to move the data out of disk into main memory. If there is a query that is processing the data using a wrong access method, even if all the data is moved into a memory store the processing will still take as long to provide the answer!
In memory computing would need re designing the applications to use the technology for better information processing. OLTP workload will surely improve the performance due to memory caching but the consistency of the data need to be managed by the application moving to a event based architecture.
OLAP and Analytical workloads would also improve the performance by using memory based column stores with a good design of the underlying structure of data that suits the processing requirements.
Overall, in memory computing is promising at the moment but without the right design to use the new technology, the old systems will not just get the performance boost just by moving the data store into the main memory
Let us wait and see how the technology shapes further in future.....
To get above the physical limitations on the amount of main memory, the data grid technologies will create a pooled memory cluster with data distributed over multiple nodes connected using a high bandwidth network.
With SAP bringing HANA, an in memory database that has option to store data in traditional row store and column store (read storing data in rows and columns) within an in-memory technology and Oracle bringing the Exaletics appliance, the in-memory computing is getting more attention.
So, the claims are that the in memory technology will boost the performance by multiple degrees. But the truth is it can only remove the time taken to move the data out of disk into main memory. If there is a query that is processing the data using a wrong access method, even if all the data is moved into a memory store the processing will still take as long to provide the answer!
In memory computing would need re designing the applications to use the technology for better information processing. OLTP workload will surely improve the performance due to memory caching but the consistency of the data need to be managed by the application moving to a event based architecture.
OLAP and Analytical workloads would also improve the performance by using memory based column stores with a good design of the underlying structure of data that suits the processing requirements.
Overall, in memory computing is promising at the moment but without the right design to use the new technology, the old systems will not just get the performance boost just by moving the data store into the main memory
Let us wait and see how the technology shapes further in future.....
Friday, December 30, 2011
Pensions Regulation in UK
As the year 2011 draws to a close, we are entering into a new year that is very important for UK workplace pensions. The new regulation around workplace pensions is coming into force in 2012.
Being purely technical for few years, I just want to test my skills around understanding regulatory documents and extract "Functional Information Needs" for a business.
A good review published in October 2010 can be accessed here: http://www.dwp.gov.uk/docs/cp-oct10-full-document.pdf
Basically, all the employers in UK have to automatically enroll all the eligible workers falling in a AGE range and EARNINGS range to a suitable pension scheme. They also need to "certify" the selected pension scheme meets the required quality criteria. (Refer to section 6.5 of the review document above) The requirement is to have at least 8% of earnings are paid towards a pension fund.
Regulation defines "Qualifying Earnings" as gross earnings that include commissions, bonuses, overtime etc., but most of the employers have the pension contribution basis as the "Basic Pay" i.e a pensionable pay. So, employer has primarily following options to "Certify" the pension scheme and comply with it.
Pseudo logic in plain English
1. IF pension contribution basis IS qualifying earnings (within the band) THEN pay the contributions of 8% and no certification is required.
2. IF pension contribution basis IS pensionable pay THEN
2a. CASE pensionable pay is 100% of gross pay THEN pay contributions of 7%
2b. CASE pensionable pay is at least 85% of gross pay THEN pay contributions of 8%
2c. CASE pensionable pay is less than 85% of gross pay THEN pay contributions of 9%
AND self certify pension scheme for all the employees participating in the pension scheme.
So, core information needs to implement this regulation is employee payroll data that covers age, all components of qualifying earnings of all employees. A bit of intelligence is needed to "model" the best possible grouping of employees and assign them to a suitable pension scheme(s) with one (or more) of the pension providers in the market.
The overall goal for a techno-functional consultant like me is to optimize the value of new pension regulations for employees, employers, pension providers and IT consulting companies by optimizing the information flows across various stakeholders!!
Wishing everyone a great new year 2012. Let there be peace, security and prosperity be with one and all.
Being purely technical for few years, I just want to test my skills around understanding regulatory documents and extract "Functional Information Needs" for a business.
A good review published in October 2010 can be accessed here: http://www.dwp.gov.uk/docs/cp-oct10-full-document.pdf
Basically, all the employers in UK have to automatically enroll all the eligible workers falling in a AGE range and EARNINGS range to a suitable pension scheme. They also need to "certify" the selected pension scheme meets the required quality criteria. (Refer to section 6.5 of the review document above) The requirement is to have at least 8% of earnings are paid towards a pension fund.
Regulation defines "Qualifying Earnings" as gross earnings that include commissions, bonuses, overtime etc., but most of the employers have the pension contribution basis as the "Basic Pay" i.e a pensionable pay. So, employer has primarily following options to "Certify" the pension scheme and comply with it.
Pseudo logic in plain English
1. IF pension contribution basis IS qualifying earnings (within the band) THEN pay the contributions of 8% and no certification is required.
2. IF pension contribution basis IS pensionable pay THEN
2a. CASE pensionable pay is 100% of gross pay THEN pay contributions of 7%
2b. CASE pensionable pay is at least 85% of gross pay THEN pay contributions of 8%
2c. CASE pensionable pay is less than 85% of gross pay THEN pay contributions of 9%
AND self certify pension scheme for all the employees participating in the pension scheme.
So, core information needs to implement this regulation is employee payroll data that covers age, all components of qualifying earnings of all employees. A bit of intelligence is needed to "model" the best possible grouping of employees and assign them to a suitable pension scheme(s) with one (or more) of the pension providers in the market.
The overall goal for a techno-functional consultant like me is to optimize the value of new pension regulations for employees, employers, pension providers and IT consulting companies by optimizing the information flows across various stakeholders!!
Wishing everyone a great new year 2012. Let there be peace, security and prosperity be with one and all.
Sunday, December 4, 2011
Storing Rows and Columns
A fundamental requirement of a database is to store and retrieve the data. In Relational Database Management Systems (RDBMS) the data is organized into a table that contain the rows and columns. Traditionally the data is stored into blocks of rows. For example a "sales transaction row" may have 30 data items representing 30 columns. Assuming a record occupies 256 bytes, a block of 8KB can hold 32 such records. Again assuming a million such transactions that need to be stored in 32150 blocks per day. All this works well as long as we need the data as ROWS! We want to access one row or a group of rows at a time to process that data, this organization has no issues.
Let us consider if we want to get a summary of total value of type x items that are sold in past seven days. This query need to retrieve 7million records that contain 30 columns each to just process the count of items of types x. All that we need is two columns item type and amount to process this. This type of analytical requirement lead us to store the data in columns. We group the columns together and store them in blocks. It improves the speed of retrieving the columns from the overall table quickly for the purpose of analyzing the data.
But the column storage has its limitations when it comes to the write and update
With a high volume of social data, where there is high volume of write is needed (like messages and status updates, likes and comments etc.,) , highly distributed, NOSQL based column stores are emerging into mainstream. Apache Cassandra is the new breed of NOSQL column store that was initially developed by Facebook.
So, we have a variety of data base / data stores available now, a standard RDBMS engine with SQL support for OLTP applications, A column based engies for OLAP processing and noSQL based key value pair stores for in-memory processing, highly clustered Hadoop style big data with map/reduce framework for big data processing and noSQL based column stores for high volume social write and read efficiencies.
Making right choice of data store for the problem in had is becoming tough with many solution options. But that is the job of an architect; Is it not?
Let us consider if we want to get a summary of total value of type x items that are sold in past seven days. This query need to retrieve 7million records that contain 30 columns each to just process the count of items of types x. All that we need is two columns item type and amount to process this. This type of analytical requirement lead us to store the data in columns. We group the columns together and store them in blocks. It improves the speed of retrieving the columns from the overall table quickly for the purpose of analyzing the data.
But the column storage has its limitations when it comes to the write and update
With a high volume of social data, where there is high volume of write is needed (like messages and status updates, likes and comments etc.,) , highly distributed, NOSQL based column stores are emerging into mainstream. Apache Cassandra is the new breed of NOSQL column store that was initially developed by Facebook.
So, we have a variety of data base / data stores available now, a standard RDBMS engine with SQL support for OLTP applications, A column based engies for OLAP processing and noSQL based key value pair stores for in-memory processing, highly clustered Hadoop style big data with map/reduce framework for big data processing and noSQL based column stores for high volume social write and read efficiencies.
Making right choice of data store for the problem in had is becoming tough with many solution options. But that is the job of an architect; Is it not?
Friday, November 11, 2011
numbers and counters
There is some amount of hype on the 11-11-11 i.e., 11-November-2011... I see the numbers as just counters and they themselves do not make much sense unless identified with some meaningful thing.
It is 14581 days since i was born, 8495 days since i was associated with software/computers, 6240 days since i started working etc., etc., in all these counters "I" remains constant while the numbers move on...
Several other numbers, top 10s, fortune 500s etc., etc., also make some hype around from time to time; but it will be continuously replaced in the flow of the numbers.
Especially in the current era of very high importance to the numbers and counters the true importance of the "Identity" and "Intelligence" seems to have lost...
Hope the 11:11AM IST of 11-11-11 bring some common sense around in the world in general and Information Technology world in particular.....
All the best flocks!
I would like to add one memorable item from year 2001 on this occasion. We have completed our first "Consolidation" project a decade back and for that we got a 500 Million years old natural slate piece printed with a small message as a memento.
It is 14581 days since i was born, 8495 days since i was associated with software/computers, 6240 days since i started working etc., etc., in all these counters "I" remains constant while the numbers move on...
Several other numbers, top 10s, fortune 500s etc., etc., also make some hype around from time to time; but it will be continuously replaced in the flow of the numbers.
Especially in the current era of very high importance to the numbers and counters the true importance of the "Identity" and "Intelligence" seems to have lost...
Hope the 11:11AM IST of 11-11-11 bring some common sense around in the world in general and Information Technology world in particular.....
All the best flocks!
I would like to add one memorable item from year 2001 on this occasion. We have completed our first "Consolidation" project a decade back and for that we got a 500 Million years old natural slate piece printed with a small message as a memento.
Friday, October 14, 2011
Most efficient multi-set Cartesian join in C
At the beginning of my career, with Indian Space Research Organization, I have been posed with a challenge that required implementing a multi-set Cartesian product with absolutely minimum memory usage to solve an optimization problem. (see my old post on description of the problem: Simple looking Complex problem )
As a tribute to Dennis M Ritchie (also known as dmr) the creator of C language, who passed away yesterday, I am posting my implementation of this algorithm in C language.
I consider the above seven "highlighted" lines of C code as one of the earliest and most notable achievements of my career!
If there is any better implementation to solve the stated problem please let me know by posting a comment.....
As a tribute to Dennis M Ritchie (also known as dmr) the creator of C language, who passed away yesterday, I am posting my implementation of this algorithm in C language.
If there is any better implementation to solve the stated problem please let me know by posting a comment.....
Sunday, October 9, 2011
ACID and BASE of data
I am completing my 18 years of working in the field of Information Technology.
All these days an enterprise data store generally provides the four qualities Atomicity, Consistency, Isolation and Durability (ACID) to the transactions. Oracle has emerged as a leader in providing enterprise class ACID transactional capabilities to the applications.
Recently in the Open World 2011, Oracle announced a noSQL database which typically characterized by the BASE acronym. Basically Available, Soft state, Eventually consistent (BASE)
I see a lot of debate on SQL vs NoSQL, ACID vs BASE and Shared Everything vs Shared Nothing architectures of data stores of late; and with Oracle getting on to the NoSQL bandwagon, this debate is just took up additional momentum.
Oracle has posted this paper nicely explaining their NoSQL database. http://www.oracle.com/technetwork/database/nosqldb/learnmore/nosql-database-498041.pdf
In my opinion, SQL and NOSQL choice is straight forward to make:-
big query: Are we storing data or BIG-DATA (read my old post on transactional data vs machine generated big data - http://technofunctionalconsulting.blogspot.com/2011/02/analytics.html)
With the new trends in 'BIG DATA' all the data almost become key, value pair with read and insert only operations with minimal or no updates to the data records. NoSQL/BASE is best suited to handle this type of data. Still the traditional transactional databases of OLTP nature, needs ACID complaint transactions.
So, when designing the big data solutions, an architect should surely look at the NoSQL dataBASE. Is it not?
Publishing this post on 09/10/11 (dd/mm/yy) and this is my 85th post to this blog.
All these days an enterprise data store generally provides the four qualities Atomicity, Consistency, Isolation and Durability (ACID) to the transactions. Oracle has emerged as a leader in providing enterprise class ACID transactional capabilities to the applications.
Recently in the Open World 2011, Oracle announced a noSQL database which typically characterized by the BASE acronym. Basically Available, Soft state, Eventually consistent (BASE)
I see a lot of debate on SQL vs NoSQL, ACID vs BASE and Shared Everything vs Shared Nothing architectures of data stores of late; and with Oracle getting on to the NoSQL bandwagon, this debate is just took up additional momentum.
Oracle has posted this paper nicely explaining their NoSQL database. http://www.oracle.com/technetwork/database/nosqldb/learnmore/nosql-database-498041.pdf
In my opinion, SQL and NOSQL choice is straight forward to make:-
big query: Are we storing data or BIG-DATA (read my old post on transactional data vs machine generated big data - http://technofunctionalconsulting.blogspot.com/2011/02/analytics.html)
With the new trends in 'BIG DATA' all the data almost become key, value pair with read and insert only operations with minimal or no updates to the data records. NoSQL/BASE is best suited to handle this type of data. Still the traditional transactional databases of OLTP nature, needs ACID complaint transactions.
So, when designing the big data solutions, an architect should surely look at the NoSQL dataBASE. Is it not?
Publishing this post on 09/10/11 (dd/mm/yy) and this is my 85th post to this blog.
Wednesday, September 28, 2011
User Experience - HTML5
Approximately two years back, I have made a post on "Rich Internet Applications" where the development of user experience focused on the plug-ins on the browser or run-time environments like Adobe AIR for rich experience. (See Desktop Widgets post.... )
As the technology progressed in last two years, the new/emerging HTML 5 seems to take on the web user experience design to the standard based, plug-in independent mode.
Some examples can be found on : http://www.apple.com/html5/
Another dimension today is the "mobile devices" along with the browser on desktop/laptop.
When it comes to the mobile devices and integration with the specific device capabilities, one should develop "native applications" to take full advantage of the native hardware of the device. Standards are good but Native Applications can do better. On the other hand, using the standards we can develop once and deploy on multiple devices where as native applications development requires "effort/time" on each platform...
So, there is no silver bullet for the problem of Rich User Experience needs of ever changing world!
As the technology progressed in last two years, the new/emerging HTML 5 seems to take on the web user experience design to the standard based, plug-in independent mode.
Some examples can be found on : http://www.apple.com/html5/
Another dimension today is the "mobile devices" along with the browser on desktop/laptop.
When it comes to the mobile devices and integration with the specific device capabilities, one should develop "native applications" to take full advantage of the native hardware of the device. Standards are good but Native Applications can do better. On the other hand, using the standards we can develop once and deploy on multiple devices where as native applications development requires "effort/time" on each platform...
So, there is no silver bullet for the problem of Rich User Experience needs of ever changing world!
Subscribe to:
Posts (Atom)
