ORA-28511: lost RPC connection to heterogeneous remote agent

A client has been seeing this error when holding sessions open over HS for lengthy periods of time (greater than an hour or so).

Some workarounds that have worked are:

  • when possible, issue, alter session close database link, and re-open it for subsequent queries

 

  • Set INBOUND_CONNECT_TIMEOUT_<listener_name>=0 in listener.ora
  • Set SQLNET.INBOUND_CONNECT_TIMEOUT=0 in sqlnet.ora

bitmap indexes… better in new versions

“Back in the day”, bitmap indexes on tables meant any kind of DML was going kill you either for the concurrency, or the performance.

The concurrency issue still applies, but with each version of Oracle, the cost of maintaining bitmaps is still significant but might still be acceptable for you

We can see some simple results as below

SQL> create table my_tab
2  ( ticket_seq  number(10),
3     c1        number(10),
4     c2        number(10),
5     c3        number(10),
6     c4        number(10)
7  ) tablespace users;
Table created.
SQL> insert into my_tab
2  select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3  from dual
4  connect by level <= 50000;
50000 rows created.
SQL> create bitmap index IX1 on my_tab ( c1) tablespace users;
Index created.
SQL> create bitmap index IX2 on my_tab ( c2) tablespace users;
Index created.
SQL> create bitmap index IX3 on my_tab ( c3) tablespace users;
Index created.
SQL> create bitmap index IX4 on my_tab ( c4) tablespace users;

So we have 4 bitmap indexes, and we’ll see what how long it takes to do 500,000 inserts…

SQL> set timing on
SQL> insert into my_tab
2  select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3  from dual
4  connect by level <= 500000;
500000 rows created.
Elapsed: 00:00:07.45
SQL> commit;
Commit complete.
SQL> select segment_name, bytes
2  from dba_segments
3  where segment_name in ( 'IX1','IX2','IX3','IX4');
SEGMENT_NAME                                  BYTES
---------------------------------------- ----------
IX4                                         4194304
IX3                                         4194304
IX2                                         4194304
IX1                                         4194304

7 seconds for 500,000 rows isn’t too bad really…and the indexes have grown to 4 meg. Now we repeat the test in the more conventional sense, namely, set the indexes to unusable and then rebuild afterwards.

SQL> drop table my_tab purge;
Table dropped.
SQL> create table my_tab
2  ( ticket_seq  number(10),
3     c1        number(10),
4     c2        number(10),
5     c3        number(10),
6     c4        number(10)
7  ) tablespace users;
Table created.
SQL> insert into my_tab
2  select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3  from dual
4  connect by level <= 50000;
50000 rows created.
SQL> create bitmap index IX1 on my_tab ( c1) tablespace users;
Index created.
SQL> create bitmap index IX2 on my_tab ( c2) tablespace users;
Index created.
SQL> create bitmap index IX3 on my_tab ( c3) tablespace users;
Index created.
SQL> create bitmap index IX4 on my_tab ( c4) tablespace users;
Index created.
SQL> alter index ix1 unusable;
Index altered.
SQL> alter index ix2 unusable;
Index altered.
SQL> alter index ix3 unusable;
Index altered.
SQL> alter index ix4 unusable;
Index altered.
SQL>   insert into my_tab
2    select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3    from dual
4    connect by level <= 500000;
500000 rows created.
Elapsed: 00:00:02.13

As expected, the population is much faster, but the indexes need to be rebuilt and are unavailable for queries. You may be better off leaving them there, you may not be… your mileage may vary.

SQL>   commit;
Commit complete.
SQL> alter index ix1 rebuild;
Index altered.
Elapsed: 00:00:00.22
SQL> alter index ix2 rebuild;
Index altered.
Elapsed: 00:00:00.19
SQL> alter index ix3 rebuild;
Index altered.
Elapsed: 00:00:00.20
SQL> alter index ix4 rebuild;
Index altered.
Elapsed: 00:00:00.19
SQL> select segment_name, bytes
2  from dba_segments
3  where segment_name in ( 'IX1','IX2','IX3','IX4');
SEGMENT_NAME                                  BYTES
---------------------------------------- ----------
IX4                                         3145728
IX3                                         3145728
IX2                                         3145728
IX1                                         3145728

But notice that the indexes get nicely recompacted on rebuild, so even if you need to leave indexes on during some DML, either adhoc or regular, its worth scheduling a rebuild at some stage.

dbms_hs_passthrough–the magic package

This is a package that you can call, that does not exist in the database.

I found this out after reading about its usage, and then running a DESCRIBE, and of course, finding nothing. Then I hunted through the familiar $ORACLE_HOME/rdbms/admin directory, looking for it….alas to no avail.

Why is it not there ? Because its fiction. Its essentially a mean of doing the equivalent of DBMS_SQL but on a remote database. For example, if HS is going via ODBC to SQL Server, then just about every ODBC driver supports the concept of parse, execute, fetch, and thus DBMS_HS_PASSTHROUGH lets you access the level on the remote database.

Hence in the example below, the database link REMOTEDB is a HS link to a SQL Server database. Obviously the DBMS_HS_PASSTHROUGH package does not exist in that database, but the code works nonetheless !

set serverout on
DECLARE
  l_cursor    BINARY_INTEGER;
  l_id VARCHAR2(60);
  l_temp      VARCHAR2(250);
  l_notes     VARCHAR2(32767);
BEGIN
  l_cursor := DBMS_HS_PASSTHROUGH.open_cursor@remotedb;
  DBMS_HS_PASSTHROUGH.parse@remotedb(
  l_cursor,
  'select "RecId","Notes" from "MySqlServerTable"'
);

LOOP
  DBMS_HS_PASSTHROUGH.get_value@remotedb(l_cursor, 1, l_id);
  DBMS_HS_PASSTHROUGH.get_value@remotedb(l_cursor, 2, l_notes);
  DBMS_OUTPUT.put_line(l_id || ' ' || l_notes);
END LOOP;

exception
  when others then
  DBMS_HS_PASSTHROUGH.close_cursor@remotedb(l_cursor);
  raise;
END;
/

Index size

I think this feature came around ages ago (10.2?) but I’m still always impressed by it every time I use it.

You can run EXPLAIN on a CREATE INDEX command

SQL> explain plan for create index IX on MY_TABLE ( bet_account_num );
Explained.
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |             |   213K|   626K|   833   (1)| 00:00:11 |
|   1 |  INDEX BUILD NON UNIQUE| IX          |       |       |            |          |
|   2 |   SORT CREATE INDEX    |             |   213K|   626K|            |          |
|   3 |    TABLE ACCESS FULL   | MY_TABLE    |   213K|   626K|   712   (2)| 00:00:09 |
--------------------------------------------------------------------------------------
Note
-----
- estimated index size: 5128K bytes

and you get a nice little estimate of how big your index will be…..Awesome!

Cute partition elimination trick

I have a large partitioned table called TXN and I want to find the most recent transaction recorded, where the transaction timestamp column (TXN_TIMESTAMP) is the partition key.  This table also happens to be sub-partitioned as well, but that’s not particular important for this example:

So we look at the execution plan and at first glance, it looks dire:


SQL> select max(TXN_TIMESTAMP) from TXN;

------------------------------------------------------------------------------------------------------ 
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
------------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT        |            |     1 |    15 |   625K  (2)| 02:05:03 |       |       | 
|   1 |  PARTITION RANGE ALL    |            |     1 |    15 |            |          |   142 |     1 | 
|   2 |   SORT AGGREGATE        |            |     1 |    15 |            |          |       |       | 
|   3 |    PARTITION LIST ALL   |            |   445M|  6368M|   625K  (2)| 02:05:03 |   KEY |   KEY | 
|   4 |     TABLE ACCESS FULL   |        TXN |   445M|  6368M|   625K  (2)| 02:05:03 |   KEY |   KEY | 
------------------------------------------------------------------------------------------------------


Looks like it’s going to scan the whole table, but check Pstart/Pstop.

It’s going to start with the last partition (142) and keep moving upward until it hits partition 1 – which of course, it never will, so stops after scanning just the last partition

(Clarification:  Obviously if the last partition is empty, then it will move onto the next-to-last partition and so forth).

Which is why it only takes…


SQL> select max(TXN_TIMESTAMP) from TXN;

MAX(TXN_TIMESTAMP) 
------------------------------- 
28-MAY-12 05.38.14.489075 AM

Elapsed: 00:00:01.11


…one second.

Nice.

Error when dropping user

Recently when trying to drop a user, I got an error that seemed unrelated to the act of dropping the user.



SQL> drop user demo cascade;

drop user demo cascade

*

ERROR at line 1:

ORA-29972: user does not have privilege to change/ create registration

It turns out, that this user account was being used via an ODP.NET application, which was using the new 11g feature – Change Notification. This is a system privilege, and (for an unknown reason) you need to revoke this privilege before you can drop the user.


SQL> revoke CHANGE NOTIFICATION from demo;

Revoke succeeded.

SQL> drop user demo cascade;

User dropped.

 

Mark Townsend

RIP.

A fine Oracle man.