Coskan’s Approach to Oracle

March 23, 2009

SQL*Plus Bug or My own bug :)

Filed under: Bugs — coskan @ 5:00 pm

update:(5 hours later after Nicolas Gasparotto’s comment) the post below is a good explanation for invalid dba exception I got for my own actions. You need to read the post together with first two comments.

I have 2 excuses for my dodgy conclusion below:)
1- I am on diet and halved the calorie intake, so my brain is not fed enough.
2- The way it gives the same result on every different/client machine combination lead me wrong idea and to be honest, I wasnt focusing on the content of trigger because of a successful case.

At the end of the day I am %100 guilty because I act like a developer and blamed ORacle at first place:)

Moral of the story for me ; check before publishing, especially your NLS settings

I think I found a bug which is available for the sqlplus clients above 10.2.0.1 .

What I was doing was an insert on a table. Table had a trigger which inserts a non-default value for a column that wasn’t in the insert list.
Insert statement wasn’t working on my 11.1.0.6 client but works fine on TOAD or SQLPLUSW exe.

This is the test case I prepared for this issue; (exactly the same trigger on our production database)

Table is on 10.2.0.3 database (DB version doesnt matter the problem is client)

SQL> drop table test_c purge;

Table dropped.

SQL> create table test_c (test_count number,tr_date date);

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TST
2 BEFORE INSERT OR UPDATE ON TEST_c
3 FOR EACH ROW
4 WHEN (
5 new.TR_DATE is NULL
6 )
7 BEGIN
8 :new.TR_DATE := '31-DEC-2049';
9 :new.TEST_COUNT := :new.TEST_COUNT + 1;
10 END;
11 /

Trigger created.

This is the behaviour for the insert on different client versions for sqlplus.exe (Windows) and sqlplus (HP-UX)

On 10.2.0.1 client the tests case works.


SQL> insert into test_c (TEST_COUNT) values (0);

1 row created.

On 10.2.0.2 client test case fails.
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

On 10.2.0.4
windows

SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

Unix

On 11.1.0.6
Windows
SQL> insert into test_c (TEST_COUNT) values (0);
insert into test_c (TEST_COUNT) values (0)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "EYDBA.TRG_TST", line 2
ORA-04088: error during execution of trigger 'EYDBA.TRG_TST'

I don’t have a unix 11G client, but I think it will fail on it as well.

I think its time to raise an SR to Oracle Support.

Query Result Cache on Encrypted Tablespace on 11G

Filed under: Performance — coskan @ 10:45 am

Question: Will query result cache work on Encrypted tablespaces.

I asked this question to Tom Kyte after his All About Encryption presentation at UKOUG 2008. His answer was ” I think it will work but you need to try”

After 4 months, here is the answer to my question;

 
SQL> ---Encrypted tablespace creation
SQL> CREATE TABLESPACE encryptedtst
  2  DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\ORACOS\encryptedtst_01.dbf' SIZE 128K
  3  AUTOEXTEND ON NEXT 64K
  4  ENCRYPTION USING 'AES256'
  5  DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> connect hr/hr;
Connected.

SQL> alter table employees enable row movement;

Table altered.

SQL> alter table employees
  2  move tablespace encryptedtst;

Table altered.

SQL> select manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    18 |   144 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> select /*+ result_cache*/manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL> select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id;

MANAGER_ID SUM(SALARY)
---------- -----------
       100      155400
                 27000
       123       25900
       120       22100
	..	...

19 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1u45jnmw74kd60brbww9gr7ss8 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    18 |   144 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   856 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id"

SQL>


SQL>--Check if it is really cached
SQL> select name from v$result_cache_objects;

NAME
-----------------------------------------------------------------------------------------------
HR.EMPLOYEES
select /*+ result_cache*/ manager_id,sum(salary) from employees group by manager_id


As you see on the explain plan and v$result_cache_table, the result is cached

Conclusion is; Oracle did not forget to combine two new features together by the help of keeping objects of encrypted tablespaces, unencrypted in SGA for better performance

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers