Query Access Plan in Oracle

Picture of Mertech Mertech : Feb 25, 2008 3:09:00 PM

Databases

A lot of developers use embedded SQL, or other tools to access their data. If a query is not optimized it can cause problems.

An easy way is to check the access plan is through the SQL Worksheet. In Oracle XE click the 'explain' tab, and in the regular server click the 'plan' tab.

Be especially wary of full table scans (TABLE ACCESS FULL).

Oracle 10g Enterprise Manager (OEM) Alerts section has a very good list of suspect queries... When they have been prepared too many times (Duplicate SQL), taking too much CPU or disk access, etc. You can also look at the Performance Tab - especially the Top SQL section.

Recommendation: Test your queries to see the access plan. Encourage customers to test their embedded SQL.

Why Migrate from Btrieve to PostgreSQL and other Relational Databases?

Riaz Merchant: Oct 28, 2024 6:12:41 AM

Introduction Many independent software vendors (ISV) and corporate users still rely on applications that use a category of database collective called...