create table a0001 as select * from dba_objects; create table a0002 as select * from dba_objects where 1=2; create index iOBJECT_ID on a0001(OBJECT_ID) create index iCREATED on a0001(CREATED) set autotr traceonly insert into a00021 select /*+ INDEX_JOIN(a0001 iOBJECT_ID iCREATED ) */ floor((rownum-1)/5) as rr,OBJECT_ID from a0001 where CREATED=sysdate-1000 and OBJECT_ID is not null;
相关文档：
Oracle® Database Performance Tuning Guide
Optimizer Hints
https://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-tuning-guide.pdf
数据准备 create table b001( b1 varchar2(100), b2 varchar2(100), b3 varchar2(100), b4 varchar2(100), b5 varchar2(100), b6 varchar2(100), b7 varchar2(100), b8 date); insert into b001 select trunc(dbms_random.value(1000,9000)) , rpad(trunc(dbms_random.value(1000,9000)),60,'B2') , rpad(trunc(dbms_random.value(1000,9000)),60,'B3') , rpad(trunc(dbms_random.value(1000,9000)),60,'B4') , rpad(trunc(dbms_random.value(1000,9000)),60,'B5') , rpad(trunc(dbms_random.value(1000,9000)),60,'B6') , rpad(trunc(dbms_random.value(1000,9000)),60,'B7') , sysdate-trunc(dbms_random.value(1,9000)) from dual connect by level <100000 create index ab8 on b001(b8) create index ab4 on b001(b4) create table b0021(rr number,b4 varchar2(100)) SQL> select dbms_sql_monitor.report_sql_monitor(sql_id=>'10gh8x2650tzr',sql_exec_id=>'16777216',type=>'TEXT') from dual; SQL Monitoring Report SQL Text ------------------------------ insert into b0021 select /*+ monitor INDEX_JOIN(b001 ab8 ab4 ) */ floor((rownum-1)/5) as rr,b4 from b001 where b8<=sysdate and b4 is not null Global Information ------------------------------ Status : DONE Instance ID : 1 Session : SYS (45:47786) SQL ID : 10gh8x2650tzr SQL Execution ID : 16777216 Execution Started : 08/12/2024 14:37:07 First Refresh Time : 08/12/2024 14:37:07 Last Refresh Time : 08/12/2024 14:37:09 Duration : 2s Module/Action : sqlplus@redhat76 (TNS V1-V3)/- Service : SYS$USERS Program : sqlplus@redhat76 (TNS V1-V3) Global Stats =================================================================================== | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Write | Write | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | =================================================================================== | 1.97 | 1.41 | 0.16 | 0.40 | 39641 | 937 | 61MB | 54 | 30MB | 写请求 30M =================================================================================== SQL> select 30*1024*1024/54 from dual; 582542.222 SQL> SQL> select 30*1024*1024/54/8192 from dual; 71.1111111 ---每次写70来个快 SQL Plan Monitoring Details (Plan Hash Value=1494519326) ==================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) | ==================================================================================================================================================================== | 0 | INSERT STATEMENT | | | | 1 | +2 | 1 | 0 | | | . | | | | 1 | LOAD TABLE CONVENTIONAL | B0021 | | | 2 | +1 | 1 | 0 | | | . | | | | 2 | COUNT | | | | 1 | +2 | 1 | 410K | | | . | | | | 3 | VIEW | index$_join$_002 | 371K | 17275 | 1 | +2 | 1 | 410K | | | . | | | | 4 | HASH JOIN | | | | 1 | +2 | 1 | 410K | | | 29MB | | | | 5 | INDEX FAST FULL SCAN | AB8 | 371K | 2073 | 1 | +2 | 1 | 410K | 226 | 12MB | . | | | | 6 | INDEX FAST FULL SCAN | AB4 | 371K | 7922 | 1 | +2 | 1 | 410K | 642 | 49MB | . | | | ==================================================================================================================================================================== SQL> set linesize 300 SQL> col segment_name for a30 SQL> select bytes/1024/1024,segment_name from dba_segments where segment_name IN ('B001','AB8','AB4','B0021'); 184 B001 13 AB8 50 AB4 32 B0021 ******* 空表插入数据后大小！ SQL> SQL> select sum(length(b4))/1024/1024 from b0021; 23.4601021 SQL> SQL> select sum(length(b4))/1024/1024 from b001; 23.4601021 SQL> SQL> select sum(length(b4))/1024/1024+sum(length(rr))/1024/1024 from b0021; 25.362134 SQL> --如何获取一个表的大小的增量！ 如何获取一个表数据增量大小 SQL> set linesize 300 SQL> select a.*,to_char(scn_to_timestamp(DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID)),'yyyy-mm-dd hh24:mi:ss') insert_tm from b0021 a 2 where scn_to_timestamp(DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID))>to_date('201911','yyyymm') 3 and scn_to_timestamp(DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID))<to_date('202011','yyyymm') 4 and rownum<10; RR B4 INSERT_TM ---------- ---------------------------------------------------------------- ------------------- 0 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 0 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 0 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 0 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 0 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 1 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 1 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 1 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 1 1000B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4B4 2019-11-23 16:30:15 9 rows selected. SQL> 通过时间获取增加的数据量！或者通过源表（数据未改的情况）获取数据量！建议备库操作
浙公网安备 33010602011771号