Thank you so much ....you saved my whole week and rewriting the whole procedure in a package. I was very disturb with nested loop and codes... I exactly did what you guided here , found perticular queries and modified accordingly to consume less time to run. Unbelievable results came . Previously it was taking 2 minutes but for same it's taking 19 seconds. You saved my hours of running long program , now it's completing in few minutes ... drastically optimized well. Thank you so much again sir.
The reason you say this so wonderfully is that great gift that God gave you.. It is a great blessing for us to have people like you.... Thankyou so much siva brother..
0:09 - How to Start with PLSQL Tuning 1:08 - How and where to start with Performance Tuning an Inbuilt function name DBMS_Profiler 1:42 - PLSQL Performance tuning start 2:46 - We can use Provided tools DBMS_Profiler 4:02 - How to use DBMS_Profiler - 4 STEPS are there 4:54 - First you can execute profiler execution on the set of PLSQL code 6:14 - First let's start with Environment setup 6:44 - The Second thing is that you need to create the Profiler Table in the Schema where you are going to run the Execution 8:00 - For an Schema or for a Database 8:12 - Once the Profiler Execution is done the next step is Profiler Execution 9:48 - Now Practical of Environment Setup and Profiler Execution 11:40 - Start Profiler ke baad 13:07 - Now we have done our Environment setup 14:10 - Unit Id ko Join kr ke
Thank you so much for educating us with awesome explanation....looking forward to more videos on performance tuning & explain plan....Thanks in advance!
you have really put lot of effort in preparation of these videos, editing, and presentation... unbelievable.. After seeing your videos I cannot watch anybody else's videos. Because your way of communication is diff level. you connect the dots, you answer the question for why, how, what..... advantages and disadvantages of using any feature!!! so that we are able to understand and get max facts in a single video quickly!!!! Great effort Siva!!! You should be in Udemy or CourseEra, etc!!!
0:10 - How to Start with PLSQL Tuning 1:42 - PLSQL Performance tuning start 2:46 - We can use Provided tools DBMS_Profiler 4:02 - How to use DBMS_Profiler 6:14 - First let's start with Environment setup 8:12 - Once the Profiler Execution is done the next step is Profiler Execution 10:30 - Procedre ke andar Procedure 15:35 - Profiler data 18:01 - In fact the last three
Superbly explained and well understood, thank you very much Siva, really appreciate your hard work and your good nature of sharing so much technical knowledge. Regards, Sujaa
inorder to run this profiles commands, i need to connect to the Pluggable database as user sys, but my default connection is set to container database. when i say sqlplus / as sysdba or sqlplus / as sysdba@orclpdb, both commands are connecting to my container database but not to my PDB. this is a problem as i am not able to grant any privilages from user sys to my user scott, any help on this will be appreciated..thank you
Siva sir, can tou let me know if the total_time in data table and total_run_time in runs table are in milliseconds?? Am in middle of resolving an issue and you are my encyclopaedia 😊
Thanks Siva fr sharing the knowledge , I have one doubt is there a way I can profile the procedure being executed from different session or a procedure which is part of some application from my session which is all together different
Hi Siva.. thanks for your informative videos.. This video is blur, not able to see it clear due to low video quality.. could u pls upload this video in higher quality
Hi In the case of a live environment,where many people are sharing the same user, once the profiler has been started will it not capture the data for the other operations also?
@Krishna, You can start learning, ETL, Data Mining, Reporting, Data Analysis, Visualization...etc ( based on your interest), all these are related to Data and Database technologies, so you can able to easily pickup....
Hello sir I have watched all your videos and I just loved it, I had just one request if you could explain to us how you tune your real project code step by step then it would be really great and appreciated 😊
Hello Brother, Big fan of your videos. Wanted learn more on performance tuning. Could you please also do a video on awr and addm reports and how to analyse it and tune our sqls
Hi Siva sir, you are an amazing teacher, you have helped me a lot becoming a db developer. Sir can you please provide some videos on performance tuning of queries
Really nice explanation..you make it so easy for people to understand Can you make more in depth videos on performance tuning and show some real time scenarios please.
In my last Interview, this q was asked...You have global database and u have data of 15 countries like Japan, India, China, UAE etc. You want filtered records as per country like chinese people should see only their data and likewise other country people. how will u manage this in efficient manner. I told that I will make 15 views and then grant permission accordingly. He was not satisfied by my answer, he told that for 15 countries, u will make different 15 views!! How can we handle this situation Siva...Please guide
How to tuning the package which has more than one procedure and function. How to identify which procedure and which query takes more time to execute? How to tuning the package instead of procedure or function?
@Ranganayaki, Thanks for your comment 🙏...Sure, will be posting all the performance related topics in the SQL/PLSQL performance video series, will cover explain plan as part of that....please stay tuned.
While Creating a procedure I am getting error create procedure proc_c is lv_avg_sal number; begin for i in 1..50 loop select avg(salary) into lvl_avg_sal from EMPLOYEE; end loop; end; / Procedure PROC_C compiled LINE/COL ERROR --------- ------------------------------------------------------------- 5/1 PL/SQL: SQL Statement ignored 6/6 PLS-00201: identifier 'LVL_AVG_SAL' must be declared 6/18 PL/SQL: ORA-00904: : invalid identifier Errors: check compiler log Can you help me with this.
Hello, so good explanation, request you to do some more videos regarding performance tuning, especially concentrating on the interview questions on it.
Hello Sir, your video was really very helpful, I got to learnt lot of thing about Performance Tuning from it. Can you plz make me understand about the total-time calculated, how you calculated it. Since it only is main thing. Please..... Warm Regards Sameer Ahmad
Hlo.. small query plz clarify anyone in one column 4 names are there like(rakesh , ajay, ganesh , praveen) i want 2nd name ajay how to do using sql query
select n from ( select n, rownum r from ( select 'rakesh' n from dual union all select 'ajay' n from dual union all select 'ganesh' n from dual union all select 'praveen' n from dual)) where r=2
Thank u for all the wonderful knowledgeable vdos👍👍 just have a doubt...At 17.22 sec, why did u truncate plsql_profiler_data table...as next time we may need it...we should only delete the data from it...plz correct me if i m wrong
Thank you for your comments, yeah its not needed to truncate....i prefer to truncate every time, because its better to query....otherwise we need to query for the specific run id. you can keep the old records as well.
@@SivaAcademy ya..very true...but we can delete all records from the table too, so no old run id and at the same time, we can have structure of the table ready with us.
@@SivaAcademy Sir, ur email id is siva.k.academy@gmail.com? also, on google, I got very different website...having unknown fonts... sivaacademy.ruplayers.com. Is it your website?
I become your fan as you explaining the oracle concept. Can I get a chance to learn oracle performance tuning in some more detail from you. Pls revert🙏
Thank you so much ....you saved my whole week and rewriting the whole procedure in a package.
I was very disturb with nested loop and codes...
I exactly did what you guided here , found perticular queries and modified accordingly to consume less time to run.
Unbelievable results came . Previously it was taking 2 minutes but for same it's taking 19 seconds.
You saved my hours of running long program , now it's completing in few minutes ... drastically optimized well.
Thank you so much again sir.
Welcome, glad that it helped 💐💐👍
What changes you did Arjun. Can you please elaborate . Thank y in advance
The reason you say this so wonderfully is that great gift that God gave you..
It is a great blessing for us to have people like you....
Thankyou so much siva brother..
🙏🙏🙏🙏🙏🙏🙏
It's my pleasure bro
Just one thing to say, Plsql = Siva Academy, That's it!! Hats off Siva!! Thanks for the great efforts from your end.
Thank you 💐🙏
0:09 - How to Start with PLSQL Tuning
1:08 - How and where to start with Performance Tuning an Inbuilt function name DBMS_Profiler
1:42 - PLSQL Performance tuning start
2:46 - We can use Provided tools DBMS_Profiler
4:02 - How to use DBMS_Profiler - 4 STEPS are there
4:54 - First you can execute profiler execution on the set of PLSQL code
6:14 - First let's start with Environment setup
6:44 - The Second thing is that you need to create the Profiler Table in the Schema where you are going to run the Execution
8:00 - For an Schema or for a Database
8:12 - Once the Profiler Execution is done the next step is Profiler Execution
9:48 - Now Practical of Environment Setup and Profiler Execution
11:40 - Start Profiler ke baad
13:07 - Now we have done our Environment setup
14:10 - Unit Id ko Join kr ke
Thank you for the thorough explanation👍. The video provided valuable insights into performance tuning and every minute was enlightening 💡👏
Thank you so much for educating us with awesome explanation....looking forward to more videos on performance tuning & explain plan....Thanks in advance!
Sure, please stay tuned for more videos
Thank you very much for initiating Performance Tuning Videos. Happy to learn through your videos.
@Mugilan, Thanks for your comment bro🙏
Fantastic explanation on this utility. Siva, you are a great teacher! Please keep up the good work.
My pleasure, thank you 💐🙏
you have really put lot of effort in preparation of these videos, editing, and presentation... unbelievable.. After seeing your videos I cannot watch anybody else's videos. Because your way of communication is diff level. you connect the dots, you answer the question for why, how, what..... advantages and disadvantages of using any feature!!! so that we are able to understand and get max facts in a single video quickly!!!! Great effort Siva!!! You should be in Udemy or CourseEra, etc!!!
🙏🙏🙏My pleasure bro 💐💐
Just amazing. Will certainly help in the interviews and the day to day work as well :)
My pleasure, thank you 💐
One of the best videos on performance tuning. Excellent content and perfect example
My pleasure, thank you 💐
0:10 - How to Start with PLSQL Tuning
1:42 - PLSQL Performance tuning start
2:46 - We can use Provided tools DBMS_Profiler
4:02 - How to use DBMS_Profiler
6:14 - First let's start with Environment setup
8:12 - Once the Profiler Execution is done the next step is Profiler Execution
10:30 - Procedre ke andar Procedure
15:35 - Profiler data
18:01 - In fact the last three
You are a great mentor, who always explain the things with quality content. Hats off to you Sir 👍💐🙏🙏
Thank you 💐🙏🙏💐💐
Brief and well explained. Thank you very much for it.
Thank you bro
Hi Siva,
Nice explanation. Video is every clear to understand.
Regards,
Venky
Thank you Venky 💐
Really shiva your teaching technique is awesome..bro be continueee really nice explanation ..I watched your all videos ..so nice
@Ravi, Thanks you.... :-)
Great explanation Shiva . You are true legend in Teaching. Keep good work Sir
Welcome bro
Such a awesome explanation about dbms_profiler i am really impressed your class sir keep post like this its really good things to know....
Thank you Ganesh, sure please stay tuned
Hi siva ,i like very much this vedio for understanding the concept of tuning.thank you very much for sharing your knowledge to people.
@Ganesh, Its my pleasure, Thanks a lot for your comment.
Thanks Siva Sir. All the videos you posting are very informative and really helpful. You are doing an Awesome Job. :)
@Bharath, Thanks 🙏 for your comment bro
Very useful and Nicely explained. Helped me a lot.
Thanks a lot!!!
Glad it helped!
Thanks for the video, I used this for performance improvement.
Very well explained. Thank You. Siva Keep uploading more performance Tuning videos in this playlist.
Sure, please stay tuned
Great session . Thank you so much sir
My pleasure, thank you
Very well articulated and explained. Thank you, sir.
Welcome 🙏💐
Superbly explained and well understood, thank you very much Siva, really appreciate your hard work and your good nature of sharing so much technical knowledge.
Regards,
Sujaa
Welcome....:-)
Thanks a lot all your comments. Please share it with someone who are in need.
What is the actual query change you did to improve performance ?
Hi Siva, I liked your videos . And they helped me to learn a lot in Oracle. thanks.
My pleasure 🙏 thank you
Very good presentation. Nicely arranged. Thanks.
Thank you
Excellent presentation. very clearly explained.Please do post more videos
🙏, sure please stay tuned for more videos
Thank you so much brother for uploading performance tuning
🙏🙏🙏🙏🙏🙏 :-) Always Welcome bro :-)
awesome dude....Nice explanation.Thanks for your videos.Keep going.
Welcome 🙏💐
excellent...I think you are the best one explaining complex topics in oracle....keep going Siva....sorry Sir😊
My pleasure, thank you 😊🙏👍💐
I have question if any table has huge data and it is causing performance issue can i know how can we overcome this
You are great sir. Thanks a lot for your help.
Welcome bro
Thank you so much, Siva. You opened a door for me!
My pleasure 💐
One doubt I have does the DBMS_PROFILER be started even though the PL/SQL code is running already in the background?
Hi sir, I have new requirement. In PL/SQL procedure creating time when exception error is raised that time, error message is sent via mail to us.
Hi Sir,
I'm getting while starting DBMS_Profiler as 0ra-00900
I have grated exec access also still no able to invoke dbms_profiler.start_profiler
we can do this by using dictionary tables like v$sql,v$session can u explain this also?
Excellent video bro 👍👏
Thank you
can you interpretation of explain plan output in oracle and how to optimize with respect to the explain plan output.
Thank you so much for covering performance tuning topic sir!!
@Mahendra, Welcome bro..🙏
Siva debugging with many parameters ( package and procedure)?
inorder to run this profiles commands, i need to connect to the Pluggable database as user sys, but my default connection is set to container database. when i say sqlplus / as sysdba or sqlplus / as sysdba@orclpdb, both commands are connecting to my container database but not to my PDB. this is a problem as i am not able to grant any privilages from user sys to my user scott, any help on this will be appreciated..thank you
Thank you for your detailed explanation:)
Welcome bro
Can we execute select query instead of procedure once we start the profiler
Thanks a lot Sir... for starting videos on performance tuning... hope you will upload all the videos of Complete Performance Tuning.
@Rajender, since the performance tuning is wide area, i will try to post whatever i know on regular basis......please stay tuned.
Sure Sir... Thanks a lot for your Reply.
Siva, can u pls explain the DBMS stats table and schema concept
very good buddy. thank you so much
Thank you bro
Siva sir, can tou let me know if the total_time in data table and total_run_time in runs table are in milliseconds?? Am in middle of resolving an issue and you are my encyclopaedia 😊
Is it only for oracle or applicable for postgresql?
This is Oracle specific package, postgres should have some similar concept
plz make playlist about performance tuning for oracle sql ?
Thanks Siva fr sharing the knowledge , I have one doubt is there a way I can profile the procedure being executed from different session or a procedure which is part of some application from my session which is all together different
I dont think so, however, i will check and post if its possible
Great videos man.Keep up the good work.
Welcome
Hi Siva.. thanks for your informative videos..
This video is blur, not able to see it clear due to low video quality.. could u pls upload this video in higher quality
Hi sir, could you please reply if we can execute select query instead of procedure once we start the profiler
Yes, you can, but if you already know the query that takes time, then there is no need to execute using profiler. you can start tuning your query
Hi In the case of a live environment,where many people are sharing the same user, once the profiler has been started will it not capture the data for the other operations also?
No, its session specific. Only your session data will be captured.
How to become a plsql developer. What are the basics required to learn & what skills need to improve. Please assist me
Excellent... Can you please provide vedio on how to debug plsql code?
Dear sir,
Please suggest any other usefull Technology for PLSQL developers....
@Krishna, You can start learning, ETL, Data Mining, Reporting, Data Analysis, Visualization...etc ( based on your interest), all these are related to Data and Database technologies, so you can able to easily pickup....
@@SivaAcademy
Thank You very much for your Suggestion sir...
Hi sit , do you have videos for Explain plan in part of performance tuning ?
Not yet, please stay tuned
Hello sir I have watched all your videos and I just loved it, I had just one request if you could explain to us how you tune your real project code step by step then it would be really great and appreciated 😊
Thank you bro, sure please stay tuned.
Hello Brother, Big fan of your videos. Wanted learn more on performance tuning. Could you please also do a video on awr and addm reports and how to analyse it and tune our sqls
Excellent presentation sir...
Welcome 💐
Very nice presentation..
Thanks a lot
Thank you for the beautiful content.
Welcome bro
Thank you very much sir and could you please share which dml queries take more time in terms of data, like merge or update for huge table
sure, please stay tuned
Is this functionality available in 11g?? If it is there could you please tell me the path for proftab.sql file??
Thanks sir, really very nice explanation. Thanks a lot..
@koothan, thanks for your comments bro
Great explanation
Thank you
how to change clob data into file and attach the data to mail oracle 12c
Hi Siva sir, you are an amazing teacher, you have helped me a lot becoming a db developer.
Sir can you please provide some videos on performance tuning of queries
Its my pleasure, thank you, Best wishes 💐💐
Really nice explanation..you make it so easy for people to understand
Can you make more in depth videos on performance tuning and show some real time scenarios please.
Sure, will post videos on performance tuning, Please stay tuned
In my last Interview, this q was asked...You have global database and u have data of 15 countries like Japan, India, China, UAE etc. You want filtered records as per country like chinese people should see only their data and likewise other country people. how will u manage this in efficient manner. I told that I will make 15 views and then grant permission accordingly. He was not satisfied by my answer, he told that for 15 countries, u will make different 15 views!! How can we handle this situation Siva...Please guide
Interesting question, I will add to upcoming list of videos, please stay tuned
@@SivaAcademy Thanks Siva..
Thanks for sharing valuable knowledge
It's my pleasure,Thank you so much :-)
Hi Sir ,
How you generate the graph ? which you presented in Last slide .
@ur, that I have created from excel sheet
Sir, can we do this on production ? as it contains insertions, deletions etc..
Generally we won't do this in production, Unless its absolutely necessary
Great video. Thank you sir.
Welcome bro
What is the value in the time column in the plsql_profiler_data table? is not microseconds? Thank you
I guess it's microseconds only
Sir please tell DBMS_Profiler is Only used to check Performance of PLSQL not for SQL Query?
Yes Vicky, DBMS_PROFILER will identify the line of SQL, you can use explain plan to proceed with specific SQL tuning.
@@SivaAcademy Sir,But there is a difference between Explain Plan and DBMS_Profiler Package? Right?
Really Helpful 👍👍👍👍
Welcome 💐
What a explained excellent sir.. Is it your online classes there total starting to end I mean plsql tutorial to real time project till...
Welcome 🙏💐🙂
How to tuning the package which has more than one procedure and function. How to identify which procedure and which query takes more time to execute? How to tuning the package instead of procedure or function?
Package tuning is nothing but tuning underlying sub programs only.... Couldn't get your question
Thank you sir, for the informative video.
Request you for an explanation on the Explain Plan.
@Ranganayaki, Thanks for your comment 🙏...Sure, will be posting all the performance related topics in the SQL/PLSQL performance video series, will cover explain plan as part of that....please stay tuned.
While Creating a procedure I am getting error
create procedure proc_c is
lv_avg_sal number;
begin
for i in 1..50 loop
select avg(salary)
into lvl_avg_sal
from EMPLOYEE;
end loop;
end;
/
Procedure PROC_C compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
6/6 PLS-00201: identifier 'LVL_AVG_SAL' must be declared
6/18 PL/SQL: ORA-00904: : invalid identifier
Errors: check compiler log
Can you help me with this.
The declared variable name is different than used one, please correct the same
very useful , thanks siva!
You are welcome 😊
Can I use dbms_profiler to get the package performance also ?
Yes, you can,dbms_profiler will help to find the location of problem, we need to further tune the code
@@SivaAcademy Thanks 😊
Hello, so good explanation, request you to do some more videos regarding performance tuning, especially concentrating on the interview questions on it.
Sure please stay tuned
Hello Sir,
your video was really very helpful, I got to learnt lot of thing about Performance Tuning from it.
Can you plz make me understand about the total-time calculated, how you calculated it.
Since it only is main thing. Please.....
Warm Regards
Sameer Ahmad
Hi Siva it very nice.
Kindly come up with a small code and show us how you tune it with example.
Sure, I was not able to cover in single video, will try to cover in some other video
Hlo..
small query plz clarify anyone
in one column 4 names are there like(rakesh , ajay, ganesh , praveen) i want 2nd name ajay
how to do using sql query
select n from (
select n, rownum r from (
select 'rakesh' n from dual
union all
select 'ajay' n from dual
union all
select 'ganesh' n from dual
union all
select 'praveen' n from dual)) where r=2
thank you so much, sir,,, finally got it performance tuning video,,
@Rahul, 🙏 welcome bro
Thank u for all the wonderful knowledgeable vdos👍👍 just have a doubt...At 17.22 sec, why did u truncate plsql_profiler_data table...as next time we may need it...we should only delete the data from it...plz correct me if i m wrong
Thank you for your comments,
yeah its not needed to truncate....i prefer to truncate every time, because its better to query....otherwise we need to query for the specific run id. you can keep the old records as well.
@@SivaAcademy ya..very true...but we can delete all records from the table too, so no old run id and at the same time, we can have structure of the table ready with us.
@@SivaAcademy Sir, ur email id is siva.k.academy@gmail.com? also, on google, I got very different website...having unknown fonts...
sivaacademy.ruplayers.com. Is it your website?
Siva.k.academy@gmail.com, this is my mail id
very useful, thank you
Thank you
Good job brother, thanks, really like it
Welcome 🙏
@Shiva How to get in touch with you? If Any email address then please mention here.
Thanks for all 🙏🏻
Eventually you started performance tuning ...just Awesome bro keep it up. But I have a little doubt like profiler and dbms_profiler are same?
Thanks... Both are same bro, profiler is just a generic term we use, however dbms_profiler is the package to profile the code
Sir plz make more series on performance tuning. Is it already done ?
sure, please stay tuned
I become your fan as you explaining the oracle concept.
Can I get a chance to learn oracle performance tuning in some more detail from you. Pls revert🙏
Its my pleasure, please stay tuned, i will post videos on performance tuning also