Today I've been looking at why some SQL is slower in one database than another. There can of course be myriad of reasons for this. Everything from you didn't gather stats to you did gather stats and everything conceivable in between.
I personally find it harder and harder to work out what the optimizer is doing now, more and more intelligence gets put into it with every release which makes it all the more difficult (more...)
Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options
- Database point in time recovery (DBPITR)
- Tablespace point in time recovery (TSPITR)
- Flashback technology
In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)
It’s been a year (and a week) since I moved from the small Israel to the huge Canada. I knew it wouldn’t be easy both personally and professionally, but until you actually do this you don’t know how strange it is. I’d like to share with you some of the professional difficulties I’ve experienced in […]
I was reading a very interesting article on Uber’s move from Postgres to MySQL. I really like it when IT professionals and/or companies take the time to explain their technology decisions. It’s a brave thing to do, because it’s easy for people to jump on the bashing bandwagon (“Ha ha … Company X chose Y and now they’re bust” etc etc). It’s the same reason you rarely see detailed customer reference information (more...)
That’s The Way We’ve Always Done It
I recently updated the EMC best practices guide for Oracle Database on XtremIO. One of the topics in that document is how many host LUNs (mapped to XtremIO storage array volumes) should administrators use for each ASM disk group. While performing the testing for the best practices guide it dawned on me that this topic is suitable for a blog post. I think too many DBAs are still (more...)
In previous versions, it was “best practice” to always get the most current opatch (patch 6880880) from MOS. Unfortunately, with Enterprise Manager Cloud Control 13c, this is problematic at the moment. The reason is that OMS 13.1 is shipped with OPatch 13.6:
[oracle@em13c ~]$ opatch version
OPatch Version: 18.104.22.168.0
Currently, OPatch 13.6 is not available in MOS. Only OUI Nextgen (more...)
Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:
ADAM@pdb1 > select max(amount_sold) from sales where channel_id=9;
ADAM@pdb1 > @lastplan
SQL_ID 3hrvrf1r6kn8s, child number 0
select max(amount_sold) from sales where channel_id=9
Plan hash value: 3593230073
| Id | Operation | Name | Rows | Bytes | Cost (more...)
If you want to know more about the Oracle Private/Hybrid Database Cloud using
the latest Enterprise Manager Cloud Control 13c, then this is the book.
Just published on August 5th 2016, and achieved Amazon Best Sellers Rank: #11 in all Oracle Books. Read more about the book in my LinkediIn Pulse post:https://www.linkedin.com/pulse/oracle-database-cloud-cookbook-porus-homi-havewala-ocm-
This book will also be available in Oracle Open World.
Porus Homi Havewala
(Oracle Certified Master)
(Oracle ACE Director Alumni)
This is the second part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using a trivial PL/SQL loop (see part 1
) the test harness executes a SQL statement that performs logical I/O only, so no physical I/O involved.
In order to achieve that a variation of Jonathan Lewis' good old "kill_cpu" script got executed. In principle each thread performed (more...)
Session 1 starts UPDATE and nothing else.
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;
1 row updated.
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
Monitor blocking locks
$ sysdba @b.sql
SQL*Plus: Release 22.214.171.124.0 Production on Sat Aug 6 02:55:03 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Oracle Database (more...)
I have been using VMware Player to build test virtual machines on my laptop with an external drive for some time now. I used to use the free VMware Server. My test VMs weren’t fast because of the slow disk drive but they were good enough to run small Linux VMs to evaluate software. I also had one VM to do some C hacking of the game Nethack for fun. I got a lot of good (more...)
I got a call to a look at a performance problem involving LOBs a little while ago. The problem was with an overnight batch that had about 40 sessions inserting small LOBs (12KB to 22KB) concurrently, for a total of anything between 100,000 and 1,000,000 LOBs per night. You can appreciate that this would eventually become a very large LOB segment – so before the batch started all LOBs older than one month were deleted.
This is a blog not related to Oracle products in any way.
This post is specific to apple Airport Extreme and Express wifi routers. However, in general: if you have multiple (unix/linux) servers, it makes sense to centralise the (sys)logging of these servers, in order to get a better overview on what is happening on these servers. I would want to go as far as saying that if you don’t you are simply (more...)
In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]
Confession: I have not been using OEM for decades since there was never a real need for it and it’s starting to take its toll on me.
Throughout the day, I keep getting paged from OEM – Message=Number of failed login attempts exceeds threshold value.
The information provided is utterly useless, e.g. what is the threshold value and what’s the error code.
What would be useful is to provide the SQL used for the (more...)
I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.
The British part of me wants (more...)
Sometimes we end up in a situation where our long running transaction is not completing and we are also not sure how much further time it’s going to take. This happened with one of our DBA where they found MLOG to be bloated because of one orphan snapshot entry. Orphan entries are the one where actual site is not registered on master (no entry in DBA_REGISTERED_SNAPSHOTS), but they see entry for MLOGS (entry in DBA_SNAPSHOT_LOGS). This could happen if (more...)
This is just a short 12c update on my post about gathering statistics on tables with many columns
from some time ago.I'm currently investigating the "Incremental Statistics" feature in 126.96.36.199 for a client, which probably will be worth one or more other posts, but since we're still in the process of evaluating and installing various patches it's too early to write about that.As part of the investigation I've noticed a (more...)