senthil_s14

• May 16th, 2007

 

Hi,

If you find the Sql Query (which make problem), then take a Sqltrace with explain plan, it will show how the sql query will executed by oracle, depending upon the report you will tune your database,

for example: one table have 10000 record, but you want to fetch only 5 rows, but in that query oracle does the full table scan.

only for 5 rows full table is scan is not a good thing, so create a index on the particular column, by this way to tune the datatabse


Senthil Kumar

tunakishore

 Profile Answers by tunakishore 

• Jan 17th, 2009

 

Hi , I just reviewed your answer. Anyway thanks for your effort to give us response.


But I have a concern .Suppose a user just reported us that his query running slow and we checked the execution plan, but I think we need more tuning and investigation before applying index on that table.

I think first of all we need to check either user requirement is more than 80% data from query table and we need to determined table size ? if that is the case I think if we create index ,again user will get poor performance . because oracle will get contention on db buffer cache since first of all index block need to be picked up as well as almost all block from that table will be pull out . hence it will increase the I/O , also other user request may get slow performance since existing data in cache will be flush out and reload .

and also additionally we need to check system level performance too , either any problem with dbwn and check alert log file too ? either dbwn writing  slow any modified data which is in buffer  to datafile and either user server process is waiting to for space in buffer cache ? if user query needed join or sorting , either there is not enough space in  temporary tablespace for now ?

and if user complain again after fixing the issue once index applied (if user really not required more % of data) or fixing any of db level issue . then we need drill down to check either any issue with table block level . eiher table defragment require if watermark reached high ....
like this we need to tuning more things for that query. if still user query having problem after followed various step as i said above , then may be we need to tune the query .

if you have any concern or question ,. please let me know , I'll really appreciate.

Thanks
Tuna

Sreejith R Nath

• May 22nd, 2012

 

The dbms_advisor package has a procedure called dbms_advisor.quick_tune that allows the DBA to quickly tune a single SQL statement with a single procedure call

Madhusmita Dash

• Jun 21st, 2012

 

I think we should check the indexing structure.May be for that it took more time.

venu

• Sep 1st, 2012

 

We can also try the explainplan and tkprof utilities.

Fabio B

• Jun 9th, 2015

 

If the same query is slow you need to analyse the following:
1) Did the path changed? Check actual path vs previous path is possible?
2) Does the table have more data and the full scan or other thing is slow it exponentially? (explain plan or tkprof should be used)
3) Is the database system overloaded? Maybe a backup is running, other intensive resource query...
4) Is the OP system overloaded? Memory use (swapping), processor...?
5) Analyse the V$SYSTEM_EVENT
6) Run report (AWR, ADDM.... depends on the case).

There are lots of different problems that can affect the efficiency of the Query.

vikram singh

• Jun 27th, 2015

 

In this situation there are some condition
1) There is any locking on table.
2) Full table scan.
3) Any other process is also hitting on same table.

1) First we check the table locking
a. Table level locking
b. Row level locking

2) Check the table full scan - It may take high CPU if there is full table scan on table then might be index is required on some field or some indexes are not picking up so need to tune the query.