When a user comes to you and asks that a particular SQL query is taking more time. How will you solve this?

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

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 tableand we need to determinedtable 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 dbwnwriting 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 issueonceindex 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 . eihertable defragmentrequire 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

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

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

We can also try the explainplan and tkprof utilities.