(RADIATOR) might be a AuthPLSQL.pm issue

[Mishari Al-Faris]

Dear All,

We're using Radiator to query an Oracle DB for authentication, and using
AuthPLSQL.pm.

<AuthBy PLSQL>
        Identifier dslauthplsql
        NoDefault
        DBSource        dbi:Oracle:oracledb
        DBUsername      xxx
        DBAuth          xxx

        DefaultSimultaneousUse 1

        AuthBlock       BEGIN
aaa.is_active('%U',:passwd,:check_item,:reply_item,'%{NAS-Identifier}','%{NAS-Port}','%P',1,'%{NAS-Port-Type}','%{NAS-IP-Address}',:bandwidth,:static_ip,:framed_route);
END;
        AuthParamDef    :passwd,        User-Password,  check
        AuthParamDef    :check_item,    GENERIC,        check
        AuthParamDef    :reply_item,    Session-Timeout,reply
        AuthParamDef    :bandwidth,     GENERIC,        reply
        AuthParamDef    :static_ip,     Framed-IP-Address,reply
        AuthParamDef    :framed_route,  Framed-Route,reply
        SQLRecoveryFile %L/qualitynet/sqlfailures.%m.%d
</AuthBy>

We're noticing some weird delay in getting the response to the query, here's
some snipets from our trace 4 microsecond log.


Wed Mar 29 00:00:00 2006 147441: DEBUG: Packet dump:
*** Received from 213.189.95.4 port 1102 ....
Code:       Access-Request
Identifier: 9
Authentic:  4<12>A<178><218><212><242>}<174>H0<159><176>nTB
Attributes:
        NAS-IP-Address = 62.150.1.10
        NAS-Port = 167
        NAS-Port-Type = Async
        User-Name = "1400069509"
        Called-Station-Id = "811811"
        Calling-Station-Id = "5658640"
        User-Password =
"><132><145><128><238><207><173><225><229>W<195>c<224>\ <223>"
        Service-Type = Framed-User
        Framed-Protocol = PPP
        Framed-IP-Address = 62.150.181.89
        NAS-Identifier = "ASK10"
        Cisco-NAS-Port = "Async167"

Wed Mar 29 00:00:00 2006 148685: DEBUG: Handling request with Handler
'Called-Station-Id=811811'
Wed Mar 29 00:00:00 2006 149036: DEBUG: Rewrote user name to 1400069509
Wed Mar 29 00:00:00 2006 149307: DEBUG: Rewrote user name to 1400069509
Wed Mar 29 00:00:00 2006 149624: DEBUG: qualitynet-online Deleting session
for 1400069509, 62.150.1.10, 167
Wed Mar 29 00:00:00 2006 149973: DEBUG: do query is: 'delete from RADONLINE
where NASIDENTIFIER='62.150.1.10' and NASPORT=0167':

Wed Mar 29 00:00:00 2006 151092: DEBUG: Handling with Radius::AuthPLSQL
Wed Mar 29 00:00:00 2006 151385: DEBUG: Handling with Radius::AuthPLSQL:
dslauthplsql
Wed Mar 29 00:00:00 2006 151846: DEBUG: Query is: BEGIN aaa.is_active
('1400069509',:passwd,:check_item,:reply_item,'ASK10','167','xxxxxx',1,'Async','
62.150
.1.10',:bandwidth,:static_ip,:framed_route); END;

Wed Mar 29 00:00:00 2006 222949: DEBUG: Radius::AuthPLSQL looks for match
with 1400069509
Wed Mar 29 00:00:00 2006 223482: DEBUG: Query is: 'select NASIDENTIFIER,
NASPORT, ACCTSESSIONID, FRAMEDIPADDRESS from RADONLINE where
USERNAME='1400069509'':


Wed Mar 29 00:00:00 2006 224183: DEBUG: Radius::AuthPLSQL ACCEPT:
Wed Mar 29 00:00:00 2006 224540: DEBUG: Access accepted for 1400069509
Wed Mar 29 00:00:00 2006 233989: DEBUG: do query is: 'insert into
QUALITYNET_AUTH_LOG
values('1143579600','OK','1400069509','811811','5658640',NULL,'76491251
','ASK10')':

Wed Mar 29 00:00:00 2006 234980: DEBUG: Packet dump:
*** Sending to 213.189.95.4 port 1102 ....
Code:       Access-Accept
Identifier: 9
Authentic:  4<12>A<178><218><212><242>}<174>H0<159><176>nTB
Attributes:



As you can see, the time spent doing the Oracle query is about 70ms. Upon
inspection of the whole log, these times are almost hovering between
60-100ms. However, using tnsping we saw that the network delay is almost
always 0ms. Also, we tried connecting to the DB using the SQLPLUS interface
and executed the query from there (from the remore client not the DB
iteself) and we get times of no more than 4-6ms.

Does that suggest there's a problem in the DBD::Oracle or AuthPLSQL.pm? I'm
clueless there.

Thanks for any help
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20060410/fbf47001/attachment.html>