Oracle data by primary key resolution for slow problems _oracle

Problem Description:

Deletes a data based on the table primary key ID, which executes more than 5 seconds after a commit has been performed on the PL/SQL.!!!

Problem Analysis:

The requirement is to delete a primary table A, and another two tables have a foreign key with the primary key ID of this table. Deletes the data cascade of table A to delete the associated data for the other two tables. Additions and deletions to check using hibernate implementation.

Always thought it was hibernate. There is a slow deletion and updating of data due to an associated operation on the internal processing. So the original use of Hibernate saveorupdate method, to check the JDBC
SQL statements to handle update and delete data operations. But still no effect!!!
Suspected database Problem! ~

The SQL statement is then executed on the Pl/sql client to view the execution plan. Both deletes and updates are available to the index. But execution is still slow after the commit! Therefore, it is possible to determine the database aspects of the problem.

Any database to delete a data can not take more than 5 seconds time Ah! It's going to take a look at the execution of SQL!

A lot of information was searched on the internet. Finally, make sure to view the SQL execution trace file. SQL execution is a good session,oracle database to support sesion tracking, lock table, and so on. Consider to operate a raw

Production database. You cannot track sessions in large numbers. So you choose to track the way you specify sesion, only to see the SQL execution plan that you execute! In the following ways:

Alter session set events= ' 10046 Trace name context forever,level 12 '; ---fixed statement
Delete from t_table1 where id = 23242342; ---The SQL statement you want to track
Alter session set events= ' 10046 Trace name context off ';---fixed statement

SQL Trace gets a trace file:

To find a storage path through SQL:

Select Pr.value | | '\' || I.instance_name | | ' _ora_ ' | | To_char (ps.spid) 
| | '. TRC ' "Trace file name" from V$session S, v$process PS, V$parameter PR, v$instance i 
where s.paddr = Ps.addr and S.Si D = userenv (' Sid ') and pr.name = ' user_dump_dest ';

/home/oracle/dbsoftware/diag/rdbms/ora11g/ora11g/trace\ora11g_ora_42990.trc

Then remove the TRC file on the server.

Open Check to see:

/* Mv_refresh (DEL) */delete from "INMS31". Mv_band_port_rel_area "

And also:

4311/* Mv_refresh (DEL) */delete from "INMS31". Mv_band_port_rel_area "4402/*mv_refresh (INS) */insert/*+/into" INMS31 ". Mv_band_port_rel_area "(" ID "," account_id "," port_id "," Dev_ip "," Port_iden "," Area_name ") Select" PR "." ID "," PR "." account_id "," PR "." port_id "," D "." Dev_ip "," P "." Port_iden "," A "." Area_name "from" "Tb_band_user_port_rel" "PR", "Tb_port" "P", "Tb_device" "D", "Tb_area" "A" WHERE "PR". " port_id "=" P "." ID "and" P "." dev_id "=" D "." ID "and" D "." dev_main_area_id "=" A "." ID "5309/* Mv_refresh (DEL) */delete from" INMS31 ". Mv_band_ftth_rel_area "5482/* Mv_refresh (INS) */insert/*+/into" INMS31 ". Mv_band_ftth_rel_area "(" ID "," account_id "," onu_info_id "," Dev_ip "," Onu_desc "," Area_name ") Select" PRH ". ID "," PRH "." account_id "," PRH "." onu_info_id "," D "." Dev_ip "," O "." Onu_desc "," A "." Area_name "from" Tb_band_user_port_rel_ftth "PRH", "Tb_onu_info" "O", "Tb_device" "D", "Tb_area" "A" WHERE "PRH". onu_info_id "=" O "." ID "and" O "." olt_id "=" D "." ID "and" D "." dev_main_area_id "=" A "." ID "9984/* mv_refresH (DEL) */delete from "INMS31". Mv_band_port_rel_area "10061/* Mv_refresh (INS) */insert/*+/into" INMS31 ". Mv_band_port_rel_area "(" ID "," account_id "," port_id "," Dev_ip "," Port_iden "," Area_name ") Select" PR "." ID "," PR "." account_id "," PR "." port_id "," D "." Dev_ip "," P "." Port_iden "," A "." Area_name "from" "Tb_band_user_port_rel" "PR", "Tb_port" "P", "Tb_device" "D", "Tb_area" "A" WHERE "PR". " port_id "=" P "." ID "and" P "." dev_id "=" D "." ID "and" D "." dev_main_area_id "=" A "." ID "

It turns out that there is a materialized view refresh operation after the deletion!!!

Oh.  Buy Karma! Think of the operation of this master table has a materialized view with the base table changes and immediately refresh the action! The base table has 10多万条 data, and materialized views are associated with multiple tables. It takes a few seconds to refresh alone! That's the reason! Actually now do not need this materialized view, the required query data has been changed to another way to get! The materialized view is then deleted. Execute Delete, update, 0.003 seconds! Problem solved!

Through this issue to deal with, summed up the following lessons:

1. Materialized view try not to make the immediate refresh mode, so if the base table update frequent performance problems immediately occur. If you need to do a materialized view, make a job at the base table with idle execution.

2. The idea of executing SQL queries for basic data or removing updates for more than a second in pl/sql and so on is a matter of tracking down the SQL execution plan.

3. SQL execution plan tracking follows several ways:

1. first view the SQL execution plan, the execution plan is normal, the cost is only 4, used the primary key index

2. View wait events,

3. SELECT * from v$session_wait where sid = 507

4. View System IO,

--------------------------------------

1. View execution plan using Autotrace

Set Autotrace on | On EXPLAIN | On STATISTICS | traceonly | Traceonly EXPLAIN
Set Autotrace off

2. Enable Sql_trace to track current session

Turn on session tracking: Alter sessions set sql_trace=true;
Turn off session tracking: Alter sessions set SQL_TRACE=FALSE

3. Enable 10046 events to track the current session

Turn on session tracking: Alter sessions set events ' 10046 Trace name Context forever, Level 12 ';
Close session Trace: Alter sessions set events ' 10046 Trace name context off ';
Label the trace file: Alter session set tracefile_identifier= ' Dragon ';
Sql> host dir E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\

The volume in drive E is Disk1_vol3

The serial number of the volume is 609e-62d9

E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP's Directory
2012-07-19 17:58 <DIR>.
2012-07-19 17:58 <DIR>.
2012-07-19 17:58 3,057 BYISDB_ORA_704.TRC
2012-07-19 17:58 169,447 BYISDB_ORA_704_DRAGON.TRC

2 files 172,504 bytes
2 Directory 22,060,634,112 Free bytes

4. Enable 10046 Event tracking global session

This will have a serious impact on the performance of the entire system, so it is generally not recommended to open.

Turn on session tracking: Alter system set events ' 10046 Trace name Context forever, Level 12 ';

Turn off session tracking: Alter system set events ' 10046 Trace name context off ';

Get trace file

Sql> Select Pr.value | | '\' || I.instance_name | | ' _ora_ ' | | To_char (ps.spid) 
| | '. TRC ' "Trace file name" from V$session S, v$process PS, V$parameter PR, v$instance i 
where s.paddr = Ps.addr and S.Si D = userenv (' Sid ') and pr.name = ' user_dump_dest ';

Trace file name
--------------------------------------------------------------------------------
e:\ Oracle\product\10.2.0\admin\byisdb\udump\byisdb_ora_372.trc

5. Use Oracle System Package Dbms_system. Set_ev Trace Specifies session

PROCEDURE Set_ev

Parameter name type input/output default value?

------------------------------ ----------------------- ------ --------

SI Binary_integer in

SE Binary_integer in

EV Binary_integer in

LE Binary_integer in

NM VARCHAR2 in

Parameter description:

si-Specifies the SID of the session;

se-specifies the session SE;

ev-Event ID (such as: 10046);

Le-represents the level of trace;

nm-Specifies the username of the session;

Sql> Select Userenv (' Sid ') SID from dual;
    Sid
----------
    143

sql> Select SID, serial#, username from v$session where sid=143;
    SID  serial# USERNAME
--------------------------------------------------
    143    112 una_hr

Open session Tracking:sql> exec Dbms_system.set_ev (143, 112, 10046, 12, ");

Close session tracking:sql> exec Dbms_system.set_ev (143, 112, 10046, 0, ");

6. Format using the Tkprof tool

Tkprof tracefile outputfile [options]

E:\oracle\product\10.2.0\admin\byisdb\udump>tkprof byisdb_ora_704.trc 10046.txt sys=no Sort=prsela, Exeela, Fchela

The above is a small series for everyone to bring the Oracle data by the primary key to remove the problem of slow resolution of the whole content, I hope that we support cloud Habitat Community ~