The Securing Account Details story in the previous blog post highlighted two problems with SQL that uses literals instead of bind variables: decreased...
The Securing Account Details story in the previous blog post highlighted two problems with SQL that uses literals instead of bind variables: decreased performance and security holes. In this post we'll investigate the impact on execution times in more detail. Why do bind variables matter for performance? Before Oracle runs a SQL statement it checks it's valid and determines how to access the tables and join them together. This is called parsing. The optimizer has the task of...
The Securing Account Details story in the previous blog post highlighted two problems with SQL that uses literals instead of bind variables: decreased performance and security holes. In this post...
In a previous article, we looked methods for fetching a row from a table along with a number of rows either side of it. In the comments, Stew Ashton offered a...
In a previous article, we looked methods for fetching a row from a table along with a number of rows either side of it. In the comments, Stew Ashton offered a solution using the match_recognize clause introduced in 12c. How do the numbers stack up on the performance of these different methods? Let's find out! To test this, I created a one million row table (with this script). For the tests I looked for one row along with the five either side of it (:lname := 'Aaaq', :lvl :=...
In a previous article, we looked methods for fetching a row from a table along with a number of rows either side of it. In the comments, Stew Ashton offered a solution using the match_recognize clause...
Continuing the query tuning video series, here are a couple more videos to help you get started with query optimization. First up, comparing execution plans....
Continuing the query tuning video series, here are a couple more videos to help you get started with query optimization. First up, comparing execution plans. When you're working with large queries it can be difficult to spot whether or not your tuning efforts have altered the plan. Fortunately SQL Developer comes with a handy execution plan comparison tool. To find out how to use it, watch this video: Query Tuning 101: Comparing Execution Plans Next up, predicates. You may...
Continuing the query tuning video series, here are a couple more videos to help you get started with query optimization. First up, comparing execution plans. When you're working with large queries it...
In the PL/SQL Challenge optimization series, we've been using autotrace. This has helped us figure out where our SQL query was going wrong and what we need to...
In the PL/SQL Challenge optimization series, we've been using autotrace. This has helped us figure out where our SQL query was going wrong and what we need to do to optimize it. At this point I'm going to take a step back and look at the how, why and what of using autotrace. If you're not familiar with this tool, keep reading for a short video series showing you how to use it in SQL Developer, why you should do so instead of relying on explain plan and what to look for in the...
In the PL/SQL Challenge optimization series, we've been using autotrace. This has helped us figure out where our SQL query was going wrong and what we need to do to optimize it. At this point I'm...
