Stack Overflow
Stack Overflow

Questions tagged [cbo]

16 questions
162
votes
10 answers

PostgreSQL - fetch the rows which have the Max value for a column in each GROUP BY group

I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent lives_remaining total for each usr_id There are…
Joshua Berry
  • 2,230
  • 3
  • 21
  • 24
4
votes
5 answers

Tips for improving this slow mysql query?

I'm using a query which generally executes in under a second, but sometimes takes between 10-40 seconds to finish. I'm actually not totally clear on how the subquery works, I just know that it works, in that it gives me 15 rows for each…
makeee
  • 2,765
  • 5
  • 35
  • 42
4
votes
1 answer

Can we leverage Spark's CBO (Cost Based Optimizer) with native parquet or in-memory dataframe?

Say i want to join 3 tables A,B,C with inner join and C being very small. #DUMMY EXAMPLE with IN-MEMORY table, but same issue if load table using spark.read.parquet("") var A = (1 to 1000000).toSeq.toDF("A") var B = (1 to…
Simon
  • 193
  • 1
  • 9
3
votes
1 answer

Adding 'distinct' keyword to oracle query obliterates query performance for no reason

I am quite confused by something I'm seeing in an Oracle 10 database. I have the following query. select t2.duplicate_num from table1 t1, table2 t2, ( select joincriteria_0 from intable1 it1, intable2 it2 where it2.identifier in…
Evan Cross
  • 31
  • 1
  • 3
2
votes
1 answer

Performance problem with QUERY using BIND variables and OR condition in Oracle 12.2

I am having a hard time understanding why the Oracle CBO is behaving the way it does when a bind variable is part of a OR condition. My environment Oracle 12.2 over Red Hat Linux 7 HINT. I am just providing a simplification of the query where the…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
2
votes
1 answer

Determining the Efferent coupling between objects (CBO Metric) using the parsed byte-code generated by BCEL

I have built a program, which takes in a provided ".class" file and parses it using the BCEL, I've learnt how to calculate the LCOM4 value now. Now I would like to know how to calculate the CBO(Coupling between object) value of the class file. I've…
Shehanka Fernando
  • 55
  • 3
2
votes
1 answer

Oracle adding a condition in where clause slows down a query

While generating a report based on few tables out of which some are really huge(1+ billion records) and some pretty small (100 records) it is taking 17 minutes. but where I added a condition to the where clause it started running for 1+ hours. The…
user3570198
  • 61
  • 1
  • 7
2
votes
1 answer

How to increase Oracle CBO cost estimation for hash joins, group by's and order by's without hints

It seems that on some of the servers that we have, the cost of hash joins, group by's and order by's is too low compared to the actual cost. I.e. often execution plans with index range scans outperform the former, but on explain plan the cost shows…
Andrew not the Saint
  • 2,496
  • 2
  • 17
  • 22
1
vote
1 answer

Oracle 12.2 - Wrong output in query with NESTED LOOPS and ANTI JOIN

I found this strange behaviour in Oracle 12cR2 ( over Linux Red Hat 7 ) with this particular query. It looks to me like it is a bug, but I post the question just in case someone has found a similar issue. The query is triggered by a Java…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
1
vote
0 answers

Oracle Query with Cross join - Performance problem

I hope anyone can help me to see whether I can improve a bit more this query: Environment: Oracle Database 12cR2 Linux Red Hat 7 VM with 8 CPU and 32GB RAM Statistics are up to date calculated with AUTO SAMPLE and method FOR ALL COLUMNS SIZE…
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
1
vote
3 answers

Oracle MAX() bug with NULLs CBO index fix leads to index limitation

Update 1/31/2011 I think I'm running against a DB limit. The GROUP BY expression and all nondistinct aggregate functions may have exceeded a single database block. See http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm…
user78706
1
vote
1 answer

hive: Plan not optimized by CBO

I am running a explain plan in hive it gives me below error. Plan not optimized by CBO. When I checked the logs it says. 2016-03-14 00:37:12,082 INFO [HiveServer2-Handler-Pool: Thread-41]: parse.BaseSemanticAnalyzer…
Amit Sharma
  • 67
  • 9
0
votes
0 answers

Oracle timestamp with local time zone CBO Format

I recently converted some "Date" columns to "Timestamp(6) with local time zone" in order to use oracle localization functionality, without modify existing enterprise applications. Everything works as expected: client sessiontimezone has an impact on…
T.TAV
  • 1
0
votes
1 answer

Optimizing Hive subquery queries

I am using HDP 2.6.2 and hive. I have a situation where I am updating a partitioned table from a large table based on a column and the query just doesnt perform well and I cant understand why. The insert statement below is an example insert into…
Subramaniam Ramasubramanian
  • 859
  • 1
  • 12
  • 31
0
votes
0 answers

Oracle Optimizer not taking the same plan. SQL Dev vs Application Code

I'm really stuck on this. I have a SQL that when I execute an Explain on it, it shows a very fast execution. When the application runs the SQL, it takes another Explain plan and very expensive set of Cartesian Products This is an Oracle 12C…
Tony Schaffert
  • 1
  • 1
1
2