开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20160121]调式PL SQL.txt

简介: [20160121]调式PL SQL.txt --一上班,看到一个帖子,里面提到一些技巧,才想起来许多东西视乎在忘记。 --链接 :https://connormcdonald.wordpress.com/2016/01/20/problematic-sql-plsql-is-your-friend/ --我自己重复测试看看。
+关注继续查看

[20160121]调式PL SQL.txt

--一上班,看到一个帖子,里面提到一些技巧,才想起来许多东西视乎在忘记。
--链接 :https://connormcdonald.wordpress.com/2016/01/20/problematic-sql-plsql-is-your-friend/

--我自己重复测试看看。

1.环境:
SCOTT@book> @&r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create or replace procedure P is
  x int;
begin
--
-- this is my proc, and it has one very poor SQL
--
  select count(*)
  into   x
  from   dba_views;
                                                
  select count(*)
  into   x
  from   dba_tables;
                                                
  select count(*)
  into   x
  from   dba_objects;
                                                
  select count(*)
  into   x
  from   dba_objects, dba_objects;
                                                
end;
/

--要以上脚本正常执行,必须显式授权select权限。

SYS@book>  GRANT SELECT ON SYS.dba_views TO SCOTT;
Grant succeeded.

SYS@book>  GRANT SELECT ON SYS.dba_tables TO SCOTT;
Grant succeeded.

SYS@book>  GRANT SELECT ON SYS.dba_objects TO SCOTT;
Grant succeeded.

2.测试:
SCOTT@book> @ &r/spid

       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
        90        589 51753       32        216 alter system kill session '90,589' immediate;

SCOTT@book> exec p
--很慢....

--打开另外的会话。
SCOTT@book> select sid,username, sql_id from   v$session where  status = 'ACTIVE' and    last_call_et > 10 and    username is not null;
       SID USERNAME             SQL_ID
---------- -------------------- -------------
        90 SCOTT                ff35fbgz27513

SCOTT@book> @ &r/wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00               1650815232          1          0        104       1031         53 SQL*Net message to client                WAITED SHORT TIME                 3               0

SCOTT@book> select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait,status from v$session where sid=90 ;
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT STATUS
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- --------------- --------
0000000062657100 0000000000000001 00               1650815232          1          0         90        589         68 SQL*Net message from client              WAITED KNOWN TIME          97590648             153 ACTIVE

--从这里看仅仅SQL*Net message from client,实际上后台执行那条sql语句。具有一定的欺骗性,自己以后要注意。

SCOTT@book> @ &r/sqlid ff35fbgz27513
old   1: select sql_id,to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = '&&1' and rownum<=1
new   1: select sql_id,to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = 'ff35fbgz27513' and rownum<=1
old   3: select sql_id,to_char(replace(sql_text,chr(13),'')) sqltext  from dba_hist_sqltext where  sql_id = '&&1' and rownum<=1
new   3: select sql_id,to_char(replace(sql_text,chr(13),'')) sqltext  from dba_hist_sqltext where  sql_id = 'ff35fbgz27513' and rownum<=1
SQL_ID        SQLTEXT
------------- ----------------------------------------------
ff35fbgz27513 SELECT COUNT(*) FROM DBA_OBJECTS, DBA_OBJECTS

--可以找到这条sql语句。

SCOTT@book> select PROGRAM_ID, PROGRAM_LINE# from v$sql where  sql_id = 'ff35fbgz27513';
PROGRAM_ID PROGRAM_LINE#
---------- -------------
     89889            19

--通过这里可以知道在那个存储过程以及哪一行.

SCOTT@book> select * from dba_objects where object_id=89889;
OWNER  OBJECT_NAME SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT  P                           89889                PROCEDURE           2016-01-21 08:46:24 2016-01-21 08:51:56 2016-01-21:08:51:56 VALID   N N N          1

SCOTT@book> select * from dba_source where owner=user and name='P';
OWNER  NAME  TYPE       LINE TEXT
------ ----- ---------- ---- --------------------------------------------------
SCOTT  P     PROCEDURE     1 procedure P is
SCOTT  P     PROCEDURE     2   x int;
SCOTT  P     PROCEDURE     3 begin
SCOTT  P     PROCEDURE     4 --
SCOTT  P     PROCEDURE     5 -- this is my proc, and it has one very poor SQL
SCOTT  P     PROCEDURE     6 --
SCOTT  P     PROCEDURE     7   select count(*)
SCOTT  P     PROCEDURE     8   into   x
SCOTT  P     PROCEDURE     9   from   dba_views;
SCOTT  P     PROCEDURE    10
SCOTT  P     PROCEDURE    11   select count(*)
SCOTT  P     PROCEDURE    12   into   x
SCOTT  P     PROCEDURE    13   from   dba_tables;
SCOTT  P     PROCEDURE    14
SCOTT  P     PROCEDURE    15   select count(*)
SCOTT  P     PROCEDURE    16   into   x
SCOTT  P     PROCEDURE    17   from   dba_objects;
SCOTT  P     PROCEDURE    18
SCOTT  P     PROCEDURE    19   select count(*)
SCOTT  P     PROCEDURE    20   into   x
SCOTT  P     PROCEDURE    21   from   dba_objects, dba_objects;
SCOTT  P     PROCEDURE    22
SCOTT  P     PROCEDURE    23 end;
23 rows selected.

--从以上的测试,都能对上。

3.如何优化那条sql语句呢?

--当然这种笛卡尔积本来不是优化的可能。不过我想起以前好像可以通过no_merge不融合来获得好的效果。上面的笛卡尔结果太大。效果不明显。
--换一个人视图看看。

SCOTT@book> set timing on
SCOTT@book> SELECT    COUNT (*)  FROM dba_tables a , dba_tables b;
  COUNT(*)
----------
   8294400
Elapsed: 00:00:26.30

SCOTT@book> SELECT  /*+ NO_MERGE(A) NO_MERGE(B) */  COUNT (*)  FROM dba_tables a , dba_tables b;
  COUNT(*)
----------
   8294400
Elapsed: 00:00:00.48

--如果没有使用别名a,b。

SCOTT@book> SELECT  /*+ NO_MERGE(DBA_TABLES_0002@SEL$1) NO_MERGE(DBA_TABLES_0001@SEL$1) */  COUNT (*)  FROM dba_tables  , dba_tables ;
  COUNT(*)
----------
   8294400
Elapsed: 00:00:00.68

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SpringBoot从入门到精通(二十六)超级简单的数据持久化框架!Spring Data JPA 的使用!
Spring Data JPA 是Spring Data框架下的一个基于JPA标准操作数据的模块,简化了操作持久层的代码,只需要编写接口就可以。如果你是第一次使用 Spring Data JPA ,你一定会惊呼这东西简直就是神器,几乎不需要写什么关于数据库访问的代码一个基本的 CURD 的功能就出来了。使用非常简单方便,下面我们就来讲述 JPA 使用的基本操作。
658 0
springboot+mybatis+druid+mysql 保存emoji表情
       最近在学习springboot,在练习做一个简单博客系统时遇到了保存emoji表情出错,搜索了半天才搞定。这里记录下来,希望能够帮助到自己或有需要的人不要踩坑。
3656 0
[20171208]ORA-16014(10G).txt
[20171208]ORA-16014(10G).txt --//别人配置dg遇到的问题,当时没有记录.在自己的环境做一个演示.这个问题相对隐藏很深.主要是自己不注意这些细节.
1089 0
[20160215]超长sql语句与父子光标.txt
[20160215]超长sql语句与父子光标.txt --看提到sql语句不会进入保留池, 要进入保留池,chunk的大小必须大于_shared_pool_reserved_min_alloc。
779 0
[20160125]闭包传递问题.txt
[20160125]闭包传递问题.txt --所谓闭包传递是指sql语句的谓词条件A=B and B=C 可以推出 A=C. oracle 的 优化器能够利用这个特性优化sql语句。
474 0
[20120601]ITL的问题补充.txt
前一阵子,遇到ITL的问题,写了一篇BLOG.http://space.itpub.net/?uid-267265-action-viewspace-itemid-717089最近看一篇blog ,才知道真正的问题在那里?http://antognini.
592 0
[20120327]toad与sqlplus下执行sql语句的一个细节.txt
TOAD是一个很好的图形化oracle管理工具,昨天在解决一个问题时遇到了一些细节问题,实际上我以前就知道,现在把它写下来:我使用toad版本是9.6.0.27.1.
791 0
[20120319]一条sql语句的优化.txt
前天检查数据库,发现一天sql语句执行如下:SELECT MAX (undrug_code)   FROM undrug_info  WHERE SUBSTR (undrug_code, 1, 1) = 'F';undrug_code是表undrug_info的主键.
796 0
[20120305]Build a Metadata Repository.txt
数据库经常变动,需要把里面的一些存储过程,表与索引的定义保存下来:使用expdp/impdp是一个很好的方法,仅仅需要转储metadata信息。命令如下:1.保存metadata文件。
564 0
[20120301]索引命名问题.txt
http://jonathanlewis.wordpress.com/2012/02/06/index-naming/重复测试。1.测试环境:SQL> select * from v$version ;BANNER------------------------...
606 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
PostgresChina2018_董红禹_SQL_Server迁移PG经验分享
立即下载
SQL Sever迁移PG经验
立即下载