Last week nearly 700 DBAs joined me to learn about application-level tuning techniques to make SQL faster. The recordings for the Masterclass series are online. Here are the slides from my talk:
There was a whole host of questions during the session, here they are with my responses:
All good frameworks will have options to use bind variables and batch processing. Work with your development team to ensure they understand and use these features.
You can check for statements in v$sqlstats with the same force_matching_signature:
select force_matching_signature, count(*) from v$sqlstats where force_matching_signature > 0 group by force_matching_signature having count(*) > 10 order by 2 desc;
Similar was deprecated in 22.214.171.124, so this is best avoided.
A SQL Profile is essentially an extended set of hints. This helps the optimizer find the right plan and can be useful when basic statistics produce poor row estimates.
An explain plan is the optimizer's prediction of how it will execute the query. It doesn't look (peek) at bind variable values when doing this, whereas it does when running the query and getting its execution plan.
So explain can report a very different plan to the one the database uses when running the query.
V$SQL_bind_capture uses sampling to capture the values, so it won't store the values for every execution.
To continue processing past errors when using forall, use the save exceptions clause.
No. This is an undocumented hint. Hints and undocumented features are both things you should avoid using.
SQL Plan Baselines check the signature of the statement. This is a version of the SQL statement with standardized case and white space formatting. This takes no account of literal values, so there isn't a force matching option as with profiles.
Look for most resource intensive operations. These are those that either:
Oracle SQL Developer has a SQL HotSpot button to help you find these:
Once a SQL statement starts executing, its plan is fixed. So currently executing statements won't use it. Any queries that start after implementing profile can use it.
You need enough memory to hold the batch. If there's not enough available, you'll need to limit how many rows you process in each batch.
You use for update in a query to lock the rows before changing them. This helps you avoid concurrency problems. You still need to write the update though.
Ensure the where clause identifies all and only the rows to change. If a transaction runs two or more updates you need to take care to avoid deadlocks. To do this, use select for update to lock all the rows at the start of these transactions. I discuss this further in this video:
There are several things that can lead to ORA-01555 errors. David Fitzjarrell has advice for dealing with ORA-01555 in the various scenarios that can cause it.
You should only commit when the transaction is complete. So after you've updated/inserted/deleted all the rows necessary to keep the data consistent.
It might, but this isn't guaranteed.
Test with different values for batch size to find the sweet spot for the transaction! Steven has more tips for choosing the bulk collect limit.
If you want to influence the developers, you need to build a relationship with them. Get involved in the development process - invite yourself to meetings if necessary!
This will help you understand why the developers are taking the approach they do. It also gives you the chance to offer alternatives early in the development phase, when it's easiest to change tack.
Just remember that going in and telling them they're "doing it all wrong" is unlikely to win you friends or their trust. Be available to help your development team; the more you can work with them to build solutions, the more likely they are to involve you in the design process.
When using sql%notfound with bulk collect, you may end up with incomplete processing.
You can also do this for concurrency protection. It means running an extra though, so is more work.
Optimizing dynamically generated queries is the same as optimizing static SQL. The challenge is knowing what all the possible variations are!
It can be valid for one SQL statement to have multiple good plans. This will happen when you're filtering columns with data skew using bind variables. If you want to limit a queries to one specific plan or a few possible plans, look into SQL Plan Management.
In the PGA.
These reside in the PGA. You can measure PGA usage in your session with:
select ms.value from v$mystat ms join v$statname sn on ms.statistic# = sn.statistic# and sn.name = 'session pga memory';
Oracle Database can optimize with queries to avoid accessing one table many times.
Partition by has been available since Oracle Database added analytic functions, way back in 8i!
The overlaps function is undocumented.
Probably no. By staging the data, you're adding an extra set of writes and queries to the process. As this is more work, it's unlikely to make the process faster.
A whole host of things! Key things include:
For more details, see the links below or check the SQL Tuning Guide.