Home » RDBMS Server » Performance Tuning » My indexes was not active (Oracle 10g, Windows 32bit)
| My indexes was not active [message #461859] |
Mon, 21 June 2010 23:21  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I consider about this query:
SELECT /*+ index(a mt_ngaynhap_idx) */
loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a. ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss') --dngay
--the duoc chuyen tu ATM sang tu nhung ngay truoc
AND a. dvcq_id = 1 --dvcq_id
AND (a. trangthai_id = 1 --the chua ban
OR a. ngayban >= TO_DATE ('06-05-2010', 'dd-mm-yyyy hh24:mi:ss')
--hoac the da ban tu ngay duoc xem xet
)
GROUP BY a. loaithe_id;
Tkprof:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 8 73.59 630.10 379466 449323 0 97
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 73.62 630.12 379466 449323 0 97
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70
Rows Row Source Operation
------- ---------------------------------------------------
97 HASH GROUP BY (cr=449323 pr=379466 pw=0 time=630107459 us)
19935257 TABLE ACCESS BY INDEX ROWID TT_MATHES (cr=449323 pr=379466 pw=0 time=697752419 us)
19945300 INDEX RANGE SCAN MT_NGAYNHAP_IDX (cr=61228 pr=60946 pw=0 time=199600773 us)(object id 159780)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
db file sequential read 379466 0.39 563.18
SQL*Net message from client 8 0.00 0.01
********************************************************************************
1. Description about table & index:
1. Table
Rows:
log_payall@PAYALL> select count(1)
2 from payall_new.tt_mathes
3 /
COUNT(1)
------------
21029729
1 row selected.
log_payall@PAYALL> desc payall_new.tt_mathes
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
ID NOT NULL NUMBER(16)
LOAITHE_ID NOT NULL NUMBER(6)
PHIEUBAN_ID NUMBER(16)
TRANGTHAI_ID NUMBER(3)
SERIAL VARCHAR2(50)
MA_BIMAT VARCHAR2(100)
NGAY_SX DATE
NGAY_HET_HAN DATE
NGAY_NHAP DATE
NGUOI_NHAP VARCHAR2(30)
NGAY_CN DATE
CHUTHICH VARCHAR2(200)
SOPHIEU NUMBER(6)
DVCQ_ID NOT NULL NUMBER(6)
GIA_NHAP NUMBER(12)
NGAYBAN DATE
b. Indexes:
log_payall@PAYALL> select owner, index_name, table_name, to_char(last_analyzed, 'dd/mm/yyyy hh24:mi:ss'
2 from dba_indexes
3 where owner='PAYALL_NEW'
4 and table_name='TT_MATHES'
5 /
OWNER TABLE_NAME INDEX_NAME ANALYZED
------------ ------------ -------------------- ------------------------
PAYALL_NEW TT_MATHES MT_PHIEUBAN_ID_IDX 21/06/2010 21:49:29
PAYALL_NEW TT_MATHES MT_NGAYBAN_ID_IDX 21/06/2010 21:52:58
PAYALL_NEW TT_MATHES MT_NGAYNHAP_IDX 21/06/2010 21:56:32
PAYALL_NEW TT_MATHES MT_TT_LT_ID_IDX 21/06/2010 21:59:53
PAYALL_NEW TT_MATHES PK_MATHE 21/06/2010 22:03:23
log_payall@PAYALL> select sample_size
2 from dba_indexes
3 where owner='PAYALL_NEW'
4 and table_name='TT_MATHES';
SAMPLE_SIZE
-----------
21029729
21074654
21074654
21079654
21079654
log_payall@PAYALL> select sample_size, num_rows
2 from dba_indexes
3 where owner='PAYALL_NEW'
4 and table_name='TT_MATHES';
SAMPLE_SIZE NUM_ROWS
----------- ----------
21029729 21029729
21074654 21074654
21074654 21074654
21079654 21079654
21079654 21079654
log_payall@PAYALL>
2. Cases:
a. Test activity of index
log_payall@PAYALL> select loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 from payall_new.tt_mathes
3 where phieuban_id=1
4 group by loaithe_id
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 901939918
--------------------------------------------------------------------------------
-------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
| 0 | SELECT STATEMENT | | 64 | 1408 |
9 (12)| 00:00:01 |
| 1 | HASH GROUP BY | | 64 | 1408 |
9 (12)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TT_MATHES | 99 | 2178 |
8 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MT_PHIEUBAN_ID_IDX | 99 | |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PHIEUBAN_ID"=1)
log_payall@PAYALL> select loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 from payall_new.tt_mathes
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
4 group by loaithe_id
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 2548407957
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 2625 | 83644 (9)| 00:16:44 |
| 1 | HASH GROUP BY | | 105 | 2625 | 83644 (9)| 00:16:44 |
|* 2 | TABLE ACCESS FULL| TT_MATHES | 19M| 475M| 79901 (5)| 00:15:59 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NGAY_NHAP"<TO_DATE('06-06-2010','dd-mm-yyyy hh24:mi:ss'))
log_payall@PAYALL>
b. CAS and testing using other schema:
log_payall@PAYALL> set autotrace off
log_payall@PAYALL> create table test as
2 select * from payall_new.tt_mathes
3 where 1=0;
Table created.
log_payall@PAYALL> insert into test select * from payall_new.tt_mathes
2 where rownum<=100;
100 rows created.
log_payall@PAYALL> commit;
Commit complete.
log_payall@PAYALL> create index mt_ngaynhap_idx on test(ngay_nhap);
Index created.
log_payall@PAYALL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'LOG_PAYALL',
4 tabname=>'TEST',
5 method_opt=>'for all indexed columns size auto',
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
log_payall@PAYALL> select index_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi:
ss') analyzed
2 from dba_indexes
3 where owner='LOG_PAYALL'
4 and table_name='TEST'
5 /
INDEX_NAME ANALYZED
-------------------- -------------------------
MT_NGAYNHAP_IDX 22/06/2010 11:06:48
log_payall@PAYALL> select loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 from test
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
4 group by loaithe_id
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1999862804
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 100 | 3400 | 4
(25)| 00:00:01 |
| 1 | HASH GROUP BY | | 100 | 3400 | 4
(25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 100 | 3400 | 3
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MT_NGAYNHAP_IDX | 100 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("NGAY_NHAP"<TO_DATE('06-06-2010','dd-mm-yyyy hh24:mi:ss'))
log_payall@PAYALL> set autotrace off
log_payall@PAYALL> create index mt_dvcq_id_idx on test(dvcq_id);
Index created.
log_payall@PAYALL> create index mt_trangthai_id_idx on test(trangthai_id);
Index created.
log_payall@PAYALL> create index mt_ngayban_idx on test(ngayban);
Index created.
log_payall@PAYALL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'LOG_PAYALL',
4 tabname=>'TEST',
5 method_opt=>'for all indexed columns size auto',
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
log_payall@PAYALL> select index_name, to_char(last_analyzed,'dd/mm/yyyy hh24:mi:
ss') analyzed
2 from dba_indexes
3 where owner='LOG_PAYALL'
4 and table_name='TEST'
5 /
INDEX_NAME ANALYZED
-------------------- -------------------------
MT_NGAYNHAP_IDX 22/06/2010 11:12:30
MT_DVCQ_ID_IDX 22/06/2010 11:12:30
MT_TRANGTHAI_ID_IDX 22/06/2010 11:12:30
MT_NGAYBAN_IDX 22/06/2010 11:12:30
log_payall@PAYALL> set autotrace traceonly explain
log_payall@PAYALL> select loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 from test
3 where ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
4 and dvcq_id = 1
5 and (trangthai_id = 1 or ngayban >= TO_DATE ('06-05-2010', 'dd-mm-yyyy hh24
:mi:ss'))
6 group by loaithe_id
7 /
Execution Plan
----------------------------------------------------------
Plan hash value: 1999862804
--------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
--------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 99 | 4752 | 4
(25)| 00:00:01 |
| 1 | HASH GROUP BY | | 99 | 4752 | 4
(25)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST | 99 | 4752 | 3
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | MT_NGAYNHAP_IDX | 100 | | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DVCQ_ID"=1 AND ("TRANGTHAI_ID"=1 OR
"NGAYBAN">=TO_DATE('06-05-2010','dd-mm-yyyy hh24:mi:ss')))
3 - access("NGAY_NHAP"<TO_DATE('06-06-2010','dd-mm-yyyy hh24:mi:ss'))
log_payall@PAYALL>
The first statement query (main select ...) did not use our indexes althrough they were gathered stats at the last night.
I'll test by deviding step by step:
1- Testing the main index if they're active ==> They are active, but, only 2 indexes on NGAY_NHAP and NGAYBAN column
did not run.
2- Testing by create as select into my schema (log_payall), created indexes, view explain, the indexes, all of them are
active.
How did the main index not active, although they are:
- Be gathered stats
- Sample size & num rows was across on table (if the sample size < 20% total rows, they are out of stats).
May you clarify more?
Thank you!
|
|
|
|
| Re: My indexes was not active [message #461862 is a reply to message #461859] |
Tue, 22 June 2010 00:14  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Then, I drop 2 index MT_NGAYNHAP_IDX and MT_NGAYBAN_ID_IDX, and recreate them
payall_new@PAYALL> drop index mt_ngayban_id_idx;
Index dropped.
Elapsed: 00:00:00.21
payall_new@PAYALL> drop index mt_ngaynhap_idx;
Index dropped.
Elapsed: 00:00:00.11
payall_new@PAYALL> create index ma_ngayban_id_idx on tt_mathes(ngayban);
Index created.
Elapsed: 00:03:42.53
payall_new@PAYALL> create index mt_ngaynhap_idx on tt_mathes(ngay_nhap);
Index created.
Elapsed: 00:03:20.31
payall_new@PAYALL> create index mt_dvcq_id_idx on tt_mathes(dvcq_id);
Index created.
Elapsed: 00:01:42.00
payall_new@PAYALL> set autotrace traceonly explain
payall_new@PAYALL> SELECT loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 FROM payall_new.tt_mathes a
3 WHERE a.ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss') --dn
gay
4 --the duoc chuyen tu ATM sang tu nhung ngay truoc
5 AND a.dvcq_id = 1 --dvcq
_id
6 AND ( a.trangthai_id = 1 --the chua
ban
7 OR a.ngayban >= TO_DATE ('06-05-2010', 'dd-mm-yyyy hh24:mi:ss')
8 --hoac the da ban tu ngay duoc xem xet
9 )
10 GROUP BY a.loaithe_id
11 /
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2548407957
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 4095 | 84079 (10)| 00:16:49 |
| 1 | HASH GROUP BY | | 105 | 4095 | 84079 (10)| 00:16:49 |
|* 2 | TABLE ACCESS FULL| TT_MATHES | 19M| 740M| 80343 (6)| 00:16:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."NGAY_NHAP"<TO_DATE('06-06-2010','dd-mm-yyyy
hh24:mi:ss') AND ("A"."TRANGTHAI_ID"=1 OR
"A"."NGAYBAN">=TO_DATE('06-05-2010','dd-mm-yyyy hh24:mi:ss')) AND
"A"."DVCQ_ID"=1)
But, they're same as old plan 
payall_new@PAYALL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'PAYALL_NEW',
4 tabname=>'TT_MATHES',
5 method_opt=>'for all indexed columns size auto',
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:36.12
payall_new@PAYALL>
payall_new@PAYALL> SELECT loaithe_id, COUNT (*) soluong, SUM (gia_nhap) tien
2 FROM payall_new.tt_mathes a
3 WHERE a.ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss') --dn
gay
4 --the duoc chuyen tu ATM sang tu nhung ngay truoc
5 AND a.dvcq_id = 1 --dvcq
_id
6 AND ( a.trangthai_id = 1 --the chua
ban
7 OR a.ngayban >= TO_DATE ('06-05-2010', 'dd-mm-yyyy hh24:mi:ss')
8 --hoac the da ban tu ngay duoc xem xet
9 )
10 GROUP BY a.loaithe_id;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2548407957
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 936 | 84336 (10)| 00:16:53 |
| 1 | HASH GROUP BY | | 24 | 936 | 84336 (10)| 00:16:53 |
|* 2 | TABLE ACCESS FULL| TT_MATHES | 19M| 742M| 80589 (6)| 00:16:08 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."NGAY_NHAP"<TO_DATE('06-06-2010','dd-mm-yyyy
hh24:mi:ss') AND ("A"."TRANGTHAI_ID"=1 OR
"A"."NGAYBAN">=TO_DATE('06-05-2010','dd-mm-yyyy hh24:mi:ss')) AND
"A"."DVCQ_ID"=1)
|
|
|
|
| Re: My indexes was not active [message #461877 is a reply to message #461859] |
Tue, 22 June 2010 01:11 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
select COUNT (*) soluong
2 from payall_new.tt_mathes
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
What is the result for the above sql?
Oracle optimizer might prefers not to choose index in this case.
Can you please provide the output for the above sql?
log_payall@PAYALL> set autotrace off
log_payall@PAYALL> create table test as
2 select * from payall_new.tt_mathes
3 where 1=0;
Table created.
log_payall@PAYALL> insert into test select * from payall_new.tt_mathes
2 where rownum<=100;
And whats the result for the below sql :
select COUNT (*) soluong
2 from test
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
** The below code is not tested. Please verify if it returns the same result set and if performance can be improved.Check if it helps!
SELECT loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban BETWEEN To_date ('06-05-2010',
'dd-mm-yyyy')
AND
To_date ('06-06-2010', 'dd-mm-yyyy') - 1/24/60/60
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
GROUP BY a.loaithe_id;
Regards
Ved
[Updated on: Tue, 22 June 2010 01:32] Report message to a moderator |
|
|
|
| Re: My indexes was not active [message #461919 is a reply to message #461877] |
Tue, 22 June 2010 02:25 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Its_me_ved wrote on Tue, 22 June 2010 13:11
select COUNT (*) soluong
2 from payall_new.tt_mathes
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
What is the result for the above sql?
Oracle optimizer might prefers not to choose index in this case.
Can you please provide the output for the above sql?
payall_new@PAYALL> select count(*)
2 from tt_mathes
3 where ngay_nhap<to_date('06-06-2010','dd-mm-yyyy');
COUNT(*)
----------
19945300
payall_new@PAYALL>
I understand what you want to say, however, the result > 40% of total rows.
Quote:
log_payall@PAYALL> set autotrace off
log_payall@PAYALL> create table test as
2 select * from payall_new.tt_mathes
3 where 1=0;
Table created.
log_payall@PAYALL> insert into test select * from payall_new.tt_mathes
2 where rownum<=100;
And whats the result for the below sql :
select COUNT (*) soluong
2 from test
3 WHERE ngay_nhap < TO_DATE ('06-06-2010', 'dd-mm-yyyy hh24:mi:ss')
Yes, it's a simple test, it's not true. I'll try to insert about ~10mil rows into TEST, and feed back to you as soon as possible.
Quote:
** The below code is not tested. Please verify if it returns the same result set and if performance can be improved.Check if it helps!
SELECT loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban BETWEEN To_date ('06-05-2010',
'dd-mm-yyyy')
AND
To_date ('06-06-2010', 'dd-mm-yyyy') - 1/24/60/60
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
GROUP BY a.loaithe_id;
Regards
Ved
Thank you for your quick reply. I see, it's difficult to use hint, I tried before to post here by remove the hint in statement, of course, it was slower than hint, because FTS
payall_new@PAYALL> set autotrace traceonly explain
payall_new@PAYALL> SELECT loaithe_id,
2 COUNT (*) soluong,
3 SUM (gia_nhap) tien
4 FROM payall_new.tt_mathes a
5 WHERE a.dvcq_id = 1
6 AND ( a.trangthai_id = 1
7 OR ( a.ngayban BETWEEN To_date ('06-05-2010',
8 'dd-mm-yyyy')
9 AND
10 To_date ('06-06-2010', 'dd-mm-yyyy') - 1/24/60/60
11 ) )
12 AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
13 GROUP BY a.loaithe_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2548407957
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24 | 936 | 82694 (8)| 00:16:33 |
| 1 | HASH GROUP BY | | 24 | 936 | 82694 (8)| 00:16:33 |
|* 2 | TABLE ACCESS FULL| TT_MATHES | 19M| 742M| 78948 (4)| 00:15:48 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."NGAY_NHAP"<TO_DATE('2010-06-06 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND ("A"."TRANGTHAI_ID"=1 OR
"A"."NGAYBAN">=TO_DATE('2010-05-06 00:00:00', 'yyyy-mm-dd hh24:mi:
ss')
AND "A"."NGAYBAN"<=TO_DATE('2010-06-05 23:59:59', 'yyyy-mm-dd
hh24:mi:ss')) AND "A"."DVCQ_ID"=1)
payall_new@PAYALL>
Maybe the time in Explain not correct.
|
|
|
|
| Re: My indexes was not active [message #461927 is a reply to message #461919] |
Tue, 22 June 2010 02:56 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
whats is the result of
select count(*) from payall_new.tt_mathes where a.dvcq_id = 1
Remove hint (Hint should be the last option to optimize a sql)
Remove group by from the sql and group by column from the select and check the plan.
Check the plan incrementing the predicates one by one and observe the plan/performance.
SELECT loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
GROUP BY a.loaithe_id;
My apolgies the previous rewritten sql was wrong!
whats the plan if you remove the below code from your sql
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
Regards
Ved
[Updated on: Tue, 22 June 2010 03:27] Report message to a moderator |
|
|
|
|
|
| Re: My indexes was not active [message #462135 is a reply to message #461927] |
Tue, 22 June 2010 23:25 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Its_me_ved wrote on Tue, 22 June 2010 14:56whats is the result of
select count(*) from payall_new.tt_mathes where a.dvcq_id = 1
Remove hint (Hint should be the last option to optimize a sql)
Remove group by from the sql and group by column from the select and check the plan.
Check the plan incrementing the predicates one by one and observe the plan/performance.
Well, I know the hint is last option I can use, it's not worst but it can be bad to use. The plan & guess time I showed you in above my post.
The plan incrementing, one-by-one I showed in the first post. Check if indexed one-by-one column, all of them used by Oracle, except 2 indexes (ngay_nhap_idx, ngayban_id_idx). Replaced statement to check by:
SQL> Select .... where ngay_nhap < ...
SQL> Select .... where ngayban > ....
FTS for 2 select statement. How did the Oracle force FTS? The query's result return about 20mil rows, quietly than total of table's rows.
[/quote]
Quote:
SELECT loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
GROUP BY a.loaithe_id;
My apolgies the previous rewritten sql was wrong!
whats the plan if you remove the below code from your sql
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
Regards
Ved
Well, it's not group by, the group by should make increasing sort on disk, however, it could not make indexes disappears.
Thank you!
"Roachcoach"
Have you tried hinting to force the use and checking the performance vs the query not using it?
Full scans aren't always bad.
Yes, I tried.
FTS are not always bad, but only in small table.
You'd like to image you can run through out some shortcuts about 3 miles, instead of all of 10 miles to get the award you walk. Which choice you will choose?
Thank you!
|
|
|
|
| Re: My indexes was not active [message #462139 is a reply to message #462135] |
Wed, 23 June 2010 00:32 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
trantuananh24hg wrote on Wed, 23 June 2010 06:25Yes, I tried.
FTS are not always bad, but only in small table.
The table size does not matter. What matters is the selectivity of the index (how large portion of rows is filtered). The borderline is around 10%, means, taking 2 million rows out of 20 million. If less rows are taken from the table, index range scan may be faster; if greater, full table scan may take less time.
trantuananh24hg wrote on Wed, 23 June 2010 06:25You'd like to image you can run through out some shortcuts about 3 miles, instead of all of 10 miles to get the award you walk. Which choice you will choose?
More fitting analogy would be: would you like to run all of 10 miles (full table scan) or walk 3 miles, checking the map after each step (index range scan - lookup the row content in the table)?
I am also curious about real performance of the query (as AUTOTRACE TRACE EXPLAIN does not provide any result set).
|
|
|
|
| Re: My indexes was not active [message #462143 is a reply to message #462135] |
Wed, 23 June 2010 00:47 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
trantuananh24hg wrote on Tue, 22 June 2010 23:25[Its_me_ved wrote on Tue, 22 June 2010 14:56]
SQL> Select .... where ngay_nhap < ...
SQL> Select .... where ngayban > ....
FTS for 2 select statement. How did the Oracle force FTS? The query's result return about 20mil rows, quietly than total of table's rows.
[email]log_payall@PAYALL> select count(1)
2 from payall_new.tt_mathes
3 /
COUNT(1)
------------
21029729
1 row selected.
[email]payall_new@PAYALL> select count(*)
2 from tt_mathes
3 where ngay_nhap<to_date('06-06-2010','dd-mm-yyyy');
COUNT(*)
----------
19945300
1* select (19945300/21029729)*100 percent from dual
SQL> /
PERCENT
----------
94.8433525
Optimizer prefers not to use index for the sql
SQL> Select .... where ngay_nhap < ...
Please analyze one step at a time.
Could you please execute the below script and post the output.
whats is the count for the below sql?
SELECT --loaithe_id,
COUNT (*) soluong
--SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1;
select count(loaithe_id) from payall_new.tt_mathes;
explain plan for
SELECT --loaithe_id,
COUNT (*) soluong
--SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1;
select * from table(dbms_xplan.display);
--select count(*) from payall_new.tt_mathes a
--where a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy');
select count(*) from payall_new.tt_mathes a
where a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy');
explain plan for
select count(*) from payall_new.tt_mathes a
where a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy');
select * from table(dbms_xplan.display);
explain plan for
SELECT --loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
/*AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )*/
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
--GROUP BY a.loaithe_id;
select * from table(dbms_xplan.display);
explain plan for
SELECT --loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( /* a.trangthai_id = 1
OR */ ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
--GROUP BY a.loaithe_id;
select * from table(dbms_xplan.display);
explain plan for
SELECT --loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
/*OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) */)
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
--GROUP BY a.loaithe_id;
select * from table(dbms_xplan.display);
explain plan for
SELECT --loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
--GROUP BY a.loaithe_id;
select * from table(dbms_xplan.display);
explain plan for
SELECT loaithe_id,
COUNT (*) soluong,
SUM (gia_nhap) tien
FROM payall_new.tt_mathes a
WHERE a.dvcq_id = 1
AND ( a.trangthai_id = 1
OR ( a.ngayban => To_date ('06-05-2010',
'dd-mm-yyyy')
) )
AND a.ngay_nhap < To_date ('06-06-2010', 'dd-mm-yyyy')
GROUP BY a.loaithe_id;
select * from table(dbms_xplan.display);
Regards
Ved
[Updated on: Wed, 23 June 2010 01:35] Report message to a moderator |
|
|
|
| Re: My indexes was not active [message #462195 is a reply to message #462135] |
Wed, 23 June 2010 02:54 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
trantuananh24hg wrote on Wed, 23 June 2010 05:25
Yes, I tried.
FTS are not always bad, but only in small table.
And what was the result? Main point being just because its not used an index, doesnt mean its slower.
Also, what's the clustering factor like?
Its pure speculation on account of not having the data but a filter of < 06-Jun-2010 doesn't look particularly selective unless perhaps its an expiry date or similar where mostentries in the field will be in the future.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 06:59:29 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
