Solved

Index Advice from Oracle SQL Developer

Posted on 2015-02-12
8
487 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:daveslash
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 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:daveslash
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 76

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
 
LVL 18

Author Comment

by:daveslash
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

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

We are OLD!!!
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 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 34

Assisted Solution

by:johnsone
johnsone earned 100 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:daveslash
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now