1 Reply Latest reply: Jan 21, 2013 6:49 AM by 943610 RSS

    Pro*C slow fetch compared to sqlplus

    943610
      It takes 36 seconds to retrieve the table contents with Pro*C, while with sqlplus it takes only 9 seconds.
      I'm connecting via a chain of ssh servers. The scp tool copies files at speed 10Mbit.
      How to make the fetch faster?
      time echo "SELECT COUNTRY_CODE || NETWORK_CODE,OPERATOR_ID FROM RAPOPERATOR ORDER BY  'x'||COUNTRY_CODE || NETWORK_CODE;" | sqlplus -L rap/rap@RAP
      COUNTRY_CODE||N OPERATOR_ID
      ...
      --------------- -----------
      ZWEN1                   293
      ZWEN3                   436
      4179                      1
      
      729 rows selected.
      
      SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options
      
      real    0m9.640s
      user    0m0.000s
      sys     0m0.015s
      char mccmnc[20+1];
      int oper;
      EXEC SQL DECLARE sel_perftest CURSOR FOR SELECT COUNTRY_CODE || NETWORK_CODE,OPERATOR_ID FROM RAPOPERATOR ORDER BY  'x'||COUNTRY_CODE || NETWORK_CODE;
      applog(PITS_INFO,"before open");
      EXEC SQL OPEN sel_perftest;
      applog(PITS_INFO,"after open");
      for(;;) {
        EXEC SQL FETCH sel_perftest into :mccmnc, :oper;
        if (sqlca.sqlcode == DB_NOTFOUND) break;
      }
      applog(PITS_INFO,"after fetch");
      
      13JAN21@08:22:06.092,before open
      13JAN21@08:22:06.191,after open
      13JAN21@08:22:42.890,after fetch
      Edited by: 940607 on 21.01.2013 0:37