创建表和存储过程

点击( 此处 )折叠或打开

1. CREATE TABLE customers AS
   

2. SELECT * FROM sh . customers where country_id in ( 52778 , 52772 ) ;
   

3. 
   

4. CREATE OR REPLACE FUNCTION f_count_obj ( p_country_id VARCHAR2 )
   

5.   RETURN NUMBER AS
   

6.   v_count NUMBER ;
   

7. BEGIN
   

8.   SELECT COUNT ( * )
   

9.      INTO v_count
   

10.     FROM customers e26856649_1
    

11.    WHERE country_id = p_country_id ;
    

12.   RETURN v_count ;
    

13. END ;
    

14. /

点击( 此处 )折叠或打开

1. 11 : 01 : 18 SQL > SET timing ON
   

2. SET autotrace traceonly
   

3. SELECT country_id , cust_first_name , f_count_obj ( country_id ) AS cnt FROM customers ;

4. 
   

5. 4049 rows selected .
   

6. 
   

7. Elapsed : 00 : 00 : 02 . 80
   

8. 
   

9. Execution Plan
   

10. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

11. Plan hash value : 2008213504
    

12. 
    

13. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

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

15. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

16. |   0 | SELECT STATEMENT   |             | 3936 | 98400 |    33     ( ) | 00 : 00 : 01 |
    

17. |   1 |   TABLE ACCESS FULL | CUSTOMERS | 3936 | 98400 |    33     ( ) | 00 : 00 : 01 |
    

18. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

19. 
    

20. Note
    

21. - - - - -
    

22.     - dynamic sampling used for this statement ( level = 2 )
    

23. 
    

24. 
    

25. Statistics
    

26. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    

27.        4078  recursive calls
    

28.       0  db block gets
    

29.      437813  consistent gets
    

30.       0  physical reads
    

31.       0  redo size
    

32.       99781  bytes sent via SQL * Net to client
    

33.        3478  bytes received via SQL * Net from client
    

34.     271  SQL * Net roundtrips to/from client
    

35.       0  sorts ( memory )
    

36.       0  sorts ( disk )
    

37.        4049  rows processed

点击( 此处 )折叠或打开

1. SQL > SELECT fetches , executions , sql_text
   

2. FROM v$sql
   

3. WHERE sql_text LIKE '%26856649 _1 %'
   

4. AND sql_text NOT LIKE '%v$sql%'
   

5. AND sql_text NOT LIKE '%OPT_DYN_SAMP%' ;
   

6. 
   

7.    FETCHES EXECUTIONS   SQL_TEXT
   

8. - - - - - - - - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

9.       4049     4049     SELECT COUNT ( * ) FROM CUSTOMERS E26856649_1 WHERE COUNTRY_ID = : B1

我们看到被解析 执行了4049次
下面我们添加如下参数

点击( 此处 )折叠或打开

1. CREATE OR REPLACE FUNCTION f_count_obj2 ( p_country_id VARCHAR2 )
   

2.   RETURN NUMBER DETERMINISTIC AS
   

3.   v_count NUMBER ;
   

4. BEGIN
   

5.   SELECT
   

6.    COUNT ( * )
   

7.      INTO v_count
   

8.     FROM customers e26856649_2
   

9.    WHERE country_id = p_country_id ;
   

10.   RETURN v_count ;
    

11. END ;
    

12. /

这里指定别名为_2是为了区分v$sql中的sql_text

点击( 此处 )折叠或打开

1. SELECT country_id , cust_first_name , f_count_obj2 ( country_id ) AS cnt FROM customers;

点击( 此处 )折叠或打开

1. 11 : 47 : 07 SQL > SELECT fetches , executions , sql_text
   

2.   FROM v$sql
   

3.  WHERE sql_text LIKE '%26856649_2%'
   

4.     AND sql_text NOT LIKE '%v$sql%'
   

5.     AND sql_text NOT LIKE '%OPT_DYN_SAMP%' ;
   

6. 
   

7.    FETCHES EXECUTIONS  SQL_TEXT
   

8. - - - - - - - - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

9.        540      540     SELECT COUNT ( * ) FROM CUSTOMERS E26856649_2 WHERE COUNTRY_ID = : B1

结果看到这个被执行解析 540次，时间从2点几秒，变成1.几秒了。。。 。。。

有的时候，说函数很慢让你改，你又不会改，那么在函数里加这个参数，哈哈。

点击( 此处 )折叠或打开

1. SELECT o . country_id ,
   

2.        o . cust_first_name ,
   

3.         ( SELECT COUNT ( * ) FROM customers t WHERE t . country_id = o . country_id ) AS cnt
   

4.   FROM customers o

改成标量子查询，时间不到1s