• Oracle DBA学习篇之SQL_TRACE


    SQL_TRACE
    
    set linesize 10000;
    set pagesize 20000;
    set serveroutput on;
    alter session set sql_trace=true;
    select count(*) from firefox;
    alter session set sql_trace=false;
    
    --查看sql_trace trace file
    select * from v$diag_info where name like 'Default%';
    select sid,serial# from v$session ;
    
    
    variable x number;
    exec :x:=1;
    alter session set events '10046 trace name context forever,level 12';
    select count(*) from firefox where object_id=:x;
    alter session set events '10046 trace name context off';
    select object_name from dba_objects where object_id=426 or object_id=427
    
    SQL> select * from aux_stats$;
    收集系统信息
    exec dbms_stats.gather_system_stats(gathering_mode=>'start');
    alter system flush_cache;
    select count(*) from fire_t01;
    select * from aux_stats$;
    
    
    初始化参数;
    db_file_multiblock_read_count
    查看数据表一共有多少个块;
    select blocks from user_tables where table_name='FIREFOX';
    
    set autotrace on;
    SELECT * FROM FIREFOX;
    
    SELECT /*+ blocks/k=cost */ 1029/10.4 from dual;
    
    查看数据块大小
    show parameter db_block_size;
    
    
    select * from sys.aux_stats$;
    SYSSTATS_MAIN    IOSEEKTIM    10
    SYSSTATS_MAIN    IOTFRSPEED    4096
    SYSSTATS_MAIN    CPUSPEEDNW    2657.0122
    
    #单块读的时间
    sreadtim = IOSEEKTIM+(size of one block/IOTFRSPEED).
    =10+(8192/4096)=12
    #多块数据块读取的时间
    mreadtim=IOSEEKTIM+(MBRC * SIZE OF one block) / IOTFRSPEED)
    =10+((16*8192)/4096)=42
    io_cost=(1046(blocks)/16)*(42/12)=232.75
    cpu_cost=cpu_cost=cpucycles/(cpuspeed*sreadtim)
    =116192792/(2657.0122*12*1000)=4.48
    cost=io_cost+cpu_cost = 232.75 + 4.48 = 
    如何查看cpucycles:PLAN_TABLE、PLAN_TABLE$;
    
     
    
     
    
    create table firefox1 as select * from firefox where rownum<100;
    create index idx_firefox on firefox(object_id);
    create index idx_firefox1 on firefox1(object_id);
    
    begin
    dbms_stats.gather_table_stats(
    user,
    'firefox',
    cascade => true,
    estimate_percent => null,
    method_opt =>'for all columns size 1'
    );
    end;
    /
    
    begin
    dbms_stats.gather_table_stats(
    user,
    'firefox1',
    cascade => true,
    estimate_percent => null, --完全精确地分析;
    method_opt =>'for all columns size 1'
    );
    end;
    /
    
    alter session set events '10053 trace name context forever,level 12';
    select * from firefox,firefox1 where firefox.object_id = firefox1.object_id;
    alter session set events '10053 trace name context off';
    select * from v$diag_info where name like 'Default%';
    
    对SGA区的动态调整;
    SQL> show sga;
    
    
    网络连接 --session &processes;
    SQL> select username,paddr from v$session where username is not null;
    
    USERNAME    PADDR
    ------------------------------ ----------------
    SYS    00000000F04DAE60
    SYS    00000000F04DAE60
    C##SCOTT    00000000F04DBEA0
    
    select addr from v$process where background is null and addr='00000000F04DAE60';
    select * from v$process where background is null and addr='00000000F04DAE60';
    
    set autotrace on stat;
    select username,paddr from v$session where username is not null;
    select addr from v$process where background is null and addr='00000000F04DAE60';
    
    CBO计算成本时索引的权重修正值.
    show parameter OPTIMIZER_INDEX_COST_ADJ;
    alter session set OPTIMIZER_INDEX_COST_ADJ=10;
    优化器--optimizer_mode
    first_rows [1|10|100|1000]
    first
    优化器--optimizer_dynamic_sampling
    动态采样的级别0to10
    默认值(9i 1, >= 10g 2)
    
    
    查看当前sql/plus 的SID
    SELECT sid, serial# FROM v$session WHERE audsid=SYS_CONTEXT('USERENV','SESSIONID');
    select a.name,b.value from v$sesstat b.v$statname a where a.static#=b.statistic# and a.name ='CPU used by this session' and sid=69;
    
    显示各种文件的I/O统计信息
    - 数据文件,临时文件,控制文件,日志文件,归档文件...
    select file_no,filetype_name,LARGE_READ_MEGABYTES,SMALL_READ_MEGABYTES,SMALL_SYNC_READ_LATENCY from v$iostat_file;
    对象--v$segstat
    获得某对象的各类统计信息;
    select * from v$segstat where obj#=79605 and statistic_name='physical_reads';
  • 相关阅读:
    SqlServer Function 实例
    技术清单
    SQLSERVER 链接服务器执行存储过程
    SQLSERVER 链接服务器
    Oracle 新手语法记录
    MarkDown 语法备份
    C# WebAPI小记
    Rabbit MQ 学习 (二)
    Rabbit MQ 学习 (一)Window安装Erlang环境
    [vue2,nodejs,echarts,json]调查问卷
  • 原文地址:https://www.cnblogs.com/xiaocen/p/4314034.html
Copyright © 2020-2023  润新知