Usually I like to demo some of the tips and features on Oracle, but with this blog post I want to share some parts of a conversation I had with my friend Jyotiee Pardessi, (Head of IT Department at SSIT, Pune), on being a PL/SQL Developer and optimizing code.
Jyotiee: What version of Oracle you are using and what of feature of excites you most.
Nimish: Currently we are working with Oracle 11g Enterprise Edition - 11.2.0.3.0 - 64 bit and 12c is in queue. There are various features I love to work with. Some of them are Analytic Functions, Bulk Operations, Caching Techniques and Tracing out Plans and Stats.
Jyotiee: You have some experience of work as a DBA, what do you like to be a DBA or Developer.
Nimish: I like to write SQLs & code and being a developer I get full chance to do what I love. So yes I love being a member of development team and helping them to write optimized code or sql.
Jyotiee: I always thought that Query Optimization is a part of DBA profile.
Nimish: I think there are four roles in managing performance of a Database Server: Developer, DBA, System Admin and Network Admin. If the output is slow due to some lag in network its Network Admin Role. If there are some issue in IO, CPU or OS its a System Admin task. If its related to Oracle Memory or Physical Files layout or Parameter or some other configuration, DBA will look into it. If your SQL is slow or your code is resource consuming, I believe tuning of this comes under development team.
Jyotiee: How do you find that a query is slow or taking extra resources?
Nimish: Oracle has provided us various tools like OEM, AWR Reports or even V$SQL may help. I usually look into top queries taking Buffer Gets or Disk Reads, check them one by one.
Jyotiee: Actually I was asking how do you know if a particular query can be optimized further.
Nimish: I don't know if a query can be optimized or not. The simplest way is to try to optimize them. Trust me there is no shortcut to hard-work.
Jyotiee: Is there any tool which you use to optimize queries or any certain steps which we can follow to optimize a query??
Nimish: I am not sure if there is such a tool which can optimize queries or code to a certain level. Once I read on ASKTOM, if there are any certain steps for Optimization, those has been already automated by Oracle and provided a tool.
Jyotiee: But you can give some steps you follow, right?
Nimish: In general some steps could be, 1) Find query to be optimized 2) Check if we can remove extra joins or unwanted calculation 3) Gather Stats 4) Generate its Plan and Stats 5) Find Missing Indexes on Tables/FKs 6) Find FTS 7) Check Cardinalities/Histograms 8) Try to Re-write better query. Most important part of Query optimization is to understand data and cardinalities.
Jyotiee: How do you know that a query is optimized upto its limit?
Nimish: I never know, may be a better developer can optimize it further. I look at optimization as a continuous process and it also depends on data and segments. So a Ok query now can be a bad query in future. You need to put continuous efforts in optimization.
Jyotiee: Do you force team to write efficient code and optimize old code if needed?
Nimish: I can not force anyone. I can just motivate them to learn and more they learn better they write the code. I also follow OTN, ASKTOM and Oracle Magazine Articles to keep myself update, there is so much to learn.
Jyotiee: It took lot of efforts to optimize, what is your take on suggesting Hardware upgrade over focusing on Optimizing?
Nimish: As I usually say you can not get performance of Ferrari from Maruti 800, sometimes it is necessary to upgrade hardware to matchup the requirements. But we need to be cautious to migrate to new hardware just to hide performance issues, trust me they will reappear on new machine in possibly worse manner.
Jyotiee: One last question, various projects are migrating from Oracle to NoSQL like Cassandra, MongoDB or Hadoop. How do you see the future?
Nimish: According to me Oracle is designed as General purpose RDBMS and is most scalable and efficient database System in World. NoSQL databases are designed as per specific needs and may serve better in their particular domains. I think both Oracle and NoSQL have great future but migrating from Oracle to NoSQL, without understanding your need and purpose of NoSQL, could be a big mistake especially now when JSON support is there in 12c with existing XML support.
Thank You for reading this conversation. I hope this was somewhat useful to you, I might be missing something, which you can update on comment box. Your suggestions and feedback are always welcome.
Related Posts
- How to Get Execution Plan and Statistics of Query
- JSON in Oracle Database with Example
- Oracle Database 12c New Features for Developers
- SQL Interview Question Answers
- Query optimization tips for Oracle
- SQLNET: How does Oracle Client connect with Oracle Server
- How to connect sqlplus without tnsnames.ora
- Why prefer COALESCE over NVL
- plsql_warnings: Ensure more quality and performance in plsql code using Oracle Compiler
- PLSQL Tuning: Bind Variables and execute immediate
- PLSQL Tuning: Bulk Collect with Dynamic SQL
Jyotiee: What version of Oracle you are using and what of feature of excites you most.
Nimish: Currently we are working with Oracle 11g Enterprise Edition - 11.2.0.3.0 - 64 bit and 12c is in queue. There are various features I love to work with. Some of them are Analytic Functions, Bulk Operations, Caching Techniques and Tracing out Plans and Stats.
Jyotiee: You have some experience of work as a DBA, what do you like to be a DBA or Developer.
Nimish: I like to write SQLs & code and being a developer I get full chance to do what I love. So yes I love being a member of development team and helping them to write optimized code or sql.
Jyotiee: I always thought that Query Optimization is a part of DBA profile.
Nimish: I think there are four roles in managing performance of a Database Server: Developer, DBA, System Admin and Network Admin. If the output is slow due to some lag in network its Network Admin Role. If there are some issue in IO, CPU or OS its a System Admin task. If its related to Oracle Memory or Physical Files layout or Parameter or some other configuration, DBA will look into it. If your SQL is slow or your code is resource consuming, I believe tuning of this comes under development team.
Jyotiee: How do you find that a query is slow or taking extra resources?
Nimish: Oracle has provided us various tools like OEM, AWR Reports or even V$SQL may help. I usually look into top queries taking Buffer Gets or Disk Reads, check them one by one.
Jyotiee: Actually I was asking how do you know if a particular query can be optimized further.
Nimish: I don't know if a query can be optimized or not. The simplest way is to try to optimize them. Trust me there is no shortcut to hard-work.
Jyotiee: Is there any tool which you use to optimize queries or any certain steps which we can follow to optimize a query??
Nimish: I am not sure if there is such a tool which can optimize queries or code to a certain level. Once I read on ASKTOM, if there are any certain steps for Optimization, those has been already automated by Oracle and provided a tool.
Jyotiee: But you can give some steps you follow, right?
Nimish: In general some steps could be, 1) Find query to be optimized 2) Check if we can remove extra joins or unwanted calculation 3) Gather Stats 4) Generate its Plan and Stats 5) Find Missing Indexes on Tables/FKs 6) Find FTS 7) Check Cardinalities/Histograms 8) Try to Re-write better query. Most important part of Query optimization is to understand data and cardinalities.
Jyotiee: How do you know that a query is optimized upto its limit?
Nimish: I never know, may be a better developer can optimize it further. I look at optimization as a continuous process and it also depends on data and segments. So a Ok query now can be a bad query in future. You need to put continuous efforts in optimization.
Jyotiee: Do you force team to write efficient code and optimize old code if needed?
Nimish: I can not force anyone. I can just motivate them to learn and more they learn better they write the code. I also follow OTN, ASKTOM and Oracle Magazine Articles to keep myself update, there is so much to learn.
Jyotiee: It took lot of efforts to optimize, what is your take on suggesting Hardware upgrade over focusing on Optimizing?
Nimish: As I usually say you can not get performance of Ferrari from Maruti 800, sometimes it is necessary to upgrade hardware to matchup the requirements. But we need to be cautious to migrate to new hardware just to hide performance issues, trust me they will reappear on new machine in possibly worse manner.
Jyotiee: One last question, various projects are migrating from Oracle to NoSQL like Cassandra, MongoDB or Hadoop. How do you see the future?
Nimish: According to me Oracle is designed as General purpose RDBMS and is most scalable and efficient database System in World. NoSQL databases are designed as per specific needs and may serve better in their particular domains. I think both Oracle and NoSQL have great future but migrating from Oracle to NoSQL, without understanding your need and purpose of NoSQL, could be a big mistake especially now when JSON support is there in 12c with existing XML support.
Thank You for reading this conversation. I hope this was somewhat useful to you, I might be missing something, which you can update on comment box. Your suggestions and feedback are always welcome.
Related Posts
- How to Get Execution Plan and Statistics of Query
- JSON in Oracle Database with Example
- Oracle Database 12c New Features for Developers
- SQL Interview Question Answers
- Query optimization tips for Oracle
- SQLNET: How does Oracle Client connect with Oracle Server
- How to connect sqlplus without tnsnames.ora
- Why prefer COALESCE over NVL
- plsql_warnings: Ensure more quality and performance in plsql code using Oracle Compiler
- PLSQL Tuning: Bind Variables and execute immediate
- PLSQL Tuning: Bulk Collect with Dynamic SQL
Hi nimish,
ReplyDeleteHow do you find that a query is slow using OEM ?
please tell me the steps:
performance tab of oem and awr reports
DeleteCan we use DBMS_PROFILER for fixing perf issues?
ReplyDeleteYou can use dbms_profiler to identify issue in your code
DeleteThank you Nimish, especially for sharing your thoughts and comparing Oracle and NoSQL.
ReplyDeleteVery nice write up on who is responsible on what part of tuning. Thanks.
ReplyDeleteAwesome and inspiring answers from Nimish
ReplyDeleteAwesome explanation Nimish. Really helpful.
ReplyDelete