Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Five Things you might not know or may have forgotten about the Oracle Database

Five Things you might not know or may have forgotten about the Oracle Database

In this session, we'll look at five things you might not have known about the Oracle Database - or that you might have known about but never realized how they could benefit you. For each topic, we will explain the functionality and demonstrate the benefits using real-world examples. The topics covered apply to anyone running Oracle Database 11g and up, including Standard Edition, with only a few minor exceptions.

Maria Colgan

March 09, 2023
Tweet

More Decks by Maria Colgan

Other Decks in Technology

Transcript

  1. Five Things You Might Not Know About The
    Oracle Database
    Maria Colgan
    Distinguished Product Manager
    Oracle Database
    Or may
    have forgotten

    View full-size slide

  2. Five things you may not know or have forgotten about the Oracle Database
    3.
    Parameterized
    Views
    2.
    Online
    Operations
    5.
    Power of
    Immutability
    4.
    PL/SQL
    Packages
    1.
    Benefits of
    Invisibility
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  3. Benefits of Invisibility
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  4. Managing Indexes
    One of the most common approaches for query tuning on OLTP systems is to add an index
    Copyright © 2023, Oracle and/or its affiliates
    The more complex the app, the more indexes you are likely to have
    But over time, how valuable are those indexes?
    After all, each additional index increases the overhead of each DML operation
    However, we are all relucent to drop an index just in case it causes a performance regression!

    View full-size slide

  5. Determine which indexes are useful via DBA_INDEX_USAGE view
    SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt,
    u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10,
    u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K,
    u.bucket_1000_plus_access_count B1K, u.last_used
    FROM dba_index_usage u, dba_indexes i
    WHERE i.owner='MARIA'
    AND i.index_name = u.name (+)
    ORDER BY u.total_access_count;
    Copyright © 2023, Oracle and/or its affiliates
    Use an outer join with DBA_INDEXES to capture
    indexes that are never used
    Note: Index usage info is flushed from memory to disk every 15 minutes
    Query LAST_FLUSH_TIME in V$INDEX_USAGE_INFO to determine the last time it was updated

    View full-size slide

  6. Determine which indexes are useful via DBA_INDEX_USAGE view
    SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt,
    u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10,
    u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K,
    u.bucket_1000_plus_access_count B1K, u.last_used
    FROM dba_index_usage u, dba_indexes i
    WHERE i.owner='MARIA'
    AND i.index_name = u.name (+)
    ORDER BY u.total_access_count;
    INDEX_NAME TOT_ACCESS EXEC_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED
    ---------------- ---------- ---------- ------- ------- ------- -------- ---------- ---------- ---------
    PROD_CUST_SALES 1 1 0 0 0 0 0 1 06-JAN-23
    INDX_LOC 2 2 0 1 1 0 0 0 12-JAN-23
    INDX_DEPTNO 19 19 0 18 1 0 0 0 26-JAN-23
    PROD_SUP_INDX 27 27 25 0 0 0 2 0 26-JAN-23
    EMPNO_PK_IND 82 82 48 32 0 2 0 0 26-JAN-23
    CHAN_SOLD
    PROD_SUB_IDX
    Copyright © 2023, Oracle and/or its affiliates
    The histogram indicates how useful an
    index is by showing how many accesses
    fall in each bucket of rows returned

    View full-size slide

  7. Determine which indexes are useful via DBA_INDEX_USAGE view
    SELECT i.index_name, u.total_access_count tot_access, u.total_exec_count exec_cnt,
    u.bucket_0_access_count B0, u.bucket_1_access_count B1, u.bucket_2_10_access_count B2_10,
    u.bucket_11_100_access_count B11_100, u.bucket_101_1000_access_count B101_1K,
    u.bucket_1000_plus_access_count B1K, u.last_used
    FROM dba_index_usage u, dba_indexes i
    WHERE i.owner='MARIA'
    AND i.index_name = u.name (+)
    ORDER BY u.total_access_count;
    INDEX_NAME TOT_ACCESS EXEC_CNT B0 B1 B2_10 B11_100 B101_1K B1K LAST_USED
    ---------------- ---------- ---------- ------- ------- ------- -------- ---------- ---------- ---------
    PROD_CUST_SALES 1 1 0 0 0 0 0 1 06-JUL-22
    INDX_LOC 2 2 0 1 1 0 0 0 06-JUL-22
    INDX_DEPTNO 19 19 0 18 1 0 0 0 06-JUL-22
    PROD_SUP_INDX 27 27 25 0 0 0 2 0 06-JUL-22
    EMPNO_PK_IND 82 82 48 32 0 2 0 0 06-JUL-22
    CHAN_SOLD
    PROD_SUB_IDX
    Copyright © 2023, Oracle and/or its affiliates
    These two indexes have never been used and are candidates to be dropped

    View full-size slide

  8. Leverage Invisible Indexes before dropping indexes for good
    -- Mark indexes you are looking to remove as invisible, if no one complains after some time; delete them
    ALTER INDEX prod_sub_idx INVISIBLE;
    -- New indexes can be marked invisible until you have an opportunity to prove they improve performance
    CREATE INDEX my_idx ON t(x, object_id) INVISIBLE;
    -- Test newly created invisible indexes by setting OPTIMIZER_USE_INVISBLE_INDEXES to TRUE
    ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  9. Managing shared schema objects across apps
    One of the difficulties with the relational model today is:
    • Different applications may need different columns from the same table
    Copyright © 2023, Oracle and/or its affiliates
    • However, all apps are impacted by any DDL change on the table
    • Dropping a column will break queries still referring to it
    • Adding a column breaks “SELECT *” queries
    • Dropping a column will delete the data from all the rows, even those that are already archived
    CUSTOMERS
    FIRST_NAME
    LAST_NAME
    INITIALS
    :
    APP A
    APP B

    View full-size slide

  10. Sharing schema objects across apps
    -- Code for App A
    SELECT *
    FROM customers;
    FIRST_NAME LAST_NAME IN ADDRESS …
    ---------- ---------- -- --------
    Dan Jones DJ New York
    Joseph Smith JS Los Angeles
    -- Code for App B
    SELECT first_name, last_name, initials
    FROM customers;
    FIRST_NAME LAST_NAME IN
    ---------- ---------- --
    Dan Jones DJ
    Joseph Smith JS
    Copyright © 2023, Oracle and/or its affiliates
    CUSTOMERS
    FIRST_NAME
    LAST_NAME
    INITIALS
    :
    -- App A and B share
    a single Customers table

    View full-size slide

  11. Sharing schema objects across apps
    Copyright © 2023, Oracle and/or its affiliates
    -- Code for App A
    SELECT *
    FROM customers;
    FIRST_NAME LAST_NAME ADDRESS …
    ---------- ---------- --------
    Dan Jones New York
    Joseph Smith Los Angeles
    -- Code for App B
    SELECT first_name, last_name, initials
    FROM customers;
    *
    ERROR at line 1:
    ORA-00904: "INITIALS": invalid identifier
    -- App A no longer needs the initials
    columns and decides to drop the column
    ALTER TABLE customers
    DROP COLUMN initials;
    CUSTOMERS
    FIRST_NAME
    LAST_NAME
    :

    View full-size slide

  12. Invisible Columns
    Copyright © 2023, Oracle and/or its affiliates
    -- Code for App A
    SELECT *
    FROM customers;
    FIRST_NAME LAST_NAME ADDRESS …
    ---------- ---------- --------
    Dan Jones New York
    Joseph Smith Los Angeles
    -- Code for App B
    SELECT first_name, last_name, initials
    FROM customers;
    FIRST_NAME LAST_NAME IN
    ---------- ---------- --
    Dan Jones DJ
    Joseph Smith JS
    -- But if App A simple marks the column
    invisible, other Apps that query it directly
    will still work
    ALTER TABLE customers
    MODIFY initials INVISIBLE;
    CUSTOMERS
    FIRST_NAME
    LAST_NAME
    :

    View full-size slide

  13. Managing Statistics
    • By default, optimizer statistics are published as soon as they are gathered
    • But what if you want to experiment with different statistic-gathering options?
    • How do you protect yourself from the possibility of unpredictable changes in execution plans
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  14. Using Invisible or Pending statistics allows you to test without worry
    -- Check the current statistics publishing setting for the database
    SELECT dbms_stats.get_prefs('PUBLISH', 'MARIA', 'SALES') FROM dual;
    DBMS_STATS.GET_PREFS('PUBLISH’)
    -------------------------------------------
    TRUE
    -- Check the current column stats for the SALES table
    SELECT column_name, histogram FROM user_tab_col_statistics WHERE table_Name ='SALES';
    COLUMN_NAME HISTOGRAM
    ------------------------------ ---------------
    PROD_ID NONE
    CUST_ID NONE
    TIME_ID NONE
    CHANNEL_ID NONE
    PROMO_ID NONE
    QUANTITY_SOLD NONE
    AMOUNT_SOLD NONE
    Copyright © 2023, Oracle and/or its affiliates
    None of the columns have a histogram

    View full-size slide

  15. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    -- Set new statistics gathered for MARIA.SALES to be invisible to the optimizer by default
    BEGIN
    dbms_stats.set_table_prefs('MARIA', 'SALES', 'PUBLISH', 'FALSE');
    END;
    /
    -- Gather a new set of statistics for MARIA.SALES that included new histograms types introduced in 12c
    BEGIN
    dbms_stats.gather_table_stats('MARIA', 'SALES' method_opt=>'FOR ALL COLUMNS SIZE 254');
    END;
    /

    View full-size slide

  16. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    -- Confirm the pending statistics are what we are expecting
    SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', systimestamp,0));
    The returned REPORT is a CLOB
    You must use the set long and longchunksize command to define the width
    of a long so the report can be displayed properly

    View full-size slide

  17. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    -- Confirm the pending statistics are what we are expecting
    SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', systimestamp,0));
    DBMS_STAT.DIFF_TABLE_STATS functions are table functions,
    you must use the key word TABLE when you are selecting from them,
    otherwise you will receive an error saying the object does not exist

    View full-size slide

  18. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    -- Confirm the pending statistics are what we are expecting
    SELECT report FROM table(dbms_stats.diff_table_stats_in_pending('MARIA','SALES', sysdate,0));
    REPORT
    --------------------------------------------------------------------------------
    :
    COLUMN STATISTICS DIFFERENCE:
    .............................
    COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZE
    ..................................................................................
    AMOUNT_SOLD A 583 .001715265 NO 0 4 C105 C2525 960
    B 583 .001715265 NO 0 4 C105 C2525 960
    CUST_ID A 630 .001587301 NO 0 5 C202 C3135 960
    B 630 .001576 YES 0 5 C202 C3135 960
    PROD_ID A 766 .001305483 NO 0 5 C106 C3056 960
    B 766 .001298 YES 0 5 C106 C3056 960
    PROMO_ID A 116 .008620689 NO 0 4 C102 C2646 960
    B 116 .008620689 NO 0 4 C102 C2646 960
    QUANTITY_SOLD A 44 .022727272 NO 0 3 C102 C130 960
    B 44 .022727272 NO 0 3 C102 C130 960
    TIME_ID A 620 .001612903 NO 0 8 77C60 78640 960
    B 620 .001608 YES 0 8 77C60 78640 960
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Source A is the current
    published stats for the SALES
    table, while source B is the
    pending stats

    View full-size slide

  19. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    --Set the initialization parameter optimizer_use_pending_statistics to TRUE to test out the new statistics
    ALTER SESSION SET optimizer_use_pending_statistics =TRUE;
    -- After testing the pending statistics with histograms, we can go ahead and publish them
    BEGIN
    dbms_stats.publish_pending_stats('MARIA','SALES');
    END;
    /

    View full-size slide

  20. Using Invisible or Pending statistics allows you to test without worry
    Copyright © 2023, Oracle and/or its affiliates
    --Set the initialization parameter OPTIMIZER_USE_PENDING_STATISTICS to TRUE to test new statistics
    ALTER SESSION SET optimizer_use_pending_statistics =TRUE;
    -- After testing the pending statistics with histograms, we can go ahead and publish them
    BEGIN
    dbms_stats.publish_pending_stats('MARIA','SALES');
    END;
    /
    -- Confirm the histograms that we gather as pending statistics are now visible
    SELECT column_name, histogram FROM user_tab_col_statistics WHERE table_Name ='SALES';
    COLUMN_NAME HISTOGRAM
    ------------------------------ ---------------
    PROD_ID HYBRID
    CUST_ID HYBRID
    TIME_ID HYBRID
    CHANNEL_ID NONE
    PROMO_ID NONE
    QUANTITY_SOLD NONE
    AMOUNT_SOLD NONE

    View full-size slide

  21. Useful Online Operations
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  22. Why Online Operations are so important
    DBAs often have a long list of maintenance activities & data model changes they need to do
    • Goal to improve overall system performance
    In the past, these tasks have required exclusive locks
    • Forcing DBAs to wait until application downtime to complete them
    With online operations, these tasks can occur, when necessary, without delay
    Online operations
    • Wait for active transactions to end before beginning
    • Do not block any new DML statements (with the only exception being parallel DML)
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  23. Adding a column to a table happens online
    Copyright © 2023, Oracle and/or its affiliates
    A fast online operation that allocates no space and generates no redo or undo
    -- Adding a column with or without a default value is a metadata-only operation since 11g
    SELECT bytes/1024/1024 MB, blocks FROM user_segments WHERE segment_name='SALES';
    MB BLOCKS
    176 22528
    ALTER TABLE sales ADD tax_code varchar2(20) DEFAULT 'xxx-xxx' NOT NULL;
    Table SALES altered.
    SELECT bytes/1024/1024 MB, blocks FROM user_segments WHERE segment_name='SALES';
    MB BLOCKS
    176 22528

    View full-size slide

  24. 09:37:38 SQL> CREATE TABLE sales3 AS SELECT * FROM sales;
    Table SALES3 created.
    09:37:41 SQL> SELECT column_name, num_distinct, num_nulls, histogram
    FROM user_tab_col_statistics
    WHERE table_name='SALES3';
    COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
    COMMENTS 0 10075747 NONE
    SHIPMODE 7 0 NONE
    REVENUE 4296902 0 NONE
    PRICE 2416797 0 NONE
    AMOUNT_SOLD 50 0 NONE
    DATE_ID 2395 0 NONE
    SUPPLIER_ID 1924463 0 NONE
    PRODUCT_ID 1370318 0 NONE
    CUST_ID 2021661 0 NONE
    ORDER_ID 2116177 0 NONE
    Online statistics gathering
    Copyright © 2023, Oracle and/or its affiliates
    Online statistics automatically gathers
    base statistics as part of any CTAS or
    IAS operation into an empty table or
    partition
    Histograms are not created as they
    require an additional pass of the data
    and add an overhead to the initial op

    View full-size slide

  25. 09:47:40 SQL> ALTER SESSION SET "_optimizer_gather_stats_on_load_hist"=TRUE;
    09:47:40 SQL> CREATE TABLE sales3 AS SELECT * FROM sales;
    Table SALES3 created.
    09:47:51 SQL> SELECT column_name, num_distinct, num_nulls, histogram
    FROM user_tab_col_statistics
    WHERE table_name='SALES3';
    COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
    COMMENTS 0 10075747 NONE
    SHIPMODE 7 0 FREQUENCY
    REVENUE 4296902 0 HYBRID
    PRICE 2416797 0 HYBRID
    AMOUNT_SOLD 50 0 FREQUENCY
    DATE_ID 2395 0 HYBRID
    SUPPLIER_ID 1924463 0 HYBRID
    PRODUCT_ID 1370318 0 HYBRID
    CUST_ID 2021661 0 HYBRID
    ORDER_ID 2116177 0 HYBRID
    Online statistics gathering with histograms
    Copyright © 2023, Oracle and/or its affiliates
    Be careful utilizing underscore
    parameters. Only use them
    within a session, and if you fully
    understand the behavior, they
    control

    View full-size slide

  26. -- Ability to convert a non-partitioned table to a partitioned table online
    ALTER TABLE sales MODIFY
    PARTITION BY RANGE(date_id)
    INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')),
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-FEB-2018', 'DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAR-2018', 'DD-MON-YYYY’))
    )
    ONLINE
    UPDATE INDEXES
    ( idx_dt_rev local,
    idx_ord_sold GLOBAL PARTITION BY RANGE (order_id)
    ( PARTITION ip0 VALUES LESS THAN (MAXVALUE))
    );
    Table SALES altered.
    Online partitioning of a non-partitioned table
    Copyright © 2023, Oracle and/or its affiliates
    SALES
    SALES
    The use of the keyword ONLINE enables concurrent
    DML operations while the conversion is ongoing

    View full-size slide

  27. -- Ability to convert a non-partitioned table to a partitioned table online
    ALTER TABLE sales MODIFY
    PARTITION BY RANGE(date_id)
    INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')),
    PARTITION p1 VALUES LESS THAN (TO_DATE('01-FEB-2004', 'DD-MON-YYYY')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('01-MAR-2004', 'DD-MON-YYYY’))
    )
    ONLINE
    UPDATE INDEXES
    ( idx_dt_rev local,
    idx_ord_sold GLOBAL PARTITION BY RANGE (order_id)
    ( PARTITION ip0 VALUES LESS THAN (MAXVALUE))
    );
    Table SALES altered.
    Online partitioning of a non-partitioned table
    Copyright © 2023, Oracle and/or its affiliates
    SALES
    SALES
    If you don’t specify what
    should happen to the
    indexes, Oracle will create
    local partitioned index if
    they contain the partition
    key or are bitmap indexes
    Otherwise, indexes become
    global non-partitioned
    indexes

    View full-size slide

  28. Parameterized Views
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  29. Database Views
    Database views have been used for decades to help simplify both ad-hoc queries and reporting
    But the problem with defining views is they are either too specific or not specific enough
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  30. Database Views
    Database views have been used for decades to help simplify both ad-hoc queries and reporting
    But the problem with defining views is they are either too specific or not specific enough
    In this example, we need a view to help quickly find details about orders that are waiting to ship
    Copyright © 2023, Oracle and/or its affiliates
    CREATE OR REPLACE VIEW orders_waiting
    AS
    SELECT i.*
    FROM orders o, order_items i
    WHERE o.order_status > 6
    AND o.order_total >= 100
    AND o.order_id = i.order_id’;
    Extremely specific, efficient view
    includes no unnecessary data but not reusable
    CREATE OR REPLACE VIEW order_details
    AS
    SELECT o.order_status, o.order_total,
    o.cust_id, o.order_date, o.rep,
    o.order_mode, o.promotion_id, i.*
    FROM orders o, order_items i
    WHERE o.order_id = i.order_id;
    Nonspecific view that includes
    lots of unnecessary data , so it can be reusable

    View full-size slide

  31. SQL Macros allow you to create portable, parameterized code
    Easy to create portable code by factoring out common SQL expressions and statements into reusable,
    parameterized constructs with SQL Macros
    A Macro acts like a pre-processor for SQL with no expensive context switching
    • Enabling faster performance
    Two types of SQL Macros:
    • TABLE expressions typically found in FROM-clause that act as a parameterized views
    • SCALAR expressions to encapsulate calculations and business logic typically found in SELECT list,
    WHERE/HAVING, GROUP BY/ORDER BY clauses
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  32. CREATE OR REPLACE FUNCTION orders_waiting_to_ship(order_value integer)
    RETURN clob sql_macro AS
    stmt clob;
    BEGIN
    stmt := '
    SELECT i.*
    FROM orders o, order_items i
    WHERE o.order_status > 6
    AND o.order_total >= order_value
    AND o.order_id = i.order_id';
    RETURN stmt;
    END orders_waiting_to_ship;
    /
    Parameterized View – AKA SQL Macro
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  33. SELECT *
    FROM orders_waiting_to_ship(100)
    ORDER BY order_id;
    ORDER_ID PRODUCT_ID PRICE DESCRIPTION
    ___________ _____________ ________ ______________
    70707 7 17 Lip gloss
    70707 8 28 Mascara
    70707 9 39 Blusher
    70707 10 30 lipstick
    80808 8 28 Mascara
    80808 8 28 Mascara
    80808 8 28 Mascara
    80808 8 28 Mascara
    Parameterized View – AKA SQL Macro
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  34. Useful PL/SQL Packages
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  35. DBMS_LOCK.SLEEP()
    From time to time, all code needs to pause or sleep for a short time
    Copyright © 2023, Oracle and/or its affiliates
    SQL> DECLARE
    v_start timestamp;
    v_end timestamp;
    BEGIN
    v_start := SYSTIMESTAMP;
    -- Sleep for 10 seconds
    DBMS_LOCK.SLEEP(10);
    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
    DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end);
    END;
    /
    This procedure started at 10-SEP-22 12.39.40.587041 AM
    This procedure ended at 10-SEP-22 12.39.50.637738 AM
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:10.02
    The problem is DBMS_LOCK includes
    other, more sensitive methods
    Therefore, not granted to public
    Requires DBA intervention to get a
    accessible sleep function in PL/SQL

    View full-size slide

  36. DBMS_SESSION.SLEEP()
    From time to time, all code needs to pause or sleep for a short time
    Copyright © 2023, Oracle and/or its affiliates
    SQL> DECLARE
    v_start timestamp;
    v_end timestamp;
    BEGIN
    v_start := SYSTIMESTAMP;
    -- Sleep for 10 seconds
    DBMS_SESSION.SLEEP(10);
    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.PUT_LINE('This procedure started at ' ||v_start);
    DBMS_OUTPUT.PUT_LINE('This procedure ended at ' ||v_end);
    END;
    /
    This procedure started at 10-SEP-22 12.39.40.587041 AM
    This procedure ended at 10-SEP-22 12.39.50.637738 AM
    PL/SQL procedure successfully completed.
    Elapsed: 00:00:10.02
    Sleep function moved to DBMS_SESSION
    DBMS_SESSION is granted to public
    Compatible with DBMS_LOCK.SLEEP,
    simply do a search/replace

    View full-size slide

  37. How do you determine which view is the right view to use?
    Views help to hide complexity from developers
    But they can also cause problems
    They make it incredibly easy to write apparently simple
    SQL statements that result in a highly complex SQL
    statement being sent to the database
    The DBMS_UTILITY.EXPAND_SQL_TEXT procedure
    expands references to views, turning them into
    subqueries in the original statement
    Copyright © 2023, Oracle and/or its affiliates
    SELECT * FROM sales_v;

    View full-size slide

  38. DBMS_UTILITY.EXPAND_SQL_TEXT
    set serveroutput on
    DECLARE
    l_clob CLOB;
    BEGIN
    dbms_utility.Expand_sql_text(input_sql_text => 'SELECT * FROM sales_v', -
    output_sql_text => l_clob);
    dbms_output.Put_line(l_clob);
    END;
    /
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  39. DBMS_UTILITY.EXPAND_SQL_TEXT
    SELECT "A1"."order_id" "ORDER_ID",
    "A1"."time_id" "TIME_ID",
    "A1"."cust_id" "CUST_ID",
    "A1"."prod_id" "PROD_ID"
    FROM (SELECT "A3"."order_id" "ORDER_ID",
    "A3"."time_id" "TIME_ID",
    "A3"."cust_id" "CUST_ID",
    "A3"."prod_id" "PROD_ID"
    FROM "SH"."sales" "A3",
    "SH"."products" "A2"
    WHERE "A3"."prod_id" = "A2"."prod_id") "A1"
    Copyright © 2023, Oracle and/or its affiliates
    View definition has alias A1

    View full-size slide

  40. How to determine why production is different to test
    Trying to figure out why things behave differently in production than they do in test can be time-
    consuming and painful
    The DBMS_COMPARISON package allows you to compare objects, schemas, or data between two
    different databases or schemas
    Note: for a table comparison you do need a unique index on both tables
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  41. DBMS_UTILITY.EXPAND_SQL_TEXT
    -- Begin by creating the comparison
    BEGIN
    dbms_comparison.create_comparison(
    comparison_name => 'COMP_SALES',
    schema_name => 'SH',
    object_name => 'SALES',
    dblink_name => 'orcl2_test'
    );
    END;
    /
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  42. DBMS_COMPARISON
    Execute the COMPARE function to perform the compare operation
    DECLARE
    scan_info DBMS_COMPARISON.COMPARISON_TYPE;
    BEGIN
    IF NOT DBMS_COMPARISON.COMPARE
    ( comparison_name => 'COMP_SALES'
    , scan_info => scan_info
    , perform_row_dif => TRUE
    ) THEN
    DBMS_OUTPUT.PUT_LINE('Scan ID:'||scan_info.scan_id);
    END IF;
    END;
    /
    Scan ID: 1
    Copyright © 2023, Oracle and/or its affiliates
    The scan_id allows you to find out what the differences are between the systems
    It will return a BOOLEAN
    indicating if the object is
    consistent or not

    View full-size slide

  43. DBMS_COMPARISON
    SELECT c.column_name, r.index_value
    case when r.local_rowid is null then ‘No’ else 'Yes’ end LOC,
    Case when r.remote_rowid is null then 'No’ else 'Yes’ end REM
    FROM user_comparison_columns c, user_comparison_row_dif r, user_comparison_scan s
    WHERE c.comparison_name = 'COMP_SALES‘
    AND r.scan_id = s.scan_id
    AND r.status = 'DIF’
    AND c.index_column = 'Y’
    AND c.comparison_name = r.comparison_name
    AND s.scan_id = 1
    ORDER BY r.index_value;
    COLUMN_NAME INDEX_VALUE LOC REM
    ----------- ----------- --- ----
    TAX_CODE 0.05 No Yes
    Copyright © 2023, Oracle and/or its affiliates
    The test environment has a different tax code

    View full-size slide

  44. Immutability
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  45. Protecting data from corrupt or compromised users
    All information stored in a database is important
    And this data is often most vulnerable to users with valid credentials
    Over the years, Oracle has offered several techniques to help protect that data:
    • Read-only tablespaces
    • Read-only users
    • Read-only views
    • Auditing
    • Schema-only accounts
    But up until now, there hasn’t been a way to allow a table to continue to accept new data but prevent
    anyone, including SYSDBAs, from changing any existing of the data
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  46. Oracle Immutable Tables
    Oracle Immutable Tables prevent illicit modifications by database users
    • Allows relational data, JSON, or LOB documents
    New data can be added, but existing data cannot be changed or deleted by
    anyone using the database
    • Even Database Administrators
    The table definition can not be changed, and it’s not possible to convert
    immutable table to updatable or vice-versa
    • It also not possible to modify table metadata in the database dictionary
    Side benefit: immutability also prevents accidental changes due to human error
    Copyright © 2023, Oracle and/or its affiliates
    Available in Database 19.11, 21.3

    View full-size slide

  47. Prevent illicit changes by Insiders using Immutable Tables
    -- Oracle Immutable Tables prevent illicit modifications by insiders using SQL but allow new data to be added
    CREATE IMMUTABLE TABLE trade_ledger (id number, stock_name varchar2(40), value number)
    NO DROP UNTIL 40 DAYS IDLE
    NO DELETE UNTIL 100 DAYS AFTER INSERT;
    -- Anyone with the appropriate privileges can insert into an immutable table
    INSERT INTO trade_ledger VALUES( 1000, ‘ORCL’, 500);
    1 row inserted.
    -- However, it’s not possible to delete from the table until the specified amount of time has elapsed
    DELETE FROM trade_ledger WHERE id=1000;
    SQL Error: ORA-05715: operation not allowed on the blockchain or immutable table
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  48. Five things you may not know or have forgotten about the Oracle Database
    3.
    Parameterized
    Views
    2.
    Online
    Operations
    5.
    Power of
    Immutability
    4.
    PL/SQL
    Packages
    1.
    Benefits of
    Invisibility
    Copyright © 2023, Oracle and/or its affiliates

    View full-size slide

  49. More Information Visit:
    SQLMaria.com
    @SQLMaria
    youtube.com/c/MariaColgan42
    Copyright © 2023, Oracle and/or its affiliates
    If you have more questions
    later, feel free to ask
    Thank you

    View full-size slide