Oakies Blog Aggregator

Richard Foote's picture

Indexing The Oracle Autonomous Data Warehouse (Autobahn)

When the Autonomous Data Warehouse was initially released in March 2018, one of the notable restrictions was that it did not support typical Data Warehouse performance related structures such as Indexes, Partitioning or Materialized Views. The platform was deemed efficient enough to not require these features, which would unnecessarily complicate the autonomous environment. In September […]

khailey's picture

Amazon RDS cluster dashboard with Performance Insights

Amazon RDS Performance Insights (PI) doesn’t have a single pane of glass dashboard for clusters, yet. Currently PI has a dashboard that has to be looked at for each instance in a cluster.

On the other hand one can create a simple cluster dashboard using Cloudwatch.

PI, when enabled, automatically sends three metrics to Cloudwatch every minute.

These metrics are

  1. DBLoad
  2. DBLoadCPU
  3. DBLoadNonCPU

DBLoad = DBLoadCPU + DBLoadNonCPU

These metrics are measured in units of Average Active Sessions (AAS). AAS is like the run queue on the UNIX top command except at the database level. AAS is the average number of SQL queries running concurrently in the database. In the case of DB Load AAS,  the average is over 1 minute since the metrics are reported each minute, and represents the total average # of SQL queries running concurrently. The DBLoad AAS can be broken down into those queries that are runnable on CPU, which is DBLoadCPU, and those queries that are not ready to run on the CPU because they are waiting for some resource like an I/O to complete, a lock , a latch, or some  resource that can only be accessed in single threaded mode like a latch or buffer.

These metrics can be used to look at the health of the database.

For example we can only have as many SQL running on the CPU as there are vCPUs. If DBLoadCPU goes above the # of vCPUs then we know that some of those queries that are runnable on the CPU are actually waiting for the CPU.

We also know that when DBLoadNonCPU is low or near 0 then the queries are not waiting for resources and can execute. When DBLoadNonCPU goes up significantly then that represents an opportunity to optimize. For example if queries are spending half their time waiting for IO then if we could buffer that IO we could remove the IO wait and theoretically the queries could go twice as fast, doubling throughput.

By looking at DBLoadCPU for each instance in a cluster we can see if the load is well balanced and we can see if the load goes above the maximum CPU resources of the instance which would indicate a CPU bottleneck.

By looking at the ratio or percentage of DBLoadNonCPU to total DBLoad we can see how much time is wasted waiting for resources instead of executing on CPU. By show this percentage for each instance in the cluster in one graph we can see if any particular instance is running into a bottleneck. If so we would want to look the performance insights dashboard for that instance to get more detail about what is happening.

So let’s set up a cluster dashboard using PI data.

Create a RDS database instance with PI enabled : https://console.aws.amazon.com/rds

PI is supported on all RDS Oracle, Aurora PostgreSQL,  RDS PostgreSQL 10 , RDS SQL Server (except 2008) and on the most recent versions of  Aurora MySQL 5.6, RDS MySQL 5.6 & 5.7. See docs for details:  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html

In the case of this blog I created an Aurora MySQL 5.6  cluster with the original writer node and 3 reader nodes. You can create the initial instance with one reader node, then after creation, go to modify instanced and add reader node.

My nodes are shard1, shard2, shard3 and shard4.

After creating or modifying an instance to support PI, navigate to Cloudwatch and create a new dashboard (or add to an existing one)

Screen Shot 2019-03-28 at 12.17.34 PM

 

after creating a dashboard (or modifying an existing one) add a new widget, click “Add widget” button

Screen Shot 2019-03-28 at 12.22.46 PM

and for this example chose the line graph, the first option on the left of popup:

Screen Shot 2019-03-25 at 6.00.44 PM

at the bottom of the screen enter “DBLoadCPU” into the search field

Screen Shot 2019-03-28 at 12.24.02 PM

hit return and click on “per database metrics”

Screen Shot 2019-03-28 at 12.25.17 PM

 

My cluster instances are shard1, shard2, shard3 and shard4 so I click those

Screen Shot 2019-03-28 at 12.26.19 PM

and click “Create Widget” in the bottom left

Screen Shot 2019-03-28 at 12.28.46 PM

I got a surprise, as each shard instance was suppose to have same load but can see  something is wrong on shard4. Will investigate that as we go.

For now there are some options on the widget that I want changed. I want the graph to start at 0 (zero) and have a maximum of 4 ,  since my instances have 2vCPUs and I want to be able to look quickly at the graph to know where I’m at without having to read the axis everytime.  My max available CPU load is 2 AAS since I have 2 vCPU. I set the max at 4 so there is some head room to be able to show load about 2.

There is pull down menu in top right of widget. Click it and choose “Edit”

Screen Shot 2019-03-28 at 12.31.25 PM

Click the third tab “Graph options” and enter 0 for min and for max enter a value above the # of vCPUs you have on your instance. I have 2 vCPUs, so I enter 4.

Screen Shot 2019-03-28 at 12.32.14 PM

click update in the bottom right.

Screen Shot 2019-03-28 at 12.34.30 PM

I can see I’m running a pretty good load as shards 1-3 are running  around 1.5 AAS on CPU i.e. our SQL are asking for about 75% of the CPU capacity of the box. ( 100% * (1.5 AAS CPU / 2 vCPU) ).

I also see shard4 has a lower DBLoadCPU. Turns out I had turned off the load to that shard this morning and forgot, so I restarted it.

Now lets add a widget to see how efficient our load is, i.e. what % of the load is waiting instead of being runnable on CPU.

Create a new widget and search for DBLoad and choose the 2 metrics DBLoadNonCPU & DBLoad for all 4 instances. We will use them in a mathematical expression.

Screen Shot 2019-03-28 at 12.40.19 PM

create the widget, then edit it,

uncheck all the metrics

then we click “Add a math expression”

add the expression 100*(DBLoadNonCPU/DBLoad)  for each instance

Screen Shot 2019-03-28 at 12.45.32 PM

Looks like

Screen Shot 2019-03-28 at 12.47.12 PM

You can see I restarted the load on shard4 because the DBLoadCPU has gone up.

Now for the new graph click on title and type “% bottleneck”

edit it and add min 0 and max 100 ( i.e. 0-100% range), now it looks like

Screen Shot 2019-03-28 at 12.49.36 PM

Be sure and click “Save dashboard” so you don’t loose you work.

Now what do we see? well now that I’ve restarted the load on shard4, we see on “DBLoadCPU”,  the DBLoadCPU is pretty evenly balanced.

On “% bottleneck”  we see it’s pretty low except for shard1. To find out what is happening we have to navigate to the PI dashboard for shard1. Looks like shard1 is spending a lot of it’s load waiting on resources.

Let’s go to the PI dashboard for shard1.

 

Screen Shot 2019-03-28 at 12.58.21 PM

 

we can see that on the left most of the load was not CPU. CPU is green. All other colors are waiting for resources.

This is the write node and other activity is going on than the reader nodes which are only selects.

On the right hand side we can see CPU load went up so the ratio of Wait load in relation to CPU load and total load went down. This is what we see in the “% bottleneck” widget we made in Cloudwatch.

Now what are those resources that we are waiting on and what changed to make CPU go up? We can see that by exploring the PI dashboard.

For a demo on how to use PI to  identify and solve performance issues see

https://www.youtube.com/watch?v=yOeWcPBT458

 

 

dbakevlar's picture

Oracle RAC vs. SQL Server AG

As I have seen the benefit for having a post on Oracle database vs. SQL Server architecture, let’s move onto the next frontier- High Availability…or what people think is high availability architecture in the two platforms.

To RAC or Not to RAC

There is a constant rumble among Oracle DBAs- either all-in for Oracle Real Application Cluster, (RAC) or a desire to use it for the tool it was technically intended for. Oracle RAC can be very enticing- complex and feature rich, its the standard for engineered systems, such as Oracle Exadata and even the Oracle Data Appliance, (ODA). Newer implementation features, such as Oracle RAC One-Node offered even greater flexibility in the design of Oracle environments, but we need to also discuss what it isn’t- Oracle RAC is not a Disaster Recovery solution.

Disaster Recovery projects hold requirements for secondary, failover environments to be housed in a second data center or cloud and multiple databases, but that simply isn’t what Oracle RAC is. That doesn’t mean we can’t build out the solution, it just means, in our comparison to grant an Apple-to-Apple comparison, I’m going to add a second product into the scenario- Oracle Data Guard.

RAC on Azure VMs

For those trying to build out Oracle RAC on Azure, yes you can do it. You can build out a VM cluster and then use FlashGrid underneath the Oracle RAC installation. Would you want to do it? I just don’t know why. If you ask the customer questions, so far, every time, I’ve discovered a better way to reach the solution they are hoping to achieve and it didn’t involve RAC. It was quickly discovered that just because you needed something on premises, bringing in the cloud changes everything. Redundancy is built into the infrastructure, VMs can have clustering built in and I would prefer to put Oracle Data Guard on a VM over implementing all the moving parts with RAC.

So what is Oracle RAC bringing to the game?

https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac1-300x162.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac1-768x415.jpg 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

Now to directly discuss Oracle RAC with Data Guard comparison to SQL Server Always-on Availability Groups:

https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac2-300x169.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac2-768x432.jpg 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

Note that Oracle RAC has numerous nodes that are connected to one RAC database. This database seamlessly connects to all sessions, directed across the nodes by the Scan Listener. Transactions, including Undo and Redo is shipped between the nodes via the Global Cache.

In our architecture, we’ve added in Oracle Data Guard, which is a secondary replica that can then be sync’d with the archive logs, (similar to transaction logs in SQL Server) and can be made use of even more if implementing Active Data Guard.

https://dbakevlar.com/wp-content/uploads/2019/03/Oracle_rac3-300x149.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/03/Oracle_rac3-768x382.jpg 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

For SQL Server Always-on Availability Groups, the architecture above has implemented a primary with two replicas, one on-premises and a secondary in a remote data center, providing full DR in case of the primary data center being unavailable. Note that there is no write to the replicas back to the primary. They are solely isolated and designed for High Availability and Disaster Recovery.

When talking scalability, Oracle RAC allows the DBA to allocate more hosts to allocate more CPU and memory to a given database. The amount of nodes that can be allocated to a unique database is quite extensive, where in SQL Server AG, each node requires storage for its unique replica that must be provisioned along with the CPU and memory resources.

The Gist

So high level, it’s good to remember the following:

https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac4-300x164.jpg 300w, https://dbakevlar.com/wp-content/uploads/2019/03/oracle_rac4-768x421.jpg 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

I know I didn’t get in too deep with any of the technology here, but that wasn’t my goal. I wanted to try to give everyone, no matter which platform you specialize in, to see the other side of what is similar. Hopefully this helps.



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [Oracle RAC vs. SQL Server AG], All Right Reserved. 2019.

connor_mc_d's picture

Determined on Determinism

I’m feeling very determined on this one. Yes I have a lot of determination to inform blog readers about determinism, and yes I have run out of words that sound like DETERMINISTIC. But one of the most common misconceptions I see for PL/SQL functions is that developers treat them as if they were “extending” the existing database kernel. By this I mean that developers often assume that wherever an existing in-built function (for example TO_NUMBER or SUBSTR etc) could be used, then a PL/SQL function of their own creation will work in the exactly the same way.

Often that will be the case, but the most common scenario I see tripping up people is using PL/SQL functions within SQL statements. Consider the following simple example, where a PL/SQL function is utilizing the in-built SYSTIMESTAMP and TO_CHAR functions.


SQL> create or replace
  2  function f(i varchar2) return varchar2 is
  3  begin
  4    return i||'-'||to_char(systimestamp,'HH24MISS:FF');
  5    --dbms_lock.sleep(0.5);
  6  end;
  7  /

Function created.

Let us compare the output from the function when used within a SQL statement, with the results from same built-in functions used directly from the SQL statement.


SQL> select rownum, to_char(systimestamp,'HH24MISS:FF') x1, f(rownum) x2
  2  from   dual
  3  connect by level <= 9;

    ROWNUM X1                             X2
---------- ------------------------------ ------------------------------
         1 181557:351000                  1-181557:351000000
         2 181557:351000                  2-181557:361000000
         3 181557:351000                  3-181557:361000000
         4 181557:351000                  4-181557:364000000
         5 181557:351000                  5-181557:364000000
         6 181557:351000                  6-181557:366000000
         7 181557:351000                  7-181557:366000000
         8 181557:351000                  8-181557:372000000
         9 181557:351000                  9-181557:372000000

9 rows selected.

A direct call to SYSTIMESTAMP is fixed for the duration of the execution of a SQL statement, but this is NOT the case for the SYSTIMESTAMP call made within the PL/SQL function. The PL/SQL function is being called multiple times during the single execution of the SQL statement, and hence each execution is totally entitled to return a “fresh” result from SYSTIMESTAMP.

Moreover, the database makes no guarantees that a PL/SQL function will be called once per row encountered in a SQL statement, so if your PL/SQL function changes session state in some way (for example, a package variable) then you can never assume that there will be a 1-to-1 relationship between rows processed and PL/SQL function executions.

The only way to be sure that you won’t get unexpected results from PL/SQL function calls within SQL is for those functions to be deterministic, and responsibility for that lies entirely with the developer not with the database. So please don’t think that the solution to this is just throwing in the DETERMINISTIC keyword. You need to inspect your code and ensure you won’t get spurious results from that PL/SQL function when used from SQL.

jeremy.schneider's picture

PostgresConf 2019 Summary

https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about how they are using PostgreSQL and what they’ve learned.
  3. Connect businesses who are doing redundant work so they can collaborate instead of each repeating the other’s work.

Last week I published a summary of the first two days of the conference which included the summits, tutorials and training sessions.

I tried to keep a few notes during the final three days of the conference as well so I could share a bit about my experience this year. But before I dive into the day-by-day summary… first, a few short high-level points.

Highlights

Who attends PostgresConf? During my session about Wait Events I asked two questions to scratch the surface a little bit. First, it seemed clear that the vast majority of session attendees had been working with relational databases for more than a decade and a number for more than 25 years. Second, it seemed clear that the vast majority of session attendees had worked seriously with a relational database other than PostreSQL as part of their job. Of course I expected these things would be true for some attendees, but it surprised me just how many – it seemed to me like almost everyone in the room.

https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

What was great about PostgresConf? First and foremost, I give PostgresConf an “A-plus” … highest possible marks … on the two things I personally value the most from a user conference: technical content and user representation. I went to as many sessions as I could and I can personally vouch that there was quality material. And I met a bunch of top notch engineers from a wide variety of industries using PostgreSQL in serious ways.

Besides that, I can point out two other general things I thought PostgresConf did especially well:

  • Running a special track for regulated industries using PostgreSQL. They support this class of users not only in the topic selection, but even in the room setup – for example rules that prohibit recording.
  • Encouraging as many different people as possible to attend and participate. The conference code of conduct was taken seriously with a third party to independently receive, review and investigate any reports. There was an excellent general session and a panel discussion that started conversations – which may not have otherwise happened – around workplace dynamics and common assumptions we make. (If I could hazard summarizing one takeaway: I still do things at work which unintentionally assume that my co-workers are similar to me in subtle ways where they might actually differ!) Finally, on Friday afternoon, a time and space was provided for people to have conversations about career trajectories and the job market. With the rapidly growing demand I see for PostgreSQL skills, we’re going to need everyone who can help! So lets listen to (and become better advocates for) those in our industry who aren’t sure whether they can fully be part of it.

Wednesday March 20

Interesting conversations:

  • Two large, established software vendors whose software ships with embedded PostgreSQL as a default data store (one of these software packages supports a number of customer-managed DB backends but many customers use the default embedded PostgreSQL database).
  • Major University (over 150 years old) actively working to migrate critical backend business systems off commercial databases and onto open source. They have learned that you need to look at the stack as a whole; it proved infeasible to change the backend of applications that are tightly integrated with one specific database when both are sold by a single vendor. Changing the application itself is sometimes necessary.
  • Two FinTech companies: a credit/lending support company and a brokerage both already relying on PostgreSQL in their business.
  • Medium-sized non-profit (100ish people in IT), historically a commercial database shop, exploring open source databases and cloud-based architectures.
  • Two individual DBAs that I didn’t catch their industry. One was following up from Monday’s Hands-On Lab and the other was asking about how to identify applications which might be good starting points for migrating to cloud-based PostgreSQL. We talked about migration success factors like application complexity and database procedural code.

I was able to attend three sessions:

  • The opening general session. It was fun to hear the PostgresConf organizers talk a bit about how the conference is continuing to grow (mark your calendars: they already announced PostgresConf 2020 will be March 23rd through 27th!) and right after that to hear Marc Linster fill in the back-story with the changes and growth that happened over the last ten years. One thing I remember Marc saying was that the questions people ask have changed: five years ago people were trying to understand PostgreSQL and today they are increasingly asking questions about integrating with their larger environment.
  • Dennis Tighe talked about PostgreSQL Security Best Practices from the perspective of Amazon RDS. I was very impressed with this talk for its breadth of security-related topics: external roles/principles/access, data encryption and key management, sharing data, network segmentation and wire encryption, database account/role management, password management, auditing… and all of these topics covered from the perspective of PostgreSQL. I told Dennis several times that talks like this should be available at more user conferences!
  • Denish Patel gave a session on SQL performance tips titled: why isn’t my query using an index? Denish gave an overview of join types and optimizations, introduced the PostgreSQL Genetic Query Optimizer, and then dove into topics including cardinality, partial indexes, histograms and optimizer parameters. I especially enjoyed his samples drawn from the public flight arrival on-time statistics for 2018.


Thursday March 21

Interesting conversations:

  • Small international software vendor in the healthcare industry. Their application supports a couple configurations for the database backend but most of their customers use the default embedded PostgreSQL database. Great conversation about troubleshooting a database-related challenge one of their customers is facing (which they are supporting). Also discussed procedural languages, taking block dumps and viewing raw data from PostgreSQL data files, and the future of javascript in the database with current industry investment in WebAssembly.
  • Individual DBA talking about their current PostgreSQL configuration: 400-500 databases consolidated into a single PostgreSQL instance, typically running around 800-1000 connections but needing to support around 3000ish connections. IIRC each database had its own app server connecting to it. All of this running on a single bare metal server with only 8GB memory! (BTW, while this ratio of memory to connection count is working for them, it wouldn’t work for every workload.)
  • DBAs from my own rather large employer! I mention this because it was a real treat to have DBAs from a few different teams around who could share their own stories and lessons learned as they have been running real production workloads on PostgreSQL and migrating new workloads from commercial databases onto PostgreSQL. I love running into these guys at conferences!

Session I attended:

https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />
  • Baron Schwartz said we were the very first people to find out that he is re-titling his talk: “Everything You Need To Know About PostgreSQL EXPLAIN.” This was a high-quality, accessible talk introducing the topic of execution plans and describing why they are important. After introducing them and peeling back the layers a bit, he then discussed what can go wrong and how to troubleshoot.
  • Grant McAlister gave a session titled HOT – UNDERSTANDING THIS IMPORTANT UPDATE OPTIMIZATION. I did point out to Grant that it’s the only session at PostgresConf which is shouting in all capital letters; he wasn’t quite sure why that happened. But I’m not complaining, because if you like database internals as much as I do then this might have been the best presentation at the conference. I once told someone that I think Grant is a combination of Julian Dyke’s ability to make genius slide animations with Jonathan Lewis’ ability to construct simple tests demonstrating complex database behaviors.
  • My session about Wait Events in PostgreSQL! Around lunch time I went to go hide somewhere and review the content. As I was reviewing, it became evident that I likely had too much content… and I ended up a bit stuck for the next few hours trying to work on that. Well… ask anyone who came to the session… I did not succeed in trimming up my content enough!! But the session seemed successful anyway based on the positive feedback from attendees.

Work:

Like many other attendees I wasn’t quite able to get rid of all my responsibilities for the week. In case you wonder how much hands-on work I do… well I ended up spending a couple hours Thursday evening on a rather fun project I’ve had this year which involved writing several hundred lines of SQL against a very large Posgres-based column-store analytical database. I get to use CTEs & window functions and leverage a deep understanding of the physical layout to write queries that run in minutes instead of hours/days. Important strategies for this project: making a single pass on source data, leveraging push-down filter processing as much as possible, avoiding unnecessary sorts, and minimizing the need to transfer data over the network between cluster nodes during different stages of query execution. (I love this stuff!)

Friday March 22

The conversations I most remember from Friday were around user group and community organizing. Conferences in South Africa and Spain, user groups in Chicago and Denver and Seattle, and a contact in Raleigh.

In addition to the general sessions on Friday, I made sure to attend Jim Nasby’s talk titled: All The Dirt On Vacuum (PG 11 Edition). There’s a version of this talk from 2015 on YouTube but it’s always worth catching Nasby live if you can, because he’s constantly updating and improving the talk. The question that I most often ask about vacuum: “how much longer until this finishes?” The PostgreSQL docs list the Vacuum Phases but they don’t mention that some of these phases might get repeated multiple times. Nasby’s talk brings it all together so that you can understand the whole process and the configuration settings that impact it.

https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=600... 600w, https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=150... 150w" sizes="(max-width: 300px) 100vw, 300px" />

Last but not least… last year I was one of the mentors helping high-school students all around the globe who made contributions to open source PostgreSQL through Google’s Code-In contest. One of the contributions these students made was to create some really cool sticker and pin designs that could be used to promote the open source project!

PostgresConf sponsored ordering a bunch of real stickers and pins with the designs created by these high school students. And we had some fun with this on the exhibition floor: each booth got one or two designs, and attendees who wanted to get all the sticker/pin designs would have to hunt around. In case you’re wondering whether anyone got them all… on Friday, I discovered at least one person who did: Ryan Lambert of RustProof Labs!

Growth Areas

So I’m fully aware (and JD recently reminded me just to make sure) that suggesting growth areas is tantamount to volunteering to implement them… I’m going to take that risk anyway and throw out two ideas for next year if the volunteer bandwidth exists to implement them. </p />
</p></div>
    <div class=»

jeremy.schneider's picture

PostgresConf 2019 Summary

https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about how they are using PostgreSQL and what they’ve learned.
  3. Connect businesses who are doing redundant work so they can collaborate instead of each repeating the other’s work.

Last week I published a summary of the first two days of the conference which included the summits, tutorials and training sessions.

I tried to keep a few notes during the final three days of the conference as well so I could share a bit about my experience this year. But before I dive into the day-by-day summary… first, a few short high-level points.

Highlights

Who attends PostgresConf? During my session about Wait Events I asked two questions to scratch the surface a little bit. First, it seemed clear that the vast majority of session attendees had been working with relational databases for more than a decade and a number for more than 25 years. Second, it seemed clear that the vast majority of session attendees had worked seriously with a relational database other than PostreSQL as part of their job. Of course I expected these things would be true for some attendees, but it surprised me just how many – it seemed to me like almost everyone in the room.

https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

What was great about PostgresConf? First and foremost, I give PostgresConf an “A-plus” … highest possible marks … on the two things I personally value the most from a user conference: technical content and user representation. I went to as many sessions as I could and I can personally vouch that there was quality material. And I met a bunch of top notch engineers from a wide variety of industries using PostgreSQL in serious ways.

Besides that, I can point out two other general things I thought PostgresConf did especially well:

  • Running a special track for regulated industries using PostgreSQL. They support this class of users not only in the topic selection, but even in the room setup – for example rules that prohibit recording.
  • Encouraging as many different people as possible to attend and participate. The conference code of conduct was taken seriously with a third party to independently receive, review and investigate any reports. There was an excellent general session and a panel discussion that started conversations – which may not have otherwise happened – around workplace dynamics and common assumptions we make. (If I could hazard summarizing one takeaway: I still do things at work which unintentionally assume that my co-workers are similar to me in subtle ways where they might actually differ!) Finally, on Friday afternoon, a time and space was provided for people to have conversations about career trajectories and the job market. With the rapidly growing demand I see for PostgreSQL skills, we’re going to need everyone who can help! So lets listen to (and become better advocates for) those in our industry who aren’t sure whether they can fully be part of it.

Wednesday March 20

Interesting conversations:

  • Two large, established software vendors whose software ships with embedded PostgreSQL as a default data store (one of these software packages supports a number of customer-managed DB backends but many customers use the default embedded PostgreSQL database).
  • Major University (over 150 years old) actively working to migrate critical backend business systems off commercial databases and onto open source. They have learned that you need to look at the stack as a whole; it proved infeasible to change the backend of applications that are tightly integrated with one specific database when both are sold by a single vendor. Changing the application itself is sometimes necessary.
  • Two FinTech companies: a credit/lending support company and a brokerage both already relying on PostgreSQL in their business.
  • Medium-sized non-profit (100ish people in IT), historically a commercial database shop, exploring open source databases and cloud-based architectures.
  • Two individual DBAs that I didn’t catch their industry. One was following up from Monday’s Hands-On Lab and the other was asking about how to identify applications which might be good starting points for migrating to cloud-based PostgreSQL. We talked about migration success factors like application complexity and database procedural code.

I was able to attend three sessions:

  • The opening general session. It was fun to hear the PostgresConf organizers talk a bit about how the conference is continuing to grow (mark your calendars: they already announced PostgresConf 2020 will be March 23rd through 27th!) and right after that to hear Marc Linster fill in the back-story with the changes and growth that happened over the last ten years. One thing I remember Marc saying was that the questions people ask have changed: five years ago people were trying to understand PostgreSQL and today they are increasingly asking questions about integrating with their larger environment.
  • Dennis Tighe talked about PostgreSQL Security Best Practices from the perspective of Amazon RDS. I was very impressed with this talk for its breadth of security-related topics: external roles/principles/access, data encryption and key management, sharing data, network segmentation and wire encryption, database account/role management, password management, auditing… and all of these topics covered from the perspective of PostgreSQL. I told Dennis several times that talks like this should be available at more user conferences!
  • Denish Patel gave a session on SQL performance tips titled: why isn’t my query using an index? Denish gave an overview of join types and optimizations, introduced the PostgreSQL Genetic Query Optimizer, and then dove into topics including cardinality, partial indexes, histograms and optimizer parameters. I especially enjoyed his samples drawn from the public flight arrival on-time statistics for 2018.


Thursday March 21

Interesting conversations:

  • Small international software vendor in the healthcare industry. Their application supports a couple configurations for the database backend but most of their customers use the default embedded PostgreSQL database. Great conversation about troubleshooting a database-related challenge one of their customers is facing (which they are supporting). Also discussed procedural languages, taking block dumps and viewing raw data from PostgreSQL data files, and the future of javascript in the database with current industry investment in WebAssembly.
  • Individual DBA talking about their current PostgreSQL configuration: 400-500 databases consolidated into a single PostgreSQL instance, typically running around 800-1000 connections but needing to support around 3000ish connections. IIRC each database had its own app server connecting to it. All of this running on a single bare metal server with only 8GB memory! (BTW, while this ratio of memory to connection count is working for them, it wouldn’t work for every workload.)
  • DBAs from my own rather large employer! I mention this because it was a real treat to have DBAs from a few different teams around who could share their own stories and lessons learned as they have been running real production workloads on PostgreSQL and migrating new workloads from commercial databases onto PostgreSQL. I love running into these guys at conferences!

Session I attended:

https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />
  • Baron Schwartz said we were the very first people to find out that he is re-titling his talk: “Everything You Need To Know About PostgreSQL EXPLAIN.” This was a high-quality, accessible talk introducing the topic of execution plans and describing why they are important. After introducing them and peeling back the layers a bit, he then discussed what can go wrong and how to troubleshoot.
  • Grant McAlister gave a session titled HOT – UNDERSTANDING THIS IMPORTANT UPDATE OPTIMIZATION. I did point out to Grant that it’s the only session at PostgresConf which is shouting in all capital letters; he wasn’t quite sure why that happened. But I’m not complaining, because if you like database internals as much as I do then this might have been the best presentation at the conference. I once told someone that I think Grant is a combination of Julian Dyke’s ability to make genius slide animations with Jonathan Lewis’ ability to construct simple tests demonstrating complex database behaviors.
  • My session about Wait Events in PostgreSQL! Around lunch time I went to go hide somewhere and review the content. As I was reviewing, it became evident that I likely had too much content… and I ended up a bit stuck for the next few hours trying to work on that. Well… ask anyone who came to the session… I did not succeed in trimming up my content enough!! But the session seemed successful anyway based on the positive feedback from attendees.

Work:

Like many other attendees I wasn’t quite able to get rid of all my responsibilities for the week. In case you wonder how much hands-on work I do… well I ended up spending a couple hours Thursday evening on a rather fun project I’ve had this year which involved writing several hundred lines of SQL against a very large Posgres-based column-store analytical database. I get to use CTEs & window functions and leverage a deep understanding of the physical layout to write queries that run in minutes instead of hours/days. Important strategies for this project: making a single pass on source data, leveraging push-down filter processing as much as possible, avoiding unnecessary sorts, and minimizing the need to transfer data over the network between cluster nodes during different stages of query execution. (I love this stuff!)

Friday March 22

The conversations I most remember from Friday were around user group and community organizing. Conferences in South Africa and Spain, user groups in Chicago and Denver and Seattle, and a contact in Raleigh.

In addition to the general sessions on Friday, I made sure to attend Jim Nasby’s talk titled: All The Dirt On Vacuum (PG 11 Edition). There’s a version of this talk from 2015 on YouTube but it’s always worth catching Nasby live if you can, because he’s constantly updating and improving the talk. The question that I most often ask about vacuum: “how much longer until this finishes?” The PostgreSQL docs list the Vacuum Phases but they don’t mention that some of these phases might get repeated multiple times. Nasby’s talk brings it all together so that you can understand the whole process and the configuration settings that impact it.

https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=600... 600w, https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=150... 150w" sizes="(max-width: 300px) 100vw, 300px" />

Last but not least… last year I was one of the mentors helping high-school students all around the globe who made contributions to open source PostgreSQL through Google’s Code-In contest. One of the contributions these students made was to create some really cool sticker and pin designs that could be used to promote the open source project!

PostgresConf sponsored ordering a bunch of real stickers and pins with the designs created by these high school students. And we had some fun with this on the exhibition floor: each booth got one or two designs, and attendees who wanted to get all the sticker/pin designs would have to hunt around. In case you’re wondering whether anyone got them all… on Friday, I discovered at least one person who did: Ryan Lambert of RustProof Labs!

Growth Areas

So I’m fully aware (and JD recently reminded me just to make sure) that suggesting growth areas is tantamount to volunteering to implement them… I’m going to take that risk anyway and throw out two ideas for next year if the volunteer bandwidth exists to implement them. </p />
</p></div>
    <div class=»

jeremy.schneider's picture

PostgresConf 2019 Summary

https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about how they are using PostgreSQL and what they’ve learned.
  3. Connect businesses who are doing redundant work so they can collaborate instead of each repeating the other’s work.

Last week I published a summary of the first two days of the conference which included the summits, tutorials and training sessions.

I tried to keep a few notes during the final three days of the conference as well so I could share a bit about my experience this year. But before I dive into the day-by-day summary… first, a few short high-level points.

Highlights

Who attends PostgresConf? During my session about Wait Events I asked two questions to scratch the surface a little bit. First, it seemed clear that the vast majority of session attendees had been working with relational databases for more than a decade and a number for more than 25 years. Second, it seemed clear that the vast majority of session attendees had worked seriously with a relational database other than PostreSQL as part of their job. Of course I expected these things would be true for some attendees, but it surprised me just how many – it seemed to me like almost everyone in the room.

https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

What was great about PostgresConf? First and foremost, I give PostgresConf an “A-plus” … highest possible marks … on the two things I personally value the most from a user conference: technical content and user representation. I went to as many sessions as I could and I can personally vouch that there was quality material. And I met a bunch of top notch engineers from a wide variety of industries using PostgreSQL in serious ways.

Besides that, I can point out two other general things I thought PostgresConf did especially well:

  • Running a special track for regulated industries using PostgreSQL. They support this class of users not only in the topic selection, but even in the room setup – for example rules that prohibit recording.
  • Encouraging as many different people as possible to attend and participate. The conference code of conduct was taken seriously with a third party to independently receive, review and investigate any reports. There was an excellent general session and a panel discussion that started conversations – which may not have otherwise happened – around workplace dynamics and common assumptions we make. (If I could hazard summarizing one takeaway: I still do things at work which unintentionally assume that my co-workers are similar to me in subtle ways where they might actually differ!) Finally, on Friday afternoon, a time and space was provided for people to have conversations about career trajectories and the job market. With the rapidly growing demand I see for PostgreSQL skills, we’re going to need everyone who can help! So lets listen to (and become better advocates for) those in our industry who aren’t sure whether they can fully be part of it.

Wednesday March 20

Interesting conversations:

  • Two large, established software vendors whose software ships with embedded PostgreSQL as a default data store (one of these software packages supports a number of customer-managed DB backends but many customers use the default embedded PostgreSQL database).
  • Major University (over 150 years old) actively working to migrate critical backend business systems off commercial databases and onto open source. They have learned that you need to look at the stack as a whole; it proved infeasible to change the backend of applications that are tightly integrated with one specific database when both are sold by a single vendor. Changing the application itself is sometimes necessary.
  • Two FinTech companies: a credit/lending support company and a brokerage both already relying on PostgreSQL in their business.
  • Medium-sized non-profit (100ish people in IT), historically a commercial database shop, exploring open source databases and cloud-based architectures.
  • Two individual DBAs that I didn’t catch their industry. One was following up from Monday’s Hands-On Lab and the other was asking about how to identify applications which might be good starting points for migrating to cloud-based PostgreSQL. We talked about migration success factors like application complexity and database procedural code.

I was able to attend three sessions:

  • The opening general session. It was fun to hear the PostgresConf organizers talk a bit about how the conference is continuing to grow (mark your calendars: they already announced PostgresConf 2020 will be March 23rd through 27th!) and right after that to hear Marc Linster fill in the back-story with the changes and growth that happened over the last ten years. One thing I remember Marc saying was that the questions people ask have changed: five years ago people were trying to understand PostgreSQL and today they are increasingly asking questions about integrating with their larger environment.
  • Dennis Tighe talked about PostgreSQL Security Best Practices from the perspective of Amazon RDS. I was very impressed with this talk for its breadth of security-related topics: external roles/principles/access, data encryption and key management, sharing data, network segmentation and wire encryption, database account/role management, password management, auditing… and all of these topics covered from the perspective of PostgreSQL. I told Dennis several times that talks like this should be available at more user conferences!
  • Denish Patel gave a session on SQL performance tips titled: why isn’t my query using an index? Denish gave an overview of join types and optimizations, introduced the PostgreSQL Genetic Query Optimizer, and then dove into topics including cardinality, partial indexes, histograms and optimizer parameters. I especially enjoyed his samples drawn from the public flight arrival on-time statistics for 2018.


Thursday March 21

Interesting conversations:

  • Small international software vendor in the healthcare industry. Their application supports a couple configurations for the database backend but most of their customers use the default embedded PostgreSQL database. Great conversation about troubleshooting a database-related challenge one of their customers is facing (which they are supporting). Also discussed procedural languages, taking block dumps and viewing raw data from PostgreSQL data files, and the future of javascript in the database with current industry investment in WebAssembly.
  • Individual DBA talking about their current PostgreSQL configuration: 400-500 databases consolidated into a single PostgreSQL instance, typically running around 800-1000 connections but needing to support around 3000ish connections. IIRC each database had its own app server connecting to it. All of this running on a single bare metal server with only 8GB memory! (BTW, while this ratio of memory to connection count is working for them, it wouldn’t work for every workload.)
  • DBAs from my own rather large employer! I mention this because it was a real treat to have DBAs from a few different teams around who could share their own stories and lessons learned as they have been running real production workloads on PostgreSQL and migrating new workloads from commercial databases onto PostgreSQL. I love running into these guys at conferences!

Session I attended:

https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />
  • Baron Schwartz said we were the very first people to find out that he is re-titling his talk: “Everything You Need To Know About PostgreSQL EXPLAIN.” This was a high-quality, accessible talk introducing the topic of execution plans and describing why they are important. After introducing them and peeling back the layers a bit, he then discussed what can go wrong and how to troubleshoot.
  • Grant McAlister gave a session titled HOT – UNDERSTANDING THIS IMPORTANT UPDATE OPTIMIZATION. I did point out to Grant that it’s the only session at PostgresConf which is shouting in all capital letters; he wasn’t quite sure why that happened. But I’m not complaining, because if you like database internals as much as I do then this might have been the best presentation at the conference. I once told someone that I think Grant is a combination of Julian Dyke’s ability to make genius slide animations with Jonathan Lewis’ ability to construct simple tests demonstrating complex database behaviors.
  • My session about Wait Events in PostgreSQL! Around lunch time I went to go hide somewhere and review the content. As I was reviewing, it became evident that I likely had too much content… and I ended up a bit stuck for the next few hours trying to work on that. Well… ask anyone who came to the session… I did not succeed in trimming up my content enough!! But the session seemed successful anyway based on the positive feedback from attendees.

Work:

Like many other attendees I wasn’t quite able to get rid of all my responsibilities for the week. In case you wonder how much hands-on work I do… well I ended up spending a couple hours Thursday evening on a rather fun project I’ve had this year which involved writing several hundred lines of SQL against a very large Posgres-based column-store analytical database. I get to use CTEs & window functions and leverage a deep understanding of the physical layout to write queries that run in minutes instead of hours/days. Important strategies for this project: making a single pass on source data, leveraging push-down filter processing as much as possible, avoiding unnecessary sorts, and minimizing the need to transfer data over the network between cluster nodes during different stages of query execution. (I love this stuff!)

Friday March 22

The conversations I most remember from Friday were around user group and community organizing. Conferences in South Africa and Spain, user groups in Chicago and Denver and Seattle, and a contact in Raleigh.

In addition to the general sessions on Friday, I made sure to attend Jim Nasby’s talk titled: All The Dirt On Vacuum (PG 11 Edition). There’s a version of this talk from 2015 on YouTube but it’s always worth catching Nasby live if you can, because he’s constantly updating and improving the talk. The question that I most often ask about vacuum: “how much longer until this finishes?” The PostgreSQL docs list the Vacuum Phases but they don’t mention that some of these phases might get repeated multiple times. Nasby’s talk brings it all together so that you can understand the whole process and the configuration settings that impact it.

https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=600... 600w, https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=150... 150w" sizes="(max-width: 300px) 100vw, 300px" />

Last but not least… last year I was one of the mentors helping high-school students all around the globe who made contributions to open source PostgreSQL through Google’s Code-In contest. One of the contributions these students made was to create some really cool sticker and pin designs that could be used to promote the open source project!

PostgresConf sponsored ordering a bunch of real stickers and pins with the designs created by these high school students. And we had some fun with this on the exhibition floor: each booth got one or two designs, and attendees who wanted to get all the sticker/pin designs would have to hunt around. In case you’re wondering whether anyone got them all… on Friday, I discovered at least one person who did: Ryan Lambert of RustProof Labs!

Growth Areas

So I’m fully aware (and JD recently reminded me just to make sure) that suggesting growth areas is tantamount to volunteering to implement them… I’m going to take that risk anyway and throw out two ideas for next year if the volunteer bandwidth exists to implement them. </p />
</p></div>
    <div class=»

connor_mc_d's picture

Long running scheduler jobs

One of the nice things about the job scheduler in the Oracle database is the easily interpreted interval settings you can apply for job frequency. The days of cryptic strings like “sysdate+0.000694444” when all you really wanted to say was “Just run this job every minute” are a thing of the past. I covered how to get the database to convert interval strings into real execution dates here 

But it raises the question: What if I have a job that is scheduled to run every minute, but it takes more than 1 minute to run? Will the scheduler just crank out more and more concurrent executions of that job? Will I swamp my system with ever more background jobs? So I thought I’d find out with a simple test.

I created a table which will record the start and end time for executions of a procedure, and then crafted that procedure to always run for at least 2 minutes using dbms_lock.sleep. (If you are on 18c, you can replace this with dbms_session.sleep to avoid the need for an explicit grant.) Then I set this procedure to be run every minute via dbms_scheduler.


SQL> create table t ( tag varchar2(10), d date);

Table created.

SQL>
SQL> create or replace
  2  procedure P is
  3  begin
  4    insert into t values ('start',sysdate);
  5    commit;
  6    dbms_lock.sleep(120);
  7    insert into t values ('end',sysdate);
  8    commit;
  9  end;
 10  /

Procedure created.


SQL> begin
  2    dbms_scheduler.create_job (
  3      job_name        => 'JOB1',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'begin p; end;',
  6      start_date      => systimestamp,
  7      repeat_interval => 'freq=minutely;bysecond=0;',
  8      enabled         => true);
  9  end;
 10  /

PL/SQL procedure successfully completed.

I waited 20 minutes and then looked at both my table and the scheduler logs to see how many concurrent executions were recorded.


SQL> select * from t order by d, tag;

TAG        D
---------- -------------------
start      25/03/2019 21:36:00
end        25/03/2019 21:38:00
start      25/03/2019 21:38:00
end        25/03/2019 21:40:00
start      25/03/2019 21:40:00
end        25/03/2019 21:42:00
start      25/03/2019 21:42:01
end        25/03/2019 21:44:01
start      25/03/2019 21:44:01
end        25/03/2019 21:46:01
start      25/03/2019 21:46:01
end        25/03/2019 21:48:01
start      25/03/2019 21:48:01
end        25/03/2019 21:50:01
start      25/03/2019 21:50:01
end        25/03/2019 21:52:01
start      25/03/2019 21:52:01
end        25/03/2019 21:54:01

18 rows selected.

SQL> select log_date
  2  from   dba_scheduler_job_log
  3  where job_name = 'JOB1'
  4  order by log_date;

LOG_DATE
-----------------------------------------------
25-MAR-19 09.38.00.866000 PM +08:00
25-MAR-19 09.40.00.920000 PM +08:00
25-MAR-19 09.42.00.998000 PM +08:00
25-MAR-19 09.44.01.037000 PM +08:00
25-MAR-19 09.46.01.078000 PM +08:00
25-MAR-19 09.48.01.143000 PM +08:00
25-MAR-19 09.50.01.171000 PM +08:00
25-MAR-19 09.52.01.206000 PM +08:00
25-MAR-19 09.54.01.272000 PM +08:00

9 rows selected.

As you can see, the scheduler is not going to swamp your system. It will not run the “next” occurrence of your submitted job until the current execution has completed. So even though we requested an execution each minute, we are bound by the run time duration of the job itself. Once additional nice thing is that once the job has finished, the scheduler immediately sees that the next execution is overdue and launches the next job straight away. So no need to worry about an excessive number of jobs all running together.

Just as an aside, when you have a scheduler job that is “always” in a running state, then you need to take care when dropping the job because by default you cannot drop a running job. In such instances, you can always add the FORCE parameter to kill the current execution and remove the job from the scheduler.


SQL> exec dbms_scheduler.drop_job('JOB1')
BEGIN dbms_scheduler.drop_job('JOB1'); END;

*
ERROR at line 1:
ORA-27478: job "MCDONAC"."JOB1" is running
ORA-06512: at "SYS.DBMS_ISCHED", line 274
ORA-06512: at "SYS.DBMS_SCHEDULER", line 753
ORA-06512: at line 1


SQL> exec dbms_scheduler.drop_job('JOB1',force=>true)

PL/SQL procedure successfully completed.

 

Jonathan Lewis's picture

Stats advisor

This is just a little shout-out about the Stats Advisor – if you decide to give it a go, what sort of things is it likely to tell you. The answer is in a dynamic performance view called v$stats_advisor_rules – which I’ve list below from an instance running 18.3.0.0.


SQL> set linesize 180
SQL> set trimspool on
SQL> set pagesize 40
SQL> column description format a75
SQL> column name format a32
SQL> break on rule_type duplicate skip 1
SQL> select * from v$stats_advisor_rules;

  RULE_ID NAME                             RULE_TYPE DESCRIPTION                                                                     CON_ID
---------- -------------------------------- --------- --------------------------------------------------------------------------- ----------
         0                                  SYSTEM                                                                                         0
         1 UseAutoJob                       SYSTEM    Use Auto Job for Statistics Collection                                               0
         2 CompleteAutoJob                  SYSTEM    Auto Statistics Gather Job should complete successfully                              0
         3 MaintainStatsHistory             SYSTEM    Maintain Statistics History                                                          0
         4 UseConcurrent                    SYSTEM    Use Concurrent preference for Statistics Collection                                  0
         5 UseDefaultPreference             SYSTEM    Use Default Preference for Stats Collection                                          0
         6 TurnOnSQLPlanDirective           SYSTEM    SQL Plan Directives should not be disabled                                           0

         7 AvoidSetProcedures               OPERATION Avoid Set Statistics Procedures                                                      0
         8 UseDefaultParams                 OPERATION Use Default Parameters in Statistics Collection Procedures                           0
         9 UseGatherSchemaStats             OPERATION Use gather_schema_stats procedure                                                    0
        10 AvoidInefficientStatsOprSeq      OPERATION Avoid inefficient statistics operation sequences                                     0

        11 AvoidUnnecessaryStatsCollection  OBJECT    Avoid unnecessary statistics collection                                              0
        12 AvoidStaleStats                  OBJECT    Avoid objects with stale or no statistics                                            0
        13 GatherStatsAfterBulkDML          OBJECT    Do not gather statistics right before bulk DML                                       0
        14 LockVolatileTable                OBJECT    Statistics for objects with volatile data should be locked                           0
        15 UnlockNonVolatileTable           OBJECT    Statistics for objects with non-volatile should not be locked                        0
        16 MaintainStatsConsistency         OBJECT    Statistics of dependent objects should be consistent                                 0
        17 AvoidDropRecreate                OBJECT    Avoid drop and recreate object seqauences                                            0
        18 UseIncremental                   OBJECT    Statistics should be maintained incrementally when it is beneficial                  0
        19 NotUseIncremental                OBJECT    Statistics should not be maintained incrementally when it is not beneficial          0
        20 AvoidOutOfRange                  OBJECT    Avoid Out of Range Histogram endpoints                                               0
        21 UseAutoDegree                    OBJECT    Use Auto Degree for statistics collection                                            0
        22 UseDefaultObjectPreference       OBJECT    Use Default Object Preference for statistics collection                              0
        23 AvoidAnalyzeTable                OBJECT    Avoid using analyze table commands for statistics collection                         0

24 rows selected.

As you can see the rules fall into three groups: system, operation, and object – and you can’t help noticing at all three levels how commonly the theme is: “just stick with the defaults!”.

As so often happens when I start writing a catch-up or “remind myself” not I found that Tim Hall has already written all about it.

Richard Foote's picture

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People)

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]

To prevent automated spam submissions leave this field empty.