huixz的客栈

SQL> connect / as sysdba
SQL> shutdown abort;
SQL> startup nomount;
SQL> alter database mount exclusive;
SQL> alter system enable restricted session;
SQL> drop database;

好几个月没发帖了，好几个月没看书了，颓废了。 查看全文

SQL> host oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener. This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which services are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the SERVICE_NAME parameter in the connect descriptor of the
// net service name used specifies a service known by the listener.
// - If an easy connect naming connect identifier was used, check that
// the service name specified is a service known by the listener.
// - Check for an event in the listener.log file.

--最近先后1在11g和10g平台遭遇12514错误。

通过查找都是listener.ora有问题，手动修改，做个记录。

增加

SID_DESC =
(GLOBAL_DBNAME = dgtest)
(ORACLE_HOME =/u01/app/oracle/product/11.1.6)
(SID_NAME = dgtest)

新的一年到了，希望自己不要荒废，革命尚未成功，自励一下。

SQL> create or replace Function RevStr(P_str in varchar2) return varchar2 is
2 l_RevStr varchar2(2000);
3 begin
4 for x in 1 .. length(P_str) loop
5 l_RevStr := substr(P_str, x, 1) || l_RevStr;
6 end loop;
7 return l_RevStr;
8 end;
9 /

函数已创建。

SQL> select RevStr('高级会员') from dual;

REVSTR('高级会员')
--------------------------------------------------------------------------------
员会级高

declare
l_Lnum number default 1;
l_Sdrow number default 0;
l_Edrow number default 0;
begin
l_Sdrow :=&Sdrow;
l_Edrow :=&Edrow;
while l_Lnum <= trunc(l_Sdrow/l_Edrow) loop
delete from test1 where rownum <=l_Edrow;
commit;
l_Lnum :=l_Lnum + 1;
end loop;
if mod(l_Sdrow,l_Edrow) <> 0 then
delete from test1 where rownum <=mod(l_Sdrow,l_Edrow);
commit;
end if;
dbms_output.put_line('finished');
expcetion when others
dbms_output.put_line(sqlerrm(sqlcode));
end;
/

explain plan for
select * from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T a /*set a.vip_flag=9 */
where exists (select 1 from acct_ly.target_cust_level b where a.acct_id=b.acct_id )
select * from table(dbms_xplan.display())

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL | OP_BILL_RENT_DETAIL_ITEM_T | | | |
|* 3 | INDEX RANGE SCAN | IX_TARGET_CUST_LEVEL | | | |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "ACCT_LY"."TARGET_CUST_LEVEL" "B" WHERE
"B"."ACCT_ID"=:B1))
3 - access("B"."ACCT_ID"=:B1)

Note: rule based optimization

acct_ly.target_cust_level index(IX_TARGET_CUST_LEVEL)
-----------------------------------------------------------------------------
create index idx_op_acct_id on acct_ly.OP_BILL_RENT_DETAIL_ITEM_T(acct_id) parallel(degree 3) nologging
tablespace ys_owe_dat_2_1;
drop index acct_ly.IX_TARGET_CUST_LEVEL
------------------------------------------------------------------------------

explain plan for
select * from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T /*set vip_flag=9*/ where rowid in
(select a.rowid from acct_ly.OP_BILL_RENT_DETAIL_ITEM_T a, acct_cc.target_cust_level b
where a.acct_id = b.acct_id)
select * from table(dbms_xplan.display()) ;

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | NESTED LOOPS | | | | |
| 2 | VIEW | VW_NSO_1 | | | |
| 3 | SORT UNIQUE | | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | TABLE ACCESS FULL | TARGET_CUST_LEVEL | | | |
|* 6 | INDEX RANGE SCAN | IDX_OP_ACCT_ID | | | |
| 7 | TABLE ACCESS BY USER ROWID| OP_BILL_RENT_DETAIL_ITEM_T | | | |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."ACCT_ID"="B"."ACCT_ID")

Note: rule based optimization

Explain plan for
select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
charge_meter,
charge_trunk,
charge_data,
charge_other,
late_fee,
bad_charge,
ture_charge,
charge_project,
staff_name,
bank_name,
cust_level
from (select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
charge_meter,
charge_trunk,
charge_data,
charge_other,
late_fee,
bad_charge,
ture_charge,
charge_project,
staff_name,
bank_name,
cust_level
from (select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk + charge_data + charge_other + late_fee) /
100 ture_charge,
decode(charge_flag,
'0',
'普通欠费',
'1',
'托收欠费',
'2',
'坏帐欠费',
'9',
'收回欠费',
'其它欠费') charge_project,
staff_name,
bank_name,
cust_level from acct_item_day_sta_t
where 1 = 1
and (region_id_acct like '110504%')
and charge_flag <> 9
and payment_method = 1 and
billing_cycle_id between 400000000 and 400000329
and cust_level in ('0', '1', '2', '3', '4') and billing_type_id in (1,0)
group by acct_id,
acc_nbr,
acct_month,
serv_name,
acct_id,
charge_flag,
service_type,
region_id_serv,
staff_name,
bank_name,
cust_level
union all
select acct_id,
'小计' acc_nbr,
'' serv_name,
'' service_type,
0 region_id_serv,
acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk +
charge_data + charge_other + late_fee) /
100ture_charge,
decode(charge_flag,
'0',
'普通欠费',
'1',
'托收欠费',
'2',
'坏帐欠费',
'9',
'收回欠费',
'其它欠费') charge_project,
'' satff_name,
'' bank_name,
0 cust_level
from acct_item_day_sta_t where 1 = 1 and
(region_id_acct like '110504%') and
charge_flag <> 9 and payment_method = 1 and
billing_cycle_id between 400000000 and
400000329 and
cust_level in ('0', '1', '2', '3', '4') and
billing_type_id in(1, 0)
group by acct_id,
acct_month,
charge_flag,
cust_level
)
order by acct_id, acc_nbr)
Union all
select count(distinct acct_id),
'总计' acc_nbr,
'' serv_name,
'' service_type,
0 region_id_serv,
'' acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk + charge_data + charge_other + late_fee) /
100 ture_charge,
'' charge_project,
'' satff_name,
'' bank_name,
0 cust_level
from acct_item_day_sta_t
where 1 = 1
and (region_id_acct like '110504%')
and charge_flag <> 9
and payment_method = 1
and billing_cycle_id between 400000000 and 400000329
and cust_level in ('0', '1', '2', '3', '4')
and billing_type_id in (1, 0)

--------------------------------------------------
create index BILL.I1_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (SERV_ID);
create index BILL.I2_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (ACCT_ID);
create index BILL.I3_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (BILLING_CYCLE_ID, CHARGE_FLAG, BILLING_TYPE_ID, PAYMENT_METHOD);
--------------------------------------------------

2 ---------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost |
4 ---------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | | | |
6 | 1 | UNION-ALL | | | | |
7 | 2 | VIEW | | | | |
8 | 3 | SORT ORDER BY | | | | |
9 | 4 | VIEW | | | | |
10 | 5 | UNION-ALL | | | | |
11 | 6 | SORT GROUP BY | | | | |
12 | 7 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_DAY_STA_T | | | |
13 | 8 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
14 | 9 | SORT GROUP BY | | | | |
15 | 10 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_DAY_STA_T | | | |
16 | 11 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
17 | 12 | SORT GROUP BY | | | | |
18 | 13 | TABLE ACCESS BY INDEX ROWID | ACCT_ITEM_DAY_STA_T | | | |
19 | 14 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
20 ---------------------------------------------------------------------------------------------
21
22 Note: rule based optimization, PLAN_TABLE' is old version
--------------------------------------------------------------------------
select * from v$session_wait where sid =59;
59 6157 db file sequential read file# 890 000000000000037A block# 249017 000000000003CCB9 blocks 1 0000000000000001 0 0 WAITING
--------------------------------------------------------------------------
执行时间:--612s

-----------------------------------
drop index BILL.I3_ACCT_ITEM_DAY_STA_T
-----------------------------------
1
2 --------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost |
4 --------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | | | |
6 | 1 | UNION-ALL | | | | |
7 | 2 | VIEW | | | | |
8 | 3 | SORT ORDER BY | | | | |
9 | 4 | VIEW | | | | |
10 | 5 | UNION-ALL | | | | |
11 | 6 | SORT GROUP BY | | | | |
12 | 7 | TABLE ACCESS FULL| ACCT_ITEM_DAY_STA_T | | | |
13 | 8 | SORT GROUP BY | | | | |
14 | 9 | TABLE ACCESS FULL| ACCT_ITEM_DAY_STA_T | | | |
15 | 10 | SORT GROUP BY | | | | |
16 | 11 | TABLE ACCESS FULL | ACCT_ITEM_DAY_STA_T | | | |
17 --------------------------------------------------------------------------------
18
19 Note: rule based optimization, PLAN_TABLE' is old version

select * from v$session_wait where sid =59;
1 59 4131 db file scattered read file# 1058 0000000000000422 block# 108473 000000000001A7B9 blocks 16 0000000000000010 0 0 WAITING
执行时间:--270s

--Unix

Memory: 2617704K (860220K) real, 2774560K (942392K) virtual, 38440908K free Page# 1/19

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 ? 20219 oracle 234 20 6390M 5384K run 31:02 100.13 99.96 oracletest1
--
select * from v$session where paddr in (select addr from v$process where spid = &Pid)--20219
--
select * from v$session_wait where sid = 50
50 383 db file sequential read file# 119 0000000000000077 block# 35698 0000000000008B72 blocks 1 0000000000000001 -1 9 WAITED KNOWN TIME
50 39582 latch free address 1.3835058074286E19 C00000046CB758D8 number 98 0000000000000062 tries 0 00 -1 1 WAITED KNOWN TIME XXXXXXXXXXXXXXX U
select * from v$latchname where latch# = 98;--P2
98 cache buffers chains
select * from v$event_name where name = 'latch free'
4 latch free address number tries [file#,block#,tries]
-------------------------------------------------------------------------------------------------
通过v$sqltext关联之后发现如下语句
--------------------------------------------------------------------------------------------------
select a.Approve_Serial_Nbr,
a.Approve_Type,
a.approve_flag,
a.acct_id,
a.Approve_charge,
a.Approve_Staff_Id,
a.Approve_Site_Id,
a.Approve_User_Name,
a.Approve_reason,
to_char(a.approve_date, 'mm/dd hh24:mi'),
a.Used_flag,
b.acc_nbr,
b.approve_value,
b.acct_month,
b.acct_item_type_id
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id
---------------------------------------------------------------------------------
1
2 --------------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 --------------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 11 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 11 | | |
7 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | APPROVE_ITEM_T | 1 | 82 | 1 | | |
8 | 3 | NESTED LOOPS | | 1 | 336 | 2 | | |
9 | 4 | PARTITION RANGE ALL | | | | | 1 | 2 |
10 | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 1 | 1 | 2 |
11 | 6 | INDEX RANGE SCAN | I1_APPROVE_SERIAL_T | 1 | | 3 | 1 | 2 |
12 | 7 | PARTITION RANGE ALL | | | | | 1 | 2 |
13 | 8 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 1 | | 2 | 1 | 2 |
14 --------------------------------------------------------------------------------------------------------------
15
16 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------------------------------------------
检查各表数据
select count(*) from bill_cc.approve_serial_t --1353163
select count(*) from bill_cc.approve_item_t --1306518
--一般来说当2个大表进行连接涉及数据量较大时hash_jion比nested loops更为合适!
如果两个表都走索引的话后果很可能是恶梦。

------------------------------------
------------------------------------
下面强制执行全表扫描模式
------------------------------------
select //用时17s
/*+ full(a) full(b) */ *
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id

1
2 -------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 734 | 14 | | |
6 | 1 | SORT ORDER BY | | 1 | 734 | 14 | | |
7 | 2 | HASH JOIN | | 1 | 734 | 5 | | |
8 | 3 | PARTITION RANGE ALL| | | | | 1 | 2 |
9 | 4 | TABLE ACCESS FULL | APPROVE_SERIAL_T | 1 | 544 | 2 | 1 | 2 |
10 | 5 | PARTITION RANGE ALL| | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 1 | 190 | 2 | 1 | 2 |
12 -------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------
察看原来的索引如下:
create index BILL_CC.I1_APPROVE_ITEM_T on BILL_CC.APPROVE_ITEM_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);
create index BILL_CC.I1_APPROVE_SERIAL_T on BILL_CC.APPROVE_SERIAL_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);

分析索引的创建应该是不合理的
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;

create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);

--考虑acct_id被经常用
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.approve_serial_t(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

--最后确认一下其他情形是否有对原来索引有所依赖?
--最终计划如下
--------------------------------------------------------
1
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 1623 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 1623 | | |
7 | 2 | HASH JOIN | | 1 | 336 | 1614 | | |
8 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 2 | ROWID | ROW L |
9 | 4 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 43 | | 1 | | |
10 | 5 | PARTITION RANGE ALL | | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 34 | 2788 | 1611 | 1 | 2 |
12 -----------------------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--进一步考虑前台查询组合繁多,approve_type类型不断变化,所以这种
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);
是不能通用的，为什么没走skip index还是值得考虑的？

应该更改为:[ysdb]
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;
--
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(approve_Type);
--
acct_id索引的建立方式不变.
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.I2_APPROVE_ITEM_T(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

1
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 1623 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 1623 | | |
7 | 2 | HASH JOIN | | 1 | 336 | 1614 | | |
8 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 2 | ROWID | ROW L |
9 | 4 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 10831 | | 1 | | |
10 | 5 | PARTITION RANGE ALL | | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 34 | 2788 | 1611 | 1 | 2 |
12 -----------------------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

补充总结:根据v$session_wait可以看到等待事件是db file sequential read，通常来说索引读是好的,
会很快，但是如果过多的索引读就令人畏惧的。

--中间表使用，不适用于最终表，否则可能影响查询计划
create table hxz_test_2 parallel(degree 4) nologging as select * from serv_t;--56.296
create index idx_hxz_test_2 on hxz_test_2(serv_id) parallel(degree 4) nologging;
--------------------------------------------------------------------------------------------
create index idx_hxz_test_2 on hxz_test_2(ioid_id0) /*parallel(degree 4)*/ nologging;--208.219
create index idx_hxz_test_3 on hxz_test_2(ioid_id0) parallel(degree 8) nologging;--30.344
--
select count(*) from hxz_t_s--67.875
alter table hxz_t_s parallel (degree 4)
select count(*) from hxz_t_s--14s
--
alter session enable parallel dml;
--并行的时候可以看到
小结：并行的实质是创建了多个session在后台执行同一任务
select *　from v$px_session;
C000000183C756D0 42 4844 42
C000000183C71770 24 6105 42 4844 1 1 1 1 4 4
C000000185C86D88 82 7096 42 4844 1 1 1 2 4 4
C000000183C70CE0 21 12192 42 4844 1 1 1 3 4 4
C000000183C7E020 81 3063 42 4844 1 1 1 4 4 4

--------------------------------------------------------------------------------------------------
select a.Approve_Serial_Nbr,
a.Approve_Type,
a.approve_flag,
a.acct_id,
a.Approve_charge,
a.Approve_Staff_Id,
a.Approve_Site_Id,
a.Approve_User_Name,
a.Approve_reason,
to_char(a.approve_date, 'mm/dd hh24:mi'),
a.Used_flag,
b.acc_nbr,
b.approve_value,
b.acct_month,
b.acct_item_type_id
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id
---------------------------------------------------------------------------------
1
2 --------------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 --------------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 11 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 11 | | |
7 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | APPROVE_ITEM_T | 1 | 82 | 1 | | |
8 | 3 | NESTED LOOPS | | 1 | 336 | 2 | | |
9 | 4 | PARTITION RANGE ALL | | | | | 1 | 2 |
10 | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 1 | 1 | 2 |
11 | 6 | INDEX RANGE SCAN | I1_APPROVE_SERIAL_T | 1 | | 3 | 1 | 2 |
12 | 7 | PARTITION RANGE ALL | | | | | 1 | 2 |
13 | 8 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 1 | | 2 | 1 | 2 |
14 --------------------------------------------------------------------------------------------------------------
15
16 Note: cpu costing is off, PLAN_TABLE' is old version

--一般来说当2个大表进行连接涉及数据量较大时hash_jion比nested loops更为合适!
如果两个表都走索引的话后果很可能是恶梦。

--Unix

Memory: 2617704K (860220K) real, 2774560K (942392K) virtual, 38440908K free Page# 1/19

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 ? 20219 oracle 234 20 6390M 5384K run 31:02 100.13 99.96 oracletest1
--
select * from v$session where paddr in (select addr from v$process where spid = &Pid)--20219
--
select * from v$session_wait where sid = 50
1 50 383 db file sequential read file# 119 0000000000000077 block# 35698 0000000000008B72 blocks 1 0000000000000001 -1 9 WAITED KNOWN TIME

------------------------------------
------------------------------------
select //用时17s
/*+ full(a) full(b) */ *
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id

1
2 -------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 734 | 14 | | |
6 | 1 | SORT ORDER BY | | 1 | 734 | 14 | | |
7 | 2 | HASH JOIN | | 1 | 734 | 5 | | |
8 | 3 | PARTITION RANGE ALL| | | | | 1 | 2 |
9 | 4 | TABLE ACCESS FULL | APPROVE_SERIAL_T | 1 | 544 | 2 | 1 | 2 |
10 | 5 | PARTITION RANGE ALL| | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 1 | 190 | 2 | 1 | 2 |
12 -------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------
察看原来的索引如下:
create index BILL_CC.I1_APPROVE_ITEM_T on BILL_CC.APPROVE_ITEM_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);
create index BILL_CC.I1_APPROVE_SERIAL_T on BILL_CC.APPROVE_SERIAL_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);

分析索引的创建应该是不合理的
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;

create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);

--考虑acct_id被经常用
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.approve_serial_t(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

--最后确认一下其他情形是否有对原来索引有所依赖?
--最终计划如下
--------------------------------------------------------
1
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 1623 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 1623 | | |
7 | 2 | HASH JOIN | | 1 | 336 | 1614 | | |
8 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 2 | ROWID | ROW L |
9 | 4 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 43 | | 1 | | |
10 | 5 | PARTITION RANGE ALL | | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 34 | 2788 | 1611 | 1 | 2 |
12 -----------------------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--进一步考虑前台查询组合繁多,approve_type类型不断变化,所以这种
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);
是不能通用的，为什么没走skip index还是值得考虑的？

应该更改为:[ysdb]
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;
--
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(pprove_Type);
--
acct_id索引的建立方式不变.
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.I2_APPROVE_ITEM_T(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

declare
cursor c1(p_id int) is select * from t where id=p_id ;
begin
open c1( 1) ;
end;
---------------------------
create table hxz_01 (a int);
insert into hxz_01 values(1);
insert into hxz_01 values(2);
insert into hxz_01 values(2);
insert into hxz_01 values(3);
commit;
/
set serveroutput on
declare
cursor l_cursor(p int) is select * from hxz_01 where a = p;
l_tmp hxz_01%rowtype;
begin
open l_cursor(2);
loop
fetch l_cursor into l_tmp;
exit when l_cursor%notfound;
dbms_output.put_line(l_tmp.a);
end loop;
end;
/

--Ref cursor exampl
declare
type refcursor is ref cursor;
l_tmp hxz_01%rowtype;
l_cursor refcursor;
begin
open l_cursor for 'select * from hxz_01 where a = :p' using 2;
loop
fetch l_cursor into l_tmp;
exit when l_cursor%notfound;
dbms_output.put_line(l_tmp.a);
end loop;
end;
/

declare
cursor c is select * from hxz_01;
l_rec hxz_01%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line(l_rec.a);
end loop;
close c;
end;


declare
cursor c is select * from hxz_01;
TYPE fetch_array IS TABLE OF hxz_01%ROWTYPE;
l_rec fetch_array;

begin
open c;
fetch c bulk collect into l_rec;
for i in 1..l_rec.count loop
dbms_output.put_line(l_rec(i).a);
end loop;
close c;
end;

----------------------------------------------------------
--Example One
declare
cursor c is select * from hxz_01;
type type_t is table of hxz_01%rowtype;
l_rec type_t;
begin
open c;
fetch c bulk collect into l_rec;
forall i in 1..l_rec.count
insert into hxz_02 values l_rec(i);
commit;
close c;
end;
--Example Two
declare
cursor c is select * from hxz_01;
type type_t is table of hxz_01%rowtype;
l_rec type_t;
begin
open c;
fetch c bulk collect into l_rec;
for i in l_rec.first..l_rec.last loop
dbms_output.put_line(l_rec(i).a||l_rec(i).b);
end loop;
end;

--Example Three
declare
cursor c is select * from hxz_01;
l_rec hxz_01%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
dbms_output.put_line(l_rec.a||l_rec.b);
end loop;
end;

-----------------------------------------------------
--Example Four
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;

d_array myarray;

declare
type type_t is table of hxz_01.a%type index by binary_integer;
l_rec type_t;
begin
l_rec(1) :='1';
l_rec(2) :='2';
l_rec(3) :='3';
forall i in l_rec.first..l_rec.last
delete hxz_02 where a = l_rec(i);
for i in l_rec.first..l_rec.last loop
dbms_output.put_line(sql%bulk_rowcount(i));
end loop;
/* commit;*/
end;
------------------------------------------------------------------
declare
cursor c is select * from hxz_01;
type type_t is table of hxz_01%rowtype;
l_rec type_t;
begin
select * bulk collect into l_rec from hxz_01;
for i in 1..l_rec.last
loop
dbms_output.put_line(l_rec(i).a);
if l_rec(i).a = '3' then
dbms_output.put_line(i);
dbms_output.put_line(l_rec(i).b);
l_rec.delete(i);
end if;
end loop;
end;

select s.sid ,s.serial#,s.PADDR 进程地址,time_remaining 剩余时间,elapsed_seconds 已用时间,round(trim(substr(message,instr(message,':',-1)+1,instr(message,'out',-1)-instr(message,':',-1)-1))/
trim(substr(message,instr(message,'of',-1)+2,instr(message,'Blocks',-1)-instr(message,'of',-1)-2))*100,2)||'%' 完成比例
from v$session_longops a,v$session s where a.sid=s.sid and time_remaining<>0

-脚本说明：查看占io较大的正在运行的session

　　SELECT se.sid,
　　　　se.serial#,
　　　　pr.SPID,
　　　　se.username,
　　　　se.status,
　　　　se.terminal,
　　　　se.program,
　　　　se.MODULE,
　　　　se.sql_address,
　　　　st.event,
　　　　st.p1text,
　　　　si.physical_reads,
　　　　si.block_changes
　　FROM v$session se,
　　　v$session_wait st,
　　　　v$sess_io si,
　　　　v$process pr
　　WHERE st.sid=se.sid
　　　　AND st.sid=si.sid
　　　　AND se.PADDR=pr.ADDR
　　　　AND se.sid>6
　　　　AND st.wait_time=0
　　　　AND st.event NOT LIKE '%SQL%'
　　ORDER BY physical_reads DESC

SQL> desc v$session_wait
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
SID NUMBER Y SESSION_ID
SEQ# NUMBER Y SESSION_SEQ#
EVENT VARCHAR2(64) Y WAIT_EVENT
P1TEXT VARCHAR2(64) Y P1解释
P1 NUMBER Y P1 VALUE
P1RAW RAW(8) Y P1 VALUE(16进制)
P2TEXT VARCHAR2(64) Y P2解释
P2 NUMBER Y P2 VALUE
P2RAW RAW(8) Y P2 VALUE(16进制)
P3TEXT VARCHAR2(64) Y P2解释
P3 NUMBER Y P2 VALUE
P3RAW RAW(8) Y P2 VALUE(16进制)
WAIT_TIME NUMBER Y 等待时间(毫秒)
SECONDS_IN_WAIT NUMBER Y
STATE VARCHAR2(19) Y