Plsql_ executing 100 million data insert long script How to determine how long it takes V$sql/v$sqltext/v$sqlarea/v$sql_plan (case) (Monitor SQL Efficiency)

2014-08-27 Baoxinjian

I. Summary

When executing long-time plsql, it is sometimes necessary to see how well the program is running, how much data has been processed, and how much data is needed to be processed.

If the special log module in the program controls this piece, the problem is not too great

Without this piece of control, it might be necessary to track the session and query the dynamic performance view, presumably guessing the operation of the system, especially the change in the Undo table space.

Second, the case

Case:

Step1. Create a test table Bxj_test

Create Table bxj_test (    number,    varchar2(+),      number,    varchar2(+),    varchar2(  ,    creation_date date)

Step2. Create a test program and kill 1 with a

DeclareIinteger;begin   forIinch 1..100000000LoopInsert  intoapps.bxj_testValues(i,'invoicenum_' ||Lpad (To_char (i),Ten,'0'), Dbms_random.value (1,100000000),       'Gavin Corporation',       'Invoice Description' ||To_char (Sysdate,'YYYYMMDD HH24:MI:SS'), sysdate); EndLoop; Commit;End;

Step3. Dynamic Performance View 1-v$session

SELECTSID,--serial#,         --username,         --command,         --status,         --Osuser,         --sql_address,         --Sql_hash_value,sql_id, Sql_exec_start, prev_sql_id, Prev_exec_start, event, WAIT_CLA SS, State, Sql_trace, program fromv$sessionWHERETerminal= 'gavin-pc'   andSid= 373  andStatus= 'ACTIVE'  andProgram like 'Plsqldev.exe'

Step4. Dynamic Performance View 2-v$sql

Select  from V$sql where = ' 3rf19a6yjvz18 '

Step5. Dynamic Performance View 3-v$sqltext

Select *  from V$sqltext where = ' 3rf19a6yjvz18 ' Order  by Piece

Step6. Dynamic Performance View 4-v$sql_plan

Select  from V$sql_plan where = ' 3rf19a6yjvz18 '

Step7. Additional Dynamic performance views

Select *  fromV$session_longopswhere 1=1 andTarget= 'APPS. Bxj_test_invoice' andSid=  -Select *  fromv$session_waitwhereSid=  -Select *  fromV$session_wait_classSelect *  fromV$sess_iowhereSid=  -Select *  fromv$session_eventwhereSid=  -

Step6. Determine the number of operational records by confirming the size change of the undo space

Third, case-nohup into the background run script

The nohup command runs commands specified by the command parameter and any related arg parameters, ignoring all hang-up (SIGHUP) signals.

Use the Nohup command to run a program in the background after logging off.

To run the Nohup command in the background, add & (the symbol representing "and") to the end of the command.

1. REDIRECT Log files

If the output of the Nohup command is not redirected, the output is appended to the Nohup.out file in the current directory. If the nohup.out file for the current directory is not writable, the output is redirected to the $HOME/nohup.out file. If no file can be created or opened for appending, then the command specified by the commands parameter is not callable. If the standard error is a terminal, then all output of the specified command to the standard error is redirected to the same file descriptor as the standard output.

2. Key Concepts

(1). Function: Causes the process to continue execution after exiting the login.

(2). Format: $ nohup < program name > &

(3) Results: If the program has the result output, the output will be saved to a file named Nohup.out in the current directory, if the user does not have write permission in the current directory, the results will be saved to the user's home directory in the Nohup.out file.

(4). View: Jobs

Abalone New ********************

Reference: http://cc59.itpub.net/post/1845/286133

Plsql_ executing 100 million data insert long script How to determine how long it takes V$sql/v$sqltext/v$sqlarea/v$sql_plan (case) (Monitor SQL Efficiency)