X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

Subscribe to email updates

Tuning Application SQL: DBA Masterclass Week 3

Chris Saxon
Developer Advocate

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:

Sometimes developers are dependent on some middle layers like Hibernate which in the large part takes over the job of producing the SQL statements - what then?

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.

How can I identify queries that could be improved by using bind variables? Is that a dynamic performance view I can check for similar queries that different on literals only?

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;

Our DataCenter sets CURSOR_SHARING=SIMILAR to all the instances, it this a good practice or a palliative that might prove dangerous?

Similar was deprecated in 12.1.0.1, so this is best avoided.

And what about SQL Profile? A good possibility?

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.

Can we run reliable a explain plan when using bind variables and cursors?

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.

Why are all binds are not captured in this view v$sql_bind_capture?

V$SQL_bind_capture uses sampling to capture the values, so it won't store the values for every execution.

How about row level error handling in batch mode?

To continue processing past errors when using forall, use the save exceptions clause.

Is it good practice to use /*+no_cpu_costing */ hint on the Oracle 12c SQL queries?

No. This is an undocumented hint. Hints and undocumented features are both things you should avoid using.

Is there a way to apply a SQL plan baseline on SQL that does not use bind variables (similar as you can do with SQL profiles)?

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.

On Queries of more than 200 lines how to read or find which SQL is expensive?

Look for most resource intensive operations. These are those that either:

  • Process the most rows
  • Do the most I/O
  • Take the longest

Oracle SQL Developer has a SQL HotSpot button to help you find these:

When a SQL Profile is recommended by SQL Advisor and implemented to a SQL, when will the SQL Profile be used, if the SQL is already running, will it make use of the SQL Profile?

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.

When you use batch, is there no need for more memory?

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.

In this "order not shipped" example, we could have used the "for update" clause in order to directly update the order line with the shipment date?

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.

When using UPDATE, any trick to avoid deadlocks, decrease updated records or operations before commit should help?

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:

What are the best practices to avoid occurrence of ORA-01555: snapshot too old: rollback segment number nn with name "_SYSSMU61_1249989600$" too small error?

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.

What about commits after your initial update?

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.

If I got it right, the returning clause, in our case, can avoid deadlocks as well?

It might, but this isn't guaranteed.

How can you empirically determine an optimal batch size for the bulk processing?

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.

I agree with your approach, but in today IT Databases are not designed according to normalized tables and stored procedures, but developers want to use code generators and microservices and use databases as a commodity, in my position I am not really able to influence the way developers work

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.

You mentioned SQL%notfound might not work - why not?

When using sql%notfound with bulk collect, you may end up with incomplete processing.

Why don't we use select for update in the first select... insert scenario?

You can also do this for concurrency protection. It means running an extra though, so is more work.

Some applications generate queries in runtime and run in DB. How do we suggest best performance measures for bad queries in this case?

Optimizing dynamically generated queries is the same as optimizing static SQL. The challenge is knowing what all the possible variations are!

What are the possible reasons for multiple SQL plans for a statement and how can we stick to single best plan?

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 which memory pool are arrays stored?

In the PGA.

To prevent from reaching memory limit how will you calculate the size of an array?

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';

Can we do the same with WITH select AS (subquery)? It will also be executed once?

Oracle Database can optimize with queries to avoid accessing one table many times.

Partition by, is it available in older Oracle releases?

Partition by has been available since Oracle Database added analytic functions, way back in 8i!

What about function OVERLAPS, is it still "underground"

The overlaps function is undocumented.

For an (very) insert intensive application, would it be better to insert into non-constrained/non-indexed tables and later batch the data into proper tables, rather than continuously inserting into already indexed/constrained tables?

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.

What does SQL performance depend on?

A whole host of things! Key things include:

  • How you write the query
  • The quality of the table statistics
  • The physical data model (indexes, partitioning, etc.)
  • The logical data model (constraints, normalization, etc.)
  • The hardware of the database server
  • How the application sends SQL statements and processes their results

For more details, see the links below or check the SQL Tuning Guide.

Further Reading

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content