Hi
One of our Physical Standby database aborted with "ORA-27061: waiting for async I/Os failed" error. The instance was killed by the DBWR process. Apparently, it was found to be an OS bug (IBM - AIX5L Based Systems) and an OS patch seems to resolve this issue.
Our current OS Patch level is AIX5.3 TL5-CSP and we need to upgrade it to either AIX5.3 TL6 SP4 or AIX5.3 TL7 to avoid this problem occurring in the future.
References:
Metalink Note Id: 467613.1 addresses this issue.
Happy reading :-)
Monday, May 12, 2008
Monday, May 05, 2008
Becareful when using DBMS_UTILITY to analyze
Hello,
If you are still using DBMS_UTILITY.ANALYZE_DATABASE or DBMS_UTILITY.ANALYZE_SCHEMA to analyze your database/schema's then you need to be very cautious.
I have observed a very strange behavior of this procedure against partitioned tables in one of our databases. Statistics are not being updated at table level. Although, partitions statistics are up to date.
The reason for incorrect statistics is:
If you use DBMS_STATS package to gather table statistics on a partitioned table and then later you use DBMS_UTILITY.ANALYZE_SCHEMA, table-level statistics are NOT updated, rather, statistics on partitions and indexes are modified.
This peculiar behavior is observed only with partitioned tables.
ANALYZE_SCHEMA procedure is obsolete and any one of us using this to gather statistics should seriously think of moving to DBMS_STATS package.
Following is a simple demo:
SQL> CREATE TABLE part_tab
2 (id NUMBER(5),
3 dt DATE)
4 PARTITION BY RANGE(dt)
5 (
6 PARTITION part1_jan2008 VALUES LESS THAN(TO_DATE('01/02/2008','DD/MM/YYYY')),
7 PARTITION part2_feb2008 VALUES LESS THAN(TO_DATE('01/03/2008','DD/MM/YYYY')),
8 PARTITION part3_mar2008 VALUES LESS THAN(TO_DATE('01/04/2008','DD/MM/YYYY')),
9 PARTITION part4_apr2008 VALUES LESS THAN(TO_DATE('01/05/2008','DD/MM/YYYY'))
10 );
Table created.
SQL>
SQL>
SQL> create table non_part_tab (id number, dt date);
Table created.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB
PART_TAB
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008
PART1_JAN2008
PART3_MAR2008
PART4_APR2008
SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:05:43
PART_TAB 0 05-05-2008 00:05:43
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:05:43
PART1_JAN2008 0 05-05-2008 00:05:43
PART3_MAR2008 0 05-05-2008 00:05:43
PART4_APR2008 0 05-05-2008 00:05:43
SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:05:59
PART_TAB 0 05-05-2008 00:05:59
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:05:59
PART1_JAN2008 0 05-05-2008 00:05:59
PART3_MAR2008 0 05-05-2008 00:05:59
PART4_APR2008 0 05-05-2008 00:05:59
SQL> exec dbms_stats.gather_schema_stats('TEST');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:06:11
PART_TAB 0 05-05-2008 00:06:11
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:06:11
PART1_JAN2008 0 05-05-2008 00:06:11
PART3_MAR2008 0 05-05-2008 00:06:11
PART4_APR2008 0 05-05-2008 00:06:11
SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:06:23
PART_TAB 0 05-05-2008 00:06:11 ---> Statistics are NOT updated.
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:06:23
PART1_JAN2008 0 05-05-2008 00:06:23
PART3_MAR2008 0 05-05-2008 00:06:23
PART4_APR2008 0 05-05-2008 00:06:23
SQL> exec dbms_utility.analyze_schema('TEST', 'COMPUTE');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:08:53
PART_TAB 0 05-05-2008 00:06:11 ---> Statistics are NOT updated.
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:08:53
PART1_JAN2008 0 05-05-2008 00:08:53
PART3_MAR2008 0 05-05-2008 00:08:53
PART4_APR2008 0 05-05-2008 00:08:53
SQL> exec dbms_stats.gather_schema_stats('TEST');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows, last_analyzed from user_tables ;
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
NON_PART_TAB 0 05-05-2008 00:09:05
PART_TAB 0 05-05-2008 00:09:05
SQL> select partition_name, num_rows, last_analyzed from user_tab_partitions;
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
PART2_FEB2008 0 05-05-2008 00:09:05
PART1_JAN2008 0 05-05-2008 00:09:05
PART3_MAR2008 0 05-05-2008 00:09:05
PART4_APR2008 0 05-05-2008 00:09:05
SQL>
Useful references:
1)How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS (Note: 237397.1)
2) Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)
Happy reading !!!
Thursday, May 01, 2008
Small Change and A Huge Gain
Hi,
Elapsed time of one of our data warehouse procedure was 17 minutes on an average. Following is the skeleton procedure:
Create or replace procedure Update_Customers Is
Cursor Cust_Cur Is
Select * From Stg_Customers;
l_Cust_Value Customers.Cust_Value;
Begin
For Cust_Rec In Cust_Cur Loop
:
:
Select Value Into l_Cust_Value
From Customers
Where Cust_Code = Cust_Rec.Cust_Code
And Cust_Key = Cust_Rec.Cust_Key;
:
:
End Loop;
End Update_Customers;
Upon taking snaps before and after executing the procedure it was evident that most of the time was consumed by the "SELECT...FROM Customers...." statement.
Elapsed Time from AWR report:
Procedure: 1021 Seconds
SQL Statement: 925 Seconds
There is a Composite Index on "Cust_Code" and "Cust_Key" columns. When I ran the same statement in SQL*Plus, it was fetching results very fast using the appropriate index.
STG_CUSTOMERS is a staging table which consists of nearly 250,000 records. Data is daily purged and populated in this table. CUSTOMERS table was probed for 250,000 times in the loop, individual query execution was very fast but repeated executions within the loop were causing the query to consume more time.
I replaced the original cursor by joining CUSTOMERS and STG_CUSTOMERS tables as shown below:
Create or replace procedure Update_Customers Is
Cursor Cust_Cur Is
Select * From Stg_Customers A, Customers b
Where a.cust_code = b.cust_code
And a.cust_key = b.cust_key;
l_Cust_Value Customers.Cust_Value;
Begin
For Cust_Rec In Cust_Cur Loop
:
:
:
End Loop;
End Update_Customers;
When this modified procedure was executed, the performance was remarkably improved and the elapsed time dropped to only less than 75 seconds.
Below is the elapsed time of the same procedure before and after modification:
TYPE DATE Elapsed(Min) CPU(Min) --------------- ----------- --------------- --------------- Procedure 19-Apr-2008 17.70 1.87 SQL Statement 19-Apr-2008 15.98 13.15 Procedure 20-Apr-2008 17.67 1.80 SQL Statement 20-Apr-2008 16.05 12.95 Procedure 21-Apr-2008 16.93 1.82 SQL Statement 21-Apr-2008 15.35 12.85 Procedure 22-Apr-2008 16.68 1.78 SQL Statement 22-Apr-2008 15.08 12.42 Procedure 23-Apr-2008 16.38 1.78 SQL Statement 23-Apr-2008 14.77 12.43 Procedure 24-Apr-2008 1.15 .92 Procedure 25-Apr-2008 1.13 .92 Procedure 26-Apr-2008 1.20 .93 Procedure 27-Apr-2008 1.23 .93Regards
Subscribe to:
Posts (Atom)