In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.
Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?
On our production system we’ve enabled the collection of PL/Scope metadata. Since this is a SmartDB/PinkDB-application (business logic and queries in the database), this makes it really easy to find, inspect and modify the source code of queries that doesn’t run efficiently. Now it’s even easier using reports in Oracle SQL Developer.
Working on a routine for dropping old partitions from a log table, I realized that I don’t have to know the name of the partition I want to drop. I can just specify a value for the partition key and Oracle resolves the partition for me. Here’s how.
I had a case where I had to match some names and needed to find a set of ways to clean and match strings. The solution I ended up with was a combination of regular expressions, the NLSSORT-function and the UTL_MATCH-package with the Jaro-Winkler algorithm.
Oracles function result cache (FRC) can in certain cases give a considerable performance boost. Application context is another useful feature. How does FRC work when the function result relies on context-settings? And how can we make them play well together?
Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!
PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.
I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.
I read this blog-post by Connor McDonald the other day about the Advanced Network Compression and network transfer savings. It reminded me of a feature not many know of and comes without an extra cost option. In fact, it’s on by default.
In-database archiving is a neat, new 12c-functionality that lets you logically “delete” rows by changing a flag in a column: A “soft-delete”. How does this functionality work in relation to foreign keys?
- nice theory... 