| Query working very slow [message #481552] |
Thu, 04 November 2010 02:55  |
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
|
Member |
|
|
dear all,
I am facing problem in running following query which is running too slow.moreover its cost is too much and taking full table scan. To speed up ,i analyzed the table before running and even tried to pass index hint .This query was taking 30-45 minutes to execute earlier but now it is running for 3-4 hrs.I m not sure what is the issue.
Please help.
Regards,
Navneet
Total records in the table = 12 million.
total resultset the query is fetching = 40K.
SELECT /*+ index( a PREPAID_DOC_LMDATE) */ '91'||c_msisdn,SRNO,C_MSISDN,C_SIM ,CLNAME, CMIDNAME, CNAME, CFATHERLNAME, CFATHERMIDNAME,
CFATHERNAME , CADDRESS
, CLANDMARK, C_CITY, CDISTRICT, CSTATE, C_PIN, C_NATION, CPADDRESS, C_PCITY, CPDISTRICT, CPSTATE, C_PPIN
, C_PNATION, C_PPASSPORTNUM, C_PVISANUM,NULL,'ACTIVE' ,DOCNO,C_IDPROOF_NUM
FROM prepaid_documents_new a WHERE last_mod_date>=sysdate- 4
and last_mod_date <=sysdate
explain plan is as follows:-
SELECT STATEMENT, GOAL = ALL_ROWS Cost=402779 Cardinality=505079 Bytes=90914220
FILTER
TABLE ACCESS BY INDEX ROWID Object owner=PRISMUPW Object name=PREPAID_DOCUMENTS_NEW Cost=402779 Cardinality=505079 Bytes=90914220
INDEX RANGE SCAN Object owner=PRISMUPW Object name=PREPAID_DOC_LMDATE Cost=3629 Cardinality=505079
|
|
|
|
|
|
| Re: Query working very slow [message #481562 is a reply to message #481552] |
Thu, 04 November 2010 04:41  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Remove the hint. If the optimizer decides not to use the index, let it do so.
IF the optimizer chooses not to use the index, it will be because
a) it is cheaper to do a Full Table Scan
b) your stats are out of date or missing
Big question of course: what happened between the time where the query took 3/4 of an hour and the time where the query took 3-4 hours
|
|
|
|
| Re: Query working very slow [message #481572 is a reply to message #481562] |
Thu, 04 November 2010 05:43 |
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
|
Member |
|
|
Dear Frank,
If I remove the hint the cost is little better
but still on higher side. I have analyzed the table today only and upto date. I have checked the table size
SELECT table_name, ROUND((blocks*8),2) tablesize, ROUND((num_rows*avg_row_len/1024),2) actualsize
FROM user_tables WHERE TABLE_NAME ='PREPAID_DOCUMENTS_NEW' ORDER BY ACTUALSIZE,TABLESIZE ASC;
TABLE_NAME TABLESIZE ACTUALSIZE
PREPAID_DOCUMENTS_NEW, 4909576 4329566.74
|
|
|
|
|
|
|
|
|
|
| Re: Query working very slow [message #481917 is a reply to message #481676] |
Mon, 08 November 2010 06:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Judging by the SQL you ran above, you think you have 8KB blocks. This would mean you have 613697 blocks, and they are read by the Full Table Scan in (say) 30 minutes, or 1800sec. Assuming your DB_FILE_MULTIBLOCK_READ_COUNT is set to 16 (do a SHOW PARAMETER MULTIBLOCK in SQL*Plus to check), then that is 613697/16 = 38356 round trips to disk in 1800 sec, or 21 disk reads per second, or 47ms per read.
This is abysmally slow, and suggests some other form of contention at play. Are there many other queries hammering the disk?
I would:
- Check your DB_FILE_MULTIBLOCK_READ_COUNT. Make sure my guess of 16 is correct.
- Check you block size. Make sure my assumption of 8KB blocks is correct.
- Check the load on your disk. You may be overloading your system.
- Run a 10043 trace and check the wait events - see if they are mostly DB FILE SCATTERED READ (full table scan)
If your system is not overloaded and those parameters above are correct, I think it should be able to FULL SCAN 600K blocks in no more than 10 minutes. Of course this would be dependent on the speed of your disk, but even 10 minutes would mean you had VERY slow disk.
Ross Leishman
|
|
|
|
|
|
| Re: Query working very slow [message #482023 is a reply to message #482000] |
Tue, 09 November 2010 02:55 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Please post the results of follwing queries:
SELECT 'Total', COUNT(*)
FROM prepaid_documents_new a
UNION ALL
SELECT 'Between', COUNT(*)
FROM prepaid_documents_new a
WHERE last_mod_date>= (sysdate - 4)
and last_mod_date <=sysdate
SELECT TO_CHAR(MIN(last_mod_date), 'YYYYMMDDHH24MISS') MIN_DATE,
TO_CHAR(MAX(last_mod_date), 'YYYYMMDDHH24MISS') MAX_DATE
FROM prepaid_documents_new |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
