Oracle PL/SQL/SQL Plus/Timing
Содержание
- 1 Loop timing
- 2 Performace difference between sql and pl/sql
- 3 Statistics query Timing on index
- 4 Time and autotrace a big table
- 5 time a query with where clause
- 6 Time command
- 7 Time lower text function
- 8 Time query for a huge table
- 9 Timing passing table collection parameter to a procedure
- 10 TIMING table copying
- 11 timing unconditional loop
Loop timing
<source lang="sql">
SQL> SQL> SQL> set timing on SQL> SQL> declare
2 some_string varchar2(255);
3 begin
4 for idx in 1 .. 100000 loop
5 some_string := rpad("*",254,"*");
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.05 SQL> SQL> set timing off SQL> SQL>
</source>
Performace difference between sql and pl/sql
<source lang="sql">
SQL> SQL> SQL> create table myTable as select * from all_objects; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable" ); PL/SQL procedure successfully completed. SQL> SQL> create table myTable2 as select * from myTable; Table created. SQL> SQL> exec dbms_stats.gather_table_stats( user, "myTable2" ); PL/SQL procedure successfully completed. SQL> SQL> set timing on SQL> SQL> update myTable set object_name = lower(object_name); 12599 rows updated. Elapsed: 00:00:00.15 SQL> begin
2 for x in ( select rowid rid, object_name, rownum r from myTable2 ) 3 loop 4 update myTable2 set object_name = lower(x.object_name) 5 where rowid = x.rid; 6 if ( mod(x.r,100) = 0 ) then 7 commit; 8 end if; 9 end loop; 10 commit; 11 end; 12 /
PL/SQL procedure successfully completed. Elapsed: 00:00:01.59 SQL> SQL> set timing off SQL> SQL> drop table myTable; Table dropped. SQL> drop table myTable2; Table dropped.
</source>
Statistics query Timing on index
<source lang="sql">
SQL> SQL> create table t
2 as 3 select object_name ename, 4 mod(object_id,50) deptno, 5 object_id sal 6 from all_objects 7 where rownum <= 1000 8 /
Table created. SQL> SQL> create index t_idx on t(deptno,ename); Index created. SQL> SQL> set autotrace traceonly SQL> set timing on SQL> select ename, deptno, sal,
2 sum(sal) over 3 (order by deptno, ename) running_total, 4 sum(sal) over 5 (partition by deptno 6 order by ename) department_total, 7 row_number() over 8 (partition by deptno 9 order by ename) seq 10 from t emp 11 order by deptno, ename 12 /
1000 rows selected. Elapsed: 00:00:00.19 Execution Plan
Plan hash value: 2335850315
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1000 | 43000 | 3 (0)| 00:00:01 | | 1 | WINDOW SORT | | 1000 | 43000 | 3 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| T | 1000 | 43000 | 3 (0)| 00:00:01 |
Note
- dynamic sampling used for this statement
Statistics
28 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
44533 bytes sent via SQL*Net to client
1106 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SQL> set timing off SQL> SQL> drop table t; Table dropped. SQL> SQL>
</source>
Time and autotrace a big table
<source lang="sql">
SQL> SQL> SQL> set echo on SQL> SQL> set termout off SQL> SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> SQL> analyze table my_all_objects compute statistics; Table analyzed. SQL> SQL> set autotrace on SQL> set timing on SQL> select owner, count(*) from my_all_objects group by owner; OWNER COUNT(*)
--------
MDSYS 1374.00 TSMSYS 6.00 FLOWS_020100 3255.00 PUBLIC 8298.00 OUTLN 21.00 sqle 603.00 CTXSYS 1014.00 HR 102.00 FLOWS_FILES 33.00 SYSTEM 1266.00 DBSNMP 138.00 OWNER COUNT(*)
--------
XDB 1002.00 SYS ######## 13 rows selected. Elapsed: 00:00:00.31 Execution Plan
Plan hash value: 2509106709
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 13 | 65 | 136 (6)| 00:00:02 | | 1 | HASH GROUP BY | | 13 | 65 | 136 (6)| 00:00:02 | | 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 36945 | 180K| 130 (2)| 00:00:02 |
Statistics
1 recursive calls
0 db block gets
470 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13 rows processed
SQL> SQL> set timing off SQL> set autotrace off SQL> drop table my_all_objects; Table dropped. SQL> SQL>
</source>
time a query with where clause
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id ) 3 values 4 ( "New Owner", "New Name", "New Type", 1111111 );
1 row created. SQL> SQL> SQL> set timing on SQL> select owner, count(*)
2 from my_all_objects 3 where owner = "New Owner" 4 group by owner;
OWNER COUNT(*)
--------
New Owner 1.00 Elapsed: 00:00:00.10 SQL> set timing off SQL> SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL>
</source>
Time command
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> set autotrace on; SQL> set timing on; SQL> select count(*) from employee;
COUNT(*)
8
Elapsed: 00:00:00.07 Execution Plan
Plan hash value: 301197670
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMPLOYEE | 8 | 2 (0)| 00:00:01 |
Note
- dynamic sampling used for this statement
Statistics
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. Elapsed: 00:00:00.14 SQL> SQL> SQL> SQL>
</source>
Time lower text function
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> SQL> spool off not spooling currently SQL> set termout on SQL> SQL> SQL> set timing on SQL> select lower(owner) from my_all_objects group by owner; LOWER(OWNER)
mdsys tsmsys flows_020100 public outln sqle ctxsys hr flows_files system dbsnmp LOWER(OWNER)
xdb sys 13 rows selected. Elapsed: 00:00:00.25 SQL> set timing off SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL> SQL>
</source>
Time query for a huge table
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> SQL> set timing on SQL> SQL> select owner, count(*)
2 from my_all_objects 3 group by owner;
OWNER COUNT(*)
--------
MDSYS 1374.00 TSMSYS 6.00 FLOWS_020100 3255.00 PUBLIC 8298.00 OUTLN 21.00 sqle 603.00 CTXSYS 1014.00 HR 102.00 FLOWS_FILES 33.00 SYSTEM 1266.00 DBSNMP 138.00 OWNER COUNT(*)
--------
XDB 1002.00 SYS ######## 13 rows selected. Elapsed: 00:00:00.26 SQL> SQL> set timing off SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL>
</source>
Timing passing table collection parameter to a procedure
<source lang="sql">
SQL> create or replace type num_list is table of number;
2 /
Type created. SQL> create or replace procedure DO_WORK(x num_list) is
2 begin 3 for i in 1 .. x.count loop 4 null; 5 end loop; 6 end; 7 /
Procedure created. SQL> set timing on SQL> declare
2 v varchar2(32767) := "num_list(";
3 begin
4 for i in 1 .. 5000 loop
5 v := v || case when i = 1 then to_char(i) else ","||i end;
6 end loop;
7 v := v || ")";
8 execute immediate "begin do_work("||v||"); end;";
9 end;
10 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.97 SQL> set timing off SQL>
</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>
timing unconditional loop
<source lang="sql">
SQL> SQL> set timing on SQL> SQL> declare
2 l_idx pls_integer := 0;
3 l_some_string varchar2(255);
4 begin
5 loop
6 l_idx := l_idx + 1;
7 exit when l_idx = 100000;
8 l_some_string := rpad("*",254,"*");
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed. Elapsed: 00:00:00.15 SQL> SQL> set timing off SQL> SQL> SQL>
</source>
