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
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>
Performace difference between sql and pl/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.
Statistics query Timing on index
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>
Time and autotrace a big table
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>
time a query with where clause
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>
Time command
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>
Time lower text function
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>
Time query for a huge table
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>
Timing passing table collection parameter to a procedure
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>
TIMING table copying
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.
timing unconditional loop
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>