Techniques for working with query execution plans in Oracle
It's like a nail in the sole of your favorite shoe. You can walk, but increasingly you catch yourself wanting to stay in one place or hand over things to others. Minor inconveniences not only slow down our work, but also reduce motivation, introduce interference to the process, reduce the quality of the result. And if there was a friend who taught you to take a hammer and hammer this nail, you will not only be grateful for his help, but also help others yourself, saving them from small, but very annoying interference. For this, you need to communicate, share not only deep and intimate knowledge in forums and on sites like Habr, but also your simple tricks and "little tricks"
Like any text, queries and SQL programs can be created in any text editor. But if you are a professional, you work a lot and often with SQL, then syntax highlighting and automatic reformatting of the code will no longer be enough, especially if you have to switch between different versions of the same DBMS or different DBMS platforms.
Recently, I happened to communicate with one of the leading professionals in the Oracle database. He told a lot of interesting things about working with the plans for executing queries in various versions of this DBMS and did not hesitate to tell everyone about the tools and techniques he uses and give some useful small tips. I did a translation of one of the articles in his blog and would like to offer it to Habravchan. Despite the fact that the described technique was used to work with Oracle, I now successfully apply the same approach for MS SQL and Sybase.
')
My name is Dan Hotka. I am the director of Oracle ACE. One of my privileges in this group is to help in the dissemination of information and useful technical knowledge related to the Oracle DBMS. I am well known after my 12 (soon 14) published books and literally hundreds of articles. I regularly write in the blog and I am going to do it in the future. We could even meet at one of the events or user group meetings. I regularly speak on these topics around the world.
I am going to share with you both technical knowledge about Oracle and how this knowledge is applied in Embarcadero solutions.
I downloaded the “big three” of Embarcadero products: Rapid Sql, DBArtisan, PowerStudio DB. Now I would like to talk about the first impression and some techniques for working with plans for the execution of queries in RapidSQL. (I installed version 8.6.1)
I'll show you a couple of tricks for query plans in and around Rapid SQL.
I like the tool. Of course, this is a great tool if you have different types of DBMS from different manufacturers, since this tool supports about a dozen different DBMS. A single interface for mastering all databases! My tricks are for Oracle. But tricks for Embarcadero tools should work regardless of which DBMS you connect to.
When viewing execution plans, I like to see the execution plan and the request itself at the same time.
This is easy to achieve.
To start, load your SQL query into the ISQL editor window (using the Open button), then turn on the Explain Plan button (marked in the red circle). The button will remain activated.
Run the execution request, and the Query Plan tab appears, filled with the execution plan.
Place the mouse cursor on any of the nodes in the diagram and additional useful information related to this step from the query plan will appear!
By default, Rapid SQL shows the execution plan graphically. I came out of the old world of optimization ... I prefer the text version, so I click the right mouse button in the window with the plan and select “View as Text”.
I prefer to see the request text and the plan at the same time.
It is easy to do. See the ISQL window tabs at the bottom of the main window? First we need to set up Rapid SQL so that it issues a plan in a separate window.
Click the Options button (left red circle) and then set the 'Unattached' option for the Result window. This will create two separate bookmarks at the bottom of Rapid SQL, after running the query to execute. Just drag a little this window over the bookmark and a rectangle will appear where you can move this window.
Or you can use the Tile windows item from the main menu
And one more thing: it all also works in DBArtisan, a solution for database administrators.
via
Like any text, queries and SQL programs can be created in any text editor. But if you are a professional, you work a lot and often with SQL, then syntax highlighting and automatic reformatting of the code will no longer be enough, especially if you have to switch between different versions of the same DBMS or different DBMS platforms.
Recently, I happened to communicate with one of the leading professionals in the Oracle database. He told a lot of interesting things about working with the plans for executing queries in various versions of this DBMS and did not hesitate to tell everyone about the tools and techniques he uses and give some useful small tips. I did a translation of one of the articles in his blog and would like to offer it to Habravchan. Despite the fact that the described technique was used to work with Oracle, I now successfully apply the same approach for MS SQL and Sybase.
')
My name is Dan Hotka. I am the director of Oracle ACE. One of my privileges in this group is to help in the dissemination of information and useful technical knowledge related to the Oracle DBMS. I am well known after my 12 (soon 14) published books and literally hundreds of articles. I regularly write in the blog and I am going to do it in the future. We could even meet at one of the events or user group meetings. I regularly speak on these topics around the world.
I am going to share with you both technical knowledge about Oracle and how this knowledge is applied in Embarcadero solutions.
I downloaded the “big three” of Embarcadero products: Rapid Sql, DBArtisan, PowerStudio DB. Now I would like to talk about the first impression and some techniques for working with plans for the execution of queries in RapidSQL. (I installed version 8.6.1)
I'll show you a couple of tricks for query plans in and around Rapid SQL.
I like the tool. Of course, this is a great tool if you have different types of DBMS from different manufacturers, since this tool supports about a dozen different DBMS. A single interface for mastering all databases! My tricks are for Oracle. But tricks for Embarcadero tools should work regardless of which DBMS you connect to.
When viewing execution plans, I like to see the execution plan and the request itself at the same time.
This is easy to achieve.
To start, load your SQL query into the ISQL editor window (using the Open button), then turn on the Explain Plan button (marked in the red circle). The button will remain activated.
Run the execution request, and the Query Plan tab appears, filled with the execution plan.
Place the mouse cursor on any of the nodes in the diagram and additional useful information related to this step from the query plan will appear!
By default, Rapid SQL shows the execution plan graphically. I came out of the old world of optimization ... I prefer the text version, so I click the right mouse button in the window with the plan and select “View as Text”.
I prefer to see the request text and the plan at the same time.
It is easy to do. See the ISQL window tabs at the bottom of the main window? First we need to set up Rapid SQL so that it issues a plan in a separate window.
Click the Options button (left red circle) and then set the 'Unattached' option for the Result window. This will create two separate bookmarks at the bottom of Rapid SQL, after running the query to execute. Just drag a little this window over the bookmark and a rectangle will appear where you can move this window.
Or you can use the Tile windows item from the main menu
And one more thing: it all also works in DBArtisan, a solution for database administrators.
via
Source: https://habr.com/ru/post/234247/
All Articles