It’s the gift that keeps on giving – no matter how many problems you find there are always more waiting to be found. It’s been some time since I last wrote about tables with more than 255 columns, and I said then that there was more to come. In the last article I described how adding a few columns to a table, or updating a trailing column in a way that made the table’s used column count exceed 255, could result in some strange row-splitting behaviour – in this article I’m going to look at a critical side effect of that behaviour.
Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion.
SQL Plan Baselines is a great feature for plan stability: you capture the plans that you accept. However, if the data model changes and the accepted plans cannot reproduce, the optimizer will come with a new plan. In 18c we have a note from DBMS_XPLAN when the optimization ‘failed to use SQL plan baseline for this statement’.
I create a table, with an index, and run a query on it using this index:
SQL> create table DEMO as select rownum n from xmltable('1 to 1000');
Table DEMO created.
SQL> create index DEMO_N on DEMO(n);
Index DEMO_N created.
SQL> select * from DEMO where n=1;
N
-
1
In the past I already shared with you the scripts I use to download the Oracle Database documentation. The aim of this short post is to reference the scripts I just wrote for 18c.
Happy downloading as well as happy reading!
It all seems simple enough. You pick a name for an object…and that is the only object that have than name right ? For example:
This the the fifth blog in a series of blogposts about Oracle database redo. The previous blog looked into the ‘null write’ (kcrfw_do_null_write actually) function inside kcrfw_redo_write_driver, which does housekeeping like updating SCNs and posting processes if needed, this blog looks into what happens when the log writer is actually posted by a process or if public redo strand buffers have been written into. In part 3 of this blog series (the log writer working cycle) it can be seen that when a session posts the log writer, it returns from the semaphore related functions, and calls ‘kcrfw_redo_write_driver’ directly, which otherwise is called inside ksbcti.
Inside the kcrfw_redo_write_driver function, the first thing of interest is executed only when the logwriter is posted, and the kcrfw_redo_write_driver function is called directly after returning from ksarcv and ksl_exit_main_loop_wait:
One of the cool things in 18c is the ability to merge partitions without causing a service interruption. Here’s a video demonstration of that in action:
This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the Oracle Cloud services that will be running 18c in the near future.
Although the recently released Oracle 18c Database is really just 12.2.0.2 under the covers, there are a few little features and enhancements that are of interest from an indexing perspective. These include: Memory Optimized Rowstore Scalable Sequences Oracle Text indexing enhancements, such as automatic background index maintenance and new optimize index options JSON Search Index […]
Every so often, we get a request to duplicate a database for one of our customers using an Oracle Database Appliance (ODA). The process for doing that is relatively straightforward, but there are a couple of nuances along the way so I thought I’d write it up as a blog post in case it’s of use to others. Obviously, I have obfuscated any customer-specific information to protect their identity.
The first nuance is to understand what database is being used as the source for the clone. Generally, a request for cloning will be something like this:
“We are creating a new environment which needs a new database to be set up. Please copy P1_SRV_T and restore as P1_SRV_F”.
Recent comments
12 weeks 6 days ago
25 weeks 15 hours ago
29 weeks 2 days ago
30 weeks 1 day ago
34 weeks 5 days ago
1 year 3 weeks ago
1 year 24 weeks ago
2 years 1 week ago
2 years 38 weeks ago
2 years 38 weeks ago