Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to view delete data in sql, which is very detailed and has certain reference value. Friends who are interested must finish it!

2. Test data, statistics of indexed and non-indexed objects, session statistics, session wait events when deleting delete

Delete test_a

Delete takes 491 seconds

Select c. Siddreb. Name from v$sesstat a. Value from v$sesstat a, v$statname b, (select SID from v$mystat where rownum select * from v$sess_time_model where sid=318 order by value desc

SID STAT_ID STAT_NAME VALUE

318 3649082374 DB time 491842941

318 2821698184 sql execute elapsed time 491750449

318 2748282437 DB CPU 436421084-waiting is more than INSERT

318 1431595225 parse time elapsed 405367

318 372226525 hard parse elapsed time 396096

318 1990024365 connection management call elapsed time 28152

318 1311180441 PL/SQL compilation elapsed time 3400

318 2643905994 PL/SQL execution elapsed time 646

318 1159091985 repeated bind elapsed time 319

Conversation 315

Delete test_b

It takes more than 1100 seconds

SQL > select c.sidreb. Name from v$sesstat a.Vale from v$sesstat a, v$statname b, (select SID from v$mystat where rownum select *

2 from v$session_event a

3 where a.SID = 315

4 order by TIME_WAITED desc

SID EVENT TIME_WAITED_MICRO

-

315 SQL*Net message from client 317688133

315 db file sequential read 131743590-131s (index reading)

315 db file scattered read 34265605-34 seconds

315 log file switch completion 6006692

315 events in waitclass Other 58204

315 SQL*Net message to client 65

SQL > select * from v$sess_time_model where sid=315 order by value desc

SID STAT_ID STAT_NAME VALUE

315 3649082374 DB time 1068676495

315 2821698184 sql execute elapsed time 1068593627

315 2748282437 DB CPU 886348292

315 1431595225 parse time elapsed 129178

315 372226525 hard parse elapsed time 112666

315 1311180441 PL/SQL compilation elapsed time 25308

315 1990024365 connection management call elapsed time 21365

315 2643905994 PL/SQL execution elapsed time 3872

315 1159091985 repeated bind elapsed time 518

In fact, compared with INSERT,DELETE statements, more LOG is generated and more UNDO delete is generated during the reading of tables and indexes.

But basically, one more index time will be * 2, so for a large number of data deletions, and ordinary INSERT is not suitable for indexing.

But if there are more operations like DELETE TABLE WHERE ID=1, then the index can improve the speed of reading the table, so that you don't have to use the

It is much faster to scan the whole table and use index scanning. So we still have to treat them differently.

The above is all the contents of the article "how to View delete data in sql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.