创建表和存储过程
点击( 此处 )折叠或打开
-
CREATE TABLE customers AS
-
SELECT * FROM sh . customers where country_id in ( 52778 , 52772 ) ;
-
-
CREATE OR REPLACE FUNCTION f_count_obj ( p_country_id VARCHAR2 )
-
RETURN NUMBER AS
-
v_count NUMBER ;
-
BEGIN
-
SELECT COUNT ( * )
-
INTO v_count
-
FROM customers e26856649_1
-
WHERE country_id = p_country_id ;
-
RETURN v_count ;
-
END ;
-
/
点击( 此处 )折叠或打开
-
11 : 01 : 18 SQL > SET timing ON
-
SET autotrace traceonly
-
SELECT country_id , cust_first_name , f_count_obj ( country_id ) AS cnt FROM customers ;
-
-
4049 rows selected .
-
-
Elapsed : 00 : 00 : 02 . 80
-
-
Execution Plan
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
Plan hash value : 2008213504
-
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
| Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
| 0 | SELECT STATEMENT | | 3936 | 98400 | 33 ( ) | 00 : 00 : 01 |
-
| 1 | TABLE ACCESS FULL | CUSTOMERS | 3936 | 98400 | 33 ( ) | 00 : 00 : 01 |
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
-
Note
-
- - - - -
-
- dynamic sampling used for this statement ( level = 2 )
-
-
-
Statistics
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
4078 recursive calls
-
0 db block gets
-
437813 consistent gets
-
0 physical reads
-
0 redo size
-
99781 bytes sent via SQL * Net to client
-
3478 bytes received via SQL * Net from client
-
271 SQL * Net roundtrips to/from client
-
0 sorts ( memory )
-
0 sorts ( disk )
-
4049 rows processed
点击( 此处 )折叠或打开
-
SQL > SELECT fetches , executions , sql_text
-
FROM v$sql
-
WHERE sql_text LIKE '%26856649 _1 %'
-
AND sql_text NOT LIKE '%v$sql%'
-
AND sql_text NOT LIKE '%OPT_DYN_SAMP%' ;
-
-
FETCHES EXECUTIONS SQL_TEXT
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
4049 4049 SELECT COUNT ( * ) FROM CUSTOMERS E26856649_1 WHERE COUNTRY_ID = : B1
我们看到被解析 执行了4049次
下面我们添加如下参数
点击( 此处 )折叠或打开
-
CREATE OR REPLACE FUNCTION f_count_obj2 ( p_country_id VARCHAR2 )
-
RETURN NUMBER DETERMINISTIC AS
-
v_count NUMBER ;
-
BEGIN
-
SELECT
-
COUNT ( * )
-
INTO v_count
-
FROM customers e26856649_2
-
WHERE country_id = p_country_id ;
-
RETURN v_count ;
-
END ;
-
/
这里指定别名为_2是为了区分v$sql中的sql_text
点击( 此处 )折叠或打开
-
SELECT country_id , cust_first_name , f_count_obj2 ( country_id ) AS cnt FROM customers;
点击( 此处 )折叠或打开
-
11 : 47 : 07 SQL > SELECT fetches , executions , sql_text
-
FROM v$sql
-
WHERE sql_text LIKE '%26856649_2%'
-
AND sql_text NOT LIKE '%v$sql%'
-
AND sql_text NOT LIKE '%OPT_DYN_SAMP%' ;
-
-
FETCHES EXECUTIONS SQL_TEXT
-
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
540 540 SELECT COUNT ( * ) FROM CUSTOMERS E26856649_2 WHERE COUNTRY_ID = : B1
结果看到这个被执行解析 540次,时间从2点几秒,变成1.几秒了。。。 。。。
有的时候,说函数很慢让你改,你又不会改,那么在函数里加这个参数,哈哈。
点击( 此处 )折叠或打开
-
SELECT o . country_id ,
-
o . cust_first_name ,
-
( SELECT COUNT ( * ) FROM customers t WHERE t . country_id = o . country_id ) AS cnt
-
FROM customers o
改成标量子查询,时间不到1s
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29990276/viewspace-1485366/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29990276/viewspace-1485366/