v$rman_status slow 11gR2

SQL> exec dbms_stats.DELETE_TABLE_STATS(‘SYS’,’X$KCCRSR’)

SQL> select p, NULL, NULL from (select count(*) p from v$rman_status where operation = ‘BACKUP COPYROLLFORWARD’);

Posted in Uncategorized | Leave a comment

nls_length_semantics=CHAR again

Check nls_length_semantics=BYTE again. And again. And again.

When collecting statistics got  ORA-20002: Version of statistics table … is too old.
Please try upgrading it with dbms_stats.upgrade_stat_table

Know why ? Because the statistics table was created with nls_length_semantics=CHAR.

Oracle compares etalon table SYS.IMPDP_STATS with your stats table and yes, column sizes differ because SYS oh, yes has column size 30 but your table has (oh, yesss) column size 120. 

 

 

 

 

 

Posted in Uncategorized | Leave a comment

impdp import statistics failes after upgrade to 11g

If while importing statistics with impdp your are getting

ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-06550: line 1, column 463:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 443:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 498:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 478:
PL/SQL: SQL Statement ignored
Failing sql is:
DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'CLASSIF';   t varchar2(60) := 'RLS_G
Job "CLASSIF"."SYS_IMPORT_FULL_01" completed with 7 error(s) at 09:17:53

then investigate Oracle Support article

DataPump Import (impdp) Raises ORA-942 When Importing Statistics (Doc ID 1313200.1)

Oracle upgrade changes SYS.IMPDP_STATS table from temporary into nomal table. The fix recreates the table by using
dbms_stats.create_stat_table ('SYS', 'IMPDP_STATS', null, true);

and grants
grant select, insert, delete on sys.impdp_stats to public;

Posted in Uncategorized | Leave a comment

LIKE operator and Underscore

Performance problem to be expected with LIKE-operator and underscore character being part of the query parameter, see a new example:

Assuming absolute majority of peoples are good, some are bad and few are ugly:


select * from folks where personality_type like 'THE_GOOD';
— the first fetch will return quickly

select * from folks where personality_type like 'THE_BAD'; -- slow

select * from folks where personality_type like 'THE_UGLY'; -- very slow

Thanks go to http://www.imdb.com/title/tt0060196/

 

Posted in Uncategorized | Leave a comment

11g slow drop tablespace including contents and…

Slow 11g drop tablespace including contents and datafiles.

Cause: Slow select with sql_id 7jrp85khm4tka:

select from ... INDCOMPART$, I_INDSUBPART_POBJSUBPART$, INDSUBPART$...

Solution (looks like it helped):

exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_schema_stats('SYS');
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush shared_pool;

Solution description:

get rid of nested loops on tables mentioned above (INDCOMPART$,…)

Relevant Metalink notes:

Drop Tablespace Including Contents And Datafiles Takes Hours To Complete (Doc ID 438461.1)

DBMS_METADATA.GET_DDL: Query Against SYS.KU$_INDEX_VIEW Is Slow In 11.2.0.3 (Doc ID 1459841.1)

Posted in Uncategorized | Leave a comment

Positive on 11g : v$active_session_history column TEMP_SPACE_ALLOCATED

Tired of analysing “ORA-1652: unable to extend temp segment by 128 in tablespace TEMP” ?

The very last column of v$active_session_history is TEMP_SPACE_ALLOCATED in 11g.

dba_hist_active_sess_history has it too.

 

 

Posted in Uncategorized | Leave a comment

Upgrade to 11g: 11g raises error but Oracle 10g not for ambiguous ANSI joins

Beware:

Metalink note: Bug 5368296  ANSI join SQL may not report ORA-918 for ambiguous column

Long story short: Oracle 10g does not raise error for ambiguous columns missing aliases. Oracle 11g catches the ambiguity meaning that for good or for bad your application won’t work in 11g.

 

Posted in Uncategorized | Leave a comment