Oracle Database Discussions

This is a spin off from another thread which started off as slow inserts. But what actaully happens is every insert is preceded by select it turned out that the select was slow.

We have a multi-tier web application conencting to the DB using connection pool and inserting about a 100000 records in a table. To isolate the issue I wrote a PL/SQL which does the same thing.

This problem happens every time the schema is recreated or the table dropped and created again and we start inserting. When the table is empty, the selects choose a full table scan but as the records are inserted it continues to use the same even though after a few thousands of rows I run stats. But as its running if gather stats and flush the shared pool, it picks up the new plan using the indexes and immediately gets faster.

But in either case, full tablescan being slow after a few thousands of rows or using the index and getting much faster. Or me just doing the same select and no inserts on a table with 100000 rows, the CPU seems to be pegged to the core.

The code snipped repeated again

DECLARE
   uname    NVARCHAR2 (60);
   primid   NVARCHAR2 (60);
   num      NUMBER;
BEGIN
   FOR i IN 1 .. 100000
   LOOP
      uname := DBMS_RANDOM.STRING ('x', 20);
      primid := DBMS_RANDOM.STRING ('x', 30);
      DBMS_OUTPUT.put_line (uname || ' ==> ' || primid);

      SELECT   COUNT (*)
          INTO num
          FROM TEST
         WHERE ID = 0
           AND (primid = primid OR UPPER (username) = uname OR uiname = uname
               )
           AND (deldate IS NULL)    
      ORDER BY TIME DESC;

      INSERT INTO TEST
           VALUES (0, uname, uname, 1, uname, primid);

      IF MOD (i, 200) = 0
      THEN
         COMMIT;
         DBMS_OUTPUT.put_line ('Commited');
      END IF;
   END LOOP;
END;

This is the original thread
2254958