I guess it is safe to say the databases also EVEOLVE with time (else we would have been stuck with the same version forever). But then it depends on the DB Company as well.

New functionalities are always incorporated (either by buying out an emerging tech co. or developing one by yourself). But eventually (as Tom pointed out), it will end up being a an [integral] part of the database itself.

"Database Killers" will come and go, but I DON'T see the database(s) going any time soon.

Data is an all too important assest and no one (and I mean NO ONE) likes to play around with that...

And an interesting one it is...
http://en.wikipedia.org/wiki/IBM_Informix#Illustra_acquisition

More like "acquired and retrofitted"...
http://esj.com/Articles/2010/12/15/SQL-Server-MPP-Retrofit.aspx

Attempting to compete with Exadata, it appears...

Voice from NoSQL community. Keep an open mind. :)

http://nosql.mypopescu.com/post/2851909889/what-is-a-database-architect

... it was truly hilarious - thanks!

Our product is an OLTP system and DB size is close 700GB+

We have to build a reporting engine for our OLTP DB, got some recommendations for using HADOOP. I like Oracle because that is the only technology stable to handle large volume of sensitive data and constantly providing lot of features in all aspects. From this site, i have got so many answers for my prior queries but for this topic, so, if possible, please provide me the advantages and disadvantages using these kind of Non Oracle related tools on a production system to build reports.

Thanks,
Mike Vel

Hello Tom,

Nowdays I hear lot of things about Vectorwise[ which is again a columnar database] ...and I was reading to this blog

http://dbmsmusings.blogspot.sg/2009/07/watch-out-for-vectorwise.html

which mentioned so many benefits of Columnar databases and specially vectorwise.

I also read that Vectorwise and Oracle were compared and response time of a query on vectorwise was 1 second , while same query took 1 minutes in oracle ... I am trying to understand the architecture of vectorwise , and as you mentioned Exadata also provide Hybrid compression..but if you please refer to the blog in above link , they have made several comments but I do not really understand that how things like

1/ "They are a column-store. I strongly believe that column-stores are the right solution for the analytical DBMS market space. They can get great compression ratios with lightweight compression algorithms, and are highly I/O efficient. In my opinion, the only reason why there are companies on the above list that are not column-stores is that they wanted to accelerate time to market by extending previously existing DBMS code, and the most readily available DBMS code at the time was a row-store. Any DBMS built from scratch for the (relational, structured data) analytical DBMS market should be a column-store."

2/2.Column-stores are so I/O efficient that CPU and/or memory usually become bottlenecks very quickly. Most column-stores do very careful optimizations to eliminate these bottlenecks. But to me, VectorWise has gone the extra mile. The query operators are run via a set of query execution primitives written in low-level code that allow compilers to produce extremely efficient processing instructions. Vectors of 100-1000 values within a column get pipelined through a set of query operations on that column, with many values typically being processed in parallel by SIMD (single instruction, multiple data) capabilities of modern CPU chips. Most database systems are unable to take advantage of SIMD CPU capabilities --- the tuple-at-a-time (iterator) processing model of most database systems is just too hard for compilers to translate to SIMD instructions. VectorWise has gone to great lengths to make sure their code results in vectorized CPU processing. Their execution primitives are also written to allow CPUs to do efficient out-of-order instruction execution via loop-pipelining (although compilers are supposed to discover opportunities for loop-pipelining on their own, without carefully written code, this doesn’t happen in practice as often as it should). So with highly optimized CPU-efficient code, along with (1) operator pipelining to keep the active dataset in the cache and (2) column-oriented execution reducing the amount of data that must be shipped from memory to the CPU, VectorWise reduces the CPU and memory bottlenecks in a major way. The bottom line is that VectorWise is disk efficient AND memory efficient AND CPU efficient. This gets you the total performance package.

are true....is it some architecture marvel , paradigm shift really ?

Hello Tom,

I know only oracle, has been using it as developer, architect for last 17 yrs, so i wanted to ask your thought on this specific blog..there was no other purpose, and I have been using exadata for 2 yrs in production now and I as well as my customer are more than happy , used compression also as great results , exceed our requirements infact...this was just an acadamic question, and totally understand your points.

Hi Tom,
I attended the keynote address last night and am wondering if I need to install a special option to enable the columnar storage of data:

select name from v$parameter where lower(name) like '%mem%'


NAME
------------------------------
shared_memory_address
hi_shared_memory_address
memory_target
memory_max_target

I believe the parameter is named inmemory_size

select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0

Thank you in advance!

Hi Tom,
At OpenWorld, columnar storage was demoed. Is this feature in a downloadable release yet? It doesn't seem to exist in 12c.

Hi Tom

Is it possible to have both row-based and column-based tables in same database? Would it just be a syntactic-sugar to create a different one?

Thanks and regards

Parthiban - I'm sure Mr. Kyte will be writing about the in-memory database option in the near future. But yes, the latest release does merge row and column stores together in the same product. The column store is a memory cache on the existing row-level storage though...pretty slick if you ask me.

http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Team,

Got an access to exadata box and started playing with it.

http://www.expertoracleexadata.com/

Able to understand the reduced execution time due to predicate pushed into storage, but why no reduced logical IO in that case?

(LIO seems to be the same in both the cases)

demo@ORA12C_EXA> show parameter cell_offload_processing

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
cell_offload_processing                       boolean     TRUE
demo@ORA12C_EXA> select count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

demo@ORA12C_EXA>
demo@ORA12C_EXA> set serveroutput off
demo@ORA12C_EXA> set timing on
demo@ORA12C_EXA> select /*+ gather_plan_statistics */ count(*) from big_table where id <0 ;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.69
demo@ORA12C_EXA> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3p6sk0y1vny7n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from big_table where id <0

Plan hash value: 599409829

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:00.02 |     496K|    496K|       |       |          |
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:00.02 |     496K|    496K|       |       |          |
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE |      1 |      1 |      0 |00:00:00.02 |     496K|    496K|  1025K|  1025K| 3085K (0)|
--------------------------------------------------------------------------------------------------------------------------------------

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

   2 - storage("ID"<0)
       filter("ID"<0)


20 rows selected.

Elapsed: 00:00:00.85
demo@ORA12C_EXA> alter session set cell_offload_processing=false;

Session altered.

Elapsed: 00:00:00.35
demo@ORA12C_EXA> select /*+ gather_plan_statistics */ count(*) from big_table where id <0 ;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:29.29
demo@ORA12C_EXA> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3p6sk0y1vny7n, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from big_table where id <0

Plan hash value: 599409829

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:28.59 |     496K|    496K|
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:28.59 |     496K|    496K|
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE |      1 |      1 |      0 |00:00:28.59 |     496K|    496K|
-----------------------------------------------------------------------------------------------------------

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

   2 - filter("ID"<0)


19 rows selected.

Elapsed: 00:00:00.87
demo@ORA12C_EXA>

SELECT
  ROUND(physical_read_bytes/1048576) phyrd_mb,
  ROUND(io_cell_offload_eligible_bytes/1048576) elig_mb,
  ROUND(io_interconnect_bytes/1048576) ret_mb,
  (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"
from v$sql
where sql_Id = '...';

I was able to understand the predicate filtering and column projection in place with Exadata storege, but why no reduced logical IO?

demo@ORAST01> show parameter cell_offload_processing

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
cell_offload_processing                       boolean     TRUE
demo@ORAST01> select count(*) from big_table;

  COUNT(*)
----------
 100000000

1 row selected.

demo@ORAST01> set serveroutput off
demo@ORAST01> set timing on
demo@ORAST01> select /*+ gather_plan_statistics */ count(*) from big_table where id <0 ;

  COUNT(*)
----------
         0

1 row selected.

Elapsed: 00:00:00.62
demo@ORAST01> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3p6sk0y1vny7n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from big_table where id <0

Plan hash value: 599409829

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |      1 |00:00:00.03 |     496K|    496K|       |       |          |
|   1 |  SORT AGGREGATE            |           |      1 |      1 |      1 |00:00:00.03 |     496K|    496K|       |       |          |
|*  2 |   TABLE ACCESS STORAGE FULL| BIG_TABLE |      1 |      1 |      0 |00:00:00.03 |     496K|    496K|  1025K|  1025K| 3085K (0)|
--------------------------------------------------------------------------------------------------------------------------------------

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

   2 - storage("ID"<0)
       filter("ID"<0)


20 rows selected.

Elapsed: 00:00:00.76
demo@ORAST01> SELECT
  2    ROUND(physical_read_bytes/1048576) phyrd_mb,
  3    ROUND(io_cell_offload_eligible_bytes/1048576) elig_mb,
  4    ROUND(io_interconnect_bytes/1048576) ret_mb,
  5    (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"
  6  from v$sql
  7  where sql_Id = '3p6sk0y1vny7n' ;

  PHYRD_MB    ELIG_MB     RET_MB    SAVING%
---------- ---------- ---------- ----------
      3880       3880          0 99.9997377

1 row selected.

Elapsed: 00:00:00.61
demo@ORAST01>

Hi guys,

We have an Exadata machine, one of the smaller racks (it's either 1/8 or 1/4 rack). We're having some internal debates one whether or not smart scans are causing more harm than good, because we are running an OLTP application with a relatively small data footprint. I was hoping you could help me determine what impact that feature is having on our queries. From reading this thread, it systems like I can run this query to find queries using smart scans, and look at the savings column to see if it's helping or hurting? I'm assuming a negative value in the saving column means it would have been better off without it?

SELECT
sql_text,
  ROUND(physical_read_bytes/1048576) phyrd_mb,
  ROUND(io_cell_offload_eligible_bytes/1048576) elig_mb,
  ROUND(io_interconnect_bytes/1048576) ret_mb,
  (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%"
from v$sql
where io_cell_offload_returned_bytes > 0

I forgot to ask, what does "STORAGE" really mean in the access path? If I understood correctly, it just means storage cells were used?

I was also reading on OTN, it seems cell offloading is not just a smart scan. So I'm a little confused about all the different features and when they're kicking in, and if they're helpful.

Does turning cell_offload_processing off have a lot bigger impact than just turning off smart scans?

Thanks.

Ok thanks, I'll go through that.

So do all scan access paths in Exadata show up as "STORAGE XYZ"? I saw in this thread you said it just means storage cells were used. To me that just means it did physical I/O like any other Oracle database, but Exadata has storage nodes so they called it something a little different?

The whitepaper says "The SQL EXPLAIN PLAN shows when Exadata smart scan is used." I don't think that's true, it just says STORAGE, unless you guys are mistaken about that not necessarily meaning a smart scan took place...