Re: Question about partitioning and ORA-01460 in parallel server in 11gr2

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Thu, 25 Apr 2013 09:33:50 +0200
Message-ID: <CAJu8R6iCN3fKcUuWJyHeeTQV4Dsuy0uM2QrFeyFxJ2Rv5TSgnw_at_mail.gmail.com>



Bonjour Dominique,
 *“Do you think the error and the "non multiple of 2" number of 6 partitions and these messages could be related?”*

 I don’t think that “your” errors have any relation to the number of partitions you have implemented. It is the number of *HASH* partitions (during the table creation) that has to be a power of 2 (2, 4, 8, 16, etc...). This 2^N desired number of partition is to ensure uniform distribution of data across all hash partitions. Remember that it might be that during the lifecycle of your application, you will need to split a partition, add a new one, exchange partition, etc... . In this case a 2^Nnumber of hash partition will help Oracle spreading data uniformly across
the existing partition

*“ORA-01460 unimplemented or unreasonable conversion requested”*

Again I think that neither parallel query nor number of partition is related to this error. We have been confronted to this error very recently (Oracle 10.2.0.4.0) to this error when using normal heap tables when selecting from these table to generate a PDF file we will send to a SharePoint Location. A web service which executes a stored procedure reported this error. However, when their same web service is restarted a second time, the error is not raised!!!

The sole resemblance between your case and our seems to be in the use of BLOB in a separate tablespace. When I tried to investigate this random error, I succeed to reproduce this error using an example I found on web

drop table t;

create table t(id number);

insert into t values(1);

commit;

SQL> declare

  2 id number;

  3 sResultset varchar2(4000);

  4 begin

  5 sResultset := lpad('a',3995,'a');

  6 sResultset := sResultset || ';1;';

  7 SELECT ID INTO ID FROM t WHERE INSTR(sResultset,';'||ID||';') != 0;

  8 end;

  9 /

PL/SQL procedure successfully completed.

But when I try to manipulate string of more than 4000 characters I got the ORA-01460
error

SQL> declare

  2 id number;

  3 sResultset varchar2(5000);

  4 begin

  5 sResultset := lpad('a',3998,'a');

  6 sResultset := sResultset || ';1;';

  7 SELECT ID INTO ID FROM t WHERE INSTR(sResultset,';'||ID||';') != 0;

  8 end;

  9 /

declare

*

ERROR at line 1:

ORA-01460: unimplemented or unreasonable conversion requested

ORA-06512: at line 7

INSTR function can’t be applied to string of more than 4000 characters.

It is around this BLOB that it, believe, you have to concentrate your troubleshooting efforts

Bonne chance et une bonne journée

Mohamed Houri

www.hourim.wordpress.com

2013/4/24 Labbens, Dominique <dominique.labbens_at_capgemini.com>

> I greet everyone - I'm a new French subscriber - One of your members
> suggested me to post you the following issue :
> After a migration from 10g to 11g, we use a new hash/range partitioning
> for a big table for load-balancing (with another table with BLOBs in
> separate tablespaces with reference partitioning to the first one) and I
> chose a number of 6 for the hash part of the partitioning key and 4 for the
> range part to limit the number of partitions to 6x4, for maintenance
> consideration, and regarding to the CPU number. It caused obviously
> different partitions sizes, but I did not see any error in copying my
> existent records, all copied in the new partitioned table, neither using
> the application. We use default parallelism for both tables.
>
> We now see unpredictable errors ORA-12801 and ORA-01460 ("parallel query"
> and "unimplemented data conversion") after several weeks in production, not
> having seen it before with the "black box" Software package we use, because
> it did not detect the error (Oracle no more maintain Lodestar).
>
> I am currently looking to Bug 13099577 : ORA-1460 WHEN PARALLEL QUERY
> SERVERS ARE USED - which looks like being the same problem but does not
> completely corresponds because we use 11.2.0.2 and documentation describes
> upgrade in 11.2.0.3, and describes a complete loss of service, which is not
> our case. I did not find corresponding trc files with this error.
>
> The error "disappear" when we turn off parallelism on the main table, or
> restart the DB, but does not "reappear" when we turn it on again. We did
> not identify what causes the error appearance. I hesitate to simply turn of
> parallelism for performance risks. But it may be the simpliest way to avoid
> the error.
>
> The other tail (lead ?) should be NLS_NCHAR_CHARACTER set to UTF8, which
> was not the case before migration, but our application schemas do not use
> NCHAR or NCLOB columns.
>
> I see now in Oracle documentation ("Partitioning in Data Warehouse") that
> the number of partitions "should be" a multiple of 2 (and not must be) what
> I had not seen in other documentations. No oracle error occurred while
> creating the tables and copying the data.
>
> Do you think the error and the "non multiple of 2" number of 6 partitions
> and these messages could be related ?
> Does the number of partitions "should be" or "must be" a multiple of 2 ?
>
> Does anyone have an experience of this ORA-12801 / ORA-01460 error and
> 13099577 patch ?
>
> Thank you for your help, if you can. It is a real urgent production bug
> and I am very worried about it because we did not see it earlier, and have
> to fix it quickly.
>
> I am in France and it is late at this time, but will have a look at my
> mail this evening and tomorrow morning.
>
> Best regards
>
> Dominique Labbens
>
> oerr ora 12801
> 12801, 00000, "error signaled in parallel query server %s"
> // *Cause: A parallel query server reached an exception condition.
> // *Action: Check the following error message for the cause, and consult
> // your error manual for the appropriate action.
>
> and
>
> ORA-01460 unimplemented or unreasonable conversion requested
> Cause: The requested format conversion is not supported.
> Action: Remove the requested conversion from the SQL statement. Check the
> syntax for the TO_CHAR, TO_DATE, and TO_NUMBER functions to see which
> conversions are supported.
>
>
>
>
> _______________________________________________________________________
> Dominique LABBENS - Architecte
>
> Capgemini Industrie & Distribution / EUC / IDS
> Tel.: +33 1 78 66 60 24 – Mob.: + 33 6 22 66 85 79
> www.capgemini.com<http://www.capgemini.com/>
>
> People matter, results count.
> _______________________________________________________________________
> This message contains information that may be privileged or confidential
> and is the property of the Capgemini Group. It is intended only for the
> person to whom it is addressed. If you are not the intended recipient, you
> are not authorized to read, print, retain, copy, disseminate, distribute,
> or use this message or any part thereof. If you receive this message in
> error, please notify the sender immediately and delete all copies of this
> message.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 25 2013 - 09:33:50 CEST

Original text of this message