X

The latest Oracle Database news and guidance from the development team

Recent Posts

Oracle Database 18c XE now available!

Heads up! DBAs and developers will be delighted to learn that Oracle Database 18c Express Edition (XE) is now available for download, development and deployment.  Oracle Database 18c XE is a free, community supported edition of the world’s most popular Database, and this release brings even more Oracle Database power to the community. CPU and memory limits have doubled to 2 CPUs and 2GB of RAM (respectively), and now supports up to 12GB of user data. Oracle Database 18c XE  also includes key database features such as; pluggable databases, in-memory column store, compression, spatial & graph support, encryption and redaction, partitioning,  analytic views, and more. Oracle Database is a true multi-model database, providing support for Relational, JSON, XML, Graph, Spatial, Object, and Key/Value data, all of which developers can use with Oracle Database 18c XE. And, applications written in Java, JavaScript, Python, .NET, Go, PHP, C/C++, and other programming languages can leverage Oracle Database 18c XE. When combined with Oracle REST Data Services, data access also can be REST-enabled, allowing interaction with the data over standard RESTful web services. In addition, Oracle Application Express (APEX), Oracle’s popular low-code app development platform, can be easily deployed on top of Oracle Database 18c XE. All of these capabilities make Oracle Database 18c XE the ideal free database to use for developing and building cutting edge data driven applications. It is also an excellent platform for DBAs to educate themselves about the latest advancements that Oracle Database 18c provides. Oracle Database 18c XE is fully compatible with other Oracle Database on-prem and in-cloud releases, making it easy to scale up to other editions and Oracle Cloud Database Services, including Autonomous Database, as required. Whether you are a developer, a DBA, a data scientist, an educator, or just curious to learns what's new, Oracle Database 18c Express Edition (XE) is the ideal way to get started. Click here for more information, or click here to download.    

Heads up! DBAs and developers will be delighted to learn that Oracle Database 18c Express Edition (XE) is now available for download, development and deployment.  Oracle Database 18c XE is a free,...

Oracle Database

Questions You Asked: How much space are objects using in the In-Memory column store?

Recently the question, "How much space are objects using in the In-Memory column store?" has come up a couple of times so I thought it would be worth revisiting. I blogged about space usage in the In-Memory column store (IM column store) in the blog post "What is an In-Memory Compression Unit (IMCU)?". In that blog post I talked about the two pools that compose the IM column store, the 1MB pool for IMCUs and the 64KB pool for Snapshot Metadata Units (SMUs) and other metadata. Of course, now all of that information is also documented in the Database In-Memory Guide which first became available in the 12.2 Oracle Database documentation. The following image is from the Database In-Memory Guide: I think this image shows a nice representation of the In-Memory Area with both IMCUs and SMUs. It is worth repeating that an object populated into the IM column store is made up of one or more IMCUs, and an IMCU is made up of one or more 1MB extents taken from the 1MB pool. Each IMCU has a corresponding SMU which is made up of one or more 64KB extents taken from the 64KB pool. It is also worth noting that you cannot control the allocation of the 1MB and 64KB pools. You can only allocate the overall size of the IM column store with the INMEMORY_SIZE initialization parameter. In addition to IMCUs and SMUs, 12.2 introduced In-Memory Expressions and Join Groups. When an In-Memory Expression is created space is allocated from the 1MB pool for In-Memory Expression Units (IMEUs). When Join Groups are created space for the common dictionary is allocated from the 64KB pool. In Oracle Database 18c, In-Memory Optimized Arithmetic will use additional space when enabled because both the traditional Oracle NUMBER data type and the new In-Memory optimized NUMBER type will be stored in the IM column store. It is possible to query information about these pools by querying the dynamic performance view V$INMEMORY_AREA and to see the space used by an individual object by querying the dynamic performance view V$IM_SEGMENTS. As an example, let's take a look at a database where we will create and populate just one table: Let's take a look at the V$INMEMORY_AREA  and V$IM_SEGMENTS dynamic performance views. Note that only one table, IMSPACE, has been populated. I did this to make it easier to see the space used by that object in the IM column store: We can see that 1048576 bytes, or one 1MB extent, has been used from the 1MB pool. This means that we have one IMCU with one 1MB extent. We can also see that we have used 262144 bytes from the 64KB pool or 4 64KB extents for the SMU associated with this table and its single IMCU. It is worth noting that a segment must be greater than 64KB in size to be eligible for population in the IM column store. In addition, the minimum size of an object populated in the column store is 1MB (i.e. one IMCU made up of one 1MB extent) plus any 64KB extents needed for corresponding SMU and other metadata. It is also worth mentioning that since SMU(s) include various metadata about a corresponding IMCU, and since this metadata includes information like the transaction journal, it is entirely possible that overall metadata space usage will grow based on workload like DML. What this means in practice is that the querying of the Database In-Memory dynamic performance views is very much a snapshot in time and can vary based on the workload.    

Recently the question, "How much space are objects using in the In-Memory column store?" has come up a couple of times so I thought it would be worth revisiting. I blogged about space usage in the...

Oracle Database

Ask TOM Office Hours Demo – Why Are Column Store Scans Faster Than Row Store Scans?

I've created another video of the demonstration that we used in the second Ask TOM Office Hours session where we focused on showing why column store scans are faster than row store scans. I've had many questions from customers and conference attendees who ask, "What's the big deal, if I just cache my entire table in the buffer cache, won't it be just as fast as being in-memory?" We've even done two different blog posts that discuss this: In-Memory Column Store versus the Buffer Cache Memory Usage with Oracle Database In-Memory In this demonstration I show the same query accessing the In-Memory column store and the buffer cache or row-store. I use the same Star-Schema Benchmark (SSB) schema that we've used in our other blog posts and fully populate the tables in the IM column store and, using the KEEP pool, fully cache the tables in the buffer cache. I then briefly discuss why querying the IM column store is so much faster and how the features of Database In-Memory enable orders of magnitude faster queries. The video is available at the link below and is located on the Database In-Memory YouTube channel. While you're there check out some of the other videos we have available as well.  

I've created another video of the demonstration that we used in the second Ask TOM Office Hours session where we focused on showing why column store scans are faster than row store scans. I've...

Oracle

Integrated Cloud Applications & Platform Services