Query Taking More Time?
Most commonly asked question in the interview:
1. Query is taking more time?
2. Query running longer time?
3. Till yesterday query used to take X time but today is taking Y time?
4. Yesterday I ran a query which used to take X time but unfortunate it took Y time?
In any of those above queries Our DBA will be having tough or challenging time to drill down and understand the root cause and provide the recommendations.
Below is out response or troubleshooting method to drill down the issue and find the root cause:
Very first question as a DBA we should ask What type of query it is?
- DML query or Select Query?
1. DML Query Taking more time (like Insert, Update etc)
Cause: locks / deadlocks
Fix/Solution: kill / Ask user to do commit/rollback
2. Select Query Taking more time
- OS Side - OS analysis
- top, vmstat, iosts, memory, sar (OEM, OS watcher, Exa watcher, Nagios etc…)
- DB Side - DB analysis
- Query dynamic perf view (v$session, v$longops, v$sql etc...)
- AWR report (ASH report, ADDM report, SQL advisory report)
Based on the above OS and DB side analysis our DBA Recommendations are:
- Latest Patch (n-1) (Jan, Apr, Jul, Oct)
- Gather stats (Table/Index/Schema)
- Validation / rebuild Index
- Table move
- Table shrink
- Check execution plan (Plan change)
- SQL Profiling
#rac #database #asm #mallik034 #vismotechnologies #oracledba #oraclecarrers #oracleworld #training #onlinetraining #liveclasses #PT #Performance Tuning
Regards,
Mallikarjun / Vismo Technologies
WhatsApp: +91 9880616848 / +91 9036478079
Cell: +91 9880616848 / +91 9036478079
Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com / info@vismotechnologies.com
Негізгі бет Query Taking More Time?
Пікірлер: 1