In 2016, 340 people answered 6,185 quizzes in the Database Design quiz on the PL/SQL Challenge. I'm pleased to see so many people taking an interest in how to...
In 2016, 340 people answered 6,185 quizzes in the Database Design quiz on the PL/SQL Challenge. I'm pleased to see so many people taking an interest in how to build database applications! And I'm delighted to announce the 50 players below have qualified for the Database Design Annual Championship 2016. They've all shown grit and a solid understanding of Oracle Database. This is currently scheduled to take place on 15 March 2017. The number in parentheses after their names are...
In 2016, 340 people answered 6,185 quizzes in the Database Design quiz on the PL/SQL Challenge. I'm pleased to see so many people taking an interest in how to build database applications! And...
.Code { font-family: monospace; white-space: pre; padding-left: 10px; } It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and...
.Code { font-family: monospace; white-space: pre; padding-left: 10px; } It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and on-premises! With it comes a whole host of new features to help you write better, faster applications. Here's my rundown of the top 12 new features to help you when developing against Oracle Database. Easier, Better, Faster, Stronger JSON JSON from SQL JSON in PL/SQL Looooooooooong Names Robust Code using Constants for Data...
.Code { font-family: monospace; white-space: pre; padding-left: 10px; } It's here! Oracle Database 12c Release 2 (12.2) is available on Oracle Cloud and on-premises! With it comes a whole host of new...
In part seven of the PL/SQL Challenge optimization series we improved performance by extracting a query out of PL/SQL and pasting it directly into the main...
In part seven of the PL/SQL Challenge optimization series we improved performance by extracting a query out of PL/SQL and pasting it directly into the main statement. This was a scalar subquery to find the most recent date a player visited each quiz. Simplifying, our query now looked like this: SELECT (SELECT created_on FROM (SELECT qv.created_on FROM qdb_quiz_visits qv WHERE qv.user_id = :user_id_in AND qv.quiz_id = q.quiz_id ORDER BY qv.created_on DESC) WHERE ROWNUM = 1),...
In part seven of the PL/SQL Challenge optimization series we improved performance by extracting a query out of PL/SQL and pasting it directly into the main statement. This was a scalar subquery to...
In the previous post we refactored a SQL query on the PL/SQL Challenge so it no longer called PL/SQL functions. This improved performance by removing context...
In the previous post we refactored a SQL query on the PL/SQL Challenge so it no longer called PL/SQL functions. This improved performance by removing context switches. The reduction in query execution time was ten seconds. The observant among you will have noticed that the statement processed over 60,000 rows. That's a saving of less than 200 microseconds per row. That's a tiny overhead for individual rows. At this point you may be saying: "Big deal. I'm calling the function...
In the previous post we refactored a SQL query on the PL/SQL Challenge so it no longer called PL/SQL functions. This improved performance by removing context switches. The reduction in query execution...
Waaaay back in the first entry in this series we tuned one of the queries on the PL/SQL Challenge. The index we created improved its performance significantly....
Waaaay back in the first entry in this series we tuned one of the queries on the PL/SQL Challenge. The index we created improved its performance significantly. This missed the bigger picture however. The query was embedded in a PL/SQL function. The main SQL statement on the Library page called this function. We could still make noticeable performance gains by extracting the query out of PL/SQL and placing it directly in the main select. Why? Due to context switching. SQL and...
Waaaay back in the first entry in this series we tuned one of the queries on the PL/SQL Challenge. The index we created improved its performance significantly. This missed the bigger picture...
So far in this series we've made substantial improvements to the performance of the Library page query on the PL/SQL Challenge. This was taking several minutes...
So far in this series we've made substantial improvements to the performance of the Library page query on the PL/SQL Challenge. This was taking several minutes to execute; our efforts have reduced this to a few seconds. These efforts were all directed at the main body of the query. There are also a number of subqueries in the select clause. Oracle is still doing a lot of work processing these, as we can see from the following autotrace summary: The SORT (AGGREGATE) steps are...
So far in this series we've made substantial improvements to the performance of the Library page query on the PL/SQL Challenge. This was taking several minutes to execute; our efforts have...
In part four of this series, we saw how the Library page query from the PL/SQL Challenge was using a crazy execution plan. Not only did it full scan three...
In part four of this series, we saw how the Library page query from the PL/SQL Challenge was using a crazy execution plan. Not only did it full scan three indexes from the same table, it used a fourth to access the table itself. We solved this by rewriting the query and creating a new index. It's worth taking a step back to look at all the indexes to see what impact they have on the query. Here's the original, slow, execution plan: The three highlighted index full scans each...
In part four of this series, we saw how the Library page query from the PL/SQL Challenge was using a crazy execution plan. Not only did it full scan three indexes from the same table, it used a fourth...
In the previous post in the PL/SQL Challenge optimization series, we'd made huge performance gains simply by removing optional bind parameter clauses. The main...
In the previous post in the PL/SQL Challenge optimization series, we'd made huge performance gains simply by removing optional bind parameter clauses. The main body of the query is still doing a significant amount of work however and taking several seconds to execute. Fortunately there are simple changes we can make to improve its performance. Let's take a look. Reviewing our trusty autotrace report we can see the following: The red boxes highlight key issues in the execution...
In the previous post in the PL/SQL Challenge optimization series, we'd made huge performance gains simply by removing optional bind parameter clauses. The main body of the query is still doing...
In the previous article in this series we were looking at the performance of the Library page query on the PL/SQL Challenge. We identified that the main...
In the previous article in this series we were looking at the performance of the Library page query on the PL/SQL Challenge. We identified that the main culprits were two table accesses. Together these contributed to nearly 90% of the work (buffer gets) performed by the query. Having identified this the next question is how do we fix it? There's a surprisingly simple change that results in huge performance gains. If you're a fan of adding more clauses to a query to improve...
In the previous article in this series we were looking at the performance of the Library page query on the PL/SQL Challenge. We identified that the main culprits were two table accesses....
