Oracle Database as our consultants live it every day: installation and patching, upgrades, RMAN backup and recovery, Data Guard, RAC, performance tuning, multitenant, licensing, and engineered systems such as ODA and Exadata.
By Franck Pachot . In the ‘traditional’ row store, the indexes are maintained at the same time as rows are changed. It’s different with the In-memory Column Store. Changes are maintained by background processes. When rows are changed, the Snapshot Metadata Units (SMU) logs the changes and In-Memory Compression Units (IMCU) are re-populated asynchronously. Let’s see an example. test case…
By Franck Pachot . In a previous post I presented how Dbvisit replicate can capture changes and write them in an audit table with old and new values. Here is how to do it wen you want only the new values – with same column names. What I want to get here is a table with: same column names as…
By Franck Pachot . I’m currently presenting at Swiss BI Day in Geneva about Oracle In-Memory option to improve analytic queries. Here are the slides, uploaded to SlideShare. http://fr.slideshare.net/pachot/oracle-inmemory-column-store-for-bi Don’t hesitate to comment here. Thanks a lot to the Swiss Business intelligence Network for the organization of the event.
By Franck Pachot . Next Tuesday in Geneva I’m presenting Oracle In-Memory Column Store to Business Intelligence people at the Swiss BI Day After defining what is an analytic query, I show the different approaches to optimize that in Oracle, IMCS being the latest one. Here are some details about that slide. What is an analytic query? First definition –…
By Franck Pachot . At our bi-annual dbiXchange I was talking with Nicolas Jardot about his presentation on Result Cache (don’t forget Jérome witt session about RC at DOAG) where he has shown an unexpected behavior on ‘fetch first n rows queries’. That behavior – if it is not a bug – can also be a good thing when using…
By Franck Pachot . When you have a query failing in ‘ORA-01555: snapshot too old: rollback segment number … with name … too small’ you have two things to do: Convince the developer that the rollback segment is not too small because the message text comes from old versions Find information about query duration, undo retention and stolen blocks statistics.…
By Franck Pachot . It don’t want to rub salt in the wound about 12c upgrades and Adaptive Dynamic Sampling being overzealous, but here is a case with an unexpected consequence: you see nothing in the ‘Database Feature Usage’ since migration. The photo novel starts like this: we are in September and when I check the ‘Database Feature Usage’ page…
By Franck Pachot . I can hear a lot of complaints about the instability coming from the adaptive features introduced into the optimizer logic at each release. Giving more intelligence to the optimizer is very good to improve the response time for generated queries, BI, ad-hoc reporting. But when you have an OLTP that works for years with its static…
By Franck Pachot . You want the amazing features of Oracle SQL and PL/SQL with minimal cost. Your database is too large for Oracle XE edition, but not big enough to require the cost of Enterprise Edition. You accept to do things manually, have maintenance windows. You are ok with a RTO of 5 minutes (time to switchover with Dbvisit…
By Franck Pachot . Do you think that online operations are available only on Enterprise Edition? That changed in 12c, there is an operation that is online (without the need for the ONLINE keyword) and available in Standard Edition and a very important feature for performance tuning: invisible indexes. Example I’ll show it from the SCOTT schema where I’ve added…
By Franck Pachot . When you are waiting on a locked row, the locked resource is the transaction that has locked the row. We have no information about which row is locked. Here is how to get it from V$SESSION. In a first session I lock a row in the SCOTT schema: SQL> select * from SCOTT.SALGRADE where grade=1 for…
By Franck Pachot . In previous posts, I’ve installed SLOB on the Cloud Services, measured IOPS and CPU. There’s another resource that is even more important in a cloud environment: network. Tuneling port 1521 over ssh I’ve create a virtual instance by default where only ssh is opened: I don’t want to open other port, so I’m using it with…
