PLSQL _ How to determine how long it takes to insert a long script into 0.1 billion pieces of data v $ SQL/v $ sqltext/v $ sqlarea/v $ SQL _plan (CASE) (monitor SQL efficiency), sqlarea

PLSQL _ How to determine how long it takes to insert a long script into 0.1 billion pieces of data v $ SQL/v $ sqltext/v $ sqlarea/v $ SQL _plan (CASE) (monitor SQL efficiency), sqlarea

2014-08-27 BaoXinjian

I. Summary

When executing a PLSQL statement that takes a long time, you sometimes need to check the program running Progress. Currently, you need to process more information about how much data has been processed.

If the Log Module is used by the program, the problem will not be too serious.

Without this control, you may need to track the session and query the dynamic performance view to guess the system running status, especially the undo tablespace changes.

 

Ii. Case studies

Case:

Step 1. Create a test table bxj_test

create table bxj_test (    invoice_id number,    invoice_num varchar2(100),    total number,    company varchar2(100),    description varchar2(500),    creation_date date)

 

Step 2: Create a test program and input 1 as one

declare  i integer;begin  for i in 1 .. 100000000 loop    insert into apps.bxj_test    values      (i,       'INVOICENUM_' || lpad(to_char(i), 10, '0'),       dbms_random.value(1, 100000000),       'Gavin Corporation',       'Invoice Description' || to_char(sysdate, 'YYYYMMDD HH24:MI:SS'),       sysdate);  end loop;  commit;end;

 

 

Step3. dynamic performance view 1-> v $ session

SELECT   sid,         --serial#,         --username,         --command,         --status,         --osuser,         --sql_address,         --sql_hash_value,         sql_id,         sql_exec_start,         prev_sql_id,         prev_exec_start,         event,         wait_class,         state,         sql_trace,         program  FROM   v$session WHERE   terminal = 'GAVIN-PC'  AND sid = 373 AND status = 'ACTIVE' AND program like 'plsqldev.exe'

 

Step 4. Dynamic Performance view 2-v $ SQL

select sql_id, sql_text, executions, cpu_time, elapsed_time from v$sqlwhere sql_id = '3rf19a6yjvz18' 

 

Step 5. Dynamic Performance view 3-v $ sqltext

select * from v$sqltextwhere sql_id = '3rf19a6yjvz18'order by piece

 

Step6. dynamic performance View 4-v $ SQL _plan

select sql_id, operation, optimizer, id, parent_id, depth, position, search_columns, cost from v$sql_planwhere sql_id = '3rf19a6yjvz18'

 

Step7. other dynamic performance views

select * from V$SESSION_LONGOPSwhere 1=1and target = 'APPS.BXJ_TEST_INVOICE'and sid = 38select * from V$SESSION_WAITwhere sid = 24select * from V$SESSION_WAIT_CLASSselect * from V$SESS_IOwhere sid = 24select * from V$SESSION_EVENTwhere sid = 24

 

 

 

 

 

Step 6. Confirm the size change of the undo space to determine the number of Operation Records

 

 

Iii. Case-transfer nohup to the background to run the script

The nohup Command runs the Command specified by the Command parameter and any relevant Arg parameter, ignoring all SIGHUP signals.

After logging out, run the nohup command to run the program in the background.

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

 

1. Redirect log files

If the output of the nohup command is not redirected, the output will be appended to the nohup. out file in the current directory. If the nohup. out file in the current directory cannot be written, the output will be redirected to the $ HOME/nohup. out file. If no file can be created or opened for append, the Command specified by the Command Parameter cannot be called. If a standard error is a terminal, write the specified command to all outputs of the standard error as the standard output and redirect it to the same file descriptor.

 

2. Main Concepts

(1). function: enable the process to continue running after logging out.

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

(3 ). result: if the program has a result output, the output result will be saved to a file named nohup in the current directory. in the out file, if you do not have the write permission in the current directory, the result will be saved to the nohup in the user's main directory. out file.

(4). View: jobs

 

********************Author: Bao Xin********************

 

 

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

Several Methods for executing SQL statements using PLSQL

Plsql is very convenient for us to execute SQL. Below is a brief introduction to several commonly used commands (of course, each svn branch can also use the ant script to automatically execute the SQL file under a file). First open the plsq command window 1) execute the SQL file (you can put the SQL statement to be executed in a file) and input @ ''to enter the path of the SQL file in single quotes, such as jbpm under D: \ db. oracle. SQL file, see (the SQL file content is select * from system_menu r where r. menu_name = 'sales order';) 2) import the dmp file. Delete the corresponding user before importing the dmp file (test_user is used as an example below) drop user test_user cascade; $ impdp system/test123 @ SYSTEM directory = data_pump_dir schemas = test_user dumpfile = date. DMP REMAP_SCHEMA = test_user: test_userTABLE_EXISTS_ACTION = replace logfile = imp. log; alter user test_user identified by 123456; 3) when we need to restart the test server or kill the Application Server (such as tomcat) from the official database to the data beta version) session connection v $ session this table can find the basic information of the application connected to the oracle database. Therefore, you can kill the session of the corresponding program through this table. If you want to kill the session connected to user test_user, you can run the following SQL: select * from v $ session r where r. USERNAME = 'test _ user'; then kill the corresponding session. For more information, see the following: for example, if you want to kill the first session, run the following SQL: alter system kill session '21, 77 '; // because of sid, serial #. these two columns are unique. The following describes the application information for connecting to oracle and the session information for oracle operations. 1. Find the basic information of the application connected to the oracle database. Select sid, serial #, username, -- connection user name program, -- Application name machine, -- machine name osuser, -- operating system user logon_time -- logon time from v $ session; 2. how to view session-level wait events? When we adjust the database performance, one of the most important reference indicators is the system waiting for events. $ System_event, v $ session_event, and v $ session_wait records system-level and session-level wait events, by querying these views, you can find out what database operations are waiting? Whether the disk I/O, the buffer is busy, or the plug-in lock, etc. You can use the following SQL statement to query what each application is waiting for, and then adjust the database performance based on the information. Select s. username, s. program, s. status, se. event, se. total_waits, se. total_timeouts, se. time_waited, se. average_waitfrom v $ session s, v $ session_event seWhere s. sid = se. sid And se. e ...... remaining full text>

Several Methods for executing SQL statements using PLSQL

Plsql is very convenient for us to execute SQL. Below is a brief introduction to several commonly used commands (of course, each svn branch can also use the ant script to automatically execute the SQL file under a file). First open the plsq command window 1) execute the SQL file (you can put the SQL statement to be executed in a file) and input @ ''to enter the path of the SQL file in single quotes, such as jbpm under D: \ db. oracle. SQL file, see (the SQL file content is select * from system_menu r where r. menu_name = 'sales order';) 2) import the dmp file. Delete the corresponding user before importing the dmp file (test_user is taken as an example below) drop user test_user cascade;
$ Impdp system/test123 @ SYSTEM directory = data_pump_dir schemas = test_user dumpfile = date. DMP REMAP_SCHEMA = test_user: test_userTABLE_EXISTS_ACTION = replace logfile = imp. log;
Alter user test_user identified by 123456; 3) when we need to restart the test server or kill the Application Server (such as tomcat) from the official database to the data beta version) session connection v $ session this table can find the basic information of the application connected to the oracle database. Therefore, you can kill the session of the corresponding program through this table. If you want to kill the session connected to user test_user, you can run the following SQL: select * from v $ session r where r. USERNAME = 'test _ user'; then kill the corresponding session. For more information, see the following: for example, if you want to kill the first session, run the following SQL: alter system kill session '21, 77 '; // because of sid, serial #. these two columns are unique. The following describes the application information for connecting to oracle and the session information for oracle operations. 1. Find the basic information of the application connected to the oracle database. Select sid, serial #,
Username, -- connection username
Program, -- Application name
Machine, -- machine name
Osuser, -- Operating System User
Logon_time -- Logon Time
From v $ session; 2. How to view session-level wait events? When we adjust the database performance, one of the most important reference indicators is the system waiting for events. $ System_event, v $ session_event, and v $ session_wait records system-level and session-level wait events, by querying these views, you can find out what database operations are waiting? Whether the disk I/O, the buffer is busy, or the plug-in lock, etc. You can use the following SQL statement to query what each application is waiting for, and then adjust the database performance based on the information.
Select s. username, s. program, s. status, se. event, se. total_waits, se. total_timeouts, se. time_waited, se. average_wait
From v $ session s, v $ session_event se
Where s. sid = se. sid And se. e ...... remaining full text>