Plan i statistika izvršenja SQL komandi

Plan i statistika izvršenja SQL komandi 1

Kako mogu da vidim plan i statistiku izvršenja komandi koristeći SQL*Plus?. 1

Test infrastructure. 1

Instalacija infrastructure. 1

ANALIZA UPITA PO ZAHTEVU.. 1

AUTOMATSKA ANALIZA UPITA - AUTOTRACE SESIJE. 3

Kako mogu da vidim plan i statistiku izvršenja komandi koristeći SQL*Plus?

Test infrastructure

Probati postoji li infrastruktura za statistiku npr. SQL> set autotrace on

Primer greske v10.2

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

Instalacija infrastructure

1.Kao korisnik sys startovati skript @%ORACLE_HOME%\sqlplus\admin\plustrce.sql

2.Grantovati rolu plustrace korisniku npr. SQL>GRANT plustrace TO SCOTT;

3.Kreirati tabelu PLAN_TABLE kao korisnik koji vrsi analizu: SQL>@%ORACLE_HOME%\RDBMS\ADMIN\utlxplan.sql

ANALIZA UPITA PO ZAHTEVU

set ORACLE_HOME=D:\oracle\product\10.2.0\db_1

SQL> explain plan for select * from emp;

Explained.

SQL> @%ORACLE_HOME%\rdbms\admin\utlxpls.sql

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

8 rows selected.

Umesto skripta moze se koristiti i paket DBMS_XPLAN:

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

SQL>truncate table plan_table

--Paralell

SQL> explain plan for select * from emp1;

SQL> @%ORACLE_HOME%\rdbms\admin\utlxplp.sql

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------

Plan hash value: 3728111555

--------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 2 (0)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 4 | TABLE ACCESS FULL| EMP1 | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------------------------------------

AUTOMATSKA ANALIZA UPITA - AUTOTRACE SESIJE

Kada se SQL*Plus parametru AUTOTRACE dodeli vrednost ON analiza se vrsi kod svakog upita.

SQL> set autotrace on

SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3728111555

--------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 518 | 2 (0)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |

| 3 | PX BLOCK ITERATOR | | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 4 | TABLE ACCESS FULL| EMP1 | 14 | 518 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

--------------------------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

12 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

1357 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

14 rows processed