в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:16Z

1 версия

Новая страница

== set timing on==



<source lang="sql">
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 "select object_name
11 from all_objects
12 where object_id = " || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
18 " seconds..." );
19 end;
20 /
47.99 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.17
SQL></source>

== Time the PL/SQL block==



<source lang="sql">
SQL>
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 myStart number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for "select object_name from all_objects where object_id = " || i;
10 fetch l_rc into l_dummy;
11 close l_rc;
12 end loop;
13 dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||" seconds..." );
14 end;
15 /
20.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.27
SQL>
SQL> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 myStart number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for "select object_name from all_objects where object_id = :x"
10 using i;
11 fetch l_rc into l_dummy;
12 close l_rc;
13 end loop;
14 dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||" seconds..." );
15 end;
16 /
.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL>
SQL> set timing off</source>

== TIMING table copying==



<source lang="sql">
SQL> CREATE TABLE project (
2 pro_id NUMBER(4),
3 pro_name VARCHAR2(40),
4 budget NUMBER(9,2),
5 CONSTRAINT project_pk PRIMARY KEY (pro_id)
6 );
Table created.
SQL>
SQL>
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678);
1 row created.
SQL> INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE server_usage (
2 pro_id NUMBER(4),
3 emp_id NUMBER,
4 time_log_date DATE,
5 hours_logged NUMBER(8,2),
6 dollars_charged NUMBER(8,2),
7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date)
8 );
Table created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created.
SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created.
SQL>
SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> TIMING START entire_script
SQL>
SQL> --Copy the emp table
SQL> TIMING START copy_emps
SQL> CREATE TABLE emp_copy AS
2 SELECT * FROM emp;
Table created.
SQL> TIMING STOP
timing for: copy_emps
Elapsed: 00:00:00.07
SQL>
SQL> --Copy the project table
SQL> TIMING START copy_project
SQL> CREATE TABLE project_copy AS
2 SELECT * FROM project;
Table created.
SQL> TIMING STOP
timing for: copy_project
Elapsed: 00:00:00.07
SQL>
SQL> --Copy the server_usage
SQL> TIMING START copy_server_usage
SQL> CREATE TABLE server_usage_copy AS
2 SELECT * FROM server_usage;
Table created.
SQL> TIMING STOP
timing for: copy_server_usage
Elapsed: 00:00:00.10
SQL>
SQL> TIMING STOP
timing for: entire_script
Elapsed: 00:00:00.41
SQL>
SQL> --Drop old versions of the copies, if any exist
SQL> DROP TABLE emp_copy;
Table dropped.
SQL> DROP TABLE project_copy;
Table dropped.
SQL> DROP TABLE server_usage_copy;
Table dropped.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table project;
Table dropped.
SQL> drop table server_usage;
Table dropped.</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Timing - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия