Oracle analytical functions to the rescue

 Below is a typical example of how using analytical functions speeds up a query, which is having a subquery inside.

Original query:

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM TEST1, TEST2, TEST3
                        WHERE TEST1.CUSTOMER_ID = 517091995
                        AND TEST1.UNIT_ESN = ESN
                        AND TEST1.ESN_SEQ_NO =
                        (SELECT MAX (ESN_SEQ_NO)
                        FROM TEST1 PD1
                        WHERE PD1.CUSTOMER_ID = TEST1.CUSTOMER_ID
                        AND PD1.SUBSCRIBER_NO = TEST1.SUBSCRIBER_NO
                        AND LENGTH(UNIT_ESN) <> 20 )
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE          )

;


The new query, using analytical functions (much faster and accesing the TEST1 table only once):

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
                        ALT_MODEL_DESC
                        FROM
(select UNIT_ESN,ESN_SEQ_NO,CUSTOMER_ID, SUBSCRIBER_NO, max(ESN_SEQ_NO) over (partition by CUSTOMER_ID,SUBSCRIBER_NO)  as max_ESN_SEQ_NO
from TEST1 where LENGTH(UNIT_ESN) <> 20 ) phd, TEST2, TEST3
                        WHERE phd.CUSTOMER_ID = 517091995
                        and ESN_SEQ_NO=phd.max_ESN_SEQ_NO
                        AND UNIT_ESN = ESN
                        AND TEST2.TEST3 = TEST3.TEST3
                        AND ( TEST3.EXPIRATION_DATE  IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE)
;

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I was getting this error while running the RMAN duplicate database in 11g. As I was duplicating only selected tablespaces over, oracle internally was trying to drop the unnecesary tablespaces and the whole duplicate process was failing with as below:

 sql statement: drop tablespace  "TEST1" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 13:06:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/26/2013 13:06:34
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace  "TEST1" including contents cascade constraints
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

RMAN> **end-of-file**


I was trying manually to drop the tablespace and I was getting the same error, but no queue tables whatsoever reside in this tablespace:

SQL> drop tablespace  "TEST1" including contents cascade constraints;
drop tablespace  "TEST1" including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables



SQL> select * from dba_queue_tables where table_name in
         (select segment_name from dba_segments where tablespace_name='TEST1';

no rows selected

 At this point, my goal was to find out which statement is failing exactly.

 Solution:

SQL> ALTER system SET EVENTS='24005 TRACE NAME ERRORSTACK FOREVER, LEVEL 1';

System altered.


I then checked the alert log file for ORA-24005 and then inside the trace file I've found the exact statement failing:


ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
----- Current SQL Statement for this session (sql_id=0000000000000) -----
drop table "florin"."DDD#DEF$_AQERROR" cascade constraints purge

 In conclusion, oracle treats a table name with special characters as a queue table.
 I was able to drop this table, using DBMS_AQADM.DROP_QUEUE_TABLE, indeed, and then I've dropped the tablespace without any issue.

How to find out fast the partitioning keys in tables or indexes?

The key in this question is the word "fast"; there are many ways to find the partitioning key for a table or an index: using dbms_metadata.get_ddl, export/import using indexfile, ussing expdp and impdp, but the fastest way is to query the views below:

DBA_PART_KEY_COLUMNS
DBA_SUBPART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS

They provide info for subpartitions also.

Example:

SQL> select * from DBA_PART_KEY_COLUMNS

  2  where owner='CUSTO'

  3   and NAME='TRB1_AUDIT_SUB_LOG'

  4  order by COLUMN_POSITION;

OWNER                          NAME                           OBJEC COLUMN_NAME                    COLUMN_POSITION

------------------------------ ------------------------------ ----- ------------------------------ ---------------

CUSTO                       MY_AUDIT_LOG             TABLE PERIOD_KEY                                   1

CUSTO                       MY_AUDIT_LOG             TABLE SOURCE_MEMBER_ID                             2

CUSTO                       MY_AUDIT_LOG             TABLE SUB_APPL_ID                                  3

CUSTO                       MY_AUDIT_LOG             TABLE PROCESS_TYPE_IND                             4

Oracle Optimizer and the evergreen need for hints

The oracle optimizer is a software, trying to generate the best execution plan for a query, with the information it has. So in theory, if we provide it the most info we can, it should always be able to generate the best execution plan. But does it? Not always.
Even if we provide the optimizer fresh statistics of all the tables involved, there is still a risk that the optimizer
will take poor decisions, since it does not know how many rows will be returned by a join and it has to estimate the
result. Sometimes, wrongly estimating that a 2 table join will return a very small amount of rows, and this result being
used as the driving table in a Nested Loop join, can seriously hinder performance.

 The solution in this particular case is to use the use_hash hint, to avoid the NL altogether.

GATHER_PLAN_STATISTICS hint and dbms_xplan

In order to get detailed information about how many rows are being returned in each step of the execution plan, the hint GATHER_PLAN_STATISTICS comes in very handy, see the simple example below:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'FRANK';

 You actually have to run the above query, to be able to see that run stats.


SET LINE 200 pages 80

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                |         |      1 |        |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMP     |      1 |      1 |      1 |00:00:00.01 |       6 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

The output above shows the estimated rows returned (E-Rows), the actual rows returned (A-Rows) and the actual time (A-Time) spent during each step of the execution.

tar: Error exit delayed from previous errors

Another Linux related post: I was trying to tar up the oracle software and I was getting a strange message at the end of the tar output:


>tar -cvf 11.2.0.tar 11.2.0

11.2.0/
11.2.0/timingframework/
11.2.0/timingframework/TimingFramework.jar
11.2.0/timingframework/README
11.2.0/OPatch/
11.2.0/OPatch/opatchprereqs/
11.2.0/OPatch/opatchprereqs/prerequisite.properties
11.2.0/OPatch/opatchprereqs/opatch/
11.2.0/OPatch/opatchprereqs/opatch/runtime_prereq.xml

-----

-----

tar: Error exit delayed from previous errors

The challenge here is to find out the root cause of this general error message.

Solution:

Run the tar command again, while redirecting the error output to a log file:

tar -cvf 11.2.0.tar 11.2.0  2> tar.log

In my case, the content of the log file solved the "mystery":

tar: 11.2.0/bin/nmhs: Cannot open: Permission denied

tar: 11.2.0/bin/nmb: Cannot open: Permission denied

tar: 11.2.0/bin/nmo: Cannot open: Permission denied

tar: Error exit delayed from previous errors

Loading an Excel file using sqlldr and "corrupted" French characters

The issue: using as input file an Excel file, 1.csv , we load it using sqlldr utility to the database and when querying the newly inserted data, the French characters seem corrupted.

The control file used is as follows:

LOAD DATA
CHARACTERSET UTF8  ---------> solution provided by the developer :-)
infile '1.csv'
APPEND
INTO TABLE  my_table
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(empno,alert_end_Date date "mm/dd/yyyy",alert_text char(500000),alert_title,emp_no)

Solution: use CHARACTERSET WE8ISO8859P1 inside the control file, or just remove the characterset entry.

LOAD DATA

CHARACTERSET WE8ISO8859P1  ---------> this has to be the same as nls_lang of the DB , see note below

infile '1.csv'

APPEND

INTO TABLE  my_table

fields terminated by "," optionally enclosed by '"'

TRAILING NULLCOLS

(empno,alert_end_Date date "mm/dd/yyyy",alert_text char(500000),alert_title,emp_no)

 Note : to find out the NLS LANG of the DB:

 select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',

'NLS_LANGUAGE', 'LANGUAGE',

'NLS_TERRITORY', 'TERRITORY') name,

value from v$nls_parameters

WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')

/

NAME VALUE

————- —————–

LANGUAGE AMERICAN

TERRITORY AMERICA

CHARACTER SET WE8ISO8859P1