Home » RDBMS Server » Performance Tuning » SQL Tuning (Oracle Database, 10.1.0.2.0, Win 2003 Server)
| SQL Tuning [message #377447] |
Tue, 23 December 2008 01:52  |
sd_md_rizwan@yahoo.com
Messages: 37
Registered: September 2007
Location: Saudi Arabia
|
Member |
|
|
Hi this is Rizwan,
I have a table which holds 11 million of records with 10 columns,and use to increase every month and when I query on that table even for 100 records it is taking very long time, to get the result,
I had already created a bitmap index on it,
I had already increased the buffer size,
I had already increased the tablespace size,
and did all what I know but still I dint get the quick performance, please can any body help and tell me the solution
step by step wise the things need to do to get my result within
less than 10 to 15 seconds.
Thanks.
|
|
|
|
|
|
|
|
| Re: SQL Tuning [message #377544 is a reply to message #377450] |
Tue, 23 December 2008 07:30  |
sd_md_rizwan@yahoo.com
Messages: 37
Registered: September 2007
Location: Saudi Arabia
|
Member |
|
|
Thanks for quick response,
The table includes this columns
MNTH_CODE,MNTH_DAY,WEEK_NUM,BR_NUM,SEX_CODE,EMP_NUM,
TRNS_CODE,TRNS_DESC,HOURS,MINUTES,SECONDS,TOT_TIME_HRS,
TXNS,JOB_CODE
and the index for the columns (bitmap-index)
MNTH_CODE,MNTH_DAY,WEEK_NUM,BR_NUM,SEX_CODE,EMP_NUM,
TRNS_CODE,JOB_CODE
and if I make a simple sql query like
SELECT MNTH_CODE,BR_NUM,SEX_CODE,EMP_NUM,TRNS_CODE,
SUM(HOURS)+SUM(MINUTES)+SUM(SECONDS),SUM(TXNS)
FROM CP_DAILY_TRNS_BY_TYPE WHERE JOB_CODE IN (1,2,3)
GROUP BY MNTH_CODE,BR_NUM,SEX_CODE,EMP_NUM,TRNS_CODE
also, my result will come in 190 to 240 seconds
and I want to resuce this time to less than 10 seconds or less
please help me
|
|
|
|
| Re: SQL Tuning [message #377554 is a reply to message #377544] |
Tue, 23 December 2008 08:14 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. You didn't post EXPLAIN.
2. It's NOT clear if you have 1 bitmap index on 8 columns (in that case your query can NOT use it), or 8 different bitmap indexes.
3. Anyway - to display summaries - look for MATERIALIZED VIEW feature.
HTH.
|
|
|
|
|
|
| Re: SQL Tuning [message #377633 is a reply to message #377447] |
Wed, 24 December 2008 00:45 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. | Quote: | I don't know how to get explain plan if it is execution plan
|
- Yes, it is.
2. It shows full table scan.
3. Optimizer can not use your index because then only column with filtering conditins is
and that column is last column in your index.
4. You can try and create index on that column only, however,
as I already wrote - materialized view is probably the best solution in your case.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 07:13:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
