Oracles function result cache (FRC) works, in simplified terms, by caching the result of a PL/SQL function. This can greatly improve performance, but it can also lead to performance problems.
Read More »Tag: Oracle
Forall is still row-by-row
I came over some code that used the forall construct to update rows in a large table. Quite a few rows were updated and the batch took some time to complete. Usually, using forall is a good idea, but sometimes other constructs can perform better.
Read More »Which tables could need updated statistics?
Does your tables need updated statistics for the optimizer? Here’s a couple of views that could be useful in the gathering of information.
Read More »Statistics gathering parameters for partitioned tables
When partitioned tables get a bit large, these parameters may be suitable to set.
Read More »DataGrip cutting DDL short, case 2
My preferred tool for writing PL/SQL code has been DataGrip from JetBrains for the last few years. Working on a case with reference partitioned tables, I came over some strange errors. At first I thought Oracle had a lot of bugs in the partitioning syntax, but it turned out to be DataGrip messing with my DDL!
Read More »Proxy user logon #JoelKallmanDay
Are you using proxy logon when connecting to your Oracle database? Why not?
Read More »
Yet another reason to unit test your (PL/SQL) code
The last few months, I’ve had the pleasure of using utPLSQL v3, a unit testing framework for PL/SQL, on a new project that I’m working on. Sure – writing tests takes a bit extra time, but it’s such a huge help when you’re constantly refactoring a system that grows from zero.
LOVs or Lists of values – one solution fits quite a few
Applications often have the need to have selection lists present and/or limit the possible choices for entry in a field. Some lists need to be static, and others need to be a bit more flexible in terms of adding new values. I’ve seen several solutions to this, and having been “stuck” on 11g for a long time, I thought I’d test out a “new” way of doing it using a composite foreign key and hidden columns.
Debug domino
We found a bug in some old code. And we fixed it. Then other stuff started failing. It turned out that there were some other code, using the same function, that worked because of the bug. That forced us to roll back the fix and thereby re-introduce the initial bug that weren’t as critical.
So – we had to find a way to find out which code-paths were working because of the bug so we could fix those.
Reviewing statements with hints, using PL/Scope
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.
- nice theory... 