SQL Tuning

I have access to SQL Developer tool.
My question is how much I can do SQL Tuning on SQL Developer with user privileges and what are the things I could do to tune SQL for better performance and where can I look for these steps to take in tuning.
lium1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
People make careers and write books about tuning. This topic is WAY to broad for a Q&A site like this.

Are you asking how to tune with SQL Developer or how to tune overall?

I would start in the online docs.

Database 2 Day + Performance Tuning Guide:
https://docs.oracle.com/cd/E11882_01/server.112/e10822/toc.htm

Database Performance Tuning Guide:
https://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm
lium1Author Commented:
How to tune with SQL Developer?
Thanks for references!
lium1Author Commented:
I mean how to tune with SQL Developer without DBA privileges!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
I don't use a GUI.  I'm old school and do pretty much everything with sqlplus.

That said:  Without access to a lot of the V$ views I'm not sure what all you can do.

Individual statements can be tuned for the most part with a simple "explain plan".

explain plan for
select ...
/

Then to see the plan:
select * from table(dbms_xplan.display);


Slightly more detail (again from sqlplus):
set autotrace traceonly;
select ...
/
set autotrace off;

This will provide some actual execution stats that explain plan will not.

Then there is tracing and tkprof.  The docs will talk about this.  I won't since I really never have to get to that level of detail.

If you are licensed for the tuning pack of OEM, there are several Wizards that will provide a LOT of insight into tuning.
Wasim Akram ShaikCommented:
if you have got the sql developer.. then you can't do much tuning with it.. tuning is a broader concept as steve suggested..!!

you can do these things with sql developer.. format the query with proper indentation, auto trace execute explain plan, copy,paste a query etc.,

write any query in sql developer and select it and do a right click you will get a drop down list.. thats all pretty much you can do with a query in sql developer .. not a bit more than that..
lium1Author Commented:
slightwv,

The DBA won't give me an access to OEM, so I won't be able to use Wizards.
Also, without Sys privileges I won't be able to see v$views and dba_dictionary views.
Correct me if I am wrong on this part!

My question is to turn auto trace on and tkprof, don't I need dba privileges?
slightwv (䄆 Netminder) Commented:
>>Correct me if I am wrong on this part!

I think some V$ views can be accessed without SYS/DBA level access.  There may also be some USER_ level views that can aid in tuning.  Which ones, I don't know.

To get a list of everything you can see:
select view_name from all_views;

Then you can look up the ones you aren't familiar with in the online docs.

You can be granted access on the specific views you need.  There is no reason to ask for DBA or nothing.

>>My question is to turn auto trace on and tkprof, don't I need dba privileges?

Try it and see?  For autotrace I think you'll just need access to the PLUSTRACE role.

autotrace is a hybrid between a regular explain plan and full tracing/tkprof.  It is for individual statements like explain plan but the statistics returned aren't as detailed as tkprof.

For full tracing I believe you'll need some elevated privileges but maybe not full DBA.  I'm not sure.


What is your role?  You need access that gives you the ability to do your job.  They either give it to you or you cannot be expected to do the job they have assigned you.

That said:
I wouldn't give someone DBA access because they said they needed it.  I would however grant select on V$ views that dealt specifically with their job.

Before you ask:  NO, I don't have a list of those specific views.  Someone involved in tuning should know them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.