It’s always cool that you can learn stuff every single day, even on the most simple of topics. This one came from an AskTom question. We define a sequence to start with 100, and then alter it to modify the INCREMENT BY.
SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;
SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME : TEST_SEQ
MIN_VALUE : (more...)
Those of you who know me will know that I’m working at Cedar and we’re moving from PeopleSoft to the Oracle Cloud. I won’t talk about the company’s journey, but more how I’ve found the transition personally.
In brief, there’s a lot to learn.
Data Model knowledge
In the PeopleSoft world, once you know the table structures you could work significantly faster. Whether you were writing an SQR, an App Engine, an XMLP report, a (more...)
Just realized that I wrote a follow-up to Part 2 2 years ago and never posted it! So here’s the stripped-out key facts to round off auditing control a bit more:
Deleting O/S audit files is, like, totally necessary. In Oracle 12C (12.1), they are created by default as AUDIT_SYS_OPERATIONS defaults to TRUE now, so you are going to be filling up your AUDIT_FILE_DEST whether you realized it or not.
Oracle cost-based optimizer (CBO) is great, but sometimes it’s making wrong choices even when correct inputs are fed to it. In such cases, you need a tool to override CBOs choices, and one of the most popular tools is optimizer hints. The main reason they’re so popular is that they allow “quick-and-dirty” kind of fixes for performance issues (provided that query text can be altered). Other ways may be more reliable, but generally require more (more...)
You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”.
But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the good data from the bad data. For example, if your table looks like this:
SQL> select * from T;
Jan 14, 2016
6 rows (more...)
We are moving a production database from 10.2 Oracle on HP-UX 64 bit Itanium to 11.2 Oracle on Linux on 64 bit Intel x86. So, we are upgrading the database software from 10.2 to 11.2. We are also changing endianness from Itanium’s byte order to that of Intel’s x86-64 processors. Also, my tests have shown that the new processors are about twice as fast as the older Itanium CPUs.
This is the 13th installment of my blog series around Stream Processing and Analytics.
Last week the new release of Oracle Stream Explorer has been release, now under a new name Oracle Stream Analytics. I have written my own blog article about it. This new version is an impressive release with over 15 new major features! It really deserves the name change. Oracle Stream Analytics simplifies stream processing and enables Self Service Streaming Analytics applications (more...)
In today’s world, where everything is at the tip of your fingers, why are your enterprise systems glued to your office desk? Imagine if you could access and operate your Oracle Forms/EBS systems from any location. What if your Oracle Forms system was so agile, so easy to adapt, that any business function you do in Oracle Forms could be included in a mobile application? What would you include in your mobile (more...)
The developers complained that their test database was so much slower than production that they could not use it to really test whether their batch processes would run fast enough when migrated to production. They did not give me any particular queries to check. Instead they said that the system was generally too slow. So, I went through a process to find SQL statements that they had run in test and that normally run in production (more...)
The OTN database forum supplied a little puzzle a few days ago – starting with the old, old, question: “Why is the plan with the higher cost taking less time to run?”
The standard (usually correct) answer to this question is that the optimizer doesn’t know all it needs to know to predict what’s going to happen, and even if it had perfect information about your data the model used isn’t perfect anyway. This (more...)
Masking with Delphix (where duplicate blocks are shared making a new copy almost free storage wise and almost instantaneous) has 4 big advantages
Instant data, no copying
Ease of Use
Consistent across data centers and databases vendors
Virtual Data Masking
Delphix masking and virtualization is the most advanced solution in the market place, because Delphix doesn’t provision data. Instead of provisioning data, Delphix sets up pointers back to existing data for a new clone. When that (more...)
It’s been forever since I last shared any of my performance troubleshooting experiences at work. This week, I got a case that I think is worth publishing, and I decided to write about it in my blog. So, here we go…
A few days ago, I received a complaint about unstable performance of one of frequently running SQL reports on a 11gR2 database. Most of the time it completed within a couple of minutes, however, on (more...)