I have a problem on performance in oracle database and I couldn't find the solution. Here is the problem; when number of records in the table increase, the memory consumption also increase and almost 168987 byte space memory out of 16GB. When I truncate the records from the table , the memory space returns. I wanna move all records to another table so that I will have temporary solution. Thanks for your collaboration.

  • If you truncate the table, you don't need the data so why bother storing it? – Philᵀᴹ Jun 22 '17 at 15:30

I fear this answer only answers your specific question and not your real problem (we would need more information as to why you have a performance problem).

In any case:

Create Table As Select (CTAS) is the fastest way to copy a table (especially as a quick band-aid to diagnose a deeper issue with the data):

create table mytable as select * from dba_tables;
  • I have a simulator that generates a log and everything is work perfectly at the beginning . I have oracle 11g r2 database that runs on the server and when the number of records that store into the storage db table increase the memory usage also increase and finally it stops inserting the record to the storage table. I suspect that may be all records fetched and dumped on the memory since the memory usage increase with number of records increase in the database table. – Taye.M Jun 22 '17 at 14:14
  • I tried to increase the shared memory, and database cache buffer size in the instance it does not changed. when I truncate the records from the table, the memory becomes free and the I restarted the instance with the presence of records in the database table and restart again the memory becomes free but after a few minutes there will be almost no free memory size. here is the sample screen shot from the top command: – Taye.M Jun 22 '17 at 14:14
  • KiB Mem: 16419056 total, 16260680 used, 158376 free, 14128 buffers KiB Swap: 16764924 total, 41272 used, 16723652 free. 14852772 cached Mem – Taye.M Jun 22 '17 at 14:18
  • @Taye.M I still see a lot of generics with very few specifics. The size of the table should not have any affect on SGA or PGA or computer RAM if you are just doing inserts (just tablespace size, so hard drive space should be affected, but that's it). We perform inserts and updates on a wide 100 million row table without issue, and many Oracle shops consider that a small table. So, I suspect it is "how" you are doing the inserts. – Kris Johnston Jun 22 '17 at 14:45
  • Here is code fragment how do I insert the records to the database table – Taye.M Jun 23 '17 at 7:15

You need to understand the fundamental difference between storage space and memory. Your table space may run out of free segments, and thus the table can't grow at a certain point. But not all of the table will be in memory at any given point. Here is an overly complex SQL script that will show all of your table spaces and data files and how much space is being used in each data file. You may need to allow the data file for the table space that you are using to grow beyond what it currently is set to. Hence memory is not the issue, free space in a table space is the issue.

SET TERM         OFF
SET ECHO         OFF
SET TERM         ON
CURSOR cur_ts IS
   SELECT tablespace_name,     file_id,    file_name name,
          ROUND(SUM(bytes)/1024/1024) totalMB,
          TO_CHAR(ROUND(SUM(bytes)/1024/1024), '999,999') totalMBV
     FROM dba_data_files
    GROUP BY tablespace_name,  file_id,    file_name
    ORDER BY 1, 3;

TYPE lineDetailRec IS RECORD
   ( HighWaterMarkMB  VARCHAR2(32),
     freeMB           VARCHAR2(32),
     largestFreeSpace VARCHAR2(32),
     pctUsed          VARCHAR2(32) );
     lineDetail lineDetailRec;
     lv_fs       NUMBER := 0;
     percent_fs  NUMBER := 0;
     large_fs    NUMBER := 0;
     blocksize   NUMBER := 0;
     lv_name     v$database.name%TYPE;
   SELECT value
     INTO blocksize
     FROM v$parameter
    WHERE name = 'db_block_size';

   SELECT name
     INTO lv_name
     FROM v$database;

       ' Database -- Tablespace Summary - Freespace Report ***************');
   DBMS_OUTPUT.PUT_LINE(LPAD('Total    Hwater    Free       LG    %Free', 125));
   DBMS_OUTPUT.PUT_LINE(' Tablespace name                '     ||
       RPAD('File name', 50)      ||
       ' SpaceMB   '              ||
       ' Mark   '                 ||
       ' SpaceMB  '               ||
       ' FreeMB '                 ||
       ' Space  ');

   DBMS_OUTPUT.PUT_LINE('****************************** '      ||
       LPAD( '*', 50, '*')        ||
       ' ******** '               ||
       ' ******* '                ||
       ' ******* '                ||
       ' ******* '                ||
       ' ******');

   FOR lv_cur_ts IN cur_ts
      SELECT TO_CHAR(hwm.HighWaterMarkMB, '999,999'),
             TO_CHAR(fs.freeMB,           '999,999'),
             TO_CHAR(fs.largestFreeSpace, '999,999'),
             TO_CHAR(NVL(ROUND( (freeMB/NVL(lv_cur_ts.totalMB, 1))*100, 1), 0), '9990.0')
        INTO lineDetail
        FROM ( SELECT ROUND(SUM(BYTES)/1024/1024) freeMB,
                      ROUND(MAX(bytes)/1024/1024) largestFreeSpace
                 FROM dba_free_space
                WHERE tablespace_name = lv_cur_ts.tablespace_name
                  AND file_id         = lv_cur_ts.file_id ) fs,
             ( SELECT NVL(MAX(block_id), 1)*blocksize/1024/1024 HighWaterMarkMB
                 FROM dba_extents
                WHERE tablespace_name = lv_cur_ts.tablespace_name
                  AND file_id         = lv_cur_ts.file_id ) hwm;

      DBMS_OUTPUT.PUT_LINE(RPAD(lv_cur_ts.tablespace_name,   31) ||
      RPAD(lv_cur_ts.name,              50) ||
      LPAD(lv_cur_ts.totalMBV,           9) ||
      LPAD(lineDetail.HighWaterMarkMB,   9) ||
      LPAD(lineDetail.freeMB,            9) ||
      LPAD(lineDetail.largestFreeSpace,  9) ||
      LPAD(lineDetail.pctUsed,           8));


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.