The essence of the problem is that any operations with a database are performed for a very long time, for example:. insert into tab1 (time) values ​​sysdate Run 40-60 ms. And when writing to the database> 1000 values, we get a very long recording. Oracle oracle 11gR2 OS Linux on CentOs is a similar problem. Please help me figure it out.

It turns out that unloading from the database, etc., is performed almost instantly, the problem is only with INSERT (writing to the database)

Everything that the native OCI driver uses (sqlplus, dbForge) works fine, and everything else, SQL Net, Thin JDBC (SqlDev, TOAD) slows down, and only on DML, and from the DB side, the execution time looks normal.

internal class Program { private static void Main() { var stopwatch = Stopwatch.StartNew(); // DateTime dt = new DateTime(); try { const string oradb = "Data Source = 192.168.137.70/xe; " + "User id = useridb; Password = user1234"; #pragma warning disable 618 var con = new OracleConnection(oradb); #pragma warning restore 618 con.Open(); #pragma warning disable 618 var cmd = new OracleCommand {Connection = con}; #pragma warning restore 618 for (var i = 0; i < 1000; i++) { cmd.CommandText = "Insert into test (datetime) values (sysdate)"; //cmd.CommandText = "Select max(datetime) from test"; var dr = cmd.ExecuteReader(); dr.Read(); // dt = Convert.ToDateTime(dr.GetValue(0)); dr.Close(); // Thread.Sleep(50); } stopwatch.Stop(); var averageTime = new TimeSpan(stopwatch.ElapsedMilliseconds / 1000); Console.WriteLine("Запись успешна {0}, {1}", averageTime, dt); } catch (Exception e) { Console.WriteLine("Error{0}", e.Message); } Console.ReadKey(); } }
  • And what about the OS with RAM, loading? Is there a lot of hard space? Are there any triggers hung on that table - gil9red
  • @ gil9red 64 gigabyte operative hard drives 2 pieces in a raid array of 1 TB each. More than free space, bd is new and does not contain much information yet, there are triggers, but on other charts and tables there is the same problem - Ethernets
  • Drives more than anything edakami not loaded? What kind of raid? I hope orakl has direct access to hardware, there are no virtualization systems? Where are the redo logs and flash_recovery_area ... - Mike
  • @Mike disks are no longer loaded, raid type 1, OS and DB have direct access to hardware, redo - sorry, I don’t know where to look, flash_recovery_area is in the folder where the folder with the base is, example / app / flash_recovery_area and DB / app / oracle - Ethernets
  • one
    @ 0xdb Thank you for your interest. But so far nothing, your answer has not yet been able to test, I think tomorrow I will get there (I was on a business trip, I apologize for the delay). I tried to build a plan, so there is nothing superfluous there, and in terms of the query was executed in 1ms. As said above, I will try the solution 0xdb tomorrow. The results will immediately report. Thank you - Ethernets

2 answers 2

On that there is an instruction EXPLAIN PLAN , type:

  EXPLAIN PLAN FOR INSERT INTO TABLE values(10)

will give you what happens when you insert a record.

How to read the output and what to do is described in detail in the documentation

roughly speaking, you will see where the bottleneck is - whether it is formed by a VIEW or a trigger or insertion condition or something else.

    You must enable SQL tracing. In contrast to EXPLAIN PLAN , it will show “what has already been” and not “what else will be”. If it does not lead to a solution to the problem immediately, it will give a direction where to look further.

    First you need to check if TIMED_STATISTICS is TIMED_STATISTICS (enabled by default) and where trace files are written (usually $ORA_BASE/diag/rdbms/orasid/orasid/trace ):

     select name, value from v$parameter where name in ('timed_statistics','user_dump_dest');

    Select candidates, for example, sessions on remote SQL Developer and SQL * Plus locally on the database server:

     select s.username, s.status, s.sid, s.serial#, sql_trace, s.program, p.spid from v$session s join v$process p on s.paddr = p.addr where s.username = 'SH1'; USERNAME STATUS SID SERIAL# SQL_TRAC PROGRAM SPID ------------- -------- ---------- ---------- -------- ------------------------------ ------ SH1 ACTIVE 139 41 DISABLED SQL Developer 26596 SH1 INACTIVE 15 92 DISABLED sqlplus@mpsrv (TNS V1-V3) 25930

    Enable tracing by user with DBA privileges:

     exec sys.dbms_monitor.session_trace_enable(session_id=>139, serial_num=>41, waits=>true, binds=>false); exec sys.dbms_monitor.session_trace_enable(session_id=>15, serial_num=>92, waits=>true, binds=>false);

    In selected customers we make the request (s):

     insert into tab1 (time) values (sysdate); commit;

    And turn off tracing:

     exec sys.dbms_monitor.session_trace_disable(session_id=>139, serial_num=>41); exec sys.dbms_monitor.session_trace_disable(session_id=>15, serial_num=>92);

    Find the trace files in the user_dump_dest folder (see above) and bring it to a readable view with tkprof :

      $ ls -1t *26596.trc *25930.trc myoradb_ora_25930.trc myoradb_ora_25507.trc tkprof myoradb_ora_25930.trc sqlp_25930.prf explain=sh1/sh1 tkprof myoradb_ora_26596.trc sqld_26596.prf explain=sh1/sh1

    The output of tkprof will look like this:

     ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** SQL ID: f281fbsngrwq0 Plan Hash: 0 insert into tab1 (time) values (sysdate) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 1 5 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 1 5 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 151 (SH1) Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS 0 LOAD TABLE CONVENTIONAL OF 'TAB1' Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00 ********************************************************************************

    For comparison, in SQL Developer noticeably significant waiting for a response from the client, since Not a high-speed connection to the server. And besides, the SQL ID is different, i.e. oracle did not recognize the request as identical:

     ******************************************************************************** SQL ID: gck3089qg8n7x Plan Hash: 0 insert into tab1 (time) values (sysdate) ... Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ Disk file operations I/O 1 0.00 0.00 log file sync 1 0.01 0.01 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 4.07 4.07

    Read more about other features and reading the formatted output of tkprof in the documentation .

    I note: it makes sense to trace in the configuration of a dedicated server, in the case of distributed servers it is a very time-consuming task to collect a trace, since even one request can be parsed in one server, and executed in another.