About a month ago, I walked off a plane into the terminal at SJC. As I hiked that long walk to the exit, I heard a familiar and annoying sound, the Emergency Alert System sound. The sound was muffled, and it took me a few more steps to realize it was my own phone bleating […]
Category Archives: Uncategorized
Dang, been a while since the last posts! A lot of water under the bridge since then.
We’ve ditched a few people that were not really helping anything, and are now actively looking at cloud solutions, "big data" use, etcetc.
Meanwhile, there is the small detail that business as usual has to continue: it’s very easy to parrot about the latest gimmick/feature/funtastic technology that will
Aanstaande dinsdag 4 maart wordt er door Oracle een Oracle EMEA Virtual Developer Day georganiseerd. Je kunt je hiervoor gratis inschrijven en live, via het internet, deze volgen, mocht je in de gelegenheid zijn. Je hebt de mogelijkheid om zelf mee te …
We are using Cisco AnyConnect to provide VPN for our users (which we authenticate via RADIUS). Recently we needed to provide some users with static IP addresses. For IPv4 this was easy since the ASA we are using supports RADIUS … Continue reading →
How well do you communicate with your customers? Are you listening, does your customer hear what you are saying? Every day we interact with our customers, team mates, colleagues and managers. Is your message getting delivered? 1. Actively listen. It is easy to be on a call, but no one can see you “nod”. Make…
In the first part, I explained that Incremental Statistics are designed to allow a partitioned tables Global Statistics to be updated based on a combination of
1) Statistics gathered by analysing the contents of one or more partitions that have just been loaded or have been updated (and see this blog post for more depth on what ‘updated’ means!)
2) The statistics of existing partitions which are represented by synopses that are already stored in the SYSAUX tablespace.
Using this combination, we can avoid scanning every partition in the table every time we want to update the Global Stats which is an expensive operation that is likely to be unfeasible every time we load some data into a large table.
For me, the key word here is Incremental. Global Statistic updates are an incremental process, building on previous statistics (represented by the synopses) and only updating the Global Statistics based on the changes introduced by loading new partitions.
Understanding this might clear up another area of confusion I keep coming across. After upgrading their database to 11g, people often want to try out Incremental Global Stats on one of their existing large tables because they’ve always struggled to keep their Global Stats consistent and up to date. Maybe it’s just the sites I work at but I’d say this is the most popular use case. Incrementals for a planned large partitioned table in your new systems might be a sensible idea, but there are a lot more existing systems out there with Global Stats collection problems that people have struggled with for years.
Most people I’ve spoken to initially had the impression that they simply flick the INCREMENTAL switch and perhaps modify some of the parameters to their existing DBMS_STATS calls so that GRANULARITY is AUTO and they use AUTO sampling sizes. All of which is discussed in the various white papers and blog posts out there.
Then they get a hell of a surprise when the very first gather runs for ages! How long is ages? I don’t know in your particular case but I’ve seen this running for hours and hour and hours and people are crying in to their keyboards wondering why something that was supposed to make things run more quickly is so much slower than their usual stats calls.
The best way I’ve found to explain this phenomenon is to concentrate on the synopses that describe the existing partitions. Where do you think they come from? How are they calculated and populated if you don’t ask Oracle to look at the existing data in your enormous table? That’s what needs to happen. In order to make future updates to your Global Stats much more efficient, we first need to establish the baseline describing your existing data that Oracle will use as the foundation for the later incremental updates.
Generating the synopses as the baseline for future improvements will be a relatively painful for the largest tables (if it wasn’t, you probably wouldn’t be so interested in Incrementals ), but it does only have to happen once. You just need to understand that it does have to happen and plan for it as part of your migration.
My personal suggestion is usually to just delete all of the existing stats and start from scratch with modern default parameter values and tidy up any stats-related junk that might be lingering around large, critical tables. Painful but probably worth it!
The Oracle 12c client does not have a ocijdbc11.dll, so when you try to login with your 12c Oracle Client (oci thick-driver), you may see : no ocijdbc11 in path Don’t worry, this boils down to the jdbc driver ojdbc6.jar. Just overwrite your sqldeveloper/jdbc/lib/ojdbc6.jar with the one from your 12c client. The same applies to […]
Sitting in an airport, waiting for a plane, I decided to read a note (pdf) about Flashback data archive written by Beat Ramseier from Trivadis. I’d got about three quarters of the way through it when I paused for thought and figured out that on the typical database implementation something nasty is going to happen […]
I just stumbled across this and could not find it anywhere else on the net. I set up a ZFS Appliance with Oracle VM and their storageconnect plugin according to the documentation pdf (which are pretty easy step-by-step instructions) but … Continue reading →
What’s the most elaborate thing you have done with DataPump? So there I was, given the requirement to export multiple partitions for multiple tables where each partition has its own dump file having the format “tablename_partitionanme.dmp”, pondering how this can be done efficiently. With the following metadata and requirements, what approach would you take? If…