SQL Troubleshooting Step By Step Lab in Oracle 19c RAC Database

 Hi All,

In this post i am sharing related to SQL Troubleshooting lab excise Step by step Process.

Firstly check the status of database services and load on the servers.

- here i am using 2 Node RAC Database , as we can see from the above snap 2 nodes database are running fine, will bit load on this server.

- Check the database status and confirmed its not a multitenant database 

- For SQL Troubleshooting Lab we can create a Table and load some data 

SQL> CREATE TABLE customer_orders (

    order_id     NUMBER,

    customer_id  NUMBER,

    order_date   DATE,

    order_amount NUMBER,

    status       VARCHAR2(10)

);

Table created.

SQL> select * from customer_orders;

no rows selected

- No data in that table , now i can load some data 

SQL> BEGIN

  FOR i IN 1 .. 500000 LOOP

  INSERT INTO customer_orders VALUES (

      i,

      MOD(i, 1000),

      SYSDATE - MOD(i, 365),

      ROUND(DBMS_RANDOM.VALUE(100, 10000)),

      CASE WHEN MOD(i, 3) = 0 THEN 'NEW'

           WHEN MOD(i, 3) = 1 THEN 'CLOSED'

           ELSE 'PENDING' END

    );

  END LOOP;

  COMMIT;

END;

 /

PL/SQL procedure successfully completed.

SQL> select count(*) from customer_orders;

  COUNT(*)

----------

    500000

- Now i have loaded 5 lakh rows of data.

- From this table we try to access the data and we can identify how much resource it consume like CPU memory and whether its taking full table scan or how we can see it.

-  this sql is insufficient.

SELECT * FROM customer_orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-06-28' AND status = 'NEW';

- Here we can see more rows of data  , problems from this query is TO_CHAR() disables index usage , Causes Full table scan , also it leads to High CPU and I/O usage.

Now here is main point how we can troubleshoot this problematic sql .

- Generate explain Plan for this sql .

EXPLAIN PLAN FOR SELECT * FROM customer_orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-06-28' AND status = 'NEW';

- here can see this sql is full table scan

- we can generate AWR and monitor this sql .

@$ORACLE_HOME/rdbms/admin/awrrpt.sql 

we can check the TOP sql   by Elapsed time / Buffer Gets also look for db file scattered read( sign of Full Table scan).

- Monitor the active sql's which are executing this sql will work from 19C db onwards.

SQL> SELECT sql_id, sql_text, status, elapsed_time, cpu_time

FROM v$sql_monitor WHERE status = 'EXECUTING';

 no rows selected

How to Troubleshoot the above Full table scan ? 

- From the above sql we identified problems which are

Issue 1 :  TO_CHAR() disables index usage , Causes Full table scan 

Fix for Issue 1 : 

SQL> SELECT * FROM customer_orders WHERE order_date = TO_DATE('2025-06-28', 'YYYY-MM-DD') AND status = 'NEW';

no rows selected

- Add index to that table .

SQL> CREATE INDEX idx_order_date_status ON customer_orders(order_date, status);

Index created.

- re-run the explain plan and see the output .

EXPLAIN PLAN FOR SELECT * FROM customer_orders WHERE order_date = TO_DATE('2025-06-28', 'YYYY-MM-DD') AND status = 'NEW';

Explained.

- Now its Index  range scan we avoid the full table scan . 

SQL Monitor report: should show reduced elapsed time

AWR: reduced cost/CPU/IO stats

Post Index creation You should see faster execution and lower logical reads .

SELECT * FROM customer_orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-06-28' AND status = 'NEW';

without Index :

after index : 

Note: Generally we dont see when we used where clause with small amount of data request but in real time when we are accessing huge amount of data then we can understand clearly the power of Indexing .

Thanks ,

Srini