Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles on Trending Technologies
Technical articles with clear explanations and examples
How does the Q-table help determine the next action for the 'agent' in terms of reinforcement learning in Machine Learning?
We previously understood how Q-learning works, with the help of Q-value and Q-table. Q-learning is a type of reinforcement learning algorithm that contains an ‘agent’ that takes actions required to reach the optimal solution. This is achieved with the help of Q-table that is present as a neural network. It helps take the right step that maximizes the reward, thereby reaching the optimal solution.Now, let us see how the agent uses the policy to decide on the next step that it needs to take to achieve optimum results.The policy considers the Q-values of all possible actions that could be taken, ...
Read MoreHow to display open cursors in Oracle?
Problem:You want to display open cursors in Oracle.SolutionWe can query the data dictionary to determine the number of cursors that are open per session. "V$SESSION" provides a more accurate number of the cursors currently open than "V$OPEN_CURSOR".Exampleselect a.value , c.username , c.machine , c.sid , c.serial# from v$sesstat a , v$statname b , v$session c where a.statistic# = b.statistic# and c.sid = a.sid and b.name = 'opened cursors current' and a.value != 0 and c.username IS NOT NULL order by 1, 2;The OPEN_CURSORS initialization parameter determines the maximum number of cursors a session can have open.
Read MoreHow to identify SQL queries with the most waits in Oracle?
Problem:You want to identify the SQL statements responsible for the most waits in your database.SolutionWe can use below SQL statement to identify SQL causing problem.The below query will rank SQL statements that ran during the past 30 minutes and display them as per the total time waited by each query.ExampleSELECT ash.user_id, u.username, s.sql_text, SUM(ash.wait_time + ash.time_waited) ttl_wait_time FROM v$active_session_history ash, v$sqlarea s, dba_users u WHERE ash.sample_time BETWEEN sysdate - 60/2880 AND sysdate AND ash.sql_id = s.sql_id AND ash.user_id = u.user_id GROUP BY ash.user_id, s.sql_text, u.username ORDER BY ttl_wait_time ;When you have a performance ...
Read MoreHow to access values from previous or following rows in Oracle ?
You want to use Oracle aggregate function XMLAGG for string aggregation.?Solution:You would like to include calculations based on preceding and following rows in the result set.Oracle supports the LAG and LEAD analytical functions to provide access to multiple rows in a table, utilizing preceding or following logic and you won’t need to resort to joining the source data to itself. To demonstrate the usage we will use the students data.The LAG function can be used to see which student/s joining followed another, and also to calculate the elapsed time between joining.SQL: Identify the student joining informationExampleSELECT first_name, ...
Read MoreHow to generate a data model from data dictionary tables in Oracle?
Problem:You wanted to generate a data model from data dictionary tables in OracleSolution:The Oracle data dictionary is a collection of tables and related views that enable us to view the structure of the Oracle database. By querying these tables and views, we can obtain information about every object and every user of the database.IntroductionThe data dictionary is packaged with a series of views owned by the SYS user. These views, known as static data dictionary views, present information contained in tables that are updated when Oracle processes a Data Definition Language (DDL) statement.There is a second set of views known ...
Read MoreHow to identify the SQL consuming more resources in Oracle?
Problem:You want to identify the SQL statements consuming more resources in Oracle.Solution“V$SQLSTATS" view displays performance statistics for SQL statements that have recently executed. You can also use "V$SQL” and “V$SQLAREA" to report on SQL resource usage. "V$SQLSTATS” is faster and retains information for a longer period of time, but contains only a subset of the columns in “V$SQL" and "V$SQLAREA”.Exampleselect * from( select sql_text ,buffer_gets ,disk_reads ,sorts ,cpu_time/1000000 cpu_sec ,executions ,rows_processed from v$sqlstats order by cpu_time DESC) where rownum < 20;
Read MoreHow to display a SQL execution progress along with execution plan in Oracle?
Problem:You want to view where Oracle SQL is taking time within a SQL execution plan.SolutionWith Oracle 11g version, we can view SQL execution plan progress while the SQL is running. The “V$SQL_PLAN_MONITOR” view contains a row for each step of a SQL statement’s execution plan. Below SQL will help to view the execution plan along with the progress.The “V$SQL_PLAN_MONITOR" provides you with information on the steps that are using the most resources. The statistics in "V$SQL_PLAN_MONITOR” are updated every second.We can also generate a real time text, HTML, or even a XML report of query progress within an execution plan ...
Read MoreHow to determine the approximate amount of SQL work left in Oracle?
Problem:You want to know how much longer a long running SQL might ake to finish.SolutionWe can use “V$SESSION_LONGOPS" view to know the approximate time of a query left to execute. "V$SESSION_LONGOPS” view displays the status of various database operations that have been running for longer than six seconds. Please note that this view give you only a rough estimate of when a SQL might complete.Exampleselect a.username , a.opname , b.sql_text , to_char(a.start_time, 'DD-MON-YY HH24:MI') start_time , a.elapsed_seconds how_long , a.time_remaining secs_left , a.sofar , a.totalwork , round(a.sofar/a.totalwork*100, 2) percent from v$session_longops a ,v$sql ...
Read MoreHow to monitor real time SQL execution statistics in Oracle?
Problem:You want to monitor currently executing SQL statistics in Oracle.SolutionIf your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view the resource consumption as it updates. These statistics are gathered by default when a SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.The “V$SQL_MONITOR" view includes a subset of statistics contained in the "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” views.The “V$SQL_MONITOR" view displays real-time statistics ...
Read MoreHow to limit Database Resources per Session in Oracle?
Problem:You want to limit the amount of resources a user can consume in your database.SolutionTo limit the resources, we can follow below steps.We can use below SQL statement to view the current setting of RESOURCE_LIMIT in our database.select name, value from v$parameter where name='resource_limit';Create a profile to limit the resources and assign it to a user. It won’t limit the CPU utilization though.ExampleCREATE PROFILE test_profile LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION UNLIMITED CPU_PER_CALL 300000 CONNECT_TIME ...
Read More