Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Perf Tuning’ Category

ORA-20000 Error during concurrent statistics

Posted by Pavan DBA on June 4, 2017


Recently I have faced an issue when application team is executing gather stats in their job.

An application job is loading the data into tables and after that it is performing gather stats. They received below error during gather stats

ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 34634
ORA-06512: at line 1

There are two steps to resolve this issue.

Step 1: Disabling Global preferences

check for the stats global preference value using below query

SELECT DBMS_STATS.get_prefs(‘CONCURRENT’) FROM dual;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)
—————————————————————————————————-
OFF

By output, we can understand that it is OFF and we can turn it on

BEGIN
DBMS_STATS.set_global_prefs (
pname => ‘CONCURRENT’,
pvalue => ‘ALL’);
END;
/

Instead of ALL (which works for both manual and automatic stats collection), we can also set below values
MANUAL – only for manual stats collection
AUTOMATIC – only for automatic stats collection

Step 2: Granting roles to the user

If you see that Global preferences value is already set to ALL, you need to grant below mentioned roles to the user which is performing gather stats.
These grants are not default, so users will face issues if they use concurrent statistics.

SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO testuser;

Once both of above steps are done, issue is resolved in my case.

Posted in Perf Tuning | Tagged: , , | Leave a Comment »

How to resolve remote data fetching when taking more time?

Posted by Pavan DBA on December 3, 2013


Hello Friends,

after ages, I got some time to post an article in my blog related to performance issue i faced recently.

To give a background, we have a database running on 10.1.0.4 version and in which there is a create table statement executing in one of data load jobs.

Statement is as below (Note – object names were changed because of security reasons)

CREATE TABLE XYZ.TABLE1 AS SELECT  /*+diriving_site(source) ORDERED */     SOURCE.* FROM     (SELECT NVL(MAX(UPDATED_ON),TO_DATE(‘1900-01-01′,’YYYY-DD-MM’)) MTIME FROM XYZ.TABLE2) T     , ABC.VIEW1@BRING_DATA SOURCE WHERE     source.UPDATED_ON >= t.MTIME

The above statement is trying to create a table by joining a remote view (VIEW1@BRING_DATA) with a table TABLE2 by checking the last updated date (updated_on)

This statement used to run for 5 min at max in 10.1.0.4 version. Recently we have migrated this database to 11.2.0.3. After that, the same statement is taking more than 6 hours of time.

For further analysis, first i checked the last_analyzed_date of tables and indexes involved and everything is perfect. Then I started looking into explain plan of the statement and compared that with the plan on 10.1.0.4 version.

Below is the explain plan on 10.1.0.4

explain plan_WMSB10g - Copy

Below is the explain plan on 11.2.0.3

explain plan_IB11g - Copy

So, from the above explain plans, you can observe that in 10g, whatever the data required from the remote view (ABC.VIEW1@BRING_DATA) is getting filtered in the remote site itself whereas on 11g, Oracle is pulling entire data from remote view and then it is filtering on the local database. This is the main reason for the time delay in 11g.

The primary reason for this is change of optimizer behaviour in 11.2.0.3 version. To fix this, we have modified the application code into 3 sections

1. creating an empty table 2. declaring a pl/sql block as below

DECLARE     V_DATE DATE; BEGIN     SELECT NVL (MAX (UPDATED_ON),TO_DATE (‘1900-01-01’, ‘YYYY-DD-MM’)) INTO V_DATE FROM XYZ.TABLE2;

3. inserting the data into XYZ.TABLE1

After the code change, the table is getting created in just 20sec including data which is a wonderful improvement in response time.

Hope this will be a good learning for us on tuning issues… HAPPY LEARNING 🙂

Posted in Perf Tuning | Tagged: , , , , , , , , , | 4 Comments »

 
%d bloggers like this: