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.
SELECT * FROM customer_orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-06-28' AND status = 'NEW';
without Index :
after index :
