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 Go to next message
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 #377449 is a reply to message #377447] Tue, 23 December 2008 01:55 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please post the SQL with an Explain Plan and execution statistics.
Re: SQL Tuning [message #377450 is a reply to message #377447] Tue, 23 December 2008 01:56 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Posting table definitions (including indexes), SQL statement and it's EXPLAIN may help.
Re: SQL Tuning [message #377544 is a reply to message #377450] Tue, 23 December 2008 07:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 #377625 is a reply to message #377554] Wed, 24 December 2008 00:08 Go to previous messageGo to next message
sd_md_rizwan@yahoo.com
Messages: 37
Registered: September 2007
Location: Saudi Arabia
Member

Thanks for the response,
I don't know how to get explain plan if it is execution plan then
it is in the attachment file, and regarding bitmap index you said one bitmap index works only for one column, if so do I need to create individual bitmap indexes for each column or what can you explain me in some more clear

Thanks
Re: SQL Tuning [message #377633 is a reply to message #377447] Wed, 24 December 2008 00:45 Go to previous message
michael_bialik
Messages: 611
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
JOB_CODE IN (1,2,3)

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.
Previous Topic: Substr,LTrim,Rtrim making query run slow
Next Topic: sql-tuning for specific query?
Goto Forum:
  


Current Time: Fri Dec 09 21:34:20 CST 2016

Total time taken to generate the page: 0.05262 seconds