TweetShareSharePin0 SharesWe’ve all been there. You have an application bought from some vendor that has less than adequate knowledge about the Oracle Database. The solution underperforms even though the SQL itself looks OK. You dig into it and it turns out the solution retrieves a lot of data but each fetch gets too few rows from the database and thus the latency of getting more data is 99% of the time it takes. The result is of course that there […]
Performance
Enable Enterprise Manager Express
TweetShareSharePin0 SharesThere are many blogs about the product and so on. I want to recommend that you enable and give your developers access to it. Setting them up and getting them to scale the mountain of complexity that is the normal Enterprise Manager is probably not what you want to do. However, EM Express is a slimed down version focusing pretty much on just what a developer ought to care about, seeing the processes in the database and data about […]
Add hint to SQL you cannot modify
TweetShareSharePin0 SharesThe last post showed how to lock the plan for a SQL that switched plan every now and then. Another common issue one can encounter is that of a SQL that uses a suboptimal plan and for which you’d want it to use a different plan. The way to instruct the optimiser to use a different plan is to use a hint to cause it to change how it accesses the table. But often the SQL is in application […]
Lock the plan for a SQL
TweetShareSharePin0 SharesA SQL that sometimes just uses a plan you prefer it to not use is fairly common. There are always reasons for unstable plans, but more important than knowing the reason is often to make it stick to a certain plan. This has historically been done with a lots of ways from changing the SQL, adding hints, adding incorrect stats to influence the optimizer, using any of the techniques that came before SQL Plan Management (SPM). All of these […]
Grabbing an AWR without access to the database server
TweetShareSharePin0 SharesGetting an AWR can sometimes be difficult. If you are on an autonomous database you cannot log in to the database server and run your trusty old script for it. Other times you may not have the access and cannot get the DBA to find time to help you with it every time you want to look at one. Fortunately you can leverage your PL/SQL skills and just get it through any tool you want to use. The first […]
Dumb Oracle refuses to use my great index
TweetShareSharePin0 SharesLet me tell you a story about a developer, let’s call her Sue, that had proved that Oracle was having mood swings and just didn’t use a perfectly good index even though it was the perfect index to use. This of course made Sue very upset and she spared nobody from her bitter words about how unreliable and temperamental the database was. It just wasn’t willing to use the index because it was stubborn. Poor Susan was the DBA […]
Grabbing sql_id for the SQL
TweetShareSharePin0 SharesI read Daniel’s recent blog about five ways to grab the sql_id and remembered reading Connor’s about getting it before you run a SQL. At the time it hit me that one ought to be able to use this to simplify the workflow. Often we run a SQL and then use some marker – typically in the form of a comment in the SQL – to look up the sql_id to then use for other lookups. It would of […]
Waiting for direct write temp – but what file?
TweetShareSharePin0 SharesThis is essentially a not for myself for the next time I forget. It seems to be like once every other year I figure this out. It is not too easy to google for so it usually takes a bit too long. So you have identified the event “direct path read temp” or “Direct path write temp” as your main culprit in a SQL. Great, now let’s just see what temp file it is. That is how I’ve started […]
When views causes havoc
TweetShareSharePin0 SharesViews are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code. But… There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration. I am asked to spend a day giving said […]
Improving data move on EXADATA V
TweetShareSharePin0 SharesWrap-up This is the last post in this series and I’ll not introduce anything new here, but rather just summarise the changes explained and talk a bit about the value the solution delivers to the organisation. Let’s first review the situation we faced before implementing the changes. The cost of writing the log-records to the database was that all the parallel writing from many different sources was such that it introduced severe bottlenecks to the point that the logging feature […]