Hinzinfotech

The Oracle RAC environments have a one to many relationship between the database and instances.In other words, multiple instances access a single database.Oracle RAC uses Oracle Clusterware for the infrastructure in order to bind multiple servers so that they operate as a single system.Oracle Clusterware also manages resources such as virtual Internet protocol, addresses,databases,listeners, services. […]

Description: You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues. You can use the below scripts to quickly act based on your scenario SQL script to kill all the sessions from the database — Note: Use this script with caution as this will kill all […]

The basic health checks Performing the below-mentioned queries and commands which applicable for RAC,standalone database and windows. Check free space on disk and DB uptime$ df -k DB uptime control whether database Listener is running$ lsnrctl status control whether database is running$ srvctl status database -d racdb $ sqlplus / as sysdba SQL> select name,open_mode […]

step 1: create the tuning task for the sql idSQL> declaresql_tune_task_id varchar2(100);beginl_sql_tune_task_id := dbms_sqltune.create_tuning_task (begin_snap => 41062,end_snap => 44516,sql_id => ‘2mjr0fw17sjuh’,scope => dbms_sqltune.scope_comprehensive,time_limit => 10800,task_name => ‘2mjr0fw17sjuh_tuning_task’,description => ‘tuning_in_OLBUI’);dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);end; DECLARE  sql_tune_task_id VARCHAR2(100);BEGIN  sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (    sql_id       => ‘2mjr0fw17sjuh’,    begin_snap    => 41062,    end_snap     […]

Here are the Generating statistics in the Oracle database examples below: exec dbms_stats.gather_system_stats(‘Start’);select * from sys.aux_stats$;exec dbms_stats.gather_table_stats(ownname => ‘SH’, tabname => ‘SALES’, cascade=>true);select * from dba_tab_statistics where table_name = ‘SALES’; select * from sales;select * from dba_tab_columns where table_name = ‘SALES’;exec dbms_stats.gather_system_stats(‘NOWORKLOAD’);select * from sys.aux_stats$;select * from v$sql_plan; exec dbms_stats.gather_database_stats;exec dbms_stats.gather_dictionary_stats;exec dbms_stats.gather_schema_stats(ownname => ‘SH’);exec dbms_stats.gather_table_stats(ownname […]

Q1.when you decide to tune the queries? Answer: We call these queries as “top consuming” queries. These queries do not exceed 5 percent of the total queries Or mostly it is much less.But most of the times, the top consuming queries use 80% of the total resources. This is a really huge ratio. Less than […]

1.To list long-running forms user sessions select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) fromv$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ andp.addr=s.paddr ; 2.To list inactive Sessions respective username SELECT username,count(*) num_inv_sessFROM v$sessionwhere last_call_et > 3600and username is not nullAND STATUS=’INACTIVE’group by usernameorder by num_inv_sess DESC; SELECT count(*) FROM v$session where last_call_et > 43200 and […]

Export and Import using all parameters: 1.SOURCE_EDITION Parameter SQL> show userUSER is “SYS”SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to remo;Grant succeeded.SQL> alter user remo enable editions;User altered.SQL> grant create any view to remo;Grant succeeded.SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME=’REMO’; USERNAME E-REMOYSQL> col USERNAME for a10SQL> / USERNAME E REMO YSQL> conn remo/remoConnected.SQL> […]

High Water Mark management in Oracle Database: Description: ⦁ Each table is made up of extents and each extent is made up of oracle blocks – a common block size is 8k. So you have a table with 10 extents (80K). ⦁ When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward […]

Table Partition: SQL>select * from user_part_tables; SQL>select * from user_tab_partitions; SQL> select table_name,table_owner,partition_name,subpartition_count from dba_tab_partitions where table_name='<table_name>’; Oracle you can partition a table by CREATING PARTITION TABLES 1)Range Partitioning:  This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;for example, value of year. Performance is […]