Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Index Advice from Oracle SQL Developer

Posted on 2015-02-12
8
Medium Priority
?
534 Views
Last Modified: 2015-02-17
I've been developer and DBA on IBM DB2 for the past two decades, and I'm just now jumping in to developing on Oracle. So, I apologize in advance if this is too-easy, "beginner stuff".

When I wanted to optimize a query in DB2, I could use their "Visual Explain" tool which gives loads of details on how the optimizer parses and runs a query. It outputted a graphical tree-structure showing the details of how each table was going to be parsed, including which index it would choose, why it chose it, its index key-list, estimated processing time, and numerous other pieces of information about the query. In fact, if the optimizer determined that it could have used an index that doesn't actually exist, it will report that back as "index advice". From there, I can even right-click on the icon to manually create the suggested index.


In Oracle SQL Developer, I've been able to successfully use "explain plan for <my SELECT statement>" and "select * from table(dbms_xplan.display);" to see a somewhat descriptive set of text-based information on the optimization plan of a query, but I don't see any sort of "Index Advice" like I did in the DB2 tool.


Again, I apologize for my ignorance, but does this kind of feature exist in Oracle? I'm using Oracle 11g, and my SQL Developer version is "Version 1.5.5 Build MAIN-5969".


Thanks in advance!

DaveSlash
0
Comment
Question by:Dave Ford
8 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 40606322
>> if the optimizer determined that it could have used an index that doesn't actually exist, it will report that back as "index advice".

The problem here is creating an index to 'fix' one query could have a huge negative impact on the overall system.

I've never used this but remember reading a little about it.

See if the SQL Access Advisor is what you are after:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/advisor.htm#CHDEEBAI

I'm not sure if SQL Developer has a menu option of GUI interface for this or not. I'm still a command line guy.

If you are licensed for the Performance Pack, Grid Control also has a lot of Advisors that will make recommendations.
0
 
LVL 18

Author Comment

by:Dave Ford
ID: 40606477
Thanks for directing me to the SQL Access Advisor, slightwv. I'll check that out.

Just out of sheer, unadulterated curiosity, how could creating a new index  have "a huge negative impact on the overall system"?

> I'm still a command line guy.

I have observed that the REALLY good technicians (in almost any technology) tend to straight to the command-line to get real work done. ( I know I do in DB2. ) Of course, that might have something to do with using that particular technology for a LONG time ... before the GUI became ubiquitous.

Thanks!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40606614
>>unadulterated curiosity, how could creating a new index  have "a huge negative impact on the overall system"?

You have not seen systems (mainly OLTP) that have been over-indexed that don't perform well?

At some point the system will spend more time updating all the indexes than the benefit of having them.

This isn't as big a reason these days in Oracle but was in the past and still may be in certain systems:  The optimizer may choose to use the new index for other queries and can cause worse actual performance.  Fix one query, break 10 others.

The optimizer in Oracle is MUCH better these days (as long as the database statistics are good) so this isn't as big an issue as it was in the past.

>> tend to straight to the command-line to get real work done

It is VERY hard to convince the younger generation that command line is faster than a GUI for many tasks.

>>that might have something to do with using that particular technology for a LONG time ...

EXACTLY!  I remember Windows 3.0...  Started out when everything was command line.  Man I'm getting old...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Author Comment

by:Dave Ford
ID: 40606666
> You have not seen systems (mainly OLTP) that have been over-indexed that don't perform well?

You're right. I have seen that ... although, as you observed, it's less of an issue now since most query-engines have progressed to the point where index-maintenance (generally) takes a negligible amount of time.

> It is VERY hard to convince the younger generation that command line is faster than a GUI for many tasks.

I wholeheartedly agree.

> Started out when everything was command line.  Man I'm getting old...

I'm with you all the way, slightwv. I started programming in 1981 ... and I'm still doing it. (Hmmmm ... what does that say about us?) :-D

Thanks for your help!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40606833
>>(Hmmmm ... what does that say about us?) :-D

We are OLD!!!
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points
ID: 40606956
the 80's, yes I recall that as my second decade of work... (oops, whisks a white hair from my keyboard)

& the simple answer to the original question ( does this kind of (index suggestion) feature exist in Oracle? ) is: no
(well, not that I remember, however that's not much of a benchmark these days)


but there are useful resources about the optimizer and using the explain plan at
https://blogs.oracle.com/optimizer/

such as "explain the explain plan" as there are more columns available in the output than you see from the defaults

& Understanding Optimizer Statistics in Oracle Database 11g
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 400 total points
ID: 40607839
Even with the advances in the optimizer, I still see it pick the "wrong" index at times.  It has gotten better, but it will never be perfect.

You should never just put a new index into a production system without thoroughly testing every application that touches the table where you are adding an index.  I have been burned by this many times.  Even when they said they tested it, they didn't.

And, I would take the command line over the GUI any day.  I can type the command in at least 10 times faster than you can even find the button in the GUI to click.  When your system is down, do you want the guy that can fix it in under a minute, or the guy that has to wait 1 minute for the GUI to come up and then another 90 seconds to find the place to click, then change something and click again?  Downtime is very costly.  Most companies I have worked for have downtime cost calculated to the second and the number is pretty large.  Unfortunately, people today just want a GUI with a big button that says FIX and then it is magically corrected for them without having to know what is going on.  These are the most dangerous people.
0
 
LVL 18

Author Closing Comment

by:Dave Ford
ID: 40615454
Thank you for your valuable insight! I have a significantly better understanding of Oracle's query optimizer and indexing strategy.

Thanks again!
DaveSlash
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question