Tips and Traps

PostgreSQL Tuning – pg_stat_statements

by

Although I’ve dabbled in postgres since 2002, I’ve only been working with it heavily since 2019. My favorite challenge was tuning a 37 TB AWS RDS instance that ran a flagship feature for a previous employer. It was a very busy system, much like an IOT system enduring millions of reads/writes and billions of updates and deletes every day. It was so busy I was only able to investigate and tune from 9pm to 2am. At any other time, even the simplest queries wouldn’t even return.

When you embark on a big tuning effort like the one just described, and one is tasked with figuring out where the sluggishness is coming from, it is invaluable to peer into all the unique SQL statements the system has had to process over time, and where inefficiencies are lurking. The pg_stat_statements extension is crucial to such an effort. First added in version 8.4, performance monitoring continues to improve with every release.

It is a good idea to learn how to use it to your advantage.

Getting Started

Assuming the user/role you are using to connect has the appropriate level of privileges, query your statement monitoring cache:

> select * from pg_stat_statements;

If this errors out, then either your don’t have the right privileges, or it still needs to be installed by your DBA (which might be you). First check that it is included in the preloaded libraries. Open your postgres.conf file and look for this line:

shared_preload_libraries = ‘pg_stat_statements’

There might be other libraries in that listed of pre-loads. If you have to add pg_stat_statements to that parameter, restart Postgres. Once that is done, then enable the feature:

> CREATE EXTENSION pg_stat_statements;

This will create the pg_stat_statements view (and also pg_stat_statements_info on v 14.X). A number of columns were renamed in version 13. The examples below were written for version 14. If the statement does not run on your system, your version is 12 or below; just remove the word “exec” from total_exec_time, min_exec_time, max_exec_time, mean_exec_time and stddev_exec_time. I won’t spend any time explaining each column. Most are self-explanatory. You can read the official Postgres doc linked above to get a better understanding of each column.

There are a handful of postgres.conf parameters that pertain to statement tracking. I like to reveal their values using this:

select name, setting, unit, min_val, max_val, source, vartype, enumvals, boot_val
  from pg_settings
 where name like 'pg_stat%' or name in ('track_io_timing','track_activity_query_size');
pg_stat_statements gathering parameters

The default settings are typically good enough. However, if your queries are quite large, you might want to set track_activity_query_size to 2048 or 4096. Multi-page queries might require even more bytes to accommodate the whole thing. Also, if you find that blk_read_time and blk_write_time columns are blank for your statements, this means that the track_io_timing parameter is off. You can turn it on for the entire server in the postgres.conf file, or just for the current database, like this:

> ALTER DATABASE mydb SET track_io_timing = on;

It does consume some resources to track I/O. You may wish to turn it back off once your heaviest physical disk-touching loads have been identified and corrected.

I’d like to introduce you to a handful of queries, that I picked up here and there over years of tuning, which I find handy for quickly identifying sore spots that need attention.

Top SQL Consumers: Time

Below you will see my favorite query for working with tracked statements. It is multi-purpose. You can add WHERE predicates if you wish, or partial query strings to filter the results to just a query or two you are looking for, or switch out the column(s) being ordered by to change the focus of the query. But this one is the one I use the most, bubbling to the top the biggest consumers of time (and therefore resources) on the system.

with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(min_exec_time::numeric, 2) as min_call_ms
        , round(max_exec_time::numeric, 2) as max_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select *
  from stmts
 where 1=1
   --and query like '%:partial_query_string%'
order by total_hours desc, calls desc;

Be sure to replace the name of your database of interest in line 16 if yours is using a name other than the default of “postgres”.

If I already know the statement I’m looking for, or a table name or keyword found in that statement, I can comment in the filter query like '%:partial_query_string%' and re-run to find only those statements matching my string. You can get fancier with regular expressions too.

You may find yourself working on a system where each of the thousands of unique statements only differs by a few characters. In order to reduce the noise, you could truncate the query down to 40 or 50 of the first characters and group the measurements by that. This aggregation may show the primary culprit, occuping 40, 50 or 70% of the total time for all statements. From there you can jump back to using the query above to zero in on the individual statements (using the partial_query_string version to find all statements sharing the same signature as found in the grouped version below), which are causing the majority of the load on the database.

-- bucket statements by simple truncation
with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select substring(query,1,50) as query_first_50chars, sum(total_hours) as hours, sum(pct_of_total_hours) as pct
     , sum(calls) as calls, sum(rows) as rows, round(avg(mean_call_ms),2) as mean_call_ms
     , sum(temp_blks_read) as temp_blks_read, sum(temp_blks_written) temp_blks_written
  from stmts
 group by query_first_50chars
order by hours desc, calls desc;

There are times in your tuning efforts, where one query was so horrendous it was consuming most of the resources, or took so much time to complete, they skew the results. This will usually stand out like a beacon, seen by a gigantic difference between min and max execution time, and a large standard deviation. In the example below, ordered by total time, the queries behind rows 3 and 4 have really huge stddev. Their minimum execution time is .02ms, and average is 19s and 8s respectively, but look at the maximum! 105 hours and 51 hours to execute!

Example output from trimmed version of pg_stat_statements query

If those queries took days to run every time, that would be the first thing you’d fix. But they don’t. Here’s where you dig a little deeper and exercise judgement. Pursuing queries with large max time or stddev could be a waste of your time. There might have been one anomalous incident where resources were locked, preventing the statements from completing. Steps should definitely be taken to log and alert when queries take this long in the future (subject of future blog post), instead of hanging indefinitely. But if this only happened once or twice, this is a distraction. In fact queries 3 and 4 aren’t executed much at all compared to queries 1 and 2, and might have even stopped being used. Perhaps they were only used during initial migration. The mean tells us queries 3 and 4 are not too bad. Indeed these queries that I can’t show to protect my client, are part of a backend data pipeline system that does large load and deletion operations, so 8s to 19s is OK. It is for reasons like these, that I’ll glance over stddev, but I get more value by focusing on total time taken.

I don’t have an example to show at the moment, but imagine that query 1 in the screenshot above took a mean time of 1 second to return. That seems pretty good if this were a query supporting a UI screen. But it is not. It was called over six million times in the week-long period where these stats were gathered. A one second response time for a query called millions of times could break the business or bring the system to its knees. Luckily the top two statements both take less than 100ms and aren’t a concern (the second query is a stored procedure, and postgres doesn’t appear to log rows affected by stored routines, otherwise the rows column can be informative).

Top SQL Consumers: Temp

One indication of a wasteful query, or a work_mem setting that is too small, is a large use of temp space because there wasn’t enough work_mem in the session to handle the sort operations. Use this to identify those types of statements:

with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(min_exec_time::numeric, 2) as min_call_ms
        , round(max_exec_time::numeric, 2) as max_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , round(stddev_exec_time::numeric, 2) as stddev_call_ms
        --, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select *
  from stmts
order by (temp_blks_read + temp_blks_written) desc, calls desc;

Having identified some big uses of temp space, now you need to know the business context behind these queries. Many top users of temp space will be large INSERT, CREATE INDEX, DELETE and other bulk-type operations that don’t happen very often. These require lots of in-memory work that spill over to temp and probably don’t need tuning. If you find a big, bloated query though, that is taking 34 minutes to run every execution and is inefficiently spilling over into disk access and temp space (and nobody knew because it was running at night and not affecting end users), then it probably requires attention and tuning.

Top SQL Consumers: I/O

In some circumstances, you may be more concerned about queries that have to spend inordinate effort reading from or writing to disk. To highlight these operations, just take the same query above, but order by physical_io_per_call_ms DESC.

In Closing…

It isn’t enough just to query pg_stat_statements and look for big time hogs. That’s a great first step and may very well net you some “low-hanging fruit” and quick wins. But you could also spin your wheels and tune the wrong thing.

Knowing the business, knowing the app or service context where the query is used, asking the right questions, figuring out what the query should be doing, how much data should be processed at each step in the query plan, and the order in which the data should be joining and sorting, this is all part of being a good performance tuner. “Know thy data” is the first principle I teach in my classes on data design and performance tuning. This helps you quickly zero in on true waste of resources, ignoring the fluff and noise, and is how you earn your living as a rock star data engineer.

Now that I’m writing again, I’d like to encourage you to subscribe to the blog for future posts. Feel free to contact me if you have questions or a desire to engage, or leave a comment if you have a clarifications or find a technical error.

Tips

  • Create a weekly job, perhaps orchestrated by cron, which saves a snapshot of everything in pg_stat_statements to a local table. Perhaps create a new schema in the database named monitor to hold this table and anything else you create to watch and learn about the database’s behavior over time.
  • If you intend to use the pg_stat_statements_reset() function to reset statement stats for the database, a user, or a query, save a copy of the pg_stat_statements data for that query, user or entire database BEFORE you make your change and reset and re-test to see if performance is better.
  • pg_stat_statements is only useful for successful, completed DML and DDL. To see queries currently in flight, typically useful when a query isn’t returning and you’d like to know what it is doing, query pg_stat_activity instead (subject of future blog post)

CREATE VIEW AS SELECT * does not dynamically adjust to the underlying object

by

It never ceases to amaze me how I can be humbled every day.

When you work with a technology for so long, teaching, writing, presenting, training and mentoring…you start to think you’re an expert.
You think by now you know all the basics, advanced aspects and nuances.
And yet, despite your experience, there it is: an aspect of Oracle that has been there all along, but somehow I missed it! 25 years doing this for a living, and I missed it!

That or I’m starting to go senile.

In every Oracle development environment in which I’ve worked, either I or an architect that preceded me has included in the SQL standard the common rule to always use explicit column lists when building SELECT or INSERT statements. The simplified version of the rule’s justification is that things tend to change and when they do, the generic SELECT * and INSERT with no column list will break your app.

With my current employer’s most recent project, for the first time I consciously chose to break that rule.

I thought my reason was sound, e.g. the remote table on another Oracle database was being altered frequently and I needed our local view of that table to adjust dynamically, rather than have to check in yet another script into our database build every time they added another column. Ask most DBAs what a view actually is, and they tend to agree with the Oracle documentation that says views are stored queries. They don’t contain the data. They contain the query definition to get at the data at runtime when the view is queried.

I figured that if my column list was the taboo “SELECT * FROM {remote table over db link}”, it would automatically see and include the new column(s) as it parsed and executed the SELECT * query.

As I understood it, upon feeding Oracle a view creation statement, it first strips out any SQL comments found at the top or bottom of the query, ensures the syntax is correct, and that it has the privileges it needed to function. After passing validations, it would then take the view’s defining query and store it as-is in the data dictionary. Then when a user queries the view, Oracle retrieves the view SQL out of the data dictionary, merges it with any predicates (WHERE clause criteria) in the user’s query, then handles it from there like any other SQL statement (optimizing, parsing, checking the SQL cache, executing, etc.)

The red text above is incorrect. My problem is found in my misunderstanding of how the view’s defining query is stored. If the defining query follows the common rule of using an explicit column list, it will store that SELECT statement as-is, preserving aliases, character case, comments, etc.  But if the defining query uses SELECT *, Oracle probes the data dictionary of the underlying objects to get every current column name. It then re-writes your “dynamic” SELECT statement to be static, replacing the “*” with a list of upper-cased, double-quoted column names as they stood at the time of view creation.

With a table defined like this:

DROP VIEW dyn_view;
DROP TABLE test_tab PURGE;

CREATE TABLE test_tab (
 ID  INTEGER NOT NULL
,NAME VARCHAR2(100)
,DESCRIPTION VARCHAR2(500)
)
/

INSERT INTO test_tab VALUES (1, 'First Row', 'I am the first row');
COMMIT;

And a “dynamic” adjustable view foolishly created like this:

CREATE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

As you can see above. The data dictionary now contains the current list of columns in test_tab, not the dynamic SELECT * as hoped for.

We can query the view just to see it in action before the table change:

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

Now let’s alter the table, as if we were the merciless department constantly changing the remote table we depend on:

ALTER TABLE test_tab
  ADD load_date DATE DEFAULT SYSDATE NOT NULL;

Did the content of the view happen to change because we changed the underlying object?

  
SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

Nope, sadly as seen above, it is the same definition as it was before. So it is not possible to have a stored query that says “give me whatever you’ve got at the present time.” Instead, Oracle is going to force us to always store a static column list at the time of view creation.

Does the new column exist on the base table?

SQL> SELECT * FROM test_tab;
ID NAME      DESCRIPTION        LOAD_DATE
-- --------- ------------------ -----------
 1 First Row I am the first row 2017Aug17 16:09:39

Just to be doubly-certain, has our view adjusted to include it?

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

No, it has not. Well, that’s that.

So it turns out, the view has to be-recreated with an altered explicit column list to pick up the new column, or the original SELECT * creation script must be re-run:

CREATE OR REPLACE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION","LOAD_DATE" FROM test_tab

And there it is. Now the view finally sees the new column.

Note: While researching the Oracle docs for this topic, I finally found that Oracle does spell this out clearly in the Database Administrator’s Guide, in the section on Managing Views, Sequences and Synonyms, under the heading Expansion of Defining Queries at View Creation Time. It says “When a view is created, Oracle Database expands any wildcard (*) in a top-level view query into a column list.” Whether it said this in the Oracle 7 manuals in 1995 when I first learned about views, is probably not worth pursuing.

It wasn’t until our application broke that I discovered my clever plan to have the view adjust dynamically was fundamentally flawed and would never work. It made me feel like it was my first week on the job with Oracle all over again. Because I had always followed the best practice of explicit column lists, I’d never run into this gap in my knowledge. Quite disappointing, but I learned something new, so it was still a good day.

BULK COLLECT and COUNT() do not raise NO_DATA_FOUND

by

I thought I knew bulk operations well.
Today while coding up a quick hunk of PL/SQL, the code did not behave as expected.
Basically I’d had a senior moment and forgotten something that I think my novice DBA and developer readers might benefit from.

When you try to select some rows from a data structure using a filter, for which none of the rows in the structure are a match, in a SQL client tool, you’ll simply be told that you received 0 rows. But if you are doing the same select statement in PL/SQL to get the output INTO some waiting variable(s), Oracle will raise the NO_DATA_FOUND exception.

DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

SET SERVEROUTPUT ON
DECLARE
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT email
     INTO l_email
     FROM my_emp
    WHERE last_name = l_last_nm;
   dbms_output.put_line('Email Handle: ' || l_email);
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;
/

Which produces this output if we give it a non-existent employee last name of “Bogus”

SQL>
Table dropped
Table created
Could not find employee given last name: Bogus
PL/SQL procedure successfully completed

This use of SELECT into a PL/SQL block is known as an explicit cursor and you must code for the NO_DATA_FOUND exception if you use it.
This is a good thing.

Unfortunately, once a new developer gets used to this NO_DATA_FOUND exception, they tend to get carried away with it and use it for basic UPDATE and DELETE DML, which is useless. Instead use cursor attributes SQL%FOUND or SQL%ROWCOUNT to determine if the UPDATE or DELETE worked.

I’ve also inherited lots of code in the past that had little functions and SQL that performed “existence checks” to ensure the data is present before attempted to do DML on the keyed data. This approach is entirely unnecessary and produces code that will never be exercised.

SET SERVEROUTPUT ON
DECLARE
   l_count   INTEGER := 0;
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT COUNT(*)
     INTO l_count
     FROM my_emp
    WHERE last_name = l_last_nm;

   IF (l_count > 0) THEN
      SELECT email
        INTO l_email
        FROM my_emp
       WHERE last_name = l_last_nm;
      dbms_output.put_line('Email Handle: ' || l_email);
   END IF;

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;

Lines 21 to 23 will never be hit. Why? Because COUNT() does not raise the NO_DATA_FOUND exception. It returns 0 if no rows match the filter.

Well, my senior moment today was forgetting that BULK COLLECT behaves in a manner similar to COUNT().
The following anonymous block retrieves all attributes for employees who match the given last name.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   BEGIN
      SELECT *
        BULK COLLECT
        INTO lr_emp_tab
        FROM my_emp
       WHERE last_name = l_last_nm;

      IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
         dbms_output.put_line('Found employees:');
         FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
            dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN no_data_found THEN
         dbms_output.put_line('Could not find any employees given last name: ' || l_last_nm);
   END;
END;
/

If we run it for “De Haan”, “King” and “Bogus”, we get the following output.

SQL>
Found employees:
Lex De Haan
PL/SQL procedure successfully completed

SQL>
Found employees:
Steven King
Janette King
PL/SQL procedure successfully completed

SQL>
PL/SQL procedure successfully completed

That last result surprised me. If there were no rows to put into the waiting collection, just like the simple explicit cursor INTO above, I was expecting it to raise the NO_DATA_FOUND exception and spit out the “Could not find…” sentence to the screen. What I had forgotten — and am sharing so you don’t have to learn the hard way — is that if the explicit cursor is using BULK COLLECT and no rows are found, it gives you an initialized empty collection, which we verify with the code listing below.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   SELECT *
    BULK COLLECT
    INTO lr_emp_tab
    FROM my_emp
   WHERE last_name = l_last_nm;

   IF (lr_emp_tab IS NULL) THEN
      dbms_output.put_line('The receiving collection is atomically NULL');
   ELSE
      IF (lr_emp_tab.COUNT = 0) THEN
         dbms_output.put_line('The receiving collection is empty');
      END IF;
   END IF;

   IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
      dbms_output.put_line('Found employees:');
      FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
         dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
      END LOOP;
   END IF;
END;
/

If we run it and provide the last name of “Bogus”, it confirms that what BULK COLLECT does is initialize the collection and skip putting anything in it.

SQL>
Table dropped
Table created
The receiving collection is empty
PL/SQL procedure successfully completed

Knowing this, I was able to remove the exception section, and as a side effect made the code more simple and correct.

Live well!

Dropping a View Drops the Instead-of Triggers

by

On top of using this blog to speak on data and database design and development, I’ll also be using it as a notebook, documenting the moments of discovery and clarity that occur when dealing with problems. I wish I’d been taking notes this way since I began my career in 1995, instead of just hoping I’d remember the next time I ran into the same problem.

Today I had to learn the hard way (or re-learn; did I learn this before?) that when you replace a view that happens to have an instead-of trigger attached to it, the CREATE OR REPLACE will drop the instead-of trigger silently. CREATE OR REPLACE lulled me into a false sense of security. It seems to imply that the object and its dependencies will not be dropped, but left alone as the source code for the view is replaced in the data dictionary.

Not so.

This “discovery” was found due to things that broke in production. My tested script replaced the view, but silently and viciously removed the important instead-of trigger that belonged to the view.

Lets have a look to be sure I’m not hallucinating:

DROP VIEW my_emp_vw;
DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 90;

CREATE OR REPLACE TRIGGER my_emp_vw_iot
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON my_emp_vw 
  FOR EACH ROW
DECLARE
BEGIN
  IF (inserting) THEN
     INSERT INTO my_emp
        (employee_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date
        ,job_id
        ,salary
        ,commission_pct
        ,manager_id
        ,department_id)
     VALUES
        (:new.employee_id
        ,:new.first_name
        ,:new.last_name
        ,:new.email
        ,:new.phone_number
        ,:new.hire_date
        ,:new.job_id
        ,:new.salary
        ,:new.commission_pct
        ,:new.manager_id
        ,:new.department_id);
     
  ELSIF (updating) THEN
     UPDATE my_emp
        SET first_name = :new.first_name,
            last_name = :new.last_name,
            email = :new.email,
            phone_number = :new.phone_number,
            hire_date = :new.hire_date,
            job_id = :new.job_id,
            salary = :new.salary,
            commission_pct = :new.commission_pct,
            manager_id = :new.manager_id,
            department_id = :new.department_id
      WHERE employee_id = :new.employee_id;
  
  ELSIF (deleting) THEN
     DELETE FROM my_emp
       WHERE employee_id = :old.employee_id;
  END IF;
END my_emp_vw_iot;
/

Now that the objects have been created, let’s look at the state and existence of our objects in the data dictionary.

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW_IOT   TRIGGER
MY_EMP_VW       VIEW
MY_EMP          TABLE

Now lets edit the view in place (in actuality it is dropping the view and re-creating it):

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 50;

And check whether the trigger was maintained after the CREATE OR REPLACE

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW       VIEW
MY_EMP          TABLE

Sure enough. The dependent trigger MY_EMP_VW_IOT was not maintained. It was silently dropped during the REPLACE part of the CREATE OR REPLACE VIEW statement. So be careful. Do better than I. Remember this every time you script a view replacement. The instead-of trigger will have to be re-created.

Live well.