welcome to the session in our today's session we'll start with performance tuning in Oracle so whenever we are using Oracle database or any other databases if you're handling with huge volume of data so definitely Performance Tuning is the very important one so even if you're a ETL developer the same Performance Tuning you might be doing it in the Informatica Power Center or any other tools okay if you go to any databases like Oracle SQL db2 or Terra dat or any databases in realtime projects definitely will be handling huge volume of data so if you handling huge volume of data then Performance Tuning will be a very important one okay mostly like uh if you're in ETL environment then mostly like fact tables so in terms of PB will have the data yes definitely it will have the terabytes of data so definitely we need to do a performance tuning the most important the most widely used performance tuning is the partitioning one so we do have different methods of Performance Tuning so that we will see one by one so first of all whenever we are doing the performance tuning so first we have to go for explain plan so you are running big queries SQL queries you are running that and definitely we need to go for we need to check how the SQL query is running so explain plan so you have to run an explain plan first you can run the explain plan and it will tell you how the query is running then based on that how much cost it is taking so all this it will tell you okay cost CPU usage and everything it will tell you so by using that explain plan you can you can do the performance training okay whether it is going for Full Table scan or it is going for any other scan types So based on that you have to decide so this is the one method we can go for so explain plan then you can go for something like collect stats you can put collect stats so if it is very big table you can do the collect stats so in order to do the collect statistics so that means it is a metadata table whenever it has the huge volume of data so everything if I want to go for statistics update then Oracle will do the collect stats so collect stats is mainly used to see the performance okay how the not performance it is see it is used to improve the performance okay one of the method and the collect stats okay so then you'll do the the hints you'll use I'll will tell you what is that hints so we'll have some parallel hints all this then the optimizer okay so you'll be using that Optimizer Optimizer is nothing but the optimizer knows it how to execute this particular queries okay say for an example you are you are moving from one location to another location very first time you are moving so that time you will be plan you will put the plan right okay how can I go from this particular location to this particular location so you'll put explain ex execution plan so next time if you running the same query so it will go and check the the optimizer execution plan and then it will execute so that's what it will go for ex the optimizer one and then it will you can use the partitioning the part partion is very very important one okay we will see one by one so partition here we cannot run it so why because most of the performance tuning method so we will not face any uh issues here that's one thing and also this partitioning we can do it in the express sorry Enterprise Edition only so not with Express Edition so in Express Edition so in XC we will not have it so you will have you have a real time system then you can run this if you have any Oracle database development environment you can create the partitioning and then you can check how it is running okay even standard edition also you will not see the partitioning okay so Enterprise Edition yes definitely you'll see if you get the chance to install Enterprise Edition then you can work on this partitioning okay but I will explain so what are the things we have to consider while doing the performance including in realtime project so when ever you are working on any realtime project right so directly don't apply select star from the table name okay so don't use select star from this any table name so like this don't use that so why because this table contains huge volume of data okay so don't use it so first of all describe the table okay so describe the table check so what is the volume of the table and you volume of the table described table you cannot find but you can find whether the table has partition if it is a fact table right yes you'll be finding the partition column so you have to use in the v class and don't use star here so always use whatever the columns you want so even if you has multiple columns table so use only that particular columns alone so whatever the column you need so that particular column alone you use don't use select star from the table name and also for an example in real time project even if you are using any data bar using tables so don't use that select star from the table name don't use this select star from instead of that use that table okay if you want to check some few data in this particular table then use if it is Terra data then use something like sample five or something if it is any other databases you can use if it is Oracle database use ronom so ronom less than or equal to five so it will select five records only so where ROM equal to less than or equal to 5 so even if the table contains millions of data it will select only five de cards right so you you can check it how the data and also don't use star and don't use all the columns names also whatever the column names needed so that particular column alone use it okay and if it is any other for an example AWS red shift you are using use that limit okay limit five so simply don't use where so limit five just it will select five records in that way you can select okay so I have given one document here just to go through that this is a galzed performance tuning methods so whenever you you are going with and cost effective methods go for that cost effective method and also the performance tuning is always is happening okay it is not so you have tuned today but after some time don't need to do that it doesn't mean that okay so Performance Tuning is always we are to do so whenever the data volume is growing up the realtime project yeah definitely you'll be doing that performance tring so always minimize the amount of data okay so amount of data that must be scanned so don't go for full stable scan always so whatever the data you want to select use filter class and filter out that particular record alone use some uh ranges like a V class you can apply date range created by this particular time period that will reduce the the number of record scanning okay so that's what you have to use and add more indexes on the table so indexes on the table but if that if if you are going for frequently insert and update on the tables don't use indexes okay if you're frequently inserting or updating on the table don't use indexes so whatever the select statement you are going to do on the tables so something like that then you can use indexes so do not include star in the select statement always use the column names so that's why in Informatica or I or any ETL tool you are going to use it so automatically it will select the column names okay and also don't use unnecessary columns if you're selecting unnecessary colums then it will create even if it is uh Informatica Power Center if you're going to use lookup catch and everything if you're using select statement it will create a lot of catching right so don't use that and whatever the record data that alone you can use it so even in the database use filter and these are all the small small things things you have to consider and whenever you are going for join so use appropriate join conditions so don't go for any cartisan product okay so instead of in keyword you can go for exist keyword that will perform very fastly so see here do not use indexes to the tables that undergo more update or insert so if you're going for more update or insert or bulk insert or bulk update don't use indexes first so you drop the index do the buk insert update and after that you create it okay that's what we will do and use some temporary tables say for an example you are building lot of in the joints in the joints quy you are using some 10 to 15 tables 10 tables you are using so don't use all the 10 tables in the joints so it will take lot of memory so if it is a huge table don't use that so instead create some temp tables create some temp tables and use the key keyword called with so by using that you can able to create easily you can use the query will perform okay for an example two to three tables you are combining and then creating the temp tables loading the data and then if you're joining it it will be very easy even if you're going to deploy any code right normally we will do time table creation and then the same simply they are using select start from the table name select statement you are using some reports in the report queries if you're going to check in the in the above is you can easily find out they should have created some temp tables so intermediately they will create some tables and then from the tables you can do the joints if you're going for more number of tables so these are all the some basic method to improve the performance in addition to this in addition to this we can go for some other performance wiing method okay so DBS also they will do performance Shing so they will increase the catching okay definitely infrastructure also it's one of the main thing for the infrastructure so they will improve the catching size with the performance Improvement so all the catching size they will improve and uh the table space they will improve and they will go for more spaces on the database side to improve the performance that they will do it on infrastructure side so that means DBA they will take it but ourself as the developer the SQL query developer so we have to consider few things so what is that so first of all whatever the query is running just go for explain plan so explain plan for this particular table so whenever you are doing explain plan so it will give you what is a join method is performing so some different join methods are there I will tell you what are the different three join methods are there so that it will tell you so what is the join method it is using and the access method for each table how it is accessing the tables and uh the different operations so whether it is going for sorting algorithm aggregation filter so all this it will go for select statement so all this it will it will show you that's what explain plan will give you so join method we have three different join methods are there so I will explain that so first join method is nested join nested Loop joint so what is that nested Loop joint right so nested Loop join means if the two tables are small table okay so whatever the query you are going to do so if you're going for explain plan so how the Oracle will do that so we'll create nested Loop joint then it will Loop each and every record compare with other tables and then it will give you the result P join is the fastest join it will do one table in the hash memory okay the cach memory other table it will keep it in the outside then it will compare with each and every record it will do the joints sorting means if you're going for two tables join so one table will take it to the catching and then it will sort the data So based on that it will key column it will join it and then it will produce a result okay these are all the three join method it will do are we going to do no we are not going to do so database will do it we we may not know what is the operation it is doing but database will do so if you are going for any explain plan so these are all the access method you will be seeing that the one thing is first one is Full Table scan so normally we'll call it as FTS that means the table is going for the if you running any select statement so database is going to check all the data one by one so that's why you can go for the Full Table scan table access by row ID so each row ID it will select it will store and then row ID it's going for so if you're creating a primary key or uni key then it will go for Unique index so unique index it will go for range index means range of values you're selecting V class some range of values okay this value to this value if that column has been indexed then it will go for range scan and index skip so not between or not equal if we going for index column then it will go for a Skip and full index so all the data it will go for and these are all the access method different access method I will show you then you'll get a clear idea I'm going to create one table here create table table name employees select star from this way I just created one table okay emplo two if you check this particular table it doesn't have any indexes okay you know all indexes right so select star from allore indexes where owner equal to HR just I'm selecting so these are all the tables it's showing then table name table name equal to and table name equal to I'm going for employees 2 it doesn't have any indexes so if you describe the table describe emplo 2 then you can find the column names right so these are all the column names you are seeing that okay so how the indexes will work say for example I'm running on Cory select star from table name employees where class where salary I'm going to use greater than 8,000 I'm just using this query it is selecting 32 records and then it's showing but how this quy is running I want to know how the Oracle is going to check see if you have how many records there in the table the emplo two you see this one6 records it has how the article will run whether it will go and check directly 8,000 or all getting 8,000 or it will go and compare each and every record so it will go and it will compare each and every record right that is our Full Table scan so if you want to check how this query is running then you have to run explain plan okay so run explain plan for so always use the select statement okay so explain plan for select statement you can run this way so if you are running this way then it it will show you explained so after that you can use okay this has been explained and then it will store in the one particular package so that package is you can select in this way select star from so you can use I will give you this particular statement so select start from table of dbms uncore xplan do display so if you're going to run this how this quy is running see here the very first query is running select statement and the second one it is going for Full Table scan so full table access employees table you are seeing the cardinality it is taking one one4 records okay so this particular cardinality means it is taking one4 records checking how many records it's going for one4 records and cost is three three here okay three it's this is what it is showing you are using some filter condition filter to 8,000 okay so you can run this way the other way is in SQL Developer you can use simply don't use explain plan just to select this quy you can see here explain plan for right so you can run this it will give you okay Full Table scan cardinality is going for 14 104 record that means these many records it's checking and cost is three CPU cost and this is what it is using then the database version and all this it is showing hints okay how this it it is using so this this is what it is running the query is running and you can see or you can select this particular statement you can press F10 so press F10 in your system then it will give you the same same result okay but if you running this is the best method explain plan for just explain it you can see the hash value here for this particular explain plan okay so it's going for Full Table scan that means each and every record it's comparing and then it is giving you the result okay but uh I'm going to create an index on this particular table on the particular column so whatever the column you are frequently quing a column so that column you do the indexing the V class so I'm going to create an index so how will you create an index on salary column we can go for B3 Index right okay create index index name idx empore salary _ idx on what is the table employees table salary you are going for the column name salary this column I'm going to create it but I will take this particular plan see this plan is for CPU cost is three employee two only I have to do so why because employee one already indexed so employ 2 it is going for 103 records so here also cost is three so it doesn't have any indexes all this so I'm just creating index on this particular table so index has been created so now I will go and execute this particular statement okay explained and I'm checking the explain plan okay still it is going for the same cost session we will we will terminate the session so you explain plan for select star from so again it is going for Full Table scan that means it is comparing with each and other record this particular index is not correct the column you are selecting right it is not going for the Full Table scan it is going for the index range can so range of values you are selecting greater than 8,000 right so that means the cost is very less here so 012 select statement so how to interpret this explain plan I will explain later but this is what it is going for the indexing but previously it was selecting 104 records right so now the cardinality is 11 records only okay it's checking these many records only so that is what the the cost has been reduced here okay if you are going for huge volume of data definitely you can you can find this cost has been reduced very very less is going for index range scan right so how to interpret this particular explain plan so normally you have to go this way first you take the topmost one so first you take one so first one is uh zero soorry zero here then it is going for the table access by index row ID that is one so this is zero then this is one here there is no parallel sorry there is no parallel one right so you can use one here simply you can use one so this step is executing then it is going for the next one next step so if you see this index range scan so that is two so if you have to write this way but still has all this hierarchy here see here there is no in between step here so that's why I have written this way so while you are you are interpreting how the article will run so from the bottom it will run from the bottom left hand side it will run so this way this is the way it will run so there is no Leaf there is no Branch here so it will go from the below Bon first it will execute the Second Step so index range scan it will execute this particular uh index name and then the cost is one then only it will go for the table access by index row ID then it will run the select statement on top of this only level record it is taking row ID it is taking that means select star from this record so 32 records are there but the indexing it is making only 11 so it might be having some duplicate record so distinct salary if I'm going to take count of distinct salary so I'm going to select so 18 we have but it is making the the explain plan sorry uh 11 records it's making and this is these many bytes see whatever it is taking the row ID a row ID given to the select statement and then it is selecting so this is what you have to interpret and you can see this is going for the explain plan right so this way so if you want to go for a complex one so what I'm going to do select start from departments 2 this is another table I have okay departments 2 so what I'm going to do now employees table departments table I'm going to join it employees and departments I'm doing the join okay so I'm just running that it will give you the data so here the department ID is the joining column so I'm going for explain plan so before that I will clear this particular session explain plan for employees 2 departments 2 I'm not I'm not changing any data in the employees table just I want to do the emplo two table okay so I'm going to run this explan plan it has been explained so now if you go and check you can find so how this is running the emplo two table if you see this now this is going for nexted Loops okay nexted Loops cost you can see here how it is running so very first time when we are going this 0 1 2 and three is parallel execution you can see here parallel execution right so that means Me 0 1 0 1 then two and three is parall so how arle will run right so this way it will run this is second step this is third step while moving from top to bottom you have to take but left hand side from left hand side okay so first it will run Second Step what is the second step table access so it will take all the data from a Department's table okay it is taking all the data from Department's table and table access full all the data from employees table then it is going for the nested Loop the first step so it is taking all the data here in the cat memory it's taking all the data here then these two it is going for the nested loops okay based on the joining column Nest Loops Nest Loop means small tables it will go for the nest Loops so I will give you one document here go through this particular document explain plan you will find different concept here so cardinality is nothing but number of Records in the ques and we'll have the join method so if you if you see the join method so what is the joining it is running okay hash join so lot of hash this is the fastest method the join method you can see has join means F joins are used for joining a large data set see whenever your your qu is running so if it is going for the hash joint it will go for large data set first if two tables are lost data set large data set then it will go for hash join what is mean by hashjin the optimizer Optimizer means article will run that query right that is Optimizer uses the smaller of the two tables so we running employees table and Department's table department is small table and normally that is what it will normally even in informatic also Informatica it will run in this way only right smaller table it will go into the cash memory that's what master table will create a small table right same way here also smaller of the two tables are data sources to build has table based on the join key in the memory then scans the large table and perform this slash algorithm and it will prod produce the result got it if you have two tables so if you're having a big tables here big tables and you are the small tables this small tables will be moved to the catch memory so catch memory then each record from here it will be compared with all other records in the large tables it will produce a result this is the hash algorithm nested Loop means both are small tables okay so nested Loop means see useful when small subset of data are being joined if there is a efficiency way of accessing the table so for every Row in the first table will be compared with second row and then it will produce the result of the far Loops each and every record that's what it will go for the N Loops so it is taking data from these two tables and it is doing the nested Loops each and every record has been compared and then it is producing the result select statement okay so what we are we can how can we Implement some tuning method for this particular query so different method is there one of the the method is indexing right you can create an index so on that see you have the nested Loops right after that sort and merge joints so whenever you're using any greater than less than greater than or equal to less than or equal to any conditions if you're using that so first it will go for sorting algorithm then it will merge the data so that's what it will go for sort and merge joints so normally this is what it will do see here j s joint first it will use and then result will be merged see whenever this this is totally seven steps are there so you have to go from the first one okay one then you have to see very the parallel way do we have any steps no one two three so three also there is no step four yes you see this three third step and fifth steps are common both are in same step play right so this two in branch and after that this one this one in same branch and inside the partition range all going for the Full Table scan so this one it is going for the Full Table scan that's what you have to interpret one by one we can check how it is producing the result okay but we are not going to do anything so Oracle will take you see this I'm going to create an index on the department ID column so normally you can create bit map index but we do not have access here I will create normal index here so create index index name empore Dore idx on employees 2 table the department ID column same way departments two card departments two table I'm going to create one more indexing okay departments to Department ID just I have created in one more index on this so now I'm going for the explain plan so before that we have seen different way right now we will see the different way so I will clear that I will run one more time explain plan so this time it will not go for Full Table scan so two table two table and I'm just running for explain plan has been explained so now I will run this query see previously it was taking Full Table scan right so now it is going for see no normally no need to clear that session but why I'm clearing it it is giving you the previous explan plan result that's why I'm just clearing is not automatically clear in my system okay see here it is going for this way so six steps are there totally so how can we interpret first say two tables emplo to this table so first we will go for zero select statement okay the top we have zero so zero here what is this so this is the top select statement the top then so how to interpret what is the of this one then it is going for the nested Loops do we have any branch on this no Mr Loops one so you can go for Mr Loops one the step then it is going for second Nest Loops in the second and the fifth step are parallel right so you can see here both are in the same Branch so second step and step both are in parallel way it's running it is going for this one then you have to take these two three and four so three and four from second step right so three and four is running in parallel so now tell me which Step will run first so which Step will run first can you tell me now so always you have to go from from bottom left to top bottom left to top okay so always you have to go this way so first you have to go with third step third step only it will run first so which table it will take first it will take Department's table Full Table scan so Oracle will first very first time while running this particular query how it will run so Department's two table it will go for Full Table scan so that's a third step then it will take the fourth step fourth step is it is going for the index range scan index SC uh index range scan on employe Department index so employees Department index means so it is taking this table and this table right so employees Department index so in that way first it is running third then fourth uh how we are able to identify that is running parall I mean you're just making right this step is running so it's par but I'm getting out over there so if you see this first it will run the first zero step okay that is Select statement so do we have any parallel step here no right there is no parall step then it will run this one right first step so do we have any step here any parallel step for this no no right then the step do we have any parallel step for this yes second and five is parallel right yes so it will run so that's why I have put two and five in parallel then third and fourth so if you take third and fourth okay see sorry three and four so three and four is parallel first it will run the zero zero Step Zero here then it is going for the one one is not in the same line right is it in the same line nested Loops no that's why I'm making like Branch next line then nested Loops then one so do we have any parall line for this one so this is what it will run so from first you have to go so from here it will it will run so whenever you are going to interpret you have to interpret from the bottom left to the top okay to the top you have to go for so you have to take third step and fourth step and it has to take it from the Second Step so these two second step it will run first it will take the department table Department's two table and then it will go for the nested loop on the employees two table with the range of indexing so index range SC then the resultant will go to the First Step next loop on the second step and fifth step so what is that second step so the resultant of these two right will be taken and the fifth step is table access by index row ID that means emplo two table it will go for again the index row ID and then it will go for the select statement the top so that's what it will interpret and it will produce a result so when we are not going to do anything as a developer we are not going to do but since you have created the index you can check whether it has been improved the performance or not okay see here this it is showing that where it is starting from the starting from index on the employees department idx so what is that employee department and idx so this index in the employees 2 table right so employees two table based on the department ID it will create a range scan based on the row ID row ID means each and every record it will go for the row ID so how it is making the row number the same way row ID will be there so based on the row ID it will go and check so this is what it will go for the explain plan if you're creating indexing so you can go for any other complex SQL also complex SQL also same way you have to interpret any complex SQL if you see this this table I'm going for the explain plan so normally in my system it is not clearing the explain plan so that's why I'm doing that explain plan clear just to run explain plan for three tables time so all the three tables but I think on this table there is no indexing so how it is running how the St step you have to run so first you take zero step so even if you are not clear how it is running check like this straight line do we have any parallel line for zero no right so that means zero is running first zero step this one only will run last okay you have to go from bottom so then you have to go for First Step first step you take do we have any parallel no there is no parallel right so you have to run you have to put first here from the first step you can see two and six are straight line right two and six are running in parallel so that means you have to take and six two and six then you have to take these two three and four so three and four from where you have to draw from second right see the these two is running parallely from this step from second so this is third step this is fourth step these two are parallel okay then you have from fourth step to fifth step from fourth step you have the fifth so first you have to run first you have to take the fifth step clear on this now you are clear so first it will run fifth step only it will run so Department location index so that's going for the Departments locations index range scan it is running okay after that you have to take third table the third step table accessible yes location stability is taking and fourth one is Department stability is taking both and it is going for the nested Loop Second Step the St loop with this resultant the sixth step is employees table it's taking the employees table whatever the resultant we are getting that the employee table then it is going for the first step Nest Loops these two is comparing this small tables right then both are like see first it is taking locations and departments only based on the index some index is created some Department I think Department ID we have created based on the index it is going for what is nister loop Nest Loop joints are useful when small subset of datas are being joined so are we going to do Nester loops no we are not going to do Nest Loops who will do Oracle will do it if small table then it will go for nest Loops if it is a big table then it will go for hash joint okay large data set it will go for hash joint when it will go for sort and Mer whenever you have any greater than less than symbol okay then it will go for S and Mer it will go for you can test it you can use something like some left outer right outer all this joint then you can see this merge joint partition means it is making all the partian product so when you have time just to go through this particular document you will get to know all this clearly see cost what is the cost Oracle Optimizer is a cost based Optimizer so whenever from Oracle 7 7th version before that we have the rule based Optimizer after that we have the cost bed Optimizer from article 7 the execution plan okay so how see if you are going from India to USA the very first time you are going from India to USA okay very first time you are going so what will you do normally okay so what will you do so that's nothing the star is nothing but it's going for the the indexing so that's a leaf leaf notes last note so you are going from India to USA so you assume that very first time you are going so direct there is no direct from India also you are going from some locations so you're going from Hyderabad or Chennai this particular location you'll be going for some transition right so you can go for Chennai to some locations Mumbai or Delhi then Delhi to some other locations then so you'll be going with some Gulf countries and then you are going to USA right so very first time you are moving from this web so he will be planning all this okay how can I go to this particular one okay second time you are moving second time you are moving so this is some other way Transit you are making and then you are going with third time you're are going so if you're running the same queries again and again Oracle knows it how to execute it it will store the the optimizer will store the execution plan so what is is the easiest method to execute right so if you're running from India to USA many times then you will know which flight is very cheapest way and which is the very uh short timing okay it will take right so that you may know so why because you are getting multiple times you are running right that's way so that's what it is making the cost based Optimizer I will tell you op what is the role of Optimizer the optimizer selects the execution plan with the lowest cost okay so which is the lowest cost where the cost represents the estimated resource usage for that plan the lower the cost the more efficient right so input output ipu network resources everything will be used so that's what the the lowest plan okay got it see to cardinality if you're seeing estimate of the number of rows coming out of each operations so that is the one thing and access method is the access uh the the different methods we have right whether it is going for Full Table scan index access all this you are going with and join method is this join method is using join type is what is the join type is using this join type we will mention in the query and partitioning I will tell you okay so parallel execution all this you are able to see okay so number of Records so all this it has been mentioned here so I will give you this particular document you go through this document you will you will get to know clearly how to interpret all this so you have to run always explain plan check whether how the explain plan is running from that you have to identify so whether it is going for Full Table scan see even it is going for the Full Table scan right so you have to optimize this don't go for Full Table scan still it is going for the Full Table scan so create some indexing on the department ID column then go for it or create the partitioning so if you're creating the partitioning here Department ID and location ID indexing all this it will not go for this much Full Table scan so first six six step uh it is what the prediction okay you ask star here right star here is nothing but this is what it will display here so what is that so access D do location ID equal to l. location ID this is the least step right then it will go for Department ID equal to Department ID filter access it is doing star is mean meaning meaning has been explained in the blow so what is operate operations ID so ID number operations name of the operations how many rows is returning cardinality and cost and how much time it is taking so all this you can able to find so this is nothing but your the explain plan you can select from this table also select star from plan table okay explain plan for you have to put always select sorry select statement how the select statement is running so do the collect stats and then check how the partitioning sorry explan plan is working then you can find okay it's running fastly then you can use hints Optimizer all this see Optimizer we have two based two Optimizer one is Rule based Optimizer another one is cost based Optimizer nowadays we are using only cost based Optimizer is nothing but we are the user okay you assume that we are the user we are running SQL query we are running just one SQL query see that this is the first one you are just executing one SQL statement select star any any exe joints or something you executing first step is parer so what is parer will do this select statement is our understanding is for our understanding but how in computer will execute this computer will go for parsing engine will convert this one into zeros and ones machine executable language so that only it will convert okay then Optimizer Optimizer will check whether this query has been whether this query has the plan execution plan previously execution plan or not it will check so it will go for cost based optimization that means it will check in the dictionary okay so if you are running any query how to execute that whether we have yes so you are running from you you are moving from India to USA very first time do we have do you have any dictionary any reference or something no you will not have it very first time you have to go after that you'll get some experiences right so that experiences is nothing but see in our memory we will store it our in our brain we will store it but how computer will store computer will store it in the dictionary tables as the statistics then the optimizer will check whether it has been executed already cost based Optimizer will check this execution plan already exist or not it will give you the query plan to the Rose row Source generator then it will execute the execution scale execution will be happening it will give you the result so normally this is what it will execute the optimizer will execute each and every statement in that way so from Oracle 7 you have to use the Oracle will use cost based Optimizer only not rule based Optimizer so this way it will not use it will go for cost based Optimizer it's nothing but already execution plan will be stored okay how many tables are there two two three tables so you are running same reporting queries multiple times very first time only very first time if you're running a report query then it will take much time then it will go on creates see say for an example in the in the mobile also you'll be hasing some you are you'll be seeing some hash file right hasch file so what is that file see whenever you are accessing that particular folder or something very first time then it will make some indexing so with that it will next time it will give you the each and every result very quickly right any picture or anything the same way here also it will be doing so that's what the optimizer hints will do see that you can use hints to specify the following see normally you'll be seeing this kind of this kind of hints in the realtime project slash something something like 32 bit 64 all rows something you are using so it is informing so lot of hints is available cost based approach so you can inform them okay inform article that so how to execute this particular query then based on that it will execute so normally we used to put something like star 64 that means you are running parallelism parall you are running parallel hints this is called parallel hints you are running the same query in a parallel way instead of for running this entire K as in a single pipeline you can go for parallel pipeline then you can use execute this particular query very quickly so just you have to mention but not all the queries if you're using all the queries right then it will take lot of time see this parallel hints see parallel HR five parallelism you are running then it will split this table into five parallel Full Table into five parallel then it will make uh very quickly so this is a 10 first 10 rows only we selecting so use exist keyword instead of in keyword that's normal we will use okay so these are all the methods to use some Performance Tuning but still we have some methods the the best method is partitioning most widely used method is partitioning in realtime project whenever you are going for any fact tables lot of fact tables will be there right in the in the onment so definitely you can go and check some described table you can put and you can see partitioning will be there in the table we will continue with partitioning see partitioning only it's very very important uh in the real time project if you're going for any tables so definitely it will be partitioned so what is partitioning see if you if you're having big tables so you're having big tables huge volume of tables then if you're using select statement select star from the table name if you're not using any partitioning even you can use indexing that's fine but you are not using any partitioning it will go and check all the record all the record one by one all the record but we can create a partitioning so how see you are going to split this table into multiple partions you are going to one mobile shop you assume that one mobile shop you are going with Mobile Shop you are going in front of you all the mobiles they are placing here you are asking some some particular brand some Samsung brand or so iMobile you are asking some some brand you are asking okay or redmi or something you are asking if all the mobiles are kept in this way you're asking particular mobile the showroom person needs to check with all all the mobiles right so one by one needs to check all this instead he has partitioned see definitely it will be partitioned all the stationary and everywhere it will be partitioned you're asking some some iPhone or something directly they will go to this particular rack even inside that will be having lot of partitions correct so you'll be having inside that also you'll be having a partitions yes you can go for indexing all this so iPhone what is the model you are asking okay this model will be available here this model yes you're going for some other brand Samsung yes you'll go for Samsung lot of models are there each model you go for one more partitions so this is the way if you have partitioned are you going to check all other remaining partition are you going to check no you will not check it so why because since you know this V class I I need something like that if you're using V class you are seeing you are showing that okay I want this particular brand if you're not telling this W class so what the C this particular showroom person will do they will they will go to one partition they will ask they will show you okay you want these mobiles you want these mobiles you want these mobiles you want these mobiles something like that right so they will ask ask some questions based on that they will go for this way so that's the same thing in partitions also happening so partition is nothing but in an Oracle database partitioning enables you to decompose very large table in very large tables and indexes into a smaller and more manageable pieces called partitions each partition is an independent object that's a main important okay so independent object so each and every partitions will be treated as independent object with its own name say for an example this name is table name space partition name so in Oracle we can say it's a partition P1 so to find information on the partition you can quy this select star from all tab partitions in Oracle use this particular table all tab partitions so here we do not have that we cannot create the partitions since it is not Enterprise editions so all tab partition is nothing but it's a metadata table in this table you can use where owner equal to HR and table name equal to this table name you can find all the partitions okay so what are the different partitions VI are there so normally when to partition a table so when should I go for partition simply all the table can I go for par S no if the table size is more than 2 GB okay if the table size is more than 2GB then go for partitions how do I know whether this table is more than 2 GB we have metadata queries So based on the database you can find see if you if you're going for realtime project most of the fact tables and realtime projects are in terms of terabytes do we need to do partitioning yes most of the dimension table will not have in the particular range 2 GB we will not have it but most of the back tables in realtime project in terms of TB you will have all the data so that we have to do tables which contains historical data yes of course the tables contains historical data S Type 2 tables even Dimension table also if it has more historical data so definitely will have more than 2 GB so that particular table you need to go for partitions okay so you have to add it into the new partitions okay so when contents of the table needs to be distributed in a different storage devices so that's what this is one device so storage device also you can go with Partition one is there in one particular device partition two on the same table is there in different devices so that is also fine but Oracle knows it where the data has been stored when table performance is weak then you can go for the partitions okay so whenever you have the huge volume of tables then go for partitions see normally in realtime project so transaction tables sales table invoice table orders table so these tables will have any historical table need to have a partitions two things you have to remember whenever you are going for a partitions the first thing is partition key okay which column I'm go I need to go go and do the partitioning okay which column I have to do the partitioning see if you have employees table based on the table you can select okay which column I can go for the partitioning first one is partition key okay which column you are going to create a partitions set of one or more columns can I create partitions on more columns yes you can create set of one or more columns that determines the partitions in which each row in a partition table should go each row is Ambiguously assigned to a single partitions yes so it will go and each partitions okay it will go on each partitions then we have the partitioning strategy so this is the one so you can go for single level partition or composite partitioning what is single s level partitioning this is single level partitioning okay this is single level partition composite means so this is composite partitioning inside partition you'll be having another partitions that is composite partitions okay these concept are common between you even you have the hive tables you'll be having buckets right partitioning and bucketing same way here also see types of partitions in Oracle we have four types of partitions how many partitions are there in Oracle four partitions are there one is range partition list partition hash partition composite partition composite partition means you can combine any of the partitions like range and list list and hash hash and range so you can combine and then you can create a partitioning so that is a composite partitioning okay composite partitioning is the partitioning strategy range partition see range partition it's similar to Informatica so if you know the definite range so you're going to use say for an example employee stable you have a salary range yes you can create a salary range partitioning one is range partitioning what is the range partitioning range partitioning means you are using some definite range okay 5,000 to 10,000 that is one range 10,000 to 15,000 is another range 5 5,000 I'm making okay 15,000 so this is range and if you if you go to fact tables mostly in realtime project most of the fact tables are range partition with a date column so whenever you have a date column like created time stamp updated time stamp created month updated month something date range partition we have so create created at some month we will use each and every month we will put it in one partition or if you have more volume data each and every date we will put it in one partition so you have to mention that interval is one day so you have to mention interval so what is that interval so if you know the definite range you can put range you can Define the partition one partition two partition three then you have defined okay say for an example today you know the range 0 to 5,000 one partition P1 5,000 to 10,000 P2 10,000 to 15,000 P3 that is what you know as of now but after that you have to mention every 5,000 is the range you have to mention interval 5,000 then say up to P1 P2 P3 we have given the name right after that system itself it will assign some name ccore some name name it will assign I'm just mentioning that P4 so interval 5,000 range of values then system will assign it some ranges so this is what it will go for the types of partitions you can see here there are three different types one is list partition range partition cash partitioning so what is list partitioning we have to define the list list of values you are mentioning P1 okay P1 you are mentioning that values East sales regions so that's you are mentioning that list of values New York Virginia and Florida these two are mentioning in the partitioning one so when to when to use this partitioning while creating the table okay so create table you have to mention the partitioning so like P2 if any values you are inserting where Central where region equal to some data you are making this range this value so you're inserting so insert into table name you're inserting sales region equal to Texas where which partition it will go and store this particular record will go and store in the third partition okay if you're using any other regions like you're using some New York regions it will go to First you are selecting select star from the table name where some region equal to each sales regions you are mentioning that so Oracle will not go and search these two table these two object it will not go and search it will go and search only this particular partitioning so inside that also you can go for one more partition that is range partition also you can go with so that is composite partition see range partition so January and February all this month you are going with so that is one partition March and April May and June so two months you are selecting that's what you can drti the data so directly it will go to this particular partitions then Hash Hash means we are not going to Define just column names you will Define it hash algorithm it will generate you know hash key right some 32bit hash key will generate So based on that it'll go to put it in one particular partitions which column you want to define the hash that column if the data is changing in that column the partition will get change what what is composite partitioning you're going for composite partitioning here range and list partitioning the right hand side if you see this going with range and composite partitioning both you are using range partition and list partition so that means this is the list partition So within this R uh sorry list it will select okay if I have any data for this particular range if I have data based on the January and February it will store here that means narrowing down narrowing down one more time right as I told if I have the rack here they going for iPhone only inside the iPhone you are making more racks here you are defining each model here so in iPhone you are asking some particular model in iPhone also I need no need to check these models right these models no need to check you can directly go to this particular model you can Define that's a way you can see here so this is a list and this way we have the range okay range the range and list both then here we have range and hash both so inside one range so January and February you have hash one hash two hash three hash four in that way so take this particular table sales table okay if you see this sales table you have some product IDs customer ID time ID sometime the sales is happening so you can see 98 2000 2001 98 99 all this data is happening some Channel ID we have some repeated values are there Channel ID right repeated value so you can go for then some promo ID so if you see promo ID also we have some duplicate record so you can go for promo ID on list partition right and channel ID also RIS partitioning and this time ID you can go for range partitioning okay you can put some ranges and quantity sold amount sold and all these are all the data will store if you see here I'm creating the range partitioning range partitioning time ID as the range how can I create it normally you will create create table table name all the column you will mention close it normally this is what you will open and close it right we will put semicolon here but instead of making the semicolon here we'll make Partition by range so this is the keyword Partition by range is the keyword so which partition strategy you are going for and which is the partition key see this time ID is the partition key so based on the time ID range of values it will create a partition okay how many partition I'm creating as of now four partitions what is that so less than 1998 sorry 1999 that means up to 1998 we're going for one partition then less than 2,000 less than 2,000 is one partition less than 2001 so that means each year we are going for one part partition and other partitions all the partitions we are going for maximum value see one disadvantage this one this query so what could be the disadvantage in this query sales 98 see you can see here all the 98 you are loading into one particular partitions whenever you are inserting the data it will go to this partitions whenever you're inserting any data on the 1999 will go for second partitions 2,000 will go for third partition any other date it will go for this partition but one advantage here it is so if you have more values for this particular more than 2001 if you have any values then all this will go to one particular partition that is a we will call it as skus right skewness so the table will be skewed one partition will have more data so we should not go for this one so we will have one method called interval here here okay so what is interval you have to mention the interval here so how much is the interval so interval is so here it is a 10 10,000 not 1,000 it's 10,000 see here partition 1 will go to up to 10,000 it will go to partition that means what is the partition key here which one is partition key so partition key is salary here right so you can see the salary here so what is the partition how many number of partition we have created four number of partition but this partitioning is dynamic partition why if in future if you're getting any values something like 60,000 okay 60,000 65,000 or anything so automatically it will go to the particular partition how we are making this interval so this keyword you have to use okay so use this keyword to Dynamic create the partitions so who will create article will create so make sure that you are using that keyword called interval okay the other one is enable row movement see this is very very important so what is enable row movement whenever you are using any partitions use this enabled row movement at the end so that means so today you have some data you have some data so salary is less than $10,000 so $8,000 you are using so which partition will be there very first time it will be there in the V1 partition okay but you are updating this particular record sometime later this particular record you are updating so if you haven't enable enable row M what will happen if you're updating this particular salary from 8,000 to some 21 1,000 you are updating you assume that that particular record you are getting updated if you don't mention this enabled row moment still this particular record will be there in the P1 that means wrongly it is there in the P1 partition where it should be there it should be available in P3 right so P2 is 11,000 to 20,000 up to here it will be so 21,000 should be there here so whenever you are updating any record if it should move to the other partition okay then you have to mention enable row movement clear see you go and check in Oracle table so these two should be there definitely these two should be there see first one is Partition by which partition we are selecting Partition by range yes it is a range of values right yes which column partition key then what is the interval we are using interval is 10,000 enable row M means whenever the de card is DML operation you are doing that automatically it will be moved to the other partitions got it see you want to select only P1 partition data then you can select this way select star from the table name where sorry no need to use where class select star from the table name partition P1 if you know the directly where how the data has been stored you can select this way partition P1 but if you are selecting select start from the table name where where salary less than 10,000 which partition it will go and check 10,000 it will go and check in the P1 only whether it will go and do the all other partition no it will not go and check so what is that interval what what is the enabled row M so how to select partition key and partition strategy are you clear now okay you can see here see you are doing the collect stats you have to run this particular statement if you want to run the collect stats on the employees table okay you have to run this this particular statement so execute this is plsql statement we are running that dbms stats gather table stats schema name table name just you run it in the Real Time Project also see before collect stats it is showing that same table you can see here P1 P2 P3 P4 four partition we have last analyzed is null number of rows also each partition don't know and blocks also don't know and sample size also don't know but High Valu is mentioning like here 1,000 so 1,000 2,000 3 3,000 4,000 6,000 see these values are there so can you identify what is this how it is created system undor p7 741 see we have created partition 1 partition 2 partition 3 and partition 4 but this partition it automatically created by the keyword called interval if you haven't used keyword called interval This Record will not go to this partition it will go to the P4 only so remember this that's what I have to mention here and if you're refreshing the statistics so from here you are seeing that okay L analyzed on this particular date so number of blocks and total number the blocks uh the number of blocks and total number of blocks and sample size so that means how many records are there in each uh particular blocks and you are seeing the high value of the particular blocks okay collect stats so you can run the collect stats the metadata tables will get collected so this is what the collect stats you have to run so whenever why we are running the collect stats after doing the DML operation on the table DML operations on the table if you do the collect stats Oracle knows it where the data has been stored so you remember in our in Informatica also I have explained that so we can do the collect stats on the source table we can do that collect stats on the target Table after inserting the record why we have to do the collect stats so if you do the collect stats the the analysis will be done on the table got it the analysis will be done on the table then Oracle will go and first see the step statistics it knows the values if you're selecting some value select star from it will go and check the statistics from here it knows which partition the data has been present from that partition it will select the data so this and all you have to do that scheduling you have to schedule it collect stats on the table in the last statement you will put some collect stats any DML operations even in the realtime project if it is Informatica in Informatica Power Center we will put this particular statement at the post SQL so post SQL if you put this right after data getting loaded it will do the collect stats on the particular table okay so you can see here list partitioning list partitioning we are going with Channel ID some list of values right so two and four is one particular partitioning three and N is the different partitioning P2 odd number even number something like that we are making so two and four Channel IDs are into one particular partitioning and this one another partitioning if you are getting any other values so that means it will not go to any partition okay it is go to default partition then then if you have more values then it will go for skus so don't get into this particular one you put some interval so interval of values so that it will go to so but you know definite range here definite list if you know country ID is some list of values then you can go for list partition hash partition means based on the product ID you are selecting the hashing so it will generate some hash value which partition should go for then it'll go for some partition see this partitioning automatically is selecting p33 p34 yeah hi hi Chandra so for this H has partitioning so you said the production ID yeah so uh to select this ID it should have the for example um it is not a primary key yeah something so this is a something ID uh only few it has a low City things here for this one to select the partitioning yeah yeah correct see based on the product ID it will go and generate some hash value okay so now no need to put only one ID here you can put two to three IDs also okay if you see this I will explain I will give you this particular document yeah yeah so this is what uh composite partitioning I will explain composite means you have to put two or three more partitioning in the particular queries okay so this is create table employees number you are putting that enable row M and you are making that this is uh range partitioning okay then list partitioning list of values you know while creating the table create table table name column names partitioning partition P1 you are making some Chennai okay so whoever is City equal to Chennai in the employee then it will move to partition one partition two partition three any other City syst is coming with it will go for the default partitioning so that's we we may think that okay these three cities only we are getting lot of values then we are splitting the data into multiple partition four partition hash partitioning here we are selecting the hash algorithm on the employee number partitions five so five partitions we are creating based on the employee number okay so these many partitions will be created so any any column you can select no issues even you can go for comp some two or three columns also you can select So based on the values it will select but what is composite partitioning composite means combination of two data distribution methods okay so one is you can go for this particular partition so range and hash range and list range and range so any partition any two partition you can select in one particular query itself okay so from the 10 10 G only it is created and I I couldn't able to show you here so actually to create this table so why because we need to have uh Enterprise Edition license yeah I I got that part okay for other example like so if I have a like the millions of record for the the person ID is the primary key then I can can I able to choose that primary key that the person ID as a hash partition key because it will create a millions of par yeah no no no it will not go for Millions it knows where the range of values should go for in that range only it will select so you are selecting how many partition two partitions only you are selecting right it will generate on hash value So based on the hash value it will put so you mean that if I choose the Partition by two then it will only create two partition just make the half half of this two uh make half table yes correct if same product ID is coming it will go to the same partition if different product ID is coming it'll go for something different okay something like odd number even number in that it will generate it will generate hash value system will generate a hash value we are not going to generate hash value okay that I know but we have to Define that how many number of partition you want okay I want to go for 10 partitions yes see employee ID you have 1 million employee ID you have okay 10 partitions only you are creating so each partition one lak record it will go for so it'll automatically it will be partition but we do not know which partition the data has been stored so arle knows it where the data has been stored that's what you have to do the collect stats it knows it where the data is present so SRA another one question so while I create the for example if I create the table then if I mention the partition then okay that will optimize the performance for example if I have a legacy table the history table so um because before I did not create any partition but I have a table so to improve the performance to do the improve the performance tuning for that uh table so we have a option to create again the partition for that table again or um or we need to yes so you we will normally call it as rebuild of the table table rebuild so you have to rebuild the table and one other thing Chandra sorry I asked a lot so for the performance tuning yeah so I have I got before one question you can go by the table of table level or Cy level because uh we can for most of the case we Define bit for the Cy level yeah uh I think yeah uh so uh okay table level we can do it also yeah maybe uh what I try to say so we need to do the performance what the difference between the performance tuning between table level and Cy level uh I fa one question before query level means whatever you are s you are running the select query right M so that select statement you are doing some uh uh some tuning okay so what I told instead of selecting all the columns you are selecting only particular columns right then you are doing that Vare class you are filtering the record then you are using some temp tables while selecting multiple table join so 10 tables you are joining it don't go for 10 table join you do five tables first five tables do it in one temporary table another five tables do it in one temporary table then do the joints of these two temporary table one example I'm saying but not five tables you can go with two table tables three tables also so you create the temporary tables you join it then you go for it these are all theery level tuning whatever I told here before but this is stable level while storing the data itself we are part we are doing the performance tuning okay so that's what table level see whatever here I have explained all quy level okay first one I have explained right so these are all the quy level tuning yes yes yeah yeah I got you yeah thank you you are selecting some large table right you are selecting some large table so don't select like this select star from the table name normally we should not select in the realtime project sometimes if you're are selecting this this entire table uh entire table will get hanged your your session also will get hanged normally in uh Real Time Project you will not be allowed use allowed to use select star from the table name right so always you have to use some row number if it is article table you some row number where row number less than or equal to some five so you will restrict the data so if it is Terra data some sample five so if it is some other tables you'll be using limit right so you'll be using limit statement to limit the records just limit five to limit it then you can you can see that how the data is being stored and even you can put some V class here even sometimes you are going for some very big table you are using created months okay created at month so I'm using created at month something like that it will be there so 0 to 223 or 03 something like that okay so you are going to select some data in the 0 to 2003 in this particular partition range but in that range also you are selecting only five records so why because in this range also this is month level right so entire month how many transaction will be happen on the tables we be having huge volume of data so even if it is a red shift under any databases it will get hanged so don't do that you use limit five and even you can use V class also you can put V class where created at date so even you will have so one more and so that's what you have to restrict the data so create created at date you'll be having some date okay say for an example 12 0 to 2023 see why directly you should not use uh created at date see first it will go to this particular partition okay this is top level partition inside that it will go and select only one date then you are selecting only five records so this will quickly give you the data so this way you can uh you can improve the performance sorry you can restrict the data in the select statement this is what you have to do the select in the Real Time Project okay so always don't select as select start from the table name yeah I got it yeah thank you yeah thank you and um what is parallel hint like so you'll be using uh you'll be using some parallel here okay some slash the thing is here I cannot show you that's uh one thing and you'll be using something like uh some parallel 32 you are using this way so this way you'll be running some queres okay that means while running this query it will parall it will execute this particular query so the optimizer will give you some data parall it will split up the range go and select the data right it will will select parall par some 12 threats is running in back end it will it will allocate more resources to this particular queries parall and then it will more thread is running so that's what it will run very quickly parallel threads so 32 means it will create 32 threads yeah parallel processing so how will we know how how much we can give like 32 or 16 no no no normally we will not give 32 and all not for all the queries simply we will we have to select if the query is not performing well then we will put like this then it will we will check okay whether the query is running or not so that's a way you can you can do parallel 32 something so you'll be using this way see here so select SL parallel table name degree of parallelism we are selecting eight parallel um degree of parallelism means here eight different process with open to run the select statement fast got it here we are using select eight eight parallelism this way you have to use employee ID it will select say for an example employee ID 100 to 200 you have so in that it will select eight parallel hints eight par sorry eight parallel processing it will select clear see instead of one how many parallelism is running three parallelism running right so this is what parallel enable hint SL star parallel index employee employ id8 they are using where Department ID equal to this one so it will allocate this much parallel processing got it so you can go for parallel 62 parallel 32 parallel 16 all this whatever you are seeing anything in the select statement the realtime project right in the realtime project if you're going with anything in the query itself they have mentioned SL star and star slash it is a parallelism parallel hint they're using Okay okay so don't think that this is like a commented out so don't sometimes what we will we will assume that okay this is has been commented out right so we will think that it has been commented out this will not work not in that meaning it is called parallel hint okay somebody used this way that means they have used some parallel hint normally we will think okay this is nothing but something like that commented out no it is not commented out it is using the parallel hint eight parallel processing it will be running in the back end it will select this particular table so question yeah one last one last question so for example in the real time so if I saw the tables correct so if I just saw the table then uh so how to check that how this table is performing doing by performance doing like for example so uh I need to do the how to do the research like instead for for to check the performance tuning for the one table so yeah do the do the do the explain plan explain plan do the explain plan and then check whether this table has been partitioned or not explain plan only you have to do okay so you can go for select star from the table name what is the query you are going to use so that's what you can perform uh you can go for this performance tuning right you have to use all explain plan only so for example everything the table first create in the de environment and then uh later once before put in the production environment so they want to scan the table okay what is the it is it is make some perit something like later or not so those kind of thing uh need to be checked before putting the production so so for that that's why you have to put always partitioning see if it is Big tables and all you are going to use some fact tables anything just to create the tables with Partition okay for the fact table always go and check fact table it will be a range partitioning definitely range partitioning will be there so inside the range one more composite partition will be there if you go and check two level two levels of partition should be there definitely you can go and describe the table okay and the describe table itself it will show you the how many partitions are there what is the partition column if it is something like AWS Shi and all you can clearly in the below also if you do the describing it will show you that uh Terra dat also it will show you how this tables has been uh created all this it will show you so even if it is Oracle yeah if it is Oracle so how I explained you have to put allore tabore partitions it will give you that information the same thing for the dim table too yeah so yeah yeah yes yes yes okay and and when you choose Partition by employee ID and if you don't give number of partitions then it will create only one partition uh which party which partitions uh which type of partitions like Partition by uh employee ID column name we we give Partition by which partition type that's what we have to mention right which partition Partition by uh maybe I don't remember like you said column name so column name no no column name is partition key which partition type you have to go for three partitions are there right see always we have to mention Partition by range Partition by list Partition by hash right you have to mention that way so you want to go for hash partitions okay hash partitions means we have to provide number of partitions no need right we have to we have to provide number of partitions see Partition by the type of partition which type of partition we want to go for then inside we have to mention the partition key then how many number of partition we have to go for so for an example here we are creating five partitions you assume that five partitions means based on the employee ID so it will so it will create okay so you are going for employee ID from 100 to th000 or 100 to 2,000 there are some 100 values are there in between okay 100 values are there how many partitions you are creating five partitions so that means 100 to 110 or 100 to 120 it will go for one partition see I'm saying my up to my knowledge I'm I'm I'm dividing that particular employee ID with under to 12 but Oracle knows it based on the hash value so hash of employee ID it will make one value some unique number will get generated hash value with that hash value it will put it in the partition okay these has values will go to one particular partition this hash value will go to one particular partition this hash value should go to another partition in that way it will create so it will take the beginning value and end value and then it make hash value and divides into five partitions right correct it will split the partition into five then it will generate hash value article knows it where should I go go and place this particular value