Obsessed with Oracle PL/SQL

The PL/SQL engine offers two profiler utilities to help identify performance bottlenecks in your application: 1. DBMS_PROFILER That's the name of the package that provides an API to the profiler that computes the time that your PL/SQL program spends at each line, in each subprogram . Saves runtime statistics in database tables, which you can then query. 2. DBMS_HPROF The hierarchical profiler; this utility reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. It accounts for SQL and PL/SQL execution times separately. Requiring no special source or compile-time preparation, it generates reports in HTML. You can also store profiler data and results in relational format in database tables for custom report generation (such as third-party tools offer). You can find lots more information about these two profilers in the documentation . In our  June 2nd 2020 Office Hours  session, I am very pleased to have Shashank B...

FORALL is a key performance feature of PL/SQL. It helps you avoid row-by-row processing of non-query DML (insert, update, delete, merge) from within a PL/QL block. Best of all, almost always, is to do all your processing entirely within a single SQL statement. Sometimes, however, that isn't possible (for example, you need to sidestep SQL's "all or nothing" approach) or simply too difficult (not all of us have the insane SQL writing skills of a Tom Kyte or a Chris Saxon or a Connor McDonald ). To dive in deep on FORALL, check out any of the following resources: FORALL documentation Videos at Practically Perfect PL/SQL  Tim Hall on Bulk Binds In this post, I am going to focus on special features of FORALL that make it easy to work with space collections: the INDICES OF and VALUES OF clauses. Typical FORALL Usage with Dense Bind Array Here's the format you will most commonly see with FORALL: the header looks just like a numeric FOR loop, but notice: no l...

PL/SQL is a key enabling technology in Oracle Database. You should make sure that you are aware of and take advantage appropriately key features of the PL/SQL language focused on performance. I offer a short list of those features below, along with links to related resources. Most of this is also capture in this  slide deck : Key Performance Features All of these are covered in the slide deck above; the links will take you to the documentation on these features. Click here for the overall section in the doc on performance and optimization. Bulk processing with FORALL and BULK COLLECT The function result cache Optimized execution of user-defined functions from SQL The NOCOPY Hint Automatic compiler optimization But Don't Forget About SQL Optimization Chances are you could take full advantage of all the features listed above and more in PL/SQL, and still end up with a slow application. That's because at the heart of every application built on Oracle Database...

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! From Oracle Help Center (a.k.a., documentation), we read : Data is said to be pipelined if it is consumed by a consumer (transformation) as soon as the producer (transformation) produces it, without being staged in tables or a cache before being input to the next transformation.   Pipelining enables a table function to return rows faster and can reduce the memory required to cache a table function's results.   A pipelined table function can return the table function's result collection in subsets. The returned collection behaves like a stream that can be fetched from on demand. This makes it possible to use a table function like a virtual table. In a nutshell, this means that the ca...

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! Last week, I published my first post in the Table Function series on pipelined table functions. I included this advice at the top of the post: Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature: You are unlikely to ever need a pipelined table function, but when you do, it is going to be  amazing . I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what they are doing with those functions, it becomes clear that they are not pumped up about the  pipelining . They are not, in fact,  benef...

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! [Gee, that was embarrassing. I start a series on table functions , and then it falters and stops. My apologies; I got so busy eating and drinking and breathing and sleeping and....anyway, back to the series !] In this post I introduce PL/SQL developers to the  pipelined table function . Here's my first piece of advice regarding this interesting and in many ways unique (within PL/SQL) feature: You are unlikely to ever need a pipelined table function, but when you do, it is going to be amazing . I bring this up right at the start because I have found over the years that many developers talk in the most glowing terms about pipelined table functions. Yet when I look at what...

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done! In my last post on table functions, I showed how I was able to reduce many Application Express  interactive reports  into one by pushing all the complexity into a table function, thereby reducing the query in the report to nothing more than a "parameterized view": SELECT *   FROM TABLE (      qdb_rankings.ir_other_ranking_tf (category_in  => :p443_category,                                 period_type_in      => :p443_period_type,                                 c...