Re:about oracle performance improvement

26 Aug 2009


      --- org/sql_oracle.c
+++ new/sql_oracle.c
@@ -29,6 +29,25 @@
 #include <oci.h>
 #include "rlm_sql.h"
 
+#define MAXBIND		40
+/*
+ *  SQL WITH BIND = <SQL>\P<BIND1>\P<BIND2>.....\P<BINDn>
+ *  BIND1 = <NAME>=<VALUE>             ; the <NAME> is optional, if none, bindbyindex
+ *  
+ */
+typedef struct rlm_sql_oraclesockbind {
+	char * ptr ;
+	int	idx ;
+	char * name ; 
+	int  namelen ;
+	char * value ;
+	int vallen;
+
+	OCIBind *bindp ;
+} rlm_sql_oraclesockbind;
+
+
+
 typedef struct rlm_sql_oracle_sock {
 	OCIEnv		*env;
 	OCIError	*errHandle;
@@ -39,11 +58,110 @@
 	int		id;
 	int		in_use;
 	struct timeval	tv;
+	
+	rlm_sql_oraclesockbind binds[MAXBIND];
+	int bindcount; 
 } rlm_sql_oracle_sock;
 
 #define	MAX_DATASTR_LEN	64
 
+/*************************************************************************
+ *
+ *	Function: sql_bindparse
+ *
+ *	Purpose: split the sql statement for pure sql , each bind item , etc .
+ *    
+ *
+ *************************************************************************/
 
+static int sql_bindparse(SQLSOCK *sqlsocket, char * query)
+{
+	char * p = query ;
+	int count = 0 , i , k;
+	rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn ;
+	oracle_sock->bindcount = 0 ;
+	
+	while (*p) {
+		printf("DEBUG:%s\n",p);
+		switch (*p) {
+			case '\\' :
+				if (p[1]!='P') {
+					break;
+				}
+				*p = '\0'; 
+				oracle_sock->binds[count].ptr = p+2;
+				count ++ ;
+				p ++ ;
+				break;
+			//default 
+		} /* end of  switch(*p) */
+		p ++ ;
+		if (count>=MAXBIND)
+			break;
+	}
+
+	for(i=0, k = 0 ; k<count; k++){
+		if (oracle_sock->binds[k].ptr==NULL)
+			continue;
+		if (oracle_sock->binds[k].ptr[0]=='\0')
+			continue;
+		if ((p=strchr(oracle_sock->binds[k].ptr, '='))==NULL)
+			continue;
+		*p = '\0';
+		oracle_sock->binds[i].value = p+1;
+		oracle_sock->binds[i].vallen = strlen (oracle_sock->binds[i].value);
+		if (p==oracle_sock->binds[k].ptr){
+			oracle_sock->binds[i].name = NULL;
+		} else {
+			oracle_sock->binds[i].name = oracle_sock->binds[k].ptr;
+			oracle_sock->binds[i].namelen =  p - oracle_sock->binds[k].ptr;
+		}
+		oracle_sock->binds[i].idx = i+1 ;
+		radlog(L_DBG, "bind[%d] v_p is<%s>(%d)-<%s>(%d)", i,
+			oracle_sock->binds[i].name,oracle_sock->binds[i].namelen,
+			oracle_sock->binds[i].value,oracle_sock->binds[i].vallen );
+		i ++ ; continue;
+	}
+
+	oracle_sock->bindcount = i ;
+
+	return 0 ;
+}
+
+
+static int sql_ocibind(SQLSOCK *sqlsocket)
+{
+	int i ; 
+	rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn ;
+	for(i=0;i<oracle_sock->bindcount; i++){
+		if (oracle_sock->binds[i].name!=NULL){
+			OCIBindByName(
+				oracle_sock->queryHandle,
+				&oracle_sock->binds[i].bindp,
+				oracle_sock->errHandle,
+				oracle_sock->binds[i].name,
+				oracle_sock->binds[i].namelen,
+				(ub1 *) oracle_sock->binds[i].value,
+				oracle_sock->binds[i].vallen+1,
+				SQLT_STR, (dvoid *) 0,  (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, 
+				OCI_DEFAULT);
+		} else {
+			OCIBindByPos(
+				oracle_sock->queryHandle,
+				&oracle_sock->binds[i].bindp,
+				oracle_sock->errHandle,
+				oracle_sock->binds[i].idx,
+				(ub1 *) oracle_sock->binds[i].value,
+				oracle_sock->binds[i].vallen+1,
+				SQLT_STR, (dvoid *) 0,  (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, 
+				OCI_DEFAULT);
+		}
+	}
+
+	return 0;
+}
+
+
 /*************************************************************************
  *
  *	Function: sql_error
@@ -248,6 +366,8 @@
 		return SQL_DOWN;
 	}
 
+	sql_bindparse(sqlsocket, querystr);
+
 	if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 				querystr, strlen(querystr),
 				OCI_NTV_SYNTAX, OCI_DEFAULT))  {
@@ -255,6 +375,9 @@
 		return -1;
 	}
 
+	if (oracle_sock->bindcount>0)
+		sql_ocibind(sqlsocket);
+
 	x = OCIStmtExecute(oracle_sock->conn,
 				oracle_sock->queryHandle,
 				oracle_sock->errHandle,
@@ -306,6 +429,8 @@
 		return SQL_DOWN;
 	}
 
+	sql_bindparse(sqlsocket, querystr);
+
 	if (OCIStmtPrepare (oracle_sock->queryHandle, oracle_sock->errHandle,
 				querystr, strlen(querystr),
 				OCI_NTV_SYNTAX, OCI_DEFAULT))  {
@@ -313,6 +438,9 @@
 		return -1;
 	}
 
+	if (oracle_sock->bindcount>0)
+		sql_ocibind(sqlsocket);
+
 	/* Query only one row by default (for now) */
 	x = OCIStmtExecute(oracle_sock->conn,
 				oracle_sock->queryHandle,

============================================
diaup.conf
	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = :uname ORDER BY id\\P:uname=%{SQL-User-Name}"


accounting_update_query = "UPDATE ${acct_table1} \
    SET FramedIPAddress = :framedip, \
    AcctSessionTime = TO_NUMBER(:acctsesstime), \
    AcctInputOctets = TO_NUMBER(:acct_in_oct) + TO_NUMBER(:acct_in_octG) * 4294967296, \
    AcctOutputOctets = TO_NUMBER(:acct_out_oct) + TO_NUMBER(:acct_out_octG) * 4294967296 \
	WHERE \
    AcctSessionId = :acctsessid AND \
    UserName = :uname AND \
    NASIPAddress= :nasip AND \
    AcctStopTime IS NULL\
\\P:framedip=%{Framed-IP-Address}\
\\P:acctsesstime=%{Acct-Session-Time}\
\\P:acct_in_oct=%{Acct-Input-Octets}\\P:acct_in_octG=%{Acct-Input-Gigawords}\
\\P:acct_out_oct=%{Acct-Input-Octets}\\P:acct_out_octG=%{Acct-Input-Gigawords}\
\\P:acctsessid=%{Acct-Session-Id}\
\\P:uname=%{SQL-User-Name}\
\\P:nasip=%{NAS-IP-Address}"

    

Re:about oracle performance improvement

zhongwei feng