Ask Tom MOST RECENT ARTICLES

1. Defragmentation in Oracle RAC Dataguard Environment

   Hi Tom Greetings We had very high critical Application(with zero downtime) running in 4 Node RAC Environment with same HA Configuration. We are planning to perform defragmentation(Index) with minimal downtime. We are planning the following steps 1) Disable Apply and open Standby database 2) Perform Index Defragmentation in Standby Database 3) After defragmentation, put back Physical Standby in Mount State Now Standby is defragmentated 1) Switch Application to Standby(Switch Standby DB to Primary and vice versa) 2) Perform Defragmentation in Old Primary(Now HA) 3) Switch back application to Old Primary My question is whether the above is possible? Or I am missing any steps here? Or when switching HA as Primary and Primary as HA, whether defragmentation will go since it is block to block replication? Please advise

2. LINGUISTIC and BINARY_CI performance problem

   Hello, We use Oracle 19c with c# ADO and EntityFramework. We have performance issue when use ALTER SESSION SET NLS_COMP = LINGUISTIC NLS_SORT = BINARY_CI; Our system use this for all queries. I use this simple table for test: <code>CREATE TABLE app."Customer" ( "Id" NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY, "Name" NVARCHAR2(254) NOT NULL, PRIMARY KEY ("Id") ) BEGIN FOR i IN 1..2000000 LOOP INSERT INTO app."Customer" ("Name") VALUES ( DBMS_RANDOM.STRING('U', 10) ); IF MOD(i, 10000) = 0 THEN COMMIT; END IF; END LOOP; -- Final commit for any remaining rows COMMIT; END; CREATE INDEX app."IX_Customer_Name_NLS" ON app."Customer" (NLSSORT("Name", 'NLS_SORT=BINARY_CI')) CREATE INDEX app."IX_Customer_Name" ON app."Customer" ("Name")</code> Now if I select: 0.02ms <code>SELECT * FROM app."Customer" WHERE "Name" LIKE N'test' || '%'</code> 0.02ms <code>SELECT * FROM app."Customer" WHERE "Name" LIKE &name || '%'</code> 0.02ms <code> BEGIN FOR rec IN (SELECT "Name" FROM app."Customer" WHERE "Name" LIKE 'test%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY) LOOP DBMS_OUTPUT.PUT_LINE(rec."Name"); END LOOP; END; </code> 0.700ms <code> DECLARE v_name NVARCHAR2(254) := N'test'; BEGIN FOR rec IN ( SELECT "Name" FROM app."Customer" WHERE "Name" like v_name || '%' OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY ) LOOP DBMS_OUTPUT.PUT_LINE(rec."Name"); END LOOP; END;</code> ADO+EF uses the last option when sending a request, so all system works slowly. I try make 4 different query for last query with parameter: BINARY+BINARY LINGUISTIC +BINARY BINARY+BINARY_CI LINGUISTIC +BINARY_CI Results: <code>Query 1 duration: 14 ms Query 2 duration: 11 ms Query 3 duration: 10 ms Query 4 duration: 557 ms <----LINGUISTIC +BINARY_CI</code> If take only 1-5 rows - the result will be fast, if it found it. If not, it will be slow - possible it do a full scan. When I crate another DB with max_string_size=EXTENDED Create same table with "Name" NVARCHAR2(254) COLLATE BINARY_CI NOT NULL I got this on all queres Results: <code>Query 1 duration: 173 ms Query 2 duration: 173 ms Query 3 duration: 171 ms Query 4 duration: 180 ms</code> Look like it that faster when use session, but not fast as if make plain parameterless sql request. I want to achieve the speed of a plain sql request, if it is possible. Thank you.

3. 2 databases got different IO stats on the same disk

   Noticed 2 databases ran on the same disk got different performance on same query in identical plan. Ran AWR and saw big difference in IO stats section. i.e Buffer Cache Reads is in us level but another one is in ms. What could caused this difference if on the same disk, it is /u01 in my case ? thanks. 19c on linux filesystemio_options none =>One active database <code> IOStat by Function summary 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 ordered by (Data Read + Write) desc Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Time Direct Reads 2.9T 845.98 832.589M 8M 0.06 .002M 3.1M 65.22ns Buffer Cache Reads 26.5G 756.04 7.484M 0M 0.00 0M 2.2M 165.58us Others 519M 7.27 .143M 263M 1.57 .073M 29.4K 75.63us </code> =>Another much less active database <code>IOStat by Function summary 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Time Direct Reads 49.2G 14.35 14.154M 0M 0.00 0M 51.1K 1.31us Others 220M 3.45 .062M 53M 0.95 .015M 14K 24.99us Buffer Cache Reads 214M 1.05 .06M 0M 0.00 0M 3163 2.56ms</code>

4. Oracle Database 23Free SYSAUX size causing ORA-12954

   Hi Tom, I?m encountering an issue with my Oracle Database 23Free. It throws the ORA-12954 error: "The request exceeds the maximum allowed database size of 12 GB." After investigating the database files, I discovered that the SYSAUX tablespace is the main contributor to the size issue. <code> /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf 6.1GB /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf 0.6GB /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf 0.3GB /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf 0.3GB /opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf 0.3GB /opt/oracle/oradata/FREE/pdbseed/system01.dbf 0.3GB /opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf 0.1GB /opt/oracle/oradata/FREE/sysaux01.dbf 6.6GB /opt/oracle/oradata/FREE/system01.dbf 1.2GB /opt/oracle/oradata/FREE/undotbs01.dbf 0.6GB /opt/oracle/oradata/FREE/users01.dbf 0.0GB </code> Upon further examination using <code> select * from V$SYSAUX_OCCUPANTS; </code> it seems that the majority of the space is being utilized by: ? SM/AWR ? SM/OPTSTAT To address this, I attempted to purge statistics using the following PL/SQL block: <code> BEGIN dbms_stats.purge_stats(dbms_stats.purge_all); END; </code> However, I received the ORA-12954 error again during this process. Do you have any advice on how to resolve this issue and bring the database back within the size limit? Your help would be greatly appreciated! Best regards, Christian

5. difference between 2 dates

   I want to get difference between two dates say (10-FEB-2000 - 02-JAN-1999) and the result has to be displayed in following format 1 year, 1 month, 8 days